Today, let’s construct customer time cohorts, specifically first purchase dates, and look at metrics like retention and revenue by cohort. We’ll work with fictitious transactions data of roughly 340,000 transactions dating from March 1, 2021 to February 28, 2022 (time machine not included). The product and seller context aren’t immediately available. For the purpose of this exercise and given the nature of the data, let’s assume transactions for an online wine shop. In this case, time cohort analysis can help us understand how retention, quantity, price, and revenue evolve both over the customer lifetime due to product quality, operations, and support and over the product lifetime due to onboarding and customer success.
To construct cohorts, we’ll need purchase history by unique customer id. The orders data set contains client ids, product ids, and purchase dates, while the products data set contains prices and product ids. We would also like to understand revenue by cohort. However, since we only have prices and not quantities, we’ll randomly generate quantities based on a right-skewed distribution as often seen in real transactions data and calculate revenue in order to simulate realistic revenue data. Let’s merge these two data sets together on product id to get what we need.
We’ll create customer cohorts based on their initial purchase month and a cohort index (time offset) of the difference in months of their subsequent purchases from initial purchase month. Finally, we’ll compare and visualize cohort metrics. We’ll calculate retention, or the proportion of customers in each cohort that return in subsequent months. We’ll also look at cohort revenue. At the end, we’ll walk through high-level findings on how the new customer base, retention, and average revenue varies over time and areas to be investigated further.
Code below and in more readable form on colab and GitHub.
