Google Data Studio

Google Data Studio charts with data labels only for min and max values

Information displayed by default in an interactive report must be chosen preciously, and it is particularly the case for data labels in a time series chart.

Do we really need labels for all points? No
Without several levels of reading? No
Displaying it as a secondary level through rollover in an interactive tool is not enough? Yes, of course, in most of cases it is sufficient.

So let’s take care of the data-pixel ratio dynamically inside a Google Data Studio dashboard, like we do for a presentation. Let’s display only min and max values of a time series chart with a simple home-made solution.

Google Data Studio - Charts with data labels only for min and max values

The result

At the end of this article, we will get the result below. I choose to apply this custom labelling to Google Analytics data for a line chart of e-commerce transactions. As usual and because I use a source of aggregated data, you will be able to do it with any data set:

  • With any source providing non aggregated raw data (number dimension), like trends of daily temperatures from BigQuery or Google Sheets
  • And also with aggregated data, as example, like the daily average position of keywords for website SEO follow-up

Data Studio available features & current limitations

To understand why we must follow the walkthrough below to get data labels and custom formatting only for plots corresponding to min and max values, let’s recap – for our use case – some Data Studio current limitations, and also available features that will be salutary.

  • For a each chart, you can currently choose to display all or none of data labels
  • Conditional formatting is not available for time series and line charts
  • Conditional formatting doesn’t allow to use aggregation functions as part of the available condition rules (same engine that comparisons for chart filters – with static rules)
  • Calculated fields cannot mix already aggregated and not aggregated fields from the same data source in one formula
  • CASE statement – useful for everything in Data Studio as an “if” operator – cannot include calculation or data transformation as part of conditions input or output (functions & mathematical operations are not available)
  • Calculated fields cannot be created on blended-data scope but only on the original data sources (so before blending) or on charts using blended data.
    So, it is not possible to create intermediary calculated fields, serving CASE statement (cf. limitation above).
  • Blending data can consist of left joining the same original data source for 2 or more different sets of data…and often, we use it just like that…for data preparation or calculation (ex: CTR of CTA, Funnel drop off…)
  • Blending data create dynamic snapshots of each data set and unlock aggregation capabilities (cf. functions and display options) for metrics already aggregated in the original data source
  • Data Studio proposes a set of arithmetic functions that can provide advantageous contributions…but so much underused…
  • Most of our data sources return already aggregated numerics (as metrics) with a level of aggregation defined by the requested dimensions, drilling down these metrics. It is more or less the definition of sets of aggregated data we usually manipulate with Google tools (Analytics, Search console, Ads…).

Required elements to build the custom labels and styles for specific dots

To get around the identified limitations above, and apply what is unsuspected possible, we need the following ingredients:

  • Blended data
    To snapshot our data set, and so, get the capability to use aggregation functions like MIN() and MAX(). It is also a way to get max and min values available for each data row, with date and related running metric value
  • Line chart
    To display our data in a line chart, fed by our blended data
  • 2 calculated fields on the line chart
    To create specific metrics for the min and max dots, with maths replacing CASE statement
  • Some line chart style customizations
    To display only dots and not lines of min & max values

Data blending

Inside Google Data Studio, data blending consists of left joining data of a data set with another one. In our case, we will use exactly the same fields for left and for right data source, just changing their names to differentiate them later. It is a trick to get for a specific date, access to all values of all other dates, and so, be able to make apply aggregation on right fields and associate it to left.

Things will be clearer with a drawing. The table below illustrates the result of our data blending, based on left join without join key.

Google Data Studio - Data blending to prepare data labels only for min and max values

We need the configuration below for your blended data source. It is exactly the same configurations at the right and at the left, just make sure to rename Date by “Same date for min and max” and Transactions by “Same transactions for min and max”, clicking at the left icon of each field.

  • As left data source
    • Join keys: –
    • Dimensions: Date
    • Metrics: Transactions
  • As right data source
    • Join keys: –
    • Dimensions: Date, renamed as Same date formin and max
    • Metrics: Transactions renamed as Same transactions for min and max

Google Data Studio - Blend data - configuration

Note: For performance and loading time purposes, you can use this technic and blend data from daily extracted data, especially if your data source is low to load live data or/and if you wish to apply this method for an important date range. After all, the result of this blending is getting the square of number of rows of the original data set.

Line chart

Now, we draw the time series chart with the configuration below:

  • Data tab
    • Select your blended data as data source
    • Add Date field as dimension
    • Add your metric, here Transactions as metric and use AVG aggregation
      (in our case, AVG is a way to get the real value before the rows multiplication because of data blending)
    • Create Max field as a 2nd chart metric with the formula below
    • Create Min field as a 3rd chart metric with the formula below
  • Style tab
    • Series #1 (transactions as trend) : Choose a neutral color without points and label, and use left axis
    • Series #2 (Max) : select a flashy color, an important line weight & show data label, you can show points but it is not mandatory to get a point in this case, and use left axis
    • Series #3 (Min) : Use a flashy color, an important line weight & show data label, be sure also to use left axis for scale consistency
    • General – Missing data: Line breaks
      (we need only points for max ans min value, not line of 0)
    • Adapt the other sections following the design of your report

      Transactions-MAX(Same transactions for min and max)

The formula above has 2 objectives:

  • Keep the value of the point if it is the max value
  • Return null if it is not the max value to not get à 0 point

We multiply transactions to a number that turns on or off the drawing of max value.

Let’s calculate the square root (SQRT() function) of the current value, subtracted of the max value. If the current value is also the max value, the result of root square will be 0 (√0 = 0). We add it the 1 and the multiplication will be fine without impact, so the drawing of the max dot will be done.

But, if the current value is not the max value, the root square of a negative value will raise an error (√-n = 🚫 ), cascading to all the formula and a null value as final output…nothing will be drawn thanks to what we configure on style tab (Missing data: Line breaks) 😈

And, of course, we use exactly the same trick for min value labelling and highlighting.

(1+SQRT(MIN(Same transactions for min and max)-Transactions))

Voila. Configuration review

Now, let’s make a quick review of all elements of our custom conditional data labelling.

Other example: Average position on Google Search results page

Added the 5th of Feb 2020

Here another example with Google Search Console Tools data. It is the same home-made system of labelling and highlighting, with average position by keyword on search results page. So, this time, we target Top 1 reversing Y-axis direction and adding a reference line with trophy emoji as label.

Maybe, a good visual way to tell a story about good progressions because of SEO productive actions during a meeting as example.

Google Search Console Tools - Average position

Dis Joe, quand est-ce qu’on mange ?