Getting Started with Looker (née Google) Data Studio

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

Looker (born as Google) Data Studio is probably the best free dashboarding tool out there, and a great place to get started - if you do need to upgrade to a more enterprise-grade version, you always have that option (for a price, of course).

Data Studio connects both to Google Sheets and to analytics warehouses like BigQuery - so no matter where you're storing raw data, you'll be able to pull it into your dashboards.

Note: This post contains nine videos excerpted from a since-retired Data Studio course. While the Data Studio UI has changed over the years, the fundamentals we cover here remain mostly the same.

Let’s dive in!

• Connecting a data source to Google Data Studio
• Prepping a Google Sheets Data Source
• WTF are Dimensions and Metrics?
• Building a simple table
• Creating time series charts
• Styling your charts
• Adding Calculated Fields
• Building Report-wide Filters with Date and Control Filters
• Adding the Same Charts and Filters to Multiple Pages
• What will you build?

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 native Google data sources: Adwords, BigQuery or  MySQL databases, Google Analytics, Youtube and Google Sheets.

A common pattern is to pull data from many different sources into Google Sheets or an analytics warehouse like BigQuery, 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 generally 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.

In the video above, we built a simple pivot table, using sample Twitter data we 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 dashboards looking great.

You're able to create your own themes, add a logo to the top-left corner, and any other styling flourishing you might need.

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?

Data Studio has been remarkably durable over the years since it was introduced in 2016. It's still one of our favorite reporting tools - hope you enjoy!