Looking to get #of Gifts and $Total in Contributions broken out by Campaign Category (Event v Development) or with Campaign Selection
Then looking at # of seats and $ total for Packages in a Season
Then Separate # of seats and $ total in Single Tickets in a Season
Over a span of 5 different years -
So in the end 5 sets of each would be built... (Patron Loyalty Metric)
Y1 Contribution # & $, Y2, Y3, Y4, Y5
Y1 Event # & $, Y2, Y3, Y4, Y5
Y1 Package by Season # & $, Y2, Y3, Y4, Y5
Y1 Single Tickets by Season # & $, Y2, Y3, Y4, Y5
Y1 School Season # & $, Y2, Y3, Y4, Y5
Y1 Adult Class Season # & $, Y2, Y3, Y4, Y5
Good afternoon Mary,
With some work in TR_QUERY_ELEMENT and TR_QUERY_ELEMENT_PARAMETER, I'm pretty sure you can create specific elements to report much of what you're asking for here.
Obviously, with $ totals, you'll be using the sum functionality in Data Select to either sum(cont_amt) or sum(recd_amt) based on whether you want to count pledges and gifts or just actual money received, and you'll get Data From vs_contribution or vs_ticket_history depending on the type.
When it comes to gifts/seat counts, there is actually a pre-cooked Tickets - Num of Unique Perfs element where you Data Select count(distinct perf_no) to Data From vs_ticket_history. I imagine that similar code could be developed for the number of contributions as well.
Where things will start getting tricky will be how you segregate the data you're collecting. Under the Data Where column, I'd be actually hard-coding the specific request campaign_no = XX to pull the Campaigns that apply to that year; but, of course, that also begs the question of whether you want each year to be representative of giving to a fiscal year or a calendar year.
The same Data Where can apply to your Productions/Productions Seasons (however you want to pull your tickets/education enrollment data).
The Output Set Builder Cookbook also details coding for Total Tickets Purchased Per Season(s), Contribution FYear Amount, and Ticket FYear Amount.
Not sure if this is helpful or not, but hopefully it is!
Thank you,
Brian
Brian -
Thanks - that was where my brain was heading... just didn't have the parts lined up. I think with this being a year over year ongoing project (at least for the next 5 years) I might do some hard coding.... maybe after I get the first approved data set!
I've switched over to hard-coding a lot of values in our Output Set Elements lately just because of how it opens up comprehensive Output Sets since you can have multiple instances of a particular request split up by fiscal or calendar year. Takes a little extra work and makes the list of Elements a little less pretty, but I think the functionality is a solid trade-off!