Purpose

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

To Elaborate

The script is designed to evaluate the financial performance of advertising campaigns by comparing the daily budget with the actual spending, referred to as “Pub. Cost £”. It calculates the difference and percentage between these two metrics to determine the pacing and budget status of each campaign. The script categorizes the budget status into different levels such as “OK”, “Close to Budget”, “Capped”, “No Spend”, and “Campaign Paused” based on specific conditions. This analysis helps in identifying campaigns that are on track, nearing their budget limits, or have stopped spending, which is crucial for effective budget management and optimization in advertising.

Walking Through the Code

1. Data Initialization
   • The script begins by defining the primary data source and relevant columns from the input data frame, which includes client, currency, campaign, account, campaign status, and financial metrics like daily budget and public cost.
2. Output Data Frame Setup
   • It initializes the output data frame by copying relevant columns from the input data frame and setting initial values for budget-related calculations, such as budget number, budget percentage, and budget status.
3. Budget Calculations
   • The script calculates the budget number as the difference between actual spending and the daily budget, formatting it to two decimal places.
   • It computes the budget percentage as the ratio of actual spending to the daily budget, expressed as a percentage.
4. Budget Status Determination
   • The budget status is initially set to “OK”. It updates to “Close to Budget” if the spending exceeds 90% of the budget, and to “Capped” if it reaches or exceeds 100%.
   • Additional logic adjusts the status to “No Spend” for active campaigns with zero spending and “Campaign Paused” for paused campaigns with zero spending.

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-02-21 10:25:25 GMT