Purpose:

The Python script calculates the adjusted daily budget for various strategies based on remaining budget and days in the month.

To Elaborate

The script is designed to compute an adjusted daily budget for different strategies by considering the remaining budget and the number of days left in the current month. It processes data from a primary data source, calculates the total spend for each strategy, and determines the remaining budget by subtracting the total spend from the target budget. The adjusted daily budget is then calculated by dividing the remaining budget by the number of days left in the month, ensuring that the calculation accounts for the current day. The script rounds the adjusted daily budget to two decimal places and outputs the first row per strategy, effectively summarizing the adjusted budget for each strategy.

Walking Through the Code

1. Data Initialization:
   • The script begins by defining the primary data source and relevant columns, such as strategy, date, target, and publication cost.
   • It initializes the output DataFrame with a placeholder for the adjusted daily budget.
2. Date Calculations:
   • The script calculates the current date and determines the last day of the current month.
   • It computes the number of remaining days in the month, including today, and ensures that this value is at least one to avoid division by zero.
3. Budget Calculations:
   • The script calculates the total spend for each strategy by grouping the data and summing the publication costs.
   • It computes the remaining budget for each strategy by subtracting the total spend from the target budget.
4. Adjusted Daily Budget:
   • The adjusted daily budget is calculated by dividing the remaining budget by the number of remaining days.
   • The result is rounded to two decimal places for precision.
5. Output Preparation:
   • The script groups the data by strategy and selects the first row per strategy to avoid duplicates.
   • It prepares the output DataFrame with the strategy and adjusted daily budget columns for further use.

Vitals

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

> 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
##
## name: Calc. Adjusted Daily Budget Column for Strategies
## description:
##  
## 
## author: Dana Waidhas 
## created: 2024-10-18
## 
# Timezone definition
CLIENT_TIMEZONE = datetime.timezone(datetime.timedelta(hours=+8))

# Primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_DATE = 'Date'
RPT_COL_TARGET = 'Target'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_CONSTRAINT_TYPE = 'Constraint Type'
RPT_COL_ADJ_DAILY_BUDGET = 'Adj. Daily Budget'


# Output columns and initial values
BULK_COL_STRATEGY = 'Strategy'
BULK_COL_ADJ_DAILY_BUDGET = 'Adj. Daily Budget'
outputDf[BULK_COL_ADJ_DAILY_BUDGET] = "<<YOUR VALUE>>"

# Get today's date and determine the remaining days in the current month
today = datetime.datetime.now(CLIENT_TIMEZONE)
current_month = today.month
current_year = today.year

# 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

# Get the last day of the current month
last_day_of_month = datetime.date(next_month_year, next_month, 1) - datetime.timedelta(days=1)

# Calculate remaining days in the month, including today
remaining_days = (last_day_of_month - today.date()).days + 2  # Add 1 to include today

# If the calculation results in 0, we can set it to 1 (to avoid division by zero)
remaining_days = max(remaining_days, 1)

# Calculate total spend for each strategy
strategy_spend = inputDf.groupby(RPT_COL_STRATEGY)[RPT_COL_PUB_COST].sum()

# Add total spend for each strategy to a new column without modifying the RPT_COL_STRATEGY
inputDf['Total Spend'] = inputDf[RPT_COL_STRATEGY].map(strategy_spend)

# Calculate remaining budget for each strategy
inputDf['Remaining Budget'] = inputDf[RPT_COL_TARGET] - inputDf['Total Spend']

# Calculate adjusted daily budget
inputDf['Adj. Daily Budget'] = inputDf['Remaining Budget'] / remaining_days

# Round the Adjusted Daily Budget to 2 decimal places
inputDf['Adj. Daily Budget'] = inputDf['Adj. Daily Budget'].round(2)

# Now, group by Strategy and select only the first row per strategy (removing duplicates)
outputDf = inputDf[[BULK_COL_STRATEGY, 'Adj. Daily Budget']].drop_duplicates(subset=[BULK_COL_STRATEGY])

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

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