How to calculate relative dates for Google Analytics queries
To automate Google Analytics API reports for Google Data Studio you’ll need to know how to calculate relative dates in Google Sheets. Here’s how it’s done.
The Google Analytics add-on for Google Sheets allows you to use the Google Analytics reporting API to create custom weekly reports and schedule them to run. However, to run a scheduled weekly report, you’re going to need to provide specific dates to the API, for which you’ll need to calculate dates relative to the current date. Here’s how it’s done.
1. Choose your week type
By default, Google Analytics uses a US-style week format in which weeks start on Sunday and end on Saturday, instead of the “correct” ISO week format we use in the UK in which weeks start on a Monday and end on a Sunday. You can acces the ISO week number via the API using ga:isoweek instead of ga:week.
2. Create some configuration cells
When you create a new Google Analytics API report in Google Sheets it will create a new worksheet called “Report Configuration” which holds each of the API requests you’re running. The “Start Date” and “End Date” fields in here can take a value like “30daysAgo”, “yesterday”, or a date i.e 10/5/2020. We’ll be replacing these dates with some automatically calculated relative dates that you can either store in the Report Configuration worksheet or a separate one.
For each of the relative dates you wish to calculate, add one of the below formulae, given it a label, and then map it to the Start Date or End Date cell in the report. These will allow you to calculate the Monday and Sunday of the last full ISO week, the Monday and Sunday of the previous ISO week, and the Monday and Sunday of the same ISO week last year.
| Relative date | Google Sheets formula |
| Monday of last ISO week | =TODAY()-WEEKDAY(TODAY(),1)-5 |
| Sunday of last ISO week | =TODAY()-WEEKDAY(TODAY(),1)+1 |
| Monday of previous ISO week | =TODAY()-WEEKDAY(TODAY(),1)-12 |
| Sunday of previous ISO week | =TODAY()-WEEKDAY(TODAY(),1)-6 |
| Monday of last ISO week last year | =(TODAY()-WEEKDAY(TODAY(),1)-5)-366 |
| Sunday of last ISO week last year | =(TODAY()-WEEKDAY(TODAY(),1)+1)-364 |
3. Schedule your report
Test your report and you should find that Google Analytics can now calculate your weekly data for the last full week, provide a comparison for the previous week and compare the last week to the same ISO week last year. Go to Add-Ons > Google Analytics > Schedule reports and set your report to run each Monday morning. It will then populate your Google Sheet automatically with data from Google Analytics so you can view or email the spreadsheet or access it in Google Data Studio.
Matt Clarke, Thursday, March 04, 2021
Other posts you might like
How to use the Pandas truncate() function
Have you ever needed to chop the top or bottom off a Pandas dataframe, or extract a specific section from the middle? If so, there’s a Pandas function called truncate()...
How to use Spacy for noun phrase extraction
Noun phrase extraction is a Natural Language Processing technique that can be used to identify and extract noun phrases from text. Noun phrases are phrases that function grammatically as nouns...
