• Replies 1 reply
  • Subscribers 415 subscribers
  • Views 73 views
  • Users 0 members are here
Related

Finding number of unique patrons in a season?

Tyler Kubat
$organization

Hello all!

We have hit upon a piece of data that we can't seem to figure out. Our organization (for grant writing purposes) would like to figure out a rough estimate of how many unique patrons purchased tickets to our shows. So, a season ticket holder who comes to all six productions would only really count as one person - but same with single ticket holders. So if they came to two of our productions under the same name, they still would count as just 1 unique patron. Meaning also that we'd need to find the show where they attended with the most people in their group. So if they attended production 1 with 27 people and production 2 with 12, we'd count the bigger number of 27 and partially assume that the 12 were the same people in both show scenarios.

Do any other organizations have a sort of process for this? I assume there is never a great way to get the real, exact number here, but we've been spending weeks on a spreadsheet in the past, which doesn't really do us any good.

Thanks for any help you can provide!

  • $organization

    Tyler,

    An interesting idea, and one I do not think that I have seen put forth quite like that before.  I suppose there may be some complex method of using custom query outputs to pull this data or maybe using T-Stats, but to me this just screams out to be a direct pull from the database.

    Given that, you are going to have a few things to consider.  Firstly, what to do about general public orders (i.e. customer_no = 0)?  Do ALL of those count?  Or none of them count since they cannot be quantifiably be included or excluded based on any criteria?  Or are they treated just as the others are, where the max ticket count for any given performance is your go to number?  Secondly, you have householding considerations that might come into play.  Do you transact everything as a household, or do orders go on indvidiual and household accounts alike?  Knowing that answer could be relevant to how you structure your query.  Obviously the assumption of a robust constituent merging process must be in play here as well.  It sounds like you want just any shows in a given season, but if you are picking and choosing shows between seasons, that obviously will affect the query as well.  Lastly, you state "production", do you mean "production" or "performance"?  Let us say Patron A attends the first performance of Production 1 with 10 people, the fourth performance of Production 1 with 9 and the fifth performance of Production 1 with 8 people, and then Patron A attends simply the first performance of Production 2 with 12 people.  Would Patron A count for 27 or 12?  Our organization is a presenting organization, so for us Production and Performance is virtually interchangeable as almost everything is a one shot deal, but that seems less likely for a Theatre company.

    Anyway, there are a lot of assumptions there, but, this piqued my interest, so I made some assumptions of my own and wrote up something which should get you most of the way there (other SQL people out there feel free to correct me if you see any errors I made below).  I assumed that all performances in a specific, given season were included.  I assumed all transactions were done as a household and, for the purposes of this query, we ignored recipient assignments as we would be counting those tickets anyway in the grand total.  I also assumed all general public orders should be excluded on the grounds of unreliability.

    Given all of that, something like this (below) should get you pretty close, where @season_id is the id from TR_SEASON of the season about whose data you are seeking:

    USE impresario

    DECLARE    @season_id INT = 174

    SELECT    o.customer_no,
            sli.perf_no,
            COUNT(sli.sli_no) AS perf_ticket_count
            INTO #initial_data
    FROM        T_SUB_LINEITEM sli WITH (NOLOCK)
        JOIN    T_ORDER o WITH (NOLOCK) ON sli.order_no = o.order_no
        JOIN    T_PERF p WITH (NOLOCK) ON sli.perf_no = p.perf_no
    WHERE    sli.sli_status IN (3,12)
        AND    o.customer_no <> 0
        AND    p.season = @season_id
    GROUP BY    o.customer_no,
                sli.perf_no
    ORDER BY    o.customer_no

    SELECT    i.customer_no,
            MAX(i.perf_ticket_count) AS total
            INTO #max_counts
    FROM    #initial_data i
    GROUP BY    i.customer_no

    SELECT    SUM(m.total) AS grand_total
    FROM    #max_counts m

    Best of luck in any case!

    John