Looker Studio charts with data labels only for min and max values
Information displayed by default inside an interactive report must be chosen preciously, and it is particularly the case for data labels in a time series chart.
Commonly, we don’t need to display labels for all points because we read dashboards and reports progressively, using interactivity and control features to get secondary level of information (ex: put the cursor over a data point to get the related value).
So let’s take care of the data-pixel ratio dynamically inside a Looker Studio (formerly known as 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.
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
Looker 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 Looker Studio current limitations, and also available features that will be salutary.
- For each chart, you can choose to display all or none of data labels, and for the moment, conditional formatting is not available for time series and line charts.
- Calculated fields cannot mix already aggregated and not aggregated fields from the same data source in one formula
- Blending data can consist of joining the same original data source for 2 or more different sets of data (tables)…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…
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
Inside Looker Studio, data blending consists here of cross joining data sets. In our case, we will use exactly the same fields for left and for right table, 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 apply aggregation on right fields and associate it to left one.
Things will be clearer with a drawing. The table below illustrates the result of our data blending, based on left join without join key.
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.
- Left table
- Name: Main data set
- Dimensions: Date
- Metrics: Transactions
- Right table
- Name: Data for min and max
- Dimensions: Date, renamed as “Same date for min and max”
- Metrics: Transactions renamed as “Same transactions for min and max”
- Join configuration
- Operator: Cross join
- No key
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 slow 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, so we need to take care of related performance impacts.
Now, we draw the time series chart with the configuration below:
- Setup 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 (don’t take care of displayed errors)
- Create “Min” field as a 3rd chart metric with the formula below (don’t take care of displayed errors)
- 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 * ( 1+SQRT( 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.
Transactions * (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 2021
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.