Script 41: Monthly Budget Cap GSheets

Script 41: Monthly Budget Cap GSheets

Purpose

The 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 Python 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 further spending. 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, which include budget caps for various strategies and campaigns. These caps are now sourced from Google Sheets instead of being hardcoded.
    • It sets up constants for column names used in data processing.
  2. Data Retrieval and Preparation:
    • The script retrieves budget cap data from Google Sheets and converts it into a DataFrame, ensuring the columns are appropriately named.
    • It reduces the input DataFrame to only the necessary columns for processing.
  3. Spend Calculation and Comparison:
    • The script calculates the total spend for each strategy by aggregating the spending data.
    • It merges this aggregated spend data with the budget cap data to identify strategies that have exceeded their budget caps.
  4. Campaign Evaluation:
    • The script evaluates each campaign to determine if it exceeds either the strategy-level or campaign-level budget cap.
    • Campaigns that exceed their caps are marked for pausing.
  5. Status Update and Output Preparation:
    • The script identifies campaigns whose status needs to be changed due to budget cap violations.
    • It prepares an output DataFrame containing only the campaigns with changed statuses, ready for further action or reporting.

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

comments powered by Disqus