By: Chris Lewis

When the first Arial Software products were create the database language of SQL was thought of to be too hard to use and just for programmers.  Many of the database operations that were done using our program were simple field/column updates, like incrementing a number in a record if the email was sent. The program was set up so that the field to be updated was in a selection box and the only operation you could do is increment the field by one. This functionality still exists today even in version 11, but as many have seen this simple operation is not sufficient.  Because of this we added the ability to run an SQL statement when an even happens in Campaign Enterprise, like when a record is sent, when an email is unsubscribed, or a click through occurs.  This feature can be turned on in the administration area which then causes the program to show this new SQL statement box on each even configuration screen.  Some have opted to, instead of incrementing a record, to insert a new record recording the event into a table that can be used for later reporting.  And example of this, say for an unsubscribe operation would be:

INSERT INTO tblUnsubscribes (CampaignID,UniqueID,EventDateTime) values ({CAMPAIGNID},{UNIQUEID},GetDate());

So, when a unsubscribe event happens for a particular email that was sent, this statement is sent to the database you are using and the values within the braces { } are replaced with real values.  The resulting statement that is actually sent to your database would look like this:

INSERT INTO tblUnsubscribes (CampaignID,UniqueID,EventDateTime) values (14,43456,GetDate());

After the operation is done, you will have a new record in the tblUnusubscribes table recording this event.  This data can now be used for filtering and/or reporting purposes later.  The advantage of this method is that it records the specific date and time of the event, and the data is separate from the original record.  Using this method is required when the original data cannot be modified due to security or database structure.

You can use any legal SQL statement you want for these events.  You could opt to update a record instead of inserting, or you could run a stored procedure to trigger other events.  More information is available on this subject on this website or by request.