EDIT: 10 Nov 2020. I made some upgrades to this solution since originally blogging. They are listed at the bottom
Recently Microsoft released a new feature to Teams allowing you to download attendees of your meeting.
Today at our Community Café user group meeting where I was acting as moderator, I noticed this change and started downloading the attendees. It's a very basic, but useful CSV file containing the name of the attendee and a timestamp they joined or left the meeting.
I have always wanted to see a end-to-end view of attendees as numbers move up & down throughout the meeting as people join, leave and sometimes re-join again. It has never been possible to know how many unique attendees there were unless you were fixated watching people leave and join and did the tallying in real time. Nobody's gonna do that!
With 4 minutes to go in the meetup, I did another download. Unfortunately, after that I was focused on wrapping up the meeting, saying goodbye to everyone then I promptly pressed End Meeting for All, at which point I couldn't get the last few mins of data. All the same, I plugged it into Power BI Desktop and starting working some magic!
Here is what I built into the Power BI report:
Measures
Unique attendee count. This is anyone who joined the meeting, even for a fleeting visit.
Average Capacity. Between the leavers and joiners, what was the average attendance numbers throughout the meeting.
Maximum Capacity. What was the highest number of attendee at any point in the meeting.
Average time in call. People leave, join and leave again. I've aggregated the total time spent per person and averaged it out.
Charts
Live attendee tracker. This handy view gives a point-in-time view of attendee numbers, minute by minute.
Duration bucketing. I've bucketed attendees into how long they stayed in the meeting, so 0-9 mins, 10-19 mins, etc. Best case scenario is people who were in for close to 60 mins, meaning they attended the whole call.
Observations
In today's meeting, I noticed it took a full ten minutes after the start for the numbers to reach the highest. It's a great view of the arrival pattern of attendees.
I also noticed that numbers started dropping with 20 minutes left. Hard to say why, but as it's a free user group, in the middle of a work day, maybe folks had to move on.
People are leaving and joining constantly. If you've been running a Teams meeting I'm sure you noticed people coming & going. The line chart really plots that nicely!
Challenges
The DAX to calculate the average time in call had to ensure it counted each person once, combining any time they joined and left to get an overall average.
The live attendance number used a fairly standard running total algorithm, which calculates the sum of joiners minus the leavers up until the current timestamp in the filter context.
The bucketing of users into groups of time they were in the meeting also used a standard dynamic segmentation DAX design pattern.
One trick was to create a number of 1 or -1 as I loaded the data depending on whether the row indicated the attendee leaving or joining. This made for easier calculated measures!
Visual Design
I built out a first version of the report with a casual & friendly design. I used the older Teams purple, with some cartoon graphics I found from a Microsoft site. The longer I looked at it, the less I liked it. I started from scratch and built a new version. This one is based on the Teams App.
I've kept both though, so would love to hear your preference!
Option 1. Business Style
Option 2. Casual Style
Notice how the attendees don't drop back down to zero? As mentioned above, that's my fault for not downloading again before I ended the meeting.
All the details and a sample Power BI report are available for you to download at my Gallery page here: Microsoft Teams Attendee Analytics. All you need to do is point to your own file & refresh! Easy Peasy!
The data model relied on some funky DAX calculations so take a look and see how it was stitched together!
Upgrades, November 2020
Based on some user feedback, and personal experience at running events, I've changed the solution to allow multiple meeting files to be added. This works great if you're running multiple meetings in parallel and want a combined view.
To combine files, rename the csv with a title you want to see, place them all in a folder & change the parameter in the query. It will load & append them allowing you to switch between them.
Below is an example from a recent event I helped organise with three parallel tracks.
Direct link here, or embedded below
Hi appreciate this is an old post, but is there any guidance on how to utilise my own attendance data within the report structure, changing the source parameter to my own data dosen't seem to work despite the file formats being the same. Thanks!