How to build an Abandoned Cart automated campaign list

Send automated abandoned cart emails to logged in patrons who left tickets in the cart. Use triggered campaigns in WordFly to send automated campaigns.

 

We’ve put together a basic example of creating the necessary database tables and procedures in Tessitura for abandoned cart emails! Follow the steps in this post to get started. If you need additional help, please reach out to Tessitura Support for expert advice.

 


Topics covered in this article:

Best practices for abandoned cart emails

Building abandoned cart emails in Tessitura

How to setup a Tessitura automated campaign in WordFly

 

 

Best practices for abandoned cart emails

Your abandoned cart message should be a polite/customer service oriented message reminding the purchaser of the items in their cart.

 

Abandoned cart email content considerations

  • Don’t forget personalization.
  • Echo the cart visually, make the email look like their cart, it reminds them of their experience on the site.
  • (if applicable) Show complete total of purchase.

 

Abandoned cart email schedule considerations

In WordFly, an automated abandoned cart email can run once a day to notify online shoppers of items left in the cart. The campaign should be scheduled to send after the AbandonedCart SQL Server Agent Job has run in the database. Be sure to coordinate the timing with your team helping in the database. We recommend the campaign send daily and for one year. Create a new abandoned cart email campaign every year to compare campaign performance year over year and update email designs.

 

 

 

Building the abandoned cart list and output set criteria in Tessitura

 

While it is relatively easy to identify abandoned carts, it takes a little database configuration for list and output set criteria to access the data. By using a combination of a local data table in Tessitura and a scheduled job to populate it, you can create simple List Manager lists and output sets for even the most complex scenarios.

 

What to keep in mind:

  • Only carts associated with customers who have logged in can be targeted. Carts associated with anonymous login cannot be tied to a specific customer.
  • T_Web_Order holds all web sessions and T_Order holds all completed orders. Comparing the two tables allows us find the abandoned cart.
  • The list will return unique customers with abandoned carts. Each customer could have more than one abandoned cart. Referencing specific cart contents will add complexity.

 

The local data approach uses a scheduled job to populate local data tables, rather than relying on the List Manager list generation to do all the work. With a scheduled job there are no constraints on the type of sql you can use. This means your scheduled job can execute a stored procedure which coordinates the entire process for identifying customers with abandoned carts and associating appropriate data with those customers. All of the complexity is kept within the stored procedure.

 

On a regularly scheduled basis, a SQL Server Agent job executes the abandoned cart stored procedure. This procedure contains all the logic for defining abandoned carts and populating the abandoned cart table with all of the customer and associated data needed in the output set. The list criteria and output set then return customers and data from the abandoned cart table.

 

TESSITURA SETUP TASKS

 

First, complete the database installation to create new database table, function and stored procedure. Scroll to the end of this article to download the setup scripts. Reach out to Tessitura support if you need assistance with any of these steps.

 

In Tessitura

1. Run the AbandonedCartLocalDataObjects.sql script in the impressario database to create local data objects.

 

This script will create the following database objects in impressario:

 

LT_WF_ABANDONED_CART

Table that stores abandoned cart data. This table will store the customers with abandoned carts as well as any data needed in the output set.

 

LP_WF_ABANDONED_CART

Stored procedure that populates LT_WF_ABANDONED_CART with abandoned cart data.

 

LFT_WF_ABANDONED_CART

Table Function that returns customers from the abandoned cart table. The List Manager list runs this function as its criteria.

 

2. Run the AbandonedCartScheduledJob.sql script to create the scheduled job.

 

The new job will be created in SQL Server Agent under the Jobs folder and will be called AbandonedCart.

 

The AbandonedCart job will be configured in step 3. The job will executes LP_WF_ABANDONED_CART procedure once a day to keep LT_WF_ABANDONED_CART data current (the old data populated from the last run is removed and replaced with new data).

 

3. Modify the AbandonedCart job in SQL Server Agent to set the appropriate schedule.

 

Creating scheduled jobs through SQL Server Agent is easy. Jobs can execute any SQL server task at any time.

 

In this step you will tell the AbandonedCart job (created in step 2) to run at a set time every day. Follow these steps:

 

  1. Open SQL Server Agent.
  2. Click open the Jobs folder.
  3. Right-click the AbandonedCart job, select Properties.
  4. In the Job Properties window, from the left navigation select Schedules.
  5. Select New from the bottom of the window.
  6. Name your job and set a schedule for the job to run.
  7. Click OK from the Job windows to finish the job schedule.

 

The example below shows the job name and the schedule set to run daily with an end date 1 year in the future.

 

 

 

Next, setup your Tessitura list and output set criteria

 

These steps will take you through setting up your abandoned cart list and output set criteria in Tessitura. Reach out to Tessitura support if you need additional help at any point.

 

1. Create a List Manger list that simply selects from the LFT_WF_ABANDONED_CART function.

 

From the navigation in Tessitura, select Tools > List Manager, select New and use the Query option to write your own criteria. The criteria should select customers from the LFT_WF_ABANDONED_CART function. Click Save when done.

 

Example criteria,

 

select distinct customer_no from dbo.LFT_WF_ABANDONED_CART(1)

 

In v14 Tessitura, next to Add Criteria, select Show Query to write your criteria.

 

 

2. Create an output set that returns data from LT_WF_ABANDONED_CART table.

 

For this step, you will need access to system tables to add a new query element group (TR_QUERY_ELEMENT_GROUP) to define where the data is (LT_WF_ABANDONED_CART) and several new query elements (TR_QUERY_ELEMENT) to define the data to return.

 

TR_QUERY_ELEMENT_GROUP

 

Create one new query element group for Abandoned Cart data.

 

Column Name Example Value
Description Abandoned Cart
Data From LT_WF_ABANDONED_CART
Data Where  
Virtual Ind N
Category Other

 

TR_QUERY_ELEMENT

 

Create several new query elements depending on the data you want to return from LT_WF_ABANDONED_CART. Follow this example format for each element you create:

 

Column Name Example Value
Description Abandoned Cart - order_no
Group ID Abandoned Cart
Data Select !.order_no
Control Group Select default, unless consortium
Single Row Y
Primary Group Default N

 

Sample data returned from LT_WF_ABANDONED_CART

 

[customer_no]
,[order_no]
,[order_dt]
,[orderdetail1]
,[orderdetail2]
,[orderdetail3]
,[datecreated]

 

Once the list manager list and output set have been created you can finish setting up your automated campaign in WordFly.

 

 

 

How to setup a Tessitura automated campaign in WordFly

Once you are done setting up your list and output set criteria in Tessitura you can start working on setting up your automated campaign in WordFly. Learn more

 

FINDING ABANDONED CARTS FOR TESTING

 

The following sql finds customers with an abandoned cart created within the last day. An abandoned cart is defined as a cart where there is no order associated with the cart and no other order has been created by the customer after the cart was created. Use this script to find subscribers who have abandoned carts as you finish testing your triggered campaign setup.

 

select distinct customer.customer_no

from t_customer customer (NOLOCK)

join dbo.T_WEB_ORDER cart (NOLOCK)

on customer.customer_no = cart.customer_no

left outer join dbo.T_ORDER cart_order (NOLOCK)

on cart.order_no = cart_order.order_no

left outer join dbo.T_ORDER customer_order (NOLOCK)

on customer.customer_no = customer_order.customer_no and customer_order.order_dt > cart.order_dt

where (cart_order.order_no is null)

and (customer_order.order_no is null)

and (cart.order_dt >= DateAdd(dd,-1,Convert(varchar,GetDate(),112)+' 23:59:59'))

 

 

 

Have more questions? Submit a request