Script 1307: Underperforming Keywords

Purpose:

This 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 within advertising campaigns, specifically focusing on identifying keywords that are not performing well in terms of conversions. It categorizes keywords into “Brand” and “NonBrand” groups and evaluates their performance using conversion metrics. The script calculates the median conversion rate and cost per conversion for each keyword, and identifies those that have a conversion rate below the median and a cost per conversion above the median. These keywords are considered underperforming. The script then sorts these underperforming keywords to find the top 10 most underperforming ones, tagging them accordingly. This process is repeated for each school program, allowing for targeted optimization of advertising efforts by highlighting keywords that may require adjustments or removal to improve overall campaign efficiency.

Walking Through the Code

  1. Data Preparation
    • The script begins by defining constants for match types and initializes the primary data source.
    • It clears the “Performance Info” column in the input DataFrame to prepare for new data.
  2. Function Definition
    • A function tag_underperformers is defined to identify underperforming keywords.
    • It calculates the median conversion rate and cost per conversion, then filters keywords that perform below these medians.
    • The filtered keywords are sorted to identify the top 10 underperformers, which are tagged as “Underperformer”.
  3. Grouping and Processing
    • The input DataFrame is grouped by “School Program”.
    • For each group, the script applies the tag_underperformers function to identify and tag underperforming keywords.
  4. Output Preparation
    • The results are compiled into an output DataFrame, selecting specific columns for final output.
    • The script concludes by printing the output DataFrame, showcasing the tagged underperforming keywords.

Vitals

  • Script ID : 1307
  • Client ID / Customer ID: 1306926629 / 60270083
  • Action Type: Bulk Upload
  • Item Changed: Keyword
  • Output Columns: Account, Campaign, Group, Keyword, Match Type, Performance Info
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
  • Created by dwaidhas@marinsoftware.com on 2024-07-30 19:33
  • Last Updated by dwaidhas@marinsoftware.com on 2024-07-30 19:36
> 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: 
## created: 2024-07-30
## 

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_SCHOOL_PROGRAM = 'School_Program'

# 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_PERFORMANCE_INFO = 'Performance Info'
BULK_COL_SCHOOL_PROGRAM = 'School_Program'
outputDf[BULK_COL_PERFORMANCE_INFO] = "<<YOUR VALUE>>"

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

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

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

# Group by School Program
groupedDf = inputDf.groupby(RPT_COL_SCHOOL_PROGRAM)

# Initialize an empty DataFrame to store results
outputDf = pd.DataFrame()

for name, group in groupedDf:
    # Tag underperformers for each School Program
    top10Underperformers = tag_underperformers(group)
    
    # Append to the output DataFrame
    outputDf = pd.concat([outputDf, top10Underperformers], ignore_index=True)

# Select the specified columns for the output DataFrame
outputDf = outputDf[[BULK_COL_ACCOUNT, BULK_COL_CAMPAIGN, BULK_COL_GROUP, BULK_COL_KEYWORD, BULK_COL_MATCH_TYPE, BULK_COL_PERFORMANCE_INFO, BULK_COL_SCHOOL_PROGRAM]]

# 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