Case Study · Gaming & digital media
Upily: discovery and audit of the Wonjo Kids data infrastructure
The Firebase, Adjust and RevenueCat data of Wonjo Kids, a games app for children, had been gathered in BigQuery, but the setup had grown organically and become complex. We examined more than 1,500 BigQuery objects and 330 events across seven areas: table inventory, pipelines, cross-platform matching, platform parity, taxonomy, event tracking and dashboards. Strengths, risks and a four-phase transformation roadmap were delivered in a single report.
- 1,500+
- Objects examined
- 330+
- Events
- 98%+
- User match
- 7
- Audit areas
Tables and views, 18 datasets
Volume, category, errors, gaps, naming
Firebase ↔ RevenueCat, same user id
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 that the setup had grown without a plan. 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, which is where the performance problems on the large dashboards came from. Which dashboards were active and which abandoned was unclear. Most importantly, there was no systematic data quality check, so nobody could be sure that data flowed into BigQuery correctly and completely.
We proposed a four-phase transformation programme and ran the first phase. Its aim was to understand the existing setup fully 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%, higher than 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 (a 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, and the pipelines feeding several of them had stopped.
Things not measured. The event list was long, but the business’s most important questions had no events to answer them: 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, it did not track the parent gate and age verification screens either, so there was no data to show in a regulatory audit. We delivered around thirty missing events and more than thirty naming corrections as a prioritised list.
Roadmap
The report became the basis for the remaining three phases of the programme:
- Discovery and audit (completed): inventory, draft data dictionary, dashboard inventory, data quality baseline, matching report and this roadmap.
- 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.
- Dashboard consolidation: an executive dashboard showing business performance on one screen; a smaller dashboard set built on the new architecture.
- Advanced analytics: user segmentation, “next best content” recommendation, churn risk scoring.
Findings that should not wait for the phases were listed separately: the root cause of error events, parent gate tracking, paywall and retention events, bucket security.
Why this way
- We measured the current state first. 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.
- We wrote up the strengths too. The match rate, pipeline health and naming discipline took as much space in the report as the risks, because the client also needed to know what to preserve.
- Missing events were moved up. If paywall, retention and parent gate events are not added now, there will be no data to analyse later. That is why they were placed ahead of the advanced analytics phase.