Automating the Renewal Process (1 of 3)

Whether you are a subscription manager for a magazine or a membership manager for an association, your primary job is to attract new subscribers and/or members while retaining existing ones. The renewal process needs to be simple yet robust, with options to accommodate different subscriber/member types.

For sake of brevity, please take any reference to subscribers to mean either subscribers or members and subscription to mean either subscriptions or memberships. In the end, the process is the same for each type of renewal, whether in a subscription or membership database. (For information on subscription versus membership systems please refer to my article on the topic by clicking here.)

At the same time, the renewal process is not to be confused with the process of acquiring new subscribers. Attracting new subscribers is a different job than renewing existing ones. What is important to realize, however, is that new subscribers must be treated differently than existing subscribers. New subscribers are taking you for a spin to see if it is worthwhile continuing to subscribe. This progression is often called “New to Renew” and needs special attention as this group is where you are most likely to have the greatest retention falloff.

At the same time, consideration needs to be given to what offers you are going to provide for your different subscriber types. The point here is that one method of attempting to renew subscribers might not work equally well for all types. Whatever database you use, it must be flexible to provide targeted messaging to the different segments (types). To facilitate an integrated approach, you need to house demographic data to help you identify these different segments or types of your list. If you have offered a group of new subscribers a deep discount on the subscription rate, jumping them to a full rate for their first renewal might scare them off. For this reason, your renewal database should be flexible enough to offer different pricing options to different segments. In the end, however, you want to run one simple process to cover all the different options you have for renewals, which is where flexible relational database design is used.

The starting point is a subscribers table (tblSubscribers which could also be called tblMembers). In it is held the basic information on the individual: name and address, preferred salutation, plus demographic data specific to them, such as birthdate and/or gender (or in today’s environment, preferred pronouns). Attached to the subscribers table is a subscriptions table, where one record for each subscription individual subscribers have is stored. There might be individuals in the subscribers table who hold no records in the subscriptions table, as would be the case for records created for promotional or targeting reasons.

To facilitate billing to different addresses, as would be defined by different ship-to and bill-to locations, the subscriptions table holds two pointers to the subscriber table. One is the subscriber ID (nID in the diagram above) and a second, which we’ll call Donor ID (DonorID), points back to the billing address. The second field could easily be called Bill to ID (Bill2ID), but in the magazine industry gift subscriptions can be a significant selling point, with the gift giver traditionally being called a donor. A donor might have their own subscription, so thus a need to store them in the subscribers’ table. If a given subscriber pays for the subscription themselves, then the subscriber ID and Donor ID fields are the same value (nID = DonorID).

The subscriptions table holds the start and end of the individual subscription, with flags to accommodate processing and demographic fields related to the subscriber information that might change over time, such as job functions, source of the subscription, size of company and/or family, and other related categories that will be specific to the magazine and of interest to its advertisers. Two additional fields are needed to automate the renewal process, one being a flag to indicate that they are to be renewed (let’s simply call this field Renewal and is turned off when the circulation manager is informed that the subscriber will not be renewing) and one to hold the renewal series code (RenID).

There is in fact a recommended third field which should be included in the subscriptions table, a renewal attempts count field (RenewalAttempts). This is not absolutely necessary as it can be calculated by querying the renewal attempts table (see information below), but it provides some added benefits to be discussed in the third and final installment of this Automating Renewals series, which will be on reporting and processing. It does, though, require added processing to keep the counter correctly updated.

Each record within the subscription table is assigned to a renewal series, that being defined in the Renewal Series table (tblRenewalSeries). The renewal series table in turn has associated with it one or more sets of messages for each attempt at renewing, including price offers, as well as defining how that attempt will be executed, whether by e-mail, traditional-mail, or an alternative means such as courier or telemarketing. In a perfect world, it would only take one renewal attempt to have subscribers re-up for a subscription. In the real world, it can take three or more attempts to have an individual renew their subscription. Tracking these efforts is what the Renewal Attempts table (tblRenewalAttempts) is for. In the ERD (Entity Relationship Diagram) above note that this table points to both the Subscriptions and the Renewal Series tables; but you ask: Why, since the Subscriptions table has an existing link to the Renewal Series table? The answer is that this design allows you to alter the renewal code if a new series is needed mid-renewal process. The best example of this is when you are trying to renew to a donor who no longer wishes to pay for someone else’s subscription, so you then switch to try to have the giftee renew directly, requiring a new set of renewal efforts.

The major advantage of the Renewal Attempts table is for tracking successful retention programs. From it you can see how many efforts are needed on average, and segment that information based on the demographic fields you have chosen to track in the database. More on this type of reporting in a future article.

In looking at the ERD above, you will note that there is a recursive pointer on the Renewal Series table, which will be called for discussion purposes Next Series. This is used for setting the next renewal message in a series once a renewal has been achieved on the existing renewal series. The idea is to look at the lifetime of a subscriber and plan out a series of renewal messages that change with the number of renewals a particular subscriber would have. As a new renewal is made, the system would lookup the previous renewal code and then assign the Next Series code to the new subscription. If the Next Series is to be the same as the existing one, meaning it would point to itself, you are using the same renewal messages over and over. Using the Next Series pointing to a different Renewal Series is intended to build upon previous renewals with complementary messaging.

BUT

Unlike other articles in our T-SQL Solutions series, there has been no code provided here, which is intentional. All good T-SQL code starts with good database design. If you do not have accurately anticipated what the needs are of the end database user and build in those considerations into the design, writing T-SQL to pull something as simple as a list may be difficult.

The goal is to write one stored procedure which will deliver, with as few input options as possible, a list for renewals regardless of where individuals are in the renewal process. That stored procedure is the focus of the next article in this series which can be seen here, followed by an article on reporting renewal success and failures.

If you have any questions, please feel free to post them on my LinkedIn page.