Getting Started with Google Data Studio

Dashboarding is like winemaking or brewing beer – everyone seems to have their own special recipe.

Some people swear by Cyfe or Geckoboard – others (myself included) prefer to build dashboards in Google Sheets.  But Google Data Studio is one of those tools that will shake up everyone’s approach: there’s never been a free tool like it.

And Data Studio connects with Google Sheets as a data source – so you can still use Blockspring or Supermetrics to pull data from any source, and push it up to a dashboard.

As part of putting together Data Studio the Lazy Way, the new CIFL course, I had the pleasure of getting intimately familiar with the nooks and crannies of Data Studio.

This post contains nine videos excerpted from the course, with cliff’s notes embedded beneath each.

Let’s dive in!

Connecting a data source to Google Data Studio

The first step to building any dashboard is wrangling your data – connecting the sources you’ll use to make charts.

Google Data Studio allows you to connect any number of data sources: Adwords, BigQuery or  MySQL databases, Google Analytics, Youtube and Google Sheets.

I usually dump data from many different sources into a Google Sheet, then connect it to Data Studio to create charts.

Once you connect a data source, you’ll be prompted to check that your columns are being pulled in correctly.  There are two things to watch out for:

1) Are columns being properly picked up as metrics or dimensions?

Metrics are fields that can be counted (ie are numbers), and dimensions are fields that you’d use to group data by (usually text fields).

 

2) Are date fields being recognized properly?

This is important, as you’ll need a date field to make any type of time series charts.

Prepping a Google Sheets Data Source

When connecting sheets to Data Studio as a data source, each tab is a unique data source, and needs to be connected separately.

This is why I recommended pulling all of your data into one tab whenever possible, before connecting it to Data Studio.

Data Studio is fairly finicky when it comes to recognizing data from Google Sheets, have to make sure your sheet’s formatted correctly before connecting it as a data source.

Dates must be in yyyy-mm-dd or yyyymmdd format, dates like ‘mon may 3 2002 12:23:00’ won’t be recognized.

Be sure to keep headers in the first row of your Sheet, and data below that. You can select a custom range instead of the entire tab, but I find it’s easier to avoid that whenever possible.

WTF are Dimensions and Metrics?

If you’ve ever worked with Google Analytics data, you’re familiar with the differences between dimensions and metrics.

Dimensions are text fields, like Browser or City.

Metrics are numeric fields, like Users or Sessions.

You can slice metrics by dimensions – for example, slicing number of Users by Browser.

Data Studio uses this same concept, so each of the charts or tables you build will have both a dimension and a metric.

Date columns are a special type of dimension, formatted as a date, that can be used in time series charts and in date filters.

Building a simple table

Once you’ve connected a data source, you can start building tables (pivot-style) to do some simple aggregation.

Learn how to build a simple pivot table, using sample Twitter data I pulled into a Google Sheet.

One nice thing about these tables, is that you can set up simple pagination – making it easy to browse through pages of data (something you can’t do in a spreadsheet).

Data Studio also lets you filter data in tables very easily – by including or excluding values based on simple logic.  This will come in handy when you’re building more complex charts.

Creating time series charts

Data Studio really shines when building charts to analyze how data changes over time.

Once you’ve set up your date range dimension (critical!), you can set a custom date range – the last 2 weeks, last 30 days, the last year.

You can also set a date comparison range, to compare say the last 30 days to the 30 days prior to that.  For me, this is what really sets Data Studio apart from a spreadsheet dashboard.

Styling your charts

Data Studio comes out of the box with a theme, Simple Dark, that is all you need to keep your dashboard looking sexy.

Since I’m lazy, it’s a waste of time to bother doing much more styling than that.

Adding Calculated Fields

Calculated columns are a super-powerful feature – they allow you to calculate new metrics, without leaving data studio. this means you can do much less formula coding in your google sheet, and instead push that math up to data studio.

For example, if you wanted to count tweets by day of the week, you could use the WEEKDAY formula on your date column.

My favorite calculated column formulas are CASE, COUNT, and date formatting formulas like WEEKDAY:

1. CASE

CASE allows you to set a field based on whether another field matches your conditions:

CASE WHEN REGEXP_MATCH(text, ‘^RT.’) THEN ‘retweet’ WHEN REGEXP_MATCH(text, ‘^@.’) THEN ‘reply’ ELSE ‘primary’ END

 

2. COUNT

COUNT allows you to, er, count the number of results in your data:

 

COUNT(text)

3. WEEKDAY

WEEKDAY (and other date formulas like MONTH, YEAR, DAY) allow you to slice out just one portion of your date column:

WEEKDAY(date, ‘DEFAULT_DASH’)

Take a spin through all of the calculated column formulas in the Google help docs.

Building Report-wide Filters with Date and Control Filters

Have you seen those beautiful dropdown menus on Data Studio reports?

They let you select a variable date range – last week, month, quarter, year – or filter for a specific dimension of your data.

For example, if I wanted to look at Twitter data from just one username, I’d set up a filter control on the ‘username’ dimension.

Setting up a date or control filter is simple – just select them from the right side of the main menu, and drag one onto your dashboard.

For date filters, you select a default date range (last 2 weeks, for example). And date filters will apply to all reports on a page by default, so no additional setup is required.

For control filters, you select a data source and dimension that you’d like to filter by. Then, to apply the filter to a specific chart, you ‘group’ them together (accessible from the right-click menu).

Note: control filters only work if they’re *on the same data source* as the chart they’re filtering for.

Adding the Same Charts and Filters to Multiple Pages

If you’re adding multiple pages to your Data Studio reports, you’ll likely want some of the same images, charts, titles and filters to show up on them.

This happens by making them ‘report-level’ in the right-click menu, which will add them to any page on your dashboard.

If you revert them back to ‘page-level’, they’ll only appear on the current report page that you’re working in.

What will you build?

If you’re curious to learn more about building dashboards in Data Studio, I’d recommend diving straight into Data Studio the Lazy Way – grab a discount code for 15% off in the CIFL Template Vault here.