How to run Shopify ABC product analysis with margin weighting, automation, and templates

Shopify ABC product analysis ranks SKUs by impact so you invest in winners, right-size stock, and improve profit. This guide shows exactly how to do it in Shopify using real data, Google Sheets, and automation.

What is ABC product analysis for Shopify stores?

Definition and when to use it

ABC analysis segments your catalog into three classes based on contribution to results (typically margin or revenue):

• A: small set of SKUs driving the majority of value

• B: mid performers worth optimizing

• C: long tail that requires control and selective investment

Use ABC to prioritize buying, replenishment, merchandising, and cycle counting. Recalculate on a cadence (monthly or quarterly) and whenever you change assortment or pricing.

ABC vs Pareto vs XYZ

• Pareto describes the 80/20 pattern. ABC turns that idea into explicit classes with thresholds.

• XYZ classifies demand variability:

  • X: stable demand (low variability)

  • Y: moderate variability

  • Z: highly variable or intermittent

• Combine them (e.g., AX, BY, CZ) to choose different forecasting and safety stock strategies.

Which metric to classify: revenue, units, or margin

• If you have cost per item, use contribution margin dollars per SKU. It aligns priority with profit.

• If cost is missing, use net sales revenue as a proxy.

• Units sold can help if pricing varies widely or for operational workloads.

• Advanced: multi-criteria scoring (e.g., margin dollars + units + GMROI) for nuanced catalogs.

Benefits and use cases

Inventory investment and cash flow impact

• Focus working capital on A-class to raise availability where it matters.

• Reduce excess on C-class to free cash and lower holding costs.

• Improve GMROI by rebalancing buys toward high-margin, high-velocity SKUs.

Merchandising and marketing prioritization

• Feature A-class in ads, email, and site placements; protect them from stockouts.

• Use B-class for testing new creatives and pricing.

• Bundle or promote C-class thoughtfully to clear space without cannibalizing A-class.

Cycle counting and operational focus

• Count A-class weekly, B-class monthly, C-class quarterly to maintain high inventory accuracy where risk is highest.

• Prioritize QA, product setup, and PDP improvements for A-class.

Get your data from Shopify

Reports/exports to use (Sales by product, product export)

You’ll need two datasets for the same time window:

• Sales by product/variant (Shopify Analytics > Reports):

  • Use Sales by product variant (or Sales by product) and include columns:

  • Product title, Variant title, Variant ID, SKU, Units sold, Gross sales, Discounts, Returns, Net sales

• Product catalog export (Products > Export):

  • Include: Variant ID, SKU, Title, Option values, Vendor, Product type, Cost per item, Status

  • Optional: inventory quantities by location via Inventory reports if doing location-level ABC

Join on Variant ID (most reliable) or SKU if consistent.

Time window and seasonality choices

• Standard: last 90 days for fast-moving catalogs; last 180–365 days for slower movers.

• Exclude partial current weeks to avoid incomplete data.

• For seasonal businesses, analyze season-to-date and the same season last year to set context.

Cleaning SKUs, variants, and mappings

• Remove gift cards, shipping lines, and non-merchandise items.

• Exclude zero-price test orders and internal transfers.

• Normalize SKUs and fix duplicates. Use Variant ID to avoid ambiguity.

• Aggregate at the variant level unless you truly stock and replenish at the parent product level.

Step-by-step: build an ABC in Google Sheets

Calculate value per SKU and cumulative percentages

Create a sheet with these columns:

• Variant ID, SKU, Title

• Units sold (period)

• Net sales (period)

• Cost per item

• COGS = Units sold × Cost per item

• Contribution margin = Net sales − COGS

• Share of total margin = Contribution margin ÷ SUM of all contribution margin

• Cumulative share = running total of Share of total margin after sorting by Contribution margin descending

If cost per item is unavailable, substitute Net sales for Contribution margin and proceed.

Example:

• SKU A1: Units 1,200, Net sales $36,000, Cost $20, COGS $24,000, Margin $12,000

• SKU B2: Units 400, Net sales $10,000, Cost $12, COGS $4,800, Margin $5,200

• SKU C3: Units 150, Net sales $3,600, Cost $15, COGS $2,250, Margin $1,350

Sort by Margin, compute each SKU’s share and cumulative share of total margin.

Set thresholds and assign A/B/C classes

Common thresholds by cumulative share of margin:

• A: top 70–80% of total margin

• B: next 15–25%

• C: remaining 5–10%

Assign class based on where each SKU’s cumulative share falls. If you prefer SKU-count constraints (e.g., A ≈ 10–20% of SKUs), fine-tune cutoffs to fit both share and count.

Quality checks, edge cases, and validation

• Negative or near-zero margin: flag for price/cost review; treat as C or “X” (exception).

• Outliers: a single promo may inflate a SKU; review price changes, stockouts, or one-off events.

• Refund-heavy SKUs: consider net units after returns; products with high return rates might be downgraded.

• Compare revenue-based vs margin-based ABC; large differences indicate pricing or cost opportunities.

• Sanity check with teams: do A-class SKUs match what merchandising and ops see as core?

Margin-weighted and multi-criteria ABC

Compute contribution margin per SKU

Use margin dollars, not percentages, to drive ABC:

• Contribution margin = Net sales − (Units sold × Cost per item)

• Optional: subtract variable selling fees (payment processing, marketplace commissions) if you track them per SKU. If you only know an average rate, apply a uniform percentage.

Margin-based ABC aligns priority with profit impact, especially when prices and costs vary.

Weighted scoring approach

If you want a balanced view:

• Normalize each metric to a 0–100 score (e.g., percentile rank):

  • Margin dollars (weight 60%)

  • Units sold (weight 20–30%)

  • GMROI or sell-through (weight 10–20%)

• Composite score = SUM(weight × metric score)

• Rank by composite score and assign A/B/C with similar cumulative thresholds.

This reduces bias toward high-priced items and highlights consistently productive SKUs.

When and how to adjust thresholds

• High-SKU catalogs: tighten A to 60–70% share to keep A-class operationally manageable.

• Luxury/high-ASP: use margin-based ABC with a stricter A threshold; one SKU can dominate.

• Launch-heavy or seasonal: loosen B band to keep more items under observation during validation periods.

Automate ABC inside Shopify

Tag products by class with Shopify Flow

On plans that support Shopify Flow:

• Trigger options:

  • Scheduled trigger (e.g., weekly) if available

  • Order created or inventory quantity changed for rolling updates

• Data source:

  • Store A/B/C in a Google Sheet, export a CSV with Variant ID and Class

  • Import to Shopify via a routine (e.g., admin import or app) to update a product metafield with the class

• Flow actions:

  • If product metafield abc_class = A, add tag “class:A”; else if B, tag “class:B”; else “class:C”

  • Remove outdated class tags before adding the new one

If Flow scheduling is not available, run a manual update on a regular cadence and let Flow maintain tags on create/update events.

Build smart collections and admin views

• Smart collections:

  • “A-Class Bestsellers”: Product tag equals class:A

  • “C-Class Long Tail”: Product tag equals class:C

• Saved admin views:

  • Inventory view filtered by class tags and low stock thresholds

  • Orders view highlighting A-class items for expedited handling

Alerts and tasks for restock and exceptions

• Low-stock alerts:

  • Trigger when Inventory quantity < Reorder point (store ROP per SKU in a metafield)

  • Notify via email or Slack, include vendor and lead time

• Exceptions:

  • Flag A-class with stockout days > 0

  • Flag C-class with >120 days of supply or zero sales last 90 days

  • Flag negative-margin SKUs for immediate pricing/cost review