The world’s leading publication for data science, AI, and ML professionals.

Visualize your product health status metrics using GA4, BigQuery, and Tableau

See the power of the heatmap table in action!

I will share one of my favorite visualizations to monitor a digital product’s health status in this post. I will focus on these three metrics: 1. Usage, 2. Success rate & 3. Completion rate. I will also share how to querying the mentioned metrics data from BigQuery.

GA4 allows all users to export raw data to Google cloud & Bigquery. This feature is fantastic because raw Data for Data professionals is like fresh organic ingredients to good chefs. Both are mandatory for great recipes! In addition, Google Cloud has extensive analytics tools to transform data, analyze it, applying ML on top of it & building data products, and much more.

Raw Data for Data professionals is like fresh organic ingredients to good chefs. Both are mandatory for great recipes!

Let’s start by getting the data of these metrics by querying it in BigQuery. The following query should help us achieve that.

-- Standard Sql, BigQuery
select parse_date('%Y%m%d',t.event_date) as date, platform, event_name, p.value.int_value as success, p2.value.string_value as flow_name
,count(event_name) as events_count
,if(p.value.int_value=1,count(event_name),0) as success_count
,if(p.value.int_value=0,count(event_name),0) as fail_count
from `[gcp_ptoject_name].[dataset_name].events_*` t, t.event_params p, t.event_params p2
where p.key = 'success' and _table_suffix between '20210101' and '20211231' and p2.key = 'flow_name'
group by 1,2,3,4,5
order by date desc

We got events to count per date, platform, flow name, event name, and the success status & then we do some calc to count the number of success and fail events.

The result of the above query should be something like the following table:

Now, after getting the data that we need, it’s time to visualize it. I’m going to use the Heatmap table to visualize the three metrics.

Heatmap table is one of my favorite charts to visualize data. It is a simple chart (just a table) that is understood by `anyone. It can encapsulate many dimensions in one image and enable you to spot trends easily and check absolute numbers if you want.

Now, let’s look into the heatmap of each metric.

1- Usage:

Usage is # of total times that people tried to do an event. You can also calculate it by # of users who tried to do an event.

There is no pre-calculation needed for this metric, explicitly shown in the data we got from BigQuery.

The following heatmap table is an example of visualizing usage trends per platform for all events and flows. Each cell includes two values (number of events and % diff from prev. day). The color code is used for % difference from the previous day to show how the usage trend of each event is changing. For example, on the 8th of Aug, there is a peak of usage for the song_end event for all platforms.

Heatmap table - Usage trends per event and flow for all platforms.
Heatmap table – Usage trends per event and flow for all platforms.

— – – –

2- Success Rate:

The success rate is the % of people who succeed in doing an event for all people who tried to do this event.

To calculate the success rate, you need to divide the successful events by all events. Here is how you can do in Tableau for the above data.

sum([Success Count])/SUM([Events Count])

The following heatmap is an example to visualize the success rate over time per platform for all events and flows. The color code is used to show if the success rate is dropping for certain features. For example, in the chart below, the song_play_start event has a significant drop of success rate on the 9th of Aug for the iOS App.

Heatmap table - Success rate per event and flow for all platforms.
Heatmap table – Success rate per event and flow for all platforms.

— – – –

3- Completion Rate:

Completion rate is the diff between users who completed the flow vs. people who started it. i.e., people who completed listening to track/ all people who started the track.

To calculate the completion rate, you can use the Tableau LOD feature to create the metric. For example, here is the code to create for the data above.

{include [Flow Name],DAY([Date]),[Platform]: sum(if(CONTAINS([Event Name],"end")) then [Events Count] else 0 END)/
sum(if(CONTAINS([Event Name],"start")) then [Events Count] else 0 END)}

The following heatmap is an example to visualize the Completion rate over time per platform for one flow. The color code is used for the completion rate to show if it is dropping at certain dates or platforms. For example, in the chart below, the play_track flow increases the completion rate between 8 & 9th of Aug for all platforms.

Conclusion:

In this post, I tried to share one of my favorite charts, the heatmap table, and how to visualize health status metrics. I also discussed how to query the data in BigQuery.

Do you have other ideas to visualize health status metrics? if so, please share with us here in the comments section! 🙂

— – – –

You can check my previous posts below:

Simplify your GA4 tracking & Analysis with this Framework – Spotify App as an example.

3 metrics to make sure your app is delivering the best experience to its users.

— – –

Note: All data shown in this post are dummy data and not real.


Related Articles