Upily: discovery and audit of the Wonjo Kids data infrastructure

1,500+

Objects examined

Tables and views, 18 datasets

330+

Events

Volume, category, errors, gaps, naming

98%+

User match

Firebase ↔ RevenueCat, same user id

7

Audit areas

From inventory to dashboards

Problem

Wonjo Kids had a data collection setup ahead of its competitors in the mobile children’s games market. Events from Firebase, attribution data from Adjust, subscription and transaction data from RevenueCat were gathered in BigQuery through scheduled queries; Looker Studio dashboards connected directly to the BigQuery tables.

The problem was not the absence of infrastructure but its organic growth. Tables had accumulated under the analytics database without a consistent naming standard; which table lived where, for what purpose, and where its data came from was undocumented. Most tables were defined as views and connected straight to Looker Studio — the performance problems on the large dashboards came from there. Which dashboards were active and which abandoned was unclear. Most importantly, nobody could be sure that data flowed into BigQuery correctly and completely: there was no systematic data quality check.

We proposed a four-phase transformation programme and ran the first phase: understand what exists, end to end, before building anything new.

An audit across seven areas

Discovery took two months. For every area we set a measurable question and drew the answer from BigQuery’s own metadata, pipeline logs and dashboard access records.

Area	Question	How we measured
Table inventory	What are the 1,500+ objects in 18 datasets, who uses them, which layer are they in?	INFORMATION_SCHEMA; size, last update, query count, source system tag, layer class
Pipelines	Does data flow from the three sources without gaps and correctly?	Missing-day checks on daily and streaming transfers; bucket storage class, delete protection, lifecycle rules
Cross-platform matching	Is the user in Firebase the same user as in RevenueCat?	User id matching; purchase events compared across the two sources
Platform parity	Does the same event fire at the same rate on iOS and Android?	Platform volume ratios for 330+ events
Taxonomy	Do table and field names follow the standard?	snake_case compliance, consistency of boolean prefixes
Event tracking	Which events exist, which are missing, which are misnamed?	Volume and category distribution; critical error events; comparison with industry standard
Dashboards	Which of the 14 dashboards are used, and which tables feed each?	Access records, data source analysis, performance bottlenecks

Findings

Strong foundations. The Firebase pipeline had run for more than two years without interruption or error. User matching between Firebase and RevenueCat was above 98% — beyond common industry practice; the remainder were users with multiple accounts. 97% of table names followed the standard and every boolean field carried a consistent prefix. The great majority of events fired at consistent rates on both platforms. The subscription model — free trial followed by renewal — was validated against RevenueCat revenue data, which also explained why “first purchase” never appeared in the reports.

Accumulated technical debt. Most objects outside Firebase’s raw daily tables — more than 600 tables and views — were never queried; the ads and CMS datasets were almost entirely unused, and a CDC stream was active but never read. There were no materialized views; most of the query cost came from two datasets, and a summary layer for the most expensive tables was the largest saving available. More than a third of the tables had no identifiable layer — there was no staging or mart layer and no lineage could be traced. The bucket holding attribution data was in the wrong storage class, with delete protection off and no lifecycle rule. Most dashboards were inactive; the pipelines feeding several had stopped.

Things not measured. The event list was wide, but the business’s most critical questions had no events: paywall shown and closed (conversion rate could not be computed), day 1/7/30 retention (the return on user acquisition spend could not be measured), session duration (there was a start but no end), a direct paid subscription start. As a children’s app subject to COPPA and Turkish data protection law, the parent gate and age verification screens were not tracked either — there was no provable data for a regulatory audit. Around thirty missing events and more than thirty naming corrections were delivered as a prioritised list.

Roadmap

The report became the foundation for the remaining three phases of the programme:

1. Discovery and audit — completed. Inventory, draft data dictionary, dashboard inventory, data quality baseline, matching report and this roadmap.
2. Layered architecture — raw, staging, mart and report layers; a central user id mapping table; summary tables and materialized views for the most expensive queries; data health monitoring.
3. Dashboard consolidation — an executive dashboard showing business performance at a glance; a reduced dashboard set built on the new architecture.
4. Advanced analytics — user segmentation, “next best content” recommendation, churn risk scoring.

Critical findings — the root cause of error events, parent gate tracking, paywall and retention events, bucket security — were listed separately as actions not to wait for the phases.

Why this way

• Measure first, then build. Before designing the new architecture we needed to know what the existing 1,500 objects were; otherwise the new structure would inherit the old tangle.
• Strengths are findings too. The match rate, pipeline health and naming discipline took as much space in the report as the risks — so that what must be preserved is known.
• A missing event cannot be produced later. If paywall, retention and parent gate events are not added today, there is no data for tomorrow’s analysis; that is why they were placed ahead of the advanced analytics phase.

Related

• Mayadem: from game events to a ClickHouse data warehouse
• Derimod: from data warehouse to Optail — three layers, seven projects in three years
• Defacto DSO: four years from the founding of a data science office

All our work