Looker Studio year over year (YoY) metric comparison for analytics annual report
Building a statistics annual report is a nice exercise for challenging the way to display metric values comparison over a granularity of time inside our data visualisation tool.
We could decide to use the default available feature of the tool, through the comparison data range of each chart option inside Looker Studio (formerly known as Google Data Studio)…we could.
Across this article, we will work towards another visualisation result, getting the full control for narrative purposes about the comparison of a specific metric, year over year (YoY famous acronym in stat reporting vocabulary).
- What is the main message of the report section?
- Which metric needs to be highlighted?
- Which information are less important, just the background of the stage, the data context?
In spite of the fact that we work on an annual report, we can use dynamic data or prepared data according to your data context, and so, use the same visualisation materials next year against minor changes.
But we also consider that we can adapt it manually following the displayed data because the report is not interactive and because there is no strong requirements to keep it dynamically reliable over the time for visualisation purposes. No need to create unnecessary constraints, common sense first.
Ingredients
Let’s begin to build this custom bar chart with Looker Studio to compare visually the value of a specific metric in 2021 Vs 2020. The tutorial is based on “online sales” metric, feel free to adapt it with one of your KPIs and another time granularity.
To create the bar chart and the related McKinsey title, let’s use the following elements:
- A Google Sheets data source as an easy way for visualisation data preparation, splitting values by year
- Blended data to mix data & metric value from our main data source (here Transactions from Google Analytics renamed as “online sales”), with Sheets data source
- A bar chart – stacked combo chart more precisely – with calculated fields for wished metric stacks, highlighting the absolute change
- A one-row table chart for the title with a calculated field for the relative change and the related wording
1. Google Sheets data source
I create a simple Google Sheets with one tab & one column “Year type” with 2 values : “Previous year” for the first data row and “Last year” for the second data row. Formally, “Previous year” will correspond to 2020 and “Last year” to 2021. The required metric value will be assigned thanks to the data blending and the related calculated fields of charts.
Then, we add the Google Sheets tab as a Looker Studio data source.
2. Blended data
Now, we configure blended data “BD – Online sales for last and previous year” as described below for our example:
- As left data source
- Data source: Main data source, here Google Analytics data source
- Join key: None
- Dimensions: None
- Metric: Transactions, renamed as “2020 online sales”
- Date range (custom): Jan 1, 2020 to Dec 31, 2020
- Join type: Cross join
- As 2nd data source
- Data source: Main data source, here Google Analytics data source
- Join key: None
- Dimensions: None
- Metric: Transactions, renamed as “2021 online sales”
- Date range (custom): Jan 1, 2021 to Dec 31, 2021
- Join type: Cross join
- As 3rd data source
- Data source: Google Sheet data source with year types
- Join key: None
- Dimension : Year type
3. Bar chart configuration
Now, we build the bar chart:
- Char type: (stacked) Combo chart
- Data tab
- Data source: Your blended data “BD – Online sales for last and previous year”
- Dimension: A chart calculated field “Year” with the formula below
- Metrics:
- A chart calculated field “Lower value” with the formula below
- A chart calculated field “Change” with the formula below
- A chart calculated field “Total” with the formula below
- Sort by “Year” (so the “New Field” dimension) ascending
- Style tab
- Series #1: Bar with a light color (common stack to 2020 & 2021)
- Series #2: Bar with a strong color, turning on data label with compact number (change between 2020 & 2021)
- Series #3: Line, without line weight, with neutral color, turning on the display of point and data label with compact number, on left axis (total)
- General: Stacked bar on left axis
- Axis: Show axis
- Other: Remove all other elements (grid, legend, high custom tick interval to hide left Y-Axis…
- Adapt manually the size of the chart regarding the scale and the final rendering
Calculated field to use as bar chart dimension
YearIF(Year type="Previous year","2020","2021")Explanation of the formula: We hard-code the value of the dimension with the good year following the value of the Sheets row. No need to do it dynamically.
Calculated fields to use as bar chart metrics
Lower valueNARY_MIN(2021 online sales, 2020 online sales)Explanation of the formula:
We use the lower value as the reference of the chart, displayed with the light color.
ChangeIF (
(Year type = "Last year" AND 2020 online sales <= 2021 online sales) OR (Year type = "Previous year" AND 2021 online sales <= 2020 online sales)
,
NARY_MAX(2021 online sales, 2020 online sales)-NARY_MIN(2021 online sales, 2020 online sales)
,
null
)Explanation of the formula:
We calculate the change subtracting the lower value to the higher value, and applying the result as the 2nd series (so the new stack of the bar chart) only for the year with the higher value.
TotalIF(Year type = "Previous year",2020 online sales,2021 online sales)Explanation of the formula:
We display the total value following the year split of the chart
4. Table chart configuration for the McKinsey title
The main chart is ready. Let’s create the dynamic title or your narrative comment through the following table chart configuration:
- Char type: Table
- Data tab
- Data source: Your blended data “BD – Online sales for last and previous year”
- Dimensions:
- A chart calculated field “Change direction” with the formula below
- A chart calculated field “Message” with the formula below
- Metric: None
- Rows per Page: 1
- Style tab
- Conditional formatting: Create 2 rules to apply red color to Change direction field for “⬇” value and green color for “⬆” value
- Table header: Hide the header
- Table colors: Remove all colors
- Adapt table labels option, especially the text size
- Resize widths of table columns (small column for change direction field)
Calculated fields to use as table chart dimensions
Change directionIF(2020 online sales <= 2021 online sales,"⬆", "⬇")MessageCONCAT(
IF(2020 online sales <= 2021 online sales,"+","")
,
CAST(ROUND((2021 online sales-2020 online sales)/2020 online sales*100,0) AS TEXT)
,
"% of online sales, compared to the previous year"
)Explanation of the formula:
We calculate the percentage of change between 2020 and 2021 and concatenate it with a message and a “+” sign if the 2021 value is higher the 2020 one.
Configuration review
At last and as usual let’s check how our set of charts is configured in the video below.
