Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 37 ยท Projects and Practice

Project: E-commerce Analytics

Build an e-commerce analytics warehouse: design partitioned and clustered tables, then write revenue, customer, cohort and basket queries you can run here.

In this project you act as the data engineer for a small online bookstore. The goal is a reporting layer that answers the questions a business asks every Monday: how much did we sell, who are our best customers, which genres drive revenue, and do customers come back? You will design the BigQuery tables first, then write the analysis queries.

The real BigQuery service cannot run in this page. The table definitions are shown as BigQuery DDL (read-only), and the analysis queries run in SQLite against the course's bookstore sample database, using only SQL that works the same way in BigQuery. Where the BigQuery spelling differs, a note says so.

Step 1: the warehouse design

The sample database has four tables: authors, books, customers and orders (each order row is one book purchased). In BigQuery you would load them as raw tables, then build facts and dimensions on top.

flowchart LR
  R1["raw.orders"] --> F["fact_orders<br/>partitioned by order_date<br/>clustered by customer_id, book_id"]
  R2["raw.books + authors"] --> D1["dim_books"]
  R3["raw.customers"] --> D2["dim_customers"]
  F --> M["mart_daily_revenue"]
  F --> C["mart_customer_value"]
  D1 --> M
  D2 --> C

Raw data is loaded once; facts and marts are rebuilt by scheduled queries or Dataform.

The fact table is the big one, so it gets the partitioning and clustering from the earlier lessons (choosing keys):

SQL
CREATE TABLE bookstore.fact_orders (
  order_id     INT64,
  customer_id  INT64,
  book_id      INT64,
  quantity     INT64,
  unit_price   NUMERIC,
  line_total   NUMERIC,
  order_date   DATE
)
PARTITION BY order_date
CLUSTER BY customer_id, book_id
OPTIONS (require_partition_filter = TRUE);

Dimensions are small, so they stay unpartitioned. Notice line_total is stored, not recomputed in every query: a small denormalization that makes marts simpler and cheaper.

Step 2: look at the data

Start every project by checking row counts and ranges. This also confirms how the sample data relates.

SQL Profile the tables ยท database: bookstore

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

Step 3: the fact table as a query

The fact table is just a join. This is the SELECT you would put in a CREATE TABLE ... AS or a Dataform model:

SQL Build the order fact ยท database: bookstore

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

In BigQuery you would write CAST(o.ordered_on AS DATE) for the partition column if the source were text, and SAFE_CAST if some values might be malformed.

Step 4: revenue by genre and month

The first mart answers "where does the money come from?". Join the fact to the book dimension and group.

SQL Revenue by genre ยท database: bookstore

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

In BigQuery, a monthly trend uses DATE_TRUNC(order_date, MONTH); the SQLite equivalent is strftime('%Y-%m', ordered_on).

SQL Monthly revenue with a running total ยท database: bookstore

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

Window functions such as SUM(...) OVER (ORDER BY month) are identical in BigQuery.

Step 5: customer value

The second mart ranks customers by what they spent. Real analyses add recency and frequency (RFM); here we compute spend, order count and the date of the last purchase.

SQL Top customers ยท database: bookstore

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

Step 6: repeat customers and first-order cohorts

A cohort groups customers by the month of their first purchase and asks whether they came back. First find each customer's first order, then count the cohort:

SQL Cohorts by first-order month ยท database: bookstore

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

For a full retention table you would join each order back to its customer's cohort and count active customers per month offset, usually with DATE_DIFF(order_month, cohort_month, MONTH) in BigQuery.

Step 7: which books sell together?

Market-basket analysis counts how often two books appear in the same customer's history. A self-join on orders produces pairs:

SQL Books bought by the same customer ยท database: bookstore

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

On a large BigQuery table a self-join explodes quickly, so you would filter to a date window first, cluster by customer_id and consider APPROX techniques or sampling.

Step 8: ship it

Turn the queries into a repeatable pipeline: load raw tables (loading data), rebuild the fact with a scheduled query, expose the marts to Looker Studio, and give the sales team access to the marts only (IAM). Add maximum_bytes_billed to every scheduled job and review INFORMATION_SCHEMA.JOBS monthly (cost control).

Stretch goals

  • Add a dim_date table and join it for week and quarter reports.
  • Build a customer segment table using RFM scores and NTILE(4).
  • Write a BigQuery ML kmeans model on the RFM features (clustering lesson).
  • Add a row access policy so city managers see only their customers.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why cluster fact_orders by customer_id and book_id?

    Cluster columns that appear in filters and joins; the partition on order_date handles date filters.
  2. 2. What does a cohort analysis group customers by?

    Cohorts group customers by when they started, then track behavior over time.
  3. 3. Which function gives a running total over months?

    A window SUM with ORDER BY accumulates values row by row.
  4. 4. In the pair query, why is the condition o1.book_id < o2.book_id used?

    Requiring a strict order avoids duplicates like (A,B) and (B,A) and self-pairs.

Summary

  • Design first: partitioned and clustered fact table, small dimensions, marts for reporting.
  • Revenue by genre, monthly trends with running totals, top customers, cohorts and basket pairs answer the core business questions.
  • Window functions, joins and aggregates are the same in BigQuery; date helpers (DATE_TRUNC, DATE_DIFF) differ in spelling.
  • Automate with scheduled queries, protect with IAM, and cap costs.

Next: Project: Event Log Pipeline