Set up Flexible Retention Analysis without Derived Tables

  • 10 March 2020
  • 1 reply

Retention is a metric to understand how many users return to your platform after the initial conversion. Retention is commonly used as a long-term health indicator in product development and improvement. Additionally, by examining how retention varies by geography, gender or other behavioral characteristics, the business gains better understanding of the contributing factors for retention and can shape data-driven business strategies.

Even though the cohort retention can be calculated with SQL, reports with slicing and dicing the cohort by other user attributes requires much more effort to maintain. For example, one of our business stakeholder wants to further investigate into a certain cohort, and asks questions like: “What is the impact of acquisition source on the retention for the 2019 Q4 cohort”? We want to take advantage of Looker’s flexible and customizable structure and design an analytical framework so that our business stakeholders can perform self-serving retention analysis by slicing and dicing the cohort with ease.

Some Looker models achieve this functionality by generating a derived table using LookML pseudo-code; unfortunately, this approach can be computationally heavy for large dataset and generally less flexible. Here we are going to provide a much more straightforward and computationally tractable way of building a highly customizable and scalable retention Explore in Looker.

With this solution for retention analysis in Looker, we are able to:

  • visualize retention curves of cohorts

  • slice and dice the cohort with ease by any dimensions available (or that we add at a stakeholder’s request)

  • evaluate retention with various metrics

  • not need to manage/synchronize any underlying pre-calculated tables

The detailed setup as well as extended functionality are documented in this Medium article:

1 reply

Userlevel 7
Badge +1

Halfway through this, I started wondering if this was a Ro post 😉. Your team always has the coolest projects and the most delightful writeups. Thank you for sharing!