Blog

If you spend too much time in your daily SEO reporting activities, pulling, integrating and sharing your SEO traffic data from Google Analytics and link related metrics from tools such as Open Site Explorer you have arrived to the right place! The Solution: An SEO Traffic & Link Popularity Dashboard in Google Docs.

Let’s use the APIs of Google Analytics and SEOmoz MozScape to import their data to Google Docs and easily automate and share our SEO Dashboards. I promise it’s going to be much easier than it sounds (no coding involve!), since we’re going to use some already created scripts:

To import Google Analytics data to Google Spreadsheets we’re going to use the Google Analytics integration with Apps Script released last August that allows us to pull and display the data in Google Docs.

To facilitate the process we will use as a template the Google Analytics Report Automation Script that can be found by going to the Google Spreadsheets Script Gallery (in the Tools menu) and searching for “analytics” as shown in the following image:

This script can be easily installed and run as explained in the following step-by-step video:

Once the script is installed we need to configure it to pull the relevant SEO traffic data we want for our dashboard. For this example I will configure it to get the visits, pageviews per visit, goal completions and conversion rates for the top 50 organic landing pages and keywords of the last 7 days, that will be published in a sheet named “OrganicWeek”:

If you want to add or modify any metrics or dimensions you can use this Google Analytics dimensions and metrics reference and test with the Google Analytics Query Explorer.

After running the script we will get the following sheet with the desired Google Analytics data:

Note how I have marked in red two areas of the sheet:

1. The key: The characters in the “key=nnnnn#gid” area. We’re going to use this later to import the data of the sheet from another one to be used for the dashboard.
2. The data: The organic traffic related data we’re going to use for the dashboard. I have selected an unfiltered profile to show how you can do in the case you don’t have a filter to show the full URL of the pages (which is recommended although unfotunately not that common). Also, in this case we see many “(not provided)” as top keywords, so if you don’t want to include these you may want to apply a segment for organic traffic without “(not provided)”.

Additionally we can also configure the script to be run automatically at any time and frequency (by going to Tools > Script Editor > Resources > All your triggers), for example, every day in the morning:

With this we have the first part of the data we need in Google Docs! Let’s continue with the second part…

To get Open Site Explorer’s link related metrics we’re going to use the SEOmoz data for Google Docs tool from Chris Le. Chris explained in this SEOmoz post how the script works and he shared an easy to configure template so we could copy and use it, thank you Chris!

After copying the template and adding our SEOmoz API credentials we will automatically get link related metrics such as MozRank, page authority, domain authority, links, external links, http status code and title for a specific set of URLs that we can add to the sheet:

Since we want to get these metrics for the landing pages with more organic traffic that we have obtained from Google Analytics what we’re going to do is to import the URLs of the landing pages from the previously generated “OrganicWeek” sheet into this one using the ImportRange function:

The requested URIs we obtain don’t show the domain name and we need it to be able to get their link metrics from the SEOmoz API so we’re going to add a “Concatenated URL” column, using the Concat operator to add our full domain name:

Once we have the full URLs we can obtain the desired authority and link related metrics (MozRank, PA, DA, links, external links, http status code, title) for the top organic landing pages of the Google Analytics report:

Now we have the second part of the data we need in Google Docs! Let’s create the SEO Dashboard…

It’s time to put the SEO Dashboard together by importing the data from the two previous Google Docs sheets. The first part of the dashboard is going to be imported from the sheet with Google Analytics organic traffic data:

And the second part from the SEOmoz authority and link metrics sheet:

Then we will have a complete table with the SEO related information we imported from both sources:

We can also re-organize the data to be more easily visualized with the chart option. For example, creating a table to see the top landing pages organized together with their keywords showing also their titles to have a better idea of their content and possible keywords in case we have (not provided):

Or simply by creating a graphic visualization of the data!

Finally, one of the best part if that we can share our SEO dashboard with a link, giving only the type of access we want -to view or also edit-:

With this example I hope you’ve gotten more ideas and see the possibility to build your own SEO Dashboard by using Google Docs, automatizing some of the more time consuming SEO tasks.

Photos under Creative Commons taken from Flickr: Photo 1 & Photo 2.

This post is also available in: Spanish