Skip to content

Search Keyword Generation Tool

  • Used to create a search engine marketing campaign taxonomy
  • Can be easily repurposed by updating the 'words' and 'products' dataframes
# List of words to pair with products
words = ('purchase', 'buy', '2023', 'shop', 'tickets', 'weekend')

print(words)

products = ['nascar', 'nascar cup series', 'cup series', 'xfinity series']

# Create an empty list
keywords_list = []

# Loop through products
for product in products:
    # Loop through words
    for word in words:
        # Append combinations
        keywords_list.append([product, product + ' ' + word])
        keywords_list.append([product, word + ' ' +  product])

# Inspect keyword list
from pprint import pprint
pprint(keywords_list)
# Load library
import pandas as pd

# Create a DataFrame from list
keywords_df = pd.DataFrame.from_records(keywords_list)

# Print the keywords DataFrame to explore it
keywords_df.head()
# Rename the columns of the DataFrame
keywords_df = keywords_df.rename(columns={0:'Ad Group', 1:'Keyword'})
keywords_df
# Add a campaign column
keywords_df['Campaign']='NASCAR Tickets'
keywords_df
# Add a criterion type column
keywords_df['Criterion Type']='Exact'
# Make a copy of the keywords DataFrame
keywords_phrase = keywords_df.copy()

# Change criterion type match to phrase
keywords_phrase['Criterion Type']='Phrase'

# Append the DataFrames
keywords_df_final = keywords_df.append(keywords_phrase)

keywords_df_final
# Save the final keywords to a CSV file
keywords_df_final.to_csv('keywords.csv',index=False)

# View a summary of our campaign work
summary = keywords_df_final.groupby(['Ad Group', 'Criterion Type'])['Keyword'].count()
print(summary)
import pandas as pd

# Read the CSV file into a DataFrame
ironworks_leads = pd.read_csv('./leads-20231227-20433-wbupz6.csv')

# Convert the 'created' column to datetime format
ironworks_leads['created'] = pd.to_datetime(ironworks_leads['created'])

# Create a new DataFrame with records created on or after 09/01/2023
ironworks_leads_last_90 = ironworks_leads[ironworks_leads['created'] >= '2023-09-01']

ironworks_leads_last_90
Spinner
DataFrameas
df3
variable
SELECT
	COUNT(*)
FROM ironworks_leads_last_90;
Spinner
DataFrameas
df
variable
WITH leads_by_campaign_input_20 AS(
	SELECT 
    DISTINCT(campaign) AS campaign_name,
    COUNT(lead_id) AS total_leads
	FROM 
    ironworks_leads_last_90
	WHERE payload LIKE '%input_20%'
	GROUP BY campaign
	ORDER BY total_leads DESC
),


leads_by_campaign_no_input_20 AS(
	SELECT 
    DISTINCT(campaign) AS campaign_name,
    COUNT(lead_id) AS total_leads
	FROM 
    ironworks_leads_last_90
	WHERE payload NOT LIKE '%input_20%'
	GROUP BY campaign
	ORDER BY total_leads DESC
)

SELECT 
	input_20.campaign_name,
	SUM(input_20.total_leads) AS leads_with_input_20,
	SUM(no_input_20.total_leads) AS leads_with_no_input_20,
	CONCAT(ROUND((SUM(no_input_20.total_leads) / (SUM(no_input_20.total_leads) + SUM(input_20.total_leads))) * 100, 2), '%') AS pct_no_input_20
FROM leads_by_campaign_input_20 AS input_20
JOIN leads_by_campaign_no_input_20 AS no_input_20
	ON input_20.campaign_name = no_input_20.campaign_name
GROUP BY input_20.campaign_name
ORDER by leads_with_no_input_20 DESC;
Spinner
DataFrameas
df1
variable
WITH leads_by_campaign_utm AS(
	SELECT 
    DISTINCT(campaign) AS campaign_name,
    COUNT(lead_id) AS total_leads
	FROM 
    ironworks_leads_last_90
	WHERE payload LIKE '%utm_term%'
	GROUP BY campaign
	ORDER BY total_leads DESC
),


leads_by_campaign_no_utm AS(
	SELECT 
    DISTINCT(campaign) AS campaign_name,
    COUNT(lead_id) AS total_leads
	FROM 
    ironworks_leads_last_90
	WHERE payload NOT LIKE '%utm_term%'
	GROUP BY campaign
	ORDER BY total_leads DESC
)

SELECT 
	SUM(utm.total_leads) AS leads_with_utm,
	SUM(no_utm.total_leads) AS leads_with_no_utm,
	CONCAT(ROUND((SUM(no_utm.total_leads) / (SUM(no_utm.total_leads) + SUM(utm.total_leads))) * 100, 2), '%') AS pct_no_utm
FROM leads_by_campaign_utm AS utm
JOIN leads_by_campaign_no_utm AS no_utm
	ON utm.campaign_name = no_utm.campaign_name;
SELECT 
	COUNT(*)
FROM ironworks_leads_last_90
WHERE payload LIKE '%utm_term%' AND payload LIKE '%&rl_retarget=1%';
Spinner
DataFrameas
df2
variable
SELECT 
    SUBSTRING(payload, 
              POSITION('rl_key' IN payload) + LENGTH('rl_key') + 1, 
              POSITION('&cpn' IN payload) - POSITION('rl_key' IN payload) - LENGTH('rl_key') - 1) AS rl_key_value
FROM ironworks_leads_last_90
WHERE payload LIKE '%rl_key%'
ORDER BY rl_key_value;