Content

Ultimate Guide to Google Data Studio (2021)

Sep 8th, 2021

Following its launch as a beta in 2016, Data Studio quickly became an important part of Click Consult’s reporting, allowing us to automate repetitive tasks and cut down on aspects of monthly reporting and free up additional time for our clients. In this guide, we’ll try to distil our learnings from the last five years to help you do the same.

Whether you are working at an agency or on the client side, in all likelihood, you are spending at least one day a month poring over your Google Analytics (GA) account to pull together reports for various parts of your site. Even people who know there is a better way will often postpone report automation over and over again because of the initial investment of hours needed to set things up.

Most of the time we will excuse the work to ourselves and others by pointing out that the monthly report is pulled together much quicker these days as we have been doing it for so long and we are used to the process. Other times, we will point out that spending the time automating reporting will set us back in other tasks. For others, there is the issue of not knowing where to start or fearing possible costs (sometimes even the worry that it may not work).

One of the best things about Data Studio is that, with effort and patience, anyone with a good knowledge of Google Analytics can (and arguably should) automate their monthly reporting – and Google are adding more functionality all the time.

What is Data Studio?

Part of the “Google Analytics 360” package released in March of 2016, Data Studio is a data visualisation tool that allows you to create and maintain a host of report types in easier to comprehend visual formats which can be shared via a link or downloaded as a PDF.

Why use Data Studio?

While there are competing products for data visualisation available, the fairly extensive uptake of the platform is at least in part due to its relatively low barriers to entry in terms of prior experience of business intelligence and data visualisation software and due to its inclusion in the free suite of Google tools. For brands looking for a way to turn data into insights – especially for SMEs and brands that aren’t looking to spend $1000+ p/a for the privilege – Data Studio is a great way to deliver.

Setting up a basic data studio report

First things first, you’ll need to ensure you have your access to Google Ads and Google Analytics, but assuming you have these set up and linked to the account you will be using for Data Studio, the next step is to sign in to Data Studio. Once there, you’ll have the option to create a new report – which you can select:

Titling your report

You’ll then be taken to an ominously blank page – name it and make it your own. Bear in mind the audience for your report and what you intend for the report to contain. While you can rename your title, it’s important to make sure it is clearly named – not only for ease of rediscovery, but for ease of comprehension.

Connecting to analytics

This will then bring a list of the currently available (built by Google) data sources – you can then select Ads or Analytics. These data sources can also be augmented by third party tools – like Supermetrics – if you want to add in things like social media followers without taking the long road.

You will then need to authorise the connection, then select the account, property and view.

This will auto load the dimensions/metrics etc that are currently part of that view in analytics or Google Ads. Select ‘Add’ to make these available to you when setting up the report.

Once you have allowed Data Studio access, it will return you to your blank report – this will now at least have some gridlines to ease your mind, it will also have a new menu bar on the right with ‘layout’ and ‘theme’ as tabs.

Using these two tabs, you can choose the colour palettes and other various presentation attributes – useful if you’re looking to use brand colours or, unlike me, have any kind of design aptitude.

Once you’ve set your theme and layout, you can then begin creating your report using the various chart types in the top toolbar.

You begin by selecting your chart of choice then drawing the area it will occupy – the chart types (from left to right) are:

Time series – a line chart plotting a metric over time.
Bar chart – showing comparisons of a metric vs. a stated dimension.
Combo chart – literally a combination of the previous two types.
Pie chart – showing the share of a metric apportioned to a chosen dimension.
Table – a versatile format allowing multiple metrics to be shown according to their relationship with a dimension.
Geo map – allowing you to display the spread of a metric across a map (you can refine the level from global down to various subcontinental areas).
Scorecard – a simple return of a count or total for a metric.
Scatter chart – allows you to plot a source on a chart with an x and y axis that are both metrics.
Bullet chart – used for showing progress toward a target (goal completions for example).
Pivot table – allows you to show a metric distributed between two or more dimensions (with a minimum of one per column and row).

Populating your chart

To begin with, I’m going to set up a table – with a view to then refining this in the next two sections
using either a segment or a filter.

Whenever I draw a table (this may not be standard, but always happens for me), it auto-populates with the Data Source I nominated initially (the view), a random dimension, and a random metric and looks like one of these:

For a top-level report table, however, using the menu on the left, I’m going to select some of the most important metrics and use the dimension ‘month of the year’ to create a month-by-month breakdown which cab be sorted using the dimension in descending order to place the current month top of the list.

Using filters

With this table giving us a starting platform, we can then look to narrow our focus and initially we’ll do this using a filter – which you can do by selecting ‘add a filter’ in the right-hand menu (under the ‘data’ tab). So, having copied the table and pasted it in to a new report page, you can select the ‘add a filter’ option. This will bring up the filter creation menu:

You can use the basic include/exclude options with any metric or dimension, refining it with ‘contains’, ‘equal to’, ‘starts with’ and other options. The full row is referred to as a ‘clause’ – which can be augmented with additional clauses to refine the filter further.

If, for example, you wanted to look at a specific subfolder on your site, you would use the following options (where ‘/blog/’ is equal to the specific name of the subfolder you want to look at):

This gives us the same report as previous, only this time it will only include sessions that include a visit to that particular subfolder:

Using segments

We can then refine this report further using ‘segments’, so if we copy the table across to a third page, then select ‘add segment’, we’ll be presented with two options in a ‘segment picker’: ‘System segments’ and ‘Custom segments’. We’ll be using the former here – the latter is defined in Analytics, so you will be aware already if you have any and what purpose they could be used for.

The list is fairly self-explanatory if you’ve spent any time in Google Analytics – allowing you to restrict the data in your table by one of a number of factors – ranging from user specific, like ‘New User’ and ‘Multi-session User’, to general source, like ‘Organic Traffic’ and ‘Paid Traffic’.

By using the ‘Organic Traffic’ segment, we now have a table which shows the organic traffic, to the specific subfolder, as part of the overall site – giving us different levels of depth to a fairly basic chart type.

This process – or, at least, a similar one – can be used across the full spectrum of available charts, allowing you to produce geo maps for conversions, device specific traffic reports and a whole range of others that can help you to better communicate your success with stakeholders.

Connectors

For the most part, connectors are accessed in the same way, in this example we’ll be using Google Search Console as the example – though the same process works for Google Analytics, Supermetrics and a host of other tools that have built themselves a connector. To access your available connectors, select the resource tab in the menu bar, then ‘Manage added data sources’ from your resource tab (in edit view).

Then scroll to the bottom of your current data sources and select ‘add a data source’.

Then select GSC from the list of sources (one scroll down – for me at least).

You’ll then need to choose the site you want to connect to – for which there’s a search function if you are managing multiple sites.

Then you’ll need to choose between the two options – site impression or URL impression. For this report, we’ll be using site impression as it holds average position data while URL impression doesn’t (though it has landing page data instead), after which you can click the connect button.

You can then add the data source to the report (changing any of the dimensions and metrics to the measurement you prefer).

You can then confirm the process and add it to the source.

Google Sheets

Google Sheets is connected in the exact same way as other tools and platforms which have developed a Data Studio connector, the main difference with Google Sheets is, obviously, that you’re responsible for the data that will serve as the feed and, therefore, how it is presented to Data Studio to ensure that it is readable.

For the most part, the best way to do this is tabulated – with the dimension in the left-most column and metrics from there. As a simple example, due to the number of eBooks that Click has produced over the years, our goals span a large number of views, which makes it difficult to pull data accurately from the Google Analytics connector. As such, there’s a separate sheet which pulls the data from each view and each goal using the Google Analytics ‘Add-on’ in Google Sheets.

As this isn’t in an especially Data Studio friendly format, and because the report needs to provide an overall view of around 15 views, this is then pulled into a separate table with a really simple formula and assigned the goal name as a dimension. While it does represent an intermediary step, this does allow us to have a regularly, automatically updating overview of a range of data that would otherwise be impossible to achieve with Data Studio as things stand.

As you can see from the above, the dimension is on the left and the metric is on the right (with a third, sorting column if we need to filter by specific views in Data Studio).

These tidy-up tables are usually necessary if you’re using automated tools – like the Google Analytics or Ads add-ons, Supermetrics or any kind of XML import. They just make things Data Studio readable and make the data you’re importing more visualisable. For example, while the above is only represented by a similar table in our internal reporting, it would just be a matter of a couple of hours to allow us to track goal popularity over time by using a few different automatic reports and a date dimension as a primary column followed by the title. Equally it could be represented by any number of other charts – the main barrier is time and imagination.

You can see a little about using APIs and sorting XML data into one of these tidy-up tables in a blog we wrote for the State of Digital site a while back.

SEO tools

There are several different ways to connect Data Studio to tools for SEO, these include:

Some SEO tools, like Semrush, AWR and others, have connectors that you can use to connect directly
Others (including various platform specific analytics, like social media platforms), an intermediate connector such as Supermetrics can act as a feed for your data
There’s also the option mentioned previously of using an XML import via an API to a Google Sheet which you can then feed into the report
Lastly, there are unique connector set-ups which require adding a custom connector which is not featured in the list

Connecting Sistrix

There’s a walkthrough on the Sistrix website, but we’ll offer a condensed version here – most custom connectors are added in a similar way, so once you’ve been through it once, you’ll find yourself well prepared for the next one.

Step one

Step two

Click the ‘Authorise’ button:

Step three

Step four

You’ll then need to fill in the connector details,

To get your API key, you’ll just need to log in to Sistrix, select a user, then under the account menu you’ll see an option named ‘Toolbox API’.

You then need to click the ‘Create’ button:

Then, if required, name and set an expiry date for the API token.

Once this is created you can copy and paste the token into the form, then select your most used domain, location and data type (along with ticking the ‘allow modification’ boxes and click ‘Connect’ followed by ‘Allow’ for parameter sharing.

This will provide access to the following reports/dimensions etc.

Scheduling reporting

This is nice and simple once you know where it is, but it’s worth noting. As you progress and begin to automate, the chances are you’ll need snapshots at regular intervals to report back to stakeholders. You can do this by scheduling a pdf to be emailed at specific points in the month. You can do this simply enough by clicking on the drop-down arrow next to the share button:

You then just need to edit the schedule:

Unfortunately, there’s not a tremendous amount of choice for the dates you can choose, so you may have to select daily sends if you need a specific date of each month.

Extras

In addition to the adding and removal of data and various functional aspects, there’s also the fiddly aspects of setting up a report which offers the chance to learn from as well as record data, we’ve also added a tip to help improve the overall appearance and usability

Blending data

Blending data is one of the best ways to add a second layer of insight to a dataset – it can help you to uncover trends and correlations that you might not otherwise catch. With so many data sources available now, there are huge possibilities.

To blend data, you can either select two charts or tables, right click and select blend – this will produce a third, blended chart (but is actually a longer process as you have to create those tables in the first place) – or you can do the following:

Step one

Select ‘Resource’ from the menu bar, then ‘Manage blended data’, then scroll to the bottom and select ‘ADD A DATA VIEW’.

Step two

Next, you’ll need to choose the data sets you want to blend – one of the most useful and easy to implement is the blend of the site and URL impression sets from Google Search Console.

Here you’ll see that there’s the requirement for a join key. A join key is a shared dimension (which is why it can be trickier to do between tools due to a lack of standardisation) between the two datasets – more often than not, this will be a time dimension but, in this case, we have a few options as the two datasets are standardised.

Once you have a join key, you can then select dimensions and metrics – again, the dimension needs to be present across both datasets, so whether it’s URL or a date it will need to be replicated in both tools. Once you have the dimension, however, you can apply any appropriate metric from either data source to allow you a much better oversight of the data than you would have with just the one.

As you can see with the above, using a blend lets us see how a page is performing for various queries (you can also add filter boxes to allow you to explore this further – and we cover how to do that here)

Calculated fields

Calculated fields are useful for blended data, but also for when Data Studio just decides it doesn’t like the way your data is coming through (unfortunately often, unpredictable and unexplainable) and can come in handy when you’re looking to get percentage values of custom fields, for example, when aggregation can cause a headache.

How to create a calculated field

Step one

Select the ‘add metric’ option in the right menu, then select ‘create field’:

Step two

This will bring up a kind of form for your new metric – the standard advice applies, be descriptive when naming and tick all the boxes.

Your formula is much like any calculation you’ll perform in a spreadsheet – and is phrased in much the same way. You’re adding, subtracting, multiplying and dividing various existing metrics to form a new metric. This is especially useful if you need to run a calculation in blended data – as there’ll be no existing metric you can use – but also for occasions when aggregated data causes trouble (as it sometimes does) giving you data errors for seemingly no reason, and also for when you’re combining data using things like CASE statements which will cause issues with existing calculated metrics like CPC, bounce rate etc.

You can only carry out one calculation per custom field (hence the exclamation mark), but – as with a spreadsheet, these calculations can get quite complex depending on what you need them to do. Usefully, there’s a syntax checker which offers you guidance on whereabout the formula falls down (and a reason if it’s detectable).

Step three

Numeric: – a selection between ‘number’, percent’ and ‘duration’. Fairly self-explanatory, this is just the format of the metric’s output.
Comparison calculation: – these are mathematical operators (and, to be honest, a step above where I operate – I’ve yet to need any of them) which allow you to run a secondary calculation in addition to the formula you specify. In this case a comparison to the total result or maximum result in the data column.
Running calculation: – as above, this is a secondary calculation, this time as a kind of second column accounting style, offering a variety of running sums on the primary function.

Regex

Regex crops up in a few places, and is useful where it does – for filters, for example, it offers the option to filter multiple sources with a RegExp Contains and the vertical line (pipe) as a separator.

It’s also useful when creating custom dimensions and metrics – with regex meaning ‘regular expression’, you can use it wherever you need to look up a specific word or text string within a range. Your options for regex are:

Regexp Contains: – which looks for a designated text string within a range and allows, excludes or amends anything that includes the string.
Regexp Match: – which looks for an exact match for the text string (you can add .* to the string if you want to include a little uncertainty – if you’re not fussed about matching upper and lower cases) and includes, excludes or amends only that which matches completely.

Creating a nav menu

Another nice and easy thing to do when you know how, but it pulls a report together and can help improve the navigation of the report for stakeholders. Essentially, when setting up a report with multiple pages and sections, adding in some hypertext ‘buttons’ will help make navigation in view mode a little more intuitive.

Just select a text box from the tool bar:

Draw your box, add the text, then add a link to the text:

As you can see, you can select specific pages in your report to link to. Once this is done, the link text will revert to a blue underlined roboto, but you can style that to produce a nav item that will speed up and improve navigation.

CASE statements

What is a CASE statement?

Simply, a CASE statement readies the program for a set of conditional selections. This selection it makes depends on the value of the expressions that follow it, you provide the program with a set of inputs you wish it to classify by following a set of IF, THEN, ELSE or WHEN conditions. It will ascertain if any of the stated conditions are true and return the appropriate value for that condition or will, if none are true, return the ELSE value.

Why use a CASE statement?

The nature of CASE statements makes them the simplest way to look for regular expressions and attribute a value. When we are looking at source data in Data Studio, we can often be left with manual work adding up sessions from multiple URLs from the same domain – the CASE statement I’m about to lay out allows us to look for a regular expression in those domains and attribute them all to one source, meaning we no longer have to look for the m. or app generated URL versions in addition to the main URL for the source.

How to use the CASE statement in Data Studio

For this, we’re going to assume you’re already using Data Studio to some extent (if you need help getting started, try our Data Studio basics eBook) and are reporting in some way on referral traffic – whether from social media, partner sites or anything else. If so, you’ll have something that looks a bit like this (or one of many variations):

If you don’t have something like this – you can set it up easily enough using the Google Analytics connector and pairing the ‘Source’ dimension with the metric of your choice (sessions, goal conversions, whichever suits).

However, there are often several instances of various referrers (especially if any have m. sites or country specific TLDs). This is one situation in which CASE statements can be useful, and we can set this up by creating a custom dimension.

Where to find custom dimensions

Click on the ‘Source’ dimension. At the bottom of the pop up you’ll see ‘CREATE FIELD’.

This will bring up the field creation box – which you should name in an easy to remember way in case you need to use the same dimension when measuring another metric. Here, you can see – it’s imaginatively called ‘SOURCE (w/CASE)’.

Creating your custom dimension

For this we’re going to use WHEN, REGEXP_MATCH, THEN and ELSE:

WHEN: – states that you’re about to set a condition that the following must meet to return a TRUE response and deliver the result.
REGEXP_MATCH: – meaning ‘regular expression matches’ indicates that the condition is that a text string must in your data set must contain the text string you’ll provide to deliver the result.
THEN: – is the outcome that will result if the conditions are met.
ELSE: – is the outcome that will result if the conditions are not met.

Putting it all together:

CASE
WHEN REGEXP_MATCH(SOURCE, “.*nkedi.*”) THEN “LinkedIn”
ELSE SOURCE
END

While this is only one of the lines (I’ll give a full social media version in a moment), you can see that it reads almost like a (albeit oddly phrased) set of instructions.

In the following situations
When a text string in (the source data, matches a series of characters found in each LinkedIn URL) then we will return the value “LinkedIn”
If not, we’ll keep the name the same
That’s it
The result

While the change isn’t massive, adding the multiple instances of each social media platform together does alter the table (bear in mind I also filter this table using a REGEXP_CONTAINS to get rid of search engines):

Here we can see that the true picture is given of FACEBOOK’s referrals (combining facebook, m.facebook and facebook.com), Twitter has moved up as all its various iterations are taken into account and it gives us the information we need at a glance (while also telling me I forgot Yahoo – but who hasn’t amirite).

The full code for this is as follows:

CASE
WHEN REGEXP_MATCH(Source, “.*aceb.*”) THEN “FACEBOOK”
WHEN REGEXP_MATCH(Source, “.*nsta.*”) THEN “Instagram”
WHEN REGEXP_MATCH(Source, “.*ube.*”) THEN “YouTube”
WHEN REGEXP_MATCH(Source, “.*witt.*”) THEN “Twitter”
WHEN REGEXP_MATCH(Source, “.*t.co.*”) THEN “Twitter”
WHEN REGEXP_MATCH(Source, “.*kedi.*”) THEN “LinkedIn”
ELSE Source
END

In case you’re wondering – I tend to use the middle characters as they’re the least likely to appear as part of another site’s domain and some of the platforms don’t use the full name in some of their links – and Twitter goes a step further by using t.co (their own link shortener).

All in all, while there’s nothing exactly wrong with finding out how many sessions the mobile Facebook site has sent, there are other places we should be finding this out – if we’re looking solely at performance per domain, this custom dimension really cleans things up.

Data Studio not matching Google Analytics

We’re going to drop this in for the simple reason that it’s one of the most common queries – why doesn’t Data Studio match Google Analytics? There are a few things that can cause this (besides looking in the wrong place, which we’re going to assume isn’t the case) and most instances tend to be caused by one of the following:

Incorrect filtering: – if you’re filtering in Data Studio, you need to make sure you’re being as accurate as possible. Inexact filtering can cause mismatches in data (if you’ve used a regex exclude that matches more than you wanted, for example), so it’s worthwhile checking your filters.
Update time: – while both Google Analytics and the connector for Data Studio are close to real time, they aren’t exactly real time – and the connector uses the API which does have a slight lag, so it’s worthwhile checking over the course of an hour or two to ensure that the issue isn’t caused by this.
Sampling: – once a brand is dealing with daily numbers in the tens and hundreds of thousands of sessions, Google Analytics and the Data Studio connector will both employ sampling – and the size of the sample will determine the accuracy of the report. In situations where the largest of sites are reporting through Data Studio connectors, without using filters to departments or layers of a site, the sample size could be as low as or lower than 20% meaning that there will likely be significant disparities in the numbers. In order to tackle this, it’s best to limit the numbers – and that means reporting separately on specific areas of the website – or to move to using an external database like BigQuery to ensure that sampling is avoided.

Data Studio is a potentially powerful tool not just for reporting, but for gathering insight from the many data sources we, as digital marketers, have access to. While the processes we need to master get more complex and time consuming as we look to take the step up from reporting data to learning from it, that extra time can lead to invaluable insights we might otherwise miss, or at least take longer to learn.

There are certainly more specialised reporting platforms, and there are better tools for reporting specific types of data and sizes of data sets, but there are few platforms which are developing so much and so quickly and – importantly – are free to use. For that reason, we feel that it’s a platform that is well worth taking the time to learn and to keep pace with as it improves. The importance of collecting data has been made countless times in the digital marketing industry, but what we do with it and how is often overlooked – good reporting can be more than just representing the success or failure of a campaign, it can be an invaluable resource.

Data Studio Dashboard Templates

To get you started, there are a few basic report types set up as templates by Google, you can find them here:

Want help setting up custom reporting, or understanding what you should be reporting on? Contact us today to see what we can do for your brand. Or check out our resources for more actionable insights.