Which discount codes are actually driving profitable revenue?

Q: What database does this query work with?

This query is designed for Shopify. Taptic Data reads your live schema, so the generated SQL always matches your actual table and column names.

Discounts drive volume, but not all discounts drive profit. This query measures each discount code's total revenue, discount amount given, and effective ROI so you can cut the losers and double down on the winners.

📊 Shopify👥 Shopify merchants and marketing teams🔤 Plain-English → SQL

This is part of the Taptic Data Shopify query library. Explore related queries: SQL query for Shopify revenue by month, SQL query for Shopify customer lifetime value, SQL query for Shopify refund rate by product, and more.

The question

Show me discount code ROI — revenue, discount given, and net contribution for each code used in the last 90 days

↓

The SQL Taptic generates

Shopify Discount Code ROI — generated by Taptic Data AI

SELECT
  COALESCE(NULLIF(o.discount_codes, '[]'), '[{"code":"No discount"}]')::jsonb
    -> 0 ->> 'code'                                  AS discount_code,
  COUNT(DISTINCT o.id)                               AS orders,
  COUNT(DISTINCT o.customer_id)                     AS unique_customers,
  ROUND(SUM(o.total_price)::numeric, 2)            AS gross_revenue,
  ROUND(SUM(o.total_discounts)::numeric, 2)        AS total_discount_given,
  ROUND(SUM(o.total_price - o.total_discounts)::numeric, 2) AS net_after_discount,
  ROUND(AVG(o.total_price)::numeric, 2)            AS avg_order_value,
  ROUND(
    SUM(o.total_discounts)::numeric * 100
    / NULLIF(SUM(o.total_price)::numeric, 0), 1
  )                                                 AS discount_as_pct_of_revenue
FROM shopify_orders o
WHERE o.financial_status IN ('paid', 'partially_refunded')
  AND o.cancelled_at IS NULL
  AND o.total_discounts > 0
  AND o.created_at >= NOW() - INTERVAL '90 days'
GROUP BY discount_code
HAVING COUNT(DISTINCT o.id) >= 3
ORDER BY gross_revenue DESC

Schema-aware SQL generated from plain English21 lines

This query was generated by Taptic Data from plain English against a real Shopify schema. In Taptic, you type the question — the AI writes the SQL, runs it, and returns the result. You can edit the SQL, ask for explanations, and save it as a refreshable report.

How this query works

Breaking it down line by line

Extracts the first discount code from the JSON discount_codes array on each order
Calculates both the discount given and the net revenue after discount per code
discount_as_pct_of_revenue shows the effective discount rate — a 30% code applied to high-value orders might still yield great net revenue
HAVING >= 3 orders filters out one-time test codes
Sorted by gross revenue descending so your most-used codes appear first

What it returns

Result description

Per-discount-code breakdown with orders, customers, revenue, discount amount, net after discount, and effective discount percentage.

Why it matters

The business impact

A 20% off code that brings in 500 new customers who never return is worth less than a 10% code that converts 50 repeat buyers. Measuring discount code ROI lets you identify which promotions build real value versus which ones just erode margin.

FAQ

Common questions

How do I generate a "Shopify Discount Code ROI" query automatically?

In Taptic Data, type "Show me discount code ROI — revenue, discount given, and net contribution for each code used in the last 90 days" and the AI generates schema-aware SQL against your real Shopify data — no manual writing required.

What database does this query work with?

This query is designed for Shopify. Taptic reads your live schema so the generated SQL always matches your actual table and column names.

Can I edit the generated SQL?

Yes. Taptic shows you the exact SQL it generated. You can edit it directly, ask the AI to explain any line, or request a revision in plain English.

Can I save this as a scheduled report?

Yes. Once you run this query in Taptic, you can save it as a report, add charts and KPIs, and schedule it to email your team on any cadence — daily, weekly, or monthly.

Generate this automatically

Skip the SQL. Ask the question.

In Taptic Data, you type "Show me discount code ROI — revenue, discount given, and net..." and this SQL runs automatically against your real Shopify data.

Try Taptic Free — $29.99/mo

No credit card required. Connect your data source in under 5 minutes.

Compare tools

Compare Taptic Data vs Sellerboard →