Script 89: Tag AdGroups with Dynamic Action Value (Cross Client; DC Market=NL)

Purpose

The Python script tags AdGroups with a dynamic action value based on historical and target ROAS (Return on Advertising Spend) to optimize bid adjustments.

To Elaborate

The script is designed to optimize advertising campaigns by adjusting bids based on the Return on Advertising Spend (ROAS). It calculates a dynamic action value for each AdGroup by comparing historical ROAS with a target ROAS. The script uses a predefined table to determine the percentage by which bids should be adjusted, depending on the difference between historical and target ROAS. If the historical ROAS is higher than the target, the script suggests increasing bids to bring future ROAS down towards the target. Conversely, if the historical ROAS is lower, it suggests decreasing bids. This approach aims to align the actual performance with strategic goals, ensuring efficient budget allocation and maximizing advertising returns.

Walking Through the Code

  1. Custom Parameters Setup
    • The script begins by defining a dictionary, ROAS_BID_ADJUSTMENT_TABLE, which maps the difference between historical and target ROAS to a bid adjustment percentage. This table is a key user-changeable parameter that dictates how bids are adjusted based on ROAS differences.
  2. Data Preparation
    • Temporary columns are added to the input DataFrame to store intermediate values such as target and strategy ROAS. These columns are initialized with NaN values to ensure they are cleared before processing.
  3. Assigning Strategy ROAS
    • The script assigns a strategy ROAS to each AdGroup based on its current ROAS performance. It categorizes ROAS into different bands (e.g., 2.75, 3.0, 3.5, 5.0) and assigns corresponding strategy names and values.
  4. Parsing Target ROAS
    • A function, parse_roas, extracts the target ROAS from the strategy name in the input data. This function assumes that the target ROAS is the last element in the strategy name string.
  5. Calculating Bid Adjustments
    • The calc_bid_adj function calculates the bid adjustment needed to move the historical ROAS towards the target ROAS. It computes the difference between the two ROAS values, clips it to the range defined in the adjustment table, and looks up the corresponding adjustment percentage.
  6. Preparing Output
    • The script identifies rows where the calculated dynamic action value or strategy differs from the existing values. It then prepares an output DataFrame with these changed rows, renaming columns to match the expected output format.
  7. Final Output
    • The script prints the final output DataFrame if there are any changes; otherwise, it indicates that the output is empty.

Vitals

  • Script ID : 89
  • Client ID / Customer ID: 1306919857 / 64302
  • Action Type: Bulk Upload
  • Item Changed: AdGroup
  • Output Columns: Account, Campaign, Group, DA-Value, DA-Strategy
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Michael Huang (mhuang@marinsoftware.com)
  • Created by Michael Huang on 2023-04-25 06:53
  • Last Updated by Grégory Pantaine on 2023-12-06 04:01
> See it in Action

Python Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
#
# Tag AdGroup with ROAS adj value to be applied by Dynamic Action
#
# Michael S. Huang
# Created: 2023-04-25
# Updated: 2023-07-04
#

##### Custom Parameters #########

# ROAS DIFF := Hist Roas - Target ROAS
# When Hist ROAS is above Target ROAS (ROAS DIFF > 0), need to push Bids UP 
# so that future ROAS comes down towards Target ROAS
ROAS_BID_ADJUSTMENT_TABLE = {
  # <ROAS DIFF>: <BID ADJ PERCENTAGE>
    -9: -50,  
    -8: -45,
    -7: -40,
    -6: -35,
    -5: -30,
    -4: -25,
    -3: -20,
    -2: -15,
    -1: -10,
    0: 0,
    1: 10,
    2: 12,
    3: 15,
    4: 20,
    5: 20,
    6: 25,
    7: 25,
    8: 30,
    9: 35,
    10: 40,
}

##### END Custom Parameters #####

RPT_COL_CLIENT = 'Client'
RPT_COL_CURRENCY = 'Currency'
RPT_COL_GROUP = 'Group'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DASTRATEGY = 'DA-Strategy'
RPT_COL_DAVALUE = 'DA-Value'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_ROAS = 'ROAS'
RPT_COL_REVENUE = 'Revenue £'
RPT_COL_PUB_COST = 'Pub. Cost £'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_DASTRATEGY = 'DA-Strategy'
BULK_COL_DAVALUE = 'DA-Value'

outputDf[BULK_COL_DASTRATEGY] = "<<YOUR VALUE>>"
outputDf[BULK_COL_DAVALUE] = "<<YOUR VALUE>>"

TARGET_ROAS = 'target_roas'
STRATEGY_ROAS = 'strategy_roas'
TMP_DA_STRATEGY = RPT_COL_DASTRATEGY + '_'
TMP_DA_VALUE = RPT_COL_DAVALUE + '_'

# clear temp columns
inputDf[TARGET_ROAS] = np.nan
inputDf[STRATEGY_ROAS] = np.nan
inputDf[TMP_DA_STRATEGY] = np.nan
inputDf[TMP_DA_VALUE] = np.nan

### Assign Strategy ROAS based on current ROAS Performance

# ROAS 2.75
inputDf.loc[ inputDf[RPT_COL_ROAS] <= 2.75, \
             [TMP_DA_STRATEGY, STRATEGY_ROAS] ] \
            = ['NoRevenue_ROAS_2.75', 2.75]

# ROAS 3
inputDf.loc[ (inputDf[RPT_COL_ROAS] > 2.75) & \
             (inputDf[RPT_COL_ROAS] <= 3.40), \
             [TMP_DA_STRATEGY, STRATEGY_ROAS] ] \
            = ['Low_ROAS_3', 3.0]

# ROAS 3.5
inputDf.loc[ (inputDf[RPT_COL_ROAS] > 3.40) & \
             (inputDf[RPT_COL_ROAS] < 5.00), \
             [TMP_DA_STRATEGY, STRATEGY_ROAS] ] \
            = ['Medium_ROAS_3.5', 3.5] 

# ROAS 5
inputDf.loc[ inputDf[RPT_COL_ROAS] >= 5.00, \
             [TMP_DA_STRATEGY, STRATEGY_ROAS] ] \
            = ['High_ROAS_5', 5.0] 


### Get Target ROAS from mapped Strategy

def parse_roas(row):
    SEP = ' '
    vals = row[RPT_COL_STRATEGY].split(SEP)
    # assume Target ROAS is after last separator
    roas = np.nan
    try:
      roas = float(vals[-1])
    except:
      roas = np.nan

    return roas

inputDf[TARGET_ROAS] = inputDf.apply(parse_roas, axis=1)

#print(tableize(inputDf))

### Calc BID adjustment to move Historical ROAS towards Target ROAS

def calc_bid_adj(row):
    # historical ROAS
    from_roas = row[RPT_COL_ROAS]
    # desired target ROAS (parsed from Strategy name)
    to_roas = row[TARGET_ROAS]
    adj = np.nan

    if (np.isnan(to_roas)) or (to_roas <= 0):
      # Target ROAS not parsed correctly, don't adjust
      adj = 0
    elif (from_roas > 0):

      # ROAS DIFF := Hist Roas - Target ROAS
      roas_diff = round(from_roas - to_roas)

      # clip roas_diff to range within ROAS_BID_ADJUSTMENT_TABLE
      if roas_diff > 0:
        roas_diff = min(roas_diff, max(ROAS_BID_ADJUSTMENT_TABLE))
      else:
        roas_diff = max(roas_diff, min(ROAS_BID_ADJUSTMENT_TABLE))

      #print(f"from_roas: {from_roas}, to_roas: {to_roas}, roas_diff: {roas_diff}")

      # lookup adj from customer supplied table
      adj = ROAS_BID_ADJUSTMENT_TABLE[roas_diff]

      #print(f"adj: {adj}")
    else:
      print(f"WARN: Historical ROAS is zero, no adjustment. Campaign: {row[RPT_COL_GROUP]}, AdGroup: {row[RPT_COL_GROUP]}")
      adj = 0

    return adj

inputDf[TMP_DA_VALUE] = inputDf.apply(calc_bid_adj, axis=1)

print("after lookup adj", tableize(inputDf))

### Prepare output with changed rows

# comparing na doesn't work, so replace with empty string
inputDf.fillna('', inplace=True)
changed = (inputDf[TMP_DA_VALUE] != inputDf[RPT_COL_DAVALUE]) | \
          (inputDf[TMP_DA_STRATEGY] != inputDf[RPT_COL_DASTRATEGY])

cols = [RPT_COL_CLIENT, BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_GROUP, TMP_DA_STRATEGY, TMP_DA_VALUE]

outputDf = inputDf.loc[ changed, cols ].copy() \
                  .rename(columns = {
                    TMP_DA_STRATEGY: BULK_COL_DASTRATEGY,
                    TMP_DA_VALUE: BULK_COL_DAVALUE
                  })

if not outputDf.empty:
  print(tableize(outputDf))
else:
  print("Empty outputDf")

print("Finished")

Post generated on 2024-11-27 06:58:46 GMT

comments powered by Disqus