I'm looking to report in Analytics on donations that came in via our TNEW cart donation ask path. A couple of weeks ago on a call, a suggestion was made to use the finance cube to find this information by filtering it to show any contributions with an order ID > 0. This works really well if you're trying to show in total what came in. What I would like to do is show this information based on what performance they also had in their order and short of a list that generates each night with ticket history I'm out of ideas on how to show this! Anyone run into this and have a solution?
Good comments on this thread, and yeah, this is a sticky one. As I read it, you want to filter on orders that contain a certain thing, and then show more than just that thing about those orders in the output. In this case, show me all contents of orders with TNEW card donation. I think Nathanael is on the right track, in that we could filter to [Order ID] that have a [Contribution Amount] > 0 and came in through the web. What follows may not be performant. More on these latter two points in a bit.
Here are some orders from my Finance cube with their Contribution and Ticket Paid Amounts and Production Seasons:
Note the first Order ID of -999999 having a giant Contribution Amount... these are the Contributions not in orders. Within the scope of these first 10 rows, none of the Orders have Contributions in them.
If we add a filter on [Order ID], set to use a Ranking type, returning the TOP let's say 1000 (a number greater than the number you expect in the results), by a formula like:
( [Max Order ID] , [Contribution Amount] , [Mode of Sale Category] )
Then when adding in [Contribution Amount] from the formula Data Browser, select More... Filter, and change it to a Value type filter using > 0. This would be enough to limit the results to those rows with contributions, but wouldn't remove that -999999 row. So we add [Mode of Sale Category] or just [Mode of Sale], selecting just your Web mode of sale, which will pull out that -999999, as well as any contributions made in orders not via the web.
The most important characteristic of this formula in the end, is that it results in a NULL [Order ID] for [Order ID] that don't meet the Contribution Amount and Mode of Sale criteria. In the following results, we're now limited to true Orders in the Web mode of sale, that have a Contribution Amount > 0, and we are seeing the complete contents of those orders, not just the contribution money.
A couple notes about this. I didn't filter the Ranking formula on [Order ID] itself being > 0 because filtering a formula by what we're grouping by breaks out what we're grouping by. So if I'd tried
( [Max Order ID] , [Contribution Amount] , [Order ID > 0] )
It's like saying, within each Order ID give me the Max Order ID from all Order ID > 0 that have Contribution Amount > 0, and that result will be the same for all Order ID. So for any Order ID, be it -999999 or 32767, it would break out of the bounds of that one Order ID and get the Max Order ID from all Order ID with Contribution Amount > 0, and all Order ID would still be in scope.
There's a known issue with using an IF or CASE statement in this scenario to produce a NULL result for a given set of circumstances. It won't work and I'm hoping that will be resolved in v16. E.g. filtering on Order ID with at least $1,000 worth of tickets:
IF ( [Total Ticket Purchase Amount] > 1000 , 1 , NULL )
Finally, if you find this filter doesn't perform well and the widget/dashboard takes a long time to load, that's expected... this filter is like saying, WHERE [Order ID] IN (15,24,53,94,...) and having a list of 1000 or more raw ID values. So this is just one more tool in your toolkit that like other tools, will sometimes save the day, and sometimes leave you looking for another approach.
I hope it works out for you!
Thanks all! Walking through this example made me realize that I may not be able to use Analytics to accurately share the information being asked...they want to know what contributions came in attached to a free event. Since the event has no cost the production information doesn't live in the finance cube. I think that since we only have the one show it will be fairly easy to filter the information based on the Mode of Sale but as soon as we do another free event I don't think I'll be able to easily track this unless we manually go in and change the source or something on the contribution.
Madeline, I was following your question, as I've tried to do some similar reporting on contributions through various types of ticket orders. I see this as one of the current shortcomings of the "cube" approach with which SiSense approaches data. Whereas Tessitura merges several aspects of a customer into a single account (attributes, tickets, donations, constituencies, payment methods, etc.), Analytics tends to separate these elements into "cubes", which I don't believe can communicate with one another. You're raising an issue I've also encountered, i.e. examining the same customer through 2 different cubes, but not being able to compare those 2 features via Analytics.
We were just chatting and it's interesting because the bi.VT_ORDER_DETAIL view has a bunch of fun things like Production Season and Performance whilst it's good friend bi.VT_ORDER has tot_ticket_purch_amt as well as tot_contribution_amt which makes it really easy to get in SSMS from the business intelligence views if you have a pal with SQL access or that can lend you the code
Update - my current solution is to pull from SSMS into Google Sheets and then toss an iFrame widget in with the gross number in a chart on one sheet and a breakdown of the dollar amount gifts in another sheet. For the time being, this will get updated once a week.
Another solution, which I am avoiding as to not add to someone else's plate (for now), would be to recode those gifts with a source of that particular free event.