Script 1147: 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 evaluates keywords based on their conversion metrics, specifically focusing on the number of conversions and the cost per conversion. By calculating the median values for these metrics, the script determines which keywords are performing below average. It then tags these underperforming keywords, allowing businesses to easily identify and address areas where their marketing efforts may not be yielding the desired results. This process helps in optimizing marketing strategies by focusing on improving or eliminating ineffective keywords.

Walking Through the Code

  1. Data Preparation
    • The script begins by clearing the “Performance Info” column in the input DataFrame to ensure no previous data affects the current analysis.
    • It filters the data to include only rows where there is at least one conversion, ensuring that only relevant data is analyzed.
  2. Data Segmentation
    • The script separates the input DataFrame into two distinct DataFrames: one for “Brand” keywords and another for “NonBrand” keywords. This segmentation allows for targeted analysis within each category.
  3. Underperformer Identification
    • A function tag_underperformers is defined to identify underperforming keywords. It calculates the median conversion and cost per conversion for the given DataFrame.
    • Keywords with conversions less than or equal to the median and cost per conversion greater than the median are considered underperforming.
    • The function sorts these keywords to prioritize those with the lowest conversions and highest costs, tagging the top 10 as “Underperformer.”
  4. Updating and Outputting Data
    • 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 containing all keywords with their respective performance tags and prints it.

Vitals

  • Script ID : 1147
  • Client ID / Customer ID: 1306927027 / 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:09
  • Last Updated by dwaidhas@marinsoftware.com on 2024-05-29 20:05
> 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 2024-11-27 06:58:46 GMT

comments powered by Disqus