Developer forum

Bump. Any suggestions on how to clean up newsletter data from a large database?

Thanks,

Imar

Hi Imar,

In order to clean your database, you first need to find the emails you want to delete. They are stored in in EmailMarketingEmail (table). Then you need to delete split tests and engagement indexes. They are stored in EmailMarketingSplitTest (table) and EmailMarketingEngagementIndex (table).

Once you have your emails, you can find the messages that correspond to those emails. The message ids are store in the EmailMessageOriginalId (column) and EmailMessageVariationId (column).One of these values will be the one stored in EmailMessageId (column), so you do not need to worry about that column. You also need to delete the message tags related to those messages from EmailMessageTag (table).

With the message ids, you also need to delete the associated links and link clicks. They are stored in OMCLink (table) and OMCLinkClick (table). You need to find links there LinkReferenceType (column) is "EmailMessaging" and the LinkReferenceKey (column) is the message id. With the link ids, you need to delete from link clicks.

Through the message ids, you need to find all recipients linked to those messages. They are stored in EmailRecipient (table). You need to delete recipient tags related to those recipients from EmailRecipientTag (table) as well as the actions performed by these recipients from EmailAction (table).

 There's a lot to get through, but hopefully, this is enough to get you there :)

- Jeppe

>> There's a lot to get through

Indeed there is ;-)

I'll give it a go and see how far I get.

Thanks,

Imar

Here's the script (attached) I ended up with. It seems to work OK although I haven't done any thorough testing yet. Anyone care to quickly review it to see if it all makes sense?

Thanks,

Imar

Hi Imar, thank you so much for sharing your script.

I have tested the script in a testing solution and it seem to be ok, but it would be nice to have feedback from Dynamicweb itself.

It would also be a great to:
1. Be able to clear all the corresponding data when deleting a Newsletter in the BackOffice
2. Add an option to the Management Center - > System -> Solution Report to Purge old Newsletter Data (Or any other Data that is "piled" over the years)

Thanks
Diogo Brito