Analyze Shopify Discount Code Usage and Revenue Leakage from CSV

How to export discount and order data from Shopify

1. In your Shopify Admin, go to Orders.
2. Click Export, select All orders (or your campaign 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 discount audit.

Shopify CSV columns used for discount analysis

• Name: order number (#1001), used to prevent summing Discount Amount multiple times on multi-item orders.
• Discount Code: the promo code entered at checkout (such as WELCOME15 or BFCM30).
• Discount Amount: total order-level discount applied, in your store currency.
• Subtotal: order product subtotal before shipping and taxes.
• Lineitem discount: product-specific discount per line item.
• Email and Created at: used to test whether customers acquired via a discount code ever place a second full-price order.

Tableau LOD formulas and SQL for discount code audits

You can test each formula below in the formula playground before building the view.

1. Deduplicated Order Discount and Gross Merchandise Value (FIXED LOD)

Because Shopify prints Discount Code, Discount Amount, and Subtotal on the first row of each order Name, wrap order-level measures in a { FIXED [Name] } expression:

// [Order Discount Amount]
{ FIXED [Name] : MAX(IFNULL([Discount Amount], 0)) }

// [Order Net Subtotal]
{ FIXED [Name] : MAX(IFNULL([Subtotal], 0)) }

// [Order Gross Value Before Discount]
[Order Net Subtotal] + [Order Discount Amount]

2. Effective Discount Rate %

// [Effective Discount %]
SUM([Order Discount Amount]) / SUM([Order Gross Value Before Discount])

3. Do Discount Buyers Ever Return? (SQL equivalent)

This equivalent SQL shows not just how much each Discount Code cost you in margin, but whether those buyers ever placed a 2nd order:

WITH orders_deduped AS (
  SELECT
    "Name" AS order_name,
    LOWER(TRIM("Email")) AS customer_email,
    MIN(CAST("Created at" AS TIMESTAMP)) AS order_at,
    COALESCE(NULLIF(UPPER(TRIM(MAX("Discount Code"))), ''), '(No Discount)') AS discount_code,
    MAX(CAST(COALESCE(NULLIF("Discount Amount", ''), '0') AS DOUBLE)) AS discount_amount,
    MAX(CAST(COALESCE(NULLIF("Subtotal", ''), '0') AS DOUBLE)) AS net_subtotal
  FROM shopify_orders
  WHERE "Email" IS NOT NULL AND TRIM("Email") <> ''
  GROUP BY 1, 2
),
customer_orders AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY customer_email ORDER BY order_at, order_name) AS order_num,
    COUNT(*) OVER (PARTITION BY customer_email) AS customer_lifetime_orders
  FROM orders_deduped
)
SELECT
  discount_code,
  COUNT(DISTINCT order_name) AS total_orders,
  ROUND(SUM(net_subtotal + discount_amount), 2) AS gross_sales,
  ROUND(SUM(discount_amount), 2) AS total_discount_given,
  ROUND(SUM(net_subtotal), 2) AS net_sales,
  ROUND(100.0 * SUM(discount_amount) / NULLIF(SUM(net_subtotal + discount_amount), 0), 1) AS effective_discount_pct,
  ROUND(AVG(net_subtotal), 2) AS net_aov,
  ROUND(
    100.0 * COUNT(DISTINCT CASE WHEN order_num = 1 AND customer_lifetime_orders >= 2 THEN customer_email END)
    / NULLIF(COUNT(DISTINCT CASE WHEN order_num = 1 THEN customer_email END), 0),
    1
  ) AS first_order_repeat_rate_pct
FROM customer_orders
GROUP BY 1
ORDER BY total_discount_given DESC;

Spotting leaked influencer and welcome codes

• Repeat buyers reusing WELCOME10: Filter to order_num > 1 (repeat orders) and sort by Discount Code. If a code meant only for first-time subscribers has hundreds of redemptions on 2nd, 3rd, and 4th orders, turn on Limit to one use per customer under Maximum discount uses in Shopify Discounts.
• Case sensitivity in exported promo codes: Shopify exports Discount Code in the casing entered or configured over time (welcome10 vs. WELCOME10). Use UPPER(TRIM([Discount Code])) so identical codes group into a single bar.

Audit promo margin with coding agents

Every action in xmetric is a command. An MCP server for Claude and ChatGPT is in development, so your assistant will be able to audit promo leakage in a Shopify CSV. Live connections to Postgres and BigQuery are also coming.