Automated Abandoned Cart email

This article serves as a starting point for your Tessitura list and output set criteria. Please refer to the Tessitura Help System for expert guidance: v15 | v16

Send automated abandoned cart emails to logged in constituents who left tickets in the cart. 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 article to get started.

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, so 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.

Build 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 List Manager lists and output sets for even the most complex scenarios.

• 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. 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.

1. In Tessitura, run the AbandonedCartLocalDataObjects.sql script in the impresario database to create local data objects

This script will create the following database objects in impresario:

• 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

Scheduled jobs created in SQL Server Agent 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.

Set up 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 Manager list that 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)

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.

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:

Sample data returned from LT_WF_ABANDONED_CART

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

Set up automation in WordFly

After you have set up your list and output set criteria in Tessitura, you can set up your automation in WordFly.

Learn more

Find 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'))

• AbandonedCart.zip 5 KB