Turning scattered company data into a decision hub — the warehouse, the backend, the AI layer and the interface, designed, built and shipped end to end by one person.
Most dashboards show everything and decide nothing. KPLUS needed the opposite — one place where the numbers that actually change a decision rise to the top. I designed and built the whole thing: the data warehouse, the backend, the AI layer and the interface.
| Item | Signal | Status |
|---|---|---|
| Store B · stockout | restock behind | act |
| SKU-07 · high try-on, low conv. | funnel gap | watch |
| Social · race recap | reach above plan | good |
Two halves that rarely live in one person — the design and the engineering. On this project both are mine, end to end. Left is the interface and the design system; right is the real code behind the data, backend and deployment.
# warehouse — one trusted number CREATE VIEW v_sales_kpi AS SELECT channel, SUM(net) AS rev FROM serving GROUP BY channel;
# backend — live SQL, secured @app.route("/q") def q(): return run_bq(req.json["sql"])
# pipeline — repeatable & safe def build_serving(): backup() for s in SOURCES: upsert(load(s))
The company's data lived in scattered CSVs, an ERP, POS machines and a handful of tools. Reports were complete but not decidable — leadership had plenty of numbers, but not the next action.
The deeper problem was coordination: retail, design, sales and product each held a piece — what was launching when, where it stood, what to push next — but no one saw the whole. My first version made it worse: I patched whichever team shouted loudest, one page at a time, and ended up chasing symptoms. So I stopped, laid every team's problems side by side, and rebuilt around one logic instead of another patch.
The brief I set myself: build a decision hub, not another report.
The whole product runs on one rule — optimise for decidability, not completeness. It's what every design decision is measured against.
For each number I ask: "whose action does this change?" If there's no answer, it gets collapsed or pushed to a deeper layer — never the main stage.
Not "weekend sales" but a headline that names the takeaway; not "try-on count" but one that surfaces the tension underneath. The title does the thinking, so the reader doesn't have to.
Inputs (launches, campaigns, content) are aligned against outputs on one timeline, each point traceable to the real deliverable. The panel doesn't just show — it evaluates: was this worth doing again?
A spreadsheet could hold the data, but not carry what this needed — backtesting, cross-referencing, one auto-updated source everyone trusts. So I didn't stop at a sheet: I built the full stack, from the data model to the pixel.
gen_*.pybuild_serving (row UPDATE)auto-backup before rundata validation/data/q live SQLIAP · domain-lockedRaw company data is messy, overlapping and sometimes missing. The hard, valuable part isn't drawing charts — it's the judgment that makes a number trustworthy enough to act on.
Customers are grouped by customer code, not name — so messy, inconsistent names never corrupt the channel split.
Overlapping channel groups are de-duplicated so revenue is never double-counted, and an exclusion list removes non-dealers.
Where exact cost is missing, margin is an estimate, clearly labelled (≈90% cost coverage) — never a fake precise number.
Headline revenue is migrating from an ERP snapshot to one BigQuery view — so everyone argues from the same number.
If a number's source is unknown it's marked pending ({待}), greyed and collapsed — never filled with a guess.
Every figure can be traced back to its CSV row or BigQuery view — the dashboard is auditable, not a black box.
Once the data is trustworthy, the real value is using it to reach a non-obvious call. On channels, I didn't rank "which one earns the most revenue." I asked the question that matters for a physical-goods brand —
Are the channels that move volume the same ones that make margin?
The data was complete — but complete isn't useful. Leadership had no time to read tables; non-technical staff couldn't query them. AI is the layer that turns data into something a busy person acts on. That's the capability that matters most here — not "adding AI", but using AI to solve specific problems.
AI draws a perfect happy path in minutes. What's scarce isn't the screen — it's the judgment about where a number quietly breaks. So before designing each decision view, I hand the business rules to AI and ask it to break them — surface every edge case that distorts a figure. AI generates that failure map in minutes; my value is deciding which ones to guard, and how.
Five decision views, each with one clear hero, and three layers of depth — glance → expand → source. You see the point first; you can open the detail; you can always trace the number.
| Layer | What it does · how the data connects | Stack |
|---|---|---|
| Sources | ERP snapshot, sales CSV, inventory, store POS and social APIs — raw, scattered, inconsistent. | ERP · CSV · POS · APIs |
| Pipeline | Python scripts clean each source, validate it, auto-backup before every run, then write to the warehouse via row UPDATE — repeatable and safe. | Python · gen_*.py |
| Warehouse | A layered warehouse — dim/fact/mart/serving, 60 tables and 10 views — converging on the single trusted number for the whole company. | BigQuery |
| Backend | /data serves the data, /q runs live SQL; the whole service sits behind IAP, locked to the company domain — secure by default. | Flask · Cloud Run · IAP |
| AI layer | A daily summary reads the day's data and writes the takeaway; a plain-language Q&A turns natural language into a query and answers — so non-technical staff can ask the data anything. | AI summary · NL→query |
| Frontend | Single-file UI, auto-binds data, inline fallback if a load fails, dark monitoring and white meeting modes. | Vanilla · single-file |
| Top content | Reach | Eng. rate | Share |
|---|---|---|---|
| Training reel · MAMBA | 12.4K | 8.1% | |
| Race recap · Wuling | 9.8K | 7.2% | |
| Product teaser | 7.1K | 5.4% | |
| Athlete story | 5.3K | 6.0% |
From data model to pixel, this was a solo, full-stack build — data engineering, backend, deployment, security, AI and design system in one project. It's the clearest proof that I can take an ambiguous business problem and ship a working product around it, not just a screen.
Outcome: Shipped and live on Cloud Run behind IAP as the company's internal decision surface. It replaced the hand-uploaded daily report, and now pulls POS, CRM and other sources into one place — data entered once flows through to everywhere it's needed. The team actively uses its five decision views to review data, pull reports and run meetings. By an internal estimate, gathering and reconciling the numbers in one place cut that prep time by roughly 14%. Specific adoption figures stay internal.
Reflection: The value was never the charts — it was the judgment about what not to show, and the discipline to never fake a number. That's what makes data safe to decide on.