Script 525: Campaign Set Rate

Purpose

The Python script calculates the “Set Rate” for campaigns by aggregating six months of data, excluding the most recent 30 days, to account for latency.

To Elaborate

The script is designed to compute the “Set Rate” for marketing campaigns, which is defined as the ratio of “Set Lead” to “Assigned Elevated Qualified Response.” This metric is calculated using data from the past six months, deliberately excluding the most recent 30 days to mitigate the effects of data latency. The script processes campaign data, aggregates necessary conversion metrics, and applies a custom function to compute the Set Rate. The result is a percentage that reflects the effectiveness of campaigns over the specified period, providing insights into campaign performance without the distortion of recent data fluctuations.

Walking Through the Code

  1. Data Preparation
    • The script begins by defining the primary data source and relevant columns for processing.
    • A temporary column is created to store the calculated Set Rate values.
    • The input data is copied to a new DataFrame for manipulation.
  2. Date Filtering
    • The ‘Date’ column is converted to a datetime format to facilitate date-based operations.
    • The script calculates a date threshold, 30 days prior to the current date, to exclude recent data.
    • A filtered DataFrame is created, containing only data older than 30 days but within the last six months.
  3. Data Aggregation and Calculation
    • The filtered data is grouped by ‘Account’ and ‘Campaign’ to aggregate conversion metrics.
    • A custom function is defined to calculate the Set Rate by dividing the sum of “Set Lead” by “Assigned Elevated Qualified Response.”
    • The calculated Set Rate is formatted as a percentage and stored in the temporary column.
  4. Output Preparation
    • The final DataFrame is prepared by renaming the temporary column to the desired output column name.
    • The script prints the final DataFrame, displaying the calculated Set Rate for each campaign.

Vitals

  • Script ID : 525
  • Client ID / Customer ID: 1306924501 / 60269325
  • Action Type: Bulk Upload
  • Item Changed: Campaign
  • Output Columns: Account, Campaign, Set Rate (6 Month Lookback)
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: Byron Porter (bporter@marinsoftware.com)
  • Created by Byron Porter on 2023-11-13 20:48
  • Last Updated by Byron Porter on 2024-02-22 20:07
> 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

#
# Calculate Set Rate by aggregating six months of data (excluding the most recent 30 days) to perform the calculation
#  
#  
#
#
# Author: Byron Porter
# Date: 2023-10-16
#

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_DATE = 'Date'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN_STATUS = 'Campaign Status'
RPT_COL_SET_LEAD_SET30_CONV = 'Set Lead (Set30) Conv.'
RPT_COL_ASSIGNED_ELEVATED_QUALIFIED_RESPONSE_AEQR_CONV = 'Assigned Elevated Qualified Response (AEQR) Conv.'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_SET_RATE = 'Set Rate (6 Month Lookback)'




# temp column to house new Set Rate dimension value
TMP_SET_RATE = BULK_COL_SET_RATE + '_'
inputDf[TMP_SET_RATE] = np.nan


# Copy input dataframe to new data frame where calculation will be performed
newDf = inputDf.copy()

# Convert the 'Date' column in newDf to datetime format
newDf['Date'] = pd.to_datetime(newDf['Date'], format='%m/%d/%y')

# Calculate the most recent three days from the current date
today = pd.Timestamp.now().normalize()
thirty_days_latency = today - pd.DateOffset(days=31)

# Create the threedayDf DataFrame by filtering for rows within the most recent three days
adjustedDf = newDf[newDf['Date'] <= thirty_days_latency].copy()

# DEBUG: print out data frame to confirm the data set is correct (Last 180 days excluding last 30)
print("== Last 180 days EXCLUDING last 30 days ==", tableize(adjustedDf))

# aggregate the conversion data to calculate the Set Rate
# aggregate the conversion data to calculate the Set Rate
adjustedDf = adjustedDf.groupby([RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN]).agg({
    # Define aggregation functions for your columns
    RPT_COL_SET_LEAD_SET30_CONV: 'sum', 
    RPT_COL_ASSIGNED_ELEVATED_QUALIFIED_RESPONSE_AEQR_CONV: 'sum'


    # Add more columns as needed
}).reset_index()

# Define a custom aggregation function to calculate ROAS for 3 Day dataframe
def custom_division(series):
    if series[RPT_COL_ASSIGNED_ELEVATED_QUALIFIED_RESPONSE_AEQR_CONV]  != 0:
        return series[RPT_COL_SET_LEAD_SET30_CONV] / series[RPT_COL_ASSIGNED_ELEVATED_QUALIFIED_RESPONSE_AEQR_CONV]
    else:
        return 0 # or return np.nan
        
# Apply the custom aggregation function
adjustedDf[TMP_SET_RATE] = (adjustedDf.apply(custom_division, axis=1) * 100).round(2).astype(str) + '%'

print("== Set Rate Agg ==", tableize(adjustedDf))

outputDf = adjustedDf.copy() \
            .rename(columns = { \
                TMP_SET_RATE: BULK_COL_SET_RATE \
                })


print(outputDf[[RPT_COL_ACCOUNT, RPT_COL_CAMPAIGN, BULK_COL_SET_RATE]])

Post generated on 2024-11-27 06:58:46 GMT

comments powered by Disqus