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
135
#
# Boost Campaign Budget if CPA performance is better than peer average by preset threshold
#
#
# Author: Michael S. Huang
# Date: 2023-02-18

RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_ID = 'Campaign ID'
RPT_COL_CAMPAIGN_TYPE = 'Campaign Type'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_DAILY_BUDGET = 'Daily Budget'
RPT_COL_PUB_COST = 'Pub. Cost $'
RPT_COL_COST_PER_CONV = 'Cost/Conv. $'
RPT_COL_ROAS = 'ROAS'
RPT_COL_AVG_CPC = 'Avg. CPC $'
RPT_COL_CONV_RATE = 'Conv. Rate %'
RPT_COL_CTR = 'CTR %'
RPT_COL_CONV = 'Conv.'
RPT_COL_REVENUE = 'Revenue $'
RPT_COL_SEARCH_LOSTTOPISBUDGET = 'Search Lost Top IS (Budget) %'
RPT_COL_SEARCH_LOSTTOPISRANK = 'Search Lost Top IS (Rank) %'
RPT_COL_LOST_IMPRSHAREBUDGET = 'Lost Impr. Share (Budget) %'
RPT_COL_LOST_IMPRSHARERANK = 'Lost Impr. Share (Rank) %'
RPT_COL_AVG_BID = 'Avg. Bid $'
RPT_COL_HIST_QS = 'Hist. QS'
RPT_COL_IMPR = 'Impr.'
RPT_COL_CLICKS = 'Clicks'
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_DAILY_BUDGET = 'Daily Budget'
BULK_COL_AUTOMATION_BUDGETBOOST = 'AUTOMATION - Budget Boost'

outputDf[BULK_COL_DAILY_BUDGET] = numpy.nan
outputDf[BULK_COL_AUTOMATION_BUDGETBOOST] = numpy.nan

# CPA Performance Threshold and Campaign Budget increase step size
performance_threshold = 0.10
budget_increase = 0.05

# when missing CONV, CPA is INF. Treat it as missing value.
pd.set_option('mode.use_inf_as_na', True)

# reduce to needed columns, fill in missing campaign type
df_reduced = inputDf[[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN_TYPE, RPT_COL_CAMPAIGN, RPT_COL_DATE, RPT_COL_DAILY_BUDGET, RPT_COL_PUB_COST, RPT_COL_CONV]].copy()
df_reduced.fillna({RPT_COL_CAMPAIGN_TYPE: 'NA'}, inplace=True)

# # limit to non-brand campaigns
# non_brand = (df_reduced[RPT_COL_CAMPAIGN].map(lambda x: 'NB' in x)) | (df_reduced[RPT_COL_CAMPAIGN].map(lambda x: 'nonbrand' in x.lower()))
# df_reduced = df_reduced[non_brand]

# print("reduced input\\n", df_reduced.to_string())

# 30-day lookback without most recent 3 days due to conversion lag
start_date = pd.to_datetime(datetime.date.today() - datetime.timedelta(days=33))
end_date = pd.to_datetime(datetime.date.today() - datetime.timedelta(days=3))
df_reduced = df_reduced[ (df_reduced[RPT_COL_DATE] >= start_date) & (df_reduced[RPT_COL_DATE] <= end_date) ]
if (df_reduced.shape[0] > 0):
    print("reduced dates\\n", min(df_reduced[RPT_COL_DATE]), max(df_reduced[RPT_COL_DATE]))
else:
    print("no more input to process")

# aggregate over remaining date range, summing cost/conv and take latest campaign budget
# remove 0 conv
agg_func_selection = {
    RPT_COL_DAILY_BUDGET: ['last'],
    RPT_COL_PUB_COST: ['sum'],
    RPT_COL_CONV: ['sum']
}
df_campaign_agg = df_reduced.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN_TYPE, RPT_COL_CAMPAIGN]).agg(agg_func_selection)

# recalc cpa
df_campaign_agg[RPT_COL_COST_PER_CONV] = df_campaign_agg[RPT_COL_PUB_COST] / df_campaign_agg[RPT_COL_CONV]
# print("recalc cpa for campaign w conv\\n", df_campaign_agg.to_string())

# calc avg cpa by account and campaign type
agg_func_selection = {
    RPT_COL_DAILY_BUDGET: ['mean'],
    RPT_COL_PUB_COST: ['sum'],
    RPT_COL_CONV: ['sum']
}
df_cpa_agg = df_reduced.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN_TYPE]).agg(agg_func_selection)
df_cpa_agg[RPT_COL_COST_PER_CONV] = df_cpa_agg[RPT_COL_PUB_COST] / df_cpa_agg[RPT_COL_CONV]
# print("avg cpa\\n", df_cpa_agg.to_string())

# merge avg cpa to input df to make comparison
df_comparison = df_campaign_agg.join(df_cpa_agg, how='left', on=[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN_TYPE], lsuffix="", rsuffix="_agg")
# print("merged avg cpa\\n", df_comparison.to_string())

# calculate performance of CPA relative to categorical average
df_comparison['cpa_perf'] = (df_comparison[RPT_COL_COST_PER_CONV + '_agg'] - df_comparison[RPT_COL_COST_PER_CONV]) / df_comparison[RPT_COL_COST_PER_CONV + '_agg']

# print("daily budget\\n", df_comparison[RPT_COL_DAILY_BUDGET]['last'])

# calc new daily budget for all campaigns
df_comparison[BULK_COL_DAILY_BUDGET + '_'] = df_comparison[RPT_COL_DAILY_BUDGET]['last'] * (1+budget_increase)

# Make budget adj if over threshold and non-zero
df_adj = df_comparison[ (df_comparison['cpa_perf'] > performance_threshold) & (df_comparison[BULK_COL_DAILY_BUDGET + '_'] > 0)].copy() 

if not df_adj.empty:

    print("df_adj\\n", df_adj.to_string())
    
    df_adj.reset_index(level='Campaign', inplace=True)

    # annotate via Marin Dimensions
    def rowFunc(row):
        return 'CPA is {:,.2f}% lower than avg ${:,.2f} for Account/CampaignType {}. Budget increased {:,.2f}% on {}'.format(
            100*row['cpa_perf'].values[0], \
            row[RPT_COL_COST_PER_CONV + "_agg"].values[0], \
            row.name, \
            budget_increase * 100, \
            datetime.date.today()
        )

    df_adj[BULK_COL_AUTOMATION_BUDGETBOOST] = df_adj.apply(rowFunc, axis=1)

    df_adj.reset_index(inplace=True)

    # print("df_adj\\n", df_adj.to_string())

    # bulk output
    outputDf = df_adj[[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_DAILY_BUDGET + '_', BULK_COL_AUTOMATION_BUDGETBOOST]].copy()
    outputDf.rename(columns={RPT_COL_DAILY_BUDGET + '_': RPT_COL_DAILY_BUDGET}, inplace=True)
    outputDf = outputDf.round(2).droplevel(1, axis=1)
    # print("output\\n", outputDf.to_string())

else:

    print("No qualified campaigns exceeded performance threshold of {:,.2f}%".format(performance_threshold * 100))
    outputDf = outputDf.iloc[0:0]