Purpose:

The script compares daily budget allocations to actual spending to assess pacing and budget status for advertising campaigns.

To Elaborate

The Python script is designed to analyze and compare the daily budget allocations against the actual spending for advertising campaigns. It evaluates the pacing and budget status by calculating the difference and percentage between the budgeted and actual spend. The script assigns a status to each campaign based on how close the spending is to the budget, whether it is within budget, close to budget, or over budget. Additionally, it handles scenarios where there is no spending, differentiating between active and paused campaigns. This analysis helps in understanding the financial performance and efficiency of advertising campaigns, ensuring that they are on track with their allocated budgets.

Walking Through the Code

1. Data Initialization
   • The script begins by defining the primary data source and relevant columns from the input data frame, such as client, currency, campaign, account, and campaign status.
   • It sets up the output data frame with initial values and column names for client, account, campaign, and budget-related metrics.
2. Budget vs. Spend Calculation
   • The script calculates the numerical difference between the actual spend (Pub. Cost £) and the Daily Budget, rounding the result to two decimal places.
   • It computes the percentage of the budget spent by dividing the actual spend by the daily budget, converting it to a percentage format.
3. Budget Status Assignment
   • Initially, all campaigns are marked as “OK”.
   • Campaigns with spending over 90% of the budget are labeled “Close to Budget”, and those at or above 100% are marked as “Capped”.
   • For campaigns with zero spend, the status is set to “No Spend” if active, or “Campaign Paused” if the campaign is paused.
4. User Changeable Parameters
   • Users can modify the thresholds for budget status, such as the percentage values that determine “Close to Budget” or “Capped” statuses.
   • The script allows for adjustments in the logic for handling zero spend scenarios based on campaign status.

Vitals

• Script ID : 1625
• Client ID / Customer ID: 1306912137 / 69058
• Action Type: Bulk Upload
• Item Changed: Campaign
• Output Columns: Account, Campaign, Spend vs. Budget Chg., Spend vs. Budget Chg.Pt, Spend vs. Budget Status
• Linked Datasource: M1 Report
• Reference Datasource: None
• Owner: Jeremy Brown (jbrown@marinsoftware.com)
• Created by Jeremy Brown on 2025-01-16 17:57
• Last Updated by Jeremy Brown on 2025-01-16 18:49

> 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
##
## name: SCRIPT: Spend vs Budget Analysis
## description:
## Compare 'Daily Budget' to 'Pub. Cost £' to determine pacing and budget status.
## 
## author: Jeremy Brown
## created: 2025-01-16
##

today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# Primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CLIENT = 'Client'
RPT_COL_CURRENCY = 'Currency'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_IMPR = 'Pub. Cost £'
RPT_COL_BUDGETNUMBER = 'Spend vs. Budget Chg.'
RPT_COL_BUDGETPERCENT = 'Spend vs. Budget Chg.Pt'
RPT_COL_BUDGETSTATUS = 'Spend vs. Budget Status'
RPT_COL_DAILY_BUDGET = 'Daily Budget'

# Define output column names
BULK_COL_CLIENT = 'Client'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'

# Output columns and initial values
outputDf[BULK_COL_CLIENT] = inputDf[RPT_COL_CLIENT]
outputDf[BULK_COL_ACCOUNT] = inputDf[RPT_COL_ACCOUNT]
outputDf[BULK_COL_CAMPAIGN] = inputDf[RPT_COL_CAMPAIGN]
outputDf[RPT_COL_BUDGETNUMBER] = "<>"
outputDf[RPT_COL_BUDGETPERCENT] = "<>"
outputDf[RPT_COL_BUDGETSTATUS] = "<>"

# Process logic
outputDf[RPT_COL_BUDGETNUMBER] = (inputDf[RPT_COL_IMPR] - inputDf[RPT_COL_DAILY_BUDGET]).round(2).apply(lambda x: f"{x:,.2f}")

outputDf[RPT_COL_BUDGETPERCENT] = ((inputDf[RPT_COL_IMPR] / inputDf[RPT_COL_DAILY_BUDGET]).fillna(0) * 100).round(2).astype(str) + "%"

outputDf[RPT_COL_BUDGETSTATUS] = "OK"
outputDf.loc[outputDf[RPT_COL_BUDGETPERCENT].str.rstrip('%').astype(float) > 90, RPT_COL_BUDGETSTATUS] = "Close to Budget"
outputDf.loc[outputDf[RPT_COL_BUDGETPERCENT].str.rstrip('%').astype(float) >= 100, RPT_COL_BUDGETSTATUS] = "Capped"

# Additional logic for zero spend
outputDf.loc[(inputDf[RPT_COL_IMPR] == 0) & (inputDf[RPT_COL_CAMPAIGN_STATUS] == "Active"), RPT_COL_BUDGETSTATUS] = "No Spend"
outputDf.loc[(inputDf[RPT_COL_IMPR] == 0) & (inputDf[RPT_COL_CAMPAIGN_STATUS] == "Paused"), RPT_COL_BUDGETSTATUS] = "Campaign Paused"

# Debugging output
print("Data after processing:")
print(outputDf)

# Uncomment below to trigger the process
# outputDf = process(inputDf)

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