Script 1117: Conversion Influencers

Purpose

The script identifies and tags the top 10 performing keywords based on conversion and cost per conversion metrics over the last 60 days.

To Elaborate

The Python script is designed to analyze keyword performance data and identify the top 10 keywords that are most effective in terms of conversion rates and cost efficiency over a 60-day period. It filters out keywords that have not achieved at least one conversion and further categorizes the data into ‘Brand’ and ‘NonBrand’ segments. The script then sorts these keywords by the number of conversions in descending order and cost per conversion in ascending order. Keywords that meet a specified cost per conversion threshold are tagged as “Top Performer.” This process helps in optimizing marketing strategies by focusing on keywords that deliver the best return on investment.

Walking Through the Code

  1. Initialization and Setup
    • The script begins by defining constants and importing necessary libraries.
    • It sets up the primary data source and specifies the columns to be used for input and output.
  2. User-Defined Parameters
    • TOP_N is set to 10, indicating the number of top-performing keywords to select.
    • COST_PER_CONV_THRESHOLD is set to 3.5, serving as the cost per conversion threshold for filtering.
  3. Data Preparation
    • The script clears the ‘Conversion Influencer’ column in the input DataFrame.
    • It filters the data to include only rows with at least one conversion.
  4. Data Segmentation
    • The data is divided into two separate DataFrames: brandDf for ‘Brand’ keywords and nonBrandDf for ‘NonBrand’ keywords.
  5. Tagging Top Performers
    • A function tag_top_performers is defined to sort and filter the DataFrame based on conversions and cost per conversion.
    • It tags the top N keywords as “Top Performer” if they meet the cost threshold.
  6. Updating the DataFrame
    • The script applies the tagging function to both ‘Brand’ and ‘NonBrand’ DataFrames.
    • It updates the original input DataFrame with the tagged top performers.
  7. Output Preparation
    • The script ensures that the necessary columns are included in the output DataFrame.
    • It prints the first few rows of the output to verify the structure.

Vitals

  • Script ID : 1117
  • Client ID / Customer ID: 1306913045 / 60268001
  • Action Type: Bulk Upload
  • Item Changed: Keyword
  • Output Columns: Account, Campaign, Group, Keyword, Match Type, Conversion Influencer, Brand vs NonBrand
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
  • Created by dwaidhas@marinsoftware.com on 2024-05-21 16:38
  • Last Updated by dwaidhas@marinsoftware.com on 2024-06-06 16:04
> 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

##
## name: Conversion Influencers
## description:
##  Populates Conversion Influencers dimension for the top 10 keywords based on Conv. and Cost/Conv.
##  - Conv. and Cost/Conv. over the last 60 days are considered
##  - At least 1 conversion needed to be considered
## 
## author: Dana Waidhas 
## created: 2024-05-20
## 

MATCH_TYPE = {
  'EXACT': 'exact',
  'PHRASE': 'phrase',
  'BROAD': 'broad',
}
today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# primary data source and columns
inputDf = dataSourceDict["1"]
RPT_COL_KEYWORD = 'Keyword'
RPT_COL_ACCOUNT = 'Account'
RPT_COL_CAMPAIGN = 'Campaign'
RPT_COL_GROUP = 'Group'
RPT_COL_MATCH_TYPE = 'Match Type'
RPT_COL_KEYWORD_ID = 'Keyword ID'
RPT_COL_STATUS = 'Status'
RPT_COL_CONV = 'Conv.'
RPT_COL_COST_PER_CONV = 'Cost/Conv. $'
RPT_COL_CONVERSION_INFLUENCER = 'Conversion Influencer'
RPT_COL_BRAND_VS_NONBRAND = 'Brand vs NonBrand'

# output columns and initial values
BULK_COL_ACCOUNT = 'Account'
BULK_COL_CAMPAIGN = 'Campaign'
BULK_COL_GROUP = 'Group'
BULK_COL_KEYWORD = 'Keyword'
BULK_COL_MATCH_TYPE = 'Match Type'
BULK_COL_KEYWORD_ID = 'Keyword ID'
BULK_COL_CONVERSION_INFLUENCER = 'Conversion Influencer'
BULK_COL_BRAND_VS_NONBRAND = 'Brand vs NonBrand'

# User code starts here

TOP_N = 10  # Number of top performers to select
COST_PER_CONV_THRESHOLD = 3.5  # Cost per conversion threshold

# Clear the Conversion Influencer column
inputDf[RPT_COL_CONVERSION_INFLUENCER] = np.nan

# Filter to include only rows with at least 1 conversion
inputDf = inputDf[inputDf[RPT_COL_CONV] > 0]

# Separate into Brand and NonBrand dataframes
brandDf = inputDf[inputDf[RPT_COL_BRAND_VS_NONBRAND] == 'Brand']
nonBrandDf = inputDf[inputDf[RPT_COL_BRAND_VS_NONBRAND] == 'NonBrand']

# Function to tag top performers
def tag_top_performers(df, top_n, conv_col, cost_col, tag_col):
    # Sort by Conv. in descending order and then by Cost/Conv. in ascending order
    sortedDf = df.sort_values(by=[conv_col, cost_col], ascending=[False, True])
    # Further filter to include only rows with Cost/Conv. < threshold
    filteredDf = sortedDf[sortedDf[cost_col] < COST_PER_CONV_THRESHOLD]
    # Tag the top N keywords as "Top Performer"
    topN = filteredDf.head(top_n).copy()
    topN[tag_col] = "Top Performer"
    return topN

# Tag top performers for Brand and NonBrand
topBrandPerformers = tag_top_performers(brandDf, TOP_N, RPT_COL_CONV, RPT_COL_COST_PER_CONV, RPT_COL_CONVERSION_INFLUENCER)
topNonBrandPerformers = tag_top_performers(nonBrandDf, TOP_N, RPT_COL_CONV, RPT_COL_COST_PER_CONV, RPT_COL_CONVERSION_INFLUENCER)

# Update the original input dataframe with the top performers
inputDf.loc[topBrandPerformers.index, RPT_COL_CONVERSION_INFLUENCER] = "Top Performer"
inputDf.loc[topNonBrandPerformers.index, RPT_COL_CONVERSION_INFLUENCER] = "Top Performer"

# Ensure the necessary columns are included in the output
outputDf = inputDf[[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_GROUP, BULK_COL_KEYWORD, BULK_COL_MATCH_TYPE, BULK_COL_KEYWORD_ID, BULK_COL_CONVERSION_INFLUENCER, BULK_COL_BRAND_VS_NONBRAND]]

# Verify the structure of the output
print(outputDf.head())

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

comments powered by Disqus