Data Stack for a Single-Channel Shopify Brand
One store holds every order, and the questions are about ads, profit per order, and who comes back. Here is what to use now, and what to build when you outgrow it.
The short answer
Sound familiar?
- Shopify is nearly all of your revenue.
- A founder checks sales and ad spend every day.
- Nobody owns the data models, and the main report is a spreadsheet of exports.
- Finance rebuilds the packaged tool's numbers before every month-end.
Why it happens
- Product cost is a single static field
- Shopify's profit reports only cover products with a cost recorded at the time of sale, and the cost field is static. When a supplier price changes, old orders pick up today's cost unless you keep a dated cost table.
- Revenue means several different things
- Shopify defines net sales as gross sales minus discounts and sales reversals, while total sales adds shipping, taxes, duties, and fees. Refunds land on the day they are processed, so a refund this month can lower a number for last month's order.
- The packaged tool's model does not match your rules
- Bundles, gift cards, and shipping income often follow rules specific to your business. When the tool models them differently, finance ends up correcting its numbers by hand.
How we fix it
- 1
Write the metric dictionary first
Define net sales, contribution margin, CAC, and repeat rate once, each with an owner and the total it must tie to. Pick a closed month with finance as the tie-out month.
- 2
Load the core sources with a managed connector
Fivetran or Airbyte cover Shopify, Klaviyo, Meta, and Google Ads. Land them in BigQuery if you already use Google Cloud and Looker Studio, or Snowflake if your tools already write to it.
- 3
Model one order table and report on it
Build one order table with refunds and discounts, a dated product cost table, and a customer table in dbt. Show them in Looker Studio for a free founder scorecard, or Sigma when finance wants a spreadsheet on live data.
What you end up with
- A metric dictionary with an owner for each number
- One order table with refunds and discounts
- A dated product cost table
- A daily scorecard that ties to Shopify net sales
Common questions
Does a single Shopify store need a data warehouse?
Usually not at first. Shopify Analytics and one packaged tool cover most questions about ads, profit per order, and repeat purchase. A warehouse makes sense when finance needs numbers that tie to the books and the packaged figures do not, or when several stores need the same definitions.
Which packaged tool should we start with?
Start with Shopify Analytics. Then add one focused tool for the main question: a profit app such as Lifetimely, BeProfit, or TrueProfit; Triple Whale for attribution; Peel for retention; or Polar if you want a warehouse without a data team. Check current plans with each vendor.
Should we use BigQuery or Snowflake?
Use the cloud you already work in. BigQuery fits if you already use Google Cloud and Looker Studio. Snowflake fits if your tools or a packaged product already write to it.
What should we define before building anything?
Write down net sales, contribution margin, CAC, and repeat rate, each with one owner who approves changes. Choose a closed month and record finance's totals for it, so every new number has something to match.
How long does a build take, and what does it cost?
It depends on your sources, how much history you need, data quality such as duplicates and missing costs, and how quickly owners agree on definitions. Cost depends on connector pricing at your volume, warehouse compute, BI seats, and who maintains the models after launch.
Bring the numbers that don't add up
We'll trace them to the source with you and say where we'd start. No pitch deck.