Home›Query Library›Amazon

How is your Amazon revenue trending month by month?

The most fundamental Amazon business question — answered with clean data from your order_items table, properly excluding cancellations and NULL-priced items.

📊 Amazon Seller Central👥 Amazon sellers🔤 Plain-English → SQL

The question

Show me total Amazon revenue by month for the last 12 months

↓

The SQL Taptic generates

Amazon Revenue by Month — generated by Taptic Data AI

SELECT
  TO_CHAR(DATE_TRUNC('month', o.purchase_date::timestamptz), 'YYYY-MM') AS order_month,
  COUNT(DISTINCT o.amazon_order_id)                                       AS total_orders,
  SUM(i.quantity)                                                         AS units_sold,
  ROUND(SUM(i.item_price * i.quantity), 2)                                AS gross_revenue,
  ROUND(SUM(i.item_price * i.quantity)
    / NULLIF(COUNT(DISTINCT o.amazon_order_id), 0), 2)                  AS avg_order_value
FROM amazon_orders o
JOIN amazon_order_items i
  ON i.amazon_order_id = o.amazon_order_id
WHERE o.order_status NOT IN ('Canceled', 'Cancelled')
  AND i.item_price IS NOT NULL
  AND o.purchase_date::timestamptz >= NOW() - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', o.purchase_date::timestamptz)
ORDER BY 1 ASC

Schema-aware SQL generated from plain English15 lines

This query was generated by Taptic Data from plain English against a real Amazon Seller Central 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

Casts purchase_date (stored as text in Amazon's SP-API) to timestamptz for proper date math
Truncates to month and formats as YYYY-MM for clean chart axis labels
Filters out Canceled/Cancelled orders (both spellings) and NULL item_price rows
Calculates gross revenue as item_price * quantity per line item, then sums per month
Includes average order value using NULLIF to handle months with zero orders gracefully

What it returns

Result description

A month-by-month table of orders, units sold, gross revenue, and average order value — covering the last 12 months in chronological order, ready to chart as a line or bar.

Why it matters

The business impact

Monthly revenue trend is the baseline metric for any Amazon business. Seeing it in clean SQL — not Seller Central's limited date filters — lets you spot seasonal patterns, growth inflection points, and the impact of catalog or pricing changes.

Generate this automatically

Skip the SQL. Ask the question.

In Taptic Data, you type "Show me total Amazon revenue by month for the last 12 months..." and this SQL runs automatically against your real Amazon Seller Central data.

Try Taptic Free — $29.99/mo

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