Manage Advanced Writebacks in Campaign Enterprise

Many of our support issues concern database writebacks. Since Campaign Enterprise has several different methods of writing back to databases I will explain two of the most used methods with the pros and cons of each.

Single Field Writebacks
The first and most basic method of writing back to the database is just simply select a field in your database that will increment (add one to the existing value) if event occurs. If all you need to know is if this click through occurred once for this recipient, then this method would be sufficient. The number one problem with using this method is that a field is not "seeded" with a zero or some other number. For many databases, even though you select a numeric type of field, it does not put an initial value in the record for that field and is usually a NULL. In order for this value to increment, it needs to add 1 to the number that already exists. If there is a NULL value there, it does not usually increment because it is technically not a zero. So, when you create this numeric field in your database to record the click through make sure you set a default value of zero and make sure when creating a new record that this field does in fact get populated with a zero.

The second biggest problem with single field writebacks is that the field you need to increment may not be "writable". There are various reasons why this can occur. The security of the table may not allow writing access, you can read it but you cannot change the record. Sometimes a new username/password are created just for Campaign Enterprise to interact with the database, so be sure that this username/password has the power it needs to write to the table/folder. Another issue that happens is that the datasource specified turns out not to be writable. This only occurs if you use "advanced" datasource method where you specify the SQL statement yourself. This occurs for many reasons, with the most prevalent being that a one-to-many query was specified which by nature cannot usually be written back to.

Other reasons might be just the complexity of the query, the use of UNIONs, embedded sub-queries, and sometimes the query actually excludes the very record that needs to be updated. I had a customer that wrote a query that would only send emails to people that had not received it yet. Since Campaign Enterprise by default uses the original datasource to lookup and writeback records, when a response from a email recipient was made, and Campaign Enterprise went to look up the record using that datasource, it was not there because it was filtered out because the record had already been sent so the records returned was zero! Now, this was an unusual circumstance because the SQL statement being used had elements in it that were not parse-able by Campaign Enterprise. This leads into how Campaign Enterprise attempts to update the original record. Because of all the various ways and SQL statement can be written, it was up to us to find the most common structure that is used. Here is how Campaign Enterprise attempts to update single field writeback records. Consider the SQL datasource:
SELECT * FROM tblYourTable WHERE SentDate IS NULL

Campaign Enterprise gets all the text in the FROM section of the statement and builds this SQL statement below using the writeback field you specified and the Unique ID field you specified on the Datasource tab:
UPDATE tblYourTable SET YourWritebackField=YourWritebackField + 1 WHERE YourUniqueIDField=<The Unique ID of the Writeback request>

This works for a lot of instances, but is it not perfect. If your FROM section had a complex structure, this method will fail.

Overall for simple single field writebacks, the key is to keep is simple. The minute you start using GROUP BYs, joining tables with WHERE statements instead of JOINS, you have moved into a more advanced area an you will probably need to use the more advanced method of writing back to your database.

Stored Procedure Writebacks
This method is the most flexible, more predictable, and in some ways easier to use then even the simple field writebacks. Though the term "stored procedure" may not be the appropriate term when you are just specifying an SQL statement, we are going to use that term to describe the functionality. This method requires a knowledge of database structure and programming. Overall, when a writeback event occurs, campaign will look to the stored procedure you created and will replace the merge fields in the stored procedure you specified and then send the command to your database. Campaign Enterprise does alter the structure of your store procedure call in any way. There are only a few stored procedure merge fields that are available for each different type of writeback. For example, say for a click through occurrence you wanted to add a record to a table called tblWritebackEvents. In the click through edit section, you would add this stored procedure text:
INSERT INTO tblWritebackEvents (WritebackType,CampaignID,UniqueIDField,ClickThruNumber,IPAddress) VALUES ('CLICKTHRU',{CAMPIGNID},{UNIQUEID},{CLICKTHRUNUMBER},'{REMOTEADDRESS}')

This statement assumes you have a table called tblWritebackEvents with the appropriate fields set and the appropriate data field types. Here is a schema used for this table:

WritebackType VARCHAR(10)
CampaignID INT
UniqueID INT
ClickThruNumber INT
IPAddress VARCHAR(20)

Notice the IPAddress is a text type because it will contains alpha characters (ie. 192.168.1.1)

Also notice the REMOTEADDRESS merge field in the VALUES section of the INSERT INTO statement table. Since the IPAddress is a text type, you must delimit it with quotes. This applies for date-types too. Overall, you need to visualize how the stored procedure statement will look after the merge fields are replaced.

Lastly, notice the WritebackType field. Using this method would allow you do use the same table for all events, like click throughs, unsubscribes, etc.

Creating a stored procedure in your database takes away some of the complexity in Campaign Enterprise and moves it to the database where you can deal with it better. For example, if you wrote a generalized stored procedure, you could use it in all of your writeback instances:
sp_WritebackEvents 'CLICKTHRU',{CAMPAIGNID},{UNIQUEID},{CLICKTHRUNUMBER},'{REMOTEADDRESS}'

As you can see, using this method creates a unique record for this event with more information than with just a single field writeback. You should also add a EventDateTime field that defaults to the current time so there is a date stamp for the record. Also, this method lends to being able to archive these types of events.

Another advantage to this method is that it is the best performance. It is much faster for a database to add a record to a table than looking it up. The single field writeback method takes time because it may have to execute the entire SQL datasource to find that one record.

Here is a list of the available merge fields for the specific writeback events:

Open Tracking
{CAMPAIGNID}
{UNIQUEID}
{DATESENT}
{EVENTDATETIME}
{REMOTEADDRESS}
Unsubscribe
{CAMPAIGNID}
{UNIQUEID}
{EVENTDATETIME}
{REMOTEADDRESS}
Click Through
{CAMPAIGNID}
{UNIQUEID}
{DATESENT}
{EVENTDATETIME}
{REMOTEADDRESS}
{CLICKTHRUNUMBER}
{URL}
Bounce
{CAMPAIGNID}
{EMAILADDRESS}
{RESULTCODE}
{XRESULTCODE}
{RETURNEMAILADDRESS}
Subscribe
{CAMPAIGNID}
{UNIQUEID}
{EVENTDATETIME}
{REMOTEADDRESS}

Take advantage of the powerful write back options available in Campaign Enterprise to help manage your email list and make it more relevant for future campaigns. -- Arial Software