Script 1149: Underperforming Keywords

Purpose:

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

To Elaborate

The script is designed to analyze keyword performance data and identify the 10 most underperforming keywords in both “Brand” and “NonBrand” categories. It uses conversion metrics to determine underperformance, specifically focusing on keywords with conversion rates below the median and cost per conversion above the median. The script separates the data into “Brand” and “NonBrand” categories, calculates median values for conversions and cost per conversion, and tags keywords that fall below these thresholds as “Underperformer.” This tagging helps in identifying keywords that may require optimization or removal to improve overall campaign performance.

Walking Through the Code

  1. Initialization and Data Preparation
    • The script begins by clearing the “Performance Info” column in the input DataFrame to prepare for new data.
    • It separates the input data into two DataFrames: brandDf for “Brand” keywords and nonBrandDf for “NonBrand” keywords.
  2. Function Definition for Tagging Underperformers
    • The tag_underperformers function calculates median values for conversions and cost per conversion within a given DataFrame.
    • It identifies underperforming keywords as those with conversion rates below the median and cost per conversion above the median.
    • The function sorts these keywords to find the top 10 underperformers, tagging them with “Underperformer” in the “Performance Info” column.
  3. Tagging Underperformers in Brand and NonBrand Categories
    • The script applies the tag_underperformers function to both brandDf and nonBrandDf.
    • It updates the original input DataFrame with the tagged underperformers.
  4. Output Preparation
    • The script prepares an output DataFrame that includes all keywords with specified columns, including the updated “Performance Info.”
    • Finally, it prints the output DataFrame for review.

Vitals

  • Script ID : 1149
  • Client ID / Customer ID: 1306927757 / 60270153
  • Action Type: Bulk Upload
  • Item Changed: Keyword
  • Output Columns: Account, Campaign, Group, Keyword, Match Type, Brand vs NonBrand, Performance Info
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
  • Created by dwaidhas@marinsoftware.com on 2024-05-24 16:33
  • Last Updated by dwaidhas@marinsoftware.com on 2024-06-12 14:37
> 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

##
## 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] = ""

# 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