Meeting Data Aggregation SOP

graph LR
  A[Cal.com API] --> E[Meeting Normalization]
  B[Calendly CSV] --> E
  C[TidyCal CSV] --> E
  D[Google Calendar] --> E
  E --> F[Contact Attribution]
  F --> G[Meeting Classification]
  G --> H[Portfolio Review Detection]
  H --> I[Metrics Calculation]
  I --> J[LACRM Field Updates]

1 Purpose

Aggregate meeting data from all booking platforms and calendar systems to provide unified client interaction metrics, portfolio review tracking, and engagement analytics for relationship management and compliance reporting.

2 Triggers

• Scheduled: Every 4 hours via automated pipeline integration with LACRM sync
• Manual: Operator execution for immediate metrics refresh or troubleshooting
• Event-driven: New client onboarding, booking platform changes, calendar integration updates

3 Inputs

• Cal.com API: Real-time booking data with attendee details and meeting metadata
• Calendly Exports: Historical CSV exports with event details and participant information
• TidyCal Data: Manual CSV exports from secondary booking platform
• Google Calendar: Meeting events from configured calendars with attendee lists
• Contact Index: Email-to-contact-id mapping for accurate attendee attribution

4 Steps

4.1 1. Multi-Platform Data Collection

• Cal.com Integration: API calls with cursor pagination to retrieve recent bookings
• CSV Processing: Automated detection and parsing of Calendly/TidyCal export files
• Calendar Sync: Google Calendar API integration for additional meeting context
• Data Validation: Format verification and completeness checks for all data sources

4.2 2. Meeting Data Normalization

• Schema Standardization: Unified meeting format across all platforms
• Timestamp Conversion: UTC normalization for accurate scheduling analysis
• Duration Calculation: Consistent meeting length computation from platform-specific formats
• Status Mapping: Standardized meeting status (confirmed, cancelled, completed, no-show)

4.3 3. Contact Attribution

• Email Matching: Map attendee emails to LACRM contact IDs using contact index
• Fuzzy Logic: Handle email variations, aliases, and name inconsistencies
• Confidence Scoring: Assign attribution confidence levels for quality assessment
• Orphan Detection: Identify meetings with unattributable attendees for manual review

4.4 4. Meeting Classification

• Portfolio Reviews: Detect formal portfolio review meetings based on duration, participants
• Check-ins: Identify regular client communication meetings vs. transactional calls
• Onboarding: Classify new client meetings and initial consultation sessions
• Internal Meetings: Filter out team meetings and administrative calls

4.5 5. Metrics Calculation

• Meeting Frequency: Meetings per contact over trailing 90, 180, 365 days
• Last Meeting Date: Most recent meeting timestamp per contact
• Portfolio Review Status: Last formal review date and next due date calculation
• Communication Cadence: Average time between meetings for relationship health

4.6 6. Quality Assurance

• Duplicate Detection: Identify and merge duplicate meetings across platforms
• Data Consistency: Cross-validate meeting data against calendar systems
• Attribution Validation: Verify contact matching accuracy and flag ambiguous cases
• Temporal Validation: Check for impossible meeting sequences or scheduling conflicts

5 Exceptions

5.1 Contact Attribution Failures

• Unknown Attendees: Email addresses not found in contact index
• Response: Generate report for contact database maintenance
• Temporary Handling: Store unattributed meetings with email for later resolution

5.2 Platform Integration Issues

• API Failures: Cal.com API unavailable or rate limited
• CSV Missing: Expected export files not found in designated directories
• Data Quality: Malformed or incomplete meeting records from any platform

5.3 Meeting Classification Errors

• Ambiguous Meetings: Cannot determine meeting type or purpose
• Duration Conflicts: Meeting length doesn’t match expected patterns
• Resolution: Default classification with manual review flag

5.4 Duplicate Meeting Detection

• Cross-Platform Duplicates: Same meeting appears in multiple systems
• Time Overlap: Meetings with same participants and overlapping times
• Deduplication Logic: Prefer Cal.com data, then Calendly, then TidyCal, then Google Calendar

6 Owner Handoffs

• Data Engineering → Client Experience for contact attribution decisions and meeting classification
• Client Experience → Compliance for portfolio review frequency validation
• Data Engineering → Platform Admin for booking platform configuration issues

7 SLAs

• Automated Processing: Complete within 45 minutes of scheduled start
• Manual Execution: Complete within 30 minutes for immediate needs
• Error Resolution: Automatic retry within 15 minutes, escalation at 2 hours
• Data Currency: Meeting metrics reflect platform changes within 4 hours

8 Controls

• Contact Privacy: Only process business meeting attendees, exclude personal appointments
• Data Validation: Verify meeting timestamps, durations, and attendee formats
• Platform Authentication: Secure API key and OAuth token management
• Output Validation: Ensure metrics calculations are mathematically consistent

9 Audit Artifacts

• Processing Logs: Detailed execution logs with timing and error information
• Attribution Reports: Contact matching success rates and orphaned meeting analysis
• Quality Metrics: Duplicate detection rates, classification accuracy, data completeness
• Change Tracking: Meeting count changes per contact and platform contribution analysis

10 Business Intelligence

• Client Engagement: Meeting frequency patterns identify at-risk relationships
• Portfolio Compliance: Review scheduling ensures regulatory requirement adherence
• Operational Efficiency: Platform usage analytics inform booking system optimization
• Relationship Health: Communication cadence metrics support proactive client management

11 Technical Dependencies

• Contact Index: Accurate email-to-contact mapping essential for attribution
• Platform APIs: Reliable access to Cal.com, Google Calendar, and booking platforms
• File Management: CSV export workflow for Calendly and TidyCal integration
• LACRM Integration: Metrics feed custom field updates in CRM system

12 Monitoring & Alerts

• Processing Success: Meeting count changes, new contacts detected, platform contributions
• Quality Indicators: Attribution success rate, classification accuracy, duplicate detection
• Performance Metrics: Processing duration, API response times, error recovery success
• Business Impact: Portfolio review compliance, client engagement trend analysis

13 FAQs

How are personal meetings excluded from business metrics? Platform-specific filtering based on event types, attendee domains, and meeting duration patterns. Personal calendar events are excluded by calendar configuration.

What happens when the same meeting appears on multiple platforms? Deduplication logic prioritizes Cal.com (most recent/accurate), then historical platforms, using attendee emails and timestamps for matching.

How are cancelled or rescheduled meetings handled? Cancelled meetings are excluded from frequency calculations but preserved for scheduling pattern analysis. Rescheduled meetings are tracked as single interactions.

Can meeting classification be customized for different client types? Yes, classification rules can be adjusted based on LACRM contact tags, account types, or custom field values to reflect different service models.