New vs returning revenue in PrestaShop
Split PrestaShop order revenue between first-time and returning buyers.
PrestaShop · PostgreSQL
WITH first_order AS (
SELECT id_customer, MIN(date_add) AS first_date
FROM ps_orders WHERE valid = 1 GROUP BY id_customer
)
SELECT CASE WHEN o.date_add > f.first_date THEN 'Returning' ELSE 'New' END AS segment,
COUNT(*) AS orders,
ROUND(SUM(o.total_paid_tax_incl), 2) AS revenue
FROM ps_orders o
JOIN first_order f ON f.id_customer = o.id_customer
WHERE o.valid = 1
GROUP BY 1
ORDER BY revenue DESC;How it works
Compares each order's date to the customer's first order to label it New or Returning, then sums revenue per segment — shows how much of your sales retention drives.
Run this on your own database
BeQuery mirrors your PrestaShop MySQL database to an isolated PostgreSQL copy, so you can run queries like this one without ever touching production.
Start free No credit card