I have a solution, but it’s not an elegant one. I agree that this is an area lacking in solid analytics.
Here’s something you could do out of a google sheet / Excel365 sheet with the help of an automation tool like Zapier. I’m going to reference Google Sheet going forward, as that’s what I use. But I imagine you could automate the same thing with Excel365 and Zapier or similar tools.
Part 1: Initial Backfill
- For all your groups, export the roster to CSV. The member since column is when they joined the group.
- Upload the CSV into a new google sheet, appending each to the bottom of the previous roster.
- As you upload/append, add (and fill) columns for the Group ID# and Group Name.
- Add a column for Date Left
- Add any other columns on user demographics you might want (optional)
- You’ll need to do the manual work for the initial backfill, but can automate for joins/leaves
The end result is a sheet with all of your group rosters, that can be sorted by group Id or group name. And if you add other user attributes, you can sort by that tool.
Part 2: Automate Updates when users join/leave
There are webhook events for when users join and leave a group.
I use Zapier, but there are other tools that could allow you to do a similar version of what I have below.
Zapier: Automation for Users Joining a Group
- Trigger: Webhooks by Zapier > Catch a Hook
- When you select Test, Zapier gives you a URL that you will need to subscribe to the webhook
- group.MemberJoined
- (I use Postman to run the API call that subscribes the url to the event)
- Actions:
- Gainsight CC > Find a User (optional)
- Google Sheets > Add Spreadsheet Row
- Populate the data in your spreadsheet with fields from 2.1 & 2.2
The end result is your initial spreadsheet is updated every time someone joins a group.
Zapier: Automation for Users Joining a Group
- Trigger: Webhooks by Zapier > Catch a Hook
- When you select Test, Zapier gives you a URL that you will need to subscribe to the webhook
- group.MemberLeft
- Actions:
- Google Sheets > Lookup Spreadsheet Row
- Find the user who left by UserId
- Google Sheets > Update Spreadsheet Row
- Populate the date they left by inserting {{zap_meta_human_now}} to insert the current date & time
Alternatively, you could do an automation similar to when people join, adding a new row to a sheet when people leave. It all depends on how you want to do your reporting (Part 3).
The end result is that when someone joins, that row in the spreadsheet is updated in the Date Left column.
Part 3: Pivot Tables and Charts in Google Sheets
Now you have a data set in your spreadsheet that you can report on via pivot tables and charts.
These should be able to be set up in ways where they update with the new data that comes in. And you’ll also have the user attributes you added as other columns, allowing you to show different types of users joining/leaving.
Depending on what and how you want to report on and visualize the joins/leaves, you may want to separate out the Leave records into their own specific sheet. I put a note on that in the steps for that automation.
---
I takes a bit of initial set up, but it gets you to the point where you can report and chart to whatever extent you’d like.