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.

To Elaborate

The script is designed to help advertisers monitor the pacing of their advertising strategies within a campaign. It calculates how much has been spent so far in the month and compares it to the expected spend based on the total budget allocated for the campaign and the number of days that have elapsed in the current month. This comparison helps determine whether the campaign is on track, underspending, or overspending. The pacing is expressed as a percentage, which indicates how closely the actual spending aligns with the expected spending. This information is crucial for advertisers to make informed decisions about adjusting their strategies to meet their budget goals by the end of the month.

Walking Through the Code

  1. Setup and Initialization
    • The script begins by setting up the client timezone and determining the current date. It then initializes the primary data source and defines the necessary columns for input and output data.
    • User-changeable parameters include the data source and column names, which should match the structure of the input data.
  2. Date Calculations
    • The script calculates the first and last days of the current month to determine the total number of days in the month and the number of days that have elapsed so far. This information is used to calculate the expected spend.
  3. Calculate Total Month-to-Date Spend
    • It groups the input data by strategy and calculates the total spend for each strategy up to the current date. This is stored in a new column, ‘Total Strategy Spend’.
  4. Calculate Expected Spend and Pacing Strategy
    • If the current day is the first of the month, the pacing is set to 0%. Otherwise, the script calculates the expected spend based on the strategy’s target budget, the total days in the month, and the days elapsed.
    • The pacing strategy is then calculated as a percentage of the total strategy spend over the expected spend.
  5. Formatting and Output
    • The pacing percentage is formatted and rounded to two decimal places. The script then selects relevant columns for output, ensuring each campaign is represented once, and prepares the data for further analysis or reporting.

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 2024-11-27 06:58:46 GMT

comments powered by Disqus