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 level.
To Elaborate
The script is designed to optimize the performance of 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) over the previous 30 days and the campaign’s maturity level as criteria for adjustments. The script applies specific adjustment rules based on predefined criteria, which include the campaign’s maturity round, minimum and maximum ROAS thresholds, and corresponding tCPA and budget adjustments. Campaigns are divided into those with and without a last update date, and adjustments are applied accordingly. The script ensures that any changes are only applied to campaigns that meet the criteria and have not been updated in the last 14 days. It also enforces a minimum tCPA threshold to maintain campaign profitability.
Walking Through the Code
-
Define Adjustment Criteria: The script begins by defining a list of tuples that specify the criteria for adjusting the tCPA and daily budget. Each tuple includes the campaign round, ROAS range, and the corresponding adjustments for tCPA and budget.
-
Initialize Temporary Columns: Temporary columns are created in the input DataFrame to store new budget and tCPA values. Campaigns are split into those with and without a last update date.
-
Apply Adjustments to New Campaigns: The script iterates over the adjustment criteria, applying them to campaigns without a last update date. It calculates new budget and tCPA values for campaigns that match the criteria and stores these values in the temporary columns.
-
Identify and Process Changed Campaigns: Campaigns with changes in budget or tCPA are identified. The script creates a DataFrame of these campaigns, updating the last adjustment date to the current date.
-
Apply Adjustments to Existing Campaigns: The script repeats the adjustment process for campaigns with a last update date, ensuring that only those not updated in the last 14 days are considered.
-
Merge and Finalize Data: The script merges the DataFrames of changed campaigns, ensuring that only non-empty DataFrames are concatenated. It enforces a minimum tCPA threshold of 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 2024-11-27 06:58:46 GMT