Dynamic date range on Google Analytics data in BigQuery

You have your Google Analytics data flowing into BigQuery, now you want to build some reports, or at least run queries on top of it. One of the first thing’s you’ll want to do it query across a date range.

As GA tables are nicely date stamped it’s fairly straightforward to work out how to run a query for yesterday, or last week using comma separated tables. However, what if you want to go a step further and feed this data into a visualization or DataStudio report? You want the data to update every day, but you don’t want to have to edit your query every day. You want a rolling date range!

The LegacySQL approach

In Legacy SQL this was easy to achieve with the TABLE_DATE_RANGE function, as seen in the example below:

SELECT date, totals.visits FROM (TABLE_DATE_RANGE([xxx:xxx.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))

This query injects a timestamp into the table name, in the same format as the GA tables, so is able to dynamically deliver the last 7 days of data.

If you wanted to include the current day also, you can adjust the Legacy SQL query to collect data from the intraday table too. See the below example taken from the BigQuery Cookbook:

SELECT date, SUM(totals.visits) visits, FROM (TABLE_DATE_RANGE([xxx.ga_sessions_], DATE_ADD(CURRENT_TIMESTAMP(), -6, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))), (TABLE_DATE_RANGE([73156703.ga_sessions_intraday_], DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP())) GROUP BY date


That’s all fine, but what if you’re trying to leave Legacy SQL behind, and have a need to replicate this in Standard SQL? Unfortunately TABLE_DATE_RANGE is unique to Legacy SQL and simply does not exist in Standard SQL.

The best replacement I have found to date is _TABLE_SUFFIX, it can be implemented in a similar way, to feed back data from a dynamic date range:

SELECT Date, totals.transactions FROM `xxx.xxx.ga_sessions_20*` AS t WHERE parse_date('%y%m%d', _table_suffix) between DATE_sub(current_date(), interval 7 day) and DATE_sub(current_date(), interval 1 day)

Note that the addition of the 20 before the wildcard is required to make this work correctly.

That’s it, rolling date ranges using Google Analytics data in BigQuery!

Note: project names have been removed from the above queries, you’ll need to replace xxx with your own details to make this sample code work on your data.

Are Britons falling out of love with booze? #MakeoverMonday

My shot at this weeks #MakeoverMonday, Are Britons falling out of love with booze?

Embedded version

Custom backgrounds for Google DataStudio reports

Here’s a quick win for Google Data Studio – although the recent updates providing theme colour settings are a great addition, you can’t currently set a custom background image for your reports.

I find that custom backgrounds can be a nice way to add subtle context to the content – and often make the data more approachable, so I wanted to make some Google Data Studio reports with custom backgrounds!

The way to achieve this is to simply add an image that floats beneath all your charts. If you want to add a custom background image it’s worth doing this early on in your report building – adding charts on top of an image is much easier than the other way around. It’s also helpful to have the background image in place so you can work your charts around any image elements you want to visually avoid or emphasise.

Report background images top tips

  • Choose a muted image that will fade into the background and not dominate your content – think about images used i desktop wallpapers, you don’t want the image to steal the show the data!
  • Your image doesn’t have to cover the whole report, it could fill just a half, or just a corner and be just as effective.
  • Feathering the edges of an image can help it blend into the background and sit better next to data tables and charts.

That’s all, a nice simple way to add some flair to your reports!Google

‘Bullet charts’ with a dynamic goal in Google Data Studio

Google Data Studio, recently released into worldwide Beta, has a good range of charts and data layout options built in by default.

One of my favourites is the bullet chart. However, one frustration I have found is that you can’t currently set a dynamic value for your goal, and editing the report to update this number isn’t always possible.

In the example below I have shared my process for recreating a bullet/goal chart by overlaying two bar charts, and linking one to a dynamic goal with a new value every day.

How to recreate this chart in Data Studio

My requirement was for a chart which would show a team their daily progress towards a goal – something a bullet chart would be perfect for, but in this case the targets we’re different for every day of the year. I didn’t want to have to edit the report each day to update the goal value, so I created a workout with a chart above.

Looks like something that would be useful for your reports? Great, let’s look at how it works!

This chart is actually two bar charts, one on top of the other, where the one below tracks the goal and the one on the top tracks the actual performance at the time of viewing. In the example above this is for target orders in a day against current orders (connected to Google Analytics and updating throughout the day).

Here’s how they look pulled apart – see how simple it really is:

Step 1: Create 2 bar charts, one for your goal and one for your target metric.

The bar for your goal can be a fixed amount, if your goal is the same every day, but in this example it’s connected to a Google Sheet which contains 2 columns – date and target orders. The value of the grey bar will read the new target from the Google Sheet every day and update the goal accordingly.

Step 2: Fix your axis to the same values

Fixed axis are important, as this will keep your progress in content to your goal – for example if you have an axis of 100 on your progress but 200 on your goal, it’s going to look as through you are twice as close to your goal than you actually are!

Step 2: Style your value axis so that they are visually hidden.

The marks on your axis are going to be different – you can’t currently hide them in Data Studio so I have made mine white, which on a white background makes them invisible.

Step 3: Drag your target bar chart on top of your goal chart and position to your preference.

That’s it, simple!

Things to keep in mind

  • Because your using a fixed axis, if your target figure is pulling from an external source (as in the example above) and this exceeds the maximum axis value, your data will fall off the chart. So set your axis at a comfortable level that will allow your target to grow without you having to edit your report.
  • What happens when you reach your goal? Your goal bar will continue to grow over the top of your target – you can tweak the visuals to make this more obvious, perhaps something like this?

Feel free to share any further ideas or comments below, happy reporting!