The promise: every morning at 07:00, your phone shows a short narrative — yesterday's sales against the weekly trend, which ad spend actually converted, three shipments stuck longer than the courier's SLA, and one anomaly worth your attention. No dashboard to open, no spreadsheet to refresh. This is the observability layer of the end-to-end stack, and at SME scale it takes far less infrastructure than most sellers assume.
The pipeline: boring on purpose
- Ingest. Shopify webhooks stream orders, refunds and fulfilment events in near-real-time; a nightly pull re-fetches the last few days via the API to fill any gaps. Courier tracking events, ad platform spend, chat logs and payment settlements arrive through the same pattern: webhook where offered, scheduled pull as the safety net.
- Store. A single Postgres instance. At tens or hundreds of orders a day you do not need a data lake, a streaming platform or a warehouse product — Postgres is plenty, and it will stay plenty for years. Raw events land in append-only tables; cleaned, deduplicated facts live in a small set of modelled tables.
- Metrics layer. SQL views (or a handful of dbt models) define every business number exactly once: conversion rate, average order value, ROAS per channel, stuck-shipment count, chat deflection rate.
- Narration. A scheduled job computes the day's metrics, packages them as structured facts, and asks an LLM to write the morning story around them — delivered by email or Zalo message at 07:00.
The golden rule: the AI narrates, SQL computes
Practically: the briefing job renders a JSON block of pre-computed metrics with explicit labels, deltas and comparison baselines, and the prompt instructs the model to reference only those values. A post-check that scans the generated text for numbers absent from the input is cheap insurance — reject and regenerate on mismatch.
Define metrics once, in one place
Metric definition drift is the quiet killer of reporting projects. Shopify's dashboard counts conversion one way, your ad platform another, your spreadsheet a third — and Monday's meeting is spent arguing about whose number is right. The metrics layer ends the argument by being the single definition:
| Metric | Definition decision you must make explicitly |
|---|---|
| Conversion rate (CR) | Sessions or unique visitors as denominator; which day the order counts against |
| Average order value (AOV) | Before or after discounts; including or excluding shipping fees |
| ROAS | Attribution window; platform-reported versus modelled revenue |
| Stuck shipments | Hours without a tracking status change, per courier service level |
| Chat deflection | Conversations resolved without human handover, over what window |
Write the decision into the SQL and into a one-line comment beside it. When a tool disagrees with your briefing, the view definition is the arbiter.
Timezone bugs live at the day boundary
Shopify timestamps arrive in UTC; your business day runs on Asia/Ho_Chi_Minh (UTC+7). Every daily aggregate must convert before truncating to a date, or orders placed between midnight and 07:00 local silently migrate to the wrong day — and yesterday's "drop in evening sales" is actually a grouping bug. Standardise: store timestamps in UTC, convert to Asia/Ho_Chi_Minh in the metrics layer only, and test the boundary explicitly with an order placed at 23:55 local. The same discipline applies to ad-spend data, which some platforms report in the account's configured timezone — normalise everything at ingestion.
Webhooks fail quietly — backfill nightly
Webhook delivery is best-effort everywhere: deploys drop a few, platform incidents delay batches, an expired secret rejects a day's worth. The nightly pull re-fetches a sliding window (three to seven days) and upserts, so gaps heal automatically. Idempotent upserts keyed on the platform's object ID make replays safe — the same principle that governs payment callbacks in our gateway reconciliation article. If your briefing ever reports a suspicious zero, the first suspect is an ingestion gap, which is why the briefing should also report its own data freshness per source.
Anomalies: flag genuinely, not constantly
A briefing that cries wolf gets ignored by week two. Alert fatigue is a design failure, not a user failure:
- Compare against the same weekday's baseline, not yesterday — Sunday is not Monday.
- Use thresholds proportional to variance: a shop doing forty orders a day should not be paged over a swing of three.
- Suppress known causes — a Tết lull or a campaign spike you scheduled yourself is context, not an anomaly.
- Cap the briefing at a handful of flagged items; everything else is available on request, not pushed.
Delivery: meet yourself where you already look
Email works, but for most Vietnamese founders the briefing lands better as a Zalo message — the same OA infrastructure that sends your customers their tracking updates can send you your numbers (Zalo automation guide). Stuck-shipment items link straight to the courier tracking page; invoice failures link to the review queue from the e-invoicing pipeline. The briefing is not a report — it is the daily front door to every other system you have automated.