Script 1305: Conversion Influencers 60 Day

Purpose

The script identifies and tags the top 10 keywords as “Top Performer” based on cost per conversion over the last 60 days for each school program.

To Elaborate

The Python script is designed to analyze keyword performance data over the last 60 days, specifically focusing on the cost per conversion metric. It filters the data to include only those keywords that have achieved at least one conversion and have a cost per conversion of less than $75. The script then groups the data by school program and sorts each group by the number of conversions in descending order and cost per conversion in ascending order. The top 10 keywords from each group are tagged as “Top Performer” in the Conversion Influencer column. This process helps in identifying the most effective keywords in terms of cost efficiency and conversion volume, allowing for better budget allocation and optimization of marketing strategies.

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 that only effective keywords are considered.
  2. Filtering and Grouping
    • Further filtering is applied to retain only those rows where the cost per conversion is less than $75, focusing on cost-effective keywords.
    • The filtered data is grouped by the ‘School Program’ column, allowing for analysis within each program.
  3. Sorting and Tagging
    • Within each group, the data is sorted by the number of conversions in descending order and cost per conversion in ascending order.
    • The top 10 keywords from each sorted group are tagged as “Top Performer” in the ‘Conversion Influencer’ column.
  4. Output Preparation
    • The script selects specific columns for the final output DataFrame, ensuring that only relevant information is included.
    • The resulting DataFrame is printed, displaying the top-performing keywords for each school program.

Vitals

  • Script ID : 1305
  • Client ID / Customer ID: 1306926629 / 60270083
  • 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-07-30 19:13
  • Last Updated by dwaidhas@marinsoftware.com on 2024-08-01 18:33
> 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

##
## name: Scripts - Conversion Influencers - 60 Day
## description:
##  Populates Conversion Influencers dimension for the top 10 keywords based on cost/conv.
##  - Cost/Conv over the last 60 days is considered
##  - At least 1 conversion needed to be considered
## 
## author: Dana Waidhas
## 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_CONVERSION_INFLUENCER = 'Conversion Influencer'
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_CONVERSION_INFLUENCER = 'Conversion Influencer'
BULK_COL_SCHOOL_PROGRAM = 'School_Program'

# 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 75
filteredDf = inputDf[inputDf[RPT_COL_COST_PER_CONV] < 75]

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

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

for name, group in groupedDf:
    # Sort by Conv. in descending order and then by Cost/Conv. in ascending order for each group
    sortedGroup = group.sort_values(by=[RPT_COL_CONV, RPT_COL_COST_PER_CONV], ascending=[False, True])
    
    # Tag the top 10 keywords as "Top Performer"
    top10Group = sortedGroup.head(10).copy()
    top10Group[RPT_COL_CONVERSION_INFLUENCER] = "Top Performer"
    
    # Append to the output DataFrame
    outputDf = pd.concat([outputDf, top10Group], 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_CONVERSION_INFLUENCER, 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