Mayadem: from game events to a ClickHouse data warehouse

7 → 1

Book dimension

Seven normalised tables, one array-based dim_book

130+

Event definitions

For TRT Çocuk Oyun Dünyası, during development

5

Live tables

4 dimensions + 1 fact, each with an ETL log

5

Report personas

Publisher, school, student, individual, parent

Problem

Mayadem’s apps produce two kinds of data. Users, books, publishers and subscriptions live in PostgreSQL, in normalised tables designed for transactions. A book’s authors, categories, illustrators, language variants and translators sit in seven separate tables; getting everything about one book takes seven joins. Behavioural data arrives from the device as events: which profile read which book on which device for how many seconds, which scene closed when. These events pile up as Parquet files in S3, millions a day.

Reports lived in the app’s admin panel as ready-made queries: most-read books, active subscribers, MRR, churn, funnel, retention. Each of them worked. But questions that need both sources at once, such as “which category held which age group this month, who reads at weekends”, were either answered slowly or never asked.

2022: Event design and marketing dashboards

The first piece of work was for TRT Çocuk Oyun Dünyası. While the app was being developed we worked line by line with the development team on which event is sent at which moment with which fields: app launch, language selection, parent panel, screen time, bedtime, subscription, game start and close. The list grew to more than 130 events, each with its key–value fields. From the same list we tied metrics such as retention, session count, sessions per user and most-played games to the events they are computed from. On top we built marketing and cross-market comparison dashboards in Power BI.

The principle we followed there carried over to the next project: data that was never collected cannot be recovered later. If an event’s name, moment and fields are written down up front, there is a meaningful history to move once the warehouse is built.

2025: A ClickHouse warehouse for MagicPages

MagicPages is a multilingual reading app for children. In August 2025 we sat down with the team and walked through the data flows from start to finish: what happens in the database from a user’s first app open to SSO, how a book’s many-valued attributes are stored, how events are collected on the device and how often they are written to S3. Then we deliberately set a small target.

MVP. Move a single fact table of reading durations, with user, book and time dimensions, to ClickHouse and produce three reports from it: user reading performance, book popularity and reading time, time-based reading trends. We did not build every dimension and fact up front, only what the three reports needed.

Array-based dimension. Someone in the meeting asked: “Do we keep dimensions normalised and join them, as in a traditional warehouse?” On SQL Server or PostgreSQL we would, because those engines are not flexible with nested structures. ClickHouse is the other way round. dim_book carries a book’s authors, categories and illustrators in array columns and its language variants and translators in a Nested structure, all in one table. “Books by this author” becomes a single has() call instead of three joins. The table deduplicates on modified date with ReplacingMergeTree. On the PostgreSQL side, data is aggregated into arrays with CTEs, JSON variants are converted to Nested, and everything is loaded in batches.

Fact table. The ReadingSceneClosed event is read straight from S3 with ClickHouse’s S3 engine and written to fact_reading_session, enriched from the book dimension: session duration, pages read, reading speed, platform, device language, age. The table is partitioned by month and ordered by (event_date, user_id, book_id); low-cardinality fields use LowCardinality, the category array has a bloom-filter index. Materialized views on top give hourly user activity and daily book performance, so queries read from these pre-aggregated summaries rather than scanning raw events.

Time dimension. Besides year, quarter and month, dim_calendar holds precomputed Turkish public holidays, school days, terms and weekend flags. A question like “evening reading on school days” takes a single filter.

What went live. As of November 2025 the warehouse has dim_book, dim_calendar, dim_child, dim_parent and fact_reading_session live. Each table has its own ETL log table, and schema changes are recorded in migration_history, so every load can be traced.

Roadmap

The target schema has ten dimensions and nine facts. Dimensions: user, book, date, time, school, class, publisher, device, location, subscription. Facts: reading session, book performance, user engagement, homework, student progress, revenue, rating, school and publisher analytics. On top of it a three-level report catalogue is defined for five personas. It covers book-level revenue and completion rates for the publisher, class comparison and homework tracking for the school, personal progress for the student, habit analysis for the individual user, and daily reading time and screen time for the parent. Twenty analytical scenarios group into four platforms: churn prediction and prevention, student success analytics, content intelligence, revenue optimisation.

The work continues in 2026 as data-organisation consulting: reviewing existing structures, testing alternative technologies, planning data flows, insight and visualisation.

Why this way

• We designed around the engine. Normalised dimensions and joins are a habit that comes from the limits of row-based engines. In a columnar engine, arrays and nested structures run faster and make queries easier to read, so we built the warehouse around what ClickHouse does well.
• The MVP started with three reports. The first live structure answered three questions; the schema grows as those reports get used.
• Event design comes before the warehouse. If session, profile, device and duration are not in the event, they cannot be added to a fact table later.

Related

• Upily: discovery and audit of the Wonjo Kids data infrastructure
• Derimod: seven projects in three layers, from data warehouse to Optail
• Defacto DSO: setting up a data science office and four years of consulting

All our work