Cohort Analysis: Data Sourcing with SQL

Cohort Analysis: Data Sourcing with SQL

摘要

As an online business owner, you hope that engagement increases over time, leading to a longer period of retention. This is rarely the case, though. The majority of web applications see a gradual decrease in user engagement, eventually leading to churn. Your goal then is to stretch out the length of engagement as long as possible. The best way to measure this is through cohort analysis.

Put simply, cohort analysis is used to test whether certain groups of users, based on the conversion date, are active and engaging longer (or shorter) than other groups.

The ultimate goal is to test not only how users within each cohort engaged within your app over time, but to also compare and contrast different cohorts with one another. It’s quite often the case that even subtle changes in your application’s feature set will change user engagement, positively or negatively. If the latter, you want to know this as soon as possible to prevent further churn.

That said, let’s look at how to source and cleanse your data in order to begin analysis.