Automate Google Analytics reporting in Google Sheets

21 April 2017

15-20 mins

Learn how to automate Google Analytics reporting in Google Sheets in order to make your data easier accessible and shareable within your company.

In this blog post, I will show you how to automate Google Analytics reporting in Google Sheets from start to finish in a three-step process. I will talk you through an example of a report on product signups attributed to product-related webpages as commonly used among companies with a freemium product business model. Using the laid out principles in this post, you can automate Google Analytics reporting and have the data from your automated reports available in Google Sheets for any other business scenario you want to report on.

With increasing complexity for businesses, the manual efforts for web analytics reporting can become extensive. Any time spent on pulling or retrieving data is the time that can not be spent on actual data analysis. If you automate Google Analytics reporting, you are freeing up the time for more valuable tasks such as analyzing the data. Additionally, it is very common within companies that only a fraction of the employees, who could make use of web analytics data, is actually able to regularly retrieve that data helping to answer current business questions. In many cases, for instance, the IT department’s resources are the bottleneck for data retrieval. This is a real issue as it slows down data-driven decision making for companies. These two common aspects call for a solution covering reporting automation and data share-ability.

With the help of the Google Analytics Add-on for Sheets you can connect Google Sheets with the Google Analytics API and run scheduled reports to automatically pull data into Google Sheets. This method allows you to:

  1. save a lot of time on reporting by eliminating manual efforts for data retrieval,
  2. manipulate the data as needed by applying any calculations or formulas to it,
  3. have more liberty on the type of data visualization compared to the Google Analytics UI and
  4. share web analytics data in your Google Sheet more easily within the organization.


The described approach on how to automate Google Analytics reporting is most suitable for your business if you:

  1. had less than 500K sessions for your website at the Google Analytics view level for the last 6 months and hence encountered no sampling as can be seen under Sampling controls here. Be aware that even if your company uses Google Analytics 360 and you are not seeing sampling within the Google Analytics UI that the Google Analytics Core Reporting API (as of April 2017) uses its own sampling threshold, independent of whether you use the free or paid version of Google Analytics
  2. can cover reporting on your business goals with less than 40 individually configured reports. For your estimation: one report with up to 10K rows can cover several pages and their pageviews, one report with up to 10K rows can cover several Google Analytics event labels (read also Google Analytics event tracking via Google Tag Manager to see how to set up events) and their counts and any used Google Analytics Segment requires a report on its own
  3. by using more configured reports, you may run into the general Google Analytics API quota limits, which can only be circumvented by using the API outside of the Google Sheets Analytics Add-On.

Now, let’s get started and see how you can automate Google Analytics reporting in Google Sheets to realize some of the listed benefits for your business. The post uses the following structure:

1. Understand parameters of report configuration in Sheets add-on

The first step of the three-step process of how you can automate Google Analytics reporting in Google Sheets is the report configuration for the Google Analytics add-on for Sheets. For this, we will use the example reporting scenario on product signups attributed to product-related webpages. You will see how we create a traffic, click and three signup reports, both for a monthly and weekly view, resulting in ten configured reports. These ten configured reports pull data from Google Analytics into Google Sheets.

1.1 Create a monthly traffic report

After you have installed the Google Analytics add-on for Sheets, you start by creating a new report (Add-ons → Google Analytics → Create new report) as shown in image 1.

Img. 1: Create new report with Sheets Add-On

The first report, we are going to create, is a monthly traffic report. All the entries that you make are changeable afterwards. I chose M_Traffic as report name, selected my Account, Property, View (Profile) and Unique Pageviews (see info 1) under Metrics and Page and Month of the year under Dimensions (see image 2).

Img. 2: Create Google Analytics report

1: Difference between Pageviews and Unique Pageviews

The most suitable metric is the one that serves best in the context of the reporting that you want to accomplish. In this case, we want to get insight on how certain pages are attributable to product signups. By choosing Unique Pageviews as metric, several views of the same page within one session will only be counted once. Unique Pageviews in this context acts as a count of whether within a given session the user got exposed to a page or not as opposed to Pageviews, which represents all page impressions in total of that page including several counts for within one session.

Then your report shows up in a sheet called Report Configuration (image 3) that serves as your control center for the different reports that you may want to create. By default, the fields Report Name, Type, View (Profile) ID / ids, Last N Days, Metrics and Dimensions have an assigned value, partly from the input during report creation and partly automatically.

Img. 3: Monthly traffic report configuration

Those fields that did not pre-populate a value are not required, but can be helpful to further refine your report. In the following, I will explain all fields and their meaning.

Report Name:
Your chosen report name will be used for the newly populated sheet, when running the report.

Type:
The core reporting type concerns the Google Analytics Core Reporting API, which allows you to access most of the reporting data in Google Analytics.

View (Profile ID /ids):
When creating the report you can select Account, Property and View (Profile) from those that are recognized under your Google account.

Start Date | End Date | Last N Days:
By default for the time range setting, the value of Last N Days will be set to 7. For this monthly report, we are interested in reporting on the last six month, so the simplest approach is to choose 184 as value for Last N Days. The longest possible 6 months period is 184 days long ranging from May to October. For this setting, it is merely important to select a range that does not potentially exclude any days from our desired six months range, while it is not problematic to select a range that exceeds the number of days. It is important to note that with the filtering, which I will demonstrate later in this post, we will clearly assign measurements to the right month.

Metrics | Dimensions:
The selected Metrics and Dimensions will represent the column titles of our individual automated reports.

Sort:
You can sort the values for any of the selected Metrics and Dimensions in ascending order as default. By inputting ga:uniquePageviews we will filter by the number of Pageviews in ascending order and -ga:uniquePageviews will order by the number of Pageviews in descending order. In this scenario, we will not make use of this.

Filters:
Similarly, you can filter the values for any of the selected Metrics and Dimensions. For instance, if you are only interested in data for all pages for product-related pages that all live under example.com/product/, then you filter for this page path with the ‘contains substring’ ([email protected]) operator ga:[email protected]/product/ to only show matching pages in the report. Other useful operators are ‘exactly matches’ (==) and ‘matches regex’ (=~) as well as ‘does not match’ (!=), ‘does not contain substring’ ([email protected]) and ‘does not match regex’ (!~).

Segment:
This allows you to either reference a previously in the Google Analytics UI created Segment or to write a new query filtering on either sessions or users and condition or sequence. The use of the Segment parameter will be further explained later in this post as part of the monthly signup reports.

Sampling Level:
There are three possible values for the Sampling Level, these are DEFAULT, FASTER or HIGHER_PRECISION. If empty, DEFAULT will be applied. I recommend using HIGHER_PRECISION as we can schedule these automated Google Analytics reports and therefore their run is not time-sensitive as will be further demonstrated later. As mentioned in the introduction of the blog post, as of April 2017, the Google Analytics API itself uses sampling, in case the volume limits in Google Analytics are reached.

Start index | Max Results:
The Start index is a row reference of the results with 1 representing the first row of the results. The maximum results that can be returned is 10,000. The default for Start index, when left empty, is 1, and for Max Results 1,000. The Start index can be helpful, if our results exceed 10,000 (which you can see in the individual report sheet in the second row) and we want to contain only the last 5,000 rows, from 8,000 to 13,000 in our report, then 8,000 as value for Start index can be helpful to accomplish that. Also, the Filter option will allow to reduce the results to the most relevant.

Spreadsheet URL:
You can use this field to paste the URL of the individual report sheet, which will be created later, for usability benefits.

1.2 Create a monthly clicks report

Now, we like to build a report that pulls the click data from Google Analytics. For this M_Clicks report, you access events in Google Analytics that you use to click tracks. In this case, we will use one CTA click measurement for each of our three exemplary pages: example.com/product/overview, example.com/product/feature-page1 & example.com/product/feature-page2, with the following exemplary Google Analytics Event Labels product-overview-cta, product-feature-page1-ctaproduct-feature-page1-cta. Copy over the parameter values from the M_Traffic report for Type, View (Profile) ID / ids, Last N Days, Sampling Level and Max Results. For the following parameters, you will need to make adjustments:

Metrics | Dimensions:
For Metrics, we input ga:uniqueEvents and for Dimensions, ga:eventLabel and ga:month accordingly.

Filters:
Given that you will likely be tracking a couple of events, if you are taking full advantage of Google Analytics event tracking, apply the following filter to restrict to the page-specific CTAs to avoid possibly exceeding the set 10,000 row limit in your M_Clicks report. The most effective way how you can set the value for the Filters parameter is by using Regex: ga:eventLabel=~product-overview-cta|product-feature-page1-cta|product-feature-page2-cta.

Your configured report parameters should look like this (image 4):

Img. 4: Monthly clicks report configuration

The output of your M_Clicks report – once you clicked Run reports (see image 1) – should look like this (image 5):

Img. 5: Monthly clicks report output

1.3 Create monthly signup reports

As last step of the monthly reports, we will create three monthly page-specific signup reports (M_Signups_op, M_Signups_fp1 & M_Signups_fp2). Copy over again the values for Type, View (Profile) ID / ids, Last N Days, Sampling Level and Max Results from any of the two other previously created monthly reports. Next, you will need to adjust the values for MetricsDimensions, and Filters as shown below. Also, these monthly and the weekly signup reports require input for Segment.

Metrics | Dimensions:
Use ga:uniqueEvents for Metrics and ga:month and ga:eventLabel for Dimensions.

Filters:
Apply the following filtering: ga:eventLabel==product-signup

Segment:
Segments in Google Analytics are a powerful feature to look only at those visitors with a certain condition for instance. Under Segments in Google Analytics, you can also find Sequences, which allows you to do targeted pathing analysis. This is what we want to use in this context with the Google Analytics API to build a three-step sequence including (1) Pageview on a specific page, (2) CTA click on that page and (3) subsequent product signup. If the Segment has already been created in the Google Analytics UI previously, then you can use the Query Explorer to retrieve the gaid such as gaid::1 and enter this as value for the Segment parameter. Otherwise, and that is what we are going to do here, you define the sequence based on the API syntax. For step (3) according to above, the signups that followed a pageview on the product overview page as well as a CTA click on that page is defined by: sessions::sequence::ga:[email protected]/product/overview;->ga:eventLabel==product-overview-cta;->ga:eventLabel==product-signup

You can check out Google’s documentation about the Google Analytics API syntax for segments, if you are interested. For now, the most important aspect is to understand that ;-> as opposed to ;->> means that a step in the sequence follows immediately with the next hit. A hit in Google Analytics is defined as a pageview or click. If the last step ga:eventLabel==product-signup only occurs with several hits (clicks or pageviews) in-between after the previous step ga:eventLabel==product-overview-cta, then you want to use ;->> instead of ;-> to account for that and to be not too restrictive with your sequence definition. Ensure that the sequence is set up the right way depending on your measured website’s path.

Given that a sequence is page-specific and we are configuring reports for signups for three exemplary pages and with that three distinct sequences, we will also need to create three monthly signup reports. Getting the syntax and sequence configuration right can sometimes take a bit time, if you are encountering errors, when running the reports, and are debugging specific reports only, see info 2 at the end of this section for a tip.

Your configured monthly signups report should follow the approach here (image 6):

Img. 6: Monthly signup report configuration

The output of your monthly signup report for the overview page should look like this (image 7):

Img. 7: Monthly signup output for overview page

2: Using a partial run, when debugging individual configured reports

Sometimes, you might be debugging an individual configured report as the Google Analytics API returns you an error. The Google Sheets Add-on will run every report in your Report Configuration sheet from left to right until it encounters a completely blank column. In order to include reports only until a certain report in the run, you could temporary insert an empty column to the right of that report in the Report Configuration sheet. This hack becomes most valuable, when you are dealing with 30 or more configured reports. Also, you could just move the column of the error yielding report to the very left in the Report Configuration sheet.

1.4 Create a weekly traffic report

For the weekly traffic report, you can simply copy over the entire report configuration column of the M_Traffic report into the W_Traffic column. We will need to make adjustments now to that report across some of the report configuration parameters.

Report Name:
For this weekly report, I chose the name W_Traffic.

Start Date | End Date | Last N Days:
For this weekly report, we are interested in reporting on an overall 14-week period including the current and the last 13 weeks, which will later yield a weekly overview of a quarter or about 3 months. For End Date, we choose =TODAY() to report until the current day. For Start Date, we input the formula =TODAY() - (WEEKDAY(TODAY()) - 1)-(7*13) that will select the data of the Sunday 13 weeks ago (we do this, because Google Analytics’ dimension ga:week, which we will select later, counts from Sunday to Saturday as week. TODAY() returns today’s date and  (WEEKDAY(TODAY()) returns the day index number of today’s weekday (per default: Sunday=1, Monday=2, Tuesday=3, Wednesday=4, Thursday=5, Friday=6, Saturday=7). This part of the formula =TODAY() - (WEEKDAY(TODAY()) - 1) returns therefore the date of whatever the most recent Sunday was, representing the current week. From here, we can further subtract days, in our case in order to report on a 14-week period, we need the 13 weeks before the current week, this is accomplished by the last part in the formula: -(7*13).

Metrics | Dimensions:
We select ga:uniquePageviews as metric and ga:pagePath and ga:week as dimensions.

Your configured Google Analytics report should follow the logic here (image 8):

Img. 8: Weekly traffic report configuration

1.5 Create a weekly clicks report

Your next step, is to create the W_Clicks report, which does the same like the M_Clicks report, but uses a weekly time basis. Similarly to the previous report, you can simply copy over Type, View (Profile) ID / ids, Last N Days, Sampling Level and Max Results from the W_Traffic report configuration. Also, use the same values for MetricsDimensions, and Filters like in the W_Clicks report.

Metrics | Dimensions:
Use ga:uniqueEvents for Metrics and ga:eventLabel and ga:week for Dimensions.

Filters:
Apply the same Filters like in the M_Clicks report: ga:eventLabel=~product-overview-cta|product-feature-page1-cta|product-feature-page2-cta.

Your configured Google Analytics report for weekly CTA clicks should follow the approach here (image 9):

Img. 9: Weekly clicks report configuration

The output of the W_Clicks Google Analytics report should look like this (image 10):

Img. 10: Weekly clicks report output

1.6 Create weekly signup reports

For all the three, page-specific weekly signup reports (W_Signups_op, W_Signups_fp1 & W_Signups_fp2), copy over again the values for Type, View (Profile) ID / ids, Last N Days, Sampling Level and Max Results from any of the two other previously created monthly reports. Next, you will need to adjust the values for MetricsDimensions, and Filters as shown below.

Metrics | Dimensions:
Use ga:uniqueEvents for Metrics and ga:month and ga:eventLabel for Dimensions.

Filters:
Restrict to the product-signup event only by: ga:eventLabel==product-signup.

Segment:
As described for the monthly signup reports, you will need to create three distinct sequences for each respective page.

The three configured weekly Google Analytics signup reports should follow the logic below (image 11):

Img. 11: Weekly signup report configuration

The output of the exemplary W_Signups_op Google Analytics report should look like this (image 12):

Img. 12: Weekly signup report output for overview page

2. Set up trailing timelines in Google Sheets

After we configured all reports to automate Google Analytics reporting in chapter 1, we will focus on creating the actual sheets in Google Sheets that will reference our pulled Google Analytics data and will consolidate and present the data. This chapter 2 deals with creating trailing timelines in Google Sheets for the monthly and weekly reporting sheets and chapter 3 discusses how you use Google Sheets formulas to reference the data in our reports created in chapter 1.

2.1 Create a monthly reporting sheet

For the monthly example, you will see how to create a trailing monthly timeline for the last six months that automatically keeps current and does not require any additional work once set up. The timeline can also be easily extended beyond 6 months, using the same approach.

Img. 13: Monthly trailing timeline

As you can see in image 13, we will create a timeline with two rows, the first called Month and the second row called Month number. The Month number row pulls in the month of today’s date in G2 with: =MONTH(TODAY()). For previous months in cells B2 to F2, we will use formulas, which will automatically calculate the given month number depending on the current month number. The formula is based on the simple logic to check what the current month number is and then to subtract the respective month gap depending on whether the used cell represents the previous month or 4 month before the current month. Also, this logic is applied to any month number except for those month numbers that would be negative, in which case, the returned month number is calculated on the basis that months before 1 are 12,11,10…

For your convenience, you can find all cell formulas for the previous months periods below.

Cell F2 (1 month before current):=IF($G2=12,12-1, IF($G2=11,11-1, IF(G$2=10,10-1, IF($G2=9,9-1, IF($G2=8,8-1, IF($G2=7,7-1, IF($G2=6,6-1, IF($G2=5,5-1, IF($G2=4,4-1, IF($G2=3,3-1, IF($G2=2,2-1, IF(G$2=1,12))))))))))))

Cell E2 (2 months before current): =IF($G2=12,12-2, IF($G2=11,11-2, IF(G$2=10,10-2, IF($G2=9,9-2, IF($G2=8,8-2, IF($G2=7,7-2, IF($G2=6,6-2, IF($G2=5,5-2, IF($G2=4,4-2, IF($G2=3,3-2, IF($G2=2,12, IF(G$2=1,11))))))))))))

Cell D2 (3 months before current): =IF($G2=12,12-3, IF($G2=11,11-3, IF(G$2=10,10-3, IF($G2=9,9-3, IF($G2=8,8-3, IF($G2=7,7-3, IF($G2=6,6-3, IF($G2=5,5-3, IF($G2=4,4-3, IF($G2=3,12, IF($G2=2,11, IF(G$2=1,10))))))))))))

Cell C2 (4 months before current): =IF($G2=12,12-4, IF($G2=11,11-4, IF(G$2=10,10-4, IF($G2=9,9-4, IF($G2=8,8-4, IF($G2=7,7-4, IF($G2=6,6-4, IF($G2=5,5-4, IF($G2=4,12, IF($G2=3,11, IF($G2=2,10, IF(G$2=1,9))))))))))))

Cell B2 (5 months before current): =IF($G2=12,12-5, IF($G2=11,11-5, IF(G$2=10,10-5, IF($G2=9,9-5, IF($G2=8,8-5, IF($G2=7,7-5, IF($G2=6,6-5, IF($G2=5,12, IF($G2=4,11, IF($G2=3,10, IF($G2=2,9, IF(G$2=1,8))))))))))))

2.2 Create a weekly reporting sheet

In the weekly example, you will learn how to create a weekly trailing timeline for the last 13 weeks, covering a 3 months time span. 

As you can see in image 14, we will create a timeline with three rows, the first called Week period, the second called Week number and the third called First day of the week (Sunday). The cells in the Week period row are simply written values as agenda for the time period.

Img. 14: Weekly trailing timeline

For the Week number row, the current week number in cell O2 – with weeks starting on Sundays as defined by 1 as type – gets pulled in with this formula: =WEEKNUM(TODAY(),1). The reason why we select a week format with Sunday as starting day is because that this is the how the ga:week dimension in Google Analytics reporting works. The formulas for the previous weeks for cells N2 to B2 follow a certain logic. For instance, for cell M2, 2 weeks before current week, the formula =IF($O2-2>0,$O2-2,IF(N2=1,MAX(W_Traffic!$B16:$B1000),N2-1)) checks whether the current week number subtracted by 2 (as this cell is the week number for 2 weeks before current week) will be positive (which would only be the case when the current week is not 1 [first week of a new year]) and if so applies this number. Otherwise, if the next week equals 1, then use the maximum reported on week number (52 or 53 depending on the year and number of Sundays) for that respective cell, otherwise just subtract 1 from the week number of next week. The details about changing elements of this formula for the respective cells can be seen below in the accordion view.  It is important to note that the referenced column to find the maximum week number should contain data for every week, which is the case for traffic data, but not necessarily Google Analytics event data (image 15).

Img. 15: Weekly traffic data

For the First day of the week (Sunday) row, the date of the Sunday of the most current week, can be pulled by  =TODAY() - (WEEKDAY(Today()) - 1). The respective format of the displayed date depends on the Google Sheets settings. For the dates of the Sundays of previous weeks, you can simply append -(7*1) at the end of the formula for cell N3, -(7*2) for cell M3, -(7*3) for cell L3

For your convenience, you can find examples of the cell formulas for previous week periods below.

Cell N2 (Week number): =IF($O2-1>0,$O2-1,IF(O2=1,MAX(W_Traffic!$B16:$B1000),O2-1))

Cell N3 (First day of week): =TODAY() - (WEEKDAY(Today()) - 1)-(7*1)

Cell M2 (Week number): =IF($O2-2>0,$O2-2,IF(N2=1,MAX(W_Traffic!$B16:$B1000),N2-1))

Cell M3 (First day of week): =TODAY() - (WEEKDAY(Today()) - 1)-(7*2)

Cell L2 (Week number): =IF($O2-3>0,$O2-3,IF(M2=1,MAX(W_Traffic!$B16:$B1000),M2-1))

Cell L3 (First day of week): =TODAY() - (WEEKDAY(Today()) - 1)-(7*3)

Cell C2 (Week number): =IF($O2-12>0,$O2-12,IF(D2=1,MAX(W_Traffic!$B16:$B1000),D2-1))

Cell C3 (First day of week): =TODAY() - (WEEKDAY(Today()) - 1)-(7*12)

Cell B2 (Week number): =IF($O2-13>0,$O2-13,IF(C2=1,MAX(W_Traffic!$B16:$B1000),C2-1))

Cell B3 (First day of week): =TODAY() - (WEEKDAY(Today()) - 1)-(7*13)

Do you like what you read so far? Do you like to get an email, whenever a new post gets published?

3. Reference Google Analytics API reports in Google Sheets

In this final chapter 3, you will see how to cross-reference the pulled data in the automated Google Analytics reports from chapter 1 into a weekly and monthly reporting sheet, for which you created already trailing timelines in chapter 2.

3.1 Build out the monthly reporting sheet

The monthly reporting sheet Monthly contains the reporting structure and references the numbers from the related monthly configured reports (“M_” sheets). As you can see in image 16, you will see how to build out a monthly report with based on the three exemplary pages (/product/overview, /product/feature-page1 & /product/feature-page2) containing the respective traffic volume, CTA click-throughs, and subsequent product signups. We create three units of four rows each for all three pages with rows for TrafficCTA Clicks and Product Signups.

Img. 16: Monthly reporting sheet

In this example, for cell B5, the unique pageviews for /product/overview in November, we use the following formula: =IFERROR(FILTER(M_Traffic!$C$16:$C$1000, M_Traffic!$A$16:$A$1000 = $A$4, M_Traffic!$B$16:$B$1000 = B$2),"") referencing the M_Traffic sheet. Unless an error occurs, in which case the cell will be left blank, the formula will consider that ga:uniquePageviews figure in column C of M_Traffic, for which the ga:pagePath equals /product/overview (cell A4 in Monthly sheet) and for which ga:month equals 11 (cell B2 in Monthly sheet). You can apply this formula to the entire month timeline by dragging it across the row.

Similarly, for cell B6, the unique events for CTA Clicks on product-overview-cta in November, we use the formula: =IFERROR(FILTER(M_Clicks!$C$16:$C$1000, M_Clicks!$A$16:$A$1000 = "product-overview-cta", M_Clicks!$B$16:$B$1000 = B$2),""). The filtering procedure works in the same way as described previously.

Finally, for cell B7, the number sessions that resulted in  product-signup following the two previous steps on /product/overview (pageview & CTA click), gets filtered in by: =IFERROR(FILTER(M_Signups_op!$C$16:$C$1000, M_Signups_op!$A$16:$A$1000 = "product-signup", M_Signups_op!$B$16:$B$1000 = B$2),"").

For your convenience, the other formulas based on image 16 are listed below.

=IFERROR(FILTER(M_Traffic!$C$16:$C$1000, M_Traffic!$A$16:$A$1000 = $A$8, M_Traffic!$B$16:$B$1000 = B$2),"")

=IFERROR(FILTER(M_Clicks!$C$16:$C$1000, M_Clicks!$A$16:$A$1000 = "product-feature-page1-cta", M_Clicks!$B$16:$B$1000 = B$2),"")

=IFERROR(FILTER(M_Signups_fp1!$C$16:$C$1000, M_Signups_fp1!$A$16:$A$1000 = "product-signup", M_Signups_fp1!$B$16:$B$1000 = B$2),"")

=IFERROR(FILTER(M_Traffic!$C$16:$C$1000, M_Traffic!$A$16:$A$1000 = $A$12, M_Traffic!$B$16:$B$1000 = B$2),"")

=IFERROR(FILTER(M_Clicks!$C$16:$C$1000, M_Clicks!$A$16:$A$1000 = "product-feature-page2-cta", M_Clicks!$B$16:$B$1000 = B$2),"")

=IFERROR(FILTER(M_Signups_fp2!$C$16:$C$1000, M_Signups_fp2!$A$16:$A$1000 = "product-signup", M_Signups_fp2!$B$16:$B$1000 = B$2),"")

3.2 Build out the weekly reporting sheet

Now, you will see how to reference the weekly configured reports (“W_” sheets) for the three exemplary pages (/product/overview, /product/feature-page1 & /product/feature-page2) in regard to traffic volume, CTA click-throughs and subsequent product signups in order to create a weekly reporting overview in the Weekly sheet (that will look like image 17).

Img. 17: Weekly reporting sheet

In this example, for cell B6, the unique pageviews for /product/overview for the time period 13 weeks ago, we use the following formula: =IFERROR(FILTER(W_Traffic!$C$16:$C$1000, W_Traffic!$A$16:$A$1000 = $A$5, W_Traffic!$B$16:$B$1000 = B$2),"") referencing the W_Traffic sheet. This is exactly the same approach as used for the Monthly reporting sheet. So, any M_Traffic gets replaced by W_Traffic and also the first filter condition for column references $A$6 instead of $A$5, because of the three instead of two rows of trailing timeline in the Weekly sheet. With these adjustments to the listed formulas in the previous section, you will be able to create all the required formulas to complete the reporting in the Weekly sheet.

Depending on your reporting needs, you might want to expand on the shown approach. In some cases, you might need to reference the page example.com/product/overview as well as any pageview for that page with appended parameters such as example.com/product/overview?utm_campaign=abc&utm_content=xyz. You can accomplish that by using the SUM function and the REGEXMATCH filter condition, which would turn the formula above into:

=IFERROR(SUM(FILTER(W_Traffic!$C$16:$C$1000, REGEXMATCH(W_Traffic!$A$16:$A$1000, "www.example.com\/product\/overview$|www.example.com\/product\/overview\?"), W_Traffic!$B$16:$B$1000 = B$2),"")

Google Sheets will add up any pageview numbers that match the regex filter and return the sum of pageviews, allowing you to report on the page as well as its parameter variants combined.

 

With the referencing completed, in order to fully automate Google Analytics reporting in Google Sheets, you can schedule the configured Google Analytics reports to run hourly, daily, weekly or monthly by clicking on Schedule reports as can be seen in image 1 in section 1.1.

4. Conclusion

This post describes how to automate Google Analytics reporting in Google Sheets with a three-step process. Chapter 1 deals with the report configuration for the automated Google Analytics reports that pull data into Google Sheets. Chapter 2 discusses how to set up trailing timelines in Google Sheets, so that the sheet continuously updates its timelines automatically. Chapter 3 shows how to reference the automated Google Analytics report data in order to structure and present it in a Monthly and Weekly reporting sheet.

As mentioned in the introduction, if you intend to do light reporting that will not require too many configured reports and your traffic volume does not exceed the sampling threshold, then investing some time into learning how to automate Google Analytics reporting in Google Sheets can be a really viable option and an alternative to reporting in the UI of Google Analytics.

Did you like what you read? Do you like to get an email, whenever a new post gets published?