Shopify data analytics: compare orders and margin

Shopify data analytics compares orders, refunds and supplied costs to show which products produce profitable sales. Start with reconciled net sales, then calculate a clearly labeled margin for the same products and period. Keep the order-line references so a surprising result can be traced to the transactions behind it.

Shopify's analytics page describes reporting on sales across channels, traffic and conversion. This file-based review answers a narrower finance question: how much contribution remains after the costs your business has supplied? Covirage is not affiliated with Shopify.

Decide what the review measures

Use net item sales as the revenue base. Exclude sales tax collected and keep customer shipping charges separate unless your written definition includes them. Deduct discounts and refunds once, using amounts allocated to the relevant products.

Then choose a cost boundary. Gross profit subtracts the cost of goods sold. The contribution used here also subtracts payment fees and outbound shipping expense. It excludes advertising, salaried staff, software subscriptions and other overhead, so it is not net profit.

The distinction matters when comparing a product with inexpensive shipping against a bulky item. Use ecommerce gross margin for the product-cost layer; add other variable costs only when the source records support them.

The IRS guide to business income and gross profit explains that returns and allowances reduce gross receipts and that goods cost is subtracted from net receipts. Its tax guidance is a useful reference for the separation, while your finance team controls the company's reporting policy.

Assemble the files without multiplying orders

Keep one identifiable record per order line in the sales working file. Useful columns include order ID, line ID, order date, SKU, quantity, item sales before adjustments, allocated discount and refund amount. A product-level summary should still let the reviewer find those original lines.

Bring refunds with both the original order reference and processing date. Add product costs effective when the goods were sold, along with actual fee and outbound shipping records. Use an explicit allocation basis where an expense covers the whole order. For example, allocate a shared shipping bill consistently rather than attaching its full amount to every item.

Do not assume a refund restores inventory cost. A returned, resalable item and a refund for damaged goods have different inventory consequences. The supplied goods cost below is already net of the cost reversals finance approved for actual returns. Credit notes and returns in revenue measures explains the broader period matching issue.

Work a product comparison from the supplied rows

An illustrative US accessories seller reviews a closed reporting period in USD. The table uses a period activity basis: refunds processed during that period reduce its sales, even when the original order was earlier. All product allocations and cost adjustments have been checked before this summary is prepared.

Net item sales = Item sales before adjustments − Discounts − Refunds

Contribution = Net item sales − Net goods cost − Payment fees − Outbound shipping

Contribution margin = Contribution / Net item sales

With the product label in A and the remaining columns in the order shown, calculate the first data row as follows:

E2 = B2-C2-D2
I2 = E2-F2-G2-H2
J2 = IF(E2=0,"",I2/E2)

Fill down, then calculate total margin as =SUM(I2:I4)/SUM(E2:E4). Dollar figures are exact in this example; percentages are rounded to one decimal place after calculation. A zero or negative sales denominator needs separate review rather than an ordinary product ranking.

The reconciliation is $7,400 − $300 − $600 = $6,500 in net sales. Then $6,500 − $2,268 − $123 − $504 = $3,605. Both the product contributions and the source expense summaries must tie to those totals. The Tote has the highest margin, while the Bottle contributes the largest dollar amount. Those are different merchandising signals.

Compare periods on the same basis

Repeat the same definition for an earlier period of equal length and scope. The earlier inputs below are supplied period totals; the current row comes directly from the product table.

Sales increased by $500 and contribution by $305. Investigate product mix, realized selling price and costs before attributing the difference to a campaign. Diagnostic analytics covers that investigation; a period comparison alone does not establish why a change occurred.

Finance owns the revenue and cost definitions. Ecommerce operations owns order status and refund completeness, and merchandising owns the resulting assortment and promotion decisions. If the next question concerns software selection rather than the calculation, see Shopify analytics apps.

Where it goes wrong

• An order-level amount repeated across lines. An order discount or shipping cost must be allocated or counted once before grouping by product.
• Refund activity mixed with order cohorts. Those views answer different questions. Reconcile each using its stated dates.
• Current product cost applied to old orders. Use the supplied historical cost basis consistently, and flag missing costs rather than treating them as zero.
• Contribution called profit. Expenses outside the calculation still exist. State the included costs beside the measure.
• Incomparable periods. Currency, channel coverage and period length must match before reading a product or period variance.

Do this on your own order files in Covirage

Bring a sample of your own order, refund, product cost and expense files to Covirage. Covirage maps the columns, and its tools compute and check product and period comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your exports.

See customer profitability for the supporting business use case.