Shopify's analytics dashboard is good. It shows you total sales, sessions, conversion rate, and top products. For most store owners, it's enough to keep the lights on.
But it's not enough to grow.
The insights that drive real growth require deeper analysis. Here are 10 reports that Shopify's dashboard will never give you, and the SQL queries to build them yourself.
1. True Customer Lifetime Value Distribution
Why the dashboard fails: Shopify shows average order value and returning customer rate. It doesn't show you the distribution of lifetime value across all customers.
SELECT
CASE
WHEN lifetime_value < 50 THEN '$0-50'
WHEN lifetime_value BETWEEN 50 AND 100 THEN '$50-100'
WHEN lifetime_value BETWEEN 100 AND 250 THEN '$100-250'
WHEN lifetime_value BETWEEN 250 AND 500 THEN '$250-500'
WHEN lifetime_value BETWEEN 500 AND 1000 THEN '$500-1000'
ELSE '$1000+'
END as ltv_bucket,
COUNT(*) as customers,
ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as pct_of_total,
SUM(lifetime_value) as bucket_revenue
FROM (
SELECT customer_id, SUM(total_price) as lifetime_value
FROM orders
WHERE financial_status = 'paid' AND cancelled_at IS NULL
GROUP BY customer_id
) clv
GROUP BY ltv_bucket
ORDER BY MIN(lifetime_value);
Insight: You'll likely discover that 20% of customers generate 80% of revenue. Target your marketing accordingly.
2. Product Affinity Map (Bought Together)
Why the dashboard fails: Shopify shows top products individually. It doesn't show which products are frequently purchased together.
SELECT
a.title as product_a,
b.title as product_b,
COUNT(DISTINCT a.order_id) as co_purchases,
ROUND(COUNT(DISTINCT a.order_id) * 100.0 /
(SELECT COUNT(DISTINCT order_id) FROM order_line_items WHERE product_id = a.product_id), 1) as pct_of_product_a_orders
FROM order_line_items a
JOIN order_line_items b ON a.order_id = b.order_id AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id, a.title, b.title
HAVING co_purchases >= 5
ORDER BY co_purchases DESC
LIMIT 20;
Action: Create bundles, cross-sell recommendations, or "Complete the Look" sections.
3. Time Between First and Second Purchase
Why the dashboard fails: You can see returning customer rate but not when they return.
SELECT
CASE
WHEN days_to_second <= 7 THEN 'Within 1 week'
WHEN days_to_second <= 14 THEN '1-2 weeks'
WHEN days_to_second <= 30 THEN '2-4 weeks'
WHEN days_to_second <= 60 THEN '1-2 months'
WHEN days_to_second <= 90 THEN '2-3 months'
ELSE '3+ months'
END as return_window,
COUNT(*) as customers,
ROUND(AVG(second_order_value), 2) as avg_second_order
FROM (
SELECT
customer_id,
MIN(created_at) as first_order_date,
(SELECT MIN(created_at) FROM orders o2
WHERE o2.customer_id = o.customer_id
AND o2.created_at > MIN(o.created_at)
AND o2.financial_status = 'paid') as second_order_date,
(SELECT total_price FROM orders o3
WHERE o3.customer_id = o.customer_id
AND o3.created_at > MIN(o.created_at)
AND o3.financial_status = 'paid'
ORDER BY o3.created_at LIMIT 1) as second_order_value,
DATEDIFF(
(SELECT MIN(created_at) FROM orders o2
WHERE o2.customer_id = o.customer_id
AND o2.created_at > MIN(o.created_at)),
MIN(created_at)
) as days_to_second
FROM orders o
WHERE financial_status = 'paid' AND cancelled_at IS NULL
GROUP BY customer_id
HAVING second_order_date IS NOT NULL
) repeat_data
GROUP BY return_window
ORDER BY MIN(days_to_second);
Action: Schedule follow-up emails at the peak return window to maximize repeat purchases.
4. Real Profit Margin by Product
Why the dashboard fails: Shopify shows revenue but not profit. You need to factor in cost, discounts, refunds, and shipping.
SELECT
p.title,
SUM(li.quantity) as units_sold,
SUM(li.price * li.quantity) as gross_revenue,
SUM(li.quantity * v.cost) as total_cost,
SUM(COALESCE(rli.subtotal, 0)) as refunds,
SUM(li.total_discount) as discounts,
SUM(li.price * li.quantity) - SUM(li.quantity * v.cost) -
SUM(COALESCE(rli.subtotal, 0)) - SUM(li.total_discount) as net_profit,
ROUND((SUM(li.price * li.quantity) - SUM(li.quantity * v.cost) -
SUM(COALESCE(rli.subtotal, 0)) - SUM(li.total_discount)) * 100.0 /
NULLIF(SUM(li.price * li.quantity), 0), 1) as margin_pct
FROM order_line_items li
JOIN products p ON li.product_id = p.id
JOIN variants v ON li.variant_id = v.id
JOIN orders o ON li.order_id = o.id
LEFT JOIN refund_line_items rli ON li.id = rli.line_item_id
WHERE o.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY p.id
ORDER BY net_profit DESC;
5. Geographic Revenue Heat Map
Why the dashboard fails: Basic country-level data without city or region granularity.
SELECT
shipping_country as country,
shipping_province as region,
shipping_city as city,
COUNT(*) as orders,
SUM(total_price) as revenue,
AVG(total_price) as avg_order,
COUNT(DISTINCT customer_id) as unique_customers
FROM orders
WHERE financial_status = 'paid' AND cancelled_at IS NULL
AND created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY shipping_country, shipping_province, shipping_city
ORDER BY revenue DESC
LIMIT 30;
6. Discount Dependency Analysis
Why the dashboard fails: Shows discount usage but not whether customers only buy with discounts.
SELECT
customer_segment,
COUNT(*) as customers,
AVG(total_orders) as avg_orders,
AVG(total_spent) as avg_ltv,
AVG(discount_rate) as avg_discount_pct
FROM (
SELECT
customer_id,
COUNT(*) as total_orders,
SUM(total_price) as total_spent,
SUM(CASE WHEN total_discounts > 0 THEN 1 ELSE 0 END) as discounted_orders,
ROUND(SUM(CASE WHEN total_discounts > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 0) as discount_rate,
CASE
WHEN SUM(CASE WHEN total_discounts > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) = 0 THEN 'Full price only'
WHEN SUM(CASE WHEN total_discounts > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) < 50 THEN 'Occasional discount'
WHEN SUM(CASE WHEN total_discounts > 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) < 100 THEN 'Frequent discount'
ELSE 'Discount dependent'
END as customer_segment
FROM orders
WHERE financial_status = 'paid' AND cancelled_at IS NULL
GROUP BY customer_id
HAVING COUNT(*) >= 2
) segments
GROUP BY customer_segment
ORDER BY avg_ltv DESC;
Insight: If most repeat buyers are "discount dependent," your pricing strategy needs adjustment.
7. Hour-of-Day Purchase Pattern
SELECT
HOUR(created_at) as hour_of_day,
DAYNAME(created_at) as day_of_week,
COUNT(*) as orders,
SUM(total_price) as revenue,
AVG(total_price) as avg_order
FROM orders
WHERE financial_status = 'paid' AND cancelled_at IS NULL
AND created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY HOUR(created_at), DAYNAME(created_at)
ORDER BY orders DESC
LIMIT 20;
Action: Schedule email campaigns and ad spend during peak purchasing hours.
8. First Purchase Product Analysis
Why it matters: Which products attract new customers vs which retain existing ones?
SELECT
p.title as first_product_purchased,
COUNT(DISTINCT first_orders.customer_id) as new_customers,
COUNT(DISTINCT CASE WHEN repeat_order.id IS NOT NULL THEN first_orders.customer_id END) as became_repeat,
ROUND(COUNT(DISTINCT CASE WHEN repeat_order.id IS NOT NULL THEN first_orders.customer_id END) * 100.0 /
COUNT(DISTINCT first_orders.customer_id), 1) as repeat_rate
FROM (
SELECT customer_id, MIN(id) as first_order_id
FROM orders WHERE financial_status = 'paid' AND cancelled_at IS NULL
GROUP BY customer_id
) first_orders
JOIN order_line_items li ON first_orders.first_order_id = li.order_id
JOIN products p ON li.product_id = p.id
LEFT JOIN orders repeat_order ON first_orders.customer_id = repeat_order.customer_id
AND repeat_order.id != first_orders.first_order_id
AND repeat_order.financial_status = 'paid'
GROUP BY p.id, p.title
HAVING new_customers >= 10
ORDER BY repeat_rate DESC;
Action: Promote products with the highest repeat purchase rate as your acquisition products.
9. Cart Size Distribution
SELECT
items_per_order,
COUNT(*) as orders,
AVG(order_total) as avg_order_value,
SUM(order_total) as total_revenue
FROM (
SELECT
o.id,
COUNT(li.id) as items_per_order,
o.total_price as order_total
FROM orders o
JOIN order_line_items li ON o.id = li.order_id
WHERE o.financial_status = 'paid' AND o.cancelled_at IS NULL
AND o.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY o.id
) cart_sizes
GROUP BY items_per_order
ORDER BY items_per_order;
Action: If most orders are single-item, add bundle offers or free shipping thresholds.
10. Refund Reason Analysis
SELECT
r.reason as refund_reason,
COUNT(*) as refund_count,
SUM(r.amount) as total_refunded,
AVG(r.amount) as avg_refund,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM refunds), 1) as pct_of_all_refunds
FROM refunds r
WHERE r.created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY r.reason
ORDER BY refund_count DESC;
Get These Reports Without Learning SQL
Each query above contains JOINs, subqueries, window functions, and conditional aggregation. Learning all of this takes months.
Or you can use AI2SQL. Type what you need in plain English:
"Show me customer lifetime value distribution in buckets" → instant query
"Which products do customers buy together most often?" → instant query
"Real profit margin by product after discounts and refunds" → instant query
Your Shopify data has the answers. AI2SQL helps you ask the right questions. Try it free at ai2sql.io.
