How to create churn table from transactional data?

Question

Currently my Transaction Table has customer's transaction data for each month. Account_ID identifies the customer's ID. Order_ID is the number of orders that the customer had made. Reporting_week_start_date is the week which begins on Monday where each transaction is made (Date_Purchased).

How do i create a new table to identify the customer_status after each transaction has been made? Note that the new table has the Reporting_week_start_date until current date despite no transactions has been made .

Customer_Status

- New : customers who made their first paid subscription
- Recurring :  customers with continuous payment
- Churned :  when customers' subscriptions had expired and there's no renewal within the next month/same month
- Reactivated : customers who had churned and then returned to re-subscribe

Transaction Table

Account_ID  | Order_ID |  Reporting_week_start_date| Date_Purchased | Data_Expired
    001     | 1001     |       31 Dec 2018         |   01 Jan 2019  |    08 Jan 2019
    001     | 1001     |       07 Jan 2019         |   08 Jan 2019  |    15 Jan 2019
    001     | 1001     |       14 Jan 2019         |   15 Jan 2019  |    22 Jan 2019    #Transaction 1
    001     | 1001     |       21 Jan 2019         |   22 Jan 2019  |    29 Jan 2019
    001     | 1001     |       28 Jan 2019         |   29 Jan 2019  |    31 Jan 2019

    001     | 1002     |       28 Jan 2019         |   01 Feb 2019  |    08 Feb 2019
    001     | 1002     |       04 Feb 2019         |   08 Feb 2019  |    15 Feb 2019    #Transaction 2
    001     | 1002     |       11 Feb 2019         |   15 Feb 2019  |    22 Feb 2019
    001     | 1002     |       18 Feb 2019         |   22 Feb 2019  |    28 Feb 2019

    001     | 1003     |       25 Feb 2019         |   01 Mar 2019  |    08 Mar 2019
    001     | 1003     |       04 Mar 2019         |   08 Mar 2019  |    15 Mar 2019
    001     | 1003     |       11 Mar 2019         |   15 Mar 2019  |    22 Mar 2019    #Transaction 3
    001     | 1003     |       18 Mar 2019         |   22 Mar 2019  |    29 Mar 2019
    001     | 1003     |       25 Mar 2019         |   29 Mar 2019  |    31 Mar 2019

    001     | 1004     |       27 May 2019         |   01 Jun 2019  |    08 Jun 2019
    001     | 1004     |       03 Jun 2019         |   08 Jun 2019  |    15 Jun 2019    #Transaction 4
    001     | 1004     |       10 Jun 2019         |   15 Jun 2019  |    22 Jun 2019
    001     | 1004     |       17 Jun 2019         |   22 Jun 2019  |    29 Jun 2019
    001     | 1004     |       24 Jun 2019         |   29 Jun 2019  |    30 Jun 2019

Expected Output

Account_ID  | Order_ID |  Reporting_week_start_date| Customer_status
    001     | 1001     |       31 Dec 2018         |   New  
    001     | 1001     |       07 Jan 2019         |   New               #Transaction 1
    001     | 1001     |       14 Jan 2019         |   New
    001     | 1001     |       21 Jan 2019         |   New  
    001     | 1001     |       28 Jan 2019         |   New  

    001     | 1002     |       28 Jan 2019         |   Recurring        
    001     | 1002     |       04 Feb 2019         |   Recurring         #Transaction 2
    001     | 1002     |       11 Feb 2019         |   Recurring  
    001     | 1002     |       18 Feb 2019         |   Recurring  

    001     | 1003     |       25 Feb 2019         |   Churned  
    001     | 1003     |       04 Mar 2019         |   Churned           #Transaction 3
    001     | 1003     |       11 Mar 2019         |   Churned      
    001     | 1003     |       18 Mar 2019         |   Churned    
    001     | 1003     |       25 Mar 2019         |   Churned    

    001     |    -     |       1 Apr 2019          |   Churned  
    001     |    -     |       08 Apr 2019         |   Churned    
    001     |    -     |       15 Apr 2019         |   Churned      
    001     |    -     |       22 Apr 2019         |   Churned    
    001     |    -     |       29 Apr 2019         |   Churned

    001     |    -     |       29 Apr 2019         |   Churned  
    001     |    -     |       06 May 2019         |   Churned    
    001     |    -     |       13 May 2019         |   Churned      
    001     |    -     |       20 May 2019         |   Churned    
    001     |    -     |       27 May 2019         |   Churned

    001     | 1004     |       27 May 2019         |   Reactivated  
    001     | 1004     |       03 Jun 2019         |   Reactivated       #Transaction 4
    001     | 1004     |       10 Jun 2019         |   Reactivated  
    001     | 1004     |       17 Jun 2019         |   Reactivated  
    001     | 1004     |       24 Jun 2019         |   Reactivated'
    ...
    ...
    ...
    current date

Is this a homework/study assignment? What have you tried so far? — Graham Polley, Jan 08 '20 at 11:29
You have three dates in your data. It is entirely unclear what the data has to do with the rules you have specified. — Gordon Linoff, Jan 08 '20 at 11:30
The customer status (Lead & Lag) actually is compared against the date_purchased, not Reporting_week_start_date. The Reporting_week_start_date is used to create the records in the new table - from the first Reporting_week_start_date of the data_purchased to last Reporting_week_start_date of the current date. Like Cohort Analysis. And another rule - Each order_id must have the same customer status. The customer status changes when the order_id changes (Next transaction) — Shang Rong, Jan 08 '20 at 14:54

score 0 · Answer 1 · answered Jan 08 '20 at 11:34

I think you just want window functions and case logic. Assuming the date you are referring to is Reporting_week_start_date, then the logic looks something like this:

select t.*,
       (case when Reporting_week_start_date = min(Reporting_week_start_date) over (partition by account_id)
             then 'New'
             when Reporting_week_start_date < dateadd(lag(Reporting_week_start_date) over (partition by account_id order by Reporting_week_start_date), interval 1 month)
             then 'Recurring'
             when Reporting_week_start_date < dateadd(lead(Reporting_week_start_date) over (partition by account_id order by Reporting_week_start_date), interval -1 month)
             then 'Churned'
             else 'Reactivated'
        end) as status
from transactions t;

These are not exactly the rules you have specified. But they seem very reasonable interpretations of what you want to do.

How to create churn table from transactional data?

1 Answers1