Script 599: zzz Campaign Calculations zzz
Purpose:
The Python script calculates and manages pacing and budget allocation for advertising campaigns based on various metrics and goals.
To Elaborate
The script is designed to manage and analyze advertising campaigns by calculating key metrics such as pacing, daily targets, and budget allocations. It processes input data to determine the performance of campaigns against their goals, which can be either monetary spend (MS), cost per thousand impressions (CPM), or cost per view (CPV). The script calculates the total cost, clicks, and impressions for each campaign within a specified date range, and then deduplicates the data to ensure each campaign is represented once. It further computes pacing percentages, categorizes campaigns based on their delivery status, and recommends daily budgets and statuses. This helps in ensuring that campaigns are on track to meet their objectives, providing insights into whether they are under-delivering, over-delivering, or on target.
Walking Through the Code
- Data Preparation
- The script begins by extracting relevant columns from the input data source to create two DataFrames,
df1anddf2, which hold campaign details and performance metrics, respectively. - Dates are converted to a datetime format to facilitate calculations.
- The script begins by extracting relevant columns from the input data source to create two DataFrames,
- Data Aggregation
- For each campaign, the script filters
df2to sum up the ‘Pub. Cost $’, ‘Clicks’, and ‘Impressions’ within the specified date range, updatingdf1with these totals.
- For each campaign, the script filters
- Data Deduplication
- The script removes duplicate entries in
df1based on the ‘Campaign’ column to ensure each campaign is uniquely represented.
- The script removes duplicate entries in
- Metric Calculations
- Functions are defined to calculate various metrics:
calculate_pacingcomputes the pacing percentage based on the campaign goal.calculate_expected_to_dateandcalculate_daily_targetdetermine expected performance metrics.categorize_pacingandcategorize_deliveryclassify campaigns based on their pacing and delivery status.calculate_remaining_budget_or_impressionsandcalculate_daily_allocationcompute remaining budgets and daily allocations.calculate_recommended_daily_budgetandrecommended_statusprovide budget recommendations and status updates.
- Functions are defined to calculate various metrics:
- Output Preparation
- The script updates the output DataFrame with calculated metrics and statuses, preparing it for further analysis or reporting.
Vitals
- Script ID : 599
- Client ID / Customer ID: 1306927177 / 60270139
- Action Type: Bulk Upload (Preview)
- Item Changed: Campaign
- Output Columns: Account, Campaign, Status, Delivery Column, Pacing Threshold, Recommended Daily Budget, Recommended Status, Todays Date, Total Pacing Days
- Linked Datasource: M1 Report
- Reference Datasource: None
- Owner: Jesus Garza (jgarza@marinsoftware.com)
- Created by Jesus Garza on 2023-12-15 23:32
- Last Updated by Jesus Garza on 2024-08-21 17:16
> 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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
##
## name: Campaign Calculations
## description:
##
##
## author:
## created: 2023-12-06
##
today = datetime.datetime.now(CLIENT_TIMEZONE).date()
# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_PACING__START_DATE = 'Pacing - Start Date'
RPT_COL_PACING__END_DATE = 'Pacing - End Date'
RPT_COL_GOAL = 'Goal'
RPT_COL_CPM_IMP_TARGET = 'CPM Imp Target'
RPT_COL_MS_BUDGET_TARGET = 'MS Budget Target'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_IMPR = 'Impr.'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_TODAYS_DATE = 'Todays Date'
RPT_COL_TOTAL_PACING_DAYS = 'Total Pacing Days'
RPT_COL_DAYS_ELAPSED = 'Days Elapsed'
RPT_COL_DAYS_REMAINING = 'Days Remaining'
RPT_COL_DAILY_TARGET_SPEND_OR_IMPRESSION = 'Daily Target (Spend or Impression)'
RPT_COL_EXPECTED_TO_DATE = 'Expected to Date'
RPT_COL_PACING = 'Pacing'
RPT_COL_PACING_THRESHOLD = 'Pacing Threshold'
RPT_COL_DELIVERY_COLUMN = 'Delivery Column'
RPT_COL_TARGET_REMAINING_BUDGET_OR_IMPRESSIONS = 'Target Remaining (Budget or Impressions)'
RPT_COL_DAILY_ALLOCATION_BUDGET_OR_IMPR = 'Daily Allocation (Budget Or Impr.)'
RPT_COL_RECOMMENDED_DAILY_BUDGET = 'Recommended Daily Budget'
RPT_COL_RECOMMENDED_STATUS = 'Recommended Status'
RPT_COL_TARGET_IMPR_PER_SPENDVIEWS = 'Target (Impr/Spend/Views)'
RPT_COL_VIDEO_VIEWS = 'Video Views'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_COL_STATUS = 'Status'
# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_STATUS = 'Status'
BULK_COL_CAMPAIGN_STATUS = 'Campaign Status'
BULK_COL_DAILY_ALLOCATION_BUDGET_OR_IMPR = 'Daily Allocation (Budget Or Impr.)'
BULK_COL_DAILY_TARGET_SPEND_OR_IMPRESSION = 'Daily Target (Spend or Impression)'
BULK_COL_DAYS_ELAPSED = 'Days Elapsed'
BULK_COL_DAYS_REMAINING = 'Days Remaining'
BULK_COL_DELIVERY_COLUMN = 'Delivery Column'
BULK_COL_EXPECTED_TO_DATE = 'Expected to Date'
BULK_COL_PACING = 'Pacing'
BULK_COL_PACING_THRESHOLD = 'Pacing Threshold'
BULK_COL_RECOMMENDED_DAILY_BUDGET = 'Recommended Daily Budget'
BULK_COL_RECOMMENDED_STATUS = 'Recommended Status'
BULK_COL_TARGET_REMAINING_BUDGET_OR_IMPRESSIONS = 'Target Remaining (Budget or Impressions)'
BULK_COL_TODAYS_DATE = 'Todays Date'
BULK_COL_TOTAL_PACING_DAYS = 'Total Pacing Days'
BULK_COL_TARGET_IMPR_PER_SPENDVIEWS = 'Target (Impr/Spend/Views)'
outputDf[BULK_COL_STATUS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN_STATUS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_DAILY_ALLOCATION_BUDGET_OR_IMPR] = "<<YOUR VALUE>>"
outputDf[BULK_COL_DAILY_TARGET_SPEND_OR_IMPRESSION] = "<<YOUR VALUE>>"
outputDf[BULK_COL_DAYS_ELAPSED] = "<<YOUR VALUE>>"
outputDf[BULK_COL_DAYS_REMAINING] = "<<YOUR VALUE>>"
outputDf[BULK_COL_DELIVERY_COLUMN] = "<<YOUR VALUE>>"
outputDf[BULK_COL_EXPECTED_TO_DATE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_PACING] = "<<YOUR VALUE>>"
outputDf[BULK_COL_PACING_THRESHOLD] = "<<YOUR VALUE>>"
outputDf[BULK_COL_RECOMMENDED_DAILY_BUDGET] = "<<YOUR VALUE>>"
outputDf[BULK_COL_RECOMMENDED_STATUS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_TARGET_REMAINING_BUDGET_OR_IMPRESSIONS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_TODAYS_DATE] = "<<YOUR VALUE>>"
outputDf[BULK_COL_TOTAL_PACING_DAYS] = "<<YOUR VALUE>>"
outputDf[BULK_COL_TARGET_IMPR_PER_SPENDVIEWS] = "<<YOUR VALUE>>"
print(inputDf.columns)
# Create DataFrame 1
data_df1 = inputDf[[RPT_COL_DATE,RPT_COL_CAMPAIGN, RPT_COL_ACCOUNT, RPT_COL_CPM_IMP_TARGET, RPT_COL_TARGET_IMPR_PER_SPENDVIEWS, RPT_COL_PACING__START_DATE, RPT_COL_PACING__END_DATE, RPT_COL_GOAL, RPT_COL_CLICKS, RPT_COL_IMPR, RPT_COL_CAMPAIGN_STATUS]].copy()
# Create DataFrame 2
data_df2 = inputDf[[RPT_COL_DATE, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_PUB_COST, RPT_COL_CLICKS, RPT_COL_IMPR, RPT_COL_VIDEO_VIEWS]]
# Create DataFrame 1 and DataFrame 2
df1 = pd.DataFrame(data_df1)
df2 = pd.DataFrame(data_df2)
# Convert 'Pacing - Start Date' and 'Pacing - End Date' columns to datetime
df1[RPT_COL_PACING__START_DATE] = pd.to_datetime(df1[RPT_COL_PACING__START_DATE])
df1[RPT_COL_PACING__END_DATE] = pd.to_datetime(df1[RPT_COL_PACING__END_DATE])
df2[RPT_COL_DATE] = pd.to_datetime(df2[RPT_COL_DATE])
# Populate 'Pub. Cost $', 'Clicks', and 'Impr.' in df1 with the sum of values from df2 for each campaign
for index, row in df1.iterrows():
campaign = row[RPT_COL_CAMPAIGN]
account = row[RPT_COL_ACCOUNT]
start_date = row[RPT_COL_PACING__START_DATE]
end_date = row[RPT_COL_PACING__END_DATE]
# Filter df2 for the specific campaign and within the date range
filtered_data = df2[(df2[RPT_COL_ACCOUNT] == account) & (df2[RPT_COL_CAMPAIGN] == campaign) & (df2[RPT_COL_DATE] >= start_date) & (df2[RPT_COL_DATE] <= end_date)]
# Calculate the sum of 'Pub. Cost $', 'Clicks', and 'Impr.' and assign them to df1
total_cost = filtered_data[RPT_COL_PUB_COST].sum()
total_clicks = filtered_data[RPT_COL_CLICKS].sum()
total_impressions = filtered_data[RPT_COL_IMPR].sum()
total_views = filtered_data[RPT_COL_VIDEO_VIEWS].sum()
df1.loc[index, RPT_COL_PUB_COST] = total_cost
df1.loc[index, RPT_COL_CLICKS] = total_clicks
df1.loc[index, RPT_COL_IMPR] = total_impressions
df1.loc[index, RPT_COL_VIDEO_VIEWS] = total_views
# Drop duplicates based on the 'Campaign' column
df1_deduplicated = df1.drop_duplicates(subset=[RPT_COL_CAMPAIGN]).copy()
print(df1_deduplicated)
##### Pacing column
def calculate_pacing(row):
pub_cost = pd.to_numeric(row[RPT_COL_PUB_COST], errors='coerce')
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
impr = pd.to_numeric(row[RPT_COL_IMPR], errors='coerce')
views = pd.to_numeric(row[RPT_COL_VIDEO_VIEWS], errors='coerce')
expected_to_date = pd.to_numeric(row[RPT_COL_EXPECTED_TO_DATE], errors='coerce')
goal = row[RPT_COL_GOAL]
# Check if expected_to_date is not zero to avoid division by zero
if expected_to_date == 0:
return "" # Return NaN or some other appropriate value indicating the calculation cannot be performed
if goal == 'MS':
pacing = pub_cost / expected_to_date
elif goal == 'CPM':
pacing = impr / expected_to_date
elif goal == 'CPV':
pacing = views / expected_to_date
else:
return ""
return pacing
##### Expected to date column
def calculate_expected_to_date(row):
campaign = row.get(RPT_COL_CAMPAIGN, "")
daily_target = pd.to_numeric(row.get(RPT_COL_DAILY_TARGET_SPEND_OR_IMPRESSION, 0), errors='coerce')
days_elapsed = pd.to_numeric(row.get(RPT_COL_DAYS_ELAPSED, 0), errors='coerce')
if campaign != "" and not pd.isnull(daily_target) and not pd.isnull(days_elapsed):
result = daily_target * days_elapsed
return result
else:
return ""
##### Daily Target (Spend or Impression) column
def calculate_daily_target(row):
goal = row[RPT_COL_GOAL]
try:
# Convert target and total_pacing_days to numeric types (float)
target = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
total_pacing_days = pd.to_numeric(row[RPT_COL_TOTAL_PACING_DAYS], errors='coerce')
# Check if the values are not null and if the goal is one of the specified values
if goal in ['MS', 'CPM', 'CPV'] and not pd.isnull(target) and not pd.isnull(total_pacing_days) and total_pacing_days != 0:
daily_target = target / total_pacing_days
return daily_target
else:
return None # Return None instead of an empty string for consistency with numeric types
except Exception as e:
# Handle any exceptions that may occur
pass # This is just a placeholder; you can also log the exception or handle it appropriately
# Sum_ifs function for various columns
def sum_ifs(data, criteria, column):
# Assuming 'data' is a single row (Series) and 'criteria' is a dictionary with column names as keys and conditions as values
# Check if the row meets all the criteria
meets_criteria = all(criteria.get(col, lambda x: True)(data[col]) for col in criteria)
# If the row meets all criteria, return the value from the specified column, otherwise return 0
return data[column] if meets_criteria else 0
# Pacing Category function
def categorize_pacing(x):
x = pd.to_numeric(x.strip('%'), errors='coerce') # Convert to numeric and remove '%' if present
if pd.isna(x):
return ""
elif x < 0.97:
return "Under Pacing"
elif (x > 1.03) & (x < 1.04):
return "Over Pacing"
elif x > 1.05:
return "Severely Overpacing"
else:
return "On Pace"
# Delivery Category function
def categorize_delivery(row):
pub_cost = pd.to_numeric(row[RPT_COL_PUB_COST], errors='coerce')
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
impr = pd.to_numeric(row[RPT_COL_IMPR], errors='coerce')
days_remaining = pd.to_numeric(row[RPT_COL_DAYS_REMAINING], errors='coerce')
if days_remaining > 1 and not pd.isnull(target_impr_per_spendviews):
if row[RPT_COL_GOAL] == "CPM":
if not pd.isnull(impr):
ratio = impr / target_impr_per_spendviews
if ratio < 0.97:
return "Underdelivery"
elif ratio > 1.15 or (row[RPT_COL_IMPR] - row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS] > 50000):
return "Overdelivery"
elif 0.97 <= ratio <= 1.15:
return "On Target"
elif row[RPT_COL_GOAL] == "MS":
if not pd.isnull(pub_cost):
ratio = pub_cost / target_impr_per_spendviews
if ratio < 1.00:
return "Underdelivery"
elif ratio > 1.03 or (row[RPT_COL_PUB_COST] - row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS] > 500):
return "Overdelivery"
elif 1.00 <= ratio <= 1.03:
return "On Target"
elif row[RPT_COL_GOAL] == "CPV":
video_views = pd.to_numeric(row[RPT_COL_VIDEO_VIEWS], errors='coerce')
if not pd.isnull(video_views):
ratio = video_views / target_impr_per_spendviews
if ratio < 1.00:
return "Underdelivery"
elif ratio > 1.03 or (row[RPT_COL_VIDEO_VIEWS] - row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS] > 5000):
return "Overdelivery"
elif 1.00 <= ratio <= 1.03:
return "On Target"
return ""
# Recommended Status function
def recommended_status(row):
pub_cost = pd.to_numeric(row[RPT_COL_PUB_COST], errors='coerce')
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
impr = pd.to_numeric(row[RPT_COL_IMPR], errors='coerce')
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
if row[RPT_COL_CAMPAIGN_STATUS] == "Paused":
return "Paused"
elif row[RPT_COL_GOAL] == "MS" and not pd.isnull(pub_cost) and not pd.isnull(target_impr_per_spendviews) and pub_cost >= target_impr_per_spendviews:
return "Paused"
elif row[RPT_COL_GOAL] == "CPM" and not pd.isnull(impr) and not pd.isnull(target_impr_per_spendviews) and impr >= target_impr_per_spendviews:
return "Paused"
elif row[RPT_COL_CAMPAIGN_STATUS] != "":
return "Active"
else:
return ""
# Calculate Daily Allocation function
def calculate_daily_allocation(row):
if row[RPT_COL_GOAL] == 'MS':
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
pub_cost = pd.to_numeric(row[RPT_COL_PUB_COST], errors='coerce')
days_remaining = pd.to_numeric(row[RPT_COL_DAYS_REMAINING], errors='coerce')
return (target_impr_per_spendviews - pub_cost) / days_remaining if days_remaining > 0 else ''
elif row[RPT_COL_GOAL] == 'CPM':
target_impr_per_spendview = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
impr = pd.to_numeric(row[RPT_COL_IMPR], errors='coerce')
days_remaining = pd.to_numeric(row[RPT_COL_DAYS_REMAINING], errors='coerce')
return (target_impr_per_spendview - impr) / days_remaining if days_remaining > 0 else ''
else:
return ''
# Calculate Recommended Daily Budget function
def calculate_recommended_daily_budget(row):
if row[RPT_COL_GOAL] == 'MS':
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
pub_cost = pd.to_numeric(row[RPT_COL_PUB_COST], errors='coerce')
days_remaining = pd.to_numeric(row[RPT_COL_DAYS_REMAINING], errors='coerce')
if pd.notnull(target_impr_per_spendviews) and pd.notnull(pub_cost) and pd.notnull(days_remaining) and days_remaining > 0:
return (target_impr_per_spendviews - pub_cost) / days_remaining
else:
return None
elif row[RPT_COL_GOAL] == 'CPM':
pub_cost = pd.to_numeric(row[RPT_COL_PUB_COST], errors='coerce')
impr = pd.to_numeric(row[RPT_COL_IMPR], errors='coerce')
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
if pd.notnull(pub_cost) and pd.notnull(impr) and pd.notnull(target_impr_per_spendviews) and impr > 0:
return (pub_cost / impr) * target_impr_per_spendviews
else:
return None
else:
return None
# Calculate_ratio function
def calculate_ratio(row):
try:
if row[RPT_COL_GOAL] == 'MS':
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
total_pacing_days = pd.to_numeric(row[RPT_COL_TOTAL_PACING_DAYS], errors='coerce')
if pd.notnull(target_impr_per_spendviews) and pd.notnull(total_pacing_days) and days_remaining > 0:
return target_impr_per_spendviews / total_pacing_days
else:
return None
elif row[RPT_COL_GOAL] == 'CPM':
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
total_pacing_days = pd.to_numeric(row[RPT_COL_TOTAL_PACING_DAYS], errors='coerce')
if pd.notnull(target_impr_per_spendviews) and pd.notnull(total_pacing_days) and days_remaining > 0:
return target_impr_per_spendviews / total_pacing_days
else:
return None
except Exception as e:
# Handle any exceptions that may occur
return None
# Apply functions to inputDf
#inputDf[RPT_COL_PUB_COST] = inputDf.apply(lambda row: sum_ifs(row, {RPT_COL_ACCOUNT: lambda x: x >= E2 and x <= F2, RPT_COL_CAMPAIGN: lambda #x: x == A2}, RPT_COL_PUB_COST), axis=1)
#inputDf[RPT_COL_IMPR] = inputDf.apply(lambda row: sum_ifs(row, {RPT_COL_ACCOUNT: lambda x: x >= E2 and x <= F2, RPT_COL_CAMPAIGN: lambda x: x #== A2}, RPT_COL_IMPR), axis=1)
#inputDf[RPT_COL_COST] = inputDf.apply(lambda row: sum_ifs(row, {RPT_COL_ACCOUNT: lambda x: x >= E2 and x <= F2, RPT_COL_CAMPAIGN: lambda x: x #== A2}, RPT_COL_COST), axis=1)
#inputDf[RPT_COL_VIEWS] = inputDf.apply(lambda row: sum_ifs(row, {RPT_COL_B: lambda x: x >= E2 and x <= F2, RPT_COL_A: lambda x: x == A2}, #RPT_COL_VIEWS), axis=1)
# Convert 'Pacing - Start Date' and 'Pacing - End Date' columns to datetime in inputDf
df1_deduplicated[RPT_COL_PACING__START_DATE] = pd.to_datetime(df1_deduplicated[RPT_COL_PACING__START_DATE])
df1_deduplicated[RPT_COL_PACING__END_DATE] = pd.to_datetime(df1_deduplicated[RPT_COL_PACING__END_DATE])
# Set 'Todays Date' to the current date in the correct timezone
today = datetime.datetime.now(CLIENT_TIMEZONE).date()
df1_deduplicated[RPT_COL_TODAYS_DATE] = pd.to_datetime(today)
df1_deduplicated[RPT_COL_TOTAL_PACING_DAYS] = (df1_deduplicated[RPT_COL_PACING__END_DATE] - df1_deduplicated[RPT_COL_PACING__START_DATE]).dt.days
df1_deduplicated[RPT_COL_TOTAL_PACING_DAYS] = df1_deduplicated[RPT_COL_TOTAL_PACING_DAYS] + df1_deduplicated[RPT_COL_CAMPAIGN].apply(lambda x: 1 if x != '' else 0)
# Calculate 'Days Elapsed' first
df1_deduplicated[RPT_COL_DAYS_ELAPSED] = (df1_deduplicated[RPT_COL_TODAYS_DATE] - df1_deduplicated[RPT_COL_PACING__START_DATE]).dt.days
# Then calculate 'Days Remaining'
df1_deduplicated[RPT_COL_DAYS_REMAINING] = df1_deduplicated.apply(
lambda row: row[RPT_COL_TOTAL_PACING_DAYS] - row[RPT_COL_DAYS_ELAPSED] if row[RPT_COL_CAMPAIGN] != '' else '',
axis=1
)
#df1_deduplicated[RPT_COL_EXPECTED_TO_DATE] = df1_deduplicated.apply(calculate_expected_to_date, axis=1)
df1_deduplicated[RPT_COL_DAILY_TARGET_SPEND_OR_IMPRESSION] = df1_deduplicated.apply(calculate_daily_target, axis=1)
df1_deduplicated[RPT_COL_DAILY_TARGET_SPEND_OR_IMPRESSION] = df1_deduplicated[RPT_COL_DAILY_TARGET_SPEND_OR_IMPRESSION].round()
df1_deduplicated[RPT_COL_DAYS_ELAPSED] = (df1_deduplicated[RPT_COL_TODAYS_DATE] - df1_deduplicated[RPT_COL_PACING__START_DATE]).dt.days
#df1_deduplicated[RPT_COL_DAILY_TARGET_SPEND_OR_IMPRESSION] = df1_deduplicated.apply(calculate_ratio, axis=1)
df1_deduplicated[RPT_COL_EXPECTED_TO_DATE] = df1_deduplicated.apply(
lambda row: float(row[RPT_COL_DAILY_TARGET_SPEND_OR_IMPRESSION]) * row[RPT_COL_DAYS_ELAPSED] if row[RPT_COL_CAMPAIGN] != '' and row[RPT_COL_DAILY_TARGET_SPEND_OR_IMPRESSION] not in ('', None) else '',
axis=1
)
# Calculate pacing as a percentage and convert to numeric type
df1_deduplicated[RPT_COL_PACING] = df1_deduplicated.apply(calculate_pacing, axis=1)
df1_deduplicated[RPT_COL_PACING] = pd.to_numeric(df1_deduplicated[RPT_COL_PACING], errors='coerce') * 100.0
df1_deduplicated[RPT_COL_PACING] = df1_deduplicated[RPT_COL_PACING].round().astype(str) + '%'
#df1_deduplicated[RPT_COL_PACING] = df1_deduplicated.apply(
#lambda row: (row[RPT_COL_PUB_COST] / float(row[RPT_COL_EXPECTED_TO_DATE])) * 100
#if row[RPT_COL_GOAL] in ('MS', 'CPM') and pd.notnull(row[RPT_COL_EXPECTED_TO_DATE]) and row[RPT_COL_EXPECTED_TO_DATE] not in (0, '')
#else np.nan,
#axis=1
#)
df1_deduplicated[RPT_COL_PACING_THRESHOLD] = df1_deduplicated[RPT_COL_PACING].apply(categorize_pacing)
df1_deduplicated[RPT_COL_DELIVERY_COLUMN] = df1_deduplicated.apply(categorize_delivery, axis=1)
def calculate_remaining_budget_or_impressions(row):
if row[RPT_COL_GOAL] == 'CPM':
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
impr = pd.to_numeric(row[RPT_COL_IMPR], errors='coerce')
return target_impr_per_spendviews - impr if pd.notnull(target_impr_per_spendviews) and pd.notnull(impr) else None
elif row[RPT_COL_GOAL] == 'MS':
target_impr_per_spendviews = pd.to_numeric(row[RPT_COL_TARGET_IMPR_PER_SPENDVIEWS], errors='coerce')
pub_cost = pd.to_numeric(row[RPT_COL_PUB_COST], errors='coerce')
return target_impr_per_spendviews - pub_cost if pd.notnull(target_impr_per_spendviews) and pd.notnull(pub_cost) else None
else:
return None
df1_deduplicated[RPT_COL_TARGET_REMAINING_BUDGET_OR_IMPRESSIONS] = df1_deduplicated.apply(calculate_remaining_budget_or_impressions, axis=1)
df1_deduplicated[RPT_COL_TARGET_REMAINING_BUDGET_OR_IMPRESSIONS] = pd.to_numeric(df1_deduplicated[RPT_COL_TARGET_REMAINING_BUDGET_OR_IMPRESSIONS], errors='coerce').fillna(0).astype(int)
df1_deduplicated[RPT_COL_DAILY_ALLOCATION_BUDGET_OR_IMPR] = df1_deduplicated.apply(calculate_daily_allocation, axis=1)
df1_deduplicated[RPT_COL_DAILY_ALLOCATION_BUDGET_OR_IMPR] = pd.to_numeric(df1_deduplicated[RPT_COL_DAILY_ALLOCATION_BUDGET_OR_IMPR], errors='coerce').fillna(0).round()
df1_deduplicated[RPT_COL_RECOMMENDED_DAILY_BUDGET] = df1_deduplicated.apply(calculate_recommended_daily_budget, axis=1)
df1_deduplicated[RPT_COL_RECOMMENDED_DAILY_BUDGET] = pd.to_numeric(df1_deduplicated[RPT_COL_RECOMMENDED_DAILY_BUDGET], errors='coerce').fillna(0).astype(int)
df1_deduplicated[RPT_COL_RECOMMENDED_STATUS] = df1_deduplicated.apply(recommended_status, axis=1)
df1_deduplicated [BULK_COL_STATUS] = df1_deduplicated [RPT_COL_CAMPAIGN_STATUS]
outputDf = df1_deduplicated
Post generated on 2025-03-11 01:25:51 GMT