Skip to content
Tutorials Center
My learning

Topics

Lesson 115 of 125 ยท Projects

Project: Bookstore Customer Analytics

Analyze an online bookshop with SQL. Segment customers with RFM, measure repeat and lapsed buyers, find books bought together, compare marketing ROI and flag low stock.

An online bookshop has two years of orders and a marketing budget, and the owner has five questions. Who are my best customers? How many of them come back? Which books sell together? Is my advertising paying for itself? And what do I need to reorder before it sells out?

Those questions are the daily bread of a data analyst, and SQL answers all of them. In this project you'll use the bookshop sample database (70 customers, 24 books, 239 orders and 397 order lines from March 2024 to December 2025, plus the marketing spend per channel and quarter) and build six small reports. Each one is a pattern you can reuse on any shop, app or subscription business.

You should be comfortable with joins, CTEs, CASE, GROUP BY and self joins. If you want the funnel and cohort side of customer analysis, see Project: E-commerce Funnel Analytics; this project focuses on segmentation, products and marketing.

The data and the ground rules

The tables are customers (with the channel they came from), books (price, cost, stock and a reorder level), orders (with a status of completed or refunded), order_items and marketing_spend (money spent per channel and quarter). Agree on definitions first, because every number depends on them:

  • Today is 2025-12-31, the last day of the data. We never use the current date, so the results stay the same.
  • A kept order is a completed one. Refunded orders are ignored everywhere.
  • Revenue is quantity * unit_price over the lines of kept orders. unit_price is what the customer paid, so it already contains discounts.
SQL A first look at the data ยท database: bookshop

Press Run (or Ctrl + Enter) to see the result here.

Of the 239 orders, 12 were refunded, so 227 count. Customers arrived through five channels: organic, search, social, email and referral. The last four cost money, which matters in Stage 5.

Stage 1: revenue and average order value

Most reports in this project start from one building block: a CTE with one row per kept order and its total. Aggregating from there avoids the classic mistake of averaging order lines instead of orders. Here it feeds a quarterly view of orders, buyers, revenue and average order value (AOV), the revenue per order.

SQL Quarterly revenue and average order value ยท database: bookshop

Press Run (or Ctrl + Enter) to see the result here.

The quarter expression turns month 1 to 3 into 1, 4 to 6 into 2, and so on, using integer division. The shop grew from 13 orders in the second quarter of 2024 to between 39 and 45 per quarter through 2025, and revenue climbed from 539.26 to between 1,436 and 1,747 a quarter. The AOV hardly moves: it stays between 31 and 41 in every quarter. That is a useful finding on its own: growth came from more orders, not from bigger baskets.

Stage 2: RFM segmentation

RFM is the standard way to sort customers into groups from three numbers per customer:

  • Recency: days since the last order. Recent buyers are warmer.
  • Frequency: how many orders. Repeat buyers are the core of a shop.
  • Monetary: how much they spent in total.

The classic version scores each number from 1 to 5 with NTILE. With only 66 buyers and many customers sharing the same frequency (NTILE splits tied values into different buckets arbitrarily), explicit thresholds that you can explain to the owner work better. The rules below turn the numbers into five named segments:

Segment Rule
Lost last order more than 180 days ago
Needs attention last order 91 to 180 days ago
Champions ordered within 90 days, 4 or more orders
Promising ordered within 90 days, 2 or 3 orders
New ordered within 90 days, 1 order

The CASE runs top to bottom and stops at the first match, so the order of the branches is the logic: lapsed customers are caught first, whatever their frequency.

SQL RFM segments ยท database: bookshop

Press Run (or Ctrl + Enter) to see the result here.

The 16 Champions (24% of buyers) bring in 45.4% of all revenue, with 6.1 orders each on average. A further 34 customers are Lost: they spent 2,736.46 over their lifetime, 31.3% of the total, and have been silent for about a year. The group that deserves a campaign is Needs attention: ten customers averaging four orders each who have gone quiet for three to six months. They know the shop, and a reminder is cheap compared with winning a stranger.

Segments are only useful if someone acts on them. Write down what each group gets (a thank-you for Champions, a "we miss you" email for Needs attention) before you write the query, and re-run it every month.

Stage 3: repeat, one-time and lapsed customers

Segments describe buyers. The owner also wants the whole customer list classified, including people who never bought. A LEFT JOIN from customers keeps them, and the number prefixes in the labels make the groups sort in a sensible order:

SQL Customer lifecycle ยท database: bookshop

Press Run (or Ctrl + Enter) to see the result here.

Four customers (5.7%) never completed an order, 23 (32.9%) bought once, 25 (35.7%) are repeat buyers who ordered in the last 180 days, and 18 (25.7%) are repeat buyers who have lapsed. Among the 66 buyers the repeat rate is 65.2%: 43 bought more than once. That is a healthy number for a bookshop. The quiet problem is the 18 lapsed repeat buyers, who proved they like the shop and then left. The second query uses the fact that a comparison like orders >= 2 is 1 or 0 in SQLite, so SUM counts the true ones; in PostgreSQL or SQL Server you'd write SUM(CASE WHEN orders >= 2 THEN 1 ELSE 0 END) instead.

Stage 4: books bought together

A market basket report finds pairs of products that appear in the same order. It powers "customers also bought" boxes and bundle offers. The trick is a self join of order_items on the order id, which pairs every line with every other line in the same order. Counting each pair from both sides gives you a useful percentage: of the orders containing book A, how many also contain book B?

SQL Books that are bought together ยท database: bookshop

Press Run (or Ctrl + Enter) to see the result here.

The strongest pair is The Deep Green with A Century of Ink: 8 orders contain both, which is 42% of the 19 orders that include The Deep Green. The pairs make sense: a science book with a history book, a children's title with a cookbook, a mystery with a literary novel, fantasy with science fiction. <> in the join condition produces each pair twice (A with B and B with A), which is intentional here because the percentage depends on the direction. Use i1.book_id < i2.book_id instead when you only want a list of unique pairs.

Stage 5: marketing ROI by channel

Four channels cost money. Return on investment (ROI) is (profit - spend) / spend, so 0.5 means each dollar returned the dollar plus 50 cents. We use gross profit (unit_price - cost per unit) rather than revenue, because a book that sells for 20 cost the shop money to buy.

One CTE totals the profit of the customers each channel acquired, another totals the spend, and a LEFT JOIN keeps organic customers, who have no spend row:

SQL ROI by acquisition channel ยท database: bookshop

Press Run (or Ctrl + Enter) to see the result here.

Email is the clear winner: 510 spent for a net gain of 350.85 (an ROI of 0.69) and 39.23 per acquired buyer. Referral earns a smaller but positive 0.36. Social loses 568.09 (ROI -0.46) and search is the worst, spending 2,172 for 13 buyers that returned 575.47 in profit (-0.74, 167.08 per buyer). Organic customers cost nothing and produced 1,147.81 of profit, the most of any channel. Dividing by zero is avoided for organic because s.spend is NULL, so its roi is NULL rather than an error.

This is lifetime profit of customers acquired through a channel against all spend on that channel. It ignores customers who would have found the shop anyway, and recent customers have had less time to earn their keep. Treat it as a ranking to question, not a verdict to act on blindly.

Stage 6: low-stock alerts

Finally, the report that saves sales: which books are at or below their reorder_level, ranked by how well they are selling? A popular book that is nearly out of stock matters more than a slow one.

SQL Books to reorder ยท database: bookshop

Press Run (or Ctrl + Enter) to see the result here.

Four titles need attention. Pip and the Paper Boat has exactly hit its reorder level (9 in stock), but it sold the most in 2025. The Night Owl Club is 5 below its level and sold 13 copies. Both are Kids titles, so a buyer would reorder those first. The LEFT JOIN keeps books that sold nothing, which would otherwise vanish from the alert list.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why do the reports first build a CTE with one row per order?

    An order has several lines. Averaging the lines gives the average line value. Summing each order first and then averaging gives the average order value.
  2. 2. In the RFM query, why does the CASE test 'recency_days > 180' first?

    CASE returns the first branch that matches. A customer with six orders who has been silent for a year is Lost, not a Champion, so the recency tests have to come before the frequency tests.
  3. 3. What does the LEFT JOIN from customers to the kept-orders CTE make possible?

    An inner join would drop customers with no completed orders, so the lifecycle report would silently lose that group. The LEFT JOIN keeps them with NULL order columns.
  4. 4. Why use gross profit instead of revenue to compute marketing ROI?

    Spending 100 to earn 120 in revenue is a loss if the goods cost 70 of that revenue. Profit is the number that should be compared with spend.

Summary

  • Fix definitions first (today's date, which orders count, what revenue means), then build a one-row-per-order CTE and reuse it.
  • Average order value is revenue divided by orders, not by lines; track it next to order counts to see whether growth comes from volume or basket size.
  • RFM segments built from explicit thresholds in a CASE are easy to explain and act on; remember that CASE stops at the first matching branch.
  • Use LEFT JOIN from the full customer or product list whenever the report must include members with no activity.
  • A self join on order_items gives market-basket pairs; use <> for directional percentages and < for unique pairs.
  • Compare marketing spend with gross profit per acquisition channel, and be honest about what the number leaves out.

Next: Project: Social Media Engagement Analytics, where you measure what people like, comment on and follow.