Script 1455: marin pilot campaign devadj

Purpose:

The Python script adjusts bid ratios for mobile, tablet, and desktop devices in advertising campaigns based on gross profit-weighted average bids.

To Elaborate

The script is designed to optimize advertising campaign bids across different devices—mobile, tablet, and desktop—by calculating bid adjustments based on gross profit-weighted average bids. It processes input data to group campaigns by account, campaign, group, and device, and calculates a weighted average bid for each group. The script then merges data for different devices and computes the gross profit-weighted average bid. Using this, it calculates the bid ratios for each device relative to the mobile bid, which are then used to adjust the bids for each device type. The output is a DataFrame containing the adjusted bid ratios for each campaign and device type, which can be used to update the campaign settings in an advertising platform.

Walking Through the Code

  1. Data Preparation:
    • The script begins by defining device types and loading the primary data source into inputDf.
    • It filters out data for a specific date and prepares the campaign names by removing device-specific suffixes.
  2. Data Aggregation:
    • The script groups the data by account, campaign, group, and device, calculating a weighted average for the ‘Avg. Bid £’ based on clicks.
    • It also sums up clicks and gross profit for each group.
  3. Data Segmentation:
    • The grouped data is split into separate DataFrames for mobile, tablet, and desktop devices.
  4. Data Merging:
    • The script merges these device-specific DataFrames on common columns to create a comprehensive view of each campaign across devices.
  5. Bid Adjustment Calculation:
    • It calculates the gross profit-weighted average bid and uses this to determine bid ratios for each device type relative to the mobile bid.
  6. Output Preparation:
    • The final output DataFrame is prepared with columns for account, campaign, group, and the calculated bid adjustments for each device type.

Vitals

  • Script ID : 1455
  • Client ID / Customer ID: 247648668 / 13095968
  • Action Type: Bulk Upload (Preview)
  • Item Changed: AdGroup
  • Output Columns: Account, Campaign, Group, Custom Parameters
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Ji Hua (hji@marinsoftware.com)
  • Created by Ji Hua on 2024-10-25 02:58
  • Last Updated by Ji Hua on 2024-10-29 02:10
> 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

##
## name: marin-pilot-campaign-devadj
## description:
##  
## 
## author: Mingxia Wu
## created: 2024-10-25
## 

DEVICE = {
  'MOBILE': 'm',
  'DESKTOP': 'c',
  'TABLET': 't',
}

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DEVICE = 'Device'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_GROUP = 'Group'
RPT_COL_CAMPAIGN_ID = 'Campaign ID'
RPT_COL_DAILY_BUDGET = 'Daily Budget'
RPT_COL_IMPR = 'Impr.'
RPT_COL_IMPR_SHARE = 'Impr. share %'
RPT_COL_DESKTOP_BID_ADJ = 'Desktop Bid Adj.'
RPT_COL_TABLET_BID_ADJ = 'Tablet Bid Adj.'
RPT_COL_MOBILE_BID_ADJ = 'Mobile Bid Adj.'
RPT_COL_CLICKS = 'Clicks'
RPT_COL_PUB_COST = 'Pub. Cost £'
RPT_COL_AVG_BID = 'Avg. Bid £'
RPT_COL_CONV = 'Conv.'
RPT_COL_APPLE_LAT_OFF_CONV = 'Apple LAT Off Conv.'
RPT_COL_CONV_RATE = 'Conv. Rate %'
RPT_COL_APPLE_LAT_ON_CONV = 'Apple LAT On Conv.'
RPT_COL_CTR = 'CTR %'
RPT_COL_APPLE_NEW_DOWNLOADS_CONV = 'Apple New Downloads Conv.'
RPT_COL_GROSS_PROFIT = 'Gross Profit £'
RPT_COL_APPLE_REDOWNLOADS_CONV = 'Apple Redownloads Conv.'
RPT_COL_REVENUE = 'Revenue £'
RPT_COL_DEVICE_FIRST_SIGNIN_CONV = 'Device First Sign-In Conv.'
RPT_COL_LOST_IMPR_SHARE_BUDGET = 'Lost Impr. Share (Budget) %'
RPT_COL_PRIME_MUSIC_FIRST_STREAM_CONV = 'Prime Music First Stream Conv.'
RPT_COL_PUB_ID = 'Pub. ID'
RPT_COL_FREE_TIER_FIRST_STREAM_CONV = 'Free Tier First Stream Conv.'
RPT_COL_PUBLISHER = 'Publisher'
RPT_COL_PRIME_REACTIVATION_CONV = 'Prime Reactivation Conv.'
RPT_COL_STRATEGY = 'Strategy'
RPT_COL_PODCAST_FIRST_STREAM_CONV = 'Podcast First Stream Conv.'
RPT_COL_TOTAL_AMU_SIGNUPS_CONV = 'Total AMU Sign-Ups Conv.'

# 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'
outputDf[BULK_COL_MOBILE_BID_ADJUSTMENT] = "<<YOUR VALUE>>"
outputDf[BULK_COL_TABLET_BID_ADJUSTMENT] = "<<YOUR VALUE>>"
outputDf[BULK_COL_DESKTOP_BID_ADJUSTMENT] = "<<YOUR VALUE>>"

today = datetime.datetime.now(CLIENT_TIMEZONE).date()
from datetime import datetime, timedelta
yesterday = today - timedelta(days=1)

inputDf = dataSourceDict["1"]
inputDf = inputDf[(inputDf['Date'] != pd.to_datetime(yesterday))]

skip_output_validations = True
print(inputDf)
inputDf['campaign_x'] = inputDf[RPT_COL_CAMPAIGN].str.replace(r'_(Mobile|Tablet|Desktop)', '__', regex=True)

# Group by 'Group' and calculate the weighted average for 'Avg. Bid'
grouped_df = inputDf.groupby([RPT_COL_ACCOUNT, 'campaign_x', RPT_COL_GROUP, RPT_COL_DEVICE]).agg({
    'Avg. Bid £': lambda x: (x * inputDf.loc[x.index, 'Clicks']).sum() / inputDf.loc[x.index, 'Clicks'].sum() if inputDf.loc[x.index, 'Clicks'].sum() != 0 else 0,
    'Clicks': 'sum',
    'Gross Profit £': 'sum',
    'Campaign': 'min'
}).reset_index()

print(grouped_df)
# Extract the device (Mobile, Tablet, Desktop, etc.) to a new column
# inputDf['device_x'] = inputDf[RPT_COL_CAMPAIGN].str.extract(r'_(Mobile|Tablet|Desktop)_')
# inputDf['device_x'] = np.where(
#     grouped_df['Device'].notna() & (grouped_df['Device'] != ''), 
#     grouped_df['Device'], 
#     grouped_df[RPT_COL_CAMPAIGN].str.extract(r'_(Mobile|Tablet|Desktop)_')[0]
# )

mobile_df = grouped_df[grouped_df['Device'].isin(['Mobile'])].copy()
tablet_df = grouped_df[grouped_df['Device'].isin(['Tablet'])].copy()
desktop_df = grouped_df[grouped_df['Device'].isin(['Desktop'])].copy()

print("moible length = " + str(mobile_df.shape[0]))
print("tablet length = " + str(tablet_df.shape[0]))
print("desktop length = " + str(desktop_df.shape[0]))

# Merging dataframes on campaign_x
merged_df = mobile_df.merge(tablet_df[[RPT_COL_ACCOUNT, 'campaign_x', 'Group',  'Avg. Bid £', RPT_COL_GROSS_PROFIT]], on=[RPT_COL_ACCOUNT, 'campaign_x', 'Group'], suffixes=('_mobile', '_tablet'), how='left')
merged_df = merged_df.merge(desktop_df[[RPT_COL_ACCOUNT, 'campaign_x', 'Group', 'Avg. Bid £', RPT_COL_GROSS_PROFIT]], on=[RPT_COL_ACCOUNT, 'campaign_x', 'Group'], how='left')

merged_df.rename(columns={'Avg. Bid £': 'Avg. Bid £_desktop'}, inplace=True)
merged_df.rename(columns={'Gross Profit £': 'Gross Profit £_desktop'}, inplace=True)

print(merged_df)
merged_df[['Avg. Bid £_mobile', 'Avg. Bid £_tablet', 'Avg. Bid £_desktop',
           'Gross Profit £_mobile', 'Gross Profit £_tablet', 'Gross Profit £_desktop']] = merged_df[[
    'Avg. Bid £_mobile', 'Avg. Bid £_tablet', 'Avg. Bid £_desktop',
    'Gross Profit £_mobile', 'Gross Profit £_tablet', 'Gross Profit £_desktop'
]].fillna(0)

# Calculate GP-weighted average
merged_df['GP_Weighted_Avg_Bid'] = (
    (merged_df['Avg. Bid £_mobile'] * merged_df['Gross Profit £_mobile'] +
     merged_df['Avg. Bid £_desktop'] * merged_df['Gross Profit £_desktop'] +
     merged_df['Avg. Bid £_tablet'] * merged_df['Gross Profit £_tablet']) /
    (merged_df['Gross Profit £_mobile'] +
     merged_df['Gross Profit £_desktop'] +
     merged_df['Gross Profit £_tablet'])
)

# Calculating the new columns
merged_df['Mobile_to_Mobile_Bid_Ratio'] = (merged_df['Avg. Bid £_mobile'].fillna(0)/ merged_df['GP_Weighted_Avg_Bid'].fillna(1) - 1).round(0)
merged_df['Tablet_to_Mobile_Bid_Ratio'] = (merged_df['Avg. Bid £_tablet'].fillna(0)/ merged_df['GP_Weighted_Avg_Bid'].fillna(1) - 1).round(0)
merged_df['Desktop_to_Mobile_Bid_Ratio'] = (merged_df['Avg. Bid £_desktop'].fillna(0)/ merged_df['GP_Weighted_Avg_Bid'].fillna(1) - 1).round(0)

outputDf = merged_df[[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, RPT_COL_GROUP, 'Mobile_to_Mobile_Bid_Ratio', 'Tablet_to_Mobile_Bid_Ratio', 'Desktop_to_Mobile_Bid_Ratio']]


outputDf = outputDf.rename(columns={RPT_COL_ACCOUNT + '_mobile': RPT_COL_ACCOUNT})
# outputDf = outputDf.rename(columns={RPT_COL_CAMPAIGN  + '_mobile': RPT_COL_CAMPAIGN})
outputDf = outputDf.rename(columns={RPT_COL_GROUP + '_mobile': RPT_COL_GROUP})

outputDf = outputDf.rename(columns={'Mobile_to_Mobile_Bid_Ratio': RPT_COL_MOBILE_BID_ADJ})
outputDf = outputDf.rename(columns={'Tablet_to_Mobile_Bid_Ratio': RPT_COL_TABLET_BID_ADJ})
outputDf = outputDf.rename(columns={'Desktop_to_Mobile_Bid_Ratio': RPT_COL_DESKTOP_BID_ADJ})

print(outputDf)

Post generated on 2025-03-11 01:25:51 GMT

comments powered by Disqus