6 Replies Latest reply on Sep 6, 2017 12:47 AM by Anders Björling

    Subscription vs. overall retention rate

    Anders Björling

      I have an issue with calculating retention rates for magazine subscriptions. I've been searching the web but haven't found what I'm looking for so if anyone could help me, I'd be very happy.

       

      The problem is this. A company produce several magazines and they want to look at retention rates. A "retained subscriber" can be defined as a person who subscribes to one or more of a set of magazines in month M that still subscribes to one or more of the magazines (not necessarily the same one(s)!) in month M+1. So if you subscribe to magazine X in month M and not in month M+1, but you instead subscribe to magazine Y in month M+1, you are considered a retained subscriber.

       

      I'm trying to create a dashboard in which you can select one or more magazines and see the retention rate over time. I've attached a Tableau workbook with mockup data to explain a bit more what I mean. Had their only been one magazine, I would have been able to solve it, but since there are multiple I'm at a loss.

       

      The table I've read has three columns ID, date and magazine. The date column is in the granularity of months. The table contains all combinations of these three where a person has a subscription, i.e. if a person has a subscription from January to March with magazine 1 and from May to June with magazine 2, this person will have 3+2 rows in the table.

       

      If anyone could help me with this I'd be very happy. Thanks,

      /Anders

        • 1. Re: Subscription vs. overall retention rate
          Michael Someck

          Hi Anders,

           

          Happy to try to help! Can you give a little bit more information though? I'm slightly confused about what you want the end product to look like. If any subscription, regardless of which magazine, counts as retention, what does will it mean when you filter by by a specific magazine? For example, if I select M3, what should I be seeing in the Retention Rate graph at the bottom?

           

          Michael

          • 2. Re: Subscription vs. overall retention rate
            Anders Björling

            Thanks Michael,

             

            Thanks for getting back to me and sorry for not being clear.

             

            If you filter by M3, you should be seeing the retention rate for that magazine only. In this case, there is only one person, ID=1, that subscribed in months 1, 2, 3, 4 and 5 so you should be seeing a retention rate graph of 100% (Jan), 100% (Feb), 100% (Mar), 100% (Apr) and 0% (as ID4 did not continue his subscription in June).

             

            If you instead were to filter out M3 and M7 you would see a different result. For M7 we have a subscriber (ID=2) in January only, so we would then see the retention rates 50% (1 out of 2 January customers continued to subscribe to either M3 or M7 in February)), 100% (1/1), 100% (1/1), 100% (1/1), 0% (0/1).

             

            If no magazines are selected (i.e. all data is used), then the retention rate should be 75% (ID4 had a subscription in January but not in February), 100%, 100%, 75% (ID 2 dropped out) and 0%. I've updated the Excel file with some calculations.

             

            Thanks,

            Anders

            • 3. Re: Subscription vs. overall retention rate
              Tushar  More

              Hi Anders,

               

              Is this what you are looking for? If so, we can discuss how to get this.

               

              ~Tushar

              • 4. Re: Subscription vs. overall retention rate
                Anders Björling

                Hi Tushar,

                 

                Unfortunately, this is not what I'm looking for. Even if I summarize over dates, I don't get the right numbers.

                 

                As an example, none of the customers are retained in May as there are no data for June in the dataset. Also, ID 1 is not retained for 3 months, only 2. The customer subscribes to magazine M5 in March, April and May, which means he or she should be counted as retained in March and April.

                 

                I've attached a new version of the Excel sheet which shows the calculations in some more detail. You can change the value in cell H13 to check the results for different magazines.

                 

                Thanks,

                Anders

                • 5. Re: Subscription vs. overall retention rate
                  Michael Someck

                  Hi Anders,

                   

                  I'm sorry to say that I can't figure this one out. I've been trying to think of a way to solve this, and I've gotten nowhere. Doesn't mean it's not possible, though, and I'll keep thinking about it. Good luck!

                   

                  Michael

                  • 6. Re: Subscription vs. overall retention rate
                    Anders Björling

                    Hi Michael,

                     

                    I understand. I'm new Tableau so I thought it was just me, but maybe it's actually a quite tricky one. Thanks for trying!

                     

                    /Anders