Script 1149: Underperforming Keywords

Purpose

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

To Elaborate

The Python script is designed to analyze a dataset of keywords used in advertising campaigns, specifically focusing on their performance metrics. It categorizes the keywords into “Brand” and “NonBrand” groups and evaluates their performance based on conversion rates and cost per conversion. The script identifies the 10 most underperforming keywords in each category by comparing their conversion metrics against the median values. Keywords with conversion rates below the median and cost per conversion above the median are considered underperforming. These keywords are then tagged as “Underperformer” to highlight them for further analysis or action. The script ultimately outputs a refined dataset that includes the performance information for each keyword, allowing marketers to make informed decisions about their advertising strategies.

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 interferes with the current analysis.
    • It then separates the input DataFrame into two subsets: one for “Brand” keywords and another for “NonBrand” keywords, based on the “Brand vs NonBrand” column.
  2. Identifying Underperformers
    • A function tag_underperformers is defined to process each subset of data. It calculates the median conversion rate and cost per conversion for the given DataFrame.
    • The function identifies underperforming keywords as those with conversion rates below or equal to the median and costs per conversion above the median.
    • These keywords are sorted to prioritize those with the lowest conversion rates and highest costs, and the top 10 underperformers are tagged.
  3. 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 “Underperformer” tags for the identified keywords.
    • Finally, it constructs an output DataFrame containing relevant columns and prints it, providing a clear view of the underperforming keywords.

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 2024-11-27 06:58:46 GMT

comments powered by Disqus