Q: How can I synchronize external data with the recipient warehouse?
Answer by Ben Parker
Chief Corporate Consultant, L-Soft
Several of our LISTSERV Maestro customers who have existing data stored in an external database have asked how to synchronize that data with data stored in the Maestro Recipient Warehouse (for example in Hosted Recipient Lists). While LISTSERV Maestro can of course pull the data directly from the external DB for mailing, features like subscriber pages, subscribe/unsubscribe management by Maestro and forward-to-a-friend are available only with data and lists hosted in the Maestro Recipient Warehouse. Again, it is possible to transfer the data to LISTSERV Maestro in a one-time move, but often, the data must remain stored externally for legacy reasons (such as being in a CRM system), so periodic synchronization is the only feasible solution.
First, let's consider what we mean by data synchronization. Ideally, after a synchronize event, the data in both systems is completely identical for the comparable data fields. Both the external and internal/hosted datasets may have additional data fields used for their relevant purposes but which are not necessary to synchronize. The most relevant fields for synchronization are of course things like email address, names, user ID or account number, data used for mail-merging and to support conditional block expressions for customized message content (for example, a birth date field might be used for a restaurant mailing for a 'come in and celebrate with us' offer). Over time, after a synchronize event, data in both the external and internal/hosted datasets will change. Records will be added, deleted and changed. So how can perform we this synchronization?
Fortunately, several features new in LISTSERV Maestro 4.0 make this easy. First we'll consider how to import data into a Hosted Recipient List in LISTSERV Maestro from an external DB. The LISTSERV Maestro 4.0 Data Administrator's Manual, Sect 9.3.2 has the detailed steps of the process, but in brief, first you select the Maestro dataset and Hosted Recipient List you want to import into. Since this will be a recurring operation, instead of doing this import on a one-time basis (Sect 9.3.1), we want to create a Subscriber Importer. An Importer is simply a series of steps for conducting the import operation saved in a manner so the process can be repeated as often as necessary.
Note that for importing into a Maestro Hosted List you must define a Subscriber Importer. If you want to import into a Hosted Dataset, then you define a Member Importer (on the Dataset pull-down menu). Both kinds of importers are configured and function in the same way. The distinction is where the data ends up in LISTSERV Maestro.
The key difference between version 4.0 of LISTSERV Maestro and previous versions is that now LISTSERV Maestro can connect directly to the external database to transfer the data. All you need to do is to provide a suitable SELECT statement.
Once the various other details are configured, you save the Importer and then enable it. On the selected Importer detail page in LISTSREV Maestro you can click the 'Launch' link anytime you want to perform the import operation. However, the import operation can also be triggered externally (i.e. outside LISTSERV Maestro) by accessing a special URL using a Security Token.
Once you know the security token, to trigger the action, the following URL must be accessed in a web browser (or by a script using something like the 'wget' utility). A trigger URL always has the following form:
http://[SERVER_NAME]/lui/externalAction.do?token=[SECURITY_TOKEN]
For example:
http://maestro-train.lsoft.com/lui/externalAction.do?token=toxdimo3p0fqiba41j
We can now update the Maestro Hosted Recipient List on an easily repeatable basis at whatever time interval makes the most sense for our data environment. However, how do we capture the changes that may occur in the Maestro hosted data via the user subscriber pages, such as unsubscribes, email address changes and so on?
Fortunately, this is easy, by turning on the Subscriber Activity Change Log in LISTSERV Maestro. Again, the LISTSERV Maestro 4.0 Administrator's Manual Sect 13.2 has the details.
Here is an example Change Log file:
2006-08-03 02:18:26 DEL L 13 22 JJAFFRAY@DIRECWAY.COM
2006-08-03 02:18:26 DEL D 13 JJAFFRAY@DIRECWAY.COM
2006-08-03 02:18:26 DEL L 12 20 JJAFFRAY@DIRECWAY.COM
2006-08-03 02:18:26 DEL D 12 JJAFFRAY@DIRECWAY.COM
2006-08-03 08:56:48 DEL L 1 24 BUDTHETEACHER@GMAIL.COM 216.17.254.77
2006-08-03 08:56:48 DEL L 1 1 BUDTHETEACHER@GMAIL.COM 216.17.254.77
2006-08-03 09:23:12 DEL L 1 14 IZZYD96@MSN.COM 71.49.153.209
2006-08-03 09:23:12 DEL L 1 16 IZZYD96@MSN.COM 71.49.153.209
2006-08-03 09:24:09 DEL L 1 1 KATHRYN.CRISLER@UCHSC.EDU 140.226.132.174
As documented in the manual, the dataset_ID and list_ID are integer numbers. If you have multiple lists or multiple datasets, you need to know which lists and datasets the changes apply to. Finding the dataset_ID and list_ID are fairly easy. Log in to LUI using the 'admin' (or HUB) login. Select Recipient Warehouse then Datasets.
On the next page, select the User Group and then the dataset in question and click on it to show the dataset_ID.
Click on the Hosted List link to show the list_ID.
With this Change Log information, you now have everything you need to extract the changes from Maestro's Hosted data and use that to update your external database source.
However, there is another way to do this. You can choose to export all of the Hosted Recipient List data from LISTSERV Maestro into a file which you can then manipulate against your external database. You can perform this export manually from the 'Download All Members' option on the 'Dataset' pull-down menu or the 'Download All Subscribers' on the 'Hosted List' pull-down menu. But since you will want to repeat this operation periodically, you can also create an external trigger for the export. The steps for the export are much simpler than for the import so all you really need to do is learn the Security Token needed for the access URL. This is detailed in the LISTSERV Maestro 4.0 Data Administrator's Manual, Sect 9.4.
Here is the example access URL:
http://maestro-train.lsoft.com/lui/externalAction.do?token=c1dwnrx9p0fqiba419
And here is the result:
The question of which overall process is 'better' really depends on your data environment. If the external database is your primary data source and you just want the convenience of easily synchronizing the data in LISTSERV Maestro for email purposes, then the Subscriber Importer/Change Log process is the most direct. However, if your primary data source is LISTSREV Maestro and you just need the external data for limited purposes or perhaps simply for backup purposes, then the Exporter method may be more useful.
References
LISTSERV Maestro 4.0 Data Administrator's Manual
LISTSERV Maestro 4.0 Administrator's Manual
| 
