working-with-data

Preventing churn with retention analysis in Metabase

· 45 minutes

  days
:
  hours
:
  minutes
:
  seconds
 

About this event

On June 20, we met with Candice Ren, a co-founder of 173tech, a data consultancy agency, to discuss retention analysis, data structuring, and mastering retention queries using Metabase.

Guest

Candice Ren

Candice Ren

Founder, 173Tech

Candice Ren is the co-founder of 173tech, a modern analytics agency helping fast-growing companies turn data into powerful growth engines. Their team of data experts has been using Metabase since 2019 helping SaaS, mobile apps, and eCommerce businesses to get a sense of their data.

Summary

This event covers the concepts of retention analysis and how Candice Ren, co-founder of 173 Tech, builds a retention analysis step-by-step in Metabase.

See the full video for Candice’s demo of how to use SQL to calculate retention rates in Metabase, her suggestions for visualizing the data, such as using heatmaps and line views, and how you can materialize results in a data warehouse or through ETL process to ensure consistent views and simplified querying.

In this event, Candice breaks down how retention analysis is an assessment of a business's health based on its ability to bring customers back. This metric measures loyalty, engagement, and the lifetime value expected from the customer base. It can help a business keep users engaged and coming back, and is more effective and cost-efficient than reactivating churned customers.

Retention analysis plays a vital role in product management, assessing product-market fit, feature-market fit, and identifying potential risks or opportunities. By paying attention to retention, your business can increase customer loyalty and lifetime value. By understanding different parts of retention analysis and implementing these strategies, your business can prevent churn, improve product-market fit, and identify opportunities for growth.

Candice dives into topics related to retention analysis, and shows you how to complete a retention analysis in Metabase by breaking down data to gain deeper insight and identify trends across different cohorts, countries, or platforms.

Some of the topics covered in this event include:

Activity retention: Assesses whether users are returning to a platform or app and engaging in various activities. For example, if you have an app, this could be measured by logging in and performing various activities.

As a part of the retention analysis framework, activity scoring modeling helps you differentiate various user activities and assign numerical values to them. By calculating an activity score on a user level, you can analyze activity score retention over time, going beyond simple percentage-based retention metrics. This deeper understanding of user engagement helps you identify which activities drive retention and tailor their strategies accordingly.

For example, on a social media platform like LinkedIn, liking a post is different from writing and publishing an article. To account for this, you assign different weights to various activities. By calculating an activity score on the user level, you can analyze activity score retention over time, going beyond mere percentage-based retention.

Feature retention: Focuses on evaluating whether users are returning to use specific features, particularly important for businesses with subscription models. For example, if you are further along in your product development, you may want to focus on feature retention. If you run a subscription model, you would want to evaluate how long people are retaining their subscriptions on average.

Building retention analysis in three steps: Gathering data and calculating retention numbers, visualizing and materializing the data for analysis, and telling a compelling story with the database dashboard.

This is the SQL query Candice used for calculating retention rates:

with cohorts as ( -- calculate cohort size select date_trunc(`bliss_analytics_viz.dim_memberships`.trial_start_dt, month) cohort_month , count(distinct `bliss_analytics_viz.dim_memberships`.membership_id) n_memberships from `bliss_analytics_viz.dim_memberships` where 1=1 [[and ]] [[and ]] group by 1 ) , ret as ( -- calculate number of users that are active during the retention window select date_trunc(`bliss_analytics_viz.dim_memberships`.trial_start_dt, month) cohort_month , floor(DATE_DIFF(`bliss_analytics_viz.f_listenings`.start_dt, `bliss_analytics_viz.dim_memberships`.trial_start_dt, DAY)/30.0) month_since , count(distinct `bliss_analytics_viz.f_listenings`.membership_id) n_retained from `bliss_analytics_viz.f_listenings` inner join `bliss_analytics_viz.dim_memberships` on `bliss_analytics_viz.f_listenings`.membership_id = `bliss_analytics_viz.dim_memberships`.membership_id where 1=1 [[and ]] [[and ]] group by 1, 2 ) -- calculate retention rate select c.cohort_month AS cohort_month , r.month_since , r.n_retained / c.n_memberships as pct_ret from cohorts c inner join ret r on c.cohort_month = r.cohort_month order by cohort_month, month_since asc ;