An Approach to Preserving Newsletter Subscribers When Upgrading to Kentico 10

By Ansel Pineiro on February 16, 2018

An Approach to Preserving Newsletter Subscribers When Upgrading to Kentico 10
If you are upgrading from Kentico 9 to 10 and your website makes use of Newsletters, there are a few things you need to do in order to keep your Subscribers. Without these steps you’ll undoubtedly lose Subscribers during the upgrade process. This blog post will show you an approach to preserving your Subscribers.

Subscribers are Contact Based

In Kentico 10 Subscribers are contact based, so all Subscribers need to have a Contact with a matching email. If no Contact is present for that Subscriber, that Subscriber will be lost during the upgrade.

Cleaning up Old Contacts

If your website has been around for a while and had a decent amount of traffic, chances are your contact table is bloated. This could make it hard to link a Contact to a Subscriber. Unless you need to keep old contacts for reasons other than Subscriptions, it is easiest to simply clean up the Contact tables and start fresh.

You can start this by running the following code to truncate tables related to Contacts:
 
TRUNCATE TABLE OM_AccountContact
TRUNCATE TABLE OM_ContactGroupMember
TRUNCATE TABLE OM_Membership
TRUNCATE TABLE OM_IP
TRUNCATE TABLE OM_UserAgent
TRUNCATE TABLE OM_ScoreContactRule
TRUNCATE TABLE OM_PageVisit
TRUNCATE TABLE OM_Search
 

The above tables can be truncated without having to do anything special. However other tables have Foreign Key restraints that need dropped.  

OM_Activity is one table. The code below will drop the Constraints, truncate the table, and add the constraints back:
 
--DROP FKS referencing OM_Activity
ALTER TABLE OM_PageVisit
    DROP CONSTRAINT FK_OM_PageVisit_OM_Activity
GO   
ALTER TABLE OM_Search
    DROP CONSTRAINT FK_OM_Search_OM_Activity
GO
TRUNCATE TABLE OM_Activity

--Add back FKS referencing OM_Activity
ALTER TABLE [dbo].[OM_PageVisit]  WITH CHECK ADD  CONSTRAINT [FK_OM_PageVisit_OM_Activity] FOREIGN KEY([PageVisitActivityID])
REFERENCES [dbo].[OM_Activity] ([ActivityID])
GO

ALTER TABLE [dbo].[OM_PageVisit] CHECK CONSTRAINT [FK_OM_PageVisit_OM_Activity]
GO

ALTER TABLE [dbo].[OM_Search]  WITH CHECK ADD  CONSTRAINT [FK_OM_Search_OM_Activity] FOREIGN KEY([SearchActivityID])
REFERENCES [dbo].[OM_Activity] ([ActivityID])
GO

ALTER TABLE [dbo].[OM_Search] CHECK CONSTRAINT [FK_OM_Search_OM_Activity]

OM_Contact has a lot more constraints and is even more trouble to truncate as a result. Luckily, I have provided the code needed to drop all the constraints, truncate, and add them back below:
 
--Remove FK Constraints holding back OM_Contact truncate

--DROP FK Constraints referencing OM_Contact on OM_Account
ALTER TABLE OM_Account
DROP CONSTRAINT FK_OM_Account_OM_Contact_PrimaryContact
GO
ALTER TABLE OM_Account
DROP CONSTRAINT FK_OM_Account_OM_Contact_SecondaryContact
GO

--DROP FK Constraints referencing OM_Contact on OM_AccountContact
ALTER TABLE OM_AccountContact
DROP CONSTRAINT FK_OM_AccountContact_OM_Contact
GO

--DROP FK Constraints referencing OM_Contact on OM_Activity
ALTER TABLE OM_Activity
DROP CONSTRAINT FK_OM_Activity_OM_Contact_Active
GO

ALTER TABLE OM_Activity
DROP CONSTRAINT FK_OM_Activity_OM_Contact_Original
GO

--DROP FK Constraints referencing OM_Contact on itself
ALTER TABLE OM_Contact
DROP CONSTRAINT FK_OM_Contact_OM_Contact_ActiveGlobal
GO

ALTER TABLE OM_Contact
DROP CONSTRAINT FK_OM_Contact_OM_Contact_Merged
GO

--DROP FK Constraints referencing OM_Contact on OM_IP
ALTER TABLE OM_IP
DROP CONSTRAINT FK_OM_IP_OM_Contact_Active
GO

ALTER TABLE OM_IP
DROP CONSTRAINT FK_OM_IP_OM_Contact_Original
GO

--DROP FK Constraints referencing OM_Contact on OM_Membership
ALTER TABLE OM_Membership
DROP CONSTRAINT FK_OM_Membership_OM_Contact
GO

ALTER TABLE OM_Membership
DROP CONSTRAINT FK_OM_Membership_OM_Contact1
GO

--DROP FK Constraints referencing OM_Contact on OM_ScoreContactRule
ALTER TABLE OM_ScoreContactRule
DROP CONSTRAINT FK_OM_ScoreContactRule_OM_Contact
GO

--DROP FK Constraints referencing OM_Contact on OM_UserAgent
ALTER TABLE OM_UserAgent
DROP CONSTRAINT FK_OM_UserAgent_OM_Contact_Active
GO

ALTER TABLE OM_UserAgent
DROP CONSTRAINT FK_OM_UserAgent_OM_Contact_Original
GO


--TRUNATE OM_Contact table
TRUNCATE TABLE OM_Contact

--Add back FK Constraints referencing OM_Contact

--Add back foreign key constraints to OM_Account table
ALTER TABLE [dbo].[OM_Account]  WITH CHECK ADD  CONSTRAINT [FK_OM_Account_OM_Contact_PrimaryContact] FOREIGN KEY([AccountPrimaryContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_Account] CHECK CONSTRAINT [FK_OM_Account_OM_Contact_PrimaryContact]
GO

ALTER TABLE [dbo].[OM_Account]  WITH CHECK ADD  CONSTRAINT [FK_OM_Account_OM_Contact_SecondaryContact] FOREIGN KEY([AccountSecondaryContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_Account] CHECK CONSTRAINT [FK_OM_Account_OM_Contact_SecondaryContact]
GO

--Add back foreign key constraints to OM_AccountContact table
ALTER TABLE [dbo].[OM_AccountContact]  WITH CHECK ADD  CONSTRAINT [FK_OM_AccountContact_OM_Contact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_AccountContact] CHECK CONSTRAINT [FK_OM_AccountContact_OM_Contact]
GO

--Add back foreign key constraints to OM_Activity table
ALTER TABLE [dbo].[OM_Activity]  WITH NOCHECK ADD  CONSTRAINT [FK_OM_Activity_OM_Contact_Active] FOREIGN KEY([ActivityActiveContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_Activity] CHECK CONSTRAINT [FK_OM_Activity_OM_Contact_Active]
GO

ALTER TABLE [dbo].[OM_Activity]  WITH NOCHECK ADD  CONSTRAINT [FK_OM_Activity_OM_Contact_Original] FOREIGN KEY([ActivityOriginalContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_Activity] CHECK CONSTRAINT [FK_OM_Activity_OM_Contact_Original]
GO

--Add back foreign key constraints to OM_Contact table (Referencing itself)
ALTER TABLE [dbo].[OM_Contact]  WITH NOCHECK ADD  CONSTRAINT [FK_OM_Contact_OM_Contact_ActiveGlobal] FOREIGN KEY([ContactGlobalContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_Contact] CHECK CONSTRAINT [FK_OM_Contact_OM_Contact_ActiveGlobal]
GO

ALTER TABLE [dbo].[OM_Contact]  WITH NOCHECK ADD  CONSTRAINT [FK_OM_Contact_OM_Contact_Merged] FOREIGN KEY([ContactMergedWithContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_Contact] CHECK CONSTRAINT [FK_OM_Contact_OM_Contact_Merged]
GO

--Add back foreign key constraints to OM_IP
ALTER TABLE [dbo].[OM_IP]  WITH CHECK ADD  CONSTRAINT [FK_OM_IP_OM_Contact_Active] FOREIGN KEY([IPActiveContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_IP] CHECK CONSTRAINT [FK_OM_IP_OM_Contact_Active]
GO

ALTER TABLE [dbo].[OM_IP]  WITH CHECK ADD  CONSTRAINT [FK_OM_IP_OM_Contact_Original] FOREIGN KEY([IPOriginalContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_IP] CHECK CONSTRAINT [FK_OM_IP_OM_Contact_Original]
GO

--Add back foreign key constraints to OM_Membership

ALTER TABLE [dbo].[OM_Membership]  WITH CHECK ADD  CONSTRAINT [FK_OM_Membership_OM_Contact] FOREIGN KEY([OriginalContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_Membership] CHECK CONSTRAINT [FK_OM_Membership_OM_Contact]
GO

ALTER TABLE [dbo].[OM_Membership]  WITH CHECK ADD  CONSTRAINT [FK_OM_Membership_OM_Contact1] FOREIGN KEY([ActiveContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_Membership] CHECK CONSTRAINT [FK_OM_Membership_OM_Contact1]
GO

--Add back foreign key constraints to OM_ScoreContactRule

ALTER TABLE [dbo].[OM_ScoreContactRule]  WITH CHECK ADD  CONSTRAINT [FK_OM_ScoreContactRule_OM_Contact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_ScoreContactRule] CHECK CONSTRAINT [FK_OM_ScoreContactRule_OM_Contact]
GO

--Add back foreign key constraints to OM_UserAgent

ALTER TABLE [dbo].[OM_UserAgent]  WITH CHECK ADD  CONSTRAINT [FK_OM_UserAgent_OM_Contact_Active] FOREIGN KEY([UserAgentActiveContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_UserAgent] CHECK CONSTRAINT [FK_OM_UserAgent_OM_Contact_Active]
GO

ALTER TABLE [dbo].[OM_UserAgent]  WITH CHECK ADD  CONSTRAINT [FK_OM_UserAgent_OM_Contact_Original] FOREIGN KEY([UserAgentOriginalContactID])
REFERENCES [dbo].[OM_Contact] ([ContactID])
GO

ALTER TABLE [dbo].[OM_UserAgent] CHECK CONSTRAINT [FK_OM_UserAgent_OM_Contact_Original]


In the event of Duplicate Subscriber entries, it’s a good idea to temporarily add a ContactSubscriberGUID that later gets linked to the Newsletter_Subscriber.SubscriberGUID field.
 
GO
--Temporarily add a column for the Subscriber GUID so the Newsletter_Subscriber table 
--will have something to reference
ALTER TABLE OM_Contact
ADD ContactSubscriberGUID UNIQUEIDENTIFIER


User Based Subscribers might not have the SubscriberEmail field filled out and instead goes by the Email field in User. This needs populated before the upgrade.
 
--Populate the SubscriberEmail field of Subscribers of type 'cms.user' from the CMS_User.Email field

UPDATE A
SET SubscriberEmail = B.Email
FROM Newsletter_Subscriber A
INNER JOIN CMS_User B ON A.SubscriberRelatedID = B.UserID AND A.SubscriberType = 'cms.user'

Now it is time to add the Subscribers to the Contacts table.  
 
--Current Date for the ContactCreated and ContactLastModifiedField
DECLARE @currentDate DATETIME2 = GETDATE()

--Add the Subscribers to the Contacts
INSERT INTO OM_Contact (ContactEmail, ContactFirstName, ContactLastName, ContactCreated, ContactLastModified, ContactGUID, ContactSubscriberGUID, ContactNotes)
SELECT SubscriberEmail,
 SubscriberFirstName,
 CASE WHEN SubscriberLastName IS NOT NULL THEN SubscriberLastName ELSE '' END,
 @currentDate, @currentDate, NEWID(), SubscriberGUID, 'Contact added as part of the Kentico 10 Upgrade'
FROM Newsletter_Subscriber ns

After this, you need to set the Newsletter_Subscriber.SubscriberType to om.contact, and set the SubscriberRelatedID to the ContactID.
 
--Update the Subscriber table setting the subscriber type to 'om.contact', 
--and the SubscriberRelatedID to the OM_Contact.ContactID
UPDATE  A
SET SubscriberType = 'om.contact', SubscriberRelatedID = B.ContactID
FROM Newsletter_Subscriber A
INNER JOIN OM_Contact B ON B.ContactSubscriberGUID = A.SubscriberGUID



Now that that’s done, be sure to  remove that temporary ContactSubscriberGUID field from the OM_Contact table.
 
--Remove temproary ContactSubscriberGUID field
ALTER TABLE OM_Contact
DROP COLUMN ContactSubscriberGUID

If you want to verify the Subscribers are linked up to the Contacts correctly, the following query is helpful.
 
--Take a look at the first 100 Rows of Contacts inner joined with Subscribers to verify

SELECT TOP 100 c.ContactFirstName, c.ContactLastName, c.ContactEmail,
 s.SubscriberFirstName, s.SubscriberLastName, s.SubscriberEmail
FROM OM_Contact c
INNER JOIN Newsletter_Subscriber s ON c.ContactID = s.SubscriberRelatedID
WHERE SubscriberType = 'om.contact'

Monitor Old Contacts

Kentico 10 includes powerful Contact Management tools including monitoring of users. New users by default will be monitored, but old users are not necessarily monitored. You can easily monitor all old users with the following command.
 
UPDATE OM_Contact
SET ContactMonitored = 1


Conclusion

Kentico 10 has provided extremely powerful and valuable tools to monitor Subscriber activity by making them Contact based. Unfortunately, as a result of these changes, there is a need to manually preserve Subscriber data. I hope you find this approach helpful in safeguarding Subscribers as you make the upgrade to Kentico 10.

Share This Post:

Twitter Pinterest Facebook Google+
Click here to read more Kentico posts
Start a Project with Us

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 Email

Enter your email address to subscribe to the BizStream Newsletter and receive updates by email.