1 Reply Latest reply on Oct 14, 2015 9:54 AM by kettan

    Business Retention

    David Marrel

      I'm trying to do a business retention analysis, in this case on auto insurance policies.  I've had help and made some progress, but I never seem to be able to get the final desired report.

       

      My data set is simple:  Policy ID, Date the Policy Started, and Cancel Date (if not active)/Expiration Date (if still active).

       

      It's easy enough to look at policies written 3 years ago and see what % are still active today, but I want to see what % were active after 1 month, after 2 months, ..., after 3 years.

       

      Also a policy written, say 3 months ago, I don't want it included when determining what % are active in 3 years.  Of course it's not been active for 3 years because that's impossible.

       

      So attached is what I have so far.  I have 1 viz showing % of policies still active after every 30 days.  The other viz shows how many days it could have possibly be active. For example, if a policy was written 3 years ago, but cancelled after 2 months, it COULD have been active for 3 years.

       

      Basically the number I'm looking for is the bin value in viz 1 divided by the bin value in viz 2.  For example, For 150 days, 49/86 for 57%.

       

      I'm not married to using bins for this.  I'm not even married to the data set I'm using if I re-pull it a different way.  In my original data set, what I don't have easily available  is a list of active policies each month.  So that solution is not an option.

       

      Side note:  I can't get 0-30 days to populate with 100 on the Days Possible viz because I don't have any policies written <30 days ago so it's not considering the starting point a missing value.

       

      Thanks in advance for your help.

        • 1. Re: Business Retention
          kettan

          MULTIPLY ROWS

          I'm not married to using bins for this.  I'm not even married to the data set I'm using if I re-pull it a different way.  In my original data set, what I don't have easily available  is a list of active policies each month.  So that solution is not an option.

          Method 1 in  The Cross Join Collection  could take care of that.

          In this regard, you might also find valuable info in  FAQ: Open & Close Dates.

           

          A cross join would also make it possible to have multiple instances of the same policy, namely one row per time box as it fits as possible/active. The little difference is that this will numerically band days rather than calendar periods.

           

          MULTIPLE FLAGS

          That said, multiple calculations with 0/1 flags might be enough. See more in attached workbook.

           

          YOUR DATA SOURCE?

          What is your original data source? Excel, Text, SQL Server, Oracle, PostgreSQL or something else?

          In case you already work with exported data in Excel and or Text, then knowing source is less important.

          If you have many policies, Excel (Jet SQL) will probably be very slow and therefore preferable to run the cross join query in assumed relational source database.

           

          Attached Workbook Version:  8.3

          .