Media & Entertainment: Audience Analytics on Microsoft Fabric¶

Executive Summary¶

Business Context¶

A streaming media platform operates a hybrid monetization model with three tiers:

• Free (AVOD): Ad-supported, limited catalog access, 2.1M users
• Basic: Ad-free, standard quality, 3.4M subscribers at $9.99/mo
• Premium: 4K HDR, offline downloads, family profiles, 2.5M subscribers at $15.99/mo

The platform generates approximately 2 billion playback events daily across web, mobile, smart TV, and gaming console clients. Content spans original productions, licensed movies, live sports, and a growing kids library subject to COPPA regulations.

Key Business Questions¶

1. Which content titles drive the highest engagement and lowest churn?
2. How do we optimize ad placement in AVOD without degrading watch time?
3. What is the true cost-per-viewing-hour for licensed vs. original content?
4. Which subscriber segments are at risk of churning in the next 30 days?
5. How do viewing patterns differ across age-gated (COPPA) profiles?

Architecture Overview¶

                    Playback Clients (Web/Mobile/TV/Console)
                                    |
                            Event Collector API
                                    |
                        Azure Event Hubs (32 partitions)
                                    |
                    +---------Fabric Eventstream---------+
                    |                                     |
            Eventhouse (KQL DB)                    Lakehouse (Delta)
            Real-time engagement                   Medallion pipeline
            dashboards, alerts                     Bronze -> Silver -> Gold
                    |                                     |
                    +----------Power BI Direct Lake-------+
                                    |
                    Audience Dashboards & Recommendation API

Data Flow¶

1. Ingest: Playback clients emit events (play, pause, seek, stop, heartbeat) via Event Collector API to Azure Event Hubs.
2. Real-Time Path: Fabric Eventstream routes events to Eventhouse for sub-second KQL queries powering live engagement dashboards and anomaly detection (e.g., buffering spike alerts).
3. Batch Path: Eventstream also lands raw events in the Bronze lakehouse layer as Delta tables partitioned by date.
4. Silver Layer: Events are sessionized (30-minute gap = new session), quality-scored, and bot traffic is filtered.
5. Gold Layer: Aggregated content performance, user engagement segments, and content affinity matrices feed Power BI and the recommendation engine.

Medallion Architecture¶

Bronze: Raw Playback Events¶

Table: bronze_media_events

Volume: ~2B events/day, ~60B events/month Partitioning: _bronze_load_date Retention: 90 days raw, then archive to cold storage

Silver: Sessionized Viewing¶

Table: silver_media_sessions

Transformations: - Sessionization: events grouped by user_id + content_id with 30-minute inactivity gap - Bot filtering: sessions < 5 seconds or > 24 hours excluded - Heartbeat deduplication: collapse consecutive heartbeats within 15-second window - Quality score: weighted combination of bitrate stability, rebuffer ratio, startup time

Gold: Content Performance¶

Table: gold_content_performance

Gold: User Engagement Segments¶

Table: gold_user_segments

Segment Definitions: - Power: 20+ hours/month, 15+ sessions, completion > 70% - Casual: 5-20 hours/month, 5-15 sessions - At-Risk: < 5 hours/month declining, or 14+ days inactive - Dormant: 30+ days since last session

Analytics Use Cases¶

1. Audience Engagement Analytics¶

Objective: Understand viewing behavior to optimize content scheduling and personalization.

Key Metrics: - Watch Time per Session: Average and P95 session duration by content genre - Completion Rate: Percentage of content watched to completion (critical for series) - Binge Score: Consecutive episodes watched in a single sitting (3+ = binge) - Time-to-First-Play: Latency from app open to first playback event

KQL Real-Time Query (Eventhouse):

PlaybackEvents
| where EventTimestamp > ago(1h)
| summarize
    ActiveViewers = dcount(UserId),
    AvgBitrateKbps = avg(BitrateKbps),
    BufferingEvents = countif(EventType == "buffering")
  by bin(EventTimestamp, 1m), ContentId
| order by ActiveViewers desc
| take 20

Business Impact: Content teams use engagement data to greenlight Season 2 renewals. Titles with > 75% completion rate and > 40% binge score receive priority renewal consideration.

2. Content Recommendation Engine¶

Objective: Improve content discovery using collaborative filtering enhanced with content-based features.

Approach: - Collaborative Filtering: User-content interaction matrix factorized via ALS (Alternating Least Squares) in PySpark MLlib - Content-Based Features: Genre, cast, director, release year, content rating, duration - Hybrid Score: 0.7 * collaborative + 0.3 * content-based, with COPPA-safe filtering for child profiles

Content Affinity Matrix:

# Gold layer: user-genre affinity for recommendation input
affinity = sessions_df.groupBy("user_id", "genre") \
    .agg(
        F.sum("watch_duration_sec").alias("total_watch_sec"),
        F.avg("completion_pct").alias("avg_completion"),
        F.count("session_id").alias("session_count")
    ) \
    .withColumn("affinity_score",
        F.col("total_watch_sec") * 0.4 +
        F.col("avg_completion") * 100 * 0.4 +
        F.col("session_count") * 0.2
    )

COPPA Constraint: Child profiles (age_bucket = "child") only receive recommendations from the kids catalog. No cross-profile recommendation leakage.

3. Ad Monetization Optimization (AVOD Tier)¶

Objective: Maximize ad revenue for free-tier users without degrading engagement.

Key Metrics: - Impression Yield: Ad impressions per content hour viewed - Fill Rate: Percentage of ad slots filled by demand partners - CPM (Cost per Mille): Revenue per 1,000 ad impressions - Ad-Induced Drop-Off: Percentage of users who stop viewing during/after an ad break

Optimization Strategy: - Dynamic ad load: reduce ad frequency for users showing engagement decline - Contextual targeting: match ad categories to content genre (no behavioral tracking for COPPA profiles) - A/B test ad pod lengths: 15s vs 30s vs 60s pre-roll impact on completion rates

Gold Metrics:

4. Content Acquisition ROI¶

Objective: Evaluate whether licensed content justifies its acquisition cost.

Key Metrics: - Cost per Viewing Hour (CPVH): Total license cost / total viewing hours - Catalog Utilization: Percentage of catalog titles viewed at least once in 30 days - Marginal Viewer Contribution: Incremental subscribers attributed to a title

Benchmarks:

5. Subscriber Lifecycle Management¶

Objective: Reduce churn through proactive engagement interventions.

Lifecycle Stages: 1. Trial (Day 1-7): Onboarding optimization, content sampling breadth 2. Activation (Day 8-30): Habit formation, push notification cadence tuning 3. Engagement (Month 2-12): Content personalization, feature adoption 4. Decay (Declining usage): Win-back campaigns, plan downgrade offers 5. Churn (Cancellation): Exit survey analysis, re-acquisition targeting

Churn Prediction Features: - Watch hours trend (7d, 14d, 30d rolling) - Session frequency decline rate - Genre diversity contraction - Device usage pattern changes - Payment failure history - Support ticket sentiment

Intervention Triggers:

Compliance Framework¶

COPPA (Children's Online Privacy Protection Act)¶

The platform serves a kids content library requiring COPPA compliance for users under 13.

Requirements Implemented:

1. Age-Gated Profiles: Each account designates child profiles during setup. Child profiles are tagged with age_bucket = "child" at the event level.

2. Parental Consent: Verifiable parental consent (VPC) is obtained before creating child profiles. Consent records are stored separately with audit trail.

3. Data Minimization: Child profile events collect only:

4. Content interaction (play/pause/stop) -- no seek granularity
5. Device type (category only, no device fingerprint)

6. No geolocation, no behavioral advertising identifiers

7. No Behavioral Advertising: AVOD ads shown to child profiles use contextual targeting only (genre-based). No user-level tracking or cross-session profiling.

8. Data Retention: Child profile event data is retained for a maximum of 30 days (vs. 90 days for adult profiles).

9. Parental Access: Parents can request full export or deletion of child profile data via self-service portal.

Implementation in Medallion:

Bronze: age_bucket field populated from profile metadata
Silver: COPPA filter applied -- child sessions excluded from behavioral models
Gold:   Child engagement metrics aggregated at content level only (no user-level)
        Recommendations use content-based filtering only (no collaborative)

GDPR (General Data Protection Regulation)¶

Requirements Implemented:

1. Consent Management: Users provide granular consent for:
2. Essential (playback functionality) -- always required
3. Analytics (engagement measurement) -- opt-in
4. Personalization (recommendations) -- opt-in

5. Advertising (behavioral ads for AVOD) -- opt-in

6. Right to Erasure (Article 17): Deletion request triggers:

7. Immediate: Remove user from active recommendation models
8. 24 hours: Purge user_id from Silver/Gold aggregations
9. 72 hours: Delete all Bronze event records matching user_id

10. Confirmation: Erasure completion notification to user

11. Data Portability (Article 20): Users can export their viewing history, preferences, and profile data in machine-readable JSON format.

12. Purpose Limitation: Event data collected for playback functionality is not repurposed for advertising without explicit consent upgrade.

13. Pseudonymization: All user identifiers in the lakehouse are pseudonymized. The mapping table (user_id <-> PII) is stored in a separate, access-controlled system outside Fabric.

GDPR Erasure Pipeline:

# Triggered by erasure request via API
def process_erasure(user_id: str):
    """Delete user data across all medallion layers."""
    # 1. Gold: Remove from segments and affinity tables
    spark.sql(f"DELETE FROM gold_user_segments WHERE user_id = '{user_id}'")

    # 2. Silver: Remove sessionized data
    spark.sql(f"DELETE FROM silver_media_sessions WHERE user_id = '{user_id}'")

    # 3. Bronze: Remove raw events (partitioned scan)
    spark.sql(f"DELETE FROM bronze_media_events WHERE user_id = '{user_id}'")

    # 4. Eventhouse: KQL purge
    # .purge table PlaybackEvents records where UserId == user_id

    # 5. Audit log
    log_erasure_completion(user_id, datetime.utcnow())

Cost Model¶

Monthly Fabric Consumption (~$25,000/month on F64)¶

ROI Analysis¶

Real-Time Intelligence¶

Eventhouse KQL Queries¶

Live Concurrent Viewers:

PlaybackEvents
| where EventTimestamp > ago(5m)
| where EventType in ("play", "heartbeat")
| summarize ConcurrentViewers = dcount(UserId) by bin(EventTimestamp, 1m)
| render timechart

Content Trending Detection:

PlaybackEvents
| where EventTimestamp > ago(1h)
| summarize Viewers = dcount(UserId) by ContentId
| join kind=inner (
    PlaybackEvents
    | where EventTimestamp between (ago(2h) .. ago(1h))
    | summarize PrevViewers = dcount(UserId) by ContentId
) on ContentId
| extend TrendPct = round((Viewers - PrevViewers) * 100.0 / PrevViewers, 1)
| where TrendPct > 50
| order by TrendPct desc

Quality of Experience Alert:

PlaybackEvents
| where EventTimestamp > ago(10m)
| where EventType == "buffering"
| summarize BufferEvents = count(), AffectedUsers = dcount(UserId) by Region
| where BufferEvents > 1000
| order by BufferEvents desc

Power BI Dashboards¶

Dashboard 1: Content Command Center¶

Purpose: Real-time content performance for programming executives.

Visuals: - Live concurrent viewer count (card with sparkline) - Top 20 trending titles (bar chart, hourly refresh) - Completion rate heatmap by genre x day-of-week - New release first-48-hour trajectory vs. benchmark

Dashboard 2: Subscriber Health¶

Purpose: Lifecycle management for growth and retention teams.

Visuals: - Subscriber funnel: Trial -> Active -> At-Risk -> Churned - Cohort retention curves (month-over-month) - Engagement segment distribution (power/casual/at-risk/dormant) - Churn prediction risk scores with intervention recommendations

Dashboard 3: Ad Revenue Operations¶

Purpose: AVOD monetization for ad sales and ad ops teams.

Visuals: - Impression yield and fill rate trends - CPM by genre and daypart - Ad-induced drop-off by ad pod length - Revenue forecast vs. actual (daily)

Implementation Checklist¶

• Deploy Eventstream with Event Hub source (32 partitions)
• Configure Eventhouse KQL database with 7-day retention
• Create Bronze notebook: 58_media_events.py
• Create Silver notebook: 58_media_sessions.py
• Create Gold notebook: 58_media_recommendations.py
• Configure COPPA age-gate filtering in Silver pipeline
• Implement GDPR erasure pipeline with audit logging
• Build Power BI Direct Lake semantic model
• Deploy Content Command Center dashboard
• Deploy Subscriber Health dashboard
• Deploy Ad Revenue Operations dashboard
• Set up KQL alerting for QoE degradation
• Configure OneLake data retention policies (30d child, 90d adult)
• Validate COPPA data minimization in child profile events
• Load test: 2B events/day sustained throughput

References¶

• Microsoft Fabric Eventstream Documentation
• Eventhouse KQL Best Practices
• COPPA Rule (16 CFR Part 312)
• GDPR Article 17 - Right to Erasure
• Direct Lake Mode in Power BI
• Delta Lake Deletion Vectors