Script 41: Monthly Budget Cap GSheets

Script 41: Monthly Budget Cap GSheets

Purpose:

The Python script enforces monthly budget caps for strategies and campaigns by pausing those that exceed their allocated budgets, using data from Google Sheets.

To Elaborate

The script is designed to manage and enforce monthly budget caps for advertising strategies and campaigns. It retrieves budget cap data from Google Sheets and compares it against the actual spending data for each strategy and campaign. If a strategy or campaign exceeds its budget cap, the script automatically pauses it to prevent overspending. This ensures that the advertising budget is adhered to and helps in maintaining financial discipline across different advertising channels. The script also identifies which campaigns have had their status changed due to budget cap violations and prepares this information for further processing or reporting.

Walking Through the Code

  1. Configuration and Setup:
    • The script begins by defining configurable parameters, such as budget caps for different strategies and campaigns, which are now sourced from Google Sheets.
    • Constants for column names and status values are defined for use throughout the script.
  2. Data Preparation:
    • Budget cap data is retrieved from Google Sheets and converted into a DataFrame.
    • The input DataFrame is reduced to only the necessary columns for processing.
  3. Spend Calculation:
    • The script calculates the total spend for each strategy by aggregating the publication costs.
  4. Budget Cap Comparison:
    • It merges the calculated spend with the budget cap data to identify strategies that exceed their budget caps.
    • Similarly, it checks each campaign against its respective campaign cap.
  5. Campaign Status Update:
    • Campaigns that exceed either the strategy or campaign cap are marked to be paused.
    • The script identifies campaigns with changed statuses and prepares an output DataFrame with these changes.
  6. Output Preparation:
    • If there are any campaigns with changed statuses, they are printed and prepared for further action.
    • If no changes are detected, an empty DataFrame is returned.

Vitals

  • Script ID : 41
  • Client ID / Customer ID: 309909744 / 14196
  • Action Type: Bulk Upload (Preview)
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Status
  • Linked Datasource: M1 Report
  • Reference Datasource: Google Sheets
  • Owner: Michael Huang (mhuang@marinsoftware.com)
  • Created by Michael Huang on 2023-03-28 09:22
  • Last Updated by kkamp@marinsoftware.com on 2024-06-25 19:14
> 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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108

#
# Auto Pause Campaigns based on Monthly Budget Cap
#
# TODO: Reactivate Campaigns paused by script when new month starts (cost < cap) 
#  * tag SCRIPTS_PAUSED with date
#  * remove tag on new month
#  * reactivate if below cap and has tag
#
# Author: Michael S. Huang
# Created: 2023-05-17
# Updated: 2023-09-04
#

########### Configurable Params - START ##########

# Update: Now using GSheets to provide caps
# # define budget cap rules
# strategy_campaign_budget_cap = {
#     # format:
#     #  'strategy name': (overall-strategy cap, campaign-in-strategy cap)
#     'Unassigned': (100000, 7000),
#     'Criteo Retail': (25000, 2000),
#     'Amazon: Sponsored Products': (5000, 1000),
#     'TikTok - CPA': (25000, 2000),
#     'Cross Channel Budget Pacing': (10000, 1000),
# }

########### Configurable Params - END ###########

RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_PUB_COST = 'Pub. Cost $'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_STATUS = 'Status'

VAL_STATUS_ACTIVE = 'Active'
VAL_STATUS_PAUSED = 'Paused'
COL_STRATEGY_CAP = 'strategy_cap'
COL_STRATEGY_CAMPAIGN_CAP = 'campaign_cap'

outputDf[BULK_COL_STATUS] = "<<YOUR VALUE>>"

# # convert budget cap into dataframe
# df_budget_caps = pd.DataFrame.from_dict(strategy_campaign_budget_cap, orient='index') \
#                  .reset_index()

# get budget cap from GSheets
df_budget_caps = dataSourceDict["2_1"]
df_budget_caps.columns = [RPT_COL_STRATEGY, COL_STRATEGY_CAP, COL_STRATEGY_CAMPAIGN_CAP]
print("budget caps", tableize(df_budget_caps))

# reduce inputDf to needed columns
cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_STRATEGY, RPT_COL_CAMPAIGN_STATUS, RPT_COL_PUB_COST]
inputDf = inputDf[cols].copy()

# create temp column to store new status and default to empty
TMP_STATUS = RPT_COL_CAMPAIGN_STATUS + '_'
inputDf[TMP_STATUS] = np.nan

# calculate total spend for each Strategy
df_strategy_agg = inputDf.groupby(RPT_COL_STRATEGY).agg({RPT_COL_PUB_COST:'sum'})
print("== Strategy-level Spend ==", df_strategy_agg.to_string())

# put above together to determine Strategies over budget cap
df_strategy_agg_with_cap = df_strategy_agg.merge(df_budget_caps, on=[RPT_COL_STRATEGY]).reset_index()
print("strategy agg with cap", tableize(df_strategy_agg_with_cap))

strategies_over_cap = df_strategy_agg_with_cap.loc[ \
                        df_strategy_agg_with_cap[RPT_COL_PUB_COST] >= df_strategy_agg_with_cap[COL_STRATEGY_CAP], \
                        RPT_COL_STRATEGY \
                      ]

# similarly, combine budget cap with campaigns to determine campaigns over cap
df_campaigns_with_cap = inputDf.merge(df_budget_caps, on=[RPT_COL_STRATEGY], how='left').reset_index()
print("campaigns with cap", tableize(df_campaigns_with_cap))

# determine which campaigns are over Strategy Cap or Campaign Cap
campaigns_over_cap = (df_campaigns_with_cap[RPT_COL_STRATEGY].isin(strategies_over_cap)) | \
                     (df_campaigns_with_cap[RPT_COL_PUB_COST] >= df_campaigns_with_cap[COL_STRATEGY_CAMPAIGN_CAP])
                      
    
# Pause all camapigns over Strategy Cap or Campaign Cap
inputDf.loc[ campaigns_over_cap, TMP_STATUS ] = VAL_STATUS_PAUSED


# find changed campaigns
changed = inputDf[TMP_STATUS].notnull() & (inputDf[RPT_COL_CAMPAIGN_STATUS] != inputDf[TMP_STATUS])

if sum(changed) > 0:

    print("== Campaigns with Changed Status ==", tableize(inputDf.loc[changed]))

    # only select changed rows
    cols = [RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, TMP_STATUS]
    outputDf = inputDf.loc[ changed, cols ].copy() \
                    .rename(columns = { \
                        TMP_STATUS: BULK_COL_STATUS \
                    })
    print("outputDf", tableize(outputDf))
    
else:
    
  print("Empty outputDf")
  outputDf = outputDf.iloc[0:0]

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

comments powered by Disqus