A digital marketing agency, and the agency is approached by a massive online retailer of furniture. The task is to generate keywords for the following products:
# List of words to pair with products
words = ['buy', 'price', 'discount', 'promotion', 'promo', 'shop']
# Print list of words
print(words)['buy', 'price', 'discount', 'promotion', 'promo', 'shop']Generate keyword combinations
buy recliners
recliners buy
price recliners
recliners price
…
and so on for all the words and products that we have.
products = ['sofas', 'convertible sofas', 'love seats', 'recliners', 'sofa beds']
# 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)[['sofas', 'sofas buy'],
['sofas', 'buy sofas'],
['sofas', 'sofas price'],
['sofas', 'price sofas'],
['sofas', 'sofas discount'],
['sofas', 'discount sofas'],
['sofas', 'sofas promotion'],
['sofas', 'promotion sofas'],
['sofas', 'sofas promo'],
['sofas', 'promo sofas'],
['sofas', 'sofas shop'],
['sofas', 'shop sofas'],
['convertible sofas', 'convertible sofas buy'],
['convertible sofas', 'buy convertible sofas'],
['convertible sofas', 'convertible sofas price'],
['convertible sofas', 'price convertible sofas'],
['convertible sofas', 'convertible sofas discount'],
['convertible sofas', 'discount convertible sofas'],
['convertible sofas', 'convertible sofas promotion'],
['convertible sofas', 'promotion convertible sofas'],
['convertible sofas', 'convertible sofas promo'],
['convertible sofas', 'promo convertible sofas'],
['convertible sofas', 'convertible sofas shop'],
['convertible sofas', 'shop convertible sofas'],
['love seats', 'love seats buy'],
['love seats', 'buy love seats'],
['love seats', 'love seats price'],
['love seats', 'price love seats'],
['love seats', 'love seats discount'],
['love seats', 'discount love seats'],
['love seats', 'love seats promotion'],
['love seats', 'promotion love seats'],
['love seats', 'love seats promo'],
['love seats', 'promo love seats'],
['love seats', 'love seats shop'],
['love seats', 'shop love seats'],
['recliners', 'recliners buy'],
['recliners', 'buy recliners'],
['recliners', 'recliners price'],
['recliners', 'price recliners'],
['recliners', 'recliners discount'],
['recliners', 'discount recliners'],
['recliners', 'recliners promotion'],
['recliners', 'promotion recliners'],
['recliners', 'recliners promo'],
['recliners', 'promo recliners'],
['recliners', 'recliners shop'],
['recliners', 'shop recliners'],
['sofa beds', 'sofa beds buy'],
['sofa beds', 'buy sofa beds'],
['sofa beds', 'sofa beds price'],
['sofa beds', 'price sofa beds'],
['sofa beds', 'sofa beds discount'],
['sofa beds', 'discount sofa beds'],
['sofa beds', 'sofa beds promotion'],
['sofa beds', 'promotion sofa beds'],
['sofa beds', 'sofa beds promo'],
['sofa beds', 'promo sofa beds'],
['sofa beds', 'sofa beds shop'],
['sofa beds', 'shop sofa beds']]Convert this list of lists into a DataFrame.
# 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
print(keywords_df) 0 1
0 sofas sofas buy
1 sofas buy sofas
2 sofas sofas price
3 sofas price sofas
4 sofas sofas discount
5 sofas discount sofas
6 sofas sofas promotion
7 sofas promotion sofas
8 sofas sofas promo
9 sofas promo sofas
10 sofas sofas shop
11 sofas shop sofas
12 convertible sofas convertible sofas buy
13 convertible sofas buy convertible sofas
14 convertible sofas convertible sofas price
15 convertible sofas price convertible sofas
16 convertible sofas convertible sofas discount
17 convertible sofas discount convertible sofas
18 convertible sofas convertible sofas promotion
19 convertible sofas promotion convertible sofas
20 convertible sofas convertible sofas promo
21 convertible sofas promo convertible sofas
22 convertible sofas convertible sofas shop
23 convertible sofas shop convertible sofas
24 love seats love seats buy
25 love seats buy love seats
26 love seats love seats price
27 love seats price love seats
28 love seats love seats discount
29 love seats discount love seats
30 love seats love seats promotion
31 love seats promotion love seats
32 love seats love seats promo
33 love seats promo love seats
34 love seats love seats shop
35 love seats shop love seats
36 recliners recliners buy
37 recliners buy recliners
38 recliners recliners price
39 recliners price recliners
40 recliners recliners discount
41 recliners discount recliners
42 recliners recliners promotion
43 recliners promotion recliners
44 recliners recliners promo
45 recliners promo recliners
46 recliners recliners shop
47 recliners shop recliners
48 sofa beds sofa beds buy
49 sofa beds buy sofa beds
50 sofa beds sofa beds price
51 sofa beds price sofa beds
52 sofa beds sofa beds discount
53 sofa beds discount sofa beds
54 sofa beds sofa beds promotion
55 sofa beds promotion sofa beds
56 sofa beds sofa beds promo
57 sofa beds promo sofa beds
58 sofa beds sofa beds shop
59 sofa beds shop sofa beds
Give the columns meaningful names.
# Rename the columns of the DataFrame
keywords_df = keywords_df.rename(columns={0 : 'Ad Group',
1 : 'Keyword'})
Add new column called Campaign for the campaign name.
# Add a campaign column
keywords_df['Campaign'] = 'SEM_Sofas'
There are different keyword match types. One is exact match, which is for matching the exact term or are close variations of that exact term. Another match type is broad match, which means ads may show on searches that include misspellings, synonyms, related searches, and other relevant variations.
Straight from Google’s AdWords documentation:
# Add a criterion type column
keywords_df['Criterion Type'] = 'Exact'
keywords_df.head()| Ad Group | Keyword | Campaign | Criterion Type | |
|---|---|---|---|---|
| 0 | sofas | sofas buy | SEM_Sofas | Exact |
| 1 | sofas | buy sofas | SEM_Sofas | Exact |
| 2 | sofas | sofas price | SEM_Sofas | Exact |
| 3 | sofas | price sofas | SEM_Sofas | Exact |
| 4 | sofas | sofas discount | SEM_Sofas | Exact |
# Make a copy of the keywords DataFrame
keywords_phrase = keywords_df.copy()
# Change criterion type match to phrase
keywords_phrase['Criterion Type'] = 'Phrase'
#keywords_phrase.head()
# Append the DataFrames
keywords_df_final = keywords_df.append(keywords_phrase)
keywords_df_final.head()| Ad Group | Keyword | Campaign | Criterion Type | |
|---|---|---|---|---|
| 0 | sofas | sofas buy | SEM_Sofas | Exact |
| 1 | sofas | buy sofas | SEM_Sofas | Exact |
| 2 | sofas | sofas price | SEM_Sofas | Exact |
| 3 | sofas | price sofas | SEM_Sofas | Exact |
| 4 | sofas | sofas discount | SEM_Sofas | Exact |
Create CSV file to upload campaign. Then we will be able to import it to AdWords editor or BingAds editor. There is also the option of pasting the data into the editor if we want, but having easy access to the saved data is great so let’s save to a CSV file!
Campaign summary is good now that the keyword work is wrapped up. This summary shows the assigned specific keywords to specific ad groups, which are each part of a campaign. Google (or Bing, etc.) that any of the words in each ad group to trigger one of the ads in the same ad group.
| Campaign | Ad Group | Headline 1 | Headline 2 | Description | Final URL |
|---|---|---|---|---|---|
| SEM_Sofas | Sofas | Looking for Quality Sofas? | Explore Our Massive Collection | 30-day Returns With Free Delivery Within the US. Start Shopping Now | DataCampSofas.com/sofas |
| SEM_Sofas | Sofas | Looking for Affordable Sofas? | Check Out Our Weekly Offers | 30-day Returns With Free Delivery Within the US. Start Shopping Now | DataCampSofas.com/sofas |
| SEM_Sofas | Recliners | Looking for Quality Recliners? | Explore Our Massive Collection | 30-day Returns With Free Delivery Within the US. Start Shopping Now | DataCampSofas.com/recliners |
| SEM_Sofas | Recliners | Need Affordable Recliners? | Check Out Our Weekly Offers | 30-day Returns With Free Delivery Within the US. Start Shopping Now | DataCampSofas.com/recliners |
Together, these tables get us the sample keywords -> ads -> landing pages mapping shown in the diagram below.
# 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)Ad Group Criterion Type convertible sofas Exact 12 Phrase 12 love seats Exact 12 Phrase 12 recliners Exact 12 Phrase 12 sofa beds Exact 12 Phrase 12 sofas Exact 12 Phrase 12 Name: Keyword, dtype: int64