Script 1577: Marin DeviceX Adjustment
Purpose:
The Python script calculates and adjusts bid modifications for different devices (mobile, tablet, desktop) based on CPA and ROAS strategy constraints for advertising campaigns.
To Elaborate
The script is designed to optimize advertising bids across different devices by calculating adjustments based on Cost Per Acquisition (CPA) and Return on Advertising Spend (ROAS) strategy constraints. It processes input data to compute CPA and ROAS values, filters relevant data based on these metrics, and groups the data by client, account, campaign, and group. The script then calculates bid adjustments for mobile and tablet devices relative to desktop performance. These adjustments are constrained to a maximum change of ±50% to prevent drastic bid changes. The final output includes the calculated bid adjustments for mobile and tablet devices, formatted as percentages.
Walking Through the Code
- Data Preparation:
- The script begins by defining device types and loading the primary data source into
inputDf. - It calculates CPA and ROAS for each entry using publication cost and conversions or gross profit, respectively.
- The script begins by defining device types and loading the primary data source into
- Data Filtering:
- Filters the data to include only rows where the strategy constraint type is either CPA or ROAS and the calculated values are valid numbers.
- Further filters groups with a sum of clicks greater than or equal to 100 to ensure sufficient data for analysis.
- Device-Specific Dataframes:
- Creates separate dataframes for mobile, tablet, and desktop devices, filtering for positive conversions and publication costs.
- Data Merging:
- Merges the device-specific dataframes on common columns to align CPA and ROAS values across devices for each group.
- Adjustment Calculation:
- Defines a function
calculate_adjustmentsto compute bid adjustments based on CPA or ROAS differences between devices. - Adjustments are capped at ±50% to prevent excessive changes.
- Defines a function
- Output Preparation:
- Applies the adjustment calculations and updates the output dataframe with the new bid adjustments.
- Renames columns to match output requirements and formats adjustments as percentages with two decimal places.
Vitals
- Script ID : 1577
- Client ID / Customer ID: 72134068 / 69058
- Action Type: Bulk Upload (Preview)
- Item Changed: AdGroup
- Output Columns: Account, Campaign, Group, Custom Parameters
- Linked Datasource: M1 Report
- Reference Datasource: None
- Owner: Mingxia Wu (mwu@marinsoftware.com)
- Created by Mingxia Wu on 2024-12-18 07:00
- Last Updated by Mingxia Wu on 2024-12-18 13:18
> 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
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
##
## name: Marin DeviceX Adjustment
## description:
##
##
## author:
## created: 2024-12-18
##
DEVICE = {
'MOBILE': 'm',
'DESKTOP': 'c',
'TABLET': 't',
}
# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CLIENT = 'Client'
RPT_COL_CURRENCY = 'Currency'
RPT_COL_GROUP = 'Group'
RPT_COL_DEVICE = 'Device'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_AVG_BID = 'Avg. Bid €'
RPT_COL_SEARCH_BID = 'Search Bid'
RPT_COL_CALC_SEARCH_BID = 'Calc. Search Bid €'
RPT_COL_GROSS_PROFIT = 'Gross Profit €'
RPT_COL_CONV = 'Conv.'
RPT_COL_IMPR_SHARE = 'Impr. share %'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_PUB_COST = 'Pub. Cost €'
RPT_COL_CTR = 'CTR %'
RPT_COL_GROUP_STATUS = 'Group Status'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_STRATEGY_GOAL = 'Strategy Goal'
RPT_COL_PUBLISHER_BID_STRATEGY_STATUS = 'Publisher Bid Strategy Status'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_IMPR = 'Impr.'
RPT_COL_CAMPAIGN_ID = 'Campaign ID'
RPT_COL_GROUP_ID = 'Group ID'
RPT_COL_STRATEGY_TARGET = 'Strategy Target'
RPT_COL_STRATEGY_CONSTRAINT_TYPE = 'Strategy Constraint Type'
TMP_COL_CPA = "CPA"
TMP_COL_ROAS = "ROAS"
# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_MOBILE_BID_ADJUSTMENT = 'Mobile Bid Adjustment'
BULK_COL_TABLET_BID_ADJUSTMENT = 'Tablet Bid Adjustment'
BULK_COL_DESKTOP_BID_ADJUSTMENT = 'Desktop Bid Adjustment'
skip_output_validations = True
print(inputDf)
inputDf['CPA'] = inputDf[RPT_COL_PUB_COST]/inputDf[RPT_COL_CONV]
inputDf['ROAS'] = inputDf[RPT_COL_GROSS_PROFIT]/inputDf[RPT_COL_PUB_COST]
inputDf = inputDf[
((inputDf[RPT_COL_STRATEGY_CONSTRAINT_TYPE] == 'CPA') & (inputDf['CPA'].apply(lambda x: isinstance(x, (int, float))))) |
((inputDf[RPT_COL_STRATEGY_CONSTRAINT_TYPE] == 'ROAS') & (inputDf['ROAS'].apply(lambda x: isinstance(x, (int, float)))))
]
# Group by 'Group' and calculate the weighted average for 'Clicks'
grouped_df = inputDf.groupby([RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP]).agg({
'Clicks': 'sum',
}).reset_index()
# Group by the specified columns and filter groups with sum of clicks >= 100
grouped_clicks = inputDf.groupby([RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP])[RPT_COL_CLICKS].sum().reset_index()
valid_groups = grouped_clicks[grouped_clicks[RPT_COL_CLICKS] >= 100]
# Filter the inputDf to only include rows from valid groups
inputDf = inputDf.merge(valid_groups, on=[RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP], how='inner')
mobile_df = inputDf[
(inputDf[RPT_COL_DEVICE].isin(['Mobile'])) &
(inputDf[RPT_COL_CONV] > 0) &
(inputDf[RPT_COL_PUB_COST] > 0)
][[
RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP,
TMP_COL_CPA, TMP_COL_ROAS, RPT_COL_STRATEGY_CONSTRAINT_TYPE
]].copy()
tablet_df = inputDf[
(inputDf[RPT_COL_DEVICE].isin(['Tablet'])) &
(inputDf[RPT_COL_CONV] > 0) &
(inputDf[RPT_COL_PUB_COST] > 0)
][[
RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP,
TMP_COL_CPA, TMP_COL_ROAS, RPT_COL_STRATEGY_CONSTRAINT_TYPE
]].copy()
desktop_df = inputDf[
(inputDf[RPT_COL_DEVICE].isin(['Desktop'])) &
(inputDf[RPT_COL_CONV] > 0) &
(inputDf[RPT_COL_PUB_COST] > 0)
][[
RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP,
TMP_COL_CPA, TMP_COL_ROAS, RPT_COL_STRATEGY_CONSTRAINT_TYPE
]].copy()
# Merging dataframes on group
merged_df = desktop_df.merge(mobile_df, on=[RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP], suffixes=('_desktop', '_mobile'), how='left')
merged_df = merged_df.merge(tablet_df, on=[RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP], how='left')
merged_df.rename(columns={'CPA': 'CPA_tablet'}, inplace=True)
merged_df.rename(columns={'ROAS': 'ROAS_tablet'}, inplace=True)
# Calculate CPA and ROAS adjustments based on Strategy Constraint Type
def calculate_adjustments(row):
if row[RPT_COL_STRATEGY_CONSTRAINT_TYPE] == 'CPA':
m_adj = (row['CPA_desktop'] - row['CPA_mobile']) / row['CPA_desktop'] if row['CPA_desktop'] != 0 else 0
t_adj = (row['CPA_desktop'] - row['CPA_tablet']) / row['CPA_desktop'] if row['CPA_desktop'] != 0 else 0
elif row[RPT_COL_STRATEGY_CONSTRAINT_TYPE] == 'ROAS':
m_adj = (row['ROAS_mobile'] - row['ROAS_desktop']) / row['ROAS_desktop'] if row['ROAS_desktop'] != 0 else 0
t_adj = (row['ROAS_tablet'] - row['ROAS_desktop']) / row['ROAS_desktop'] if row['ROAS_desktop'] != 0 else 0
else:
m_adj = t_adj = 0
m_adj_chg = m_adj
if m_adj > 0.5:
m_adj_chg = 0.5
elif m_adj<-0.5:
m_adj_chg = -0.5
t_adj_chg = t_adj
if t_adj > 0.5:
t_adj_chg = 0.5
elif t_adj<-0.5:
t_adj_chg = -0.5
return pd.Series([m_adj, t_adj, m_adj_chg, t_adj_chg])
# Assuming CPA and ROAS columns for each device are already calculated
merged_df[['MAdj_org','TAdj_org','MAdj', 'TAdj']] = merged_df.apply(calculate_adjustments, axis=1)
# Update output DataFrame with new adjustments
outputDf = merged_df[[RPT_COL_CLIENT, RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, 'MAdj', 'TAdj','MAdj_org', 'TAdj_org']]
# Rename columns to match output requirements
outputDf = outputDf.rename(columns={'MAdj': BULK_COL_MOBILE_BID_ADJUSTMENT, 'TAdj': BULK_COL_TABLET_BID_ADJUSTMENT})
# Convert adjustments to percentage with 2 decimal places
outputDf[BULK_COL_MOBILE_BID_ADJUSTMENT] = (outputDf[BULK_COL_MOBILE_BID_ADJUSTMENT]).round(2)
outputDf[BULK_COL_TABLET_BID_ADJUSTMENT] = (outputDf[BULK_COL_TABLET_BID_ADJUSTMENT]).round(2)
Post generated on 2025-03-11 01:25:51 GMT