Create your SEO Dashboard in Google Docs with Google Analytics and SEOmoz MozScape Data

SEO Dashboard Google Docs

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:

Google Docs Analytics API Data

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:

Google Analytics Report Automation (Magic) in Script Gallery

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”:

Google Analytics Dashboard with API

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:

Google Analytics Dashboard (Magic) Google Docs

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:

Google Analytics Report Automation Timer

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

Bring SEOmoz data to Google Docs

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:

SEOmoz API Google Docs Tool

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:

ImportRange SEOmoz API Google Docs

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:

Concat SEOmoz API Google Docs

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:

SEOmoz API Google Docs Metrics

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

Get the Google Analytics and SEOmoz Api Data in Google Docs

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:

GA SEOmoz SEO Dashboard Import

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

Google Analytics SEOmoz SEO Dashboard Import

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

SEO Dashboard Google Docs

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):

Google Docs Top Landing Pages Keywords Table

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-:

Share Google Docs SEO Dashboard

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