Home›Query Library›Shopify

How is your Shopify revenue trending month over month?

The first question every Shopify merchant asks — answered cleanly from your orders table with correct paid-order filtering and period-over-period comparison.

📊 Shopify👥 Shopify merchants🔤 Plain-English → SQL

The question

Show me monthly Shopify revenue for the last 12 months with order count and average order value

↓

The SQL Taptic generates

Shopify Revenue by Month — generated by Taptic Data AI

SELECT
  TO_CHAR(DATE_TRUNC('month', o.created_at), 'YYYY-MM')  AS order_month,
  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_price)::numeric
    / NULLIF(COUNT(DISTINCT o.id), 0), 2)                AS avg_order_value,
  ROUND(SUM(o.total_price - o.total_refunded)::numeric, 2) AS net_revenue
FROM shopify_orders o
WHERE o.financial_status IN ('paid', 'partially_refunded')
  AND o.created_at >= NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY 1 ASC

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

Filters to paid and partially_refunded orders — the orders that actually contributed revenue
Includes both gross revenue (pre-refund) and net revenue (total_price minus total_refunded) in the same row
Counts unique customers per month alongside orders to distinguish AOV changes from customer mix changes
Truncates to month and formats as YYYY-MM for clean chart display
NULLIF prevents division by zero in the AOV calculation for any month with zero orders

What it returns

Result description

Monthly table with order count, unique customers, gross revenue, average order value, and net revenue after refunds — 12 months in chronological order.

Why it matters

The business impact

Monthly revenue is the heartbeat metric for any Shopify store. Seeing gross vs net side-by-side lets you spot whether a revenue dip is real or just a refund spike. Tracking unique customers vs orders reveals whether you're growing through acquisition, repeat purchases, or AOV improvements.

Generate this automatically

Skip the SQL. Ask the question.

In Taptic Data, you type "Show me monthly Shopify revenue for the last 12 months with ..." 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.