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

Purpose:

The Python script dynamically adjusts bid values for ad groups based on historical and target Return on Advertising Spend (ROAS) to optimize advertising strategies across clients in the Dutch market.

To Elaborate

The script is designed to optimize advertising strategies by adjusting bid values for ad groups based on their historical and target Return on Advertising Spend (ROAS). It calculates the difference between historical ROAS and a mapped target ROAS, then uses this difference to determine a bid adjustment percentage from a predefined table. The goal is to align future ROAS with the target ROAS by either increasing or decreasing bids. This process is applied across multiple clients, specifically targeting the Dutch market, and aims to enhance the efficiency and effectiveness of advertising campaigns by ensuring that ad groups are tagged with the appropriate dynamic action values.

Walking Through the Code

  1. Custom Parameters Setup
    • A dictionary ROAS_BID_ADJUSTMENT_TABLE is defined to map 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 for target and strategy ROAS are initialized in the input DataFrame to store intermediate calculations.
    • The script assigns a strategy ROAS based on current ROAS performance, categorizing them into predefined ranges (e.g., ‘NoRevenue_ROAS_2.75’, ‘Low_ROAS_3’).
  3. Target ROAS Extraction
    • A function parse_roas extracts the target ROAS from the strategy name in the input data, assuming it follows a specific format.
  4. Bid Adjustment Calculation
    • The calc_bid_adj function calculates the bid adjustment needed to move historical ROAS towards the target ROAS. It uses the ROAS difference to look up the adjustment percentage from the predefined table.
    • The function handles cases where the target ROAS is not parsed correctly or historical ROAS is zero, ensuring no adjustment is made in these scenarios.
  5. Output Preparation
    • The script identifies rows where the dynamic action strategy or value has changed and prepares an output DataFrame with these changes.
    • The output is then printed or flagged as empty if no changes are detected.

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 2025-03-11 01:25:51 GMT

comments powered by Disqus