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

5 – CTRL+SPACE
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!

‘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!

Ben