Repeat ticket buyers for the same production?

I think what you want here is a list: look under the "Ticketing" criteria.  Ticket History Prod Season will give you your run, and then Ticket History Unique Perfs >= 2 should show you all the people who bought tickets to more than one performance of that event.  Now, that will just give you the people.  You can use Output Sets with filters on Production Season to show what their actual shows and ticket purchase amounts were.

Hi Gawain, thank you for this. I don't think my list is accurate, though - the query seems to be pulling people who came to Joseph who have ticket history of more than two Broadway Rose performances, rather than more than two Joseph performances. Am I doing it wrong? 

Sure enough: looking at the query it's an absolute mess.  What could possibly be going on here, anyone?

SELECT DISTINCT a.customer_no
FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
       INNER JOIN
       (SELECT   a1.customer_no
        FROM     vs_tck_hist AS a1 WITH (NOLOCK)
        GROUP BY a1.customer_no
        HAVING   count(DISTINCT CONVERT (CHAR (12), a1.performance_dt) + a1.matinee_or_evening) >= 2) AS e
       ON e.customer_no = a.customer_no
       INNER JOIN
       (SELECT a1.customer_no
        FROM   vs_ticket_history AS a1 WITH (NOLOCK)
        WHERE  a1.prod_season_no IN (17659)) AS f
       ON f.customer_no = a.customer_no
WHERE  a.inactive = 1

Hello,

Not sure about that query, but this is the query I've been using for this purpose. Just change the prod_season number, it counts the number of customers with more than one unique perf_no for that prod_season.

SELECT DISTINCT o.customer_no
FROM T_SUB_LINEITEM li (nolock)
JOIN T_ORDER o (nolock) ON o.order_no = li.order_no
JOIN T_PERF pr ON pr.perf_no = li.perf_no
JOIN T_INVENTORY iv ON iv.inv_no = pr.prod_season_no
WHERE li.sli_status IN (3, 12)
AND o.customer_no > 0
AND pr.prod_season_no = 17383 -- change prod season number here
GROUP BY o.customer_no
HAVING COUNT(DISTINCT li.perf_no) > 1
ORDER BY o.customer_no;

Maybe I am missing something - it looks like this will do the same as Gawain's query above. Isn't this showing people who bought tickets to more than one production in a single season, not more than one performance in a single production? 

I could not figure out how to do what I wanted within Tess, but I did find the answer to my question using a basic list pull, a pivot table, and some excel math:

I created a list of everyone who attended Joseph and filtered the output set of attendance date by the dates of the Joseph run. Then I exported to a spreadsheet, merged exact duplicate rows, and created a pivot table of Tess ID versus attendance date with the interior value as the count of attendance date. I did a summation of each row (total number of attendances per constituent) and COUNTIF to find the number of households that attended more than 1, 2, 3, and 4 times.

Shouldn't there be a way to do this with Analytics in Tessitura and not have to use Excel??

Yes, you can. I'm using tips from Chris Wallingford and Heath Wilder.

Example of using this method but only counting constituents that meet criteria.

1. ticket paid amount >= $200 per order
2. count of customers that have done that

SUM ( [Constituent ID],
    IF (
        SUM( [Order ID], IF ( [Total Ticket Paid Amount] >= 200 , 1, NULL )
       )
    > 0 , 1 , NULL)
)



Think of it as if it meets the criteria, then return a 1, if not a 0, and then finally sum up all the 1's to give you the count.

Does that make sense?

Okay, so I was able to do it  with with the following: Ticketing Performance Name, Tickets-Number of Unique Perfs, and Ticket Season.  For us the Performance Name and the Season can be used to define the production season, although the it's a dumb way to have to do it.

Basically, the "Ticketing" criteria set is very problematic.  As a rule, everything in one criteria set should be looking at the same view: this allows you to use multiple criteria knowing that they will be applied to the same query.  But when they use different views, as Ticket History Prod Season and Ticket HIstory Unique Perfs do (VS_TICKET_HISTORY and VS_TKT_HIST respectively), then queries are disconnected, and so you basically evaluate which customers apply to each criteria separately.

I do think Jerry's query would work for you: basically he says give me all the tickets purchased for this production season, group by customer, and limit to customers with two or more distinct performance numbers in that set.

So, you should be able to do what you want within Tessitura, I think you might be able to, but that criteria set is something of a mess and given its importance I do think Tessitura should take steps to clean it up and improve it.

Hey! No it will show customers with more than one unique perf_no in a specific prod_season. Do you have access to the SQL server? If you do, you can run the below query which will include a column of the amount of unique perf no's associated with the prod season no. You can then sense check it against constituent ID's :-)

select o.customer_no,
COUNT(distinct li.perf_no) "perf_count"
from T_SUB_LINEITEM li (nolock)
join T_ORDER o (nolock)
on o.order_no = li.order_no
join T_PERF pr
on pr.perf_no = li.perf_no
join T_INVENTORY iv
on iv.inv_no = pr.prod_season_no
where li.sli_status in (3,12)
and o.customer_no > 0
and pr.prod_season_no = 17383 -- change prod season number here
group by o.customer_no
having COUNT(distinct li.perf_no) > 1
order by COUNT(distinct li.perf_no) desc

An addendum: I think "Ticket History Unique Perfs" has had it's definition fixed in v16 to point to the right view, and this is something trivially done to T_KEYWORD in v15.

Hi Chelsea,

As Neil is suggesting, an Analytics formula might get you there... With a filter set to the Joseph production season...

SUM ( [Constituent ID] ,
  IF ( COUNT ( [Performance ID] ) >= 2 , 1 , 0 )
)

This looks at every customer holding tickets to Joseph, and only if they attended 2 or more performances of the show, then count them in the overall total.

I have the same issue as Chelsea and I'm trying to apply this solution, but my output seems wonky. Any idea what's going on here?

Yeah, you may not want Order ID on rows there, as your limited to cases where the constituent has more than 1 performance in the same order. Also, with Constituent ID on rows, the multi-pass aggregation doesn't work as you'd expect. Instead, just do the IF statement...

IF ( COUNT ( [Performance ID] ) >= 2 , 1 , 0 )