Script 137: New Campaigns Adj Google tCPA and Budget
Purpose
Python script to adjust the tCPA and daily budget of Google campaigns labeled as ‘New Launch’ through ‘New - Round 5’ based on ROAS over the previous 30 days and campaign maturity dimension.
To Elaborate
The Python script aims to adjust the tCPA (target cost per acquisition) and daily budget of specific Google campaigns based on their ROAS (return on ad spend) over the previous 30 days and their campaign maturity dimension. The adjustments are made according to predefined criteria, which include different rounds, minimum and maximum ROAS values, and corresponding tCPA and budget adjustments. The script also handles campaigns with no last updated value and assigns the current date to them. Additionally, it calculates the days since the last update for non-blank campaigns and applies adjustments based on the same criteria. The script identifies campaigns with changed adjustments and creates a DataFrame to store the changed values. Finally, it merges the dataframes and outputs the resulting DataFrame.
Walking Through the Code
- Define the criteria for campaign tCPA and daily budget adjustments.
- Define column parameters.
- Assign the current date to a parameter.
- Create temporary columns to store new values and default them to empty.
- Check for campaigns with no last updated value and assign the current date.
- Loop through each PAUSE criteria and apply adjustments to campaigns that match the criteria.
- Find campaigns with changed adjustments.
- Create a DataFrame for campaigns with changed adjustments and rename the columns.
- Define an empty DataFrame for campaigns with changed RPT_COL_LAST_TCPA_BUDGET_UPDATE values.
- Create a temporary column to store the days since the last update for non-blank campaigns.
- Loop through each PAUSE criteria and apply adjustments to non-blank campaigns that match the criteria.
- Find non-blank campaigns with changed adjustments.
- Create a DataFrame for non-blank campaigns with changed adjustments and rename the columns.
- Merge the defined dataframes, excluding those that are empty, to create the output DataFrame.
- If the output DataFrame contains a tCPA value less than 2.50, update it to 2.50.
- Print the resulting 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-05-15 07:44:05 GMT