Gaming & digital media

A mobile game or content app produces two kinds of data. Operational data (users, books, subscriptions) sits in the app’s own database, in normalised tables designed for transactions. Behavioural data arrives from the device as events and piles up file by file in object storage: which screen opened when, which book was read for how many seconds, which game was launched how many times. Each does its own job. But “which content held which age group this month” needs both at once, and both are slow to answer it.

Event design comes first

Data that is not collected cannot be produced later. Writing down, before the app ships, which event is sent at which moment and with which fields (session id, profile, device, scene, duration) is the foundation of every later report. We build this list together with the development team and tie retention and session metrics to their source events from the start.

A separate warehouse for analytics

An operational database is designed for joins, an analytics warehouse for scans. In a columnar engine such as ClickHouse, a book’s authors, categories and languages sit in one row as arrays; one table is read instead of seven. Event files are read straight from object storage into fact tables enriched with dimensions, with materialized views producing hourly and daily aggregates on top.

Starting small and growing report by report

We do not try to build every dimension and every fact in the first step. We take a single fact table live with user, content and time dimensions and produce three reports from it. As the schema grows, school, publisher, subscription and revenue layers are added, each one answering a report need.