How we cut Lore Bathing Club's reporting time by 60%

Lore Bathing Club is a small, founder-run DTC brand with a tight team and an outsized content and marketing operation. Before we started working together, their weekly reporting process looked like this: pull Shopify exports manually, paste into a master Google Sheet, run a set of formulas that someone had built two years ago and that nobody fully trusted anymore, then share a screenshot in Slack and hope the numbers matched the ones in Meta’s own dashboard.

It was taking about four hours a week. The numbers were frequently slightly wrong. And every time a new channel came online, someone had to extend the sheet by hand.

What we built

We replaced the entire stack with a lightweight ELT pipeline using dlt and SQLMesh. The architecture is simple enough to fit on a whiteboard:

1. dlt pulls raw data from Shopify, Meta Ads, and Google Analytics on a nightly schedule. It handles pagination, schema evolution, and incremental loads automatically — no custom API wrappers needed.
2. SQLMesh transforms the raw tables into a set of clean, semantically meaningful models: daily revenue, channel-attributed acquisition cost, cohort retention by first-order month. Each model is version-controlled, testable, and documented in SQL.
3. A lightweight dashboard reads from the final models and updates automatically each morning.

The whole pipeline runs on a $7/month VPS. No Fivetran, no Airbyte, no Snowflake bill.

The 60% number

Before: ~4 hours per week across two people. After: ~90 minutes, almost all of which is reading and responding to the numbers rather than producing them. The pipeline itself runs in under three minutes.

The less obvious gain is trust. When the numbers come from a version-controlled SQL model with tests, there’s a reference point when something looks off. Before, a suspicious number meant a thirty-minute audit of the sheet. Now it means checking a dbt-style test result and tracing it to a single source table.

What made it work

A few things made this project go smoothly that often don’t:

We started with the output, not the input. Before writing a line of code, we spent an hour with the founder defining the three or four numbers she actually made decisions from. That determined the schema of the final models. Everything upstream was in service of those numbers.

dlt’s schema inference is genuinely useful. Shopify’s API returns a lot of nested JSON. dlt flattened it sensibly without us having to write a schema by hand. When Shopify changed a field name in a minor API update, dlt detected the drift and flagged it rather than silently breaking.

SQLMesh’s dev/prod separation is worth the learning curve. You can run a model change against a dev copy of the data before promoting it. For a small team where one broken query would break the morning report, this was important.

What we’d do differently

We underestimated how long it would take to agree on the cohort definition. “Retention” sounds simple until you have to decide whether a customer who bought in month one and month four counts as retained in months two and three. We burned a sprint on this. Define your terms in writing before you write SQL.

If you’re running a similar manual reporting stack and want to talk through whether this kind of pipeline makes sense for your scale, get in touch.