How to Concatenate Data in Email Messages with Campaign Enterprise

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.