Implementing DataStudio Dashboards with Goal Markers

As I’m writing this, the fourth quarter of the year is knocking at the door. And I’ve been tasked with setting up a new dashboard for tracking our measurable goals for the rest of the year.

I’ll set up a monitor or TV in my team’s work area and we will display this dashboard at all times. Hopefully, this will help to inform our planning and discussions to keep us aligned with our goals.

When building dashboards like this at Vendasta, we often leverage BigQuery tables and “views” to surface metrics and graphs via Google DataStudio. Google DataStudio provides an excellent baseline for data visualization. However, it can be tough to build some more comprehensive graphs.

Source: Google DataStudio Tutorial

In this article, I’ll share some of my techniques for preparing BigQuery views for DataStudio. I will focus, primarily, on presenting data along with goal lines. This way, you can have a better idea of whether or not you’re on track.

Building a BigQuery view for your goals

Whenever possible, I attempt to overlay “goals” on my graphs in DataStudio. To keep my queries organized and easy to understand, I split those goals into an entirely separate query and save it as a view.

Let’s say you worked at a company with a (very strange) Q4 goal of “10000 lines of code written by developers.” If you wanted to add a couple of goal markers to your graph, you could build a view that looks something like:

SELECT
 activity_day, day_num, goal_50, goal_100 
FROM (
 SELECT
  d AS activity_day,
  5000/
  (DATE_DIFF(DATE ‘2019–12–31’, DATE ‘2019–10–01’, DAY)) * 
  (DATE_DIFF(d, DATE ‘2019–10–01’, DAY)) AS goal_50,
  10000/
  (DATE_DIFF(DATE ‘2019–12–31’, DATE ‘2019–10–01’, DAY)) *     
  (DATE_DIFF(d, DATE ‘2019–10–01’, DAY)) goal_100
 FROM (
  SELECT GENERATE_DATE_ARRAY(
   ‘2019–10–01’, ‘2019–12–31’, INTERVAL 1 Day
  ) AS dates
 )
 JOIN
 UNNEST(dates) AS d 
)

(I usually just copy this view for every new graph I build and customize the values to suit the different metrics)

This query will produce the following results when run. Notice that we start at 0 at the start of the quarter, increase by about 110 every day, and end the quarter with our goal of 10000 “lines of code”.

Let’s save this as a BigQuery view so we can add it to DataStudio. I’ll call it `blog_goals`.

Adding this Data Source to DataStudio and charting it on a Time Series graph, we end up with the following graph:

This is great, but this graph is missing our real user data, so it’s not very useful. We can use DataStudio’s “Blend Data” feature to mix our goals with our real data.

For this blog, I assembled some mock data to display alongside the goals.

Assuming we’ve already added the mock data above as a data source, we can select it by clicking the “Blend Data” option on our chart.

This will display the data blending pane. Note: the “join keys” must have the EXACT SAME name and data type to be blendable.

Here we can select our join key (activity_day) and choose which metrics we’d like to sample from each data source. I’ve selected “goal_50”, “goal_100” from blog_goals and “cumulative_count” from “blog_data”.

After clicking “Save”, our graph will now look like this:

Perfect. Now we can track our progress throughout the quarter and pivot if we notice a sudden spike or a lull in activity.

Dealing with non-cumulative data

Now, you may have noticed in the example above that our “blog_data” source was conveniently providing us with a cumulative count for the “lines of code” metric. However, in reality, your data will likely not be as well prepared.

What would we do if the data were actually “lines of code written ON this date”? We would need to accumulate that count over time to render the same graph as above.

Example of non-cumulative data

DataStudio does offer a “cumulative” setting for TimeSeries charts.

And we can turn this on for the individual metric that we want to accumulate, leaving the other “goal” lines untouched.

day_count is being summed along the x-axis

Extra: Modifying the Goals query for ease of editing

The original query I provided for generating the goals graph gets the job done.

However, as I mentioned previously, I find it useful to clone this view and tailor it to new scenarios. Since the boundary dates are defined multiple times in this query, we have to slog through, updating all of the dates. It’s tedious.

As a refresher, here’s the original query:

SELECT
 activity_day, goal_50, goal_100 
FROM (
 SELECT
  d AS activity_day,
5000/
  (DATE_DIFF(DATE ‘2019-12-31’, DATE ‘2019-10-01’, DAY)) * 
  (DATE_DIFF(d, DATE ‘2019-10-01’, DAY)) AS goal_50,
10000/
  (DATE_DIFF(DATE ‘2019-12-31’, DATE ‘2019-10-01’, DAY)) *     
  (DATE_DIFF(d, DATE ‘2019-10-01’, DAY)) goal_100
FROM (
  SELECT GENERATE_DATE_ARRAY(
   ‘2019-10-01’, ‘2019-12-31’, INTERVAL 1 Day
  ) AS dates
 )
 JOIN
 UNNEST(dates) AS d 
)

To make this easier to reuse, I have started using the following variant, which leverages BigQuery’s WITH keyword to create a temporary view.

WITH vals AS (
 SELECT 
 DATE ‘2019-10-01’ as quarter_start,
 DATE ‘2019-12-31’ as quarter_end,
 5000 as goal_50,
 10000 as goal_100
)
SELECT
 activity_day, goal_50, goal_100 
FROM (
 SELECT
 d AS activity_day,
 (SELECT goal_50 FROM vals)/
 (DATE_DIFF(
  (SELECT quarter_end FROM vals), 
  (SELECT quarter_start FROM vals), DAY
 )) * 
 (DATE_DIFF(
  d, (SELECT quarter_start FROM vals), DAY)
 ) goal_50,
 (SELECT goal_100 FROM vals)/
 (DATE_DIFF(
  (SELECT quarter_end FROM vals), 
  (SELECT quarter_start FROM vals), DAY
 )) * 
 (DATE_DIFF(
  d, (SELECT quarter_start FROM vals), DAY)
 ) goal_100
 FROM (
  SELECT GENERATE_DATE_ARRAY(
   (SELECT quarter_start FROM vals), 
   (SELECT quarter_end FROM vals), 
   INTERVAL 1 Day
  ) AS dates 
 )
 JOIN
 UNNEST(dates) AS d 
)

Notice that, while this does make the query more complex to read, it isolates the important values to the top of the query.

To generate an arbitrary set of goals across an arbitrary date range, a developer needs only to adjust those 4 values (down from 14).

—

Hopefully the queries and strategies I’ve shared above can assist you in building dashboards to plan work or to monitor systems. Consider, also, that these queries could be modified with a bit of creativity and effort to build exponential goal lines, static “water lines” that remain constant throughout the period, and many other applications.

More Resources

Vendasta: https://vendasta.com

Google BigQuery Documentation: https://cloud.google.com/bigquery/docs/

Google DataStudio Introduction: https://support.google.com/datastudio/answer/6283323