Bringing Google Analytics Data into Google Spreadsheets

Bringing Google Analytics Data into Google Sheets

/

blog-google-add-on

If you’re reading our blog, the chances are pretty favorable that you’ve used Google Analytics at some point. Maybe you’ve used it to check on basic metrics like overall pageviews and sessions from time to time, or maybe you’ve performed more in-depth reporting with filters, advanced segments and custom dimensions.

You’ve clicked, scrolled and explored using the date range calendar, the left navigation menu and the tables and graphs showing your data. All of this within the Google Analytics interface, because that’s where the data lives and that’s where the data stays. Right?

Not so! In this post, I’ll introduce you to a really easy method (seriously) to make automated reports completely within Google Sheets using the Google Analytics add-on. If you’ve ever caught yourself manually typing numbers into spreadsheet cells for web reporting, this is one solution to automatically get the numbers you need into your reports.

Data can be accessed outside of the web application thanks to Google Analytics’ Core Reporting API. Veterans may remember the precursor, the Magic Script, developed by Googler Nick Mihailovski in 2012 as a way to work with and automate Google Analytics reporting in Google Docs. This add-on was built on top of the script and shortens the path from opening a Google Doc spreadsheet to running a report.

Getting Started

If you’re not a programmer, don’t run away when I mention ‘API’! This does not require any coding experience. Here is what you need to create a custom spreadsheet report with your Google Analytics data:

  • Access to a Google Analytics account
  • A blank Google Docs spreadsheet (you can create one in your Google Drive)

To start, open the spreadsheet. In the menu, look for the ‘Add-ons’ then select ‘Get Add-ons.’ NOTE: You have to have upgraded to the latest Google Spreadsheets, identified by a green checkmark in the lower right corner and the Add-ons menu option.

Add-On-Menu

A pop-up should appear showing a lot of different add-on options. In the search bar within the pop-up, type ‘Google Analytics’ to find the one we need- it should be the first result. Now, just click the button to add it and grant Google Analytics the necessary permissions.

Google-Analytics-Add-On

Once it is installed, you can then go to Add-ons > Google Analytics > Create Report to start your first report. A new prompt should appear on the right side of your spreadsheet.

Report-WizardThis is where you will specify which property and view you would like to report on. You can also choose which metrics and dimensions for your first query, but everything else is optional. After clicking ‘Create Report’, your workbook should now have a sheet called ‘Report Configuration.’

You can add more reports in the columns and every report will generate a new sheet. It is generally best practice to add a sheet to act as the ‘finished product’ which references the data in the report sheets. When you select ‘Run Reports’ from the add-on menu, everything will be updated.

Metrics and dimensions need to be formatted a specific way. For example, instead of just typing ‘users’ in the cell, we need to type ‘ga:users.’ Having the reference guide open is useful to find which metric or dimension you need to use. There is even a Query Explorer widget where you can test the output of your queries before putting them in your spreadsheet.

Below are some reasons to use the add-on instead of or in addition to exporting reports from the interface:

Specific and Granular Reporting

Report on exactly what you or your department needs. No more, no less. There’s no reason to go through the interface and take more steps than needed to answer a simple monthly/quarterly/yearly question of “What is ‘x’ compared to ‘x’?”

Branding

Creating your own reports outside of Google Analytics means that you control exactly how it looks. Unlike reports from the interface, you’ll have the ability to add whatever logos, fonts and branding elements that fit within your organization.

Sharing

You can share your Google Docs spreadsheets the same way you would any Google Drive file. Whomever you share it with can view and edit the report. However, only Google accounts that have access to the corresponding analytics account can run the reports to update it.

Dashboards

If you’ve used the dashboard functionality in Google analytics, you might have noticed that there is a limit of 12 widgets. Other limitations include a homogenous date range for all widgets, a limit to how many rows or chart items can be displayed and the inability to selectively add segments. With the add-on, you can create a dashboard in Google Docs without those limits.

Work your data

Instead of just looking at the numbers in Google Analytics, make them work for you. Having the data in a spreadsheet give you the ability to add, subtract, get percentages and use formulas to gain more specific insights. For example, you’d be able to easily answer the lengthy question, “What percentage of users who visited blog page titles containing ‘Tag Manager’ came from organic search in the last 30 days?”

An example of that configuration is below. Notice that not every field needs to be filled-in. I’m using a descending sort by users and I’ve applied two filters to my first query and three filters to my second. ‘=~’ is equal to the RegEx or contains option. So when I use ga:pageTitle=~(tag manager|GTM), this means that I am looking for page titles containing ‘tag manager’ or ‘GTM’.

reportconfiguration_1

It may take some time to set up, but once you have the report framework, you only need to change the date range every time you use it. For more information, check out Google’s help section offering suggestions and more detailed instructions for complicated setups.

If you are more of an Excel person, another method of report automation is available by using Shufflepoint. In any case, remember that when it comes to reporting, you are not limited to the Google Analytics web interface!

Samantha is a Senior Analytics Engineer at LunaMetrics. She has a passion for exploring data and loves the excitement of finding solutions and explanations behind metrics. Her background includes advertising, SEO, and analytics as well as involvement in the arts. When she isn't logged into Google Analytics, she can be found testing her conversion rate at new things from golf to bread-making to new programming languages.

  • Jim

    very useful,
    But there is no trigger for automated updates;
    Do you see a way to get automated updates soon?

    • Samantha Barnes

      Jim- that’s a really great point to bring up. As of right now, the only way to set up the reports to run and retrieve data automatically is through the script editor. It would be nice if a future update of the add-on included triggers, though! Hint, hint, Google…

  • http://www.appliancesonline.com.au Ash Rane

    Great post. I have been using this for the past couple of weeks. One tip from me is to use a summary sheet and setup a start and end date reference cell, then you can use Google sheets date functions to change for example yesterday is -1 and last week us -7 etc

  • http://www.duo.be Frederik Vermeire

    Hi Samantha,

    Great post! Now my data results are like 250,00 €. I like to have it it in another format like 250 €. Do you have any hints about this?

    Frederik

  • Bastiaan van de Werk

    Hi Samantha, thanks for sharing this. I’m having a little problem I want to extract the users per medium per week. (direct / cps / organic / referral)

    So I use the following settings:

    Metrics ga:users
    Dimensions ga:week,ga:medium
    Sort ga:medium,ga:week

    Will put the results in one single column. Is there a way to get the results sorted in two columns next to each other? I’m not sure how to do this.

    Thanks in advance for helping me out!

  • Graham

    How do you set up the segments?

    • Samantha Barnes

      Hi Graham,

      Sorry for the delay, but to set up segments you can use the “Segment” field in the report configuration. The format of the segment is below:

      sessions::condition::ga:source==google

      You can choose whether you want the segment to be session- or user-level and then apply the conditions.

      Best,
      Samantha

  • Edgar Lechaudel

    Hi Samantha, your article is very helpful. Thank you very much for this ressource! But I’m wondering how to use Segment? I mean segmenting data to see on Paid Traffic for example. I’m browsing the web since a few hours but just can’t find any ressources about that… Do you have any idea?

    Thank you very much in advance.

    Cheers,
    Ed

    • Samantha Barnes

      Hi Ed,

      To use segments, go the the report configuration where it says “Segment” and add the conditions in the format below:

      sessions::condition::ga:medium==cpc

      You’re telling the Reporting API that you want the segment to be session-level (you can also do user-level), then the conditions. I attached an image below.

      Hope this helps!

      Best,
      Sam

      • Edgar Lechaudel

        Hi Samantha,

        Thank you vey much for your reply! This is very helpful 🙂
        I have the feeling that there are too few ressources about the Segment topic… whereas it is very important to create a relevant report.

        All the best.
        Cheers,
        Ed

  • Ravi Ranjan

    Wow……really awessome!!

  • Nick

    Can you pull data from more than one view ID into the same report?

Contact Us.

LunaMetrics

24 S. 18th Street, Suite 100,
Pittsburgh, PA 15203

Follow Us

1.877.220.LUNA

1.412.381.5500

getinfo@lunametrics.com

Questions?
We'll get back to you
in ONE business day.