Shopify connector: datasets and metric definitionsBeta
Bring in orders, line items, refunds, a sales ledger that matches Shopify reports, products and customers, plus variant cost history and contribution margin inputs. Updates daily.
When you connect Shopify, Quiriz imports the datasets below and adds the metric definitions to Company Context, the one place your company’s definitions live. Every answer that uses one of these metrics uses the same definition, in the app, in Slack and in the Excel, Sheets, PowerPoint, Slides, Word and Docs add-ins. An admin can edit any of them.
DatasetsMetric definitionsWorks withQuestionsFAQ
Datasets
| Dataset | What it holds |
|---|---|
| [Shopify] Orders | One row per order in the store currency, dated in the store time zone. subtotal is before discounts and excludes shipping and gift cards; net_sales = subtotal - discount - returned goods, by order date (the Sales dataset counts returns on the refund date). Test orders excluded.25 columnsorder_id, order_name, order_date, financial_status, fulfillment_status, is_cancelled, subtotal, discount, order_value, shipping, tax, total, refunded_amount, net_sales, items_count, sales_channel, source_name, utm_source, utm_medium, utm_campaign, customer_id, is_first_order, discount_codes, tags, customer_type |
| [Shopify] Products | One row per product variant. cost is the variant's current unit cost, not its cost when each order was placed.13 columnsproduct_id, variant_id, product_title, variant_title, sku, product_type, vendor, status, price, compare_at_price, cost, inventory_quantity, created_at |
| [Shopify] Variant cost history | Shopify keeps only each variant's current unit cost, so this dataset records the cost from the first sync on: one row each time a variant's cost is first seen or changes (previous_cost is the cost before; blank when new). A variant's cost on a date is its latest row on or before that date; an order older than the variant's first row uses that first cost. History starts when Quiriz first synced the store. Never add rows together.10 columnssnapshot_date, product_id, variant_id, sku, product_title, variant_title, cost, previous_cost, price, change_type |
| [Shopify] Order line items | One row per order line. line_total = line_gross - line_discount. Gift cards are flagged with is_gift_card.18 columnsline_item_id, order_id, order_name, order_date, product_id, variant_id, product_title, variant_title, sku, product_type, vendor, qty, unit_price, line_gross, line_discount, line_total, refunded_qty, is_gift_card |
| [Shopify] Refunds | One row per refund. amount = value of returned goods without tax or shipping; total_refunded = all money returned to the customer.9 columnsrefund_id, order_id, order_name, refund_date, amount, shipping_refunded, tax_refunded, total_refunded, items_returned |
| [Shopify] Sales | One row per dated sale or return line, like Shopify's sales report: orders on the order date, returns (negative) on the refund date. net_sales = gross_sales + discounts + returns. Gift cards excluded.28 columnssale_date, order_id, order_name, event_type, line_type, product_id, product_title, variant_title, sku, product_type, vendor, quantity, gross_sales, discounts, returns, net_sales, shipping, taxes, total_sales, sales_channel, utm_source, utm_medium, utm_campaign, customer_id, is_first_order, customer_type, cogs, cost_source |
| [Shopify] Payments transactions | One row per Shopify Payments balance transaction, like the Payments balance report: gross amount, processing fee and net, by transaction date in the store time zone. type is charge, refund, adjustment, payout and so on; fee is what Shopify Payments kept. Only Shopify Payments; no PayPal or other gateway. Amounts are in the payments currency (currency column); never add currencies together. Last 24 months.12 columnstransaction_id, transaction_date, type, amount, fee, net, currency, order_id, order_name, order_transaction_id, payout_id, payout_status |
| [Shopify] Payouts | One row per Shopify Payments payout: the net amount sent to the bank account and the date it was issued. Payouts arrive days after the sales they pay for, so a month of payouts does not equal a month of net sales. Last 24 months.5 columnspayout_id, payout_date, status, net, currency |
| [Shopify] Customers | One row per customer who placed an order in the imported orders, without names, emails, phones or addresses. first_order_date comes from those orders.8 columnscustomer_id, created_at, orders_count, amount_spent, first_order_date, last_order_date, marketing_state, tags |
Metric definitions
Each metric is a total over the period you ask about. A metric built from other metrics, such as a rate, divides the totals for the whole period; it does not average daily rates. Dated by is the date column that decides which period a row belongs to.
[Shopify] Orders
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Orders | Count of distinct order_id | order_date | Orders placed, by order date. Includes cancelled orders and excludes test orders, as Shopify counts them. |
| Order value | Sum of order_value | order_date | Subtotal minus discounts per order: the numerator of Shopify's average order value. |
| Average order value | Order value ÷ Orders | order_date | Order value divided by orders (Shopify's AOV: before returns, shipping and tax). |
| New customers | Count of distinct customer_id, where is_first_order = 1 | order_date | Customers placing their first order, by order date. Guest checkouts have no customer record and are not counted. |
| First-order value | Sum of order_value, where is_first_order = 1 | order_date | Subtotal minus discounts on first orders, by order date, before returns, shipping and tax. |
| First-order AOV | First-order value ÷ New customers | order_date | First-order value divided by new customers: what a customer spends on their first order, before returns, shipping and tax. Guest checkouts are excluded from both. |
[Shopify] Order line items
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Units sold | Sum of qty, where is_gift_card = 0 | order_date | Units ordered, by order date. Gift cards excluded. |
[Shopify] Sales
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Gross sales | Sum of gross_sales | sale_date | Product sales before discounts and returns, without shipping, tax or gift cards (Shopify's definition). |
| Discounts | Sum of discounts | sale_date | Product discounts, shown negative as in Shopify. |
| Returns | Sum of returns | sale_date | Value of returned goods, counted on the refund date and shown negative as in Shopify. |
| Net sales | Sum of net_sales | sale_date | Gross sales + discounts + returns, with returns on the refund date: matches Shopify's sales report. |
| Total sales | Sum of total_sales | sale_date | Net sales plus shipping and taxes. |
| New customer net sales | Sum of net_sales, where is_first_order = 1 | sale_date | Net sales from first orders (each customer first order, with returns on those orders netted on the refund date), by sale date. Guest checkouts have no customer record and are not counted. |
| Returning customer net sales | Sum of net_sales, where customer_type = 'returning' | sale_date | Net sales from later orders by customers who had ordered before, by sale date. Guest checkouts are excluded. |
| Returning customer revenue share | Returning customer net sales ÷ Net sales | sale_date | Returning-customer net sales divided by all net sales, guest checkouts included in the denominator: the share of revenue from repeat buyers (0.4 = 40%). |
| Refund rate | (0 − Returns) ÷ Gross sales | sale_date | Value of returned goods (without shipping or tax), counted on the refund date, divided by gross sales in the same period (0.05 = 5%). Not the share of orders refunded. |
| Cost of goods sold | Sum of cogs | sale_date | Unit cost of the goods sold, at the cost in force on the order date (Shopify unit cost history recorded since the first sync; an older order uses the first recorded cost, a store with no history yet uses current costs). A return reverses the cost it carried when sold, on the refund date. Gift cards and shipping carry no cost. Variants with no unit cost count as 0: see Net sales without a unit cost. |
| Net sales without a unit cost | Sum of net_sales, where cost_source = 'none' AND line_type = 'product' | sale_date | Net sales of product lines whose variant has no unit cost in Shopify. Cost of goods sold treats them as 0, so contribution margin is overstated by their share; fill in the cost in Shopify to fix it from the next sync. |
| Shipping and fulfillment cost | Net sales × 0.08 | sale_date | ASSUMPTION: shipping, packaging and fulfillment cost at 8% of net sales (net sales exclude the shipping customers pay). Edit the 0.08 in this formula in Company Context to your real rate, or replace it with a per-order figure. |
| Estimated payment fees | Total sales × 0.03 | sale_date | ASSUMPTION: card and gateway fees at 3% of total sales (what customers paid, shipping and tax included), roughly 2.9% plus a fixed fee per order averaged in. Edit the 0.03 in Company Context. Where Shopify Payments is connected, Contribution margin before ads (actual fees) uses the real fees instead. |
| Contribution margin before ads | Net sales − Cost of goods sold − Shipping and fulfillment cost − Estimated payment fees | sale_date | Net sales minus cost of goods sold, shipping and fulfillment, and payment fees: what is left to pay for advertising and overhead. Cost of goods and net sales are Shopify data; shipping and payment fees are the editable assumptions above. Not net profit: no ad spend, apps, salaries or other overhead. |
| Contribution margin rate before ads | Contribution margin before ads ÷ Net sales | sale_date | Contribution margin before ads as a share of net sales (0.35 = 35%). |
| Break-even MER | Net sales ÷ Contribution margin before ads | sale_date | The marketing efficiency ratio (net sales per ad dollar) at which ad spend uses up the whole contribution margin before ads: above it ads add profit, below it they lose money. Compare with MER. Uses the assumptions above. |
[Shopify] Payments transactions
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Payment processing fees | Sum of fee | transaction_date | Fees Shopify Payments charged, by transaction date, as its balance report shows (charges, refunds, chargebacks). Only Shopify Payments: PayPal and other gateways are not included. Needs the read_shopify_payments_payouts permission. |
[Shopify] Payouts
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| Payouts | Sum of net | payout_date | Net amount of Shopify Payments payouts, by the date each was issued. Payout timing differs from the sales ledger: a sale is paid out a few days later. Needs the read_shopify_payments_payouts permission. |
[Shopify] Customers
| Metric | How it is calculated | Dated by | Notes |
|---|---|---|---|
| First-order customers | Count of distinct customer_id | first_order_date | Customers by the date of their first order, for customers who ordered inside the imported orders. The group a repeat rate is measured on. |
| Repeat customers | Count of distinct customer_id, where orders_count >= 2 | first_order_date | First-order customers who have placed at least two orders to date (orders_count is the lifetime count at the last refresh). |
| Repeat purchase rate | Repeat customers ÷ First-order customers | first_order_date | Share of customers, grouped by first-order date, who have ordered again since (0.3 = 30%). A cohort rate with no fixed look-back: recent cohorts have had less time to reorder, and only customers in the imported orders count. |
Works with other connectors
These metrics are added automatically when Shopify and the other connector are both connected. The rules they follow are on the connectors page.
| Metric | How it is calculated | Needs | Notes |
|---|---|---|---|
| Meta Ads nCAC | Meta Ads spend ÷ New customers | Shopify + Meta Ads | Meta Ads spend per new customer: Meta Ads spend divided by New customers in the same period. |
| Meta Ads aMER | New customer net sales ÷ Meta Ads spend | Shopify + Meta Ads | First-order net sales per Meta Ads dollar: New customer net sales divided by Meta Ads spend in the same period. |
| Google Ads nCAC | Google Ads spend ÷ New customers | Shopify + Google Ads | Google Ads spend per new customer: Google Ads spend divided by New customers in the same period. |
| Google Ads aMER | New customer net sales ÷ Google Ads spend | Shopify + Google Ads | First-order net sales per Google Ads dollar: New customer net sales divided by Google Ads spend in the same period. |
| Microsoft Ads nCAC | Microsoft Ads spend ÷ New customers | Shopify + Microsoft Advertising | Microsoft Ads spend per new customer: Microsoft Ads spend divided by New customers in the same period. |
| Microsoft Ads aMER | New customer net sales ÷ Microsoft Ads spend | Shopify + Microsoft Advertising | First-order net sales per Microsoft Ads dollar: New customer net sales divided by Microsoft Ads spend in the same period. |
| Contribution margin before ads (actual fees) | Net sales − Cost of goods sold − Shipping and fulfillment cost − Payment processing fees | Shopify | Net sales minus cost of goods sold, shipping and fulfillment (assumption) and the Shopify Payments fees actually charged. Fees of other gateways are not included. |
| Break-even MER (actual fees) | Net sales ÷ Contribution margin before ads (actual fees) | Shopify | Net sales per ad dollar at which ad spend uses up the whole contribution margin before ads (actual fees). Above it ads add profit, below it they lose money. |
| Blended nCAC | (Meta Ads spend + Google Ads spend) ÷ New customers | Shopify + Meta Ads + Google Ads | Meta Ads and Google Ads spend per new customer. |
| Blended aMER | New customer net sales ÷ (Meta Ads spend + Google Ads spend) | Shopify + Meta Ads + Google Ads | First-order net sales per paid ad dollar across Meta Ads and Google Ads. |
| Blended MER | Net sales ÷ (Meta Ads spend + Google Ads spend) | Shopify + Meta Ads + Google Ads | Marketing efficiency ratio: Shopify net sales per paid ad dollar across Meta Ads and Google Ads, in the same period. Net sales are Shopify's own, not what the platforms report. |
- Shopify + Meta Ads: New-customer cost and return on Meta. Meta Ads nCAC and aMER are added for you: Meta spend against Shopify’s new customers and their first-order net sales.
- Shopify + Google Ads: New-customer cost and return on Google. Google Ads nCAC and aMER, the same pair for Google spend.
- Shopify + Microsoft Advertising: New-customer cost and return on Microsoft. Microsoft Ads nCAC and aMER, the same pair for Microsoft Advertising spend.
- Shopify + Meta Ads + Google Ads: Blended MER across ad platforms. With Meta and Google both connected, Blended nCAC, aMER and MER divide Shopify’s figures by the combined spend.
- Shopify + Klaviyo: Email and SMS revenue against store sales. Klaviyo flow and campaign revenue next to Shopify net sales, and why the two never match. Read the guide.
- Shopify + Google Analytics: Store revenue against analytics revenue. Why Shopify and GA4 disagree on revenue, and how to line them up by day. Read the guide.
- QuickBooks + Shopify: Books against the store. QuickBooks Revenue is the books; Shopify Net sales is the store’s own figure. Compare them by month to find what has not been booked. Define it as your own metric.
Questions to ask
- What were net sales by month this year?
- Which products drove the most net sales last quarter?
- What's our average order value by sales channel this year?
- How many new customers did we get each month?
- Which products are running low on inventory?
- Which products get returned most?
- What share of customers placed a second order, by month of first order?
Related guides
- Shopify Analytics, Reports and Decks From Your Own Store
- Ecommerce Contribution Margin, Break-Even ROAS and MER
- Shopify AI: Ask Your Store Data Questions
- Shopify MCP: Connecting Claude to Your Store, and the Questions Store Data Alone Cannot Answer
- Shopify Sales Report: Which One to Use and What the Columns Mean
- Net Sales vs Gross Sales in Shopify: Why Your Three Reports Disagree
- Break-Even ROAS Calculator: The Formula, a Worked Example, and Your Real Margin per Product
- Shopify Profit Calculator — Profit per Product, Free
Ask Shopify in plain words
Try the questions on sample data with no signup, then connect Shopify read-only and ask your own.
Try Quiriz free →Frequently asked questions
Does Quiriz change anything in Shopify?
How often does the Shopify data refresh?
Can I change how a metric is calculated?
Can I combine Shopify with other sources?
All connectors: Quiriz connectors. Definitions on this page are generated from the product’s own connector code.