Script 137: New Campaigns Adj Google tCPA and Budget
Purpose:
The Python script adjusts the target CPA and daily budget of Google campaigns labeled as ‘New Launch’ through ‘New - Round 5’ based on their ROAS over the past 30 days and campaign maturity.
To Elaborate
The script is designed to optimize Google advertising campaigns by adjusting their target Cost Per Acquisition (tCPA) and daily budget. It focuses on campaigns labeled from ‘New Launch’ to ‘New - Round 5’, using the Return on Advertising Spend (ROAS) from the last 30 days and the campaign’s maturity level as criteria for adjustments. The script applies specific adjustment rules based on predefined criteria, such as ROAS thresholds and campaign maturity stages. These adjustments aim to enhance the performance of new campaigns by dynamically modifying their budgets and tCPA to align with their performance metrics. The script processes campaigns with and without a recorded last update date separately, ensuring that adjustments are only applied to campaigns that meet specific conditions, such as having no previous updates or being updated more than 14 days ago.
Walking Through the Code
- Define Adjustment Criteria:
- The script begins by defining a list of criteria for adjusting tCPA and daily budgets. Each criterion specifies a campaign maturity stage, ROAS range, and corresponding adjustments for tCPA and budget.
- Initialize Temporary Columns:
- Temporary columns are created in the input DataFrame to store new budget and tCPA values. These columns are initially set to NaN.
- Process Campaigns Without Last Update Date:
- Campaigns without a recorded last update date are filtered into a separate DataFrame. The script iterates over the adjustment criteria, applying them to these campaigns if they match the specified maturity stage and ROAS range.
- Apply Adjustments:
- For matched campaigns, the script calculates new budget and tCPA values based on the adjustment criteria and updates the temporary columns with these values.
- Identify and Process Changed Campaigns:
- Campaigns with changed budget or tCPA values are identified. The script creates a new DataFrame for these campaigns, updating the last adjustment date to the current date.
- Process Campaigns With Last Update Date:
- Campaigns with a recorded last update date are processed similarly, but only if the last update was more than 14 days ago. Adjustments are applied based on the same criteria.
- Merge and Finalize Adjustments:
- The script merges the DataFrames of campaigns with and without last update dates, excluding any empty DataFrames. It ensures that the minimum tCPA is set to 2.50 for all campaigns in the final output DataFrame.
Vitals
- Script ID : 137
- Client ID / Customer ID: 1306925431 / 60269477
- Action Type: Bulk Upload (Preview)
- Item Changed: Campaign
- Output Columns: Account, Campaign, Daily Budget, Publisher Target CPA, Date of Last tCPA / Daily Budget Adj.
- Linked Datasource: M1 Report
- Reference Datasource: None
- Owner: Michael Huang (mhuang@marinsoftware.com)
- Created by Michael Huang on 2023-05-25 11:07
- Last Updated by alejandro@rainmakeradventures.com on 2024-01-26 18:55
> 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
175
176
177
178
179
180
181
182
183
184
185
#
# Publisher Budget and Target CPA Adjustment - New Campaigns
#
#
# Author: Michael S. Huang
# Date: 2023-05-26
#
# define criteria for campaign tCPA and Daily Budget adjustments
# note: MIN values are inclusive; MAX values are non-inclusive
campaign_adj_criteria = [
# format: (new round, end, min roas, max roas, tCPA adj, budget adj),
('New - Round 2', 0.30, 0.50, -0.30, 0.0),
('New - Round 2', 0.50, 0.75, -0.20, 0.0),
('New - Round 2', 0.75, 1.00, -0.10, 0.0),
('New - Round 2', 1.00, 999999.0, -0.10, 1.0),
('New - Round 3', 0.50, 0.75, -0.20, 0.0),
('New - Round 3', 0.75, 1.00, -0.10, 0.0),
('New - Round 3', 1.00, 1.50, -0.10, 0.5),
('New - Round 3', 1.50, 999999.0, -0.10, 1.0),
('New - Round 4', .50, 1.00, -0.20, 0.0),
('New - Round 4', 1.00, 1.25, -0.10, 0.0),
('New - Round 4', 1.25, 1.50, -0.10, .50),
('New - Round 4', 1.50, 2.00, 0.0, 1.0),
('New - Round 4', 2.00, 999999.0, 0.0, 1.5),
('New - Round 5', .75, 1.00, -0.20, 0.0),
('New - Round 5', 1.00, 1.25, -0.10, 0.0),
('New - Round 5', 1.25, 1.50, -0.10, .50),
('New - Round 5', 1.50, 2.00, 0.0, 1.0),
('New - Round 5', 2.00, 999999.0, 0.0, 1.5)
]
# define column parameters
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_CAMP_MATURITY = 'Campaign Maturity'
RPT_COL_DAILY_BUDGET = 'Daily Budget'
RPT_COL_PUBLISHER_TARGETCPA = 'Publisher Target CPA'
RPT_COL_ROAS = 'CLICKS ROAS'
RPT_COL_LAST_TCPA_BUDGET_UPDATE = 'Date of Last tCPA / Daily Budget Adj.'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_DAILY_BUDGET = 'Daily Budget'
BULK_COL_PUBLISHER_TARGETCPA = 'Publisher Target CPA'
# Assign current date to a parameter
today = datetime.datetime.now()
# create temp column to store new values and default to empty
TMP_BUDGET = RPT_COL_DAILY_BUDGET + '_'
inputDf[TMP_BUDGET] = np.nan
TMP_TARGETCPA = RPT_COL_PUBLISHER_TARGETCPA + '_'
inputDf[TMP_TARGETCPA] = np.nan
null_check = inputDf[RPT_COL_LAST_TCPA_BUDGET_UPDATE].isnull()
# Use check to create DataFrame for campaigns with no Last Updated value and assign current date
blankDf = inputDf.loc[null_check, :].copy()
nonblankDf = inputDf.loc[~null_check, :].copy()
nodateDf = pd.DataFrame()
# loop through each PAUSE criteria and apply
for (new_round, min_roas, max_roas, tcpa_adj, budget_adj) in campaign_adj_criteria:
print(f"Applying adj criteria: new_round={new_round}, min roas={min_roas}, max roas={max_roas}, tcpa adj={tcpa_adj}, budget adj={budget_adj}")
matched_campaigns = (blankDf[RPT_COL_CAMP_MATURITY] == new_round) & \
(blankDf[RPT_COL_ROAS] >= min_roas) & \
(blankDf[RPT_COL_ROAS] < max_roas)
if sum(matched_campaigns) > 0:
print("matched campaigns: ", sum(matched_campaigns))
new_budget = blankDf.loc[matched_campaigns, RPT_COL_DAILY_BUDGET] * (1 + budget_adj)
# print("new_budget", new_budget)
blankDf.loc[ matched_campaigns, TMP_BUDGET ] = new_budget
#Added logic for tCPA adjustment
new_tcpa = blankDf.loc[matched_campaigns, RPT_COL_PUBLISHER_TARGETCPA] * (1 + tcpa_adj)
blankDf.loc[ matched_campaigns, TMP_TARGETCPA ] = new_tcpa
print("adj applied", tableize(blankDf.loc[matched_campaigns]))
# find changed campaigns
changed = (blankDf[TMP_BUDGET].notnull() & (blankDf[RPT_COL_DAILY_BUDGET] != blankDf[TMP_BUDGET])) | \
(blankDf[TMP_TARGETCPA].notnull() & (blankDf[RPT_COL_PUBLISHER_TARGETCPA] != blankDf[TMP_TARGETCPA]))
if sum(changed) > 0:
print("== Campaigns with Changed Adj ==", tableize(blankDf.loc[changed]))
# only select changed rows
cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_BUDGET, TMP_TARGETCPA, RPT_COL_LAST_TCPA_BUDGET_UPDATE]
nodateDf = blankDf.loc[ changed, cols ].copy() \
.rename(columns = { \
TMP_BUDGET: BULK_COL_DAILY_BUDGET, \
TMP_TARGETCPA: BULK_COL_PUBLISHER_TARGETCPA \
})
nodateDf[RPT_COL_LAST_TCPA_BUDGET_UPDATE] = datetime.date.today()
print("nodateDf", tableize(nodateDf))
else:
print("Empty nodateDf")
nodateDf = nodateDf.iloc[0:0]
# Define empty data frame for campaigns with changed RPT_COL_LAST_TCPA_BUDGET_UPDATE values
dateDf = pd.DataFrame()
# create temp columm to store the days since last update
nonblankDf['ConvertedDate'] = pd.to_datetime(nonblankDf[RPT_COL_LAST_TCPA_BUDGET_UPDATE], format="%Y-%m-%d")
nonblankDf['DaysSinceUpdate'] = (today - nonblankDf['ConvertedDate']).dt.days
for (new_round, min_roas, max_roas, tcpa_adj, budget_adj) in campaign_adj_criteria:
print(f"Applying adj criteria: new round={new_round}, min roas={min_roas}, max roas={max_roas}, tcpa adj={tcpa_adj}, budget adj={budget_adj}")
matched_campaigns = (nonblankDf[RPT_COL_CAMP_MATURITY] == new_round) & \
(nonblankDf[RPT_COL_ROAS] >= min_roas) & \
(nonblankDf[RPT_COL_ROAS] < max_roas) & \
(nonblankDf['DaysSinceUpdate'] > 14)
if sum(matched_campaigns) > 0:
print("matched campaigns: ", sum(matched_campaigns))
new_budget = nonblankDf.loc[matched_campaigns, RPT_COL_DAILY_BUDGET] * (1 + budget_adj)
# print("new_budget", new_budget)
nonblankDf.loc[ matched_campaigns, TMP_BUDGET ] = new_budget
#Added logic for tCPA adjustment
new_tcpa = nonblankDf.loc[matched_campaigns, RPT_COL_PUBLISHER_TARGETCPA] * (1 + tcpa_adj)
nonblankDf.loc[ matched_campaigns, TMP_TARGETCPA ] = new_tcpa
print("adj applied", tableize(nonblankDf.loc[matched_campaigns]))
# find changed campaigns
changed = (nonblankDf[TMP_BUDGET].notnull() & (nonblankDf[RPT_COL_DAILY_BUDGET] != nonblankDf[TMP_BUDGET])) | \
(nonblankDf[TMP_TARGETCPA].notnull() & (nonblankDf[RPT_COL_PUBLISHER_TARGETCPA] != nonblankDf[TMP_TARGETCPA]))
if sum(changed) > 0:
print("== Campaigns with Changed Adj ==", tableize(nonblankDf.loc[changed]))
# only select changed rows
cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_BUDGET, TMP_TARGETCPA, RPT_COL_LAST_TCPA_BUDGET_UPDATE]
dateDf = nonblankDf.loc[ changed, cols ].copy() \
.rename(columns = { \
TMP_BUDGET: BULK_COL_DAILY_BUDGET, \
TMP_TARGETCPA: BULK_COL_PUBLISHER_TARGETCPA \
})
dateDf[RPT_COL_LAST_TCPA_BUDGET_UPDATE] = datetime.date.today()
# Merge defined data, exlcuding those that are empty, and print the resulting outputDf
dataframes = [nodateDf, dateDf]
non_empty_dataframes = [df for df in dataframes if not df.empty]
if non_empty_dataframes:
outputDf = pd.concat(non_empty_dataframes)
else:
print("Empty outputDf")
outputDf = outputDf.iloc[0:0]
if (outputDf[BULK_COL_PUBLISHER_TARGETCPA] < 2.50).any():
outputDf.loc[outputDf[BULK_COL_PUBLISHER_TARGETCPA] < 2.50, BULK_COL_PUBLISHER_TARGETCPA] = 2.50
print("outputDf:", tableize(outputDf))
else:
print("outputDf", tableize(outputDf))
Post generated on 2025-03-11 01:25:51 GMT