Case Study · Retail
Defacto PAT: meeting analytics that survived the move from Teams to Meet
A dashboard producing attendance, talk-time and meeting-efficiency metrics from online meeting data. Built on Microsoft Teams' relational data; when the company moved to Google Meet, Meet's nested event data was mapped to the Teams schema and the dashboard continued unchanged. Row-level security means everyone sees only their own hierarchy.
- 2
- Platforms
- Daily
- Refresh
- RLS
- Privacy
- 2
- Phases
Teams, then Meet, in one schema
SSIS package, written to the log structure
Everyone sees their own hierarchy
Teams build · Meet migration
Problem
Meetings are one of a company’s biggest invisible costs. How many meetings are held, how many people attend, how long they last, whether cameras are on, whether invitees show up? Until these are measured, anyone trying to change meeting culture has only impressions to go on. Defacto wanted a dashboard that measures this from online meeting data and shows each manager their own team’s habits.
Phase 1: Teams
Microsoft Teams data came as a relational structure of call records, sessions, scheduled meetings, invitees and media (camera, audio) tables. A daily SSIS package took these tables and turned them into a meeting warehouse in the DSO’s database. The hard part was the join with HR data: who reports to whom, in which unit. A loop was added to find the current manager for employees whose manager has left. Matching problems caused by Turkish character variations were solved by normalising user names.
Every ETL step writes to the DSO’s log structure; which step failed and when is visible in a single query.
Phase 2: Moving to Meet
When the company moved from Microsoft Teams to Google Meet, we chose to keep the dashboard as it was and change only its data source, rather than rewrite it from scratch.
Meet data looks nothing like Teams’: a nested event stream in BigQuery with hundreds of columns. The calendar block holds every calendar action and the Meet block every meeting event as a separate row. This stream was transformed into Teams’ relational meeting–session–participant schema; HR data was moved into the same project so the mapping happens in one place. In the end the two platforms’ data merged in one warehouse, in one format, and the dashboard kept working unchanged.
Privacy
Meeting data is personal data. The dashboard applies row-level security (RLS): each user sees only the hierarchy below them: an employee sees themselves, a manager their team, a director their directorate.
Outcome
PAT made meeting efficiency traceable by person, manager, unit and department, and the platform change did not break the report’s continuity. Lumtify built the data infrastructure, the ETL packages and the dashboard. Mapping data whose source changes onto a fixed schema became our standard approach in later business intelligence work.