How to create paid search keywords using Pandas

Setting up keywords for new paid search accounts can be a repetitive and time-consuming process. While it’s historically been done using Excel, many digital marketers are now taking advantage of powerful data science tools, such as Pandas, to help make the process quicker, easier, and more repeatable.

In this project we’ll be using Pandas to create a set of paid search keywords to use with Google Adwords or Microsoft Ads for a fictional website selling fly fishing tackle. The code is easy to adapt and will allow you to apply it to any site. Let’s get started.

Load the packages

First of all, open a Jupyter notebook and load up the re Python regular expression package and the pandas package. The re package is part of Python so there’s no need to specifically install it, but you’ll need to install pandas with pip3 install pandas if you don’t already have it.

import re
import pandas as pd

Set up the input data

Next, we’ll set up some simple lists containing our input data. The products list contains the product names our fictional retailer sells. The keywords_prepend list includes a range of words we want to prepend in front of the product names, i.e. buy fly rods, and the keywords_append list includes the words we want to append after the product name, i.e. fly rods cheap. Finally, the campaign_name holds the name for our ad campaign.

products = ['fly rods', 'fly reels']
keywords_prepend = ['buy', 'best', 'cheap', 'reduced']
keywords_append = ['for sale', 'price', 'promotion', 'promo', 
                   'coupon', 'voucher', 'shop', 'suppliers']
campaign_name = 'fly_fishing'

Generate keyword combinations

Next we need to create a function that takes the products list, the keywords_prepend list, and the keywords_append list. It loops over each of the products in the list, then prepends and appends keywords to each one to create a range of unique combinations. The product name itself is added to the list generated for each one, i.e. ['fly rods', 'buy fly rods'] so we can set the ad group accordingly.

def generate_combinations(products, 
                          keywords_prepend, 
                          keywords_append):
    """Return a list of all prepended and appended keywords combinations. 

    Args:
        products (list): List of product names.
        keywords_prepend (list): List of keywords to prepend to product names.
        keywords_append (list): List of keywords to append to product names.

    Returns: 
        keywords (list): List of lists containing the product name and keyword combination.

    Example: 
        [['fly rods', 'fly rods'],
        ['fly rods', 'buy fly rods'],
        ['fly rods', 'best fly rods']]
    """

    keywords = []

    for product in products:    
        keywords.append([product, product])

        for keyword_prepend in keywords_prepend:
            keywords.append([product, keyword_prepend+' '+product])

        for keyword_append in keywords_append:
            keywords.append([product, product +' '+keyword_append])

    return keywords

Running the generate_combinations() function returns all the combinations of keywords, which are appended and prepended to each product name, and placed in a list containing the name of the associated product.

combinations = generate_combinations(products, keywords_prepend, keywords_append)
combinations

[['fly rods', 'fly rods'],
 ['fly rods', 'buy fly rods'],
 ['fly rods', 'best fly rods'],
 ['fly rods', 'cheap fly rods'],
 ['fly rods', 'reduced fly rods'],
 ['fly rods', 'fly rods for sale'],
 ['fly rods', 'fly rods price'],
 ['fly rods', 'fly rods promotion'],
 ['fly rods', 'fly rods promo'],
 ['fly rods', 'fly rods coupon'],
 ['fly rods', 'fly rods voucher'],
 ['fly rods', 'fly rods shop'],
 ['fly rods', 'fly rods suppliers'],
 ['fly reels', 'fly reels'],
 ['fly reels', 'buy fly reels'],
 ['fly reels', 'best fly reels'],
 ['fly reels', 'cheap fly reels'],
 ['fly reels', 'reduced fly reels'],
 ['fly reels', 'fly reels for sale'],
 ['fly reels', 'fly reels price'],
 ['fly reels', 'fly reels promotion'],
 ['fly reels', 'fly reels promo'],
 ['fly reels', 'fly reels coupon'],
 ['fly reels', 'fly reels voucher'],
 ['fly reels', 'fly reels shop'],
 ['fly reels', 'fly reels suppliers']]

Create match type keywords

Now we need to extend this and create match type keywords. These keyword matching options control which Google searches can trigger from your ads. There are four main keyword match types: broad match, broad match modifier, phrase match, and exact match. These use the same keywords as above, but with some additional formatting.

Generate exact match keywords

Exact match keywords are based on the [flip flops] format and simply wrap the original keyword phrase in square brackets. We’ll create another function, called match_type_exact() which takes our list of keywords from generate_combinations() and then wraps the keyword phrase in square brackets.

Rather than returning another list, we’ll output the data as a Pandas dataframe, using the from_records() function to insert the exact list, and then setting the column names as product and keywords accordingly, before finally adding the match_type value and returning the final dataframe.

def match_type_exact(keywords):

    exact = []
    for keyword in keywords:        
        exact.append([keyword[0], '['+keyword[1]+']'])

    df = pd.DataFrame.from_records(exact, columns=['product', 'keywords'])    
    df['match_type'] = 'Exact'

    return df

exact = match_type_exact(combinations)
exact.head()

Generate phrase match keywords

Phrase match keywords are based on the "bowler hat" format and wrap the original keyword phrase in double quotes. We can modify the approach above and replace the square brackets with double quotes.

def match_type_phrase(keywords):

    phrase = []
    for keyword in keywords:        
        phrase.append([keyword[0], '"'+keyword[1]+'"'])

    df = pd.DataFrame.from_records(phrase, columns=['product', 'keywords'])    
    df['match_type'] = 'Phrase'

    return df

phrase = match_type_phrase(combinations)
phrase.head()

Generate broad match keywords

We don’t need to do anything to transform our keywords to be broad match, as this keyword type doesn’t have any special enclosures. However, we still need to return the keywords and add the “Broad” value to our column, so we’ll just churn out the unmodified keywords.

def match_type_broad(keywords):

    broad = []
    for keyword in keywords:        
        broad.append([keyword[0], keyword[1]])

    df = pd.DataFrame.from_records(broad, columns=['product', 'keywords'])    
    df['match_type'] = 'Broad'

    return df

broad = match_type_broad(combinations)
broad.head()

Generate broad match modified keywords

Finally, we have broad match modified keywords. Broad match modified keywords have the format +driving +gloves, so we need to prefix each word with a + symbol. To do this I’ve used the code bmm = ['+' + keyword[1].replace(' ', ' +')] to add an initial + symbol, and then have replaced each space with another +, which prefixes the data.

def match_type_broad_modified(keywords):

    broad_modified = []
    for keyword in keywords:

        bmm = ['+' + keyword[1].replace(' ', ' +')]
        broad_modified.append([keyword[0], bmm])

    df = pd.DataFrame.from_records(broad_modified, columns=['product', 'keywords'])    
    df['match_type'] = 'Modified'

    return df

broad_modified = match_type_broad_modified(combinations)
broad_modified.head()

Generate the paid search dataset

The final step is to put it all together in a function that does everything, which I’ve named generate_keywords(). We pass the products, keywords_prepend, and keywords_append lists, and the campaign_name.

Then we run generate_combinations() to get our list of all keyword combinations, then run the match type functions to modify these by match type. Finally, we can use pd.concat([exact, phrase, broad, broad_modified]) to vertically join all of the dataframes together.

def generate_keywords(products, 
                      keywords_prepend, 
                      keywords_append, 
                      campaign_name):
    """Return a Pandas dataframe of keywords data for use in Google Adwords. 

    Args:
        products (list): List of product names.
        keywords_prepend (list): List of keywords to prepend to product names.
        keywords_append (list): List of keywords to append to product names.
        campaign_name (str): Name of paid search campaign. 

    Returns:
        df (object): Pandas dataframe containing generated data.  
    """

    keywords = generate_combinations(products, keywords_prepend, keywords_append)

    exact = match_type_exact(keywords)
    phrase = match_type_phrase(keywords)
    broad = match_type_broad(keywords)
    broad_modified = match_type_broad_modified(keywords)

    df = pd.concat([exact, phrase, broad, broad_modified])
    df['campaign_name'] = campaign_name
    return df

Running the generate_keywords() function generates 104 exact match, phrase match, broad match, and broad match modified keywords, that we can use in our PPC campaigns. As the code is now function based, all we need to do to repeat this process is update the lists and re-run the code.

keywords = generate_keywords(products, keywords_prepend, keywords_append, campaign_name)
keywords.sample(10)

Matt Clarke, Tuesday, March 09, 2021