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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
##
# Name: Recommended tROAS and Budget based on New Customer ROAS
# Author: PythonGPT & Michael Huang
# Created: 2023-06-16
# Updated: 2023-07-06
##

######### Configurable Params ##########

NEW_CUSTOMERS_BENCHMARK_ROAS = 0.15
TROAS_CHANGE_CAP = 0.10
TROAS_ROAS_RATIO_HIGH = 1.30
TROAS_ROAS_RATIO_LOW = 0.70

MIN_DAILY_BUDGET = 100
BUDGET_INCREASE_LOST_IS_BUDGET_THRESHOLD = 0.07
BUDGET_CHANGE_CAP = 0.25

TOTAL_BUDGET_BY_MONTH = {
    # <month as integer>: <total monthly budget>
    1: 60000,
    2: 60000,
    3: 60000,
    4: 60000,
    5: 60000,
    6: 60000,
    7: 60000,
    8: 60000,
    9: 60000,
    10: 60000,
    11: 60000,
    12: 60000,
}
######### End of Configurable Params ##########

# Constants for column names
ACCOUNT = 'Account'
CAMPAIGN = 'Campaign'
PUBLISHER_TARGET_ROAS = 'Publisher Target ROAS'
DAILY_BUDGET = 'Daily Budget'
ROAS = 'ROAS'
NEW_CUSTOMERS_ROAS = 'New Customers ROAS'
NEW_CUSTOMERS_CONV = 'New Customers Conv.'
NEW_CUSTOMERS_CPA = 'New Customers Cost/Conv.'
SEARCH_LOST_TOP_IS_BUDGET = 'Search Lost Top IS (Budget) %'
RECOMMENDED_TROAS = 'Recommended tROAS'
RECOMMENDED_BUDGET = 'Recommended Budget'

COL_TOTAL_CONV = NEW_CUSTOMERS_CONV + '_TOTAL'
COL_CONV_RATIO = NEW_CUSTOMERS_CONV + '_RATIO'
COL_TOTAL_CPA = NEW_CUSTOMERS_CPA + '_TOTAL'
COL_CPA_RATIO = NEW_CUSTOMERS_CPA + '_RATIO'

COL_BUDGET_RATIO = RECOMMENDED_BUDGET + '_RATIO'
COL_REC_DAILY_BUDGET = RECOMMENDED_BUDGET + '_'
COL_REC_TROAS = RECOMMENDED_TROAS + '_'



def process(inputDf):
    # Create new columns initialized with np.nan
    inputDf[COL_REC_TROAS] = np.nan
    inputDf[COL_REC_DAILY_BUDGET] = np.nan

    ### Business Logic for Recommended tROAS

    # calculate recommended Strategy tROAS
    def calc_troas(row):

        # init to existing value
        troas = row[PUBLISHER_TARGET_ROAS]

        if row[NEW_CUSTOMERS_ROAS] >= NEW_CUSTOMERS_BENCHMARK_ROAS:
            # New Customers convert well when there is low competition.
            # Raise Strategy tROAS to decrease bids, but not higher than ROAS * TROAS_ROAS_RATIO_HIGH

            raised_troas = round(row[PUBLISHER_TARGET_ROAS] * (1 + TROAS_CHANGE_CAP), 2)
            max_troas = round(row[ROAS] * TROAS_ROAS_RATIO_HIGH, 2)

            troas = min(raised_troas, max_troas)

        else:
            # New Customers don't convert well when there is high competition.
            # Lower Strategy tROAS to increase bids, but not lower than ROAS * TROAS_ROAS_RATIO_LOW

            lowered_troas = round(row[PUBLISHER_TARGET_ROAS] * (1 - TROAS_CHANGE_CAP), 2)
            min_troas = round(row[ROAS] * TROAS_ROAS_RATIO_LOW, 2)

            troas = max(lowered_troas, min_troas)

        return troas


    inputDf[COL_REC_TROAS] = inputDf.apply(calc_troas, axis=1)

    ### Business Logic for Recommended Budget

    # Get total budget for this month
    this_month = datetime.datetime.now(CLIENT_TIMEZONE).month
    print("this month", this_month)
    total_budget = TOTAL_BUDGET_BY_MONTH[this_month]
    print("this month total budget", total_budget)


    # Calculate ratio of "New Customers" Conv for each campaign
    inputDf[COL_TOTAL_CONV] = inputDf[NEW_CUSTOMERS_CONV].sum()
    inputDf[COL_CONV_RATIO] = inputDf[NEW_CUSTOMERS_CONV] / inputDf[COL_TOTAL_CONV]

    # Calulate ratio of "New Customers" CPA for each campaign
    # Inverted so high ratio number is better
    # Normalized to sum up to 1 so can be averaged with Conv Ratio
    # Note: sum of CPA/TOTAL_CPA across campaigns is 1. 
    #       sum of (1 - CPA/TOTAL_CPA) across campaigns is (campaign count - 1)
    inputDf[COL_TOTAL_CPA] = inputDf[NEW_CUSTOMERS_CPA].sum()
    inputDf[COL_CPA_RATIO] = (1 - (inputDf[NEW_CUSTOMERS_CPA] / inputDf[COL_TOTAL_CPA])) / (inputDf.shape[0]-1)

    # To prevent artifically choking low CPA campaigns due to inventory issues,
    # Budget Ratio is average of both Conv Ratio and CPA Ratio
    inputDf[COL_BUDGET_RATIO] = (inputDf[COL_CONV_RATIO] + inputDf[COL_CPA_RATIO]) / 2

    # for reference, calc the raw rec budget before applying min/cap rules
    inputDf[COL_REC_DAILY_BUDGET + 'raw'] = round(inputDf[COL_BUDGET_RATIO] * total_budget / 31, 2)

    # calculate reallocated budget based on min, cap, and lost impression share rules
    def calc_budget(row):

        # calc rec budget
        rec_budget = row[COL_BUDGET_RATIO] * total_budget / 31
        
        # limit change to CAP in both directions
        delta_ratio = (rec_budget - row[DAILY_BUDGET])/row[DAILY_BUDGET]
        if (delta_ratio > BUDGET_CHANGE_CAP):
            rec_budget = row[DAILY_BUDGET] * (1 + BUDGET_CHANGE_CAP)
        elif (delta_ratio < (-1 * BUDGET_CHANGE_CAP)):
            rec_budget = row[DAILY_BUDGET] * (1 - BUDGET_CHANGE_CAP)
            
        # no lower than min daily budget
        rec_budget = max(MIN_DAILY_BUDGET, rec_budget)
        
        # if lost impression share due to budget is low, no point increasing budget
        if (rec_budget > row[DAILY_BUDGET]) & (row[SEARCH_LOST_TOP_IS_BUDGET] < BUDGET_INCREASE_LOST_IS_BUDGET_THRESHOLD):
            # no change in rec budget
            print(f"rec budget not increased due to low Lost IS: {row}")
            rec_budget = np.nan
            
        rec_budget = round(rec_budget, 2)
        
        if (rec_budget == row[DAILY_BUDGET]):
            # no change in rec budget
            rec_budget = np.nan
         
        return rec_budget

    inputDf[COL_REC_DAILY_BUDGET] = inputDf.apply(calc_budget, axis=1)

    print("inputDf after all calculations", tableize(inputDf))
    
    # Check for changes
    changed = (inputDf[COL_REC_TROAS].notnull() & (inputDf[RECOMMENDED_TROAS] != inputDf[COL_REC_TROAS])) | \
              (inputDf[COL_REC_DAILY_BUDGET].notnull() & (inputDf[RECOMMENDED_BUDGET] != inputDf[COL_REC_DAILY_BUDGET]))

    if changed.sum() > 0:
        # print changed rows
        print("changed", tableize(inputDf[changed]))
        
        # construct outputDf
        outputDf = inputDf[changed][[ACCOUNT, CAMPAIGN, COL_REC_TROAS, COL_REC_DAILY_BUDGET]].copy()
        outputDf.rename(columns={COL_REC_DAILY_BUDGET: RECOMMENDED_BUDGET, COL_REC_TROAS: RECOMMENDED_TROAS}, inplace=True)
        
    else:
        print("No changes detected, returning an empty dataframe")
        outputDf = pd.DataFrame(columns=[ACCOUNT, CAMPAIGN, RECOMMENDED_BUDGET, RECOMMENDED_TROAS])

    return outputDf.round(2)

def test_process():
    print("### UNIT TEST START ###")
    
    # Create sample input DataFrame
    # Campaign 1: ROAS > tROAS, change/increase rec tROAS; large conv ratio, increase budget
    # Campaign 2: ROAS < tROAS, same (increased) rec tROAS; medium conv ratio, same (increased) rec budget
    # Campaign 3: ROAS < tROAS, change rec tROAS; small conv ratio with large cpa ratio, increase rec budget
    input_data = {
        ACCOUNT: ["Account 1", "Account 2", "Account 3"],
        CAMPAIGN: ["Campaign 1", "Campaign 2", "Campaign 3"],
        PUBLISHER_TARGET_ROAS: [0.40, 0.35, 0.40],
        DAILY_BUDGET: [100.0, 200.0, 200.0],
        ROAS: [0.50, 0.50, 0.32],
        NEW_CUSTOMERS_ROAS: [0.13, 0.27, 0.19],
        NEW_CUSTOMERS_CONV: [10, 4, 1],
        NEW_CUSTOMERS_CPA: [10.0, 50.0, 200.0],
        SEARCH_LOST_TOP_IS_BUDGET: [0.12, 0.03, 0.15],
        # Campaign 2 existing Rec tROAS and Rec Budget same as new calc values, so not in output
        RECOMMENDED_TROAS: [10.0, 0.39, 20.0],
        RECOMMENDED_BUDGET: [100.0, 250.0, 200.0],
    }
    inputDf = pd.DataFrame(input_data)

    # Expected output DataFrame
    expected_output_data = {
        ACCOUNT: ["Account 1", "Account 3"],
        CAMPAIGN: ["Campaign 1", "Campaign 3"],
        # Campaign 3 rec troas should be 0.40 * 1.1 = 0.44, but over 0.32 * 1.3 = 0.42, so clipped to 0.42
        RECOMMENDED_TROAS: [0.36, 0.42],
        RECOMMENDED_BUDGET: [125.0, 176.18],
    }
    expected_outputDf = pd.DataFrame(expected_output_data)

    # Test the process function
    outputDf = process(inputDf).reset_index(drop=True)
    
    # if outputDf.equals(expected_outputDf):
    if np.array_equal(expected_outputDf.values,outputDf.values):
        print("#### PASS ####")
    else:
        print("#### FAIL ####")
        print("expected", tableize(expected_outputDf), expected_outputDf.dtypes, expected_outputDf.values)
        print("actual", tableize(outputDf), outputDf.dtypes, outputDf.values)
    
    print("### UNIT TEST END ###")

# Run the unit test
test_process()

# Process the inputDf
outputDf = process(inputDf)
print("final output", tableize(outputDf))