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.

5 Beginner Tips for Google BigQuery

Or, 5 things I wish I could have read before I started working with Google BigQuery.

First a disclaimer, I am a complete BigQuery newbie, I’m not a programmer or somebody with a background in database code. I have been working with Google BigQuery to analyse large data sets for a couple of months now, learning as I go.

I’ve found limited resources online for complete beginners like myself, and those that do exist are quite technical – designed for people with an established programming background.

With this in mind, I thought I’d share some of the things I’ve discovered so far and hopefully help anyone else starting out with Google BigQuery. To those with the above mentioned technical knowledge, these may seem completely obvious, but they are things I wish someone had told me when I first started out! Here we go…

1 – You can save queries.
Once you’ve got a handy query nailed down, you can save it with the ‘Save Query’ button below the query window. Saving it as a ‘Project Query‘ means that anyone on your cloud account can see it/use it too – great for teams working together.

Saved a query but can’t find it? Click onto “Query History” and look under the ‘Queries‘ header for the following buttons:

2 – You can save a query as a view.
Are you, like me, running the same query over and over again and having to export the results as a table? Click onto ‘Save view‘ beneath the query window and you’ll save this query as an automatically refreshing table.
View’s can be differentiated from tables by this icon: 

3 – There is a log of your previous queries.
It might be pretty obvious that ‘Query History‘ is your log of previous queries, a handy resource if you want to pick up what you we’re working on yesterday, but it’s also important to note that if your part of a team, this log does not show you the queries of other users, only yours.

4 – You can click on the schema.
Clicking on field names in the schema will automatically insert them into your query – it even adds a comma after each one. This makes adding multiple fields to your query a quick and simple process

Hitting CTRL+SPACE is a neat little suggest / auto-complete feature that shows you options for completing field names or available functions – great when you are first experimenting with what you can extract from your data – try it out!

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!