Hi all,
Longtime lurker, rare participant, but I get a lot out of this forum and I have an issue that's becoming pressing enough that I can't ignore it anymore. I have a question in two parts!
The first: is there a good resource for learning more about coding in Tessitura Analytics? I use Google Sheets, Excel, and some SQL most of the time, so it shouldn't be too technically difficult, but I haven't found anything that really helps me with Analytics. For instance (and this is the second and related issue):
There's a lot of interest in my organization about tracking membership: how it's changing over time, who moves into and out of and between our various membership levels, etc. In the past, the way I've been tracking this is to simply create a table in Analytics that just lists every constituent, their membership level, the initiation, expiration, and inception date for that member and member level, and then doing all the actual work on that table in a spreadsheet program (usually, much to my chagrin, Google Sheets because most of the organization does its work in the Google suite). That's fine when I'm tracking information from a few months at a time, but I'm being asked to do analysis over years. This is proving far too much data for our meager little spreadsheets to handle, and just trying to generate running lists of exactly how many members we had, at each level, on each date for the last few years turns into something that takes 48 hours to run. Obviously that's not tenable! What I'd love to do is just generate a pivot table using the calendar date as the row, the membership levels as the columns, and the count for each membership level *at that date* as the value. But I have no idea how to code in Analytics to tell it "give me the number of unique constituents who had a membership at this membership level where the intitiation date was <= the row date and the expiration date > row date." This seems like it should be simple, but I'm stuck! Any help you can provide would be hugely appreciated. Thank you!
Hi Justin,
I really want others to chime in on your first question too, but a couple of thoughts:
For your second question...
Justin Walters said:This seems like it should be simple
It does. And I have spun my wheels trying to produce a front-end formula approach to this need many times. But to date, the only solutions have been hard-coding membership start and expiration dates into formulas and filters, rather than a dynamic solution that can change from date to date in your widget.
Building a "Membership Active Date (!)" dimension into Analytics is technically simple as a customization, but because such a dimension can lead to errors if used incorrectly, we have not made it a standard part of the Memberships data source. There is a path to that being a standard dimension, but there are a number of Analytics platform changes needed to support it. It's on the roadmap though, and if anyone were interested in posting a Community Idea, that might be helpful as well.
Thanks, Chris. Those links are very helpful! It seems wild that something so obviously useful and simple is apparently impossible in Analytics, but oh well. Excel and SQL always end up saving the day when the shiny new toys break.