Data Engineering · mobility provider (DE)

Analytics Platform (BigQuery)

A production-grade analytics platform for a mobility app, from raw-data normalization to AI-assisted self-service.

Role: Freelance Data Engineer · end-to-end ownership
Timeframe: since 2022 · long-term part-time mandate

Context

Starting point & problem

A mobility app produces large amounts of product and tracking data: Firebase/GA4 raw events, screen flows, feature usage, device information, conversion and revenue. In raw form, the data is deeply nested, arrives late and differs between iOS and Android. Without clean modelling it quickly becomes hard to understand and unusable for business teams.

The goal was a data platform that does not just produce reports, but makes daily analyses reproducible, quality-assured and usable for non-technical stakeholders. The result is an ecosystem with four pillars: a three-layer BigQuery warehouse, a dedicated analytics pipeline for a dashboard personalization feature, five custom Looker Studio visualizations and an AI-assisted self-service assistant.

Role

My role & scope

Sole end-to-end responsibility across all four pillars: architecture, data modelling, ETL pipelines, data quality, visualization, AI integration, deployment and documentation.

Long-term mandate since 2022 with about 180 files, including roughly 120 SQL scripts. The focus throughout was a high degree of automation instead of manual reporting work.

Architecture

Decisions & trade-offs

Three-layer medallion model (Foundation, Feature, Application) on BigQuery.

Why: Clear separation of normalized raw data, calculated metrics and dashboard aggregates keeps pipelines maintainable, traceable and independently testable.
Trade-off: More modelling discipline and intermediate layers in exchange for long-term clarity and reuse.

Idempotent batch ETLs via DELETE+INSERT or TRUNCATE+INSERT.

Why: Backfills and manual reruns must be reproducible at any time without duplicates, independent of how often an ETL is executed.
Trade-off: Slightly more expensive write operations in exchange for reliable repeatability.

Multi-day reprocessing (T-2/T-3/T-4) instead of simple T-1 processing.

Why: Firebase exports arrive late and sometimes incomplete. Reprocessing several days stabilizes numbers automatically instead of requiring manual corrections.
Trade-off: Every run reprocesses multiple days, so compute cost is higher in exchange for stable final numbers.

Star schema with a synthetic GENERATE_UUID() join key.

Why: A shared UUID key across the five foundation tables makes EAV-normalized satellites (event parameters, user properties, geo, devices) cleanly and consistently joinable.
Trade-off: Synthetic key per run instead of a natural key, but consistent joins across the full layer.

Consecutive deduplication via config_hash for the dashboard personalization feature.

Why: An MD5 hash of the widget configuration removes consecutive identical states while preserving legitimate repeats after an intermediate change. This was necessary because iOS emitted configuration events on every app start instead of only on changes, producing around 87% duplicates.
Trade-off: More pipeline logic (lookback, window functions) instead of a naive insert.

Click-to-order attribution across multiple time windows instead of one conversion number.

Why: Widget clicks are linked to orders in 1h, 24h and 7d windows. This gives a more realistic picture of impact than a single context-free conversion rate.
Trade-off: More complex joins, including conversion between BYTES and Base64 string because the customer number is stored differently across systems.

Custom Looker Studio community visualizations instead of standard charts.

Why: Standard charts do not cover domain-specific views such as feature flows with screenshots, platform status badges and Figma deep links. Custom vanilla-JS visualizations deliver exactly the required representation.
Trade-off: Custom build and GCS deployment effort in exchange for full control over presentation and interaction.

AI self-service through a Slack assistant (n8n + Claude) with embedded schema.

Why: Business users ask questions in natural language; the assistant knows the complete BigQuery schema and generates suitable SQL and visualization suggestions. This keeps many questions away from the data team.
Trade-off: Ongoing API cost (around USD 0.02 to 0.05 per request) and prompt maintenance in exchange for meaningful relief for the data team.

Implementation

Technical highlights

Three-layer data warehouse on Google BigQuery (Foundation, Feature, Application) with around 120 SQL scripts
Ten deployed scheduled queries (03:30 to 05:00) with documented dependencies, idempotency and runtime estimates
Normalization of deeply nested Firebase/GA4 raw data into a star schema (EAV for event parameters and user properties)
Session reconstruction and screen flow via window functions (30-minute timeout, islands pattern for location and session)
State-accumulating user-property snapshots so users never disappear from history
Dedicated dashboard analytics pipeline with config_hash deduplication and click-to-order attribution (1h/24h/7d)
16 idempotent backfill and migration scripts plus isolated QA pipeline with 13 automated validation tests
Five custom Looker Studio community visualizations in vanilla JS (dscc) with light/dark theming and GCS deployment
AI self-service assistant (n8n + Claude) in Slack, self-hosted on Cloud Run and Cloud SQL including deployment scripts

Stack

Technologies used

BigQuerySQLETLFirebase / GA4Looker StudioJavaScriptn8nAnthropic ClaudeCloud RunGCP

Outcome

Impact

The result is an automated analytics platform covering the full value chain: from raw-data normalization and reproducible, quality-assured pipelines to self-service analysis and AI-assisted data querying.

The mandate has been managed long-term and reliably since 2022. It demonstrates end-to-end ownership across four pillars and a high degree of automation instead of manual reporting work.

Reflection

What I would decide differently today

Today I would keep the data freshness logic (T-2/T-3/T-4) synchronized more strictly between code and documentation from the start, so offsets stay unambiguous across all layers.

Some KPI concepts, such as dwell-time efficiency or the dashboard funnel, intentionally remained documented concepts until the required transaction and screen-flow integration is available. I communicate this separation between implemented and conceptually prepared KPIs transparently.

Sounds like a profile that fits your project?

Check availability More projects