Label Keywords With Organic Data Script
This script takes ranking data from a spreadsheet and uses it to label exact match keywords, for manual data analysis.
What’s included?
How does the script work?
Fill out a Google Docs spreadsheet with the organic positions of your site and a few competitors, and this script will label up any matching keywords in your account with those positions. This means it’s easy to analyse how SEO is affecting your PPC performance!
If you’ve not run a script before, please read our Introduction to Google Ads Scripts before starting.
How do I use it?
First set up a new Google Docs spreadsheet, and set up Sheet1 like this:
The first row is the headers ≠ fill in your name and your competitors’ names. Then the first column is the keyword text, followed by the organic positions of the sites.
(Technically the first column is the search query, not the keyword text – but the script only looks at exact keywords, where the two are the same, so that the PPC data will be accurate.)
If given this sheet, the script will label the keyword ‘search query’ with ‘Us – 3’, ‘Competitor 1 – 1’ and ‘Competitor 2 – 6’. If a position is blank or over 9, the script will assume that means the site was not on the first page of results: the keyword ‘keyword’ would be labelled with ‘Us – Off First Page’, ‘Competitor 1 – 6’ and ‘Competitor 2 – Off First Page’.
If you want to do a before/after analysis, you could put in your current ranks and previous ranks, like this:
Then go to your Google Ads account and copy and paste in the script below. There are a few settings to change:
- spreadsheetUrl is the URL of your spreadsheet.
- Use campaignNameContains and campaignNameDoesNotContain if you only want to label keywords in some campaigns.
- Set includePaused to true if you want to label paused keywords, in paused ad groups and campaigns.
Then authorise the script, hit Preview to make sure it runs correctly – and then you’re all set.
Where does that ranking data come from?
To get the data, we’ve been using SEMrush. To find which keywords’ positions have changed recently, type in your domain in the search bar, then go to ‘Position Changes’ in the Organic Research section. Then click Improved or Declined to see which keywords have better or worse position since the previous month. If you’re using a paid plan and want historic data, then change the month you’re looking at in the ‘Historic Data’ drop down at the top right.
It’s not available in the free version, but if you’re paying you can use the API to download their data. There are handy instructions on how to do that in Google Sheets on the SEMrush blog. However, we recommend that you only access their API from a temporary spreadsheet, and delete it afterwards – otherwise you may accidentally use up your API credits, as Google automatically redownloads imported data every so often.
If you want the first nine domains in the organic ranks for a particular keyword, put your keyword in A2 and this in B2: TRANSPOSE(IMPORTDATA("http://api.semrush.com/?type=phrase_organic&key=YOUR-KEY-HERE&display_limit=9&export_columns=Dn&phrase="&A2&"&database=uk"))
You have to replace YOUR-KEY-HERE with your actual key, of course. And if you’re not in the UK you’ll want to change ‘database=uk’.
To get historic data use: TRANSPOSE(IMPORTDATA("http://api.semrush.com/?type=phrase_organic&key=YOUR-KEY-HERE&display_limit=9&export_columns=Dn&phrase="&A2&"&database=uk&display_date=20140815")) Replacing ‘20140815’ with the wanted date (in ‘yyymmdd’ format).
You’ll get something like this:
For costing purposes each domain counts as a ‘line’: each keyword will cost you 90 API credits for current data or 450 credits for historic data.
To turn use that with the script, you can just add some extra columns to the side, headed with the sites you’re interested in, and use the MATCH function to find what the rank number is: =iferror(match(N$1,$C2:$K2,0),11)
Then you can copy that and paste as values into the spreadsheet you’re using for the script, and delete the spreadsheet with the API calls in.