Automating the Renewal Process (2 of 3)

This is the second of three articles related to automating the renewal process for either membership-based or subscription-based systems. The first installment was on the structures needed to enable the SELECT command to be reviewed shortly and the reporting requirements to be reviewed in the final installment. For those arriving to this page who have yet to see the first installment, please stop now and click here to read it.

New to Renew - 2 of 3

As with the previous article in the series, this discussion will focus on subscription-based systems for magazine publishers, where there is a start and end issue number for each subscription a subscriber purchases. The example SELECT command presented below would need to be modified to be based on the end membership date instead of a number. (For discussion on the differences between subscription and membership systems click here.)

For simplicity, this discussion provides two statements, the first being the declaration of variables used for the SELECT command, and the second being a two-part SELECT command utilizing a custom table equation (CTE) and then the main SELECT command. In an actual production version this functionality would be provided inside of a stored procedure (sproc), with the variable declarations being the parameters and the SELECT command in the body of the sproc along with other coding, such as validation tests for the variables being passed in.

Here is the sample code for discussion purposes:

DECLARE @cCurPub as VARCHAR(4) = 'NL' -- Publication Code
         , @nCurIssueNo as INTEGER = 144 -- Current publication issue number
         , @cDeliveryMode VARCHAR(4) = 'EM' -- Short for E-Mail
         , @nAttempt as SMALLINT = 1 -- Attempt to increment messages counter
         , @dAttemptDate as DATE = GETDATE() -- Current renewal attempt date
         ;

WITH cteMax_Issues (nID, LastEnd) as
(SELECT nID, MAX(EndIssue)
     FROM dbo.tblSubscriptions 
     WHERE SubList = 'AS' AND Pub = @cCurPub
     GROUP BY nID)
SELECT t1.nID, t1.Salute, t1.FirstName, t1.LastName, t1.Title, t1.Company,
          t1.MailStop, t1.cAddress, t1.City, t1.cState, t1.ZipCode,
          t1.Country, t1.Phone, t1.EMail, t1.UseEMail,
     t2.SubID, t2.DonorID, t2.StartIssue, t2.EndIssue, t2.Renew_Code, t2.Ren_Attemp, 
          t2.Renew_Date, (t2.Ren_Attempts + @nAttempt) as R_Attempts,
     b2.nID as Bill2ID, b2.Salute as Bill2Salute, b2.FirstName as Bill2FirstName,
          b2.LastName as Bill2LastName, b2.Title as Bill2Title,
          b2.Company as Bill2Company, b2.MailStop as Bill2MailStop,
          b2.cAddress as Bill2Address, b2.City as Bill2City, b2.cState as Bill2State,
          b2.ZipCode as Bill2ZipCode,b2.Country as Bill2Country, b2.Phone as Bill2Phone,
          b2.EMail as Bill2EMail, b2.UseEMail as Bill2UseEMail,
     r2.TopMessage, r2.BottomMessage, r2.SubOffer
FROM tblSubscribers as t1
     INNER JOIN dbo.tblSubscriptions as t2 ON t1.nID = t2.nID
     INNER JOIN cteMax_Issues as m1 ON m1.nID = t2.nID AND t2.EndIssue = m1.LastEnd
     INNER JOIN dbo.tblSubscribers as b2 ON b2.nID = t2.DonorID
     INNER JOIN dbo.tblRenewalSeries as r1 ON r1.RenCode = t2.Renew_code AND t2.pub = r1.RenPub
     INNER JOIN dbo.tblRenewalMsg as r2 ON r2.SeriesID = r1.SeriesID
WHERE t2.SubList = 'AS' AND t2.cStatus = 'PC' AND t2.Renewal = 1 AND
          t2.Pub = @cCurPub AND r2.DeliveryMode = @cDeliveryMode AND
          (t2.EndIssue - @nCurIssueNo) < r1.Start_Ren AND
          t2.Ren_Attempts < r1.Max_Attemp AND
          r2.Attempt = t2.Ren_Attempts + @nAttempt AND
          ISNULL(t2.Renew_Date,'1900-01-01') < DATEADD(DAY,-r2.DaysBefore, @dAttemptDate)
ORDER BY t2.DonorID, t2.EndIssue DESC

The first element of the example is the declaration of the variables that will be used in the commands provided. These are, in order of appearance:

  1. The publication code needed as the system is designed to handle multiple magazines, so the specific one to produce a list for must be specified. In a membership system, this might be omitted if there was only one association. For association management companies (AMC) that handle multiple associations, this could be the specific association needing renewal.

  2. Next is the current issue number, which is the next issue to be mailed, and as such sets the starting point for calculation of whether the subscription is due to be renewed. In a production version, this is an optional parameter as the current issue number can be selected from the issues table. For membership systems this would be a date value and set into the future as you want to renew members before their membership runs out. The calculation of a membership expiration date would also be calculated in a production system making this parameter optional as well.

  3. The third variable is the delivery mode, or rather, an indicator as to how this set of renewals will be delivered to the subscriber (or member in a membership system). Our example is set to ‘EM’ which is intended to be short for e-mail delivery. The previous article listed several different possibilities, but here only one mode is being shown. For each different delivery mode, this command would need to be run with the corresponding code.

  4. The fourth variable is an optional one, which is intended to be the increment counter for the renewal effort. It will be set to one (1) for all but special circumstances, such as re-running renewal attempts when it would be set to zero (0).

  5. The final variable is the date of the attempt to renew subscriptions, which is by default the date the process is run but can be set to a later date if the list is being prepared for distribution further down the road.

The second statement in the example utilizes these variables to pull the necessary records from the database. It starts with a WITH command that creates a CTE of all the unique subscriber IDs and the last issue number they have an active subscription to (SubList = 'AS') for the publication in question (@cCurPub). We want to always limit the renewal list pulled to the very last valid subscription, so the CTE is used to condition that list for the follow-up SELECT command.

The main SELECT command pulls the data together from the CTE and four other tables, one of which is used twice. In order of appearance, here is the list in the FROM clause:

  1. Table one (t1) is the subscribers list which holds the mail to address for the delivery of magazines.

  2. Table two (t2) is the subscriptions list which holds all the subscriptions across different subscribers and publications.

  3. The CTE is the third listed item (m1) where the maximum calculated issue for each subscriber determines what subscription records are pulled based on last end issue number for each ID. The ON clause here adds a second parameter (t2.EndIssue = m1.LastEnd) to ensure that only the last active subscription record for each ID in question is selected.

  4. In fourth spot is the subscribers table again, which is used to pull the bill to addresses (b2) based on the DonorID value of the subscriptions table. Remember that, as covered in the previous installment of this series, if the subscriber ID (nID) of the subscriptions table is the same value as the DonorID of that record, the subscriber pays for the subscriptions themselves. Note that 'Bill2' is added in front of the field names to differentiate these from the mail to fields.

  5. The fifth table call in the list is to the renewal series header (r1) table. This defines the major renewal parameters, which are used to assign specific messaging and timing to a given subscription renewal. The subscriptions table holds a renewal code which ties to the renewal series table and is publication dependent, meaning that different publications might have the same code but be structured differently with different messaging.

  6. The final table is the renewal messages which stores the specific messages used by attempt and define the number of days between which the renewal efforts are separated.

This is the basic structure pulling in the tables that were discussed in the first of this series’ articles. The main work to filter out the data is in the WHERE clause, as follows:

  1. The first four fields in the WHERE clause are from the subscriptions table limiting our selection of data to those that are active subscriptions (AS), classified as paid continuous (PC) which have the renewal flag set on (1) and are for the publication in question (@cCurPub).

  2. The fifth element of the where clause references the delivery mode from the renewal messages table (r2.DeliveryMode = @cDeliveryMode) to ensure only one type of delivery is being pulled at a time.

  3. The sixth condition might not be as obvious as to what it accomplishes in the filter but is important to limit the renewal pulls to those subscriptions that are at or below the number of remaining issues which triggers their inclusion in the list. This is accomplished by taking the end issue number of the subscription then subtracting from it the current issue number to see if it is less then the number of remaining issues to start renewing as set in the renewal series table ((t2.EndIssue - @nCurIssueNo) < r1.Start_Ren).

  4. The seventh limits the data pulled to those subscriptions which have not yet maxed out on the number of attempts set in the renewal series (t2.Ren_Attempts < r1.Max_Attemp). In order to do this correctly, the attempt counter field (t2.Ren_Attempts) must be incremented after the selection of the data.

  5. The eighth condition ensures that only the correct renewal attempt messages are pulled based on the previous pulled message plus the current attempt (r2.Attempts = t2.Ren_Attemp + @nAttempt), which is usually 1, meaning the next one in the series.

  6. The final condition is intended to ensure the days set between the attempts, as set in the DaysBefore field, are adhered to. This is done by establishing that the last renewal date (defaulting to 01/01/1900 if not set) is less than the date for the renewal attempt minus the number of days set for the interval between attempts (DATEADD(DAY, -r2.DaysBefore, @dAttemptDate)). For this to work, the last renewal date field (t2.Renew_Date) must be updated at the same time the renewal attempts field is (t2.Ren_Attemp) in the subscriptions table.

Using this technique, it doesn’t matter where in the renewal process a subscriber is or how many days it was from when an attempt was made to renew them, they will receive the correct messaging at the correct time. You do not have to worry about breaking down the selection of the data based on different issues, attempts and intervals, as it is all calculated automatically when you run this one process.

Note that while the maximum attempts (r1.Max_Attemp) could be calculated based on the total number of attempts or maximum attempt number in the related renewal messages, structuring it as outlined allows for extra capabilities. For example, if a specific renewal series needs to be suspended for special processing later, the maximum attempts can be set to zero (0) to disable it. Or, if the last attempt in a series needs to be disable for separate processing, the maximum attempts can be reduced by one (1) to place it on hold then set back at the appropriate time to remove the hold.

But

This process does require that the data settings are correctly populated, which means in turn accurate updates and stored history. For this reason, you must have strong built-in business rules enforced at the database level with the ability to crosscheck the data. A history of renewal mailings is an important element of ensuring correct data validation. As mentioned before, this process is best placed in a sproc with extra code for validation, error trapping and tracking.

While the example in this is for one integrated approach, normally, when these systems are written there are two renewal processes, one for individuals paying for themselves and one where there is a second organization or individual paying for the subscription or membership. The first is called regular renewal and the second is donor or bill-to renewals. The reason for this separation is that the renewal forms look very different as donor renewals may list two or more individuals being renewed. Regular renewals do not need this extra list, and as such do not need the second lookup to the Subscribers table or the bill-to fields (starting with b2.Bill2 in the example).

The articles in this series are intended to be short suggestions for more technically inclined individuals in the subscription or membership database areas. If you have any questions, please feel free to post them on my LinkedIn page. In the third piece, I cover retention reporting on the success of the renewal process to avoid missing renewal opportunities, which can be found here.