Setting up an effective write-back table in your database

One of the primary goals of permission-based email marketing is to engage customers more fully by collecting relevant information into a database, which can then be used effectively to directly answer customer needs using highly personalized messages. To accomplish this, a working knowledge of database interactions is needed. One such database-related skill is setting up a good write-back table.

As your customer list and collected data grow, good database management becomes a necessity. Many companies use several tables to manage all the information collected: One for the customer contact information, one for purchases made, one for subscription requests, etc. When using Campaign Enterprise, it can be challenging to decide where to put the specific write-back information gathered.

One method is to consolidate information to a write-back table. The write-back table can be different from the source table, but the write-back table must reside on the same database as the source since Campaign Enterprise uses the same database connection string to connect to both tables. (Note: When using the advanced feature set in the Datasource tab in Campaign Enterprise, an alternate write-back table must be selected.)

A database view is by nature a temporary record set constructed from a query using structured query language (SQL). A view is an amalgam of data pulled from various sources in a relational database, to be displayed dynamically. The email address and contact information may be pulled from one table, while sign up date and product information is pulled from another. Since views are temporary record sets, they are not easily updatable. It is possible to update a view when stored on the database, but additional configuration of the view is necessary. The advanced features of Campaign Enterprise require a field in the table to update, and when using a view exclusively the updates might not be possible.

The best option for managing the write-backs for the advanced features like click through processing and opened email tracking is to construct a separate table, or several tables, to manage those actions.

Simple write-back table
The simplest write-back table must contain the same values for the email address field and unique ID as the source record set. For example, if a record in the source record set has a unique ID of 100, and an email address of email@domain.com, the unique ID and email address field of the write back table must also be 100 and email@domain.com in order for the write-backs to occur correctly.

The query can consist of a number of inner or outer joins which combines two or more tables together without altering the individual tables in the database. Views display records dynamically from one or more tables. If the source data changes, the view also changes.

When using a query that pulls records from a table named contact containing the unique ID and email addresses, combined with other tables and other data, the best option for the write-back table is to use the contact table to update. Simply add the necessary fields to update to that table: click1, click2, unsubscribe, bounce, etc. The ID and email address fields are already available and are a perfect match. Here's an example:

SELECT contact.id, contact.email, product.sale, products.purchasedate
FROM contact INNER JOIN Updates ON contacts.id = updates.id;

Write-back table to update: contact
Unique ID field: id

Using a query like this creates a relationship between the contact and product tables, which are then displayed in the resulting view. By selecting the contact table as the write-back, only the contact table is actually updated, changes to the source record set are displayed in the view since it is a dynamic reflection of the selected source tables.

When Campaign Enterprise connects using this query that connects the two tables with an inner join, it sees the ID and Email address fields from the contact table, and the Sales and Purchase Date fields from the Product table. Select the contact table as the alternate write-back table on the Datasource tab.

When configuring advanced features like unsubscribe processing, the fields to update are pulled from the alternate table specified in the Datasource tab, not the source view. The full contact table then should contain a field for all the updates specified for a particular campaign. These fields should be numeric fields with default values of zero. Since the contact table already uses the same ID and Email address fields as the original view, there is no problem with matching up the IDs and updating the records appropriately.

If the source table cannot be modified -- even if it's not part of a query -- use an alternate write-back table. Ensure that the email address and unique ID fields have a one to one match, meaning that they are the same on the source table as they are on the write-back table as explained above. Make sure the write-back table contains all of the fields necessary to update. When configuring the fields to update for each feature, the options displayed are those of the alternate write-back table, not the source data.

Complex write-back table
Using the advanced write-back feature of Campaign Enterprise, it is possible to update a more complex and empty write-back table with INSERT INTO statements. To enable this feature, log in as an administrator and go to the Administrator>Manage Configuration area and enable stored procedure write backs. This enables an additional field on each of the advanced feature edit tabs for the INSERT INTO statement. More data is available for updates when using this feature.

The format of an insert statement is:

INSERT INTO tablename (fieldname1,fieldname2) VALUES (value1,value2)

If the fields to update are text type fields, the values entered must be surrounded by single quote marks. There are several campaign specific merge fields available as well, like {REMOTEADDRESS}, {EMAILADDRESS}, {UNIQUEID} that can be put back into the write-back table.

For example:

INSERT INTO UpdateTable (CAMPAIGNID,UNIQUEID,Event,RequestType,REMOTEADDRESS,EMAILADDRESS) VALUES ({CAMPAIGNID},{UNIQUEID},'Unsubscribe','Web','{REMOTEADDRESS}','{EMAILADDRESS}')

The table structure for this UpdateTable is as follows:

ID	AutoNumber	The ID and primary key for the update table, not the ID from the source table
UNIQUEID	Number	This is the ID passed back from the source table
CAMPAIGNID	Number	This is the Campaign ID
REMOTEADDRESS	Text	This is the IP address captured on opened email tracking, click through tracking or web-based unsubscribe. It is a text field because it of the format of the address
EVENT	Text	This is hard coded in the string above, the event would be 'Unsubscribe', 'Bounce', 'Clickthrough' etc.
REQUESTTYPE	Text	This can be used to differentiate between a web based or email based unsubscribe, or a hard or soft bounce
CLICKTHRUNUMBER	Number	This is the click through number of the link clicked
CLICKDATE	Date/Time	This is the date the click through was sent out, not when it was clicked
DATESTAMP	Date/Time	This is the date the click through was recorded
EMAILADDRESS	Text	This is the email address pulled from the source table

This type of write-back table is more versatile and collects data from all sorts of actions. The values of many of the fields help determine what type of action occurred. A report can then be run in the database to help parse the data into something useful. When designing the table, keep in mind all of the variables that are written back for each type of action. Consult the help files or user manual for more specific information on the merge fields that are available.

Multiple write-back tables
Using the same concept, separate write-back tables can be created to manage each action separately. There can be a click through table, bounce table, unsubscribe table etc. Each INSERT INTO statement would point to a different table and update it accordingly.

Remember, when using the Advanced feature set on the Datasource tab, an alternate write-back table must be selected, even when using INSERT INTO statements exclusively, use the table where the email addresses are stored as the write-back table.
-- Arial Software

Return to Technical Tips