Generate PPC Keyword Lists with SQL in BigQuery

If you run massive search accounts with millions of keywords created based on templates, it can get really tricky to regularly generate such keyword lists on scale on daily basis for example.

You can:

  1. Write your keywords manually. Good luck with that.
  2. Do some copying and pasting in Excel/Google Sheets. Good luck with making no errors.
  3. Use 3rd party tool to help you out.  Generally the most convenient option but you need to shell out some cash.
  4. Make your own tool 100% fitting your needs. You can use for example Power Query in Excel or level up your game and employ some SQL and that’s what I will describe in this article.

Workflow

Utilizing SQL for keyword generation purposes should follow this workflow:

  1. Define you multipliers in a Google Sheet
  2. Upload your multipliers to BigQuery
  3. Write some SQL magic in BigQuery to create all the keyword combinations
  4. Download the generated keywords back to Google Sheets
  5. Post new keywords to Google Ads

And that’s it. It’s not as difficult as you might think.

Define keyword multipliers in a Google Sheet

Assume your run campaigns for various car dealerships. You always need to create same keywords such as “configure chevrolet silverado 2019”, “buy toyota prius 2019” for different models, manufacturers, years, cities and zip codes.

I have created this input sheet, where I can easily drop my input variables: https://docs.google.com/spreadsheets/d/15eQdGhnxl1rIuPerZmON6u3QxB0SLF_E-Iza59IyPMM/edit?usp=sharing

(notice the “-” items, they don’t make any sense to you now, but they will start making sense later)

Uploading keyword multipliers to BigQuery

Unless you know nothing about BigQuery at this point, I strongly recommend you to start with this article. You will get an idea about what BigQuery is, what it can do and how to actually start using it.

So now big question is how to get your user friendly input sheet into BigQuery? Well, I have a good news for you. With our partner project www.MythicalReports.com, we prepared an add-on for Google Sheets called “BigQuery Uploader for Sheets”. Grab it from Chrome Web Store here. The add-on will upload the inputs from Google Sheets to BigQuery with few clicks.

After you successfully install the add-on, it will start showing up in the add-on menu across all your Google Sheets files:

Start by clicking on “Create Config Sheet” and fill it out:

If you are unsure what each column means, read detailed instructions on the official add-on page here.

Once your config sheet is ready, upload your sheets to BigQuery:

If everything goes well, you will see your input table loaded into BigQuery:

SQL magic

Now it’s time for some SQL magic. You now need to “multiply” all the columns between each other. The steps in this procedure are:

  1. Create temporary tables containing only 1 column via WITH …. AS ()
  2. Cross join these temporary tables
  3. Concatenate the columns to create the desired keyword combinations + filter out unwanted keyword combinations
  4. Get rid of duplicates
  5. Create campaigns, ad groups and different keyword match types

Temporary tables

Let’s start with a simple SELECT which creates a table with 1 column make_model:

I just “typed”:

SELECT make_model
FROM `test-project-excelinppccom.sample_sql_kw_generator.inputs`

into the Query editor area and pressed “Run” button and this query returned the 3 row table with male_model column.

Couple notes. BigQuery has auto-suggestion feature which gives you list of potential column names, functions, datasets, tables after pressing TAB when you are in the middle of writing the SQL. So I did not really have to type the entire project.dataset.table string in the FROM clause. I just typed “t”, pressed tab and it was suggested to me and simply selected the last option with my mouse:

Another important note is that you should ALWAYS include the project name in your FROM clauses. It will save you many headaches in future (e. g. saving a query as view requires the project names in FROM clause).

If you want to comment out a line you start with #. If you want to comment out a block of code, you start with /* and end with */.

And the last note might be obvious – BigQuery does not care about spaces or tabs, so you can make your SQL look nice and readable.

Okay, now it’s time to create all the temporary tables with WITH clause:

The pattern is this: WITH some_table_name AS (your query). If you are creating multiple temporary tables, you have to separate them by commas. As you can see, I have 5 tables because will be multiplying 5 columns against each other.

Cross join

This is where the cool stuff starts. Let’s start with cross joining only 2 temporary tables (make_model_table against year_table):

Notice I am declaring from which table each column is coming from in the SELECT clause (make_model_table.make_model, year_table.year)

Press Run button and you will get:

That wasn’t too difficult, was it? You now have multiplied the make_model with year giving you all the possible combinations of make_model and year.

Proceed in similar fashion with remaining tables:

Press Run and watch the magic:

Now you have all possible combinations generated, you just need to “make keywords” out of them.

Concatenate the columns + filter out unwanted combinations

It’s time to create the keywords. I am creating 2 keyword sets based on  this concatenate formula:

And this query will return:

This does not look particularly nice, does it? We can see many keywords including “-“. We need to REPLACE these “unwanted” characters with “nothing” and do some replacing of triple and double spaces, trimming and lowering. This simple move will give all the possible keyword combinations not limited to always including values from all the 5 columns. At the same time, we need to filter down only to keywords which have model_model column filled in.

So the final code can look like this:

In the first upgrade, I am wrapping up the existing CONCAT function into 2 replace functions (dashes, triple+double spaces need to go out), then I trim the resulting string (=get rid of spaces at the beginning and end of keyword) and as final step, I convert the keyword to lower case.

In the second upgrade, I filter out all rows which are shorter than 2 characters in the make_model column.

The final outcome is this (around 750 keywords):

Getting rid of duplicates

On the picture above, we can see the keyword_all_elements on row 10 and 12 are essentially duplicates. Action column of these keywords is slightly different, it’s “-” and “”. It’s the result of multiplication of temporary tables with unequal number of rows. So how to we get rid of these? It’s little complicated. The best way to get rid of these is to add more rules to the WHERE clause:

This move will remove the “duplicates”, now my keyword count is 576.

So you keyword list is now ready but you still don’t have match types, campaigns, and ad groups, right?  Before we do that, let me share the code written up until now:

WITH
make_model_table AS (
SELECT make_model
FROM `test-project-excelinppccom.sample_sql_kw_generator.inputs`
),

year_table AS (
SELECT year
FROM `test-project-excelinppccom.sample_sql_kw_generator.inputs`
),

city_table AS (
SELECT city
FROM `test-project-excelinppccom.sample_sql_kw_generator.inputs`
),

zip_table AS (
SELECT zip
FROM `test-project-excelinppccom.sample_sql_kw_generator.inputs`
),

action_table AS (
SELECT action
FROM `test-project-excelinppccom.sample_sql_kw_generator.inputs`
)

#############
#############
#############

SELECT
make_model_table.make_model,
year_table.year,
city_table.city,
zip_table.zip,
action_table.action,

LOWER(
TRIM(
REPLACE(
REPLACE(
REPLACE(
CONCAT(make_model_table.make_model,’ ‘, year_table.year,’ ‘, city_table.city,’ ‘, zip_table.zip,’ ‘, action_table.action),
‘-‘,”),
‘    ‘,’ ‘),
‘   ‘,’ ‘)
)
)
AS keyword_all_elements

FROM make_model_table

CROSS JOIN year_table
CROSS JOIN city_table
CROSS JOIN zip_table
CROSS JOIN action_table

WHERE
LENGTH(make_model_table.make_model)>2 AND

LENGTH(year_table.year)>0 AND
LENGTH(city_table.city)>0 AND
LENGTH(zip_table.zip)>0 AND
LENGTH(action_table.action)>0

We should save the code as view and then run more queries on the top of the view. So let’s save it:

Then:

And now you can query this saved “view”, which basically means, that you run a query on the top of the long query:

Campaign, ad groups and match types

Let’s start with creating different keyword match types. You need to use this trick with UNNEST function:

In my example, I am only working with broad and exact keywords. Basically, you are creating and unpivoting an array of “exact, broad” as new column at the same time.

You are now wondering how do you create broad match modifier keyword with + signs? You need to employ CASE:

 

And now it’s time for creating campaigns and ad groups. Assume you want to use the first word from the make_model column + match type as campaign. All lower case.

You want to use concatenation of make_model + year + city + zip + match type as ad group name. Again, all lower case. At the same time, you don’t want to show “-” in the ad group name when the item is equal = “-“. Here is the code:

And that’s pretty much it. In the real world, you would need some landing pages (maybe a join from a different table) and Max CPCs  (probably based on some CASE statement).

Here is the code which queries the kws_generted view:

#campaign_structure
SELECT
make_model, year, city, zip, action,

LOWER(
CONCAT(
SPLIT(make_model,’ ‘)[ORDINAL(1)],’-‘,
match_type)
) AS campaign,

LOWER(
CONCAT(
make_model,’-‘,
REPLACE(year,’-‘,’no_year’),’-‘,
REPLACE(city,’-‘,’no_city’),’-‘,
REPLACE(zip,’-‘,’no_zip’),’-‘,
match_type
)
) AS ad_group,

CASE
WHEN match_type = ‘broad’ THEN CONCAT(‘+’,REPLACE(keyword_all_elements,’ ‘,’ +’))
ELSE keyword_all_elements
END AS keyword_all_elements,
match_type

FROM
`test-project-excelinppccom.sample_sql_kw_generator.kws_generated`,
UNNEST([‘exact’,’broad’]) AS match_type

I am saving this as “campaign_structure” view:

Downloading your generated keywords back to the Google Sheet

That’s where Supermetrics for Google Sheets comes in because you can use their BigQuery connector for downloading the data from BigQuery. All we need to do now is to write a query to select all the columns from the “campaign_structure” view. When querying the data in BigQuery through Supermetrics, you need to declare that you are using #standardSQL on the first row. The rest is just a simple select of all the columns from the final view (=campaign_structure is the final view).

Run the query and check the sheet: https://docs.google.com/spreadsheets/d/15eQdGhnxl1rIuPerZmON6u3QxB0SLF_E-Iza59IyPMM/edit#gid=823210206

And that’s it, you just generated all possible combinations.

The best part

Say you just realized that you need to generate keywords for more cities and zips. All you need to do is to go to input sheet, push it to BigQuery and re-run your query:

SELECT *
FROM `test-project-excelinppccom.sample_sql_kw_generator.campaign_structure`

My updated input sheet looks like this:

I run the query and I am immediately getting the new keywords. My keyword count is now 3456 keywords in stead of 550. All it took was to ru-run the query and few seconds of wait time. 🙂

You can schedule Supermetrics to run daily. However, you need to push the inputs from Google Sheets manually via our add-on. We’ll add scheduler into the add-on later on.

Conclusion

I am aware that I am creating some unwanted combinations of cities and zips. In the real world, that SQL would be little more complicated. In order to convey the message here, I accepted some simplified concepts which make the article more understandable (I hope).

When you start working with BigQuery, you will be frequently getting errors around parentheses, commas, weird characters and so on. Don’t worry, it’s normal. After you get used to syntax, you will be OK.

The article is ending with loading keywords into Google Sheets from where you can copy and paste the keywords and upload them through AdWords Editor, or you can have a script loading your shiny new keywords from Google Sheets to Google Ads. That’s topic for more advanced users and I may cover it in a separate article.

To conclude, I hope it’s now little clearer how you can use BigQuery and SQL to create thousands or millions of keywords. Yes, it’s may not be that easy at first sight, but if you dive into the topic, you can learn pretty quickly.

2 thoughts on “Generate PPC Keyword Lists with SQL in BigQuery

  1. Hi DANZRUST, this great article to read and I am going to this method.

    I have a few questions, if I want to use it only for keywords research then is there any way to use this free?

    1. Hi Usman,
      can you be more specific what are you trying to achieve? In my article, I am creating the KWs, not doing in any sort of KW research. Google will not export any of the KW planner data into BigQuery natively.

Leave a Reply

Your email address will not be published. Required fields are marked *