Skip to content
Datablare
Claude Code E-commerce Customer analytics

Customer retention by first-order cohort

2,856 new customers this year, 76.1% of revenue from returning customers, and a median 139 days to a second order.

Data
Synthetic e-commerce demo data (PostgreSQL)
Asked in
Claude Code
Updated
Try this on your data

1 · The brief

What Claude Code was asked

How well do we keep customers? Show me how many new customers we won this year, what share of revenue comes from returning customers, how quickly people place a second order, how each quarter’s new customers come back, and new versus returning revenue by month.

Given exactly like this. Claude Code asked Datablare which tables it could read, wrote the SQL itself, ran each query through Datablare and built the dashboard from what came back.

2 · The dashboard

The dashboard Claude Code built

Customer retention by first-order cohort dashboard: New and returning revenue per month, Back for a second order, by first-order quarter, New customers per quarter

Built from 4 read-only queries. Every figure on the dashboard was read from the database through Datablare; nothing was estimated or typed in.

Headline numbers (January 2025 – October 2026):

  • Buying customers: 8,636 (With a completed order)
  • New customers: 2,856 (First order in 2026)
  • Returning revenue: 76.1% (Share of 2026 revenue)
  • Days to second order: 139 (Median, first to second order)

What stands out:

  • New and returning revenue per month: Repeat orders now bring in most of the revenue
  • Back for a second order, by first-order quarter: 39% of 2025-Q1 customers came back within 180 days
  • New customers per quarter: 6,814 first-time buyers from 2025-Q1 to 2026-Q1

The data is a synthetic e-commerce dataset made for this example, so the numbers describe no real company. The queries, the dashboard and the audit entries are real: Claude reached the database only through Datablare, read-only, and every query is on record with the question it served.

3 · What Datablare ran

4 read-only queries, nothing changed

Each query below is copied from the audit log of this run. Datablare checked that it only reads, ran it read-only, and gave only the rows to Claude Code; it never stores them.

  1. Query 1

    Buying customers, new customers in 2026 and the returning share of 2026 revenue

    1 row · 106 ms

    WITH o AS (
      SELECT customer_id, order_date, total,
             row_number() OVER (PARTITION BY customer_id ORDER BY order_date, id) AS n
      FROM ecommerce.orders WHERE status = 'completed'
    )
    SELECT count(DISTINCT customer_id) AS buying_customers,
           count(*) FILTER (WHERE n = 1 AND order_date >= DATE '2026-01-01') AS new_customers_2026,
           round(sum(total) FILTER (WHERE n > 1 AND order_date >= DATE '2026-01-01') / sum(total) FILTER (WHERE order_date >= DATE '2026-01-01'), 4) AS returning_revenue_share_2026
    FROM o
  2. Query 2

    Median days between a customer’s first and second completed order

    1 row · 90 ms

    WITH o AS (
      SELECT customer_id, order_date, total,
             row_number() OVER (PARTITION BY customer_id ORDER BY order_date, id) AS n
      FROM ecommerce.orders WHERE status = 'completed'
    )
    SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY second - first) AS median_days_to_second_order, count(*) AS customers_with_second_order
    FROM (
      SELECT customer_id, min(order_date) FILTER (WHERE n = 1) AS first, min(order_date) FILTER (WHERE n = 2) AS second
      FROM o GROUP BY 1
    ) f
    WHERE second IS NOT NULL
  3. Query 3

    For each quarter’s new customers, the share who ordered again within 90 and 180 days

    5 rows · 95 ms

    WITH o AS (
      SELECT customer_id, order_date, total,
             row_number() OVER (PARTITION BY customer_id ORDER BY order_date, id) AS n
      FROM ecommerce.orders WHERE status = 'completed'
    )
    , f AS (
      SELECT customer_id, min(order_date) FILTER (WHERE n = 1) AS first, min(order_date) FILTER (WHERE n = 2) AS second
      FROM o GROUP BY 1
    )
    SELECT to_char(date_trunc('quarter', first), 'YYYY-"Q"Q') AS cohort, count(*) AS new_customers,
           round(avg(CASE WHEN second <= first + 90 THEN 1.0 ELSE 0 END), 4) AS back_within_90d,
           round(avg(CASE WHEN second <= first + 180 THEN 1.0 ELSE 0 END), 4) AS back_within_180d
    FROM f
    WHERE first < DATE '2026-04-01'
    GROUP BY 1
    ORDER BY 1
  4. Query 4

    Revenue from first orders and from repeat orders, by month since January 2025

    22 rows · 108 ms

    WITH o AS (
      SELECT customer_id, order_date, total,
             row_number() OVER (PARTITION BY customer_id ORDER BY order_date, id) AS n
      FROM ecommerce.orders WHERE status = 'completed'
    )
    SELECT to_char(date_trunc('month', order_date), 'YYYY-MM') AS month,
           round(sum(total) FILTER (WHERE n = 1), 0) AS new_customer_revenue,
           round(sum(total) FILTER (WHERE n > 1), 0) AS returning_revenue
    FROM o
    GROUP BY 1
    ORDER BY 1
Tables read ecommerce.orders

4 · The audit entry

Who asked what, on record

Every query is recorded with the person who asked, the question in their words and why the agent ran it. Admins see this in the audit log; nothing reaches the database without it.

How the audit log works

query_data_source

via Claude Code

Allowed
Who
The person signed in (hidden on this public page)
Question
How well do we keep customers? Show me how many new customers we won this year, what share of revenue comes from returning customers, how quickly people place a second order, how each quarter’s new customers come back, and new versus returning revenue by month.
Intent
Buying customers, new customers in 2026 and the returning share of 2026 revenue
Result
4 queries, 29 rows, read-only

More use cases

All examples
  • E-commerce sales and retention dashboard dashboard: Monthly revenue, Revenue by category, 2026, Lifetime revenue per customer, by channel, Orders per month
    Claude Code E-commerce Executive overview

    E-commerce sales and retention dashboard

    ₹5.21M revenue from 11,897 orders this year, a 57.9% repeat-customer rate, and which channels bring the customers who spend most.

    Updated Open

  • Product performance and returns dashboard: Top ten products by revenue, Return rate by category, Return and cancellation rates per month
    Claude Code E-commerce Product & marketing

    Product performance and returns

    41,246 units sold this year with a 9.6% return rate; Footwear and Apparel are returned most, and Classic Headphones leads revenue.

    Updated Open

  • Customer activity by city and segment dashboard: Active customers per month, Approved value by city, 2026, Spend per active customer per month, Customers by city and risk level
    Claude Code Banking Customer analytics

    Customer activity by city and segment

    2,277 of 3,000 customers active this year, spend per active customer each month, value by city, and the risk mix in each city.

    Updated Open

Build this on your own data.

Connect your database, choose the tables Claude may read, and give it the same brief. Claude is connected in about two minutes.

30 minutes with the founder. Or WhatsApp / [email protected]