Analyze Shopify Product Mix and 80/20 SKU Concentration from CSV

How to export line-item sales data from Shopify

1. In your Shopify Admin, navigate to Orders.
2. Click Export, select your date window, and choose CSV for Excel, Numbers, or other spreadsheet programs.
3. Download orders_export.csv. Drop it above for a first chart, or import it into the xmetric app to build the Pareto view.

Shopify CSV columns used for SKU and product mix analysis

Unlike order-level financial fields that only appear on the first row of an order, Shopify populates line-item columns on every row of orders_export.csv:

• Lineitem sku and Lineitem name: product and variant identifiers.
• Lineitem quantity: units sold on that line.
• Lineitem price: unit price before line-item discounts.
• Lineitem discount: discount applied to that line item.
• Name, Email, and Created at: used to classify whether each line item was bought on a customer's 1st order (Acquisition SKU) or a 2nd+ order (Expansion/Repeat SKU).

Tableau Table Calculations, LODs, and SQL for Product Mix

1. Line-Item Net Sales

// [Line Net Sales]
([Lineitem quantity] * [Lineitem price]) - IFNULL([Lineitem discount], 0)

2. Cumulative 80/20 Pareto Share (Table Calculation)

In the xmetric app, place [Lineitem sku] on Rows sorted descending by SUM([Line Net Sales]), and add this running-total table calculation on a secondary axis:

// [Cumulative Revenue %]
RUNNING_SUM(SUM([Line Net Sales])) / TOTAL(SUM([Line Net Sales]))

3. First-Order vs. Repeat-Order SKU Mix (FIXED LOD)

Find which products bring new customers in the door versus which products existing customers come back to buy. You can test these formulas in the formula playground:

// [Customer First Order Date]
{ FIXED [Email] : MIN([Created at]) }

// [Order Type]
IF [Created at] = [Customer First Order Date] THEN "1st Order (Acquisition)"
ELSE "Repeat Order"
END

Equivalent SQL

WITH customer_first AS (
  SELECT
    LOWER(TRIM("Email")) AS customer_email,
    MIN(CAST("Created at" AS TIMESTAMP)) AS first_order_at
  FROM shopify_orders
  WHERE "Email" IS NOT NULL AND TRIM("Email") <> ''
  GROUP BY 1
),
sku_sales AS (
  SELECT
    COALESCE(NULLIF(TRIM("Lineitem sku"), ''), "Lineitem name") AS sku,
    "Lineitem name" AS product_name,
    SUM(CAST("Lineitem quantity" AS INTEGER)) AS units_sold,
    SUM(
      CAST("Lineitem quantity" AS DOUBLE) * CAST("Lineitem price" AS DOUBLE)
      - COALESCE(CAST(NULLIF("Lineitem discount", '') AS DOUBLE), 0)
    ) AS net_line_sales,
    COUNT(DISTINCT CASE WHEN CAST(o."Created at" AS TIMESTAMP) = c.first_order_at THEN o."Name" END) AS first_time_orders,
    COUNT(DISTINCT CASE WHEN CAST(o."Created at" AS TIMESTAMP) > c.first_order_at THEN o."Name" END) AS repeat_orders
  FROM shopify_orders o
  LEFT JOIN customer_first c
    ON LOWER(TRIM(o."Email")) = c.customer_email
  GROUP BY 1, 2
)
SELECT
  sku,
  product_name,
  units_sold,
  ROUND(net_line_sales, 2) AS net_sales,
  ROUND(100.0 * net_line_sales / NULLIF(SUM(net_line_sales) OVER (), 0), 1) AS revenue_share_pct,
  ROUND(
    100.0 * SUM(net_line_sales) OVER (ORDER BY net_line_sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    / NULLIF(SUM(net_line_sales) OVER (), 0),
    1
  ) AS cumulative_pareto_pct,
  first_time_orders,
  repeat_orders
FROM sku_sales
ORDER BY net_line_sales DESC;

Data quirks in Shopify line-item exports

• Financial Status blanks on row 2+ of an order: In Shopify's CSV format, Financial Status is printed only on the first row of a multi-item order and left blank on additional line items of the same order. Always propagate order status across the whole order using { FIXED [Name] : MAX([Financial Status]) } before filtering to paid.
• Renamed product titles vs. stable SKUs: If you changed a product's marketing title mid-year, Lineitem name splits into two rows while Lineitem sku stays unified. Group by Lineitem sku and take MAX([Lineitem name]) as the display label.

Query your Shopify catalog with AI agents

Every chart and formula in xmetric is controlled by a command. An MCP server for Claude and ChatGPT is in development, so your agent will be able to find dead-stock SKUs and top bundle pairs in a CSV export. Live connections to Postgres and BigQuery are also coming.