• Replies 1 reply
  • Subscribers 332 subscribers
  • Views 62 views
  • Users 0 members are here
Related

Advice for data in analytics/reporting

Anisha Thomas
$organization

Long time lurker and first time poster here.

We have a fundraising campaign we are hoping to get underway by mid-Feb and there have been some data requests, one of which I am unsure how to produce. The request is:

Audience members 2020, 2021, 2022 who have not been affected by cancellations (those with cancellations were given options to rebook, get a credit, a refund or asked to donate their ticket value). 

I am unsure whether it would be better to run a report or do this in analytics - does Tessitura record this kind of information? It is my understanding that once a ticket is returned it won't be in analytics so that wouldn't be a great way to report on it. We didn't use a separate fund unfortunately so if they chose to donate we wouldn't know. 

Thanks in advance team!

Anisha

Parents
  • $organization

    Hi Anisha,

    I recommend creating a List element in the Ticketing category, or perhaps in a new List Category for Cancelled Events, that will return constituents who held tickets to events cancelled and refunded using the Event Cancellation Utility. 

    For example, in the System Table: Attributes and Keywords (T_KEYWORD)

    Add a row with description: Cancelled Event Date

    Detail Tbl: (SELECT CONVERT(DATE,perf_dt) perf_dt, customer_no FROM dbo.T_EVENT_CANCELLATION_UTILITY_LOG a JOIN dbo.T_EVENT_CANCELLATION_UTILITY_LOG_DETAIL b ON a.id=b.log_id JOIN dbo.T_PERF p ON a.perf_no=p.perf_no)

    This feels easiest to me because we can select a date range of event cancellations.

    Alternatively, we could create a list element for selecting explicit events rather than an event date range.

    Add a row with description: Cancelled Event

    Detail Tbl: (SELECT perf_no, customer_no FROM dbo.T_EVENT_CANCELLATION_UTILITY_LOG a JOIN dbo.T_EVENT_CANCELLATION_UTILITY_LOG_DETAIL b ON a.id=b.log_id)

    Detail Col: !.perf_no

    Ref Tbl: dbo.T_PERF

    Ref IDCol: perf_no

    Ref DescCol: CAST(perf_dt AS varchar) + ' ' + perf_code

    Ref Where: perf_no IN (SELECT perf_no FROM dbo.T_EVENT_CANCELLATION_UTILITY_LOG)

    Ref Sort: perf_dt DESC

    Then, arms with a list of constituents who were refunded, you can use a List criteria with Not In to exclude these constituents from your list of audience members. 

Reply
  • $organization

    Hi Anisha,

    I recommend creating a List element in the Ticketing category, or perhaps in a new List Category for Cancelled Events, that will return constituents who held tickets to events cancelled and refunded using the Event Cancellation Utility. 

    For example, in the System Table: Attributes and Keywords (T_KEYWORD)

    Add a row with description: Cancelled Event Date

    Detail Tbl: (SELECT CONVERT(DATE,perf_dt) perf_dt, customer_no FROM dbo.T_EVENT_CANCELLATION_UTILITY_LOG a JOIN dbo.T_EVENT_CANCELLATION_UTILITY_LOG_DETAIL b ON a.id=b.log_id JOIN dbo.T_PERF p ON a.perf_no=p.perf_no)

    This feels easiest to me because we can select a date range of event cancellations.

    Alternatively, we could create a list element for selecting explicit events rather than an event date range.

    Add a row with description: Cancelled Event

    Detail Tbl: (SELECT perf_no, customer_no FROM dbo.T_EVENT_CANCELLATION_UTILITY_LOG a JOIN dbo.T_EVENT_CANCELLATION_UTILITY_LOG_DETAIL b ON a.id=b.log_id)

    Detail Col: !.perf_no

    Ref Tbl: dbo.T_PERF

    Ref IDCol: perf_no

    Ref DescCol: CAST(perf_dt AS varchar) + ' ' + perf_code

    Ref Where: perf_no IN (SELECT perf_no FROM dbo.T_EVENT_CANCELLATION_UTILITY_LOG)

    Ref Sort: perf_dt DESC

    Then, arms with a list of constituents who were refunded, you can use a List criteria with Not In to exclude these constituents from your list of audience members. 

Children
No Data