Campaign Enterprise can connect to any ODBC compliant database, one that complies with Open Database Connectivity requirements. These include, but may not be limited to the following:

• MS Access
• MS SQL Server
• MySQL
• Oracle

There are three steps to go through before making data available to the end user for actually sending the campaign.

• Set up the database connection
• Select the table and and filters
• Assign it to the end user

Set up database connection:

• Determine your database type. The database connection is dependent on what database you intend to use for your email addresses. Go to connectionstrings.com and get the right connection string for your database. You will need to know the version of your database, the permissions used, the IP address and other information as specified by your database administrator. You may also need a username and password depending on how your database is administered.
  
  Example Strings: 
  Access: Standard Security: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Persist Security Info=False;
  
  Access: With Database Password: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Jet OLEDB:Database Password=MyDbPassword;
  
  MS SQL Server: Standard Security: Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword;
  
  MS SQL Server: Trusted Connection: Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
  
  MySQL Standard Security: Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
  
  These are just a few examples. Go to connectionstrings.com to find the string for your database.
• In Campaign, log in as the administrator and click the Administration link.
• Click Database Connection Management
• Click Add New Database Connection
  Name it something readily identifiable, "Main SQL Connection" for example
  Select the database type
  Enter the string, with any variables necessary already filled in.
  
  When you save the string, Campaign will automatically test the connection. You may have to tweak the syntax of your string as prompted to successfully connect.
• If you are in a multi-user environment, make sure you assign the users and groups that have access to this connection.
  

Create External Email List:
You can now create a new external email list, using this connection string. All available connection strings will show in the drop down and you would select the one you need for a particular list.

• Click Email List
  
• Click Add New Email List
• Name the list something readily identifiable "Sales Followup" for example
  
• Select External and click continue
  Select the database connection from the list of available connections
  Select the table, SQL Statement option, Stored procedure or use a filter in the project by selecting "From Campaign"
  Match the columns shown, Bounce, Unsubscribe, ID and Email
  Then save the Email List
• Enter a Bounce SQL statement if you want one.
• Set the permissions for this connection
  

There are other options available for more advanced list configuration should you need it. Please contact technical support for more information. The email list will appear in the campaign to address field for those users with permission to connect to this list.

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.

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.

As your database grows in size and complexity, it may affect the performance of your email marketing campaigns. Frequently, performance issues appear to some people as a sudden change, causing them to blame the program for the issue, when the problem may be much deeper. One of the best ways to improve database and campaign performance is to index your fields, especially the fields you are using for merging and updating.

According to Wikipedia
A database index is a data structure that improves the speed of data retrieval operations on a database table     at the cost of slower writes and increased storage space. Indexes can be created using one or more columns     of a database table, providing the basis for both rapid random look ups and efficient access of ordered             records. The disk space required to store the index is typically less than that required by the table (since             indexes usually contain only the key-fields according to which the table is to be arranged, and exclude all the     other details in the table), yielding the possibility to store indexes in memory for a table whose data is too         large to store in memory.

In a relational database, an index is a copy of one part of a table. Some databases extend the power of indexing by allowing indexes to be created on functions or expressions. For example, an index could be created on upper(last_name), which would only store the upper case versions of the last_name field in the index. Another option sometimes supported is the use of "filtered" indexes, where index entries are created only for those records that satisfy some conditional expression. A further aspect of flexibility is to permit indexing on user-defined functions, as well as expressions formed from an assortment of built-in functions.

When I worked on running dive meets (springboard and platform diving) for my daughter, we had a system where people would turn in dive sheets based on age brackets, gender, and diving level.  At first, we had just one inbox for the divers to turn in their dive sheets. With a total of 50 divers we could answer the questions of how many girls vs. boys are there, how many 10-11 years olds, how many novice or junior Olympic levels are there. We had to cycle through the whole pile to get those questions answered each time, but we could answer the questions pretty quickly. As the season progressed, we started having more divers come, and when it go up to 500 divers, having the one in box was crazy and it took a lot of time to answer those questions.

In the same way, using a database, a small number of records within a table can be managed pretty efficiently without any indexing.  When a question is asked of the database (filtering), the database system cycle throughs ALL of the records noting which records should be included based on the filtering criteria (ie: State='CA').  The time it takes to do this is unnoticeable until about 10 thousand records or so.  Now you start noticing a lag time, but, it is still acceptable and you are too busy to deal with it.  Time moves on, the number of records in your table is increasing (which is good!) and now your waiting 10-15-20 seconds. Then someone imports 50,000 records into the table and boom, you start getting timeouts.  You say, "this worked this morning!  And I did not change anything."  You have just crossed over into the "Indexing Zone."
 
These types of issues sneak up on you unless you have a completely static list of people to send email too. It will happen!  Now even the most seasoned database programmer can get caught in this trap.  The only way to really make sure things are in tip-top shape is awareness and maintenance.

Indexing is kind of like presorting.  Say you have a table with basic contact information in it (name, city, state, etc.).  When you add a record to the table without having any indexing, all you are doing is simply appending a piece of data onto the end of the table, no order, no nothing. Contact your database administrator today to start indexing your data and improve your email marketing performance. 

By: Chris Lewis

We are asked frequently "what database should I use?"  Now since the question is being asked, usually that means you don't have one and the field is wide open. Though you can convert to other database types later, it is better if you can look at your future needs and plan based on that.  This might take a bit more cost and setup, but if you are planning on growing your lists and abilities, it is the best course.  The following is a list of databases with their advantages and disadvantages

Microsoft Access - This is a "file" based database which is a great start and may actually be all you need.  If you are primarily sending out emails, don't have a ton of responses events, than this database is great.  We have actually seen it work for many years for large situations.  An MS Access file is very stable, easy to backup (one file), and portable.  The disadvantages is that with heavy use the file can become corrupted but very rarely.  You do need to compress this file so that the database does not get too inflated, especially if you are adding and deleting a lot of records.  Some people have used an Access database for the database back-end for Websites, and that may be OK, but if it is used with an active webserver and Campaign Enterprise at the same time there can be a chance of deadlocks (trying to access the same info at the same time). Since many processes may be accessing the database file at one time without "each other's knowledge", there is the potential for collisions.  BUT, don't worry about this until it happens.  Access is a great solution and will be support by Microsoft for many years to come.  MS Access is not free but it is usually $100 or part of the MS Office Professional package.

Microsoft SQL Server - This system is a true client-server database. Since the server takes requests from many different processes and lines them up in a queue, these requests are handled in an orderly fashion and avoids a lot of the problems that a single-database file has.  Advantages are that the SQL server can be installed on the same or a different computer than the one running Campaign Enterprise so you can distribute the workload.  It also allows you to have your webserver, database, and email sending system separated.  We always recommend you use an SQL server like this whenever possible.  This system can cost from $995 and up for MS SQL Server 2012.

Microsoft SQL Server Express - This free database functions just like the full SQL server above, but they have a database volume size limitation and it will only use on CPU core.  The limitations of the Express versions change each release, but many will find this system to be more than sufficient.

MySQL - This free database is created and maintained by the public domain.  Many people use MySQL with Campaign Enterprise successfully.  Most of the time people use ODBC to connect to the database but there have been OLEDB providers available too. MySQL will use multiple cores on your computer and there is not a limit on the database volume size like MS SQL Express.   If you don't like Microsoft products and want a good free SQL database then MySQL is a good choice.

Oracle 11g - Oracle works well with Campaign Enterprise. You will need to be experienced setting up database connections using their ODAC connector.  You can use a personal version of Oracle for free.  The full versions of Oracle have a lot of different pricing structures so it is hard to quote.

DB2 (AS 400)  - This database is for IBM mainframes. Campaign Enterprise has connected to this type of database but it is mostly used large companies with established systems.

Many Others - Campaign Enterprise can connect to any database that has an ODBC or OLEDB connector.  We have successfully seen it work with Foxpro, Filemaker, and even text-based files. These databases many times do not allow for outside writebacks so if you are trying just read information from a database than these will work but they are not recommended.

Here is a sample MS Access database mdb file you can use.

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.

There are instances in an email marketing campaign where you may need to compile customer information out of several tables in your database, and have it appear in a dynamically formatted list in the email message.

Using standard merge fields is possible, but this often results in unwanted spaces or line breaks. The way to manage multiple data displays effectively is to use a stored procedure to concatenate the data prior to inclusion in the merged email message.

We will assume that you have a table that contains the Customer's unique ID from your regular customer table, the name of the product purchased, the name of the customer and the email address. I have named my table PurchaseDetail and the schema looks like this:

CREATE TABLE dbo.PurchaseDetail
(
ProductID int NULL,
CustomerID int NULL,
ProdName varchar(100) NULL,
CustName varchar(100) NULL,
Email varchar(100) NULL
) ON [PRIMARY]
GO

Note: There is no primary key and no default values for fields. One row is inserted for each purchased item.

We will use a stored procedure to create a row in the NewPurchases table for each unique CustomerID in the PurchaseDetail table. The stored procedure will then process each row of the PurchaseDetail table and update the Purchases field of the NewPurchases table and append the purchased item for that customer to any existing purchased items for that customer.

NewPurchases:

CREATE TABLE dbo.NewPurchase
(
CustID int NULL,
CustomerName varchar(100) NULL,
Email varchar(100) NULL,
Purchases varchar(8000) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.NewPurchase ADD CONSTRAINT
DF_NewPurchase_Purchases DEFAULT '<br>' FOR Purchases
GO
COMMIT

Note: There is no primary key and the Purchases field has a default value of <br> so the purchased items appear in a column in your HTML email message. Also, the limitation of this field is 8000 characters. If you have more than 8000 characters of product sold to one customer, you will have to think of an alternative to this stored procedure. When this stored procedure is run, you will be able to merge the Purchases field into your Campaign Enterprise message body and have a list of products purchased by one customer. You will also have the customers name to use as a merge field in your message such as, 'Dear {CustomerName}', to personalize your message. The email address and unique id to use on the Data Source page of your campaign are also available.

Please consider another limitation of this solution. This solution is a one-time option. For instance, a scenario where you need to send more than one email to a specific customer, you will need to consider the filters to employ to have the correct list of products merge into the Purchases field. Using a date/time field is the easiest, by allowing you to filter the PurchaseDetail table on only products sold within a time frame e.g. the last month and the email is sent once a month.

Stored procedure BuildContent:

CREATE PROCEDURE dbo.BuildContent
AS
DECLARE @CustID int
DECLARE @PurchaseItem varchar (100)
DECLARE @Email varchar (100)
DECLARE @CustomerName varchar (100)

SET NOCOUNT ON

Insert into NewPurchase (CustID) (select distinct CustomerID from PurchaseDetail)

DECLARE BuildCursor CURSOR FOR
SELECT CustomerID, ProdName, CustName, Email FROM PurchaseDetail

OPEN BuildCursor
FETCH NEXT FROM BuildCursor INTO @CustID, @PurchaseItem, @CustomerName, @Email

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE NewPurchase SET CustomerName = @CustomerName, Email = @Email, Purchases = Purchases + @PurchaseItem + '<br>' WHERE CustID = @CustID
FETCH NEXT FROM BuildCursor INTO @CustID, @PurchaseItem, @CustomerName, @Email
END
CLOSE BuildCursor
DEALLOCATE BuildCursor
GO

This is just one example of using a stored procedure with Campaign Enterprise. Stored procedures are not for everybody, as you can see they are quite technical and require a lot of trial and error. Once created however, stored procedure calls on your database can greatly reduce data management workload and improve overall email marketing efficiency.