Script 1079: Conversion Influencers

Purpose

The script identifies and tags the top 10 performing keywords based on conversion metrics and cost efficiency over the last 60 days.

To Elaborate

The Python script is designed to analyze keyword performance data and identify the top 10 keywords that are most effective in terms of conversions and cost per conversion over a 60-day period. It filters out keywords that have not achieved at least one conversion and further narrows down the selection to those with a cost per conversion of less than $60. The script then categorizes the keywords into brand and non-brand categories, sorts them by conversion count and cost efficiency, and tags the top 10 keywords in each category as “Top Performer.” This process helps in optimizing marketing strategies by focusing on the most cost-effective and high-performing keywords.

Walking Through the Code

  1. Initialization and Data Preparation
    • The script begins by clearing the ‘Conversion Influencer’ column in the input DataFrame to prepare for new data.
    • It filters the data to include only rows where the number of conversions is greater than zero, ensuring only relevant data is considered.
  2. Filtering and Categorization
    • Further filtering is applied to retain only those keywords with a cost per conversion of less than $60.
    • The filtered data is split into two separate DataFrames: one for brand keywords and another for non-brand keywords.
  3. Sorting and Selection
    • Each DataFrame is sorted by the number of conversions in descending order and by cost per conversion in ascending order.
    • The top 10 keywords from each sorted DataFrame are selected and tagged as “Top Performer.”
  4. Updating and Output
    • The original input DataFrame is updated with the “Top Performer” tags for the selected keywords.
    • A final output DataFrame is created, containing specified columns, and is printed to display the results.

Vitals

  • Script ID : 1079
  • Client ID / Customer ID: 1306927757 / 60270153
  • Action Type: Bulk Upload
  • Item Changed: Keyword
  • Output Columns: Account, Campaign, Group, Keyword, Match Type, Conversion Influencer
  • Linked Datasource: M1 Report
  • Reference Datasource: None
  • Owner: dwaidhas@marinsoftware.com (dwaidhas@marinsoftware.com)
  • Created by dwaidhas@marinsoftware.com on 2024-05-13 20:18
  • Last Updated by dwaidhas@marinsoftware.com on 2024-06-12 15:00
> 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: Conversion Influencers
## description:
##  Populates Conversion Influencers dimension for the top 10 keywords based on Conv. and Cost/Conv.
##  - Conv. and Cost/Conv. over the last 60 days are considered
##  - At least 1 conversion needed to be considered
## 
## author: Dana Waidhas 
## created: 2024-05-13
## 

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_CONVERSION_INFLUENCER = 'Conversion Influencer'
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_CONVERSION_INFLUENCER = 'Conversion Influencer'
BULK_COL_BRAND_VS_NONBRAND = 'Brand vs NonBrand'

# User code starts here

# Clear the Conversion Influencer column
inputDf[RPT_COL_CONVERSION_INFLUENCER] = np.nan

# Filter to include only rows with at least 1 conversion
inputDf = inputDf[inputDf[RPT_COL_CONV] > 0]

# Further filter to include only rows with Cost/Conv. $ smaller than 60
filteredDf = inputDf[inputDf[RPT_COL_COST_PER_CONV] < 60]

# Split the dataframe into brand and nonbrand keywords
brandDf = filteredDf[filteredDf[RPT_COL_BRAND_VS_NONBRAND] == 'Brand']
nonbrandDf = filteredDf[filteredDf[RPT_COL_BRAND_VS_NONBRAND] == 'NonBrand']

# Debugging: print the number of rows in each subset
print(f"Number of brand keywords: {len(brandDf)}")
print(f"Number of nonbrand keywords: {len(nonbrandDf)}")

# Sort by Conv. in descending order and then by Cost/Conv. in ascending order for both dataframes
sortedBrandDf = brandDf.sort_values(by=[RPT_COL_CONV, RPT_COL_COST_PER_CONV], ascending=[False, True])
sortedNonbrandDf = nonbrandDf.sort_values(by=[RPT_COL_CONV, RPT_COL_COST_PER_CONV], ascending=[False, True])

# Debugging: print the top 10 rows of each sorted dataframe
print("Top 10 brand keywords:\n", sortedBrandDf.head(10).to_string(index=False))
print("Top 10 nonbrand keywords:\n", sortedNonbrandDf.head(10).to_string(index=False))

# Tag the top 10 keywords as "Top Performer" for both dataframes
top10BrandDf = sortedBrandDf.head(10).copy()
top10BrandDf[RPT_COL_CONVERSION_INFLUENCER] = "Top Performer"

top10NonbrandDf = sortedNonbrandDf.head(10).copy()
top10NonbrandDf[RPT_COL_CONVERSION_INFLUENCER] = "Top Performer"

# Update the original input dataframe with the top performers
inputDf.loc[top10BrandDf.index, RPT_COL_CONVERSION_INFLUENCER] = "Top Performer"
inputDf.loc[top10NonbrandDf.index, RPT_COL_CONVERSION_INFLUENCER] = "Top Performer"

# 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_CONVERSION_INFLUENCER, BULK_COL_BRAND_VS_NONBRAND]]

# 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