Script 305: Monthly Budget Cap Data Feed

Script 305: Monthly Budget Cap Data Feed

Purpose:

The script enforces monthly budget caps for advertising 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 advertising budgets by automatically pausing campaigns that exceed their monthly budget caps. These caps are defined at both the strategy and campaign levels and are stored in a Google Sheets document. The script calculates the total spend for each strategy and compares it against the predefined budget caps. If a strategy or campaign exceeds its cap, the script updates the campaign’s status to ‘Paused’. This ensures that advertising spend is controlled and does not exceed the allocated budget, thereby optimizing the use of marketing funds.

Walking Through the Code

  1. Configuration and Setup:
    • The script begins by defining configurable parameters, which were initially hardcoded but are now sourced from Google Sheets. These parameters include budget caps for various advertising strategies and campaigns.
  2. Data Preparation:
    • The script retrieves budget cap data from a data source and converts it into a DataFrame. It also reduces the input data to only the necessary columns for processing.
  3. Spend Calculation:
    • It calculates the total spend for each strategy by aggregating the publication costs. This aggregated data is then merged 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:
    • Campaigns identified as over budget have their status updated to ‘Paused’. The script then identifies campaigns whose status has changed and prepares this data for output.
  6. Output:
    • If there are any campaigns with changed statuses, the script outputs this data; otherwise, it indicates that there are no changes.

Vitals

  • Script ID : 305
  • 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: FTP/Email Feed
  • Owner: Michael Huang (mhuang@marinsoftware.com)
  • Created by Michael Huang on 2023-09-18 07:43
  • Last Updated by Michael Huang on 2023-12-06 04:01
> 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

#
# Auto Pause Campaigns based on Monthly Budget Caps uploaded via Data Feed
#
# 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-09-18
#

########### 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 Data Feed
df_budget_caps = dataSourceDict["2"]
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