Script 1117: Conversion Influencers

Purpose:

The script identifies and tags the top 10 keywords as “Top Performer” based on conversion count and cost per conversion 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 count and cost per conversion. It considers data from the last 60 days and requires each keyword to have at least one conversion to be eligible for consideration. The script distinguishes between “Brand” and “NonBrand” keywords, processing each category separately. It sorts the keywords by conversion count in descending order and cost per conversion in ascending order, filtering out those with a cost per conversion above a specified threshold. The top performers are then tagged as “Top Performer” in the dataset, allowing for easy identification and further analysis.

Walking Through the Code

  1. Initialization and Setup
    • The script begins by defining constants and importing necessary libraries. It sets up the data source and specifies the columns to be used for input and output.
    • User-changeable parameters include TOP_N, which determines the number of top performers to select, and COST_PER_CONV_THRESHOLD, which sets the maximum allowable cost per conversion for a keyword to be considered a top performer.
  2. Data Filtering and Preparation
    • The script clears the “Conversion Influencer” column in the input data and filters the dataset to include only rows with at least one conversion.
    • It separates the data into two dataframes: one for “Brand” keywords and another for “NonBrand” keywords.
  3. Tagging Top Performers
    • A function tag_top_performers is defined to sort and filter the keywords based on conversion count and cost per conversion. It tags the top N keywords as “Top Performer”.
    • This function is applied separately to the “Brand” and “NonBrand” dataframes.
  4. Updating and Outputting Data
    • The script updates the original input dataframe with the “Top Performer” tags for both “Brand” and “NonBrand” keywords.
    • It ensures that the necessary columns are included in the output dataframe and prints the first few rows 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 2025-03-11 01:25:51 GMT

comments powered by Disqus