1 Reply Latest reply on Sep 17, 2018 2:34 PM by Patrick A Van Der Hyde

    Compute month over month connects(new customers) and disconnects (lost customers) for each product

    Rohit Bhatia

      Hello,

      I have about 20 odd products to which customers subscribe and unsubscribe. I have sample raw data below. The data is at month level.

       

      MONTH          PRODUCT_KEY      CUSTOMER_KEY      PRODUCT_DESC

      31-JUL-18       7000                          100085655                 ABC

      31-JUL-18       7000                          100089088                 ABC

      31-JUL-18       7001                          100089499                 BCD

      31-JUL-18       7001                          100090616                 BCD

      31-JUL-18       7002                          100095759                 CDE

      31-JUL-18       7002                          100097722                 CDE

       

      The expected output is

       

      Product_Key           Starting_Subs           Connects(new customers)           Disconnects(lost customers)           End_Subs

      7000                         2                              2                                                    0                                                      2

      7001                         5                              10                                                  0                                                      15

       

      starting subs = subscribers count (customer count) at the from the previous month. For example if I am looking for starting subs in the month of August 2018, then it is the count of customers for the month of July 2018.

      connects = number of subscribers not subscribed in the previous month and subscribed in the current month. For example if I am looking for connects in the month of August 2018, then it is the count of customers that were not subscribed in July 2018 but are subscribed in Aug 2018.

      Disconnects = number of subscribers, subscribed in the previous month and not subscribed in the current month. For example if I am looking for disconnects in the month of August 2018, then it is the count of customers that were  subscribed in July 2018 but are no longer subscribed in Aug 2018.

      end subs = subscribers count (customer count) for the given month.

       

      I am trying to get this tabular data also limited by some filters like customer_type and date range( as I am trying to produce this for a total of 25 months of data).

       

      Thank you.