Script 1307: 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 “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 processes data grouped by “School Program” and tags the identified underperforming keywords with a specific label. This helps in optimizing marketing strategies by highlighting keywords that are not yielding expected results, allowing for targeted improvements or adjustments in advertising campaigns.

Walking Through the Code

  1. Data Preparation
    • The script begins by defining constants for match types and initializes the primary data source.
    • It sets up the input DataFrame inputDf with necessary columns for analysis, including keywords, account details, and performance metrics.
  2. Tagging Underperformers
    • A function tag_underperformers is defined to calculate the median conversion and cost per conversion.
    • It identifies underperforming keywords by selecting those with conversion rates below the median and cost per conversion above the median.
    • The function sorts these keywords and tags the top 10 underperformers with “Underperformer” in the performance info column.
  3. Grouping and Output
    • The script groups the data by “School Program” and initializes an empty DataFrame outputDf to store results.
    • For each group, it applies the tag_underperformers function and appends the results to outputDf.
    • Finally, it selects specific columns for the output and prints the resulting DataFrame, highlighting the 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 2024-11-27 06:58:46 GMT

comments powered by Disqus