Purpose:

The Python script calculates the adjusted daily budget for strategies at the campaign level based on remaining budget and days left in the month.

To Elaborate

The script is designed to calculate an adjusted daily budget for advertising strategies at the campaign level. It takes into account the total spend at the strategy level and the remaining budget for each strategy. By determining the number of days left in the current month, the script computes how much budget should be allocated daily to ensure the strategy’s budget is utilized efficiently by the end of the month. This calculation helps in managing and optimizing the budget allocation for advertising campaigns, ensuring that the strategies do not overspend or underspend within the given timeframe.

Walking Through the Code

1. Setup and Initialization:
   • The script begins by setting up the necessary date and timezone configurations, which are used to determine the current month and year.
   • It identifies the primary data source and defines the relevant columns needed for processing, such as campaign, account, strategy, and publisher cost.
2. Determine Remaining Days in the Month:
   • The script calculates the last day of the current month and determines the number of remaining days. This is crucial for calculating the daily budget allocation.
3. Calculate Total Spend at Strategy Level:
   • It groups the input data by strategy and sums up the publisher costs to get the total spend for each strategy.
4. Map Total Strategy Spend and Calculate Remaining Budget:
   • The total strategy spend is mapped back to the input data, and the remaining budget for each strategy is calculated by subtracting the total spend from the strategy target.
5. Calculate Adjusted Daily Budget:
   • The script computes the adjusted daily budget by dividing the remaining strategy budget by the number of remaining days in the month, rounding to two decimal places.
6. Prepare Output Data:
   • It selects the relevant columns for output, ensuring each campaign is represented once, and prepares the data for further use or analysis.

Vitals

• Script ID : 1467
• Client ID / Customer ID: 1306926629 / 60270083
• Action Type: Bulk Upload
• Item Changed: Campaign
• Output Columns: Account, Campaign, Adj. Daily Budget, Campaign ID, Strategy
• Linked Datasource: M1 Report
• Reference Datasource: None
• Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
• Created by dwaidhas@marinsoftware.com on 2024-10-30 20:45
• Last Updated by dwaidhas@marinsoftware.com on 2024-10-31 18:08

> 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
##
## name: Script: Calc. Adjusted Daily Budget Column for Strategies at Campaign Level
## description:
##  
## 
## 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)
current_month = today.month
current_year = today.year

# 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_STRATEGY_CONSTRAINT_TYPE = 'Strategy Constraint Type'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_ADJ_DAILY_BUDGET = 'Adj. Daily Budget'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_STRATEGY = 'Strategy'
BULK_COL_ADJ_DAILY_BUDGET = 'Adj. Daily Budget'
BULK_COL_CAMPAIGN_ID = 'Campaign ID'
outputDf[BULK_COL_STRATEGY] = "<<YOUR VALUE>>"
outputDf[BULK_COL_ADJ_DAILY_BUDGET] = "<<YOUR VALUE>>"
outputDf[BULK_COL_CAMPAIGN_ID] = "<<YOUR VALUE>>"

# Determine the last day of the month
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)
remaining_days = (last_day_of_month - today.date()).days + 2
remaining_days = max(remaining_days, 1)  # Avoid division by zero

# Step 1: Calculate total spend at the strategy level
strategy_spend = inputDf.groupby(RPT_COL_STRATEGY)[RPT_COL_PUB_COST].sum()

# Map total strategy spend to each campaign and calculate remaining budget
inputDf['Total Strategy Spend'] = inputDf[RPT_COL_STRATEGY].map(strategy_spend)
inputDf['Remaining Strategy Budget'] = inputDf[RPT_COL_STRATEGY_TARGET] - inputDf['Total Strategy Spend']

# Step 2: Calculate the adjusted daily budget at the strategy level
inputDf['Adj. Daily Budget'] = (inputDf['Remaining Strategy Budget'] / remaining_days).round(2)

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

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

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