Data viz

Scorecard sparkline – How to build your custom version with adaptative time granularity


Scorecard sparkline with adaptative time granularity inside Looker Studio

Sparkline is a mini line chart without axis and labels to get more context about the trend of a displayed metric for a specific period or variation series. It is often used in data visualization through tables displaying metric trends broken down by dimension values, or in combination with indicator scorecards.

This article is about this 2nd case inside Looker Studio (formerly known as Google Data Studio).

Sparkline should display a metric trend through a line built thanks to several dots, even when you select a single day. The granularity of a variation should also change to get the best insights: If you select a full year as date range, the trend would be more consistent with monthly variations contrary to daily ones.

So, let’s cook a custom configuration to address these particular case and improve the behavior by default of a basic configuration, based only on daily granularity.


Need to refresh this article

I wrote the first version of this article in January 2021. A lot of improvements & features have been released for more than 2 years of Looker/Data Studio life, including data blending engine with join types and CASE statement capabilities for calculated fields.

In the other hand, Data Studio was also rebranded to Looker Studio and Google Analytics Universal is no longer the norm, 2 months before its sunset, even as a data set for a viz tutorial: Even worse, it could be confusing now with GA4, and their unavailable fields through the direct native Looker Studio connector.

At last, Google Cloud team has just released new improvements about scorecard chart type, including sparklines ๐Ÿ˜ƒ.

So yes, there are a lot of reasons to update this article about adaptative sparkline for reports & dashboard with dynamic date range.


The result

For this tutorial, I used a sample data source from BigQuery with hourly sessions. 3 fields : Date, Hour as Number and Sessions.

Of course, you can use any other data source to apply the same recipe. If your data source returns only daily data, feel free to remove the hour level from this tutorial.

Now, let’s see the result of our sparkline, using the built-in feature available on scorecard charts, but customized to apply the following rules to adapt time granularity of trends regarding the number of days for the selected date range:

  • 1 day selected: Hourly variations
  • From 2 days to 55 days: Daily variations
  • From 56 days to 179 days: Weekly variations
  • More than 179 days: Monthly variations

If we select only one day of data, our custom sparkline automatically adapts the time dimension to draw a line based on hour dots. We keep the function of the sparkline: providing timing trend.

And, conversely, if we select too much days, the sparkline is built with dots from higher time granularity.




Current limitations of sparkline for scorecard

The sparkline for scorecard is quite specific inside Looker Studio. For the moment, only date and time dimensions are allowed with limitations about max number of rows. Otherwise, you will notice annoying performance problems. I suspect behind the scenes the usage of the same engine shared with time series charts (different to line charts), but this time, without all the options and error message management.

During my tests, I noticed cases of high granularity with side effect on browser performance, with browser error popup and error page redirections provoked by Data Studio frozen page.

To workaround these limitations of field types and performance, I had to consider and transform all levels of time granularity as series of numbers, and provide it to the scorecard as minutes. Minute fields must be valued with a value between 0 and 59, so 60 is my maximum number of dots to draw the sparkline. There is no label or additional info displayed when you put your cursor over the sparkline ; so providing series with the right metric values to draw the line makes the job.

It was possible for me to transform dates to rank values but I hope in the future some improvements on sparkline management to avoid this trick of trick, and allow to work with series easily an not just for date & time dimensions.

On the other hand, there is no function inside Looker Studio for running calculation, so the workarounds are limited.

Now you know why my final calculated field seems complicated. No choice except this trick to address my visualization improvement using the new sparkline feature for scorecard.


Ingredients to create the automatic switch of time granularity for sparklines

To get this automatic behavior in our dashboard, following the number of days of data, we will combine the Looker Studio components below.

  • Blended data
    To isolate the calculation of the number of selected days and minimum date, and so, avoiding impossible mix of aggregated and non-aggregated fields
  • A scorecard chart with sparkline
  • A calculated field on the scorecard chart level, as sparkline field

Blended data

We create new blended data named “BD – Data with number of days” with 2 tables respecting the following configuration, and using the same data source:

  • First table
    • Data source: The main data source, here my BigQuery table
    • Dimensions: Date, Hour
    • Metrics: Sessions
  • 2nd table
    • Data source: The main data source
    • Dimensions: “Nb of days” and “Min date” using the formulas of calculated fields below (don’t take in account the error message and the red color on these 2 aggregated fields. everything is fine)
  • Join configuration
    • Operator: Cross join
    • No key


Nb of days
COUNT_DISTINCT(Date) 

Min date
MIN(Date)

Scorecard chart

Now, create your scorecard chart with the configuration below:

  • Setup tab
    • Data source: The blended data “BD – Data with number of days”
    • Metric: Sessions
    • Sparkline: “Minute” using the formula of calculated field below.
      Field type must be “Minute (mm)”, check and save the calculated field twice if necessary.
  • Style tab
    • Sparkline: Choose the line color and turn on “Fill” and select “Line to zero” for missing data

Minute
MINUTE( DATETIME(1970,1,1,0,
 CASE 
  WHEN CAST(Nb of days as NUMBER) <= 1 THEN Hour
  WHEN CAST(Nb of days as NUMBER) >= 180 THEN ((MONTH(Date)-MONTH(PARSE_DATE("%Y%m%d",Min date)))+((YEAR(Date)-YEAR(PARSE_DATE("%Y%m%d",Min date)))*12))  
  WHEN CAST(Nb of days as NUMBER) >= 56 THEN (((YEAR(Date)-1)*52)+IF(WEEK(Date) IN (52,53) AND MONTH(Date) = 1, 0, WEEK(Date)))-(((YEAR(PARSE_DATE("%Y%m%d",Min date))-1)*52)+IF(WEEK(PARSE_DATE("%Y%m%d",Min date)) IN (52,53), 0, WEEK(PARSE_DATE("%Y%m%d",Min date))))
  ELSE DATE_DIFF(Date,PARSE_DATE("%Y%m%d",Min date))
 END
 ,0))

Configuration review

It was our last action. Now, let’s make a quick review of all elements of our custom configuration.



Final thoughts

  • Sparkline on scorecard is a nice addition but for advanced visualization usage, some improvements would be welcomed.
  • This article was also an opportunity to play with time & series functions. One time again, I’m not sure if there is a language or any technical playground where date/time format and management are easy.
  • You will find a lot of similarities of approach on another tutorial about Time dimension slider for line charts. What, I cooked here with minute field, I’ve previously done it with text with format allowing a chronological order.

Il faut que tout change pour que rien ne change