You have designed your database to incorporate a renewal process that is targeted to different segments based on specific renewal series, which was outlined in the first of the three articles in this series. You have run multi-faceted renewals based on one command needing only a few parameters to generate renewals regardless of where in the process individuals are, thus providing a degree of automation, as outlined in the second article of the series. Now it is time to generate a retention report to see how successful your renewal efforts were.

Whether you are running renewals for magazine subscribers or association members, the report process itself is similar. As before in the series, the discussion focuses primarily on subscribers for simplicity’s sake, but applies to membership systems as well (for discussion on the differences between the two types of system click here). Based on your renewal codes, which represent the different renewal series you are using, you want to know:

To keep this discussion simple, and hopefully therefore, clear, here’s the T-SQL command to return the desired data for a basic retention report:

There are two SELECT commands, the first being a common table expression (CTE) to prepare the data to summarize. In the CTE (cteOrderedSubs2) there are two T-SQL WINDOWs instructions: LAG() and LEAD(). These are used to obtain, first, the previous end issue number relative to the current record (LAG(EndIssue) as PreviousEnd) and, then, the following start issue number (LEAD(StartIssue) as NextStart). Prior to the introduction of these two WINDOWs functions to SQL Server 2012, you would have had to use a double linked list method to easily accomplish the same functionality. For those uninitiated, a double linked list has two extra fields, one with the value of the primary key of the previous records in the series and one with the value of the next primary key. Besides the obvious added overhead of extra fields to accommodate this functionality, the additional burden of maintaining correct sequencing due to adds, edits and deletes of related records over time makes double-linked lists a cumbersome option.

A WINDOW sub-command declared in the CTE groups data by the subscriber ID (Partition By nID) then provides instruction to order the output by the End Issue (Order by EndIssue) number. The first record in the partition group will not be able to pull a previous value for the end issue, so it will be null. Likewise, the last record in the grouping (Partition) will not have an assigned NextStart value, and thus too will be null. This fact can be used to determine the original subscription and the last subscription for a given individual. Using this method, the data can identify new subscriptions that have renewed (NewSubRenewed), new subscriptions that have not renewed (NewSubNotRenewed), existing subscriptions that have renewed (ExistingSubRenewed) and existing subscriptions that have not renewed (ExistingSubNotRenewed).

Always Verify the Calculations

These fields are summarized in the final SELECT command with a GROUP BY clause from the data prepared in the CTE. As a side note, a simple COUNT (Qty) value has been added to provide a total of all the records processed for each record, which is used to verify the calculations. If the four preceding columns using SUM do not total to the Qty amount, then there is something wrong with the logic in the Inline IFs (IIF()) used to derive the values in question. Once the logic is verified to be correct, the COUNT column can be dropped as it will always be the total of the four respective values calculated.

This command will give you a list by renewal codes and the attempts taken for each so that the efficiency of the different retention efforts can be gauged. Further, it allows for comparison of retention rates of new subscribers/members against existing ones. This information provides for a simple retention-rate report.

Two additional fields have been added to the final SELECT for discussion purposes, specifically the NonContinuous and RenewedNonContinious values. A subscriber or member can lapse their subscription or membership but return later. If the subscription has a prior value, but that value does not equal the current start issue less one, then the subscription is said to be non-continuous. So too, if the subsequent subscription has a start issue value but does not match the current subscription’s end issue plus one, then the renewal is non-continuous. If this is true for both comparisons, you have a data island in terms of retention, meaning a non-continuous retention at both ends of a subscription/membership period.

Large number of non-continuous retention for members or subscribers can point to issues with the renewal process. These issues might be inconsistent renewal strategy execution or timing issues, such as renewal efforts that are executed later than is optimal for continuous retention of subscribers/members. A future article will expand upon the use of non-continuous data island analysis for retention reporting.

But

This retention calculation looks at the renewal efforts (Renewal_Code) and attempts (Ren_Attempt), but as such is only part of a larger retention report. Other factors, such as source or age, can be used to give a better understanding of retention issues. As an example, if you have a high drop-off of subscribers/members from a given source, such as list purchased from a third party, then you might want to use another source in the future. Or, if you have a low retention rate for individuals of retirement age, you might want to consider aging-out as a factor for your retention model, and thus the type of messaging to try to hold on to individuals.

While a CTE was used to prepare the data for summary reporting, if this is something which is used for a range of reports, consider using a VIEW instead to ensure consistency across report SELECT commands. If you do go with a VIEW, add the primary key field and any important foreign key fields to allow for easy retrieval of supporting data from the related tables.

This concludes the three-part series on renewals within a subscription or membership database system. If you have any questions or comments, please feel free to post them on my LinkedIn page. As well, follow me on LinkedIn to receive notifications of future T-SQL Solution articles.