Script 511: Auto Pause & Re enable

Purpose

Python script to pause and re-enable campaigns based on budget allocation.

To Elaborate

This Python script solves the problem of automatically pausing and re-enabling campaigns based on budget allocation. The script takes into account the monthly budget, spend, and recommended pacing status to determine which campaigns should be paused or re-enabled. It also considers the franchise and location of each campaign to calculate the spend. The script then updates the campaign status and pause date accordingly.

Walking Through the Code

  1. The script starts by defining a configurable parameter, BUDGET_CAP_SAFETY_MARGIN, which determines how close the monthly spend can get to the monthly budget before campaigns are paused.
  2. The script then initializes the current date and time.
  3. It defines the columns used in the input and output dataframes.
  4. The script fills any NaN values in the input dataframe with empty strings to avoid comparison errors.
  5. It creates a copy of the original input dataframe for later comparison.
  6. The script calculates the total spend for each franchise and location combination and adds it as a new column, COL_MTD_FRANCHISE_LOCATION_SPEND, in the input dataframe.
  7. It identifies campaigns that have spent over the budget by a margin and updates the recommended pacing status to “Paused” for those campaigns.
  8. The script also identifies campaigns that have spent under the budget by a margin and have a non-empty SBA Pause Date. It updates the recommended pacing status to “Active” for those campaigns.
  9. It then checks if the campaign should be paused or resumed based on the SBA Traffic column, the recommended pacing status, and the current campaign status.
  10. The script updates the campaign status and pause date accordingly.
  11. Finally, it selects the changed rows from the input dataframe and renames the campaign status column to the bulk column header.
  12. The script prints the shape of the output dataframe.

Vitals

  • Script ID : 511
  • Client ID / Customer ID: 1306926711 / 60270099
  • Action Type: Bulk Upload (Preview)
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Status, SBA Pause Date, Pacing- Recommended Status
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: ascott@marinsoftware.com (ascott@marinsoftware.com)
  • Created by ascott@marinsoftware.com on 2023-11-07 20:31
  • Last Updated by ascott@marinsoftware.com 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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134

##
## name: Epicor Budget Cap
## description:
##  Pause campaigns when Franchise & Location spend reaches  Budget
## 
## author: Michael S. Huang
## created: 2023-10-31
## 

##### Configurable Param #####
# Define how close MTD spend can get to Monthly Budget before being Paused
#  - compensates for lag in system
#  - compendates for non-linearity in intraday spend
BUDGET_CAP_SAFETY_MARGIN = 0.02 # set to 2%
##############################


today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_PUBLISHER_NAME = 'Publisher Name'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_SBA_TRAFFIC = 'SBA Traffic'
RPT_COL_SBA_PAUSE_DATE = 'SBA Pause Date'
RPT_COL_PACING_RECOMMENDED_STATUS = 'Pacing- Recommended Status'
RPT_COL_PACING_RECOMMENDED_DAILY_BUDGET = 'Pacing- Recommended Daily Budget'
RPT_COL_DAILY_BUDGET = 'Daily Budget'
RPT_COL_PACING_BUDGET_PACING = 'Pacing- Budget Pacing'
RPT_COL_PACING_ALLOCATION = 'Pacing - Allocation'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_FRANCHISE = 'Franchise'
RPT_COL_LOCATION = 'Location'
RPT_COL_BUDGET = 'Budget'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_STATUS = 'Status'
BULK_COL_SBA_PAUSE_DATE = 'SBA Pause Date'
BULK_COL_PACING_RECOMMENDED_STATUS = 'Pacing- Recommended Status'

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


# user code start here
print("inputDf shape", inputDf.shape)
print("inputDf dtypes", inputDf.dtypes)

# define some intermediate columns
COL_MTD_FRANCHISE_LOCATION_SPEND = 'mtd_franchise_location_spend'

# define Status values
VAL_STATUS_ACTIVE = 'Active'
VAL_STATUS_PAUSED = 'Paused'
VAL_BLANK = ''

# HACK: replace nan with empty strings so comparison doesn't fail
inputDf.fillna(VAL_BLANK, inplace=True)
print("inputDf describe", inputDf.describe())

# Keep copy of original inputDf for comparison later
originalDf = inputDf.copy()

# MTD_FRANCHISE_LOCATION_SPEND
inputDf[COL_MTD_FRANCHISE_LOCATION_SPEND] = inputDf.groupby([RPT_COL_FRANCHISE, RPT_COL_LOCATION])[RPT_COL_PUB_COST].transform('sum')

# Recommend to Pause camapigns with MTD FRANCHISE LOCATION spend over Budget (by a margin)
over_spent_campaigns = inputDf[COL_MTD_FRANCHISE_LOCATION_SPEND] >= inputDf[RPT_COL_BUDGET] * (1 - BUDGET_CAP_SAFETY_MARGIN)

inputDf.loc[ over_spent_campaigns, \
             RPT_COL_PACING_RECOMMENDED_STATUS \
           ] = VAL_STATUS_PAUSED
 
# Recommend to reactivate campaigns with MTD Epicor Spend under Monthly Epicor Budget (by a margin)
# but limited to campaigns with SBA Paused populated
under_spent_campaigns = inputDf[COL_MTD_FRANCHISE_LOCATION_SPEND] < inputDf[RPT_COL_BUDGET] * (1 - BUDGET_CAP_SAFETY_MARGIN)
sba_paused_campaigns = inputDf[RPT_COL_SBA_PAUSE_DATE].str.len() > 0

inputDf.loc[ under_spent_campaigns & sba_paused_campaigns, \
             RPT_COL_PACING_RECOMMENDED_STATUS  \
           ] = VAL_STATUS_ACTIVE

## Actually taffic PAUSE

should_traffic = inputDf[RPT_COL_SBA_TRAFFIC].astype(str).str.lower() == 'traffic'
# TODO: comment out below TEST line for PROD
# should_traffic = inputDf[RPT_COL_PUB_COST] > 1.0

should_traffic_pause = should_traffic & \
                       (inputDf[RPT_COL_PACING_RECOMMENDED_STATUS] == VAL_STATUS_PAUSED) & \
                       (inputDf[RPT_COL_PACING_RECOMMENDED_STATUS] != inputDf[RPT_COL_CAMPAIGN_STATUS])

inputDf.loc[should_traffic_pause, RPT_COL_CAMPAIGN_STATUS] = inputDf.loc[should_traffic_pause, RPT_COL_PACING_RECOMMENDED_STATUS]
inputDf.loc[should_traffic_pause, RPT_COL_SBA_PAUSE_DATE] = today

## Actually taffic RESUME

should_traffic_resume = should_traffic & \
                       (inputDf[RPT_COL_PACING_RECOMMENDED_STATUS] == VAL_STATUS_ACTIVE) & \
                       (inputDf[RPT_COL_PACING_RECOMMENDED_STATUS] != inputDf[RPT_COL_CAMPAIGN_STATUS]) & \
                       sba_paused_campaigns

inputDf.loc[should_traffic_resume, RPT_COL_CAMPAIGN_STATUS] = inputDf.loc[should_traffic_resume, RPT_COL_PACING_RECOMMENDED_STATUS]
inputDf.loc[should_traffic_resume, RPT_COL_SBA_PAUSE_DATE] = VAL_BLANK

# only include changed rows in bulk file
(outputDf, debugDf) = select_changed(inputDf, \
                                    originalDf, \
                                    diff_cols = [ \
                                        RPT_COL_CAMPAIGN_STATUS, \
                                        RPT_COL_PACING_RECOMMENDED_STATUS, \
                                        RPT_COL_SBA_PAUSE_DATE, \
                                    ], \
                                    select_cols = [ \
                                        RPT_COL_ACCOUNT, \
                                        RPT_COL_CAMPAIGN, \
                                        RPT_COL_CAMPAIGN_STATUS, \
                                        RPT_COL_PACING_RECOMMENDED_STATUS, \
                                        RPT_COL_SBA_PAUSE_DATE, \
                                    ])

# remember to use Bulk column header for Status
outputDf = outputDf.rename(columns = { \
                RPT_COL_CAMPAIGN_STATUS: BULK_COL_STATUS \
                })

print("outputDf shape", outputDf.shape)

Post generated on 2024-03-10 06:34:12 GMT

comments powered by Disqus