Script 1455: marin pilot campaign devadj

Purpose

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

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 each device type and computes the gross profit-weighted average bid. Using this average, it determines the bid ratios for each device relative to mobile, which are then used to adjust the bids. This approach aims to enhance the efficiency of budget allocation by aligning bid adjustments with the profitability of each device type, thereby potentially increasing the return on investment for advertising campaigns.

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 from the previous day and prepares the campaign data by removing device-specific suffixes from campaign names.
  2. Grouping and Aggregation:
    • The data is grouped by account, campaign, group, and device.
    • For each group, it calculates the weighted average of the ‘Avg. Bid £’ using the number of clicks as weights, and sums up the clicks and gross profit.
  3. Device-Specific DataFrames:
    • Separate DataFrames are created for mobile, tablet, and desktop devices by filtering the grouped data.
  4. Merging and Calculations:
    • The script merges the device-specific DataFrames on common columns to consolidate the data.
    • It calculates the gross profit-weighted average bid across all devices.
  5. Bid Ratio Calculation:
    • The script computes bid ratios for mobile, tablet, and desktop devices relative to the gross profit-weighted average bid.
    • These ratios are rounded and prepared for output.
  6. Output Preparation:
    • The final output DataFrame is constructed with the calculated bid adjustments for each device type.
    • The script renames columns to match the expected output format and prints the result.

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 2024-11-27 06:58:46 GMT

comments powered by Disqus