Querying Mobile App Metrics with AWS Athena

TL;DR: Reference guide for querying mobile application telemetry data using AWS Athena, including device health metrics, user flow analytics, and operational alerts.
Vinay Naik 6 min read
#aws#athena#sql#metrics#analytics#mobile#monitoring

AWS Athena provides serverless SQL querying against S3-based data lakes, making it ideal for analyzing mobile application telemetry. This guide covers common query patterns for device health monitoring and user flow analytics.

Data Architecture

┌─────────────────────────────────────────────────────────────────────┐
│                     Mobile Metrics Pipeline                          │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐          │
│  │  Mobile App  │───▶│   Kinesis    │───▶│   S3 Data    │          │
│  │  Telemetry   │    │   Firehose   │    │    Lake      │          │
│  └──────────────┘    └──────────────┘    └──────────────┘          │
│                                                 │                    │
│                                                 ▼                    │
│                                          ┌──────────────┐           │
│                                          │    Glue      │           │
│                                          │   Catalog    │           │
│                                          └──────────────┘           │
│                                                 │                    │
│                                                 ▼                    │
│  ┌──────────────┐                       ┌──────────────┐            │
│  │  Dashboard   │◀──────────────────────│    Athena    │            │
│  │  / Alerts    │                       │   Queries    │            │
│  └──────────────┘                       └──────────────┘            │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Partition Structure

Metrics are partitioned by type and date for efficient querying:

s3://metrics-bucket/
├── partition_0=system/           # System metrics
│   └── partition_1=2022/
│       └── partition_2=04/
│           └── partition_3=06/
│               └── data.parquet
├── partition_0=realtime/         # Real-time events
│   └── ...
└── partition_0=business/         # Business metrics
    └── ...

Partition Columns:

  • partition_0: Metric category (system, realtime, business)
  • partition_1: Year (YYYY)
  • partition_2: Month (MM)
  • partition_3: Day (DD)

Device Health Metrics

Battery Monitoring

-- Last plug-in battery charge level
SELECT 
    deviceid,
    metricvalue as battery_percent,
    timestamp
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'lastPlugBatteryCharge' 
ORDER BY timestamp DESC;

-- Battery temperature monitoring
SELECT 
    deviceid,
    metricvalue as temp_celsius,
    timestamp
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'batteryTemp' 
ORDER BY timestamp DESC;

Memory and Storage

-- RAM utilization
SELECT 
    deviceid,
    MAX(CASE WHEN metricname = 'ramTotal' THEN CAST(metricvalue AS BIGINT) END) as ram_total_mb,
    MAX(CASE WHEN metricname = 'ramAvailable' THEN CAST(metricvalue AS BIGINT) END) as ram_available_mb,
    ROUND(
        (1 - MAX(CASE WHEN metricname = 'ramAvailable' THEN CAST(metricvalue AS DOUBLE) END) / 
         NULLIF(MAX(CASE WHEN metricname = 'ramTotal' THEN CAST(metricvalue AS DOUBLE) END), 0)) * 100, 
        2
    ) as ram_utilization_pct
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname IN ('ramTotal', 'ramAvailable')
GROUP BY deviceid;

-- Storage space
SELECT 
    deviceid,
    MAX(CASE WHEN metricname = 'hddInternalTotal' THEN metricvalue END) as storage_total,
    MAX(CASE WHEN metricname = 'hddInternalAvailable' THEN metricvalue END) as storage_available
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname IN ('hddInternalTotal', 'hddInternalAvailable')
GROUP BY deviceid;

Network Status

-- Network connectivity status
SELECT 
    deviceid,
    metricvalue as network_status,
    timestamp
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'networkStatus' 
ORDER BY timestamp DESC;

-- Network type (WiFi, LTE, etc.)
SELECT 
    deviceid,
    metricvalue as network_type,
    timestamp
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'networkType' 
ORDER BY timestamp DESC;

-- Signal strength (RSRP)
SELECT 
    deviceid,
    CAST(metricvalue AS INTEGER) as rsrp_dbm,
    CASE 
        WHEN CAST(metricvalue AS INTEGER) >= -80 THEN 'Excellent'
        WHEN CAST(metricvalue AS INTEGER) >= -90 THEN 'Good'
        WHEN CAST(metricvalue AS INTEGER) >= -100 THEN 'Fair'
        ELSE 'Poor'
    END as signal_quality,
    timestamp
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'networkRsrp' 
ORDER BY timestamp DESC;

Device Online Status

-- Last online timestamp per device
SELECT 
    deviceid,
    MAX(timestamp) as last_seen,
    DATE_DIFF('minute', MAX(timestamp), CURRENT_TIMESTAMP) as minutes_since_seen
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'lastOnline' 
GROUP BY deviceid
ORDER BY last_seen DESC;

User Flow Analytics

Entry Flows

-- Invite flow metrics
SELECT 
    societyid,
    COUNT(*) as flow_count,
    AVG(CAST(metricvalue AS DOUBLE)) as avg_duration_ms
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'InviteNewFlow'
GROUP BY societyid
ORDER BY flow_count DESC;

-- Total wait time comparison
SELECT 
    'New Flow' as flow_type,
    COUNT(*) as count,
    AVG(CAST(metricvalue AS DOUBLE)) as avg_wait_time_ms
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'TotalWaitTimeNewFlow'

UNION ALL

SELECT 
    'Legacy Flow' as flow_type,
    COUNT(*) as count,
    AVG(CAST(metricvalue AS DOUBLE)) as avg_wait_time_ms
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metricname = 'TotalVisitorWaitTimeOlderFlow';

Communication Metrics

P2P Call Analytics

-- Guard-to-guard calls placed
SELECT 
    societyid,
    COUNT(*) as calls_placed,
    DATE_TRUNC('hour', timestamp) as hour
FROM "app_metrics"."mobileappv2" 
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND p2ptype = 'g2g' 
  AND metricname = 'CallSuccessfullyPlaced'
GROUP BY societyid, DATE_TRUNC('hour', timestamp)
ORDER BY hour;

Operational Alerts

Outage Alert Monitoring

-- Outage alerts created
SELECT 
    DATE_TRUNC('hour', timestamp) as hour,
    COUNT(*) as alerts_created
FROM metrics_db.alerts 
WHERE partition_0 = 'realtime' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metric_name = 'OUTAGE_ALERT' 
  AND metric_key = 'outage_alert::status=CREATED'
GROUP BY DATE_TRUNC('hour', timestamp)
ORDER BY hour;

-- Escalated alerts by location
SELECT 
    societyid,
    COUNT(*) as escalated_count
FROM metrics_db.alerts 
WHERE partition_0 = 'realtime' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'
  AND metric_name = 'OUTAGE_ALERT' 
  AND metric_key = 'outage_alert::status=ESCALATED'
GROUP BY societyid
ORDER BY escalated_count DESC;

CCTV Alert Analysis

-- CCTV alerts by location
SELECT 
    societyid,
    metric_key,
    COUNT(*) as alert_count,
    MIN(metric_emitted_at) as first_alert,
    MAX(metric_emitted_at) as last_alert
FROM metrics_db.alerts
WHERE partition_1 = '2022'
  AND partition_2 = '04'
  AND partition_3 = '06'
  AND metric_key <> ''
  AND metric_name = 'CCTV_ALERT'
GROUP BY societyid, metric_key
ORDER BY alert_count DESC;

Query Optimization Tips

Partition Pruning

Always include partition filters to minimize scanned data:

-- Good: Uses partition pruning
WHERE partition_0 = 'system' 
  AND partition_1 = '2022' 
  AND partition_2 = '04' 
  AND partition_3 = '06'

-- Bad: Scans all partitions
WHERE timestamp >= '2022-04-06'

Columnar Selection

Select only needed columns:

-- Good: Select specific columns
SELECT deviceid, metricvalue, timestamp

-- Avoid: SELECT *
SELECT * FROM ...

Aggregation Pushdown

Use aggregations to reduce data transfer:

-- Aggregate in Athena, not in application
SELECT 
    siteid,
    COUNT(*) as count,
    AVG(CAST(metricvalue AS DOUBLE)) as avg_value
FROM ... 
GROUP BY siteid

Common Metric Categories

CategoryMetricsUse Case
BatterylastPlugBatteryCharge, batteryTempDevice health monitoring
StoragehddInternalTotal, hddInternalAvailable, cacheCapacity planning
MemoryramTotal, ramAvailablePerformance monitoring
NetworknetworkStatus, networkType, networkRsrpConnectivity analysis
User Flows*UnlockFlow, WaitTimeUX optimization
AlertsOUTAGE_ALERT, CCTV_ALERTOperational monitoring

Summary

AWS Athena enables powerful analytics on mobile application telemetry without managing infrastructure. Key practices:

  1. Partition Wisely: Design partitions around common query patterns
  2. Filter Early: Always include partition predicates
  3. Select Minimally: Request only needed columns
  4. Aggregate Remotely: Perform calculations in Athena, not client-side
  5. Monitor Costs: Track scanned data volume to optimize expenses