I have a tricky one. I'm trying to do this in Analytics, but I suspect I'm going to need to use SQL to get the end results I need. That said, let's give it a shot.
We have an Animal Sponsorship program whose broader effects we want to analyze. We spend $X and Y staff hours to achieve $Z in revenue, but it's also a gateway contribution, where we believe people who first engage on A/S are stronger candidates for prospecting for other contributions, as minor as $50 for an Annual Appeal all the way to being cultivated for our patron level memberships. To test this, I have three ideas that I don't know how to do:
1. Set three contribution types and three years, and find only people who have contributed to all three Funds in each of the past three years. In Analytics, I set this up as a Pivot with FY as a column, then Fund as a column, filtered Fund to the three I was interested in, and Const ID as a row, then set Contrib count and Contrib Total Amount as values. This works with a filter for Contrib Count > 0, but I'd like to apply that to all nine of those fields.
2. Find people whose first engagement with us was Animal Sponsorship and then track total average giving and average change in giving year to year.
3. Find people who have Animal Sponsorship and tickets within certain titles.
If anyone has any recommendations, I'd appreciate it.