Reporting trends in your online marketing data is often more art randomness than science. It’s relatively straightforward to eyeball time-series data and interpret the direction of the data – is it going up or is it going down? Yet, with so many sources, segments and campaigns, it’s difficult to surface trends, understand the reliability of trends, and report them to your clients, team or boss.
In this blog post I’ll show you how you can use Google Spreadsheets to easily and automatically monitor different trends in your Google Analytics top segments and metrics.
This is an adapted version of Jamie Steven’s post on a building Google Analytics reports in Google Spreadsheets. It uses scripts by Mikeakl Thuneberg to pull data from the Google Analytics API.
Charting trends with regression analysis
Trendlines are the best way to graphically represent trends in data. This is also known as regression analysis, which is a statistical technique for studying linear relationships. By plotting a trendline and calculating the R-Squared value, we can understand whether a trend exists beyond a rudimentary “eyeball” approach. It also gives us a great visualization and statistical backing that most stakeholders easily understand.
Getting to know R²
In these reports, we’re using R-squared values to understand the reliability of a trend. The short explanation is that an R-Squared value is something of a magic number. R-Squared values are between 0-1 and a trend is most reliable when its R-squared value is at or near 1. It represents the strength of the relationship and is mainly used to analyze how well a variable can predict another one. We’re simply using it as a determinant of how much “weight” we can place on a trend and thus how much further analysis or attention it deserves. The higher the R-Squared value, the stronger the trend and the more it becomes critical to report and explain.
So let’s get started by setting up and customizing your report.
1. Save a copy of the Trends Analysis Report
Open the Google Analytics Trends Analysis Report and save a copy to your Google Account. Click “File”, then “Make a copy”.
Get the free report: Google Analytics Trend Report
Once you’ve saved your own version of the report, we’ll be working on the plumbing to connect your data.
2. Connect your Google Analytics account
In order to automatically connect your Google Analytics data, you’ll need to ask Google for a token that allows Google Spreadsheet to access your data. We’ll use AutomateAnalytics Functions to request a token.
Get your token here.
Copy and paste your token into cell B14 on the Instructions & Data tab in the report.
3. Locating your Google Analytics View ID –
Next, you’ll need to enter your Google Analytics View ID. View IDs can be found in the Admin section of Google Analytics, under View Settings.
Enter your Google Analytics View ID into cell B13 on the Instructions & Data tab in the report.
If entered correctly, you’ll start to see the spreadsheet pulling in data from your Google Analytics account and populating the Reporting tabs. A few things to note about the reports:
- We’re analyzing trends over the last 24 weeks, beginning with the previous week. For the monthly report, it analyzes data over the last 12 months, beginning with the previous month.
- An R-Squared value of .6 is used to generate the narrative about a trend. This can be adjusted in the narrative formulas.
- Ideally your Google Analytic account will have at least 1 year of data. The formula array is not dynamic, meaning it will calculate the 0’s in your data if you don’t have data that goes back a year. You’ll need to adjust the formula arrays on the Instructions & Data tab.
Customizing the metrics
We’re analyzing visits against different segments. To change the metric, simply update the metric cell for that particular trend on the Instructions & Data tab. For instance, if you want to measure pageviews, simply enter “pageviews” in the cell that says “visits”. All trends operate independently so each one can be adjusted to the metric of choice.
A full list of Google Analytics API metrics and dimensions can be found here.
Customizing the segments
Much like metrics, segments can also be edited. Segments specify a subset of visits based on an expression or filter. In the example below, the ID “-3” specifies referring visits as the subset.
The easiest way to see a list of various IDs is to use the Google Analytics Query Explorer tool. Once you’re signed in to the tool with your Google Account, you can use the Segment drop-down to view the different segments and corresponding IDs. Pick the ID you want and enter it into the Advanced Segment cell.
Here are a few quick IDs you can use to customize your reports:
- New Visitors: -2
- Returning Visitors: -3
- Paid Search Traffic: -4
- Non-paid Search Traffic: -5
- Search Traffic: -6
- Direct Traffic: -7
- Referral Traffic: -8
- Visits with Conversions: -9
- Visits with Transactions: -10
- Mobile Traffic: -11
- Non-bounce Visits: -12
- Tablet Traffic: -13
Changing the dates
Lastly, you can edit the dates in the report with a few quick tweaks. Keep in mind that if you change the dates you’ll also need to adjust some formulas as well as the chart arrays. Currently, the “End Date” is set to today and the “Start Date” is a formula that calculates either 24 weeks or 12 months back depending on whether it’s a Weekly or Monthly trend report. You can change both the dates to analyze the number of weeks or months you want.
The formulas, RSQ and Trend Line arrays will need to be adjusted if you add or subtract rows by changing the dates. Be sure to edit the charts in the reports as well if you add or subtract rows.
Feel free to comment with any questions. We’d love to see any useful iterations to the reports or great use cases!