Sending abandoned cart emails is one of the best ways to connect with your customers using relevant messaging that keeps purchasers engaged and buying.
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:
It usually takes about 2 emails to see success with purchasers who abandoned their online shopping cart. For the best results, consider this approach:
- Your first message should be a polite/customer service oriented message reminding the purchaser of the items in their cart.
- Your second message should follow if you continue to have no response, there should be more of a “last chance” message that creates a sense of urgency.
Considerations for abandoned cart email content:
- Don’t forget personalization.
- Echo the cart visually, make the email look like their cart, it reminds them of their experience on the site.
- Show shipping (if applicable) and complete total.
- Show payment options.
While it is relatively easy to identify abandoned carts through, it is more difficult to define an output set that returns data associated with those abandoned carts. However, 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 let's 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 simply return customers and data from abandoned cart table.
TESSITURA SETUP TASKS
To configure abandoned cart emails in Tessitura for use with WordFly triggered email campaigns:
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. Run the AbandonedCartLocalDataObjects.sql script in the impressario database to create local data objects.
This script will create the following database objects in impressario:
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.
Stored procedure that populates LT_WF_ABANDONED_CART with abandoned cart data.
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.
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 case, a job should be set up that executes LP_WF_ABANDONED_CART procedure once a day to keep LT_WF_ABANDONED_CART data current.
These steps will take you through setting up your abandoned cart list and output set criteria. Reach out to Tessitura support if you need additional help at any point.
1. Register LT_WF_ABANDONED_CART table with Query Builder so that it can be used when building output sets.
This step simply means to create an output set element that references the LT_WF_ABANDONED_CART table.
2. Create 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.
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.
3. Create Output Set that returns data from LT_WF_ABANDONED_CART table.
Once the list manager list and output set have been created you can finish setting up your triggered email campaign in WordFly.
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'))