Google launched a new business intelligence tool called Data Studio in May 2016. It’s a really smart reporting tool for quickly creating powerful, stunning dashboards from multiple Google data sources.
It’s a great option for small/medium businesses already using Google tools, who want to build bespoke dashboards for that 40,000ft view of their business.
Google Data Studio Example Reports
Here are two example reports for a mid-size website (~500k pageviews a month).
Firstly, a mobile performance dashboard:
And second, a social media referral dashboard:
It’s only in beta version at the moment and still has a number of caveats, most noticeably:
- It’s only available in the US at time of writing (July 2016)* although is being release elsewhere this year.
- It only has connections to Google data sources at the moment, although more are going to be added, including external SQL databases. Update August 2016: MySQL and Google Cloud SQL connectors have been added!
- It doesn’t have deep, customizable, analytical capabilities when compared to the what’s available natively in Google Sheets, or in Excel, PowerBI or Tableau. I suspect that more features and customization options will be added pretty rapidly going forward, so it will be interesting to see where Google take this product.
* update October 2016: it’s now been released in over 180 countries around the world.
I wrote an introduction to the tool earlier this year, so that’s a good place to start if you want to find out more about it.
Note: this post deals with the free version of this software, called Data Studio. There is also an enterprise version called Data Studio 360 which allows for more reports, more data etc. The user interface is very similar.
If you’re interested in a really deep dive into this tool check out my new, online course all about building beautiful, informative dashboards with Google Sheets and Data Studio.
Build Business Dashboards With Google Sheets and Data Studio
– 9+ hours of video content, lifetime access and copies of all the finished dashboard templates.
6 advanced tips for working with Google Data Studio
In this post I’m going to dive a little deeper into the tool and look at 6 techniques to master for creating dashboard reports using this tool:
1. Working with pages
Pages allow you to display multiple dashboard reports in a single Data Studio report. They’re useful and simple to implement in Data Studio.
You might use pages to drill-down in your data, starting with an overall summary sheet and then going into more specific details in subsequent sheets.
There are two menus for workings with pages, one in the main toolbar:
and one in the page control widget in top-left corner of your dashboard:
The toolbar menu is more extensive but both allow you to navigate through your pages and add new ones. The main toolbar menu allows you to duplicate pages, a handy feature if you set the first page up with your report styles, which can then easily be replicated.
The “Current page settings” option allows you to specify the data source (see point 2 below) for the page and control the background color scheme:
2. Adding and filtering data at page level
You can specify data sources at a page-level, rather than individually for each chart (although that’s perfectly acceptable too). The advantage of this page-level approach is that you can apply filters which will then be implemented across all your charts in that page.
For exmaple, in this GIF, I add my web data source and then restrict it to only data for the “iPhone” by creating a filter on the Mobile Device Model dimension:
If you use page-level filters, but then find yourself wanting to add a chart of KPI scorecard on unfiltered data, then you can toggle the “Inherit Filters” option at the bottom of the current chart options in the sidebar. This will remove the page-level filter from that specific chart only. The following GIF shows this toggle:
This is exactly the same page-level filter I applied when creating the mobile performance dashboard:
3. Adding Date Range filters
The Date Range filter is found on the top right position of the main toolbar:
and the Date Range filter is the first of those two icons:
The Date Range filter is added by selecting that icon and dragging out a shape on your report where you want the date filter to go. By default the filter will be applied to all the charts, tables and scorecards on your page.
What if you don’t want that behavior? Well, you can restrict a date filter to only work with a single chart, or only specific, selected charts.
By grouping chart elements together with the Date Range filter, by highlighting them all at the same time, the filter will only be applied to those grouped elements. You group elements by selecting them all, then right clicking and selecting “Group”, or going to the Arrange menu and selecting “Group”, or by pressing Command + G,
The following GIF shows this process:
Before grouping the top elements, the date filter is applied to both charts. However, after grouping the top chart and the date filter, it is only then applied to that top chart. The bottom chart remains unchanged even if the filter changes.
4. Adding Filter Controls
The Filter Control option is the final icon of the main toolbar , the second of the two icons highlighted here:
A Filter Control is a control for the user to narrow down the data to be displayed in the charts in the report. In the example below, I’ll show you how to add a Filter Control for device category, so that a user can select from Desktop, Tablet and/or Mobile data in the report.
Filter Controls are added in the same way as a Date Filter, by selecting the icon and then dragging out the required shape in your report.
There are “Data” and “Style” menu options added to the sidebar, as follows:
I’ve added the red numbers to highlight some of the specific features, firstly in the Data menu:
1. The Data Source – you can edit this if required, but it should default to the same as your page setting, which is most likely what you want.
2. The Dimension of your Control Filter, i.e. the dimension that is presented to the user as a choice and will narrow down the data showing in your charts and reports. In this example, I’ve chosen “Device Category”, which will allow a user to select from Desktop, Tablet and/or Mobile data in the report.
3. The Metric to be displayed. IMHO, not something I really want to see in my filter, so I’ve unchecked this item.
4. The sort order and number of options to be displayed. In this case, the options in my filter control will be sorted from highest to lowest, by number of sessions. So desktop comes at the top because that had the highest number of sessions.
In the Styles menu:
5. The checkbox to make the Control Filter expandable or not. In general, you’ll probably want to set this to be expandable unless you only have a very small number of options in the the control.
The remaining options in the Style menu are formatting for things like borders and backgrounds, so feel free to explore.
Like the Date Range filter, the Control Filter is applied to all your chart elements in a page based off the same data source, so if you want to apply to only selected elements then you’ll need to group them (see above).
5. Report-level v Page-level elements
For reports with multiple pages, any element in your report can be set to be Report-level or Page-level. Report-level elements are duplicated across all pages of your report, in the same position on each page. So this could be used for headers and footers, or perhaps an important KPI scorecard that you want shown on each page.
To access this option, right-click the element you want to duplicate across pages and select the last option “Make report-level”.
In the following GIF, the bullet chart is duplicated across all 3 pages:
Note: Making a chart element report-level also removes it from being governed by any filters you have on that page.
6. Creating calculated fields
Finally, the most technical tip of the 6. It’s possible to define new data fields to use in your charts and reports.
What do I mean by this?
You can add new fields to your dataset, which you create by a custom calculation with existing data fields.
I’ll show you two examples, one simple example and one KPI that’s a little more useful.
First up, creating a calculated field to show the Medium dimension in uppercase, rather than the mix of lowercase and proper format of the default. This is done by applying a basic formula to the Medium dimension.
This is what the table looks like with the standard Medium dimension:
See how the different categories in the Medium column are showing some with capitals, some without. It’s messy, so let’s fix that.
Under data source in page settings, click the edit icon to the right of the data source:
This opens the data pane, showing all of the current fields in our dataset.
It’s here that we can add a new calculated field, which will then be available to use in our charts. Click the small blue plus above the current list of fields to add a new one:
Give the calculated field a meaningful name so you’ll be able to find it easily later on. In this example, I’m leaving the ID field alone. I enter a formula into the Formula input box:
The formula is:
Click “Create Field” on the right side and Google will add this new calculated field to the long list of fields in the dataset:
Note the little
fx next to the name of your calculated field, to show that, well, it’s a calculate field, based on a formula.
Now this field will be available for use in your charts. Create a new version of the original table, but replace Medium with Upper Medium (the new calculated field). The new table looks like this:
Looking much more consistent right? Whilst this might not be preferable, this example illustrates a basic calculated field.
Notice anything different? There are fewer entries, because previous differences, e.g. “newsletter” and “Newsletter” in our original, have now been aggregated together since they’re now identical, e.g. “NEWSLETTER”.
For another example of a calculated field, let’s create a useful KPI that we can display in our dashboard. I want to understand the quality of the traffic coming from each source, in terms of what percentage completes a designated “goal” (i.e. performs some action that we want the user to do, e.g. make a purchase, or sign-up for an email list).
So I want to create a new calculated field that shows goal completions divided by number of sessions and shows that as a percentage.
Following the steps above, with the following formula, gives:
After clicking Create, this new metric “CQI” is added to our field list in the dataset:
Note how I changed the “Type” to be “Percent”, rather than leaving as a decimal number. Now it’s ready to use in our reports:
Read more about calculated fields in the official Google docs.
7. BONUS! Read all about the brand new community connectors
In September 2017, Google launched native data connectors for Data Studio, so you can bring almost any data into your Data Studio reports directly.
Read more about what they are, how to use them and how to build your own with Apps Script, in this in-depth post.
My Google Sheets & Data Studio newsletter
Why not sign up for my rather lovely Google Sheets & Data Studio newsletter? Join my community of over 5,000 data analysts, digital marketers, educators and YOU! 👍
Further reading and resources:
Google Data Studio: A Step-By-Step Guide from Daniel Waisberg.
Empowering Google Analytics with Google Data Studio from Lizzie Silvey.
A First Look at Google Data Studio from Ben Jones at Data Remixed.
Google Data Studio Public Beta from Datasaurus-rex.
Create visually appealing reports with Google Data Studio from Rachael Law.
More advanced tips on Data Studio from David Loake.
There’s a Google Data Studio community! Join it here.
There’s also an active forum to ask questions and share knowledge.
Know any other good resources? Let me know in the comments and I’ll add them here.