Home / Connectors / Shopify
Connector

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.

9 datasets28 metric definitionsRead-onlyRefreshes daily11 cross-source metrics

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

DatasetWhat it holds
[Shopify] OrdersOne 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] ProductsOne 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 historyShopify 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 itemsOne 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] RefundsOne 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] SalesOne 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 transactionsOne 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] PayoutsOne 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] CustomersOne 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.

Highlighted numbers, such as 0.08, are assumptions Quiriz starts with because the data does not hold the real figure. Change them in Company Context to your own rates.

[Shopify] Orders

MetricHow it is calculatedDated byNotes
OrdersCount of distinct order_idorder_dateOrders placed, by order date. Includes cancelled orders and excludes test orders, as Shopify counts them.
Order valueSum of order_valueorder_dateSubtotal minus discounts per order: the numerator of Shopify's average order value.
Average order valueOrder value ÷ Ordersorder_dateOrder value divided by orders (Shopify's AOV: before returns, shipping and tax).
New customersCount of distinct customer_id, where is_first_order = 1order_dateCustomers placing their first order, by order date. Guest checkouts have no customer record and are not counted.
First-order valueSum of order_value, where is_first_order = 1order_dateSubtotal minus discounts on first orders, by order date, before returns, shipping and tax.
First-order AOVFirst-order value ÷ New customersorder_dateFirst-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

MetricHow it is calculatedDated byNotes
Units soldSum of qty, where is_gift_card = 0order_dateUnits ordered, by order date. Gift cards excluded.

[Shopify] Sales

MetricHow it is calculatedDated byNotes
Gross salesSum of gross_salessale_dateProduct sales before discounts and returns, without shipping, tax or gift cards (Shopify's definition).
DiscountsSum of discountssale_dateProduct discounts, shown negative as in Shopify.
ReturnsSum of returnssale_dateValue of returned goods, counted on the refund date and shown negative as in Shopify.
Net salesSum of net_salessale_dateGross sales + discounts + returns, with returns on the refund date: matches Shopify's sales report.
Total salesSum of total_salessale_dateNet sales plus shipping and taxes.
New customer net salesSum of net_sales, where is_first_order = 1sale_dateNet 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 salesSum of net_sales, where customer_type = 'returning'sale_dateNet sales from later orders by customers who had ordered before, by sale date. Guest checkouts are excluded.
Returning customer revenue shareReturning customer net sales ÷ Net salessale_dateReturning-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 salessale_dateValue 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 soldSum of cogssale_dateUnit 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 costSum of net_sales, where cost_source = 'none' AND line_type = 'product'sale_dateNet 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 costNet sales × 0.08sale_dateASSUMPTION: 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 feesTotal sales × 0.03sale_dateASSUMPTION: 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 adsNet sales − Cost of goods sold − Shipping and fulfillment cost − Estimated payment feessale_dateNet 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 adsContribution margin before ads ÷ Net salessale_dateContribution margin before ads as a share of net sales (0.35 = 35%).
Break-even MERNet sales ÷ Contribution margin before adssale_dateThe 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

MetricHow it is calculatedDated byNotes
Payment processing feesSum of feetransaction_dateFees 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

MetricHow it is calculatedDated byNotes
PayoutsSum of netpayout_dateNet 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

MetricHow it is calculatedDated byNotes
First-order customersCount of distinct customer_idfirst_order_dateCustomers by the date of their first order, for customers who ordered inside the imported orders. The group a repeat rate is measured on.
Repeat customersCount of distinct customer_id, where orders_count >= 2first_order_dateFirst-order customers who have placed at least two orders to date (orders_count is the lifetime count at the last refresh).
Repeat purchase rateRepeat customers ÷ First-order customersfirst_order_dateShare 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.

MetricHow it is calculatedNeedsNotes
Meta Ads nCACMeta Ads spend ÷ New customersShopify + Meta AdsMeta Ads spend per new customer: Meta Ads spend divided by New customers in the same period.
Meta Ads aMERNew customer net sales ÷ Meta Ads spendShopify + Meta AdsFirst-order net sales per Meta Ads dollar: New customer net sales divided by Meta Ads spend in the same period.
Google Ads nCACGoogle Ads spend ÷ New customersShopify + Google AdsGoogle Ads spend per new customer: Google Ads spend divided by New customers in the same period.
Google Ads aMERNew customer net sales ÷ Google Ads spendShopify + Google AdsFirst-order net sales per Google Ads dollar: New customer net sales divided by Google Ads spend in the same period.
Microsoft Ads nCACMicrosoft Ads spend ÷ New customersShopify + Microsoft AdvertisingMicrosoft Ads spend per new customer: Microsoft Ads spend divided by New customers in the same period.
Microsoft Ads aMERNew customer net sales ÷ Microsoft Ads spendShopify + Microsoft AdvertisingFirst-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 feesShopifyNet 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)ShopifyNet 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 customersShopify + Meta Ads + Google AdsMeta Ads and Google Ads spend per new customer.
Blended aMERNew customer net sales ÷ (Meta Ads spend + Google Ads spend)Shopify + Meta Ads + Google AdsFirst-order net sales per paid ad dollar across Meta Ads and Google Ads.
Blended MERNet sales ÷ (Meta Ads spend + Google Ads spend)Shopify + Meta Ads + Google AdsMarketing 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.

Questions to ask

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?
No. The Shopify connector is read-only. Quiriz reads the data into datasets and never writes back to Shopify.
How often does the Shopify data refresh?
Once a day. Each dataset shows when it was last updated, and answers use the latest refresh.
Can I change how a metric is calculated?
Yes. The definitions are added to Company Context, where an admin can edit the formula, the filter or the date it uses. A daily refresh never overwrites a definition someone has changed.
Can I combine Shopify with other sources?
Yes. 11 cross-source metrics are added automatically when the other connector is there too. An admin can also define a metric from metrics on different datasets. See cross-source metrics.

All connectors: Quiriz connectors. Definitions on this page are generated from the product’s own connector code.