Script 89: Tag AdGroups with Dynamic Action Value (Cross Client; DC Market=NL)
Purpose
Tag AdGroups with Dynamic Action value calculated based on mapped Strategy ROAS and historical ROAS with a 120-day lookback.
To Elaborate
The Python script solves the problem of tagging AdGroups with a dynamic action value. This value is calculated based on two factors: the mapped Strategy ROAS (Return on Ad Spend) and the historical ROAS with a 120-day lookback. The goal is to adjust bids for AdGroups to optimize future ROAS and bring it closer to the target ROAS.
Walking Through the Code
- Custom Parameters:
- The script defines a dictionary called
ROAS_BID_ADJUSTMENT_TABLEthat maps the difference between historical ROAS and target ROAS to a bid adjustment percentage.
- The script defines a dictionary called
- Column Constants:
- The script defines various column constants used for data manipulation and output.
- User Changeable Parameters:
- The script initializes two variables,
outputDf[BULK_COL_DASTRATEGY]andoutputDf[BULK_COL_DAVALUE], with placeholder values.
- The script initializes two variables,
- Strategy ROAS Assignment:
- The script assigns a strategy ROAS value to each row in the input dataframe based on the current ROAS performance.
- Rows with ROAS <= 2.75 are assigned the strategy “NoRevenue_ROAS_2.75” with a strategy ROAS of 2.75.
- Rows with ROAS > 2.75 and <= 3.40 are assigned the strategy “Low_ROAS_3” with a strategy ROAS of 3.0.
- Rows with ROAS > 3.40 and < 5.00 are assigned the strategy “Medium_ROAS_3.5” with a strategy ROAS of 3.5.
- Rows with ROAS >= 5.00 are assigned the strategy “High_ROAS_5” with a strategy ROAS of 5.0.
- Target ROAS Calculation:
- The script defines a function
parse_roasthat extracts the target ROAS from the strategy name in each row of the input dataframe. - The function splits the strategy name by spaces and assumes that the target ROAS is the last value.
- The target ROAS is then assigned to the
TARGET_ROAScolumn in the input dataframe.
- The script defines a function
- Bid Adjustment Calculation:
- The script defines a function
calc_bid_adjthat calculates the bid adjustment based on the historical ROAS and target ROAS in each row of the input dataframe. - The function checks if the target ROAS is valid (not NaN or <= 0) and if the historical ROAS is greater than 0.
- If both conditions are met, the script calculates the difference between the historical ROAS and target ROAS (
roas_diff). - The
roas_diffis then used to look up the bid adjustment percentage from theROAS_BID_ADJUSTMENT_TABLE. - The bid adjustment is assigned to the
TMP_DA_VALUEcolumn in the input dataframe.
- The script defines a function
- Output Preparation:
- The script fills any NaN values in the input dataframe with empty strings.
- It identifies rows where the bid adjustment or strategy has changed compared to the original values.
- The script creates a new dataframe
outputDfwith the changed rows and selected columns. - The column names are renamed to match the desired output format.
- Output Display:
- If the
outputDfdataframe is not empty, it is displayed in a tabular format. - Otherwise, a message indicating an empty output is displayed.
- If the
- Finished:
- The script prints “Finished” to indicate the completion of the code execution.
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-05-15 07:44:05 GMT