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]