Script 1469: Calc. Pacing for Strategies at Campaign Level

Purpose:

The Python script calculates the pacing of advertising strategies at the campaign level by comparing month-to-date spending against expected spending based on the total budget and elapsed days in the month.

To Elaborate

The script is designed to assess the pacing of advertising strategies within campaigns by determining how much has been spent so far in the month and comparing it to the expected spend. This is achieved by calculating the month-to-date spend for each strategy and comparing it to the expected spend, which is derived from the total budget allocated for the strategy divided by the number of days in the month, multiplied by the number of days that have elapsed. The script outputs the pacing as a percentage, indicating whether the strategy is on track, underspending, or overspending relative to the budget.

Walking Through the Code

  1. Data Setup:
    • The script begins by setting up the primary data source and defining relevant columns for campaigns, accounts, strategies, and costs.
    • It calculates the current date, the first and last days of the month, and the total number of days in the month.
  2. Spend Calculation:
    • The script calculates the total month-to-date spend for each strategy by grouping the data by strategy and summing the publication costs.
    • This total spend is then mapped back to the original data frame.
  3. Pacing Calculation:
    • If the script is run on the first day of the month, the pacing is set to 0% since no spending has occurred yet.
    • For other days, it calculates the expected spend based on the strategy’s target budget and the number of days elapsed.
    • The pacing strategy is then calculated as the ratio of total strategy spend to expected spend, expressed as a percentage.
  4. Output Preparation:
    • The pacing percentage is formatted and rounded to two decimal places.
    • The script selects relevant columns for output, ensuring each campaign is represented once, and prepares the final output data frame.

Vitals

  • Script ID : 1469
  • Client ID / Customer ID: 1306926629 / 60270083
  • Action Type: Bulk Upload
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Campaign ID, PACING_Strategy, Strategy
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
  • Created by dwaidhas@marinsoftware.com on 2024-10-30 21:45
  • Last Updated by dwaidhas@marinsoftware.com on 2024-11-07 20:06
> 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

##
## name: Calc. Pacing for Strategies at Campaign Level
## description:
##   (Month to date spend)/ ((Total Budget/Days in Episode)*Days Elapsed)
## 
## author: Dana Waidhas 
## created: 2024-10-30
## 

# Set up the date and timezone
CLIENT_TIMEZONE = datetime.timezone(datetime.timedelta(hours=+8))
today = datetime.datetime.now(CLIENT_TIMEZONE).date()  # Get only the current date

# Set up primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_CAMPAIGN_ID = 'Campaign ID'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_STRATEGY_TARGET = 'Strategy Target'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_PACING_STRATEGY = 'PACING_Strategy'

# Output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_PACING_STRATEGY = 'PACING_Strategy'
BULK_COL_CAMPAIGN_ID = 'Campaign ID'
BULK_COL_STRATEGY = 'Strategy'
outputDf[BULK_COL_PACING_STRATEGY] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN_ID] = "<<YOUR VALUE>>"

# Use the actual current date
today = datetime.datetime.now(CLIENT_TIMEZONE).date()  # Get only the current date
current_month = today.month
current_year = today.year

# Calculate the first and last days of the month
first_day_of_month = datetime.date(current_year, current_month, 1)
if current_month == 12:
    next_month = 1
    next_month_year = current_year + 1
else:
    next_month = current_month + 1
    next_month_year = current_year
last_day_of_month = datetime.date(next_month_year, next_month, 1) - datetime.timedelta(days=1)

# Calculate total days in the month and days elapsed
days_in_episode = (last_day_of_month - first_day_of_month).days + 1
days_elapsed = (today - first_day_of_month).days + 0

# Step 1: Calculate total month-to-date spend per strategy
strategy_spend = inputDf.groupby(RPT_COL_STRATEGY)[RPT_COL_PUB_COST].sum()
inputDf['Total Strategy Spend'] = inputDf[RPT_COL_STRATEGY].map(strategy_spend)

# Debug print for Total Strategy Spend
print("Total Strategy Spend by Strategy:\n", inputDf[['Strategy', 'Total Strategy Spend']].drop_duplicates())

# Step 2: Calculate Expected Spend and Pacing Strategy
if today.day == 1:
    # On the 1st of the month, set pacing to 0%
    inputDf['PACING_Strategy'] = 0
else:
    # Calculate Expected Spend and Pacing
    inputDf['Expected Spend'] = (inputDf[RPT_COL_STRATEGY_TARGET] / days_in_episode) * days_elapsed
    inputDf['PACING_Strategy'] = (inputDf['Total Strategy Spend'] / inputDf['Expected Spend']) * 100

# Debug prints for Expected Spend and Pacing Strategy
print("Expected Spend by Strategy:\n", inputDf[['Strategy', 'Expected Spend']].drop_duplicates())
print("Pacing Strategy Calculation:\n", inputDf[['Strategy', 'Total Strategy Spend', 'Expected Spend', 'PACING_Strategy']].drop_duplicates())

# Format pacing as a percentage and round
inputDf['PACING_Strategy'] = inputDf['PACING_Strategy'].round(2).astype(str) + "%"

# Select relevant columns for output and ensure each campaign is represented once
outputDf = inputDf[[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_PACING_STRATEGY, BULK_COL_CAMPAIGN_ID, BULK_COL_STRATEGY]].drop_duplicates(subset=[BULK_COL_CAMPAIGN])

# Print for debugging
print(tableize(outputDf.head()))

Post generated on 2025-03-11 01:25:51 GMT

comments powered by Disqus