Search Engine Marketing, SEO

Inbound marketing reminds us that there is no shortcut to market research, data-driven decision making and communicating correctly with prospects. Most importantly, it reminds us that the message to our prospects must focus on a real connection, driven by purpose and based on permission.

But how does our voice stand out from all others? How do we communicate from that one immutable focal point of purpose when all forms of data and all other voices in the market seek to overpower our voice and gain the mindshare of our prospects? How can our message’s aim be true if it’s not backed by powerful data?

In this light — the light of using data to create messaging that resonates with an identified target audience — we’re releasing a tool to which we’ve added some functionality. We use it every day to help our clients’ voices rise above the rabble. The link to the tool is at the end of the post, but first, here’s what it can do:

Originally developed by our fellow marketers over at SEER Interactive, the Google Scraper allows you to input a keyword (like you would when searching in Google), select the number of Google results you’d like to see and which Google TLD you’d like to use (,, etc.), and dictate which SERP listing you’d like to start from (the offset).

We liked the tool and saw the potential to get even more actionable data from the results, so we added some functionality. We built on the option to pull data based on a few more specific verticals, like blogs, news sites and videos (the regular search functionality is still there). We also added the ability to perform a “site:” search (e.g., “keyword phrase”), if necessary.


In addition, we added the ability to pull the Mozscape API URL Metrics for any one of the SERP results returned. So, now you’re able to see items like domain authority, page authority, MozRank, external links and so on for any one of the SERP results.


We also added SEMRush API functionality to pull estimated domain traffic. (Yes, we are working on a dedicated SEMRush API spreadsheet that covers all SEMRush API capabilities.)


We added the option to pull social data from some of the major social networks (Twitter, Facebook, LinkedIn, Google+, Pinterest) for any SERP result using the SharedCount API.


And finally, we included a few menu items for additional functionality to the spreadsheet.


With all of these different tools and APIs built into a single Google Drive spreadsheet, it might look like a Frankenstein’s monster of a tool? And maybe it is. However, we use this tool every day to quantitatively analyze site data, which is only part of the puzzle. We’ve also built entire methodologies around understanding sites from a qualitative perspective.

As time passes and more competitors receive access to the same surface-level data, the figures that will lead to that real connection with prospects and help gain an edge over competitors will be buried deeper and deeper beneath the surface. More than ever, there is a need to collect and assess data that will deliver actionable insights and lead directly to prospects.

This tool has helped us assess sites quantitatively, and we hope it helps you do the same. You can get a copy of the tool here. The tool is locked; make a copy to unlock this or any other Google Drive tool.

In addition, your feedback is important to us. Please let us know how we can make this tool better.

Basic Instructions

If you’d like to just get started with the tool, you can quickly open and unlock the tool. Basic instructions are included in the Google Drive Spreadsheet. It is possible to get started quickly with those instructions.

Detailed Instructions

If you’d like to understand how to use the tool in greater detail before you get started using it, here are the detailed instructions. At the time of creation for these instructions, the current version of the tool is version 1.4.

The tool is here. It is a protected Google Drive document, so you’ll have to unlock the document. It is protected so that your data or anyone else’s data does not get left in the spreadsheet or overwritten when you come back to the document and need the data. When you unlock the tool, you’ll be making a copy of the the tool so that no one else will be using the tool and you’ll have access to your own business intelligence.

Let’s go over the layout of the tool before getting into the specifics of using it. The first two columns of the tool, columns A and B, have everything you need to overwrite data or pull different sets of data each time you use the tool. These columns in addition to the top row are frozen so that you can always refer to key parts of the document quickly just like you would . In these first two columns, you’ll see four key sections. First, you’ll see the Google search section. Second, you’ll see the Mozscape API section. Third, you’ll see the Social Analytics section, by SharedCount’s API. And fourth, you’ll see the SEMRush API section.

Continuing with layout, across the top starting from Column C, you’ll see the “Results:” column. The results column will change based upon your input from the Google search section. The remaining columns that follow, Columns D through infinity (or however many columns of data you’d like), are interchangeable. These columns are interchangeable with data you’d like to receive from any one of the the three integrated APIs: Mozscape, SharedCount and SEMRush.

Search Functionality (Core)

Now, let’s get into use of the spreadsheet. Let’s start with the search functionality. Here’s a link to the core search functionality. It was originally built by the guys over at SEER Interactive (hats off to those guys for building something awesome). The link provided above gets into the use of the search functionality, but I’ll also go over it here.


The core search functionality includes four key inputs. But Brad, there are six inputs here. I know, I know, two of the inputs are extended search functionality. We’ll get into those in a minute. The four key inputs are Keyword, Results, Google TLD and Offset. Keyword would be your Google search phrase that you would normally type into the Google search engine. Results would be how many results you’d like to see back. A normal Google search would return 10 results. You can return as many results as you want here; it’s completely up to you (but you’re probably not going to need more than 100 at any one time because that’s a lot of results).

Note: Every once in a while you’ll receive an error where Google will not return any results when you run a scrape. Change the number of results and see if Google then returns any results.

Google TLD is the Google search engine you’d like to search from (e.g.,,,, etc.), and Offset is the result that you want to start from. For instance, say you don’t want the first page of Google results, but you want the second page of results. You’d input 10 for Results and you’d input 11 for Offset. So, together, this would mean give me 10 results starting from the 11th search result position; in essence you’d be getting page two of regular search results.

Search Functionality (Extended)

The two items we provided for extending the search functionality were “Site:” and “Vertical:”. Site allows you to perform a “site: search” in google (e.g., “keyword phrase”). This would be helpful when trying to pull specific metrics for your website’s pages related to a specific topic or when you’re trying to pull specific metrics for your competitor’s website pages related to a specific topic.

The “Vertical:” input allows you to select a Google search vertical to pull results from. The options provided for this field are included the dropdown next to the “Vertical:” label. Options provided include “Search”, “Blog”, “News” and “Videos”. If using the “Search” vertical, you’d be pulling results from a regular Google Web search. The “Blog” vertical pulls search results Google’s Blogs vertical. The “News” vertical pulls from Google’s News vertical. And the “Videos” pulls from Google’s Videos vertical. For reference, here’s a screenshot of the Google verticals you could pull from when performing a search on Google.


We’d like to incorporate more verticals in the future, but each vertical takes time to implement because of the custom coding involved with each one. Look for updates to verticals in future version releases of this tool.

Search Functionality (Running the Search)

So once you’ve entered your search inputs, now it’s time to perform the search. Searching is simple and straightforward. From the Google spreadsheet, you’ll see a menu item titled “Google Results Menu”. Here’s a screenshot:


When you’re ready, click the “Google Results Menu” item. A dropdown menu appears. Screenshot below:


To perform the search, click the item labeled “Pull Google Results (w/ current options)” menu item. When you click that item, the search is activated. Your search results will appear in Column C, the column cleverly marked “Results:”. Screenshot of example results below:


That’s it, now you know how to use the tool to perform a search. Now, let’s pull some metrics on the returned URLs!

Pulling Data Points or API Metrics for Your URLs

This tool is really flexible for pulling data for URLs in the “Results:” column. Technically, if you wanted, you could drop a list of URLs into the “Results:” column from Google Analytics, or from an exported URL list from a different tool or a crawl you’ve performed elsewhere, etc. Basically, you can pull data points on any URLs you have in the “Results:” column. In addition, you don’t have to pull API data from all three APIs or in any particular order, etc. When pulling the data, the program knows to look at the header of each column and decide where you’re trying to pull data from.

After the header columns are set up to pull the desired data points from each desired API, to activate the data pull, click the “Pull URL Data” option from the “Google Results Menu”. Screenshot below:


If the script times out because of an error similar to “Script Execution Time Limit” (based on Google Drive limits), the option marked “Continue Pulling URL Data (Begin From “Thinking…” Rows)” will pick up where the script timed out. Usually the script will time out if you’re pulling data for a large number of URLs.

This tool is built to allow you to have maximum use of of each of these APIs. We’ve built the tool so that when the program encounters one of the three services or APIs you’re pulling data from, if you have not entered a key for this particular session, it will ask for an API key for that service. This is intentional in the design so that queries against each of these APIs are not pulling away from any other user’s credits. Entering your own API key ensures that you and only you are using your API credits for each particular service.

Note: Future versions of this tool will remove the need for entering the API key for each service at the start of each session. You will enter the key once. The tool will verify the validity of the key and safely store the key for your future use of the tool.

As it works now, the prompt appears. With each prompt for the API key, a link is provided for you to go get an API key for that particular service. A screenshot with an example is below:


If you do not already have an API key, copy and paste the API Key link into a browser and retrieve your API Key from that service. At the time of writing, the Mozscape API is available free to anyone. However some features of the tool are paid. This tool is built so you can utilize the free options. If you’d like all options, however, you have to have a paid subscription to the Mozscape API. SharedCount did not have an API Key until after the tool was created. The tool still works without it. Future versions will include an input for the SharedCount API Key. The key is free and will allow higher usage limits so it will be beneficial in the future. The SEMRush API Key is only available via a paid subscription.

Mozscape API Functionality

The integrated Mozscape API details are found here: If trying to pull Mozscape data, you will be prompted for two items, a Mozscape API Access ID and a Mozscape API Secret Key. You can obtain your Mozscape Access ID and Mozscape API Secret Key from this link, A screenshot of each prompt for credentials is below:



Again, this API is available for free with limited functionality. You’ll see in the relevant dropdown which options are free and which are paid. Here’s an example:


Items marked as “(paid)” are only available if you have a paid subscription to the Mozscape API.

To pull Mozscape API data, you’ll have to cut and paste the dropdown from the Moz Dropdown Copy in the first two columns to the header of whichever column you’d like that data to be in on the spreadsheet. The dropdown looks like this:


Copy and paste that dropdown to the header of any column (starting from column D and moving up) in the spreadsheet. Screenshot below:


This example pastes the “Moz Dropdown Copy” in the first column next to the search results. You can put any of these dropdowns in any order. If you want something other than Domain Authority from the Mozscape API, click the dropdown and select the desired Mozscape data. Screenshot below:


Copy and paste this header as many times as you’d like selecting the desired Mozscape metric for each header.

SharedCount API Functionality

The SharedCount API pulls social analytics data for five major social networks. Details for the API can be found here: SharedCount currently includes Google+, Twitter, Facebook, LinkedIn and Pinterest. To pull SharedCount API data, you’ll have to cut and paste the dropdown from the Social Dropdown Copy in the first two columns to the header of whichever column you’d like that data to be in on the spreadsheet. The dropdown looks like this:


Copy and paste that dropdown to the header of any column (starting from column D and moving up) in the spreadsheet. Screenshot below:


This example pastes the Twitter dropdown next to the Domain Authority dropdown from the Mozscape API. You can put any of these dropdowns in any order. If you want something other than Twitter from the SharedCount API, click the dropdown and select the desired SharedCount (or social network) data. Screenshot below:


Copy and paste this header as many times as you like, selecting the desired social network for each header.

SEMRush API Functionality

Currently, the data integrated into this tool from the SEMRush API is basic. The SEMRush API is extensive and can build over 30 unique reports by default. We plan to release a future tool incorporating the entire default SEMRush API. The current data integrated is one data point, “Estimated Domain Traffic”. First things first, the SEMRush API requires a paid subscription. Details for the API can be found here:

Note: If you decide to pull this data, you will not be pulling estimated traffic for a specific URL. You will be pulling traffic for that specific URL’s domain. The script automatically pulls out the domain from any provided URL and looks up estimated domain traffic. Estimated domain traffic according to SEMRush is based on keyword rankings, click-through rates and Google search volume for all keywords ranking in positions 1-20 for a particular domain that SEMRush has identified with an associated website.

To pull SEMRush API data, you’ll have to cut and paste the dropdown (which is not really a dropdown ’cause it’s currently only one data point) from the SEMRush Dropdown Copy in the first two columns to the header of whichever column you’d like that data to be in on the spreadsheet. The dropdown looks like this:


Copy and paste that dropdown to the header of any column (starting from Column D and moving up) in the spreadsheet. Screenshot below:


This example pastes the Estimated Domain Traffic dropdown next to the Twitter dropdown from the SharedCount API. You can put any of these dropdowns in any order.

The “Google Results Menu” Options

Some of these items were already mentioned in these instructions, but this section should serve as a recap. Here’s a screenshot of the Google Results Menu and each of the options:


1. Pull Google Results (w/ current options) – Use this option once you’ve set up your search options and you’re ready to perform your Google search and pull back the URLs for your search query.

2. Pull URL Data – Use this option once you’ve pulled in the Google search results or pasted in your custom list of URLs to pull search data.

3. Continue Pulling URL Data (Begin From “Thinking…” Rows) – Use this option if the script throws an error when pulling data. A related error could be along the lines of “Script Execution Time Limit”. This is based on a Google Drive script time limit. You’ll run into this error if you’re pulling lots of data points across lots of different URLs.

4. Clear URL Data – Use this option if you want to clear the data points returned for each URL. Ideally, you would run this when you’re ready to restart your data pull.

5. Check for Spreadsheet Update – By default, your spreadsheet checks to see if it’s up-to-date each time it’s opened. However, if you want to manually activate a check once you’ve already opened the document, you can click this item from the “Google Results Menu”.

Download the Google SERP Analysis Tool

You can get a copy of the tool here. The tool is locked; make a copy to unlock this or any other Google Drive tool.


In addition, your feedback is important to us. Please let us know how we can make this tool better.

Image Credit: Flickr

Sponsored Resources

Want more resources

View all Resources



Our publication contributors combine decades of experience with unique insights into the content promotion and distribution industry.
Chad Pollitt Co-founder Relevance
Jay Baer Marketing Strategist, Speaker and Author
Gini Dietrich CEO Arment Dietrich
Jason Falls Social Media & Public Relations Thought Leader
Joe Beccalori CEO Interact Marketing
Douglas Karr Founder & CEO & DK New Media
Brianne Carlon Rush Content Director Kuno Creative
Janine Popick Co-founder & CMO Dasheroo
Arnie Kuenn CEO Vertical Measures
Pam Didner Global Content Marketing Strategist & Author
Chirag Ahuja Head of Marketing WorkflowMax
Jayson DeMers Founder & CEO AudienceBloom
Erik Deckers Professional Writer Pro Blog Service
Bernie Borges CEO Find and Convert
Jessica Stephenson VP Marketing ExactHire
Michael Ferrari Marketing Consultant Pen Cap Online Marketing
Larry Alton Freelance Writer and Editor
David Tile Founder & Director Nimble Media
Kelsey Libert Marketing VP & Partner
Dan Steiner Co-Founder & CEO Elite Legal Marketing
Joydeep Bhattacharya Relevance Contributor
Jonah Bliss Founder CMO ContentIntent
Andrea Lehr Promotions Supervisor Fractl
Fernando Labastida Co-Founder Content Propulsion
Dan Moyle Creative Dir. Marketing AmeriFirst
Dennis Yu Chief Technology Officer BlitzMetrics
Arnaud Roy VP Marketing Augure
John Rugh Copywriter/Content Marketing Specialist
Justin Spicer Content Researcher, Producer & Editor
Michael Becker Marketing Support Spec. Teradata
Anna Johansson Freelance Writer
Amanda DiSilvestro Content Editor and Writer HigherVisibility
Sujay Maheshwari Founder & CEO
Kelly Coulter Online Marketing Strategist
Taylor Radey Senior Consultant PR 20/20
Rodger Johnson Public Relations Leader & Consultant
Simon Penson Founder & Managing Dir. Zazzle Media
Danielle Wolter Nolan Co-Owner
Fernando Cuscuela Founder & CEO Everypost
Kelly Smith Content Manager CourseFinder
John McTigue EVP Kuno Creative
Yogita Arora Content Strategist Zoomph
Jordan Teicher Associate Editor Contently
Jonathan Crowl Digital Marketing Writer & Editor
Brian Honigman Marketing Consultant, Writer & Professional Speaker Skyword
Katherine Halek Content Strategist
Amanda Drinker Dooley Community Product Marketing Manager Netline
Anton Rius Digital Marketing Consultant More Than Metrics
Matthew Zajechowski Outreach Manager Digital Third Coast
Kevin Bailey Co-founder DigitalRelevance
Peter Chen Digital Marketing Consultant DigitalRelevance
Luana Spinetti Multi-Specialized Freelancer
Kyle Harper Writer Skyword
Elad Natanson Founder appnext
Maël Roth Content & Inbound Marketer Park7
Quin Woodward Pu Marketing Director Audienti
Greg Shuey Co-Founder Stryde
Jean Bansemer CEO My Web Writers
Owen Andrew Journalist
Luke Kintigh Global Content & Media Strategist Intel

In case you missed

Read more of the latest news and insights from thought leaders in content promotion and distribution.


Get the latest content promotion news and insights everyday.

Champion Sponsor

Relevance is proud to present our Champion Sponsor that helps make our site possible.

Cision Logo

© 2017 Relevance | Content Promotion News & Insights

Connect with RELEVANCE

Thank you for subscribing Your subscription has been confirmed.