Just about every company that uses Google Analytics has an involved process to keep reports up-to-date. This might include manually exporting data from the user interface, setting up scheduled data deliveries, or licensing custom applications.
In this tutorial, we'll show you how to enable automatic updates for Google Analytics reports in Excel, allowing you to build a trailing 30 day Bounce Rate report that stays up-to-date without manual exports or custom applications. You will go through the following two steps:
- Build a "Trailing 30-day Bounce Rate" Google Analytics report in Excel
- Create your own trailing X days/weeks/months reports that automatically stay up-to-date
Once finished, you'll know how to start building your own Google Analytics reports that pull the most recent data directly into Excel.
Step 1: Build a "Trailing 30-day Bounce Rate" Google Analytics report in Excel
Google Analytics reports are often used to measure performance over a over a relative time period. Whether it's last year, last month, or last week, the dates will change and you'll need to make sure your report stays up-to-date.
The typical ways to keep your report updated are using an application to schedule updates, or manually exporting data from Google Analytics. But here we're going to create an self-updating Google Analytics report by simply using two spreadsheet functions: =TODAY() and =BLOCKSPRING().
Recall that the Blockspring Google Analytics report is built around a single spreadsheet function: "=BLOCKSPRING()". This function takes a number of inputs either from text or cell references in the formula bar, including Start Date and End Date. In the last example, we just entered explicit dates like "10/02/2015", but we could just as easily use the built-in date and time functions of the spreadsheet to achieve the result. To illustrate this concept, open up the Bounce Rate report example and try changing the End Date of the Analytics Report to the formula “=TODAY()”.
Now every time you load up the report, Blockspring will retrieve Google Analytics data from the Start Date to whatever today’s date happens to be. By changing the Start Date in conjunction, you can report on relative time periods. To complete the example report covering Bounce Rates over the last 30 days, all you need to do is to set the Start Date cell to the formula “=TODAY() - 30” and the End Date cell to "=TODAY()". That'll refresh the report with the correct 30 days of data each time you open the spreadsheet.
Step 2: Create your own trailing X days/weeks/months reports that automatically stay up-to-date
Let's keep the train moving: right now, every time you load the spreadsheet, you'll be greeted with the last 30 days of data. But what if you want to use a different time period - like last month? This presents more of a challenge since each month could have between 28 and 31 days. Luckily, we can use more spreadsheet formulae to get just about any time range you want. A word of warning: Dates can be difficult to work with, especially for financial / accounting applications, so be sure to check all formula before using them to ensure they produce inclusive dates, and that they are consistent with the date convention you should be using!Here are some common time periods that you might want to report over:
|Time Period||Start Date||End Date|
|Yesterday||=TODAY() - 1||=TODAY()|
|Trailing week (7 days)||=TODAY() - 7||=TODAY()|
|Trailing month (30 days)||=TODAY() - 30||=TODAY()|
|Trailing 12 weeks||=TODAY() - 84||=TODAY()|
|Trailing year (365 days)||=TODAY() - 365||=TODAY()|
|Current (Sunday) week to date||=TODAY()-WEEKDAY(TODAY()-7)||=TODAY()|
|Current month to date||=DATE(YEAR(TODAY()), MONTH(Today()), 1)||=TODAY()|
|Current year to date||=DATE(YEAR(TODAY()), 1, 1)||=TODAY()|
You've learned how to build Google Analytics reports that automatically update with the most recent data when they're opened, which can help eliminate manual reporting on Google Analytics data. Here are some ideas on what to do now:
- Try building a report that compares multiple time periods against each other - for example, week over week sessions by campaign.
- Use the large number of Excel data visualizations to create charts and graphs that are linked to your dynamic data. They'll update automatically, too!
Ready to jump to our next lesson? We'll learn how to aggregate, sort and pivot your Google Analytics data to create user-facing views that are ready to publish.