Database Write-back Methods

By Chris Lewis

Many of the best features of Campaign Enterprise is the ability to write directly back to a database triggered by certain events. In the early days of email marketing many were using simple database structures so the writeback methods were simple field updates.  If a response event occurred, a database field would simply be incremented corresponding to a click through or unsubscribe event.  As time moved on, clients wanted more flexibility and the ability to add SQL statements (stored procedures) were implemented but the field update/increment method was kept.  I will discuss the differences of the two methods in order for you to figure which is the most appropriate for you:

Simple Field Update
Using this method, you simply select a field that you want incremented when a conversion event occurs.  If you need this simplicity and you just need to know if a recipient responded in some way that this will work fine.  One of the biggest roadblocks for this method is that your datasource, the table or view you specified, cannot be written back to for some reason.  These reasons can be as simple as the table is locked or the SQL statement you are using does not allow updates for a multitude of reasons.  Some of these reasons can be that your SQL statement is a one-to-many relationship, you are using grouping or aggregate statements, you are using a union query, etc.  Campaign Enterprise can only do as much as you allow it to do and many of the reasons for non-updateable data can be subtle.  If you just specify a table for your datasource field increment updates should not be a problem, but the key is to keep it simple.

SQL Statements (Stored Procedures)
This method of database updates give you complete control of the situation.  You are no longer bound to using the original datasource for updates, in fact, you can write back to any table you specify or perform mammoth size operations for a response event  like click throughs.  In Campaign Enterprise you can specify any SQL statement that your SQL Server will allow and Campaign Enterprise just passing that statement along to your SQL Server.  The only modifications that are done to your SQL statement is the replacement of "merge fields" in the statement.  For example, you can use this to record conversion events:

UPDATE tblMyClientList set EventEmailDate=GetDate() where ID={UNIQUEID};

If you put this statement in the particular event tab in your campaigns, when the event occurred this statement would be sent to your SQL Server with the {UNIQUEID} merge field replaced with the true unique ID value for that recipient.  As you can see, this is a free-form area and you could make the SQL statement anything you want.  You can even have a stored procedure run:

EXEC spTrackingEvent {UNIQUEID};

In that way, you don't have to put any code at all in Campaign Enterprise.  Also, no tables are exposed and your can even lock down the security in the SQL Server for this stored procedure.  There are other merge fields you can include in your SQL statement that Campaign Enterprise will replace, but it is too big for this article. You can request a list from us or look in the documentation for more merge field replacements.

There is a switch in the Campaign Configuration section in the Administrative area of Campaign Enterprise if you want to use this ability.  It is by default turned off to now confuse people when they first start to use the product.