My Marketing contact was in a meeting with our web developers and asked them if he could get from them a report that would show who logged into the website (so account holders, but not necessarily subscribers), filled their cart, but ultimately didn't buy anything. The developers told him that all of that information is kept in Tessitura.
Of course, they neglected to tell him in what tables such information was kept. Now I know that T_CUST_LOGIN would show the logins and the last login date, but if an order wasn't completed, is it likely that this information is written to tables in Tessitura?
What say you, Tessiturians?
Essentially it is stored in T_WEB_ORDER. I have some abandoned cart SQL floating around here from somewhere, but we haven't used it in any capacity yet. Maybe you can adjust it to your liking...
select customer.customer_no,max(cart.order_no) as order_no, max(cart.order_dt) as order_dt
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 >= @cutoffDate)
group by customer.customer_no
Beth
Matt,
You can also get information about the timestamps and duration of the sessions from T_WEB_SESSION_SESSION (no, that's not a typo :) ).
- Levi