Purpose

The Python script calculates the adjusted daily budget for different strategies based on their remaining budget and the number of days left in the current month.

To Elaborate

The Python script is designed to compute an adjusted daily budget for various 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 script then divides the remaining budget by the number of days left in the month to calculate the adjusted daily budget, ensuring that the division does not result in zero by setting a minimum of one day. The adjusted daily budget is rounded to two decimal places for precision. Finally, the script outputs a table with unique strategies and their corresponding adjusted daily budgets.

Walking Through the Code

1. Data Preparation
   • 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, ensuring that the count 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 Calculation
   • The remaining budget is divided by the number of remaining days to calculate the adjusted daily budget, which is then rounded to two decimal places.
5. Output Preparation
   • The script groups the data by strategy and selects the first row for each strategy to remove duplicates.
   • It outputs the final table containing strategies and their adjusted daily budgets.

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