SQL for Retention Analysis: Preparing Cohort Data

The "What" and "Why": A Business Analyst's View on Cohorts

Before we write a single line of SQL, we need to think like a Business Analyst. A cohort is simply a group of users who share a common characteristic. In retention analysis, that characteristic is typically the time they started using your product (e.g., all users who signed up in January 2023).

Cohort analysis helps us answer critical business questions:

• Are our newer users sticking around longer than our older users?
• Did a major product change in Q2 improve long-term user engagement?
• Which marketing channels bring in users with the highest long-term retention?

To properly set up our analysis, we need to make three key business decisions.

For our exercise today, we will use a common e-commerce scenario:

1. Cohort Definition: A user's cohort is defined by the month of their first purchase.
2. Active Event: A user is considered "retained" in a given month if they make any purchase during that month.
3. Timeline: We will track retention on a monthly basis.

The "How": A Step-by-Step SQL Workflow for Cohort Data

Now that we have our business logic, let's translate it into SQL. We will follow a detailed video tutorial that walks through the entire process. This process breaks down a complex problem into manageable steps, which is a great approach for any analysis.

The core steps are:

1. Data Cleaning: Prepare the raw data by handling nulls and invalid records.
2. Assign Cohort Month: For each customer, find the month of their very first purchase.
3. Calculate Cohort Index: For each subsequent purchase, calculate how many months have passed since their first purchase. This "index" is the key to grouping user activity over time.
4. Aggregate and Pivot: Count the number of unique customers for each cohort and each index to create the final, structured table.

Let's start the walkthrough.

Step 1: Assigning the Cohort Month

This is the first critical step in our logic. For every transaction in our dataset, we need to know which "cohort" the user belongs to. As we defined, the cohort is the month of the user's first purchase.

There are two common ways to achieve this in SQL:

• Using GROUP BY: You can create a temporary table or CTE that groups by CustomerID and finds the MIN(InvoiceDate).
• Using Window Functions: You can use MIN(InvoiceDate) OVER (PARTITION BY CustomerID). This is often more efficient as it avoids a separate join or subquery. This pattern should feel familiar from our last lesson on funnels!

Let's see how the video tackles this.

The key takeaway here is creating a lookup that maps every CustomerID to their CohortDate. For example: CustomerID 12345 -> 2010-12-01.

Step 2: Calculating the Cohort Index

This is the heart of the analysis. For every single transaction a customer makes, we want to ask: "How many months has it been since this customer's first purchase?" The answer is the Cohort Index.

• A CohortIndex of 1 means the transaction happened in the same month as the first purchase.
• A CohortIndex of 2 means the transaction happened in the next month.
• ...and so on.

To calculate this, we need to find the difference in months between the InvoiceDate of any given transaction and the CohortDate we found in the previous step.

The logic usually involves these calculations:

1. Calculate the difference in years between InvoiceDate and CohortDate.
2. Calculate the difference in months between InvoiceDate and CohortDate.
3. Use a formula to combine these into a single index:
   cohort_index = (year_difference * 12) + month_difference + 1

Let's see this in action.

After this step, you will have a dataset where every transaction is tagged with both the customer's cohort and a cohort index.

Step 3: Preparing the Final Cohort Table

Our final goal is to create a table that counts, for each cohort, how many unique customers were active in each subsequent month (i.e., for each cohort index).

The final output should look something like this (in a "long" format):

This format is perfect for data visualization tools like Tableau. To get here, we simply need to group our data by cohort_month and cohort_index and take a COUNT(DISTINCT customer_id).

Some analyses go one step further and PIVOT this table, but having it in the long format is often more flexible and completes our data preparation goal.

Conclusion

Congratulations! You've just walked through the complete process of preparing data for cohort retention analysis. This is a challenging but incredibly valuable skill that combines data cleaning, date manipulation, window functions, and aggregation to produce powerful business insights.

Key Takeaways:

• Business First: Cohort analysis starts with clear business definitions for the cohort, the active event, and the timeline.
• Find the First Event: The first step in SQL is to identify each user's cohort by finding their first action (e.g., MIN(purchase_date)), often using a GROUP BY or a PARTITION BY window function.
• The Cohort Index is Key: The cohort_index, calculated as (year_diff * 12) + month_diff + 1, is the crucial metric that standardizes the user lifecycle across all cohorts.
• Final Output: The prepared data is typically a table with cohort_month, cohort_index, and customer_count, which is ready for calculating retention rates or for visualization.

Preview of the Next Lesson:
We have successfully prepared the data. In a future lesson in our "Business Analytics and Experimentation" module, we will learn how to "Calculate and interpret cohort retention rates to analyze user behavior over time." We will take the table you learned to build today, calculate the retention percentages, and use a tool like Tableau to create the classic color-coded cohort chart that is a staple of business reporting.