Mobile Games Data Architecture & Diagnostic Dashboard

---

Table of Contents

• Overview
• Features
• Architecture
• Event Schema
• LTV & Attribution
• Dashboard
• Run Locally
• License

---

Overview

A complete, production-ready analytics engineering system for mobile free‑to‑play games.
Includes data pipelines, event schema, attribution modeling, LTV/ROAS analytics, friction diagnostics, and an interactive KPI dashboard built with Chart.js.

---

Features

• Production-grade event schema
• Real-time + batch pipelines
• BigQuery modeling (Bronze → Silver → Gold)
• Cohort LTV & ROAS system
• SKAN CV 6-bit mapping
• Gameplay friction diagnostics
• Retention heatmaps
• Funnel & economy analytics
• Interactive dashboard (HTML + JSON)

---

Architecture

Data Sources

• Game client events
• Ad/attribution networks (SKAN, Singular, Adjust)
• External ETL systems

Ingestion

• Load Balancer → Pub/Sub queue
• GCS Staging

Processing

• Speed Layer: Dataflow streaming
• Batch Layer: dbt incremental models

Warehouse Modeling

• Bronze: Raw logs
• Silver: Cleaned/clustered events
• Gold: Pre‑aggregated metrics (DAU, Retention, LTV)

---

Event Schema

Example fields:

• user_id
• timestamp_client, timestamp_server
• device, platform, app_version
• level, attempt_id, session_id
• currency_balances
• ad_impressions
• is_cheater

Example: gameplay.level.complete.v2.

---

LTV & Attribution

Matching Strategy

• Primary: Advertising ID
• Fallback: probabilistic matching
• SKAN: campaign-level mapping

LTV Calculation

• ARPU by day-since-install
• Windowed cumulative LTV
• Stored in fact_cohort_ltv_daily

1. UserInstall CTE (Attribution + Internal User Profiles Join)

DailyUserRevenueSource AS (
    SELECT
        user_id,
        DATE(event_timestamp) AS revenue_date,
        SUM(
            CASE WHEN event_name = 'monetization.iap.verified.v1'
                 THEN properties.iap_total_spend_usd
                 ELSE 0 END
        ) AS iap_revenue,
        SUM(properties.ad_revenue_usd) AS ad_revenue
    FROM Silver.events
    WHERE event_name IN ('monetization.iap.verified.v1', 'ads.impression.v1')
    GROUP BY 1, 2
),

2. DailyUserRevenue – Aggregated IAP + Ad Revenue (clean version)

Step A — First aggregation level

DailyUserRevenueSource AS (
    SELECT
        user_id,
        DATE(event_timestamp) AS revenue_date,
        SUM(
            CASE WHEN event_name = 'monetization.iap.verified.v1'
                 THEN properties.iap_total_spend_usd
                 ELSE 0 END
        ) AS iap_revenue,
        SUM(properties.ad_revenue_usd) AS ad_revenue
    FROM Silver.events
    WHERE event_name IN ('monetization.iap.verified.v1', 'ads.impression.v1')
    GROUP BY 1, 2
),

Step B — Final daily revenue calculation

DailyUserRevenue AS (
    SELECT
        *,
        iap_revenue + ad_revenue AS total_daily_revenue
    FROM DailyUserRevenueSource
),

3. CohortDSI (Day Since Install + Joined Revenue)

CohortDSI AS (
    SELECT
        t1.install_cohort_date,
        t1.campaign_id,
        t1.media_source,
        t2.user_id,
        t2.revenue_date,
        t2.total_daily_revenue,
        DATE_DIFF(t2.revenue_date, t1.install_cohort_date, DAY) AS days_since_install
    FROM UserInstall AS t1
    INNER JOIN DailyUserRevenue AS t2
        ON t1.user_id = t2.user_id
),

4. FinalLTV – Cohort LTV Calculation (Window + ARPU)

FinalLTV AS (
    SELECT
        install_cohort_date,
        campaign_id,
        media_source,
        days_since_install,

        COUNT(DISTINCT user_id)
            OVER (PARTITION BY install_cohort_date, campaign_id)

Dashboard

Contains 4 analytical layers:

1. Pulse: DAU, ARPDAU, crashes, trends
2. Growth Engine: LTV curves, ROAS recovery
3. Retention: Triangle heatmap
4. Gameplay Friction: Level‑based churn, economy issues

---

Run Locally

Python HTTP Server

python3 -m http.server 8000

Open:

http://localhost:8000/dashboard.html

VS Code

Right‑click → Open with Live Server

---

License

MIT License