Shopify Customer LTV and Repeat Purchase Rate in SQL

Quick answer

Average LTV, orders per customer and repeat purchase rate, on Shopify data synced by Fivetran into Postgres.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with per_customer as (
  select customer_id, count(*) as orders, sum(total_price) as revenue
  from shopify."order"
  where not test and cancelled_at is null and customer_id is not null
  group by customer_id
)
select
  count(*) as customers,
  round(avg(revenue), 2) as avg_ltv,
  round(avg(orders), 2) as avg_orders,
  round(100.0 * count(*) filter (where orders > 1) / nullif(count(*), 0), 1) as repeat_rate_pct
from per_customer;

Schema assumptions

Source	Tables used	Notes
Shopify via Fivetran (classic schema)	order (id, customer_id, created_at, cancelled_at, test, total_price, source_name), order_line (id, order_id, product_id), product (id, title), refund (id, order_id), order_line_refund (refund_id, subtotal)	Quote "order". Fivetran’s GraphQL-based schema uses total_price_set_shop_amount instead of total_price.
Shopify via Airbyte	orders (customer JSON, created_at, current_total_price, test, cancelled_at)	current_total_price already reflects refunds and edits.
demo_shop (AI2SQL)	customers (id, signup_date), orders (id, customer_id, order_date, status, total)	Revenue = completed and shipped orders.

Copy-ready SQL

All PostgreSQL on Fivetran’s Shopify schema unless the first line says otherwise. Example outputs use made-up data.

1. 90-day LTV and repeat rate by first-order cohort

Compares cohorts at the same age. Change: 90 days (in all three places) to 30, 180 or 365. Customers whose first order is less than 90 days old are left out, so every customer counted has had the full window.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with orders as (
  select customer_id, created_at, total_price,
         min(created_at) over (partition by customer_id) as first_order_at
  from shopify."order"
  where not test and cancelled_at is null and customer_id is not null
),
per_customer as (
  select
    customer_id,
    first_order_at,
    sum(total_price) filter (where created_at < first_order_at + interval '90 days') as revenue_90d,
    count(*) filter (where created_at < first_order_at + interval '90 days') as orders_90d
  from orders
  group by customer_id, first_order_at
)
select
  date_trunc('month', first_order_at)::date as cohort,
  count(*) as customers,
  round(avg(revenue_90d), 2) as ltv_90d,
  round(100.0 * count(*) filter (where orders_90d > 1) / count(*), 1) as repeat_90d_pct
from per_customer
where first_order_at <= now() - interval '90 days'
group by 1
order by 1 desc;

cohort	customers	ltv_90d	repeat_90d_pct
2026-06-01	498	81.20	17.3
2026-05-01	455	84.75	18.9

2. Days between first and second order

How long to wait before a reorder email. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with ranked as (
  select customer_id, created_at,
         row_number() over (partition by customer_id order by created_at) as n
  from shopify."order"
  where not test and cancelled_at is null and customer_id is not null
),
gaps as (
  select extract(epoch from (second.created_at - first.created_at)) / 86400 as days
  from ranked first
  join ranked second on second.customer_id = first.customer_id and second.n = 2
  where first.n = 1
)
select
  count(*) as repeat_customers,
  round(percentile_cont(0.5) within group (order by days)::numeric, 1) as median_days,
  round(percentile_cont(0.9) within group (order by days)::numeric, 1) as p90_days
from gaps;

repeat_customers	median_days	p90_days
1408	34.5	118.0

3. LTV by first-order channel

Which channel brings customers who come back. Change: source_name to referring_site for referrers.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with orders as (
  select customer_id, total_price, source_name,
         first_value(source_name) over (partition by customer_id order by created_at) as first_channel
  from shopify."order"
  where not test and cancelled_at is null and customer_id is not null
)
select
  first_channel,
  count(distinct customer_id) as customers,
  round(sum(total_price) / count(distinct customer_id), 2) as ltv
from orders
group by first_channel
order by ltv desc;

first_channel	customers	ltv
web	5210	96.40
pos	610	71.10

4. LTV by first product bought

Which product is the best entry point. Change: the minimum of 30 customers.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with first_orders as (
  select distinct on (customer_id) id as order_id, customer_id
  from shopify."order"
  where not test and cancelled_at is null and customer_id is not null
  order by customer_id, created_at
),
first_product as (
  select distinct on (fo.customer_id) fo.customer_id, p.title as product
  from first_orders fo
  join shopify.order_line ol on ol.order_id = fo.order_id
  join shopify.product p on p.id = ol.product_id
  order by fo.customer_id, ol.price * ol.quantity desc
),
ltv as (
  select customer_id, sum(total_price) as revenue
  from shopify."order"
  where not test and cancelled_at is null and customer_id is not null
  group by customer_id
)
select fp.product, count(*) as customers, round(avg(l.revenue), 2) as avg_ltv
from first_product fp
join ltv l on l.customer_id = fp.customer_id
group by fp.product
having count(*) >= 30
order by avg_ltv desc;

product	customers	avg_ltv
Starter Kit	340	142.80

5. LTV net of refunds

Lifetime revenue minus what was refunded. Change: nothing.

-- PostgreSQL (Fivetran Shopify schema "shopify")
with refunds as (
  select r.order_id, sum(olr.subtotal) as refunded
  from shopify.refund r
  join shopify.order_line_refund olr on olr.refund_id = r.id
  group by r.order_id
),
per_customer as (
  select o.customer_id, sum(o.total_price - coalesce(rf.refunded, 0)) as net_revenue
  from shopify."order" o
  left join refunds rf on rf.order_id = o.id
  where not o.test and o.cancelled_at is null and o.customer_id is not null
  group by o.customer_id
)
select count(*) as customers, round(avg(net_revenue), 2) as avg_net_ltv
from per_customer;

customers	avg_net_ltv
6120	88.15

6. 90-day LTV by cohort in Snowflake

Query 1 for Fivetran’s Shopify schema in Snowflake. Change: shopify to your database and schema.

-- Snowflake (Fivetran Shopify schema "shopify")
with orders as (
  select customer_id, created_at, total_price,
         min(created_at) over (partition by customer_id) as first_order_at
  from shopify."ORDER"
  where not test and cancelled_at is null and customer_id is not null
),
eligible as (
  select * from orders
  where first_order_at <= dateadd(day, -90, current_timestamp())
)
select
  date_trunc('month', first_order_at) as cohort,
  count(distinct customer_id) as customers,
  round(sum(iff(created_at < dateadd(day, 90, first_order_at), total_price, 0)) / count(distinct customer_id), 2) as ltv_90d
from eligible
group by 1
order by 1 desc;

7. LTV from Airbyte tables (Postgres)

Airbyte stores the customer as a JSON object on each order. current_total_price already accounts for refunds. Change: the airbyte_shopify schema name.

-- PostgreSQL (Airbyte Shopify schema "airbyte_shopify")
with per_customer as (
  select customer ->> 'id' as customer_id, count(*) as orders, sum(current_total_price) as revenue
  from airbyte_shopify.orders
  where not test and cancelled_at is null and customer is not null
  group by 1
)
select
  count(*) as customers,
  round(avg(revenue), 2) as avg_ltv,
  round(100.0 * count(*) filter (where orders > 1) / nullif(count(*), 0), 1) as repeat_rate_pct
from per_customer;

with per_customer as (
  select
    c.id,
    date_trunc('month', c.signup_date)::date as cohort,
    count(o.id) as orders,
    coalesce(sum(o.total), 0) as revenue
  from customers c
  left join orders o
    on o.customer_id = c.id
    and o.status in ('completed', 'shipped')
  group by c.id, cohort
)
select
  cohort,
  count(*) as customers,
  round(avg(revenue), 2) as avg_ltv,
  round(100.0 * count(*) filter (where orders > 1) / count(*), 1) as repeat_rate_pct
from per_customer
group by cohort
order by cohort desc