Which traffic sources bring the highest-value orders?

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.

Not all traffic is equal. Customers from Google Ads might have a different average order value than those from Instagram or email. This query breaks down AOV by referring source so you can allocate marketing budget smarter.

📊 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 average order value by traffic source for the last 90 days

↓

The SQL Taptic generates

Shopify Average Order Value by Acquisition Source — generated by Taptic Data AI

SELECT
  COALESCE(NULLIF(o.referring_site, ''), 'Direct / Unknown') AS traffic_source,
  COUNT(DISTINCT o.id)                                       AS orders,
  COUNT(DISTINCT o.customer_id)                             AS unique_customers,
  ROUND(SUM(o.total_price)::numeric, 2)                    AS total_revenue,
  ROUND(AVG(o.total_price)::numeric, 2)                    AS avg_order_value,
  ROUND(SUM(o.total_price)::numeric
    / NULLIF(COUNT(DISTINCT o.customer_id), 0), 2)         AS revenue_per_customer
FROM shopify_orders o
WHERE o.financial_status IN ('paid', 'partially_refunded')
  AND o.cancelled_at IS NULL
  AND o.created_at >= NOW() - INTERVAL '90 days'
GROUP BY COALESCE(NULLIF(o.referring_site, ''), 'Direct / Unknown')
HAVING COUNT(DISTINCT o.id) >= 5
ORDER BY avg_order_value DESC

Schema-aware SQL generated from plain English15 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

Uses referring_site from shopify_orders to group orders by traffic source
COALESCE with NULLIF handles both NULL and empty string cases, bucketing them as Direct / Unknown
Revenue per customer (total revenue / unique customers) shows which sources bring high-LTV buyers
HAVING >= 5 orders filters out one-off referral noise
Sorted by AOV descending to surface your highest-value traffic sources first

What it returns

Result description

Traffic sources ranked by average order value, with order count, unique customers, total revenue, and revenue per customer.

Why it matters

The business impact

A source driving $80 AOV is worth 2x more per conversion than one driving $40 AOV. When you know which sources drive higher-value orders, you can shift ad spend toward quality over quantity — improving ROAS without increasing total spend.

FAQ

Common questions

How do I generate a "Shopify Average Order Value by Acquisition Source" query automatically?

In Taptic Data, type "Show me average order value by traffic source for 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 average order value by traffic source for the last 9..." 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 →