Harvesting Locations for Google Ads Keywords using Google Sheets
Google has recently introduced a feature that has long been available in Microsoft Excel, that combined with Split to Columns, offers those of us who build large-scale Google (and Bing) Ads campaigns, a powerful new tool. The ability to remove duplicates is a core feature of Excel that has long been lacking in Google Sheets. While work-arounds in the form of scripts have been, and continue to be available, Google’s inclusion of the feature greatly simplifies things.
Split to Columns and Remove Duplicates
If you’re considering building or expanding an existing SEM campaign using very granular locations or phrases, you may have discovered that finding meaningful data that is formatted correctly can be difficult. Say you’re selling lemonade, and want to target specific streets or postcodes with long tail keywords. You need to first find a list of these postcodes or streets. This can be difficult in itself, but not impossible. For example, if you’re targeting UK streets, you can find very useful information through StreetCheck.
Let’s take the London postcode SE1 as an example. StreetCheck offers a comprehensive list of streets and their postcodes in this specific postcode. It would be ideal to simply copy/paste this data into a spreadsheet, attach “Lemonade Delivery” before each street name, and upload it through Google Ads Editor to create a very granular, comprehensive list of phrases.
Bulk Data Source Limitations
As quickly becomes apparent, simply copy/pasting data from a website into Google Sheets or Microsoft Excel can present more work to sort and filter than expected.
You can follow along the progress of this example on our public sheet.
We can’t simply append the core search terms that we want to target to this data. We need to be able to pull just the street names (or postcodes, or whatever data we want), and discard the rest.
The blank spaces are a problem, but they’re easily remedied by sorting. Highlight the entire ‘A’ column by clicking on it, then:
Data -> Sort Range by Column A, A-Z. The results can be seen on the second example sheet titled ‘Sorted’
Split Text to Column
While all the information we’re looking for is available, it’s still not in any sort of format that is of use to us. Next, we want to continue highlighting the ‘A’ column and selecting:
Data -> Split Text to Columns
In the case of our example, which is found on sheet 3 titled ‘Split’, our data is very clearly split. However, to get to this point, you may need to decide what you’re splitting the data by. In this case, it’s a comma, but you may have a dialogue box show up to ask you to choose.
Select the appropriate separator, and your data will split clean
Removing Duplicates
As you can see, streets exist across numerous postcodes. We need to remove the duplicates, and just have one entry for each Since we’re only looking to add street names to our search phrases, we can delete all the columns except ‘A’, where our street names our located.
Using the new remove duplicates feature in Google Sheets. While previously you would need to set up a script to remove duplicates, you can now do it easily through the menu. Simply select the ‘A’ column and choose:
Data -> Remove Duplicates
If your data has headers like “Street Name”, “Postcode” or anything else, select the box.
After a quick processing, you’ll receive a confirmation of your results. As you can see here, we took 3,116 rows and cut them to 529 individual ones:
Be sure to go through and double-check just in case, as you may have unnecessary entries like: SE1 6FQ (No Longer In Use). You can see the final results of our de-duping on the ‘De-Duped’ page in our example spreadsheet.
Incorporating the Data into your Google Ads Account
Now that we have a list of all the street names within the SE1 postcode area in London, we can start using them to expand our accounts. The simplest way here would be to go into the empty ‘B’ column, and start creating search terms. For example, you can add the following code into B1:
=CONCATENATE(A1,” Lemonade”)
Pull that downwards and fill in all the available cells, and you’ll be left with a very comprehensive keyword list to put into Google Ads (either directly or through Google Ads Editor).
Don’t forget location targeting, to make sure your ads are only showing where you want them to! Street names, of course, not only exist across multiple postcodes, but cities, states and countries as well.
Final Thoughts on Google Sheets
Google Sheets is a powerful tool with excellent collaboration capabilities, and catching up with the feature set found in Microsoft Excel. The addition of the “remove duplicates” combined with standard sorting and splitting text to columns creates a powerful new way of quickly building large-scale, comprehensive Google and Bing Ads SEM campaigns.
