By: Chris Lewis

One of the uses of Campaign Enterprise is to send out triggered emails like for birthdays, anniversaries, or other info that has to be sent on X-amount of days after a date.  The following example sends out an email 3 days after someone signs up for information:

First, we have a table where information about your signups are stored.  For this example we will call this tblSignups.  In this table, we need a date column in this table to trigger on in your email.  For this example, we will have the column called "SignupDate" and this should be set at the date that the person signs up on your website.

Next, we create a campaign that we want to use to send the email and on the Datasource tab we use this following SQL statement (assuming MS SQL Server):

SELECT * FROM tblSignUps WHERE DateDiff(dd,SignupDate,GetDate())=3

Now for this Campaign to work, the campaign will need to be scheduled to run each day at the same time. If a day is missed, then that day's emails will not go out. The only way to make sure all emails go out is having another column in the database that is marked when the email is sent, and then you can check that in the SQL statement used for sending.  For example, if you had another column called EmailSent (a BIT type) that was set when the email was sent, you would use an SQL statement like this:

SELECT * FROM tblSignUps WHERE DateDiff(dd,SignupDate,GetDate())=>3 and EmailSent=0

You can add more sophistication to these statements but this is a good start.

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.

Consider the following as a text representation of your original database source table.

ID EmailAddress FirstName LastName
1 bobsmith@yourdomain.com Bob Smith
2 janehooper@yourdomain.com Jane Hooper

To insert information into a new table use an insert statement, the built-in merge fields that can be used for Click-Throughs include: {CAMPAIGNID},{UNIQUEID}, {CLICKTHRUNUMBER}, {REMOTEADDRESS} and {EMAILADDRESS}. 

Sample Syntax:
INSERT INTO ClickThroughInfo (CAMPAIGNID,UNIQUEID,Event,CLICKTHRUNUMBER,REMOTEADDRESS,EMAILADDRESS) VALUES ({CAMPAIGNID},{UNIQUEID},'Click',{CLICKTHRUNUMBER},'{REMOTEADDRESS}','{EMAILADDRESS}')

ClickThroughInfo Table - Before Bob Smith or Jane Hooper have clicked on a click through.

CAMPAIGNID UNIQUEID Event CLICKTHRUNUMBER REMOTEADDRESS EMAILADDRESS

ClickThroughInfo Table - After Bob Smith clicked on a click through number 1 from campaign number 7, and click through number 3 from campaign number 2, and Jane Hooper clicked on click through number 11 for campaign number 5.

CAMPAIGNID UNIQUEID Event CLICKTHRUNUMBER REMOTEADDRESS EMAILADDRESS
7 1 Click 1 200.235.68.74 bobsmith@yourdomain.com
5 2 Click 11 12.45.74.1 janehooper@yourdomain.com
2 1 Click 3 200.235.68.74 bobsmith@yourdomain.com

Click-Through Tracking Stored Procedure
To run a stored procedure each time a click through is recorded, create the stored procedure on the database, then execute the stored procedure from the stored procedure field for open tracking.

Example: EXECUTE dbo.ClickthroughSP

You can now also use the {URL} merge field to pass the URL for the click through back to some field in your table.

By: Chris Lewis

When you send out an email and something goes wrong with it being delivered it is usually returned to you or "bounced".  Bounced emails can happen for various reasons, some of them being permanent and some of them being temporary.

Hard Bounces - Hard bounces happen when any of the SMTP servers down that the email address you used to send will just never work.  One of the first reasons an email will not reach it's destination is that the domain name does not exist.  The second reason is that the mailbox on the mail server for that domain just does not exist.  Hard bounces are usually characterized by a 500-series error.  When an email is returned with a 500 series error inside, Campaign Enterprise and Email Marketing Director will categorize these as hard bounces.  Usually when a hard bounce like this occurs, you mark a hard bounce field in the corresponding database record, and then when you send using that list again you filter out any email records that have this hard bounce field marked.  Since it is a permanent failure, there is no reason to try sending to that address again.  Aside from other information you want to keep in that record, the email address is virtually useless.

Soft Bounces - When a returned email not a 500-series return code, it is assumed to be a temporary or soft bounce.  These types of bounces usually mean the domain is good and the email box is valid but for some reason the email server is not accepting any emails for this address at the present time.  The reasons can range from the mail box is full, the server is too busy, or the person has marked his account as being on holiday.  In the case of soft bounces, it is ok to try again the next time you send using that list.  There will come a time though when maybe too many soft bounces really means a hard bounce...where an email box is always full (maybe not in use).  In this case, you many not want to filter soft bounces out until a certain point, say, you get 20 soft bounces for that email address. In that case, you can filter the email list to exclude any records that have over 20 soft bounces.  Now this is very arbitrary so you will have to set the number of times a record can soft bounce before you remove it from the list.  Many times, sending emails to these addresses over and over is ok because the destination mail server does see the email box as valid, just not reachable, and that is not a bad mark on you.

So, for many, they just worry about hard bounces because those are the ones they can get in trouble with concerning the big mail providers.  I would recommend just ignoring the soft bounce recording but it is up to your companies conventions.

BTW: Hotmail just got transformed to a new mail service called OUTLOOK.COM.  We will be following this change to see how it affects our customer's deliver ability so check back over the next few weeks.
 

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