1. The brief
Imagine working for a digital marketing agency, and the agency is approached by a massive online retailer of furniture. They want to test our skills at creating large campaigns for all of their website. We are tasked with creating a prototype set of keywords for search campaigns for their sofas section. The client says that they want us to generate keywords for the following products:
- sofas
- convertible sofas
- love seats
- recliners
- sofa beds
The brief: The client is generally a low-cost retailer, offering many promotions and discounts. We will need to focus on such keywords. We will also need to move away from luxury keywords and topics, as we are targeting price-sensitive customers. Because we are going to be tight on budget, it would be good to focus on a tightly targeted set of keywords and make sure they are all set to exact and phrase match.
Based on the brief above we will first need to generate a list of words, that together with the products given above would make for good keywords. Here are some examples:
- Products: sofas, recliners
- Words: buy, prices
The resulting keywords: 'buy sofas', 'sofas buy', 'buy recliners', 'recliners buy', 'prices sofas', 'sofas prices', 'prices recliners', 'recliners prices'.
As a final result, we want to have a DataFrame that looks like this:
| Campaign | Ad Group | Keyword | Criterion Type |
|---|---|---|---|
| Campaign1 | AdGroup_1 | keyword 1a | Exact |
| Campaign1 | AdGroup_1 | keyword 1a | Phrase |
| Campaign1 | AdGroup_1 | keyword 1b | Exact |
| Campaign1 | AdGroup_1 | keyword 1b | Phrase |
| Campaign1 | AdGroup_2 | keyword 2a | Exact |
| Campaign1 | AdGroup_2 | keyword 2a | Phrase |
The first step is to come up with a list of words that users might use to express their desire in buying low-cost sofas.
# List of words to pair with products
words = ['buy','price','discount','promotion','promo','shop','budget','ecommerce','offers','sale']
# Print list of words
print(words)
['buy', 'price', 'discount', 'promotion', 'promo', 'shop', 'budget', 'ecommerce', 'offers', 'sale']
2. Combine the words with the product names
Imagining all the possible combinations of keywords can be stressful! But not for us, because we are keyword ninjas! We know how to translate campaign briefs into Python data structures and can imagine the resulting DataFrames that we need to create.
Now that I have brainstormed the words that work well with the brief that I received, it is now time to combine them with the product names to generate meaningful search keywords. I want to combine every word with every product once before, and once after, as seen in the example above.
As a quick reminder, for the product 'recliners' and the words 'buy' and 'price' for example, we would want to generate the following 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'],
['sofas', 'sofas budget'],
['sofas', 'budget sofas'],
['sofas', 'sofas ecommerce'],
['sofas', 'ecommerce sofas'],
['sofas', 'sofas offers'],
['sofas', 'offers sofas'],
['sofas', 'sofas sale'],
['sofas', 'sale 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'],
['convertible sofas', 'convertible sofas budget'],
['convertible sofas', 'budget convertible sofas'],
['convertible sofas', 'convertible sofas ecommerce'],
['convertible sofas', 'ecommerce convertible sofas'],
['convertible sofas', 'convertible sofas offers'],
['convertible sofas', 'offers convertible sofas'],
['convertible sofas', 'convertible sofas sale'],
['convertible sofas', 'sale 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'],
['love seats', 'love seats budget'],
['love seats', 'budget love seats'],
['love seats', 'love seats ecommerce'],
['love seats', 'ecommerce love seats'],
['love seats', 'love seats offers'],
['love seats', 'offers love seats'],
['love seats', 'love seats sale'],
['love seats', 'sale 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'],
['recliners', 'recliners budget'],
['recliners', 'budget recliners'],
['recliners', 'recliners ecommerce'],
['recliners', 'ecommerce recliners'],
['recliners', 'recliners offers'],
['recliners', 'offers recliners'],
['recliners', 'recliners sale'],
['recliners', 'sale 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'],
['sofa beds', 'sofa beds budget'],
['sofa beds', 'budget sofa beds'],
['sofa beds', 'sofa beds ecommerce'],
['sofa beds', 'ecommerce sofa beds'],
['sofa beds', 'sofa beds offers'],
['sofa beds', 'offers sofa beds'],
['sofa beds', 'sofa beds sale'],
['sofa beds', 'sale sofa beds']]
3. Convert the list of lists into a DataFrame
Now I want to convert this list of lists into a DataFrame so I can easily manipulate it and manage the final output.
# Load library
import pandas as pd
# Create a DataFrame from list
#keywords_df = pd.from_records(keywords_list)
keywords_df = pd.DataFrame(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 sofas sofas budget
13 sofas budget sofas
14 sofas sofas ecommerce
15 sofas ecommerce sofas
16 sofas sofas offers
17 sofas offers sofas
18 sofas sofas sale
19 sofas sale sofas
20 convertible sofas convertible sofas buy
21 convertible sofas buy convertible sofas
22 convertible sofas convertible sofas price
23 convertible sofas price convertible sofas
24 convertible sofas convertible sofas discount
25 convertible sofas discount convertible sofas
26 convertible sofas convertible sofas promotion
27 convertible sofas promotion convertible sofas
28 convertible sofas convertible sofas promo
29 convertible sofas promo convertible sofas
.. ... ...
70 recliners recliners shop
71 recliners shop recliners
72 recliners recliners budget
73 recliners budget recliners
74 recliners recliners ecommerce
75 recliners ecommerce recliners
76 recliners recliners offers
77 recliners offers recliners
78 recliners recliners sale
79 recliners sale recliners
80 sofa beds sofa beds buy
81 sofa beds buy sofa beds
82 sofa beds sofa beds price
83 sofa beds price sofa beds
84 sofa beds sofa beds discount
85 sofa beds discount sofa beds
86 sofa beds sofa beds promotion
87 sofa beds promotion sofa beds
88 sofa beds sofa beds promo
89 sofa beds promo sofa beds
90 sofa beds sofa beds shop
91 sofa beds shop sofa beds
92 sofa beds sofa beds budget
93 sofa beds budget sofa beds
94 sofa beds sofa beds ecommerce
95 sofa beds ecommerce sofa beds
96 sofa beds sofa beds offers
97 sofa beds offers sofa beds
98 sofa beds sofa beds sale
99 sofa beds sale sofa beds
[100 rows x 2 columns]
4. Rename the columns of the DataFrame
Before I can upload this table of keywords, I will need to give the columns meaningful names. If we inspect the DataFrame I just created above, we can see that the columns are currently named 0 and 1. Ad Group (example: "sofas") and Keyword (example: "sofas buy") are much more appropriate names.
# Rename the columns of the DataFrame
keywords_df = keywords_df.rename({0:'Ad Group',1:'Keyword'},axis='columns')
5. Add a campaign column
Now I need to add some additional information to our DataFrame.
We need a new column called Campaign for the campaign name. We want campaign names to be descriptive of our group of keywords and products, so let's call this campaign 'SEM_Sofas'.
# Add a campaign column
keywords_df = keywords_df.assign(Campaign = 'SEM_Sofas')
6. Create the match type column
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:
In general, the broader the match type, the more traffic potential that keyword will have, since your ads may be triggered more often. Conversely, a narrower match type means that your ads may show less often—but when they do, they’re likely to be more related to someone’s search.
Since the client is tight on budget, we want to make sure all the keywords are in exact match at the beginning.
# Add a criterion type column
keywords_df.insert(len(keywords_df.columns),'Criterion Type','Exact')
keywords_df
| 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 |
| 5 | sofas | discount sofas | SEM_Sofas | Exact |
| 6 | sofas | sofas promotion | SEM_Sofas | Exact |
| 7 | sofas | promotion sofas | SEM_Sofas | Exact |
| 8 | sofas | sofas promo | SEM_Sofas | Exact |
| 9 | sofas | promo sofas | SEM_Sofas | Exact |
| 10 | sofas | sofas shop | SEM_Sofas | Exact |
| 11 | sofas | shop sofas | SEM_Sofas | Exact |
| 12 | sofas | sofas budget | SEM_Sofas | Exact |
| 13 | sofas | budget sofas | SEM_Sofas | Exact |
| 14 | sofas | sofas ecommerce | SEM_Sofas | Exact |
| 15 | sofas | ecommerce sofas | SEM_Sofas | Exact |
| 16 | sofas | sofas offers | SEM_Sofas | Exact |
| 17 | sofas | offers sofas | SEM_Sofas | Exact |
| 18 | sofas | sofas sale | SEM_Sofas | Exact |
| 19 | sofas | sale sofas | SEM_Sofas | Exact |
| 20 | convertible sofas | convertible sofas buy | SEM_Sofas | Exact |
| 21 | convertible sofas | buy convertible sofas | SEM_Sofas | Exact |
| 22 | convertible sofas | convertible sofas price | SEM_Sofas | Exact |
| 23 | convertible sofas | price convertible sofas | SEM_Sofas | Exact |
| 24 | convertible sofas | convertible sofas discount | SEM_Sofas | Exact |
| 25 | convertible sofas | discount convertible sofas | SEM_Sofas | Exact |
| 26 | convertible sofas | convertible sofas promotion | SEM_Sofas | Exact |
| 27 | convertible sofas | promotion convertible sofas | SEM_Sofas | Exact |
| 28 | convertible sofas | convertible sofas promo | SEM_Sofas | Exact |
| 29 | convertible sofas | promo convertible sofas | SEM_Sofas | Exact |
| ... | ... | ... | ... | ... |
| 70 | recliners | recliners shop | SEM_Sofas | Exact |
| 71 | recliners | shop recliners | SEM_Sofas | Exact |
| 72 | recliners | recliners budget | SEM_Sofas | Exact |
| 73 | recliners | budget recliners | SEM_Sofas | Exact |
| 74 | recliners | recliners ecommerce | SEM_Sofas | Exact |
| 75 | recliners | ecommerce recliners | SEM_Sofas | Exact |
| 76 | recliners | recliners offers | SEM_Sofas | Exact |
| 77 | recliners | offers recliners | SEM_Sofas | Exact |
| 78 | recliners | recliners sale | SEM_Sofas | Exact |
| 79 | recliners | sale recliners | SEM_Sofas | Exact |
| 80 | sofa beds | sofa beds buy | SEM_Sofas | Exact |
| 81 | sofa beds | buy sofa beds | SEM_Sofas | Exact |
| 82 | sofa beds | sofa beds price | SEM_Sofas | Exact |
| 83 | sofa beds | price sofa beds | SEM_Sofas | Exact |
| 84 | sofa beds | sofa beds discount | SEM_Sofas | Exact |
| 85 | sofa beds | discount sofa beds | SEM_Sofas | Exact |
| 86 | sofa beds | sofa beds promotion | SEM_Sofas | Exact |
| 87 | sofa beds | promotion sofa beds | SEM_Sofas | Exact |
| 88 | sofa beds | sofa beds promo | SEM_Sofas | Exact |
| 89 | sofa beds | promo sofa beds | SEM_Sofas | Exact |
| 90 | sofa beds | sofa beds shop | SEM_Sofas | Exact |
| 91 | sofa beds | shop sofa beds | SEM_Sofas | Exact |
| 92 | sofa beds | sofa beds budget | SEM_Sofas | Exact |
| 93 | sofa beds | budget sofa beds | SEM_Sofas | Exact |
| 94 | sofa beds | sofa beds ecommerce | SEM_Sofas | Exact |
| 95 | sofa beds | ecommerce sofa beds | SEM_Sofas | Exact |
| 96 | sofa beds | sofa beds offers | SEM_Sofas | Exact |
| 97 | sofa beds | offers sofa beds | SEM_Sofas | Exact |
| 98 | sofa beds | sofa beds sale | SEM_Sofas | Exact |
| 99 | sofa beds | sale sofa beds | SEM_Sofas | Exact |
100 rows × 4 columns
7. Duplicate all the keywords into ‘phrase’ match
The great thing about exact match is that it is very specific, and we can control the process very well. The tradeoff, however, is that:
- The search volume for exact match is lower than other match types
- We can't possibly think of all the ways in which people search, and so, we are probably missing out on some high-quality keywords.
So it's good to use another match called phrase match as a discovery mechanism to allow our ads to be triggered by keywords that include our exact match keywords, together with anything before (or after) them.
Later on, when I launch the campaign, we can explore with modified broad match, broad match, and negative match types, for better visibility and control of our campaigns.
# 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
| 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 |
| 5 | sofas | discount sofas | SEM_Sofas | Exact |
| 6 | sofas | sofas promotion | SEM_Sofas | Exact |
| 7 | sofas | promotion sofas | SEM_Sofas | Exact |
| 8 | sofas | sofas promo | SEM_Sofas | Exact |
| 9 | sofas | promo sofas | SEM_Sofas | Exact |
| 10 | sofas | sofas shop | SEM_Sofas | Exact |
| 11 | sofas | shop sofas | SEM_Sofas | Exact |
| 12 | sofas | sofas budget | SEM_Sofas | Exact |
| 13 | sofas | budget sofas | SEM_Sofas | Exact |
| 14 | sofas | sofas ecommerce | SEM_Sofas | Exact |
| 15 | sofas | ecommerce sofas | SEM_Sofas | Exact |
| 16 | sofas | sofas offers | SEM_Sofas | Exact |
| 17 | sofas | offers sofas | SEM_Sofas | Exact |
| 18 | sofas | sofas sale | SEM_Sofas | Exact |
| 19 | sofas | sale sofas | SEM_Sofas | Exact |
| 20 | convertible sofas | convertible sofas buy | SEM_Sofas | Exact |
| 21 | convertible sofas | buy convertible sofas | SEM_Sofas | Exact |
| 22 | convertible sofas | convertible sofas price | SEM_Sofas | Exact |
| 23 | convertible sofas | price convertible sofas | SEM_Sofas | Exact |
| 24 | convertible sofas | convertible sofas discount | SEM_Sofas | Exact |
| 25 | convertible sofas | discount convertible sofas | SEM_Sofas | Exact |
| 26 | convertible sofas | convertible sofas promotion | SEM_Sofas | Exact |
| 27 | convertible sofas | promotion convertible sofas | SEM_Sofas | Exact |
| 28 | convertible sofas | convertible sofas promo | SEM_Sofas | Exact |
| 29 | convertible sofas | promo convertible sofas | SEM_Sofas | Exact |
| ... | ... | ... | ... | ... |
| 70 | recliners | recliners shop | SEM_Sofas | Phrase |
| 71 | recliners | shop recliners | SEM_Sofas | Phrase |
| 72 | recliners | recliners budget | SEM_Sofas | Phrase |
| 73 | recliners | budget recliners | SEM_Sofas | Phrase |
| 74 | recliners | recliners ecommerce | SEM_Sofas | Phrase |
| 75 | recliners | ecommerce recliners | SEM_Sofas | Phrase |
| 76 | recliners | recliners offers | SEM_Sofas | Phrase |
| 77 | recliners | offers recliners | SEM_Sofas | Phrase |
| 78 | recliners | recliners sale | SEM_Sofas | Phrase |
| 79 | recliners | sale recliners | SEM_Sofas | Phrase |
| 80 | sofa beds | sofa beds buy | SEM_Sofas | Phrase |
| 81 | sofa beds | buy sofa beds | SEM_Sofas | Phrase |
| 82 | sofa beds | sofa beds price | SEM_Sofas | Phrase |
| 83 | sofa beds | price sofa beds | SEM_Sofas | Phrase |
| 84 | sofa beds | sofa beds discount | SEM_Sofas | Phrase |
| 85 | sofa beds | discount sofa beds | SEM_Sofas | Phrase |
| 86 | sofa beds | sofa beds promotion | SEM_Sofas | Phrase |
| 87 | sofa beds | promotion sofa beds | SEM_Sofas | Phrase |
| 88 | sofa beds | sofa beds promo | SEM_Sofas | Phrase |
| 89 | sofa beds | promo sofa beds | SEM_Sofas | Phrase |
| 90 | sofa beds | sofa beds shop | SEM_Sofas | Phrase |
| 91 | sofa beds | shop sofa beds | SEM_Sofas | Phrase |
| 92 | sofa beds | sofa beds budget | SEM_Sofas | Phrase |
| 93 | sofa beds | budget sofa beds | SEM_Sofas | Phrase |
| 94 | sofa beds | sofa beds ecommerce | SEM_Sofas | Phrase |
| 95 | sofa beds | ecommerce sofa beds | SEM_Sofas | Phrase |
| 96 | sofa beds | sofa beds offers | SEM_Sofas | Phrase |
| 97 | sofa beds | offers sofa beds | SEM_Sofas | Phrase |
| 98 | sofa beds | sofa beds sale | SEM_Sofas | Phrase |
| 99 | sofa beds | sale sofa beds | SEM_Sofas | Phrase |
200 rows × 4 columns
8. Save and summarize!
To upload our campaign, I need to save it as a CSV file. Then I 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!
Looking at a summary of my campaign structure is good now that I've wrapped up our keyword work. I can do that by grouping by ad group and criterion type and counting by keyword. This summary shows us that we assigned specific keywords to specific ad groups, which are each part of a campaign. In essence, we are telling Google (or Bing, etc.) that we want any of the words in each ad group to trigger one of the ads in the same ad group. Separately, we will have to create another table for ads, which is a task for another day and would look something like this:
| 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 | KaushikSofas.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 | KaushikSofas.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 | KaushikSofas.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 | KaushikSofas.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 20
Phrase 20
love seats Exact 20
Phrase 20
recliners Exact 20
Phrase 20
sofa beds Exact 20
Phrase 20
sofas Exact 20
Phrase 20
Name: Keyword, dtype: int64
Further Reading
- If you’re curious about pasting the data into the AdWords or BingAds editor, check out the pandas
to_clipboarddocumentation.
Dig deeper?
You can find out more about this project at Github.