E-commerce Database Analytics: Track Revenue, Churn, and Inventory Without SQL

Running an e-commerce business means sitting on top of a goldmine of data. Every order, every cart abandonment, every product view, every refund — it's all recorded in your database. The problem is that most of the people who need those insights (founders, ops managers, marketers) can't easily get to them without asking an engineer to write a custom query.

This guide covers the most important e-commerce metrics your database already tracks, how to pull them without writing SQL, and how to set up automated monitoring so you catch problems before they become crises.

Why E-commerce Analytics Needs More Than Shopify's Dashboard

If you're running on Shopify, WooCommerce, or a custom stack, you already have some built-in analytics. They're fine for top-line numbers. But they fall short as soon as you need anything specific:

These questions require your actual database — not a dashboard someone built for the average store. Your data is unique to your business. Your questions should be too.

The Core E-commerce Tables You Should Know

You don't need to be a database administrator to use this guide. But a basic mental model helps. Most e-commerce databases have some version of these tables:

Here's a simplified schema for reference:

-- Typical orders table
orders (
  id, customer_id, status, total_amount,
  created_at, updated_at, shipping_country
)

-- Typical order_items table
order_items (
  id, order_id, product_id, quantity, unit_price, discount_applied
)

-- Typical products table
products (
  id, name, sku, category, price, inventory_count, reorder_threshold
)

When you ask a natural language tool like AI for Database a question, it reads this schema and writes the appropriate query for you. You never need to remember table names or columns.

Revenue Analytics: The Questions That Matter

Monthly Recurring Revenue and Growth

For subscription e-commerce (subscription boxes, replenishment services):

SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total_amount) AS revenue,
  COUNT(DISTINCT customer_id) AS paying_customers
FROM orders
WHERE status = 'completed'
GROUP BY 1
ORDER BY 1;

With a natural language tool: "Show me monthly revenue and customer count for the past 12 months."

Revenue by Product Category

Understanding which categories drive the most revenue (and margin) helps you make smarter merchandising decisions:

SELECT
  p.category,
  COUNT(oi.id) AS units_sold,
  SUM(oi.quantity * oi.unit_price) AS gross_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND o.status = 'completed'
GROUP BY p.category
ORDER BY gross_revenue DESC;

With a natural language tool: "What's revenue by product category for the past 30 days?"

Average Order Value Trends

If your AOV is declining, you need to know early — not after a full quarter of margin erosion:

SELECT
  DATE_TRUNC('week', created_at) AS week,
  ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
WHERE status = 'completed'
  AND created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Customer Retention and Churn Analysis

Acquisition is expensive. Retention is where most e-commerce profitability lives. These queries help you see who's staying and who's slipping away.

Repeat Purchase Rate

What percentage of customers who bought in a given month came back for a second order?

SELECT
  first_order_month,
  COUNT(DISTINCT customer_id) AS first_time_buyers,
  COUNT(DISTINCT CASE WHEN subsequent_orders > 0 THEN customer_id END) AS repeat_buyers,
  ROUND(
    100.0 * COUNT(DISTINCT CASE WHEN subsequent_orders > 0 THEN customer_id END)
    / COUNT(DISTINCT customer_id), 1
  ) AS repeat_rate_pct
FROM (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(created_at)) AS first_order_month,
    COUNT(*) - 1 AS subsequent_orders
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
) sub
GROUP BY first_order_month
ORDER BY first_order_month;

With a natural language tool: "What's our repeat purchase rate by month?"

Lapsed Customers

Customers who bought before but haven't ordered recently are often easier to win back than acquiring new ones:

SELECT
  c.id,
  c.email,
  MAX(o.created_at) AS last_order_date,
  COUNT(o.id) AS total_orders,
  SUM(o.total_amount) AS lifetime_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.id, c.email
HAVING MAX(o.created_at) < CURRENT_DATE - INTERVAL '90 days'
ORDER BY lifetime_value DESC;

With a natural language tool: "Which customers spent the most but haven't ordered in 90 days?"

This list is your win-back campaign audience. Export it, load it into your email tool, and send a targeted offer.

Inventory Management: Catch Problems Before They Happen

Stockouts are expensive — not just the lost sale, but the customer who goes elsewhere and doesn't come back. Overstock ties up cash. Getting inventory right requires watching your sell-through rates continuously.

Products Running Low

SELECT
  id,
  name,
  sku,
  inventory_count,
  reorder_threshold
FROM products
WHERE inventory_count <= reorder_threshold
  AND inventory_count > 0
ORDER BY inventory_count ASC;

With a natural language tool: "Which products are at or below their reorder threshold?"

Velocity-Based Stock Prediction

How many days of stock do you have left at current sell rate?

SELECT
  p.id,
  p.name,
  p.sku,
  p.inventory_count,
  ROUND(AVG(daily_units.units_sold), 1) AS avg_daily_units,
  ROUND(
    p.inventory_count / NULLIF(AVG(daily_units.units_sold), 0), 0
  ) AS days_of_stock_remaining
FROM products p
JOIN (
  SELECT
    oi.product_id,
    DATE(o.created_at) AS order_date,
    SUM(oi.quantity) AS units_sold
  FROM order_items oi
  JOIN orders o ON oi.order_id = o.id
  WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
    AND o.status = 'completed'
  GROUP BY oi.product_id, DATE(o.created_at)
) daily_units ON p.id = daily_units.product_id
GROUP BY p.id, p.name, p.sku, p.inventory_count
HAVING ROUND(p.inventory_count / NULLIF(AVG(daily_units.units_sold), 0), 0) < 14
ORDER BY days_of_stock_remaining ASC;

With a natural language tool: "Which products will run out in the next 14 days at current sales velocity?"

Setting Up Automated Alerts for E-commerce Metrics

Checking dashboards manually is better than checking nothing, but it still puts the burden on you to remember. Automated alerts flip this — the system tells you when something needs attention.

Useful e-commerce alerts to set up:

AI for Database action workflows support exactly this pattern. You define the condition in plain English ("when any product's inventory count falls below its reorder_threshold"), set the action (send an email, post to Slack, call a webhook), and the system watches your database and fires automatically. No stored procedures, no cron jobs.

Building an E-commerce KPI Dashboard Without SQL

If you want a persistent view of your key metrics — something your whole team can check without asking anyone — a dashboard built from live queries is the answer.

A useful e-commerce command centre might include:

In AI for Database, you build each panel by typing a question. The dashboard refreshes automatically on whatever schedule you set — hourly for operations, daily for executive summaries.

Start with One Question

You don't need to build a perfect analytics system on day one. Pick the one question that currently takes the most time to answer — whether it's weekly revenue, low inventory alerts, or lapsed customer lists — and replicate it with a live query.

The data is already in your database. The hard part was always getting to it. Try AI for Database free and see what you can ask.