Data viz

Google Data Studio adaptative sparkline for your dashboard KPIs

January 14, 2021 - by Mehdi Oudjida

Dashboard KPIs are often enhanced with time comparison (with the previous period or the previous year) as a small score card, and also with sparkline to get more context about the trend of the KPIs during the selected period.

This set of elements is displayed to serve the follow-up of each KPI and is not interactive. It must to be consistent and readable regardless of the selected date range. Sparkline should display a metric trend through a line built thanks to several dots, even when you select a single day.

Let’s apply what is necessary to address these requirements and improve the behavior by default of a basic configuration.

Google Data Studio sparkline for your dashboard KPIs

The result

Demo time! Let’s see how the orange sparkline (Revenue) behaves following the selected date range and compare it with the other KPIs with basic configuration.

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 of number of week: Smoother line, easier to click and less risk to overload the report because of drawing purposes.

Ingredients to create this of automatic switching of time dimension

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

As usual, I take advantage of this device with data from Google Analytics demo account, but as usual too, you can apply it to any dated data source.

  • Calculated fields
    3 time dimensions in text format for each necessary granularity (hour, day, week), a field to count the number of days of data, and a constant as blending key
  • Blended data
    To isolate our number of days of data, and avoid a calculation by data set row (total function are not yet available inside Data Studio)
  • Line chart
    To display our data, a line chart fueled by our blended data
  • Calculated field on the line chart
    To create the automatic switching between time dimensions following the number of days of data

Calculated fields

We need the following calculated fields. I use “AdSp” as prefix for “Adaptative sparkline”.
Let’s begin with our 3 levels of time dimensions cast as text to avoid format incompatibility and ease temporal sorting.

AdSp - Hour as text
CONCAT(REGEXP_REPLACE(CAST(Hour AS TEXT),"^([0-9]{1})$","0\\1"),"h")

Above, an example of regexp_replace function usage, transforming unique digital to 2 digit-format for hour, then adding “h” at the end of the string to avoid any doubt about the displayed unit. 

AdSp - Date as text
CAST(TODATE(Date, "%Y-%m-%d") AS TEXT)
AdSp - Week as text
CAST(TODATE(Date,"%Y W%W") AS TEXT)

Now, we create a calculated field to count the number of days in our data set, forcing a text format to create a dimension (not a metric).

AdSp - Nb days as dim
CAST(COUNT_DISTINCT(Date) AS TEXT)

Then, we switch to number type in the fields list of the data source, to compare it in the future. as a dimension, but with integers corresponding to our adaptative buckets of number of days.

Google Data Studio - Fields list - Type to Number

Last calculated field for our data source: A constant to use it as a common key for blended data.
Contrary to other use cases, this key is required to use CASE statement across blended data.

AdSp - All constant
"All"

Blended data

We create new blended data named “Adaptative sparkline for revenue” with 2 columns respecting the following configuration, using the same data source:

  • As left data source
    • Join keys: AdSp – All constant
    • Dimensions: AdSp – Hour as text , AdSp – Date as text , AdSp – Week as text
    • Metrics: Here, Revenue
  • As right data source
    • Join keys: AdSp – All constant
    • Dimensions: AdSp – Nb days as dim
  • Google Data Studio - Blended data for adaptative sparkline chart

Line chart

Now, we draw our line chart with the configuration below:

  • Select your blended data “Adaptative sparkline for revenue” as data source source
  • Create AdSp – Time dimension as a chart calculated field (see formula below)
  • Here, Revenue as metric
  • Sort by AdSp – Time dimension ASC
  • On the style tab, hide axes & legend
  • Resize your chart, insert your sparkline as wished with your KPI and adapt the styles
AdSp - Time dimension
CASE 
 WHEN AdSp - Nb days as dim <= 1 Then AdSp - Hour as text 
 WHEN AdSp - Nb days as dim > 150  Then AdSp - Week as text
 ELSE AdSp - Date as text 
END

C’est fini

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

Final thoughts remarks

  • This kind of mechanism is possible recently, after a correction of problem of case statement fields on blended data. Big thanks to Riccardo Muti and GDS team for having solved this issue.
  • The first time I’ve tried to built this kind of trick, my intention was to create a dimension selector using a list of dimension names on a Google Sheets data source and blend it to my GA data source to create a dropdown menu. After the launching of “parameter” feature, this kind of trick is no more necessary (cf. this article about secondary dimension).
  • Then, I thought about this particular use case with time dimensions switch, but on the road, waiting for the correction of the bug, I found a manual solution, with parameter feature (again)…I enjoyed designing it with a slider…it makes sense for granularity.
  • …
  • Of course, this custom configuration could be adapted to be deployed for other purposes, requiring static evaluation and corresponding to specific dimensions. Here, others examples of automatic switch:
    • Product category levels and product names following the possible values cardinality to display
    • Full back link URLS or just back links domains if too much instances
    • Google Ads campaigns level instead of ads group
    • …

Il faut que tout change pour que rien ne change