• Replies 3 replies
  • Subscribers 701 subscribers
  • Views 76 views
  • Users 0 members are here
Options
Related

Count of constituents who bought tickets for shows in one venue

Michael Dorsey
$organization

Hello!

I'm trying to figure out the number of people who bought tickets to shows in one specific venue, then see the count of constituents for each venue.

In pivot table format, it would look like this:

Venue Name # of Constituents w/ Shows in Venue ONLY
Venue A 475
Venue B 225
Venue C 100

And a grand total would be folks who only had performance(s) in one unique venue.

I have tried bucketing & case, but I keep getting the number of people with at least one show in the venue, not people with only this venue in their ticket history. I can get at the number when I do an extraction, but cannot match that number in Analytics. So many of you have helped me so many times that I feel like I should have the tools I need to get to the right number, but alas, I'm stuck. Any help is greatly appreciated.

Thanks,

Michael

Parents
  • $organization

    Hi Michael,

    I'm assuming you have a filter to limit to just certain venues. We can count constituents within a venue row only if the unique count of venues within the whole widget for that constituent is 1, meaning the row they're in is the only row they're in. The grand total will respect that as well.

    SUM ( [Constituent ID] ,
      IF ( ( [# unique Venue Name] , [Venue Name filter IN A, B or C] ) = 1 , 1 , 0 )
    )

    If your filter is on something other than Venue Name (e.g. all venues are displayed within say a selected Season), then the formula can be more robust and responsive to filter changes, without having to edit the formula filter on Venue Name, like:

    SUM ( [Constituent ID] ,
      IF ( ( [# unique Venue Name] , ALL ( [Venue Name] ) ) = 1 , 1 , 0 )
    )

    Best,
    Chris

Reply
  • $organization

    Hi Michael,

    I'm assuming you have a filter to limit to just certain venues. We can count constituents within a venue row only if the unique count of venues within the whole widget for that constituent is 1, meaning the row they're in is the only row they're in. The grand total will respect that as well.

    SUM ( [Constituent ID] ,
      IF ( ( [# unique Venue Name] , [Venue Name filter IN A, B or C] ) = 1 , 1 , 0 )
    )

    If your filter is on something other than Venue Name (e.g. all venues are displayed within say a selected Season), then the formula can be more robust and responsive to filter changes, without having to edit the formula filter on Venue Name, like:

    SUM ( [Constituent ID] ,
      IF ( ( [# unique Venue Name] , ALL ( [Venue Name] ) ) = 1 , 1 , 0 )
    )

    Best,
    Chris

Children
  • $organization in reply to Chris Wallingford

    Thank you, Chris! I did not have the venue name filter after the count. I just had a venue filter on the widget. Thanks for taking the time to help me!

    I might be pushing my luck, but any chance you have an easy way to make this work in the finance cube to identify anyone who only purchased one specific performance? I tried using the same logic you've given here (but using performance name and performance ID), but I am getting anyone with that performance in an order. I'm trying to get anyone with only one unique perf ID, for that one specific performance. We have to use the finance cube because this performance was cancelled.

    Thanks again!

    Michael

  • $organization in reply to Michael Dorsey

    Hi Michael,

    If you're counting Performance ID, we'll need to make sure you're counting across all Performance names on rows (assuming that's all you have on rows).

    SUM ( [Constituent ID] ,
      IF ( ( [# unique Performance ID] , ALL ( [Performance Name] ) ) = 1 , 1 , 0 )
    )