Preserving Newsletter Subscribers When Upgrading From Kentico 9
It’s been almost 2 years since I wrote the “An Approach to Preserving Newsletter Subscribers When Upgrading to Kentico 10”. That approach worked, but we have found a better process to upgrade from Kentico 9.

Refresher Points

As stated in my last post on this, starting with Kentico 10, Newsletter Subscribers are Contact Based. Because of that, unless steps are taken to preserve subscribers, they will be lost during the upgrade to Kentico 10.

Difference between approaches

My last approach consisted entirely of SQL Scripts. Using SQL Scripts to alter data within Kentico is something that should be done with caution as table relationships can be tricky and this is prone to error. My new approach does not use SQL to alter any data and instead makes use of tools within the Kentico Admin.

Before the Kentico Upgrade create CSV

The only step necessary to do before the Kentico upgrade is to create a CSV of current subscribers and what newsletter they are subscribed to. This is the only step that involves SQL which is used to gather data as opposed to altering data. In this example, I will query sample data from the Dancing Goat template site.

This query can be broken into 3 parts. The first of which is to store the Newsletter IDs as variables. 

Newsletter ID Variables

In the Kentico Dancing Goat template, there are 5 Newsletters. To get the Newsletter ID you first need the Newsletter which you can get by clicking the Edit Pin and going to Configuration.

Example of how to edit a newsletter
How to edit Newsletter ID

Declaring the variable needs to be done in the following format:
DECLARE @NewsletterName INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = ‘NewsletterName’)
Example:
DECLARE @Coffee101 INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = ‘Coffee101’)
Example:
DECLARE @Coffee101 INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = ‘Coffee101’) Dancing Goat template variables should look like this: DECLARE @Coffee101 INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'Coffee101') DECLARE @CoffeeClubMembership INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'CoffeeClubMembership') DECLARE @ColombiaCoffeePromotion INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'ColombiaCoffeePromotion') DECLARE @ColombiaCoffeeSamplePromotion INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'ColombiaCoffeeSamplePromotion') DECLARE @DancingGoatNewsletter INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'DancingGoatNewsletter'

Select Statement

The select statement is a little tricky since you need to LEFT JOIN on the Users table and each Kentico Newsletter and this involves Case statements to determine if the subscription is approved. In this blog post, we are going to ignore subscriptions that have not been approved.

This will pull up all subscribers that are either backed by a Kentico user, or only a subscriber and are not related to any other Kentico object.
SELECT 
        CASE WHEN SubscriberEmail IS NOT NULL
            THEN SubscriberEmail
        WHEN SubscriberType = 'cms.user' 
            THEN Email
        ELSE NULL
        END AS SubscriberEmail        
FROM Newsletter_Subscriber sub    
LEFT JOIN CMS_User usr ON sub.SubscriberRelatedID = usr.UserID AND sub.SubscriberType = 'cms.user'
WHERE SubscriberType IS NULL OR SubscriberType = 'cms.user'

This only gets the email, but not the Newsletters they are subscribed to, or their subscriber status. To get their subscriber status you need to LEFT JOIN the subscriber tables by the Subscriber ID and the subscription being approved. With the dancing Goat newsletters, it will look like this:

LEFT JOIN Newsletter_SubscriberNewsletter Coffee101 ON sub.SubscriberID = Coffee101.SubscriberID AND Coffee101.NewsletterID = @Coffee101 AND Coffee101.SubscriptionApproved = 1
LEFT JOIN Newsletter_SubscriberNewsletter CoffeeClubMembership ON sub.SubscriberID = CoffeeClubMembership.SubscriberID AND CoffeeClubMembership.NewsletterID = @CoffeeClubMembership AND CoffeeClubMembership.SubscriptionApproved = 1
LEFT JOIN Newsletter_SubscriberNewsletter ColombiaCoffeePromotion ON sub.SubscriberID = ColombiaCoffeePromotion.SubscriberID AND ColombiaCoffeePromotion.NewsletterID = @ColombiaCoffeePromotion AND ColombiaCoffeePromotion.SubscriptionApproved = 1
LEFT JOIN Newsletter_SubscriberNewsletter ColombiaCoffeeSamplePromotion ON sub.SubscriberID = ColombiaCoffeeSamplePromotion.SubscriberID AND ColombiaCoffeeSamplePromotion.NewsletterID = @ColombiaCoffeeSamplePromotion AND ColombiaCoffeeSamplePromotion.SubscriptionApproved = 1
LEFT JOIN Newsletter_SubscriberNewsletter DancingGoatNewsletter ON sub.SubscriberID = DancingGoatNewsletter.SubscriberID AND DancingGoatNewsletter.NewsletterID = @DancingGoatNewsletter AND DancingGoatNewsletter.SubscriptionApproved = 1

With that, you now need to select the subscriber status of each newsletter in a way that will return 0 if not subscribed to that newsletter, and 1 if subscribed. 
        ,CASE WHEN Coffee101.SubscriptionApproved IS NOT NULL AND Coffee101.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS Coffee101                    
        ,CASE WHEN CoffeeClubMembership.SubscriptionApproved IS NOT NULL AND CoffeeClubMembership.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS CoffeeClubMembership    
        ,CASE WHEN ColombiaCoffeePromotion.SubscriptionApproved IS NOT NULL AND ColombiaCoffeePromotion.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS ColombiaCoffeePromotion            
        ,CASE WHEN ColombiaCoffeeSamplePromotion.SubscriptionApproved IS NOT NULL AND ColombiaCoffeeSamplePromotion.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS ColombiaCoffeeSamplePromotion
        ,CASE WHEN DancingGoatNewsletter.SubscriptionApproved IS NOT NULL AND DancingGoatNewsletter.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS DancingGoatNewsletter    

Where Clause

Now we need to write the WHERE clause to get the relevant data we want. In this case, it is Kentico Newsletter subscribers that have an approved subscription and are either backed by users or have no backing (om.contact type subscribers will already be preserved).
WHERE (SubscriberType IS NULL OR SubscriberType = 'cms.user') AND

(

    (sub.SubscriberID = Coffee101.SubscriberID AND Coffee101.NewsletterID = @Coffee101 and Coffee101.SubscriptionApproved = 1)
    OR
    (sub.SubscriberID = CoffeeClubMembership.SubscriberID AND CoffeeClubMembership.NewsletterID = @CoffeeClubMembership and CoffeeClubMembership.SubscriptionApproved = 1)
    OR
    (sub.SubscriberID = ColombiaCoffeePromotion.SubscriberID AND ColombiaCoffeePromotion.NewsletterID = @ColombiaCoffeePromotion and ColombiaCoffeePromotion.SubscriptionApproved = 1)
    OR
    (sub.SubscriberID = ColombiaCoffeeSamplePromotion.SubscriberID AND ColombiaCoffeeSamplePromotion.NewsletterID = @ColombiaCoffeeSamplePromotion and ColombiaCoffeeSamplePromotion.SubscriptionApproved = 1)
    OR
    (sub.SubscriberID = DancingGoatNewsletter.SubscriberID AND DancingGoatNewsletter.NewsletterID = @DancingGoatNewsletter and DancingGoatNewsletter.SubscriptionApproved = 1)
)
Overall the query should look like this:
DECLARE @Coffee101 INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'Coffee101')
DECLARE @CoffeeClubMembership INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'CoffeeClubMembership')
DECLARE @ColombiaCoffeePromotion INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'ColombiaCoffeePromotion')
DECLARE @ColombiaCoffeeSamplePromotion INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'ColombiaCoffeeSamplePromotion')
DECLARE @DancingGoatNewsletter INTEGER = (SELECT NewsletterID FROM Newsletter_Newsletter WHERE NewsletterName = 'DancingGoatNewsletter')

SELECT 
        CASE WHEN SubscriberEmail IS NOT NULL
            THEN SubscriberEmail
        WHEN SubscriberType = 'cms.user' 
            THEN Email
        ELSE NULL
        END AS SubscriberEmail    
        ,CASE WHEN Coffee101.SubscriptionApproved IS NOT NULL AND Coffee101.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS Coffee101                    
        ,CASE WHEN CoffeeClubMembership.SubscriptionApproved IS NOT NULL AND CoffeeClubMembership.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS CoffeeClubMembership    
        ,CASE WHEN ColombiaCoffeePromotion.SubscriptionApproved IS NOT NULL AND ColombiaCoffeePromotion.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS ColombiaCoffeePromotion            
        ,CASE WHEN ColombiaCoffeeSamplePromotion.SubscriptionApproved IS NOT NULL AND ColombiaCoffeeSamplePromotion.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS ColombiaCoffeeSamplePromotion
        ,CASE WHEN DancingGoatNewsletter.SubscriptionApproved IS NOT NULL AND DancingGoatNewsletter.SubscriptionApproved = 1
            THEN 1
            ELSE 0
        END AS DancingGoatNewsletter    
FROM Newsletter_Subscriber sub    
LEFT JOIN CMS_User usr ON sub.SubscriberRelatedID = usr.UserID AND sub.SubscriberType = 'cms.user'
LEFT JOIN Newsletter_SubscriberNewsletter Coffee101 ON sub.SubscriberID = Coffee101.SubscriberID AND Coffee101.NewsletterID = @Coffee101 AND Coffee101.SubscriptionApproved = 1
LEFT JOIN Newsletter_SubscriberNewsletter CoffeeClubMembership ON sub.SubscriberID = CoffeeClubMembership.SubscriberID AND CoffeeClubMembership.NewsletterID = @CoffeeClubMembership AND CoffeeClubMembership.SubscriptionApproved = 1
LEFT JOIN Newsletter_SubscriberNewsletter ColombiaCoffeePromotion ON sub.SubscriberID = ColombiaCoffeePromotion.SubscriberID AND ColombiaCoffeePromotion.NewsletterID = @ColombiaCoffeePromotion AND ColombiaCoffeePromotion.SubscriptionApproved = 1
LEFT JOIN Newsletter_SubscriberNewsletter ColombiaCoffeeSamplePromotion ON sub.SubscriberID = ColombiaCoffeeSamplePromotion.SubscriberID AND ColombiaCoffeeSamplePromotion.NewsletterID = @ColombiaCoffeeSamplePromotion AND ColombiaCoffeeSamplePromotion.SubscriptionApproved = 1
LEFT JOIN Newsletter_SubscriberNewsletter DancingGoatNewsletter ON sub.SubscriberID = DancingGoatNewsletter.SubscriberID AND DancingGoatNewsletter.NewsletterID = @DancingGoatNewsletter AND DancingGoatNewsletter.SubscriptionApproved = 1

WHERE (SubscriberType IS NULL OR SubscriberType = 'cms.user') AND
(
    (sub.SubscriberID = Coffee101.SubscriberID AND Coffee101.NewsletterID = @Coffee101 and Coffee101.SubscriptionApproved = 1)
    OR
    (sub.SubscriberID = CoffeeClubMembership.SubscriberID AND CoffeeClubMembership.NewsletterID = @CoffeeClubMembership and CoffeeClubMembership.SubscriptionApproved = 1)
    OR
    (sub.SubscriberID = ColombiaCoffeePromotion.SubscriberID AND ColombiaCoffeePromotion.NewsletterID = @ColombiaCoffeePromotion and ColombiaCoffeePromotion.SubscriptionApproved = 1)
    OR
    (sub.SubscriberID = ColombiaCoffeeSamplePromotion.SubscriberID AND ColombiaCoffeeSamplePromotion.NewsletterID = @ColombiaCoffeeSamplePromotion and ColombiaCoffeeSamplePromotion.SubscriptionApproved = 1)
    OR
    (sub.SubscriberID = DancingGoatNewsletter.SubscriberID AND DancingGoatNewsletter.NewsletterID = @DancingGoatNewsletter and DancingGoatNewsletter.SubscriptionApproved = 1)
)
That will output results that look like this. Right-click the top left corner and choose “Save Results As…”

Example of Output Results

Save it as a CSV.

Open the CSV.  Notice how that CSV has no headers. Add headers to the top of that start with SubscriberEmail and the different newsletters and save. The header for this example will look like this:
SubscriberEmail,Coffee101,CoffeeClubMembership,ColombiaCoffeePromotion,ColombiaCoffeeSamplePromotion,DancingGoatNewsletter

After the Kentico Upgrade

Before the upgrade, we saved all the subscribers and their subscriptions in a CSV.  The next steps cover how to import the subscribers into Kentico.

Add Category and Columns to Contact Class

In the Kentico Admin Interface, go to Modules -> Contact Management -> Classes -> Contact management - Contact -> Fields

At the bottom of the fields, add a new category called “Custom”.  Within that category create new Integer fields for each newsletter.

Example of creating new integer fields

Import Contacts

In the Kentico Contact Management Module, there is a feature to import contacts via a button at the top. Click the button.

Example of importing contacts

In the next screen click “Select a file”

Image showing where "select a file" is located

Select the CSV file you created before the upgrade. After that, you should get a screen that maps the fields together. Map to the proper fields. There is no need to segment the imported contacts. Note that the SubscriberEmail fields maps to the Email Address, and the rest map to the custom fields.

Example of mapping fields

When the fields are mapped, click “Import contacts”.

Create Condition Based Contact Groups

A condition-based Contact Group should be created for each Newsletter that has subscribers that have been imported. To do so, go to the Kentico Contact Group Module, create a new Contact Group for the first newsletter. Checking the box “Condition-based contact group”. For the macro condition, write a macro in the following format:

Contact.CustomFieldName == 1

Image showing where to write macro

Click Save. Then click “Rebuild contact group” and reload the frame. If successful it should look like this:

Example of successful rebuild

Repeat that for each Newsletter.

Create a Marketing Automation Process

A Kentico Marketing Automation process should be created for each different Newsletter. This is what will re-subscribes all the subscribers to the newsletters they were subscribed to before the upgrade. In the Kentico Marketing Automation Process, create a “New Process” for adding to the first newsletter.

Image showing "New Process" button

Click "Save".

Image showing "Save" button

Go to “Steps”.

Image showing "Steps" in menu

Click and drag to add a step for “Newsletter Subscription”.

Example of dragging to add a new step

Connect Start to Newsletter subscription and Newsletter subscription to Finished.

Example of connecting Start to Newsletter subscription and Newsletter subscription to Finished.

Edit the Newsletter Subscription and set the Newsletter to the proper one for this task, uncheck “Inherit double opt-in settings” and click “Save”.

Example of unchecking “Inherit double opt-in settings” and “Save”

Repeat these steps to create Kentico Marketing Automation Processes for the other newsletters.

Add the Contact Groups Contacts to the Automation Process

Now that we have the Kentico Marketing Automation Processes created we need to add the contacts of each contact group to their respective processes.

First, go back to the Kentico Contact Groups module. Hit the editing pin of the first contact group and go to “Contacts”.

Showing location of "Contacts" button

At the bottom dropdown menu, choose “All contacts” and on (select an action) choose “Start new process” and click “OK”.  

Example of choosing "All Contacts", "Start a new process" and selecting "ok"
When it prompts you, choose “OK”.

Example of prompt

Then select the proper automation process for this contact group.

Example of selecting proper automation process

Go to the Email Marketing Module and choose “Email feeds” and click the edit pin of the email feed in which you just added contacts. Go to “Recipients” and verify the Recipients have been added and subscribed.

Email Marketing Module

Repeat the above steps for each newsletter to get all subscribers from before the upgrade subscribed again.

Clean Up

Now that the subscribers from before the upgrade have been added back into Kentico, you can now clean up and remove the custom category and fields from the Condition Based Contact Class, the Contact Groups, and the Marketing Automation Process. This is not necessary but will help keep your Kentico instance clean by removing objects and modifications that are no longer necessary. 

Conclusion

This is different and at least, in my mind, easier and simpler than my last approach to preserving subscribers after upgrading from Kentico 9. The only time SQL was used in this approach was to generate a CSV of subscribers and the rest was done within the Kentico Admin Interface. I hope you also found this method to be an improvement over my last method.

Share This Post:

Twitter Pinterest Facebook Google+
Click here to read more Kentico posts
Start a Project with Us
Photo of the author, Ansel Pineiro

About the author

Ansel is the Hotfixer. Whenever a site needs to be hotfixed or upgraded, he gets it done lightning fast. He always seeks to find the right balance between performance and readability in all the code he writes. Between code writing and hotfixes, he makes the office a more enjoyable place to work by finding new music and desktop wallpapers for everyone to enjoy. Some think he’s crazy, but Bizstream knows him as The Hotfixer!

View other posts by Ansel

Subscribe to Updates

Stay up to date on what BizStream is doing and keep in the loop on the latest with Kentico.