◆ QUEST LOG / CLOUD BI DASHBOARD
From raw CSVs to KPIs a manager can trust
A dashboard is only as good as the definitions underneath it — so this project starts with the warehouse, not the charts.
01The problem
The Olist dataset arrives the way real company data usually does: eight raw CSVs with orders, items, payments, reviews, sellers, and geolocation scattered across files. Ask three analysts for “monthly revenue” on top of that and you’ll get three numbers — does revenue include freight? Cancelled orders? Which of the five order timestamps defines the month?
The goal here wasn’t a prettier chart. It was one defensible definition per metric, and a dashboard a non-technical manager can use without asking anyone for help.
02The warehouse
I modeled the data into a Kimball-style star schema — a central order-item fact table surrounded by conformed dimensions (date, customer, seller, product, geography) — and built the ETL pipeline into BigQuery: staging, cleaning, surrogate keys, then the star.
- Grain declared explicitly: one row per order item — every metric aggregates up from there.
- Slowly-changing details (like customer city) resolved at load time, so history doesn’t rewrite itself.
- Data quality checks between staging and the star: row counts, referential integrity, and null audits.
03The dashboard
On top of the warehouse sits a Looker Studio dashboard organized around three questions a commercial team actually asks: how are sales moving, are we delivering on time, and are customers happy?
- Sales: revenue, order volume, and average order value with period-over-period comparisons.
- Delivery: promised-vs-actual delivery gaps, late-delivery rates by region.
- Satisfaction: review scores tied back to delivery performance — the two move together, and the dashboard shows it.
04Governance
Every KPI on the dashboard has a written definition — the exact SQL logic, the tables it touches, and the decisions behind it (freight excluded from revenue; delivered orders only). Metric definitions and data lineage are documented alongside the pipeline, so the next person can audit any number back to its raw rows.
05What I learned
Dimensional modeling is a communication tool disguised as a database technique. Declaring the grain and naming the dimensions forced every ambiguity into the open before it became a wrong chart. And building for a non-technical audience is a harder design problem than building for analysts — every label, filter, and default range has to survive without a data dictionary open in the next tab.