Hi all,
I'm trying to pull together a list that contains all active members who've attended more than 5 times this year. I'd need this to be automated and run daily, otherwise I could draw something up in Analytics manually, and pull that into a list.
I'm able to get a list of Members who've attended 5 or more times across every year, but I can't figure out how to refine the list only to look at this year for the attendance.
The only way I can see would be to write out some custom SQL, but I don't have any experience drawing up something that complex.
As a rule of thumb (although not perfect), if you use multiple criteria from one criteria set, in the same list, those criteria should work with each other. So if you use both performance count and season from the same criteria set, you ought to get a result that is five attendances in that season. You can confirm that by looking at the query under manual edit.
To expand, in this example I'm using two values from the List Criteria Set "Ticketing":
And here in the query we can see that both conditions are being applied to the same "FROM" table:
Interesting, I will try this out- thanks! Will let you know of my progress.
I've gotten very close- thanks again! I am able to pull a list together of folks who've reserved tickets for 5+ performances, which is good.
I can pull a list of people who've attended 5+ times in total.
I can't seem to figure out how to pull a list together of folks who've attended 5+ separate performances. Still working on it.
If I understand, Unique Perfs isn't quite perfect because this would include anyone who has booked 5 performances, but you need to verify attendance?I don't see an out-of-the-box criteria element that pulls unique performances for attendance (from VS_ELEMENTS_ATTENDANCE). But if we steal the SQL logic from Unique Perf we can create a new criteria item in T_KEYWORD (by putting "count(distinct !.perf_no)" in Detail Col)The downside for you is that VS_ELEMENTS_ATTENDANCE has no column for season. Hopefully you can use Performance date to filter by season. That may not work if you have concurrent seasons but I hope this is the right direction for what you need.
Hmm, that is interesting! Thanks for the reply.Our Admission Production Seasons are only ever 1 per year, so adding in a filter for performance date (containing only this year) and using the new custom criteria could be doable. Let me see what we can tinker with on our end.