First time ticket buyers query

I've been asked to create a report that will show which patrons are first time ticket buyers.  This is a new request and for future orders we will be using a value in the channel drop down of the order to identify them.  However I need to go back to the beginning of the season to identify those that are already in this group.  So there are two questions:

1) Is this the kind of thing T-Stats could identify (we don't have T-Stats but I am trying hard to build a case for us getting it).

2) What is the best place to start in the database.  t_tck_hist doesn't populate until the performance has happened which doesn't work for this because they want to send out a "newcomers kit" prior to the show they purchased.  Is there somewhere else to look for purchases.  I'm assuming I can do some work with t_order:

select customer_no,  min(order_dt) from t_order where min(order_dt) > @start_date

And then determine the performances through the sub line items.  But is there a simpler way?

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

I've been asked to create a report that will show which patrons are first time ticket buyers.  This is a new request and for future orders we will be using a value in the channel drop down of the order to identify them.  However I need to go back to the beginning of the season to identify those that are already in this group.  So there are two questions:

1) Is this the kind of thing T-Stats could identify (we don't have T-Stats but I am trying hard to build a case for us getting it).

2) What is the best place to start in the database.  t_tck_hist doesn't populate until the performance has happened which doesn't work for this because they want to send out a "newcomers kit" prior to the show they purchased.  Is there somewhere else to look for purchases.  I'm assuming I can do some work with t_order:

select customer_no,  min(order_dt) from t_order where min(order_dt) > @start_date

And then determine the performances through the sub line items.  But is there a simpler way?

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

We've not quite solved this either but my approach (when I get time to do it) will be to store in a local table (e.g. ltx_cust_first_perf) a customer number and a performance number (being the performance number of the first performance they purchased). This table, I envisage, would be updated via a nightly script that simply finds the min(seat_tran_dt) from lt_tkt_hist and collects the performance number relating to that transaction. Our lt_tkt_hist updates nightly and adds all transactions that have happened to date (whether the performance has happened or not).

Once that table is populated, for any given production season, I would first collect all the performance numbers together that belong to that production season, take that into the local table and ask for all the customer numbers that have one of those performance numbers as their first performance number:

e.g. (have written this quickly so can't guarantee it would work but you get the gist)

 

 

select

customer_no

from

 

 

 

ltx_cust_first_perf x

 

where

 

 

x.perf_no in (select perf_no from t_perf where prod_season_no = xx

)

Now I've written it out it seems quite simple.

Hope this stimulates some thinking.