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.

How to calculate relative dates for Google Analytics queries
Dates. Picture by Ella Olsson, Unsplash.
2 minutes to read

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

Matt Clarke Matt is an Ecommerce and Marketing Director who uses data science to help in his work. Matt has a Master's degree in Internet Retailing (plus two other Master's degrees in different fields) and specialises in the technical side of ecommerce and marketing.