Hi everyone:
Marketing has asked me to pull a report showing how many unique buyers we have (see output set values below). I've managed to pull the info the problem is I'm now having to clean over 10,000 records due to the way Tessitura outputs each sub-line item in an order. I have T-Stats newly installed but haven't been able to dive in yet. Any suggestions for a very frazzled TSM? I'm now on my second day of cleaning this amongst three people using a shared workbook. While the data appears to be correct as I spot check there are some that are not.
They want to know out of the entire database:
1. How many have not purchased
2. How many have made a purchase and how many shows and tickets i.e., 1 show 2 tickets, 2 shows 4 tickets and so on.
3. Repeat buyers which is really answered by #2 above.
Marketing is finding it difficult to fathom that I'm not able to push a button and get this. Has anyone had success pulling this type of list.?
I've done an output set using as follows:
Customer_fname
Customer_lname
Ticket Season
Tickets-Number of Seats
Tickes-Number of Unique Performances
This is code that will get you what you want via MS Access connected using ODBC if you have MS Office set up & the permissions.
It could also be converted easily to SQL if you have someone there who can do it. Hope this helps! I use dbo_T_PERF.Season ID 58 which is our current year ticket sales. Yours will be different, of course.
Good Luck!
SELECT dbo_T_ORDER.customer_no, dbo_T_PERF.perf_no, Count(dbo_T_SUB_LINEITEM.seat_no) AS Seats, dbo_T_CUSTOMER.fname, dbo_T_CUSTOMER.lname
FROM ((dbo_T_ORDER LEFT JOIN dbo_T_SUB_LINEITEM ON dbo_T_ORDER.order_no = dbo_T_SUB_LINEITEM.order_no) LEFT JOIN dbo_T_PERF ON dbo_T_SUB_LINEITEM.perf_no = dbo_T_PERF.perf_no) LEFT JOIN dbo_T_CUSTOMER ON dbo_T_ORDER.customer_no = dbo_T_CUSTOMER.customer_no
WHERE (((dbo_T_PERF.season)=58) AND ((dbo_T_SUB_LINEITEM.sli_status)=2 Or (dbo_T_SUB_LINEITEM.sli_status)=3 Or (dbo_T_SUB_LINEITEM.sli_status)=12))
GROUP BY dbo_T_ORDER.customer_no, dbo_T_PERF.perf_no, dbo_T_CUSTOMER.fname, dbo_T_CUSTOMER.lname;
Thanks David. That's a fantastic solution. We're in a consortium so it's not possible for us. I guess I'm stuck with scrubbing thousands of names for now!!!