Purpose

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

To Elaborate

The script is designed to assist in the financial management of advertising campaigns by calculating an adjusted daily budget for each strategy within a campaign. It takes into account the total spend at the strategy level and the remaining budget for each strategy. The script then divides the remaining budget by the number of days left in the current month to determine the adjusted daily budget. This calculation helps ensure that the budget is allocated efficiently across the remaining days, preventing overspending or underspending. The script processes data from a primary data source and outputs the adjusted daily budget for each campaign strategy, ensuring that each campaign is represented once in the final output.

Walking Through the Code

1. Setup and Initialization
   • The script begins by setting up the client timezone and determining the current date, month, and year.
   • It identifies the primary data source and defines the relevant columns 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 days remaining, ensuring there is at least one day to avoid division by zero in subsequent calculations.
3. Calculate Total Strategy Spend
   • It groups the data by strategy and calculates the total spend for each strategy using the publisher cost column.
4. Calculate Remaining Budget and Adjusted Daily Budget
   • The script maps the total strategy spend to each campaign and calculates the remaining budget by subtracting the total spend from the strategy target.
   • It then computes the adjusted daily budget by dividing the remaining budget by the number of remaining days, rounding the result to two decimal places.
5. Prepare Output Data
   • The script selects relevant columns for the output and ensures each campaign is represented once by dropping duplicates based on the campaign column.
   • The final output is printed for debugging purposes.

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