Track Shopify Average Order Value (AOV) and Units per Order from CSV

How to export your Orders CSV from Shopify

1. In your Shopify Admin, click Orders.
2. Click Export, choose your date range, and select 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 AOV decomposition.

Shopify CSV columns used for AOV decomposition

• Name: unique order identifier (#1001), used as the denominator (COUNTD([Name])) for all order averages.
• Created at: order date and time.
• Subtotal: order product subtotal (populated on the first line-item row of each order).
• Lineitem quantity: units purchased on each line item row.
• Lineitem price and Lineitem discount: unit price and line discount.
• Shipping: shipping charge collected on the order.

Decomposing AOV into Basket Size (UPT) × Price per Unit (AUR)

When AOV drops by 12% month over month, a single AOV line chart cannot tell you whether customers are buying fewer items per basket (UPT) or cheaper/discounted items (AUR). Because AOV = Units per Transaction × Average Unit Retail, charting all three metrics together isolates the root cause. Test the formulas below in the formula playground, then put MONTH([Created at]) on Columns and the three measures on Rows in the xmetric app.

1. True Net AOV without Line-Item Distortion (FIXED LOD)

Never take AVG([Subtotal]) across raw rows of orders_export.csv, or blank rows on multi-item orders will skew the average. Instead, sum deduplicated order subtotals and divide by distinct order Name:

// [Net AOV]
SUM({ FIXED [Name] : MAX([Subtotal]) }) / COUNTD([Name])

2. Units per Transaction (UPT / Basket Size)

Because Lineitem quantity is recorded on every line item row, sum it across all rows and divide by distinct orders:

// [Units per Transaction (UPT)]
SUM([Lineitem quantity]) / COUNTD([Name])

3. Average Unit Retail (AUR)

// [Average Unit Retail (AUR)]
SUM({ FIXED [Name] : MAX([Subtotal]) }) / SUM([Lineitem quantity])

Equivalent SQL

WITH order_rollups AS (
  SELECT
    "Name" AS order_name,
    DATE_TRUNC('month', MIN(CAST("Created at" AS TIMESTAMP))) AS order_month,
    MAX(CAST(COALESCE(NULLIF("Subtotal", ''), '0') AS DOUBLE)) AS order_subtotal,
    MAX(CAST(COALESCE(NULLIF("Shipping", ''), '0') AS DOUBLE)) AS order_shipping,
    SUM(CAST("Lineitem quantity" AS INTEGER)) AS order_units
  FROM shopify_orders
  WHERE "Name" IS NOT NULL AND TRIM("Name") <> ''
  GROUP BY 1
)
SELECT
  order_month,
  COUNT(*) AS total_orders,
  SUM(order_units) AS total_units_sold,
  ROUND(AVG(order_subtotal), 2) AS mean_net_aov,
  ROUND(MEDIAN(order_subtotal), 2) AS median_net_aov,
  ROUND(1.0 * SUM(order_units) / NULLIF(COUNT(*), 0), 2) AS units_per_order_upt,
  ROUND(SUM(order_subtotal) / NULLIF(SUM(order_units), 0), 2) AS net_price_per_unit_aur
FROM order_rollups
GROUP BY 1
ORDER BY 1;

Setting your free-shipping threshold from the basket distribution

Mean AOV is pulled upward by wholesale or bulk orders. In the xmetric app, plot { FIXED [Name] : MAX([Subtotal]) } as a box plot, or count orders by an order-value bucket field, to see where most single-item and two-item baskets cluster. Setting your free-shipping threshold 15% to 20% above your modal / median order value nudges single-item shoppers to add a second item (UPT lift) without pricing out your core traffic.

Run basket diagnostics with AI agents

Every shelf placement and formula in xmetric is a command. An MCP server for Claude and ChatGPT is in development, so your assistant will be able to inspect a Shopify CSV, compare median vs. mean AOV, and test free-shipping thresholds. Live connections to Postgres and BigQuery are also coming.