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
##
## name: Ascend Strategy analysis
## description:
##  
## 
## author: mwu
## created: 2024-08-14
## 

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

# reference data source and columns
# gSheetsDf = dataSourceDict["2_2"]  # gsheets dataframe (first sheet)
# To access 10th row of Column C, use gSheetsDf.loc[10, 'C']

# output columns and initial values
# outputDf = gSheetsDf.round(2)

# outputDf.columns = ['Month','Strategy ID','Strategy','Target','Actual','Deviation',
# 'Date-Weighted Target',	'Status','Conv Value','Convs','Goal Changed','Campaign Chged']

# outputDf['Deviation'] = outputDf['Deviation'].apply(lambda x: f"{x * 100:.2f}%")
folderDf = outputDf.drop(columns=outputDf.columns[-1])

folderDf.columns = ['Client ID','Strategy ID','Strategy','Goal', 'Episode Month',  'Target','Actual','Avg. Target', 'Status','Goal Changed','Campaign Changed',
'Convs', 'Conv Value', 'Spend', 'Post. Nr.', 'Post. Bids', 'Post. CPA/ROAS Targets', 'Post. Daily Budgets', 'Month', 'Category']

folderDf['Error vs. Episode-End'] = (folderDf['Actual'] / folderDf['Target']) 
folderDf['Error vs. Avg. Target'] = (folderDf['Actual'] / folderDf['Avg. Target'])
folderDf['Goal Changed']= folderDf['Goal Changed'].apply(lambda x: 'N' if x == 1 else 'Y')
folderDf['Campaign Changed']= folderDf['Campaign Changed'].apply(lambda x: 'N' if x == 1 else 'Y')
folderDf['Post. CPA/ROAS Targets'] = folderDf['Post. CPA/ROAS Targets'].apply(lambda x: 'Post. to Publisher' if x == 'Traffic' else x)
folderDf['Post. Bids'] = folderDf['Post. Bids'].apply(lambda x: 'Post. to Publisher' if x == 'Traffic' else x)
folderDf['Post. Daily Budgets'] = folderDf['Post. Daily Budgets'].apply(lambda x: 'Post. to Publisher' if x == 'Traffic' else x)

selected_columns = ['Category', 'Episode Month', 'Month', 'Strategy', 'Spend', 'Goal', 'Target', 'Actual', 'Error vs. Episode-End', 'Avg. Target', 'Error vs. Avg. Target', 'Goal Changed', 'Campaign Changed', 'Post. Daily Budgets', 'Post. CPA/ROAS Targets', 'Post. Bids', 'Strategy ID', 'Convs', 'Conv Value', 'Post. Nr.']

# filtered_folderDf = folderDf[folderDf['Client ID'] == 69747]
filtered_folderDf = folderDf

folderDf = filtered_folderDf[selected_columns].sort_values(by='Category')

# Group by 'Category' and perform the calculations
outputDf = folderDf.groupby(['Category', 'Episode Month', 'Month']).agg(
    strategy_count=('Strategy ID', 'count'),
    weighted_abs_deviation=(
        'Spend',
        lambda df: (
                (
                        (folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'])
                        .fillna(0)
                        .abs() * df
                ).sum() / df.sum()
        )
    ),
    within_target_spend=(
        'Spend',
        lambda df: (
            df.loc[
                (folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'] >= 0.9) &
                (folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'] <= 1.1)
            ].sum() / df.sum()
        )
    ),
    slightly_off_target_spend=(
        'Spend',
        lambda df: (
            df.loc[
                ((folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'] < 0.9) &
                 (folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'] >= 0.75)) |
                ((folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'] >= 1.1) &
                 (folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'] <= 1.25))
            ].sum() / df.sum()
        )
    ),
    off_target_spend=(
        'Spend',
        lambda df: (
            df.loc[
                (folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'] < 0.75) |
                (folderDf.loc[df.index, 'Actual'] / folderDf.loc[df.index, 'Avg. Target'] > 1.25)
            ].sum() / df.sum()
        )
    ),
    total_actual_spend=('Spend', 'sum'),
    total_actual_convs=('Convs', 'sum'),
    total_conv_value=('Conv Value', 'sum')
).reset_index().sort_values(by=['Category', 'Episode Month'])

outputDf['weighted_abs_deviation'] = outputDf['weighted_abs_deviation'].apply(lambda x: f"{x * 100:.0f}%")
outputDf['within_target_spend'] = outputDf['within_target_spend'].apply(lambda x: f"{x * 100:.0f}%")
outputDf['slightly_off_target_spend'] = outputDf['slightly_off_target_spend'].apply(lambda x: f"{x * 100:.0f}%")
outputDf['off_target_spend'] = outputDf['off_target_spend'].apply(lambda x: f"{x * 100:.0f}%")
outputDf['total_actual_convs'] = outputDf['total_actual_convs'].apply(lambda x: '{:,}'.format(int(x)))
outputDf['total_actual_spend'] = outputDf['total_actual_spend'].apply(lambda x: '{:,}'.format(int(x)))
outputDf['total_conv_value'] = outputDf['total_conv_value'].apply(lambda x: '{:,}'.format(int(x)))

outputDf.drop(columns='Episode Month', inplace=True)
outputDf.columns = ['Posting Category', 'Month', 'Strategy', 'Accuracy vs. Avg. Target', 'Spend% in 90% to 110% ①', 'Spend% in 75-90% | 110-125% ②', 'Spend% <75% | >125% ③', 'Spend', 'Conversions','Revenue']

# Define the custom order
custom_order = ['ON', 'Partial', 'OFF']

config = {
    "email_template":"ascend_email_template"
}
import json
mscripts_output_config = json.dumps(config)

# Sort using Categorical inside sort_values
outputDf = outputDf.sort_values(by='Posting Category', key=lambda x: pd.Categorical(x, categories=custom_order, ordered=True))

df_string = outputDf.to_string(index=False, header=True, justify='center')
emailSummaryPrompt = f'''
You are a helpful pay-per-click marketing data analyst with a deep understanding of common marketing targets and how to hit them.

You are working with the output of a target deviation report.
Please summarize the results in a clear, easy-to-understand, and concise manner.
Make the report useful and insightful to read by using language from the hospitality sector(reader is not really in hospitality industry) while keeping the tone professional.
Please make sure the report is not alarming while still pointing out the actual vs. target result.

Each line stands for a rollup posting category. Posting Category is the levels that controled by Marin AI Dynamic Allocation.
It has 3 posting categories: 
ON means 100% managed by Marin AI Dynamic Allocation. Please state it as Managed Strategies.
Partial means partially managed.
OFF means Unmanaged. Please state as Unmanaged. Marin has no control so cannot be responsible for the result or missing result.

Accuracy(`Accuracy vs. Avg. Target`) is spend weighted rollup of target percent across all strategies in this posting category.
Metric with (`Accuracy vs. Avg. Target`) greater than 90% and less than 110% are qualified to hit the target. 
Metric with (`Accuracy vs. Avg. Target`) greater than 75% and less than 90% or greater than 110% and less than 125% are miss the target that require attention. Please highlight the exact accuracy numbers and explain why they didn’t hit the target.
Metric with (`Accuracy vs. Avg. Target`) greater than 125% or less than 75% are seriously miss the target. Please highlight the exact accuracy numbers and explain why they didn’t hit the target.
Metric with (`Accuracy vs. Avg. Target`) greater than 100% means over target while less than 100% means under target.

Marin Software is a market leader in on-line advertising and pay-per-click marketing solution. 
Its Dynamic Allocation is a powered by AI solution cross channels and help customers to hit their perforamnce targets! 

Generate output in Markdown, using this format:
# Marin Dynamic Allocation Insights
# Please put the month range from values with ('Month') in DataFrame, in small font here

# Trends: 
* Positive: please check Accuracy(`Accuracy vs. Avg. Target`) by month and category, to emphasize if there is an improvement of the performance to hit the target vs. last month.
* Negative: please summarize if cannot see performance getting better by months. 

Example like this one: 
*Trends:
    Positive: Managed Strategies (ON) have shown a consistent performance in staying close to the target, with a slight improvement in accuracy from August to September.
    Negative: Unmanaged (OFF) strategies have demonstrated a concerning trend, with accuracy significantly deviating from the target in September."

#Overall Insights and Suggestions
* And give overall suggestions accordingly, eg. if managed cateogires shows beter result then other categories, you should suggest user to move their strategies to managed category. Let Marin's AI Dynamic Allocation help to hit performance target! 
Example like below: 
"Overall Insights and Suggestions:

Based on the observed trends, it is advisable to consider shifting more strategies to the Managed Strategies category. Marin's AI Dynamic Allocation has proven effective in maintaining spend within a closer range to the target, which is indicative of its ability to optimize performance. By leveraging the AI-powered solution, there is a greater likelihood of achieving and even surpassing performance targets.
For the Unmanaged strategies, it is crucial to review the substantial deviation observed in September and identify the underlying causes. This may involve analyzing external factors or internal campaign settings that are not under Marin's control. Implementing corrective measures or transitioning these strategies to Managed Strategies could help in mitigating such deviations in the future.

In conclusion, embracing Marin's AI Dynamic Allocation for a larger portion of your advertising strategies appears to be a strategic move to enhance overall campaign performance and achieve desired marketing objectives in the hospitality sector."


Please don't explain data for each category.
Please don't print dataframe raw data.
Please don't put any signature.

===

DataFrame for target deviation:
---
 {df_string}
---
 '''