• Replies 5 replies
  • Subscribers 962 subscribers
  • Views 102 views
  • Users 0 members are here
Related

People who bought tickets and also contributed

Sara Funk
$organization

Hi Team,

I'm trying to see, from 2018 to now, how many households purchased tix, and of that number, how many also made a gift. In Analytics - using the Seats & Tickets cube to get ticket information as well as contributed dollars - I'm creating a Bar or Column chart with Categories set to Season, Values set to # of unique constituent ID's, and then I want the Break by sections to be Tickets Only vs Tickets + Contributions. Is there a filter that's simply Tickets? = Yes and Contribution? = Yes ?

Thanks for the help.

Sara

Parents
  • $organization

    My first thought was this little widget I pulled together in the Finance Cube.

    It's looking at the number of orders where there was a contribution. 

    I think you'll need to set this up in the Finance cube, as the Seats and Tickets cube will not look at Contributions, and the Contributions Cube won't look at seats and tickets much. The trick is that Contribution Amount = 0 is NOT saying "there is no contribution". It is instead saying "There is an amount noted for a Contribution, and it's 0". In other words, the amount was backed out or refunded. I don't know if that works the same way for 'Ticket Paid Amount', but I imagine it would. The opposite of that are Orders where the Contribution Amount was NULL, which is to say "there is no contribution".

    You'd want something like "# of Order Ds where Contribution amount IS NULL or = 0" vs "# of Order IDs where contribution amount >0". I've been tinkering with Analytics for a bit now, but can't seem to figure out how to piece this together.

  • $organization in reply to Nathanael Pearson

    I think Nathanael is correct regarding using the Finance Cube.

    Instead of using the Break By, you can use a Sum If on the values. You would have 2 values each filtered on the formula level to show the two things you want to display. One of them would be the Count of constituents with a ticket paid amount > 0 and the other a count of constituents with a Contribution amount >0.

    For example,

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

    This equation basically says group by Constituent ID and then if the paid amount is > 0 count it as a 1. Then add up all the 1's to get your count. This assumes you already have Dashboard or Widget level filters to get your 2018 numbers.

Reply
  • $organization in reply to Nathanael Pearson

    I think Nathanael is correct regarding using the Finance Cube.

    Instead of using the Break By, you can use a Sum If on the values. You would have 2 values each filtered on the formula level to show the two things you want to display. One of them would be the Count of constituents with a ticket paid amount > 0 and the other a count of constituents with a Contribution amount >0.

    For example,

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

    This equation basically says group by Constituent ID and then if the paid amount is > 0 count it as a 1. Then add up all the 1's to get your count. This assumes you already have Dashboard or Widget level filters to get your 2018 numbers.

Children