1 Reply Latest reply: Nov 28, 2016 12:28 PM by Gysbert Wassenaar RSS

    Unsubscribe rate from week subscription model

    Tomy Blondeau

      Hi everyone,
      I have a subscription model by week (each week, user pay).

       

      Here is my data strcture:

       

      Got a billing tab with multiple lines. Each line equals one billing action. Here is all the fields needed in my problem:
      for each line:
      billing_id: billing identifier (primary k)
      subscriber_id = referencing a subscriber from another table (foreign key)

      billing_date = date when the payment was made
      creation_date = date when the subscriber subscribed. It means that the first billing action for a subscriber was made at this date.
      Then inside Qlik Sense I make a Pivot table with on line weekname(creation_date) and column weekname(billing_date)
      this gives me a cohort tab by week. Which means for all weeks we group all subscribers that subscribed on the same week, and then we look for every week how much billing has been made (using the count(billing_id)). This gives us some lifetime. For example (from the picture below)

       

      On the Week number 13 of 2016
      43 people subscribed
      On the week after,  14 people got billed. it means that 14/43 people are still subscribed.
      On the week after,  12 people got billed. It means taht 12/43 people are still subscribed

       

      What I want is a representation in pourcentages of this tab, for the same example I used just before:
      On the week number 13 of 2016:
      100% people subscribed
      On the week after, 14 people got billed, it means that 32% people are still subscribed
      On the week after, 12 people got billed, it means that 27% people are still subscribed.
      But I cannot achieve this !
      Thanks in advance and sorry for my english.
      Here is a piece of the tab:
      Qlik.png