4 Replies Latest reply: Nov 15, 2013 4:56 AM by Annika Palm RSS

    User Churn Chart

      Hi everyone!

       

      I'm new to the QlikView Community, so forgive me if this subject has been covered. I can't seem to find the answer I'm looking for.

       

      I'm trying to calculate a churn report by taking the amount of unique users that have logged on to our site during a 30 day period, and finding the amount of those that have logged on again in the next 30 day period. The catch is that I want to calculate this every week using WeekStart. For e.g.:

       

      5 unique users in the WeekStart-60 to WeekStart-30 range (a.k.a. "A"), 4 of those are still in the WeekStart-30 to WeekStart range (a.k.a. "B"), therefore churn would equal (A-B)/A or (5-4)/5 = 20%.

       

      Here's some test log in data to give you an idea of how the log file is structured:

       

      User_IDDate_Logged_On
      101/01/13
      201/01/13
      301/01/13
      401/01/13

      5

      01/01/13
      201/02/13
      301/02/13
      101/02/13
      501/02/13
      201/02/13

       

      I hope this make sense, it's been driving me crazy looking at ARRG() functions, Set Analysis, etc. Not sure what to use or how to use it.

       

      Thanks in advance for any help you can give me!

       

      Adam

        • Re: User Churn Chart
          Gysbert Wassenaar

          See attached qvw

            • Re: User Churn Chart

              Hi Gysbert,

               

              Thanks very much for the reply. Unfortunately I don't have the paid version of QlikView yet. I'm still waiting on approval and a budget to get it, which could take months. Could you please explain how you did it? Maybe with examples of the code?

               

              Sorry if it's a hassle, really appreciate it.

               

              Regards,

               

              Adam

                • Re: User Churn Chart
                  Stefan Wühl

                  I think there might be an issue with the suggested approach, because it does check that group B is a subset of A, instead it just compares the distinct count of users in both periods.

                   

                  For example, if you replace your second last line with:

                   

                  7, 01/02/13

                   

                  the distinct count won't change and the result is the same, -20%, while user 7 has not appeared in previous period yet and should be disregarded.

                   

                  I think you can potentially create a frontend only solution, but it will be probably very complex and resource extensive.

                   

                  If possible, try creating a solution in the script, e.g. you can check for User appearance in the requested periods in script already.

                  Maybe like this (you can probably optimize this a lot):

                   

                  SET DateFormat='DD/MM/YY';

                   

                  T1:

                  LOAD * INLINE [  

                      User_ID, Date_Logged_On

                      1, 01/01/13

                      2, 01/01/13

                      3, 01/01/13

                      4, 01/01/13

                      5, 01/01/13

                      2, 01/02/13

                      3, 01/02/13

                      1, 01/02/13

                      5, 01/02/13

                      2, 01/02/13

                  ];

                   

                  Let vStart  = num(makedate(2013,1,1));

                  Let vEnd     = num(makedate(2013,3,1));

                   

                  Churn:

                  LOAD Date(weekstart($(vStart),iterno()-1)) as ChurnRateDate,

                       Date(weekstart($(vStart),iterno()-1)-30) as Date30,

                       Date(weekstart($(vStart),iterno()-1)-60) as Date60

                  AutoGenerate 1

                  while weekstart($(vStart),iterno()-1) <= weekstart($(vEnd));

                     

                  JOIN LOAD distinct User_ID Resident T1;   

                     

                  Let vNumDates = NoOfRows('Churn');

                   

                  for i = 0 to vNumDates-1

                   

                  TMP1:

                      LOAD peek('ChurnRateDate',$(i),'Churn') as ChurnRateDate,

                           peek('User_ID',$(i),'Churn') as User_ID,

                           if(count(Date_Logged_On),1,0) as Flag30

                      Resident T1

                       where User_ID = peek('User_ID',$(i),'Churn')

                           and Date_Logged_On <= peek('ChurnRateDate',$(i),'Churn')

                          and Date_Logged_On > peek('Date30',$(i),'Churn');

                          

                  TMP2:

                      LOAD peek('ChurnRateDate',$(i),'Churn') as ChurnRateDate,

                           peek('User_ID',$(i),'Churn') as User_ID,

                           if(count(Date_Logged_On),1,0) as Flag60

                      resident T1

                        where User_ID = peek('User_ID',$(i),'Churn')

                            and Date_Logged_On <= peek('Date30',$(i),'Churn')

                            and Date_Logged_On > peek('Date60',$(i),'Churn');    

                   

                  next i

                   

                  Left Join (Churn) LOAD * Resident TMP1;

                  Left Join (Churn) LOAD * Resident TMP2;

                   

                  drop table TMP1;

                  drop table TMP2;

                   

                   

                   

                  Then, in the frontend, just create a straight table chart with dimension ChurnRateDate and as expression:

                   

                  = (count({<Flag30 = {1}, Flag60={1}>} User_ID) - count({<Flag60={1}>} User_ID))

                            / count({<Flag60={1}>} User_ID)

                   

                   

                  Hope this helps,

                  Stefan

                    • Re: Re: User Churn Chart
                      Annika Palm

                      Hi! I have a smiliar problem so i tag along here .

                       

                      I want to look at month for when a customer "churned". I have used your example, swuehl, and changed it so that i can se customer that has not bought anything in the past 12 moths. My problem is that if a customer bought at two different dates, that customer will have 2 churn dates. i only want to established when that customer first (or maybe last(?)) churned according to my definiton when looking at a time period.

                       

                      My problem is if i look at Orgno 1 you can se that it had activity 2012-01-04 and 2012-09-20 so when i look at a period of time this orgno "churns" at both 2013-01 and 2013-09 but since that customer has come back in a 12 month period the churn should only be at 2013-09. how can i solve this?

                       

                      Thanks in advance!