Trying to find tickets purchased for consecutive days?

Hi Amy,

I have two approaches for you. One from Analytics and the other using Lists. In Analytics I set a dashboard filter on Season, and created a Pivot widget with Constituent ID on Rows. Then added Values for unique counts of Performance ID (for performances) and Performance Dates. The latter to help with distinguishing people with 3 performance on 2 dates from those with 3 performances on 3 dates. Then to filter on constituents with 3 performances on 3 different dates, I could either filter on # of Unique Performance ID = 3 and filter on # of Unique Performance Days in Date = 3, or set up another value shown here that flags up those cases, and then filter on that value equaling 1.

For the Consecutive vs Non aspect of this, I added a couple values for the MIN and MAX performance dates for each constituent. These will be limited by the Season filter selection from the dashboard. I used the Performance Date (Calendar Year * 10,000 + the Calendar Month * 100 + the Day of Month) to turn the performance date into a number. Then I created a value to take the difference between the MAX and MIN dates. I don't have any in my data, but at this point I could filter on that difference to values = 2 (or maybe 3, not sure how the math works here) for the Consecutive buyers, and then <> 2 (...or 3) for the Non-Consecutive buyers.

To turn this into counts of constituents, you might add a Value for # Unique Constituent ID, which will be 1 for each row, but with a Grand Total visible, will return the total counts. If you need to turn this into something more KPI-like, that you can more easily monitor day to day. Then we'd need to convert the values above into a formula, grouped by Constituent ID, combining the 3 Performance, 3 Dates, and 3 Days Difference criteria like so:

SUM ( [Constituent ID] ,
 IF (
   [# of unique Performance ID] = 3 AND [# of unique Days in Date] = 3
   AND (
     MAX([Calendar Year]*10000 + [Calendar Month]*100 + [Calendar Day Of Month])
     - MIN([Calendar Year]*10000 + [Calendar Month]*100 + [Calendar Day Of Month]) = 2
   )
 , 1 , 0
 )
 )

This states that for each Constituent ID, SUM a 1 IF for that constituent there are exactly 3 unique Performance ID, 3 unique Performance Dates, and the difference between the MAX and MIN Performance Dates is = (or <>) to 2. Again, I have no Consecutives in my data, but that's the idea.

ConsecutiveDates.dash

From Analytics you can create a Tessitura list from that first Pivot widget, but this second Pie value won't pass through the Value formula filters and create the list you'd expect. However, if you need a dynamic list that you don't have to manually recreate when needed, then setting something up in Lists may better meet your needs. In Lists, we can't quite get there with standard elements, and would need to edit the SQL of the list criteria. If you or someone is comfortable with that, I would start with a list like this, using Ticket History Season and Ticket History Unique Perfs HH = 3.

Looking then at the query for that shows this...

SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
 INNER JOIN
 (SELECT hv.customer_no
 FROM vs_ticket_history AS a1 WITH (NOLOCK)
  INNER JOIN
  V_CUSTOMER_WITH_HOUSEHOLD AS hv
  ON a1.customer_no = hv.expanded_customer_no
  WHERE a1.season IN (146, 147, 148)
  GROUP BY hv.customer_no
  HAVING count(DISTINCT a1.perf_no) = 3) AS e
 ON e.customer_no = a.customer_no
WHERE a.inactive = 1

Which I'd edit to also count of distinct perf_dt (with the time stripped out) = 3, and use the MIN and MAX perf_dt to limit to those HH where the difference between those dates is 2 or is not 2. My additions are in bold here:

SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
 INNER JOIN
 (SELECT hv.customer_no, MIN(a1.perf_dt) as min_perf_dt, MAX(a1.perf_dt) as max_perf_dt
 FROM vs_ticket_history AS a1 WITH (NOLOCK)
 INNER JOIN
 V_CUSTOMER_WITH_HOUSEHOLD AS hv
 ON a1.customer_no = hv.expanded_customer_no
 WHERE a1.season IN (147, 146, 148)
 GROUP BY hv.customer_no
 HAVING count(DISTINCT a1.perf_no) = 3
 AND count(DISTINCT CAST(a1.perf_dt AS DATE)) = 3
 ) AS e
 ON e.customer_no = a.customer_no
WHERE a.inactive = 1
AND e.max_perf_dt = DATEADD(dd,2,e.min_perf_dt)

I hope you're doing well. All the best,
Chris

Hi Amy,

I have two approaches for you. One from Analytics and the other using Lists. In Analytics I set a dashboard filter on Season, and created a Pivot widget with Constituent ID on Rows. Then added Values for unique counts of Performance ID (for performances) and Performance Dates. The latter to help with distinguishing people with 3 performance on 2 dates from those with 3 performances on 3 dates. Then to filter on constituents with 3 performances on 3 different dates, I could either filter on # of Unique Performance ID = 3 and filter on # of Unique Performance Days in Date = 3, or set up another value shown here that flags up those cases, and then filter on that value equaling 1.

For the Consecutive vs Non aspect of this, I added a couple values for the MIN and MAX performance dates for each constituent. These will be limited by the Season filter selection from the dashboard. I used the Performance Date (Calendar Year * 10,000 + the Calendar Month * 100 + the Day of Month) to turn the performance date into a number. Then I created a value to take the difference between the MAX and MIN dates. I don't have any in my data, but at this point I could filter on that difference to values = 2 (or maybe 3, not sure how the math works here) for the Consecutive buyers, and then <> 2 (...or 3) for the Non-Consecutive buyers.

To turn this into counts of constituents, you might add a Value for # Unique Constituent ID, which will be 1 for each row, but with a Grand Total visible, will return the total counts. If you need to turn this into something more KPI-like, that you can more easily monitor day to day. Then we'd need to convert the values above into a formula, grouped by Constituent ID, combining the 3 Performance, 3 Dates, and 3 Days Difference criteria like so:

SUM ( [Constituent ID] ,
 IF (
   [# of unique Performance ID] = 3 AND [# of unique Days in Date] = 3
   AND (
     MAX([Calendar Year]*10000 + [Calendar Month]*100 + [Calendar Day Of Month])
     - MIN([Calendar Year]*10000 + [Calendar Month]*100 + [Calendar Day Of Month]) = 2
   )
 , 1 , 0
 )
 )

This states that for each Constituent ID, SUM a 1 IF for that constituent there are exactly 3 unique Performance ID, 3 unique Performance Dates, and the difference between the MAX and MIN Performance Dates is = (or <>) to 2. Again, I have no Consecutives in my data, but that's the idea.

ConsecutiveDates.dash

From Analytics you can create a Tessitura list from that first Pivot widget, but this second Pie value won't pass through the Value formula filters and create the list you'd expect. However, if you need a dynamic list that you don't have to manually recreate when needed, then setting something up in Lists may better meet your needs. In Lists, we can't quite get there with standard elements, and would need to edit the SQL of the list criteria. If you or someone is comfortable with that, I would start with a list like this, using Ticket History Season and Ticket History Unique Perfs HH = 3.

Looking then at the query for that shows this...

SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
 INNER JOIN
 (SELECT hv.customer_no
 FROM vs_ticket_history AS a1 WITH (NOLOCK)
  INNER JOIN
  V_CUSTOMER_WITH_HOUSEHOLD AS hv
  ON a1.customer_no = hv.expanded_customer_no
  WHERE a1.season IN (146, 147, 148)
  GROUP BY hv.customer_no
  HAVING count(DISTINCT a1.perf_no) = 3) AS e
 ON e.customer_no = a.customer_no
WHERE a.inactive = 1

Which I'd edit to also count of distinct perf_dt (with the time stripped out) = 3, and use the MIN and MAX perf_dt to limit to those HH where the difference between those dates is 2 or is not 2. My additions are in bold here:

SELECT DISTINCT a.customer_no
FROM V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
 INNER JOIN
 (SELECT hv.customer_no, MIN(a1.perf_dt) as min_perf_dt, MAX(a1.perf_dt) as max_perf_dt
 FROM vs_ticket_history AS a1 WITH (NOLOCK)
 INNER JOIN
 V_CUSTOMER_WITH_HOUSEHOLD AS hv
 ON a1.customer_no = hv.expanded_customer_no
 WHERE a1.season IN (147, 146, 148)
 GROUP BY hv.customer_no
 HAVING count(DISTINCT a1.perf_no) = 3
 AND count(DISTINCT CAST(a1.perf_dt AS DATE)) = 3
 ) AS e
 ON e.customer_no = a.customer_no
WHERE a.inactive = 1
AND e.max_perf_dt = DATEADD(dd,2,e.min_perf_dt)

I hope you're doing well. All the best,
Chris