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
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.
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.
Hi Sara,
When you say "also made a gift", do you mean within the same order as the purchase, or just generally made a gift at some time? In the former case a lot of it will depend on your configuration and processes, and you will likely need to use a different cube (see Nathanael's post for an example). In the latter case you many be able to use either the contribution info supplied in Seats and Tickets or you might want to use Analytics enabled lists (if so, be aware of this caveat).
--Gawain
I realized just now I was tunnel-visioning on Order IDs, when that wasn't what Sara wanted! My bad.I think that makes things easier, and I like Neil's thoughts.How would we tell Analytics "Show me everyone who hasn't given a gift, but has bought tickets?"
Hi Gawain,
GREAT Q. I am indeed speaking about the latter. In 2018, I want to know people who purchased tickets at some time and also gave a contribution at some time, not necessarily together. I'll jump back in and create a list of what I'm looking for - in fact it makes more sense in my brain to pull a list of to find this, but I need a visual representation to present to others - and see what it does when enabled for Analytics. Thank you for that idea.