2 Replies Latest reply on Feb 17, 2018 9:04 AM by andreas långbergs

    Customer Churn with a subscription model

    andreas långbergs

      Hi!

      Im quite new to Tableau but managed to get my data up and running surprisingly fast. Im mostly interested in customer churn and in the long run to create a model that predicts churn (but thats only the long run).

       

      My data is built up by snapshots of our customer list with a few facts about every customer. For now i have set it up with a ”masterfile” where i will add all active customers in the bottom with a few added kolumns (weeknumber and date). This will, over time be a quite big file but hopefully it will work.

       

      I see a few cases that could occure white customer A, B, C and D over a period of 4 weeks:

       

      I 1 I 2 I 3 I 4 I      Status:

      I A I A I A I A I     New Customer

      I B I B I B I -  I     Churned

      I -  I C I -  I C I    New customer, Churnes, Re-aquired customer

      I -  I -  I D I D I    New customer

       

       

      I'we managed to get the model to understand that A and B are different but i think i need to start over because i need to find a new way to sense that customer C have churned in week 3 and to store that info for week 3 while have the information about the re-aquire in week 4.

       

      I’we tried to find something about this on the web but moust example i find have better raw data than i have. I dont know, based on the raw data if a customer is new, reaguiered or have churned. All i have is the customer number and what store he/she is connected to.

       

      I'we have found this video quite helpful Donor Retention, Churn and Other KPIs using Tableau - YouTube but it's now quite what i'm looking for.....

       

      Any thoughts/links/videos?


      /Andreas

        • 1. Re: Customer Churn with a subscription model
          Norbert Maijoor

          Hi Andreas,

           

          Not sure if my approach is applicable in your scenario but find it as reference below and stored in attached workbook version 10.3 located in the original thread.

           

          1. NM M1. countd week: {fixed: countd([week])}

              // count distinct weeks in dataset

           

          2. NM M2. Week/Customer: {fixed [Customer]:countd([week])}

              // count distinct weeks per Customer

           

          3. NM D1. Customer min week: {fixed [Customer]:max([week])}

              // define last week per customer

           

          4. NM D2. max week: {fixed :max([week])}

              // define last week in dataset

           

          5. NM D3. Status:

          if  [NM D1. Customer max week ]=[NM D2. max week ] and ({fixed [Customer]: sum([week])}=6 or {fixed [Customer]: sum([week])}=5) then "New customer, Churnes, Re-aquired customer"

          elseif [NM D1. Customer max week ]=[NM D2. max week ] then "New Customer"

          elseif [NM D1. Customer max week ]<[NM D2. max week ] then "Churned"

          END

           

          6. Drag the required objects to the indicated locations.

           

           

          Regards,

          Norbert

          • 2. Re: Customer Churn with a subscription model
            andreas långbergs

            Thank you for this, it's a musch cleaner way than i tried before.. But from what i can see this also makes a change backwards in time. I want to keep this as a status per customer but i also what to be able to se when a customer churned or the churn rate for that week was.

             

            From what i understand i need to set a status per week then, right?

            /Andreas