0 items - $0.00

Your shopping cart is empty

Why not add some items in our Shop
Call 1-844-640-8203

Automating Google Analytics reporting via Google sheets

Comments are off for this post.

Im often shocked at how much time some marketers spend trawling through the Google Analytics interface to pull stats together for reporting purposes. By manually extracting data from Google Analytics on a regular basis, youre using up valuable time that would surely be much better spent on non-menial tasks.

As a digital agency, we have recently delivered huge time savings by automating our client reporting via the Google Analytics add-on for Google sheets, which has allowed us to set up highly customisable analytics dashboards for each of our clients, providing them with a concise overview of their performance at all times.

In this post, I explain how you too can automate the generation of custom Google Analytics reports within Google sheets to display key SEO, PPC, ecommerce, social media and email marketing metrics and projections for your clients or direct reports.

Report Overview
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

Step 1: Download the add-on

To start pulling data into Google Sheets, youll firstly need to download the GA add-on. To do this, open Google sheets and navigate to Add-ons > Get add-ons:

Get Add-ons
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

Note that in order to use this add on you must have access to the Google Analytics profile that you wish to report on via the Google account youre using with Google Sheets.

Once youve downloaded the add-on and ensured you have access to the GA profile in question, youre ready to start creating your first report.

Step 2: Define metrics and dimensions

The first thing youll notice after downloading the add-on is the create a new report table:

Create a new report
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

Here you will be able to specify which Analytics property and view you would like to report on and choose whichmetrics and dimensionsto use for your report. This can be further customised after clicking create report, so dont spend too much time customising your metrics and dimensions at this early stage.

Once youve clicked Create Report, a new sheet will be created called Report Configuration, which is where you will be able to tweak the settings of each report youd like to create.

Heres an example of what the Report Configuration sheet looks like when used to generate multiple reports (separated by columns):

Configuration Options
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

The report configuration sheet doesnt have drop downs or help options for beginners, so I would strongly advise having the Analytics API reference guideopen to identify which dimensions and metrics you need to use in this sheet when creating a new report.

Within the report configuration sheet, there are 6 mandatory fields:

Report name what you would like each report to be calledType leave this as is, core is the defaultView (profile) this references the Google Analytics property you originally selectedviaadd-ons > Google Analytics > create new reportStart / end date the date ranges you would like to report onMetrics the metrics you would like to include in your report seereference guidefor detailsDimensions the dimensions you would like to use for your report seereference guidefor details

In addition to these fields, there are options available to sort the data, apply filters, use pre-defined segments from your GA account, specify sampling levels and limit the number of results returned in each report.

Step 3: Customise and generate reports

To generate a report in Google sheets you must firstly think about what you want to display in your final report. For example, a basic SEO report can be generated to display the following information:

Report Example
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

Broken down, the SEO report above displays:

Data from the last full month vs. YOY. Metrics reported include sessions, % new users, bounce rate, page views per session, average session duration, transactions, revenue and conversion rateTop landing pages from the last full month vs. YOY. Metrics reported include sessions, bounce rate, conversions and revenueData from the last 12 months with a month on month comparison and trend line. Metrics reported include sessions, bounce rate, conversions and revenue

To generate a set of reports that will allow you to build the SEO dashboard shown above, you would need to configure your report configuration sheet by setting up the 3 reports displayed below:

Configure SEO options
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

The report settings in column E are designed to pull together SEO metrics for our YOY reports, while the settings in columns F and G will provide us with data on the top landing pages for the last full month (column F) and the same month a year previous (column G).

entrepreneurs

Now, because you will be automating the generation of these reports, it is important that your start and end date settings update automatically. To do this, we would need to enter formulas into the report configuration so they update automatically by using variousDATE formulas.

In the example displayed in column E above, we are lookingto create a report that includes data for the last complete month vs.the same montha year previous (and each monthin between). To capture all of this data, we need to ensure that the end date automatically updates to the last day of the last fullmonth, and the start date is set 13 months prior to the end date column.

https://en.wikipedia.org/wiki/Search_engine_optimization

We can achieve this by usingthe following formula:

In theEnd datecolumn, we would set the last day of the most recentfullmonth. Heres the formula in full:=if(today()=eomonth(today(),0),today(),eomonth(today(),-1))In theStart datecolumn, we need to ensure the date automatically updates to 13 months prior to the date referenced in theEnd datecolumn. To do this, we can use theEDATE function, which returns a date a specified number of months before or after another date. To do this, we would use the following formula (wherethe end date cell = E6):EDATE(E6, -13)

To report on just the previous months data (as displayed in column F), you would instead need to use the following formula:

For the end date, we want to display the last day of the last full month: =if(today()=eomonth(today(),0),today(),eomonth(today(),-1))For the start date, we want to display the 1st day of the last full month, which can be achieved by referencing the end date column in the following formula =eomonth(F6,-1)+1

And to compare this to the previous year (as displayed in column G), you would need to replicate that formula, minus a year:

To do this, we would use the following formula to return a date a specified number of months previous to another date. So for the end date, F6 would refer to the last day of the most recent month: EDATE(F6, -12)And for the start date, F5 would refer to the first day of the most recent full month: EDATE(F5, -12)

You will note on the example report configuration screen that I have used a filter on all 3 reports to specify that I only want to report on traffic classed as organic.

This is achieved via use of the following formula in the filter row:

ga:medium=~organic

ga:medium is the dimension, =~ is theoperatorfor matches and organic is the onlymediumwe want to report on.

To add more than one filter to a report, you need to consider whether the filter should be an and or an or.

To do this, just remember the following:

Or = Comma (,)And = Semicolon (;)

For help with the syntax for these fields, check out thisguide for Filters with the API. The filters field allows you to drill down into specific dimensions and metrics.

Once youve finished configuring your reports via the report configuration sheet, navigate to the add-on and click run reports:

Generate Report
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

Each report you create will now be displayed on a new sheet, however these individual sheets can be hidden once you set up a dashboard to reference this data. This is simply raw data devoid of any formatting, so the next step is to create a dashboard tab to better visualise this data.

Step 4: Set up your dashboard

To create a basic dashboard to display data from multiple reports in Google sheets, youll firstneed to create a new sheet. On this sheet, you should create an empty table with row headings that correspond to the headings in your individual reports / sections of the reports youd like to use on your dashboard.

For example, we could create a table like this to reference the data in the organic traffic report weve just created:

SEO table 1
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

In the above example, I have createda new row for each month (13 rows in total) and usedthe following formula to automate the text in the table to reference the month prior to the current one, as the final row heading:

=text(date(year(today()-1),month(today())-1,1),MMMM)

To complete the months, we need to roll backwards from the most recent month by changing the -1 to -2 for two months prior, then -3, -4 all the way back to -13, to give the current month and 13 preceding months in the example above,

The Year column in the table is simply created with the same formulas listed above, but replacingMMMM withYYYY.

Once you have your date rows set up, you can simply use a cell reference to map the data over to your raw analytics reporting sheets over to your newly created table.

Of course, creating a comparison table in your dashboard is just the beginning. You can move on from here to create and formatmultiple reports within your dashboard sheet and startadding chartsand sparklinestovisualise the data included in your dashboard tables.

Step 5: Visualise the data

Google sheets allows you to create a wide variety of charts. For example, you could create a chart to display the split of website traffic by source:

Traffic Sources
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

You can also create line charts to compare metrics YOY, as we have done to compare all website sessions vs. YOY:

All Traffic YoY
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

To create charts in Google sheets, I would recommend creating a new sheet (which can later be hidden) purely for the purpose of referencing the data you want to include in your charts. This will allow you to use attractive headings for your metrics rather than the standard ga:sessions headings in the individual report sheets.

social media

For example, to create the pie chart displaying the split of traffic in the last full month, I created the following table within a charts sheet and referenced the data from a report created to measure sessions split by channel:

Chart Creation - final
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

In addition to creating basic charts, you can use the charts function to map projections vs. YOY data. For example, we have started adding projections into some of our reports by using the AVERAGE function to estimate data for the year ahead based on average YTD performance:

Projected traffic
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

Step 6: Automate

Once youve created a set of reports and customised your dashboard, you can automate the generation of your reports using the Google Analytics add-on settings.This will ensure your reports are always up to date, and isparticularly useful if you used formulas in yourstart/end dateswithin the report configuration sheet as we outlined earlier.

To automate the generation of your reports, navigate to: Add-ons > Google Analytics and schedule your reports to run as and when you require:

Automate reports
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

You can then share this Google sheet in just the same way you would any Google Drive file. Simply click the share button and input the email addresses of anyone youd like to grant access to:

Share reports
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

Bonus step: Explore other APIs

It is no secret that Google sheets is a great tool for collating data from multiple sources using APIs. Major keyword tracking providers such as Authority Labs and STAT provide access to their APIs, so there is no reason you cant attempt to collate all relevant SEO data into a single Google sheet for your clients or bosses.

In addition to collating all your SEO data, you could even pull social media data into Google sheets and display email marketing performance stats using the Mailchimp API.

Conclusion

As a digital agency, we have delivered huge time savings by moving to this method for our monthly reporting, and at the same time have been able to provide each of our clients with customisable analytics dashboards which are accessible via a single Google sheets URL at all times.

Working client-side, I often found that we had to tailor any reports delivered by an agency into an internal format, so bonus points for any agencies who work closely enough with their clients to deliver a dashboard theyre happy to use to display all the digital marketing metrics they need to review.

Of course, this reporting format is not solely for agency use. If you are an in-house marketer theres no reason you cant build a customised dashboard and share it with key stakeholders within your business who are interested in reviewing certain metrics within GA.

Useful resources

banner-content-course
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr

h
  • Facebook
  • Twitter
  • Google+
  • Evernote
  • Pinterest
  • LinkedIn
  • Blogger
  • Tumblr
Ben Wood

Ben Wood is the Head of Digital at UK based marketing agency Hallam Internet, and has previously gained extensive client-side experience at a well known FTSE100 company. Ben specialises in SEO, PPC and Web Analytics.

http://feedproxy.google.com/~r/stateofsearch/~3/3EDEcevQHxs/

Related Post

Share this article

Comments are closed.

Pin It on Pinterest

Share This

Share this post with your friends!