Script 1151: Underperforming Keywords

Purpose:

The script identifies and tags the 10 most underperforming keywords in both “Brand” and “NonBrand” categories based on conversion metrics.

To Elaborate

The Python script is designed to analyze keyword performance data and identify the 10 most underperforming keywords within two categories: “Brand” and “NonBrand.” The script uses conversion metrics to determine underperformance, specifically focusing on keywords with conversion rates below the median and cost per conversion above the median. By tagging these keywords as “Underperformer,” the script helps marketers quickly identify areas that may require optimization or further investigation to improve advertising efficiency and effectiveness.

Walking Through the Code

  1. Initialization and Data Preparation
    • The script begins by defining a dictionary for match types and setting up the primary data source.
    • It clears the “Performance Info” column in the input DataFrame to prepare for new data.
    • Filters the data to include only rows with at least one conversion, ensuring that only relevant data is analyzed.
  2. Data Segmentation
    • The script separates the input DataFrame into two subsets: one for “Brand” keywords and another for “NonBrand” keywords.
    • This segmentation allows for independent analysis of each category.
  3. Underperformer Identification
    • A function tag_underperformers is defined to calculate the median conversion and cost per conversion for a given DataFrame.
    • It identifies underperforming keywords as those with conversions below or equal to the median and cost per conversion above the median.
    • The function sorts these keywords and tags the top 10 as “Underperformer.”
  4. Tagging and Output
    • The script applies the tag_underperformers function to both the “Brand” and “NonBrand” DataFrames.
    • It updates the original input DataFrame with the identified underperformers.
    • Finally, it prepares an output DataFrame with selected columns and prints it, providing a clear view of the underperforming keywords.

Vitals

  • Script ID : 1151
  • Client ID / Customer ID: 1306927029 / 60270153
  • Action Type: Bulk Upload
  • Item Changed: Keyword
  • Output Columns: Account, Campaign, Group, Keyword, Match Type, Performance Info, 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-24 16:46
  • Last Updated by dwaidhas@marinsoftware.com on 2024-05-29 20: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
82
83

##
## name: Underperforming Keywords
## description: This script identifies and tags the 10 most underperforming keywords within both the "Brand" and "NonBrand" categories based on their conversion metrics.
##  
## 
## author: Dana Waidhas 
## created: 2024-05-24
## 

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_STATUS = 'Status'
RPT_COL_CONV = 'Conv.'
RPT_COL_COST_PER_CONV = 'Cost/Conv. $'
RPT_COL_PERFORMANCE_INFO = 'Performance Info'
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_BRAND_VS_NONBRAND = 'Brand vs NonBrand'
BULK_COL_PERFORMANCE_INFO = 'Performance Info'

# user code start here

# Assign current date to a parameter
today = datetime.datetime.now(CLIENT_TIMEZONE).date()

# Clear the Performance Info column
inputDf[RPT_COL_PERFORMANCE_INFO] = ""

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

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

def tag_underperformers(df):
    # Calculate median Conv. and Cost/Conv.
    median_conv = df[RPT_COL_CONV].median()
    median_cost_per_conv = df[RPT_COL_COST_PER_CONV].median()
    
    # Identify underperforming keywords
    underperformingDf = df[(df[RPT_COL_CONV] <= median_conv) & (df[RPT_COL_COST_PER_CONV] > median_cost_per_conv)].copy()
    
    # Sort by Conv. ascending and then by Cost/Conv. descending to get the most underperforming keywords
    underperformingDf = underperformingDf.sort_values(by=[RPT_COL_CONV, RPT_COL_COST_PER_CONV], ascending=[True, False])
    
    # Tag the top 10 underperformers
    top10UnderperformingDf = underperformingDf.head(10).copy()
    top10UnderperformingDf[RPT_COL_PERFORMANCE_INFO] = "Underperformer"
    
    return top10UnderperformingDf

# Tag underperformers for both Brand and NonBrand
top10BrandUnderperformers = tag_underperformers(brandDf)
top10NonBrandUnderperformers = tag_underperformers(nonBrandDf)

# Update the original input dataframe with the underperforming keywords
inputDf.loc[top10BrandUnderperformers.index, RPT_COL_PERFORMANCE_INFO] = "Underperformer"
inputDf.loc[top10NonBrandUnderperformers.index, RPT_COL_PERFORMANCE_INFO] = "Underperformer"

# Output dataframe includes all keywords with the specified columns
outputDf = inputDf[[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_GROUP, BULK_COL_KEYWORD, BULK_COL_MATCH_TYPE, BULK_COL_BRAND_VS_NONBRAND, BULK_COL_PERFORMANCE_INFO]]

# Print the output DataFrame
print("outputDf\n", outputDf.to_string(index=False))

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

comments powered by Disqus