Sales Analytics Data Warehouse

Introduction

30 Second Summary

Every online store collects mountains of sales data, but raw transaction records don't answer the questions that actually matter. Questions like "which products drive 80% of our revenue?" or "which customers are slipping away?" require transforming that data into something structured and queryable.

In this project, you will build a sales analytics data warehouse that transforms raw e-commerce data into a star schema optimized for business reporting. You will write advanced SQL queries, then connect your warehouse to a live dashboard with revenue trends and customer insights.

What You'll Build

You will design a star schema data warehouse in Google BigQuery using a public e-commerce dataset, write advanced SQL analytics reports with window functions and CTEs, and build a Looker Studio dashboard displaying business KPIs.

By the end of this project, you'll have:

• A star schema data warehouse in BigQuery with dimension and fact tables modeled from raw e-commerce data.
• A set of advanced SQL analytics reports using window functions, CTEs, and running totals to answer real business questions like revenue trends and customer lifetime value.
• A live Looker Studio dashboard with scorecard KPIs, time series charts, and interactive filters sourced directly from your warehouse.
• Secret Mission: Build an RFM customer segmentation model that classifies customers into actionable business segments using NTILE window functions.

Are there any prerequisites?

You should be comfortable with SQL fundamentals including SELECT, JOIN, GROUP BY, and aggregation functions. You also need a Google Cloud account with BigQuery access.

No additional software installations are required. Everything runs in your browser through the BigQuery console and Looker Studio.

Set Up Your Analytics Dataset and Explore Source Data

Raw transactional data is messy. Customers, orders, products, and line items live in separate tables with different column naming conventions and no unified structure for reporting.

In this step, you will create a dedicated analytics dataset in BigQuery, explore the source tables from Google's public TheLook e-commerce dataset, and plan the star schema you will build in the next step.

In this step, get ready to:

• Create the ecommerce_dwh dataset in BigQuery.
• Explore the TheLook source tables and their relationships.
• Document your star schema design plan.

Create your analytics dataset in BigQuery

A dataset in BigQuery is a container that holds your tables and views. You need one before you can create any warehouse tables.

• In the BigQuery console, click on your project name in the left Explorer pane.
• Click the three-dot menu next to your project name and select Create dataset.
• For Dataset ID, enter ecommerce_dwh.
• For Location type, select Multi-region, then choose US (multiple regions in United States).
• Leave all other settings at their defaults and click Create dataset.

Why US multi-region?

The TheLook public dataset is stored in the US multi-region location. BigQuery requires that all tables referenced in a query reside in the same location. Setting your dataset to US ensures your queries can join against the public source data.

Confirm you have access to the public source data by running a test query in the BigQuery query editor.

• Click the + button (or the SQL query option in the Create new section) to open a new query tab.
• Paste and run the following query:

-- Confirm access to the TheLook public dataset
SELECT COUNT(*) AS total_orders
FROM `bigquery-public-data.thelook_ecommerce.orders`;

What does this query do?

This query counts all rows in the public orders table. A successful result (you should see a number around 200,000+) confirms your project can read from the bigquery-public-data project.

• Click Run. You should see a single row in the results panel with a total_orders value in the hundreds of thousands.

Seeing an error about permissions or billing?

If you see a permissions error, make sure you are querying with the fully qualified table name including the bigquery-public-data project prefix. If you see a billing-related error, you may need to enable billing on your Google Cloud project. BigQuery's free tier covers 1 TiB of queries per month, so this project will not incur charges.

Help me fix my BigQuery permissions or billing error.

Explore the source schema

Before building your warehouse, you need to understand what data you are working with. The TheLook dataset contains four core tables you will use.

• Run the following query to check the columns in the users table:

-- Explore the users table structure
SELECT column_name, data_type
FROM `bigquery-public-data.thelook_ecommerce.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'users'
ORDER BY ordinal_position;

What does this query do?

The INFORMATION_SCHEMA.COLUMNS view is a metadata table that lists every column name and data type for tables in a dataset. This is a quick way to see a table's structure without scanning its data.

• Review the results. You should see columns like id, first_name, last_name, age, gender, country, traffic_source, and created_at.
• Now run profiling queries to check row counts and date ranges across all four core tables:

-- Profile row counts across core tables
SELECT 'users' AS table_name, COUNT(*) AS row_count
FROM `bigquery-public-data.thelook_ecommerce.users`
UNION ALL
SELECT 'orders', COUNT(*)
FROM `bigquery-public-data.thelook_ecommerce.orders`
UNION ALL
SELECT 'order_items', COUNT(*)
FROM `bigquery-public-data.thelook_ecommerce.order_items`
UNION ALL
SELECT 'products', COUNT(*)
FROM `bigquery-public-data.thelook_ecommerce.products`;

What should I see?

You should see approximate counts of 100K+ users, 200K+ orders, 400K+ order_items, and 30K+ products. These numbers may grow over time since the dataset is synthetic and periodically refreshed.

• Run one more profiling query to check the status values in the orders table:

-- Check distinct order status values
SELECT DISTINCT status, COUNT(*) AS order_count
FROM `bigquery-public-data.thelook_ecommerce.orders`
GROUP BY status
ORDER BY order_count DESC;

Why check status values?

When you build the fact table later, you will filter to only Complete orders for clean revenue analytics. Knowing all possible status values (Complete, Returned, Cancelled, etc.) helps you make informed filtering decisions.

• Click Run and review the results. Note that Complete should be the largest group.

Query taking too long or timing out?

The UNION ALL query scans four tables in one go. If it takes longer than expected, try running each SELECT individually. Also check that you are using the exact table names shown above (they are case-sensitive).

Help me troubleshoot slow BigQuery queries on public datasets.

Document the data model plan

Now that you know the source data, it is time to plan how you will restructure it into a star schema. A star schema separates data into dimension tables (descriptive attributes) and a central fact table (measurable events).

• Open a notes document or a piece of paper and sketch the following plan:

• dim_customers will pull from the users table: customer_id, first_name, last_name, age, gender, country, city, traffic_source, account_created_at.
• dim_products will pull from the products table: product_id, product_name, category, brand, department, cost, retail_price, profit_margin.
• dim_date will be a generated date spine: date_key, full_date, year, quarter, month, month_name, day_of_week, is_weekend.
• fact_order_items will join order_items with orders and products: order_item_id, order_id, customer_id, product_id, order_date, order_status, sale_price, product_cost, profit.

What is the grain of the fact table?

The grain is one row per order line item. Each row represents a single product purchased within an order. This is the most granular level for revenue analysis because a single order can contain multiple products at different prices.

The three measures in the fact table are sale_price (what the customer paid), product_cost (what the company paid), and profit (the difference).

• Write down the join paths that connect your tables:

• users.id = orders.user_id = order_items.user_id (connects customers to their orders and line items).
• products.id = order_items.product_id (connects products to line items).
• orders.order_id = order_items.order_id (connects order metadata to individual items).

Why plan before building?

In production data warehousing, teams always design the schema on paper before writing SQL. This prevents costly rebuilds later. Your plan ensures every source column has a clear destination and every join path is accounted for.

✔️ Awesome, I've got everything!

Great. You have your ecommerce_dwh dataset created, you have confirmed access to the source data, and you have a clear schema plan documented.

ⓧ I'd like to double check the full code

Here are all the queries you ran in this step for reference:

-- Query 1: Confirm access to the TheLook public dataset
SELECT COUNT(*) AS total_orders
FROM `bigquery-public-data.thelook_ecommerce.orders`;

-- Query 2: Explore the users table structure
SELECT column_name, data_type
FROM `bigquery-public-data.thelook_ecommerce.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'users'
ORDER BY ordinal_position;

-- Query 3: Profile row counts across core tables
SELECT 'users' AS table_name, COUNT(*) AS row_count
FROM `bigquery-public-data.thelook_ecommerce.users`
UNION ALL
SELECT 'orders', COUNT(*)
FROM `bigquery-public-data.thelook_ecommerce.orders`
UNION ALL
SELECT 'order_items', COUNT(*)
FROM `bigquery-public-data.thelook_ecommerce.order_items`
UNION ALL
SELECT 'products', COUNT(*)
FROM `bigquery-public-data.thelook_ecommerce.products`;

-- Query 4: Check distinct order status values
SELECT DISTINCT status, COUNT(*) AS order_count
FROM `bigquery-public-data.thelook_ecommerce.orders`
GROUP BY status
ORDER BY order_count DESC;

Your dataset is ready and you know your source data inside and out. Next up, you will turn your schema plan into real dimension and fact tables using SQL.

Design and Build the Star Schema

In the previous step, you explored the raw source tables and confirmed your access to the TheLook dataset. Now it is time to transform that raw data into a structured star schema optimized for analytics.

A star schema separates your data into two types of tables: dimension tables (descriptive attributes like customer names, product categories, and dates) and fact tables (measurable events like individual sales). This structure makes queries faster, simpler, and easier for BI tools to consume.

In this step, get ready to:

• Create dimension tables for customers, products, and dates.
• Create a fact table for order line items with revenue and profit metrics.
• Validate all join paths between dimensions and facts.

Create dim_customers

Your first dimension table captures customer attributes useful for segmentation and analysis. You will use a CREATE TABLE AS SELECT (CTAS) statement to pull columns directly from the source users table into your ecommerce_dwh dataset.

• Create the dim_customers table by pasting the following query into the BigQuery query editor and clicking Run:

-- Create the customers dimension table
CREATE TABLE `ecommerce_dwh.dim_customers` AS
SELECT
  id AS customer_id,
  first_name,
  last_name,
  age,
  gender,
  country,
  city,
  traffic_source,
  created_at AS account_created_at
FROM `bigquery-public-data.thelook_ecommerce.users`;

What does this query do?

The CREATE TABLE ... AS SELECT pattern creates a new table and populates it with the results of a query in one step. BigQuery derives the column names and types automatically from your SELECT clause.

The AS keyword renames source columns to analytics-friendly names. For example, id becomes customer_id so the column's purpose is obvious when joining to the fact table later.

• Confirm the table was created by running this verification query:

-- Verify dim_customers row count and sample data
SELECT COUNT(*) AS total_customers FROM `ecommerce_dwh.dim_customers`;

You should see a row count of approximately 100,000 customers.

Seeing an error about the table already existing?

If you ran the CREATE TABLE statement more than once, BigQuery returns an error because the table already exists. Replace CREATE TABLE with CREATE OR REPLACE TABLE at the top of the query and run again.

If you see a permissions error, make sure the dataset location is set to US (matching the public dataset location).

Help me troubleshoot this error

Create dim_products and dim_date

Next you will build two more dimension tables. The products dimension captures catalog attributes plus a calculated profit margin. The date dimension is a generated table covering every day in your orders date range, which makes time-based analysis much simpler.

• Create the dim_products table by pasting this query into the editor and clicking Run:

-- Create the products dimension table with a calculated profit margin
CREATE TABLE `ecommerce_dwh.dim_products` AS
SELECT
  id AS product_id,
  name AS product_name,
  category,
  brand,
  department,
  cost,
  retail_price,
  ROUND((retail_price - cost) / NULLIF(retail_price, 0), 2) AS profit_margin
FROM `bigquery-public-data.thelook_ecommerce.products`;

What does the profit_margin calculation do?

The formula (retail_price - cost) / retail_price calculates what percentage of the retail price is profit. NULLIF(retail_price, 0) prevents a division-by-zero error if any product has a retail price of zero.

ROUND(..., 2) keeps the result to two decimal places for cleaner reporting. A value of 0.45 means 45% profit margin.

• Verify dim_products was created successfully by running this query:

-- Check dim_products row count
SELECT COUNT(*) AS total_products FROM `ecommerce_dwh.dim_products`;

You should see approximately 30,000 products.

• Now create the dim_date table by pasting this query and clicking Run:

-- Create the date dimension using GENERATE_DATE_ARRAY
CREATE TABLE `ecommerce_dwh.dim_date` AS
SELECT
  FORMAT_DATE('%Y%m%d', d) AS date_key,
  d AS full_date,
  EXTRACT(YEAR FROM d) AS year,
  EXTRACT(QUARTER FROM d) AS quarter,
  EXTRACT(MONTH FROM d) AS month,
  FORMAT_DATE('%B', d) AS month_name,
  FORMAT_DATE('%A', d) AS day_of_week,
  CASE
    WHEN EXTRACT(DAYOFWEEK FROM d) IN (1, 7) THEN TRUE
    ELSE FALSE
  END AS is_weekend
FROM UNNEST(
  GENERATE_DATE_ARRAY(
    (SELECT DATE(MIN(created_at)) FROM `bigquery-public-data.thelook_ecommerce.orders`),
    (SELECT DATE(MAX(created_at)) FROM `bigquery-public-data.thelook_ecommerce.orders`),
    INTERVAL 1 DAY
  )
) AS d;

What does GENERATE_DATE_ARRAY do?

GENERATE_DATE_ARRAY creates an array containing every date between a start and end date. UNNEST then converts that array into individual rows, giving you one row per day.

The subqueries pull the earliest and latest order dates dynamically, so your date dimension automatically covers the full range of your orders data. Each row is enriched with year, quarter, month, day of week, and a weekend flag for flexible time-based analysis.

• Verify dim_date was created and check the date range:

-- Check the date range covered by dim_date
SELECT
  MIN(full_date) AS earliest_date,
  MAX(full_date) AS latest_date,
  COUNT(*) AS total_days
FROM `ecommerce_dwh.dim_date`;

You should see a date range spanning several years with one row per day.

Query takes a long time or returns an error?

The GENERATE_DATE_ARRAY query uses subqueries to find the min/max dates. If you see a timeout, try running the query again. BigQuery occasionally needs a moment to schedule cross-dataset queries.

If you see an error about dim_date already existing, use CREATE OR REPLACE TABLE instead of CREATE TABLE.

Help me debug this error

Create fact_order_items

The fact table is the heart of your star schema. It records every completed order line item with its revenue and profit, linking back to each dimension through foreign keys.

• Create the fact_order_items table by pasting this query and clicking Run:

-- Create the central fact table by joining order_items, orders, and products
CREATE TABLE `ecommerce_dwh.fact_order_items` AS
SELECT
  oi.id AS order_item_id,
  oi.order_id,
  oi.user_id AS customer_id,
  oi.product_id,
  DATE(oi.created_at) AS order_date,
  oi.status AS order_status,
  oi.sale_price,
  p.cost AS product_cost,
  oi.sale_price - p.cost AS profit
FROM `bigquery-public-data.thelook_ecommerce.order_items` AS oi
JOIN `bigquery-public-data.thelook_ecommerce.orders` AS o
  ON oi.order_id = o.order_id
JOIN `bigquery-public-data.thelook_ecommerce.products` AS p
  ON oi.product_id = p.id
WHERE oi.status = 'Complete';

What does this query do?

This query joins three source tables together. order_items provides the line-level detail (what was sold, at what price). orders provides the order-level context. products provides the cost so you can calculate profit.

The WHERE oi.status = 'Complete' filter ensures you only include successfully fulfilled orders. This gives you clean revenue data without noise from cancelled or returned items.

The order_date column is cast to DATE so it can join cleanly to dim_date.full_date in your analytics queries.

• Verify the fact table and confirm the row count is less than the raw order_items table (because you filtered to Complete status only):

-- Compare fact table count to raw source count
SELECT
  (SELECT COUNT(*) FROM `ecommerce_dwh.fact_order_items`) AS fact_rows,
  (SELECT COUNT(*) FROM `bigquery-public-data.thelook_ecommerce.order_items`) AS raw_rows;

The fact_rows count should be noticeably less than raw_rows because your fact table only includes completed orders.

• Validate that all dimension-to-fact join paths work by running this test query:

-- Validate join paths between fact and all dimensions
SELECT
  f.order_item_id,
  c.first_name,
  p.product_name,
  d.month_name
FROM `ecommerce_dwh.fact_order_items` AS f
JOIN `ecommerce_dwh.dim_customers` AS c
  ON f.customer_id = c.customer_id
JOIN `ecommerce_dwh.dim_products` AS p
  ON f.product_id = p.product_id
JOIN `ecommerce_dwh.dim_date` AS d
  ON f.order_date = d.full_date
LIMIT 10;

You should see 10 rows with data from all four tables joined together successfully. This confirms your star schema is correctly linked.

Join validation returning zero rows?

If the join to dim_date returns no results, check that order_date in your fact table is a DATE type (not TIMESTAMP). The DATE() cast in the fact table creation query handles this.

If the join to dim_customers fails, confirm both tables use the same column name (customer_id). The fact table aliases user_id to customer_id in its SELECT.

Help me debug my join paths

Why filter to Complete orders only?

In production data warehouses, fact tables typically contain only finalized transactions. Including cancelled or returned orders would inflate revenue numbers and create misleading dashboards.

If you needed to analyze returns separately, you would create a second fact table (like fact_returns) with its own grain. Keeping concerns separate makes each table trustworthy for its specific purpose.

✔️ Awesome, I've got everything!

Great. Double check that your ecommerce_dwh dataset now shows four tables in the BigQuery Explorer panel on the left: dim_customers, dim_products, dim_date, and fact_order_items.

ⓧ I'd like to double check the full code

Here are all four CREATE TABLE statements together. You can run each one individually (BigQuery only allows one CREATE TABLE per execution):

-- 1. dim_customers
CREATE TABLE `ecommerce_dwh.dim_customers` AS
SELECT
  id AS customer_id,
  first_name,
  last_name,
  age,
  gender,
  country,
  city,
  traffic_source,
  created_at AS account_created_at
FROM `bigquery-public-data.thelook_ecommerce.users`;

-- 2. dim_products
CREATE TABLE `ecommerce_dwh.dim_products` AS
SELECT
  id AS product_id,
  name AS product_name,
  category,
  brand,
  department,
  cost,
  retail_price,
  ROUND((retail_price - cost) / NULLIF(retail_price, 0), 2) AS profit_margin
FROM `bigquery-public-data.thelook_ecommerce.products`;

-- 3. dim_date
CREATE TABLE `ecommerce_dwh.dim_date` AS
SELECT
  FORMAT_DATE('%Y%m%d', d) AS date_key,
  d AS full_date,
  EXTRACT(YEAR FROM d) AS year,
  EXTRACT(QUARTER FROM d) AS quarter,
  EXTRACT(MONTH FROM d) AS month,
  FORMAT_DATE('%B', d) AS month_name,
  FORMAT_DATE('%A', d) AS day_of_week,
  CASE
    WHEN EXTRACT(DAYOFWEEK FROM d) IN (1, 7) THEN TRUE
    ELSE FALSE
  END AS is_weekend
FROM UNNEST(
  GENERATE_DATE_ARRAY(
    (SELECT DATE(MIN(created_at)) FROM `bigquery-public-data.thelook_ecommerce.orders`),
    (SELECT DATE(MAX(created_at)) FROM `bigquery-public-data.thelook_ecommerce.orders`),
    INTERVAL 1 DAY
  )
) AS d;

-- 4. fact_order_items
CREATE TABLE `ecommerce_dwh.fact_order_items` AS
SELECT
  oi.id AS order_item_id,
  oi.order_id,
  oi.user_id AS customer_id,
  oi.product_id,
  DATE(oi.created_at) AS order_date,
  oi.status AS order_status,
  oi.sale_price,
  p.cost AS product_cost,
  oi.sale_price - p.cost AS profit
FROM `bigquery-public-data.thelook_ecommerce.order_items` AS oi
JOIN `bigquery-public-data.thelook_ecommerce.orders` AS o
  ON oi.order_id = o.order_id
JOIN `bigquery-public-data.thelook_ecommerce.products` AS p
  ON oi.product_id = p.id
WHERE oi.status = 'Complete';

Your star schema is built. You have clean dimension tables for customers, products, and dates, all connected to a central fact table tracking every completed sale. Next up, you will write advanced SQL analytics that leverage this structure to answer real business questions.

Write Advanced SQL Analytics Reports

Your star schema is built and validated. Now it is time to put it to work by writing the kind of SQL analytics reports that hiring managers look for on a resume.

In this step, you will write three queries that answer real business questions using advanced SQL techniques. Each query joins your fact and dimension tables, applies window functions and CTEs, and gets saved as a reusable view in your ecommerce_dwh dataset.

In this step, get ready to:

• Write a monthly revenue query with year-over-year comparison using LAG.
• Rank product categories by revenue and calculate cumulative contribution using RANK and running totals.
• Calculate customer lifetime value metrics and rank customers into deciles using NTILE.

Create the monthly revenue trend with year-over-year comparison

A stakeholder asking "how is revenue trending compared to last year?" is one of the most common business questions. The LAG window function lets you look back at a previous row in your result set to compare the same month across different years.

• Create the monthly revenue view by running this query in the BigQuery query editor:

CREATE OR REPLACE VIEW `ecommerce_dwh.vw_monthly_revenue_yoy` AS
-- Monthly revenue with year-over-year comparison using LAG
WITH monthly_revenue AS (
  SELECT
    d.year,
    d.month,
    -- Total revenue for each month
    SUM(f.sale_price) AS monthly_revenue
  FROM `ecommerce_dwh.fact_order_items` f
  JOIN `ecommerce_dwh.dim_date` d
    ON f.order_date = d.full_date
  GROUP BY d.year, d.month
)
SELECT
  year,
  month,
  monthly_revenue,
  -- Look back 12 rows to find same month last year
  LAG(monthly_revenue, 12) OVER (ORDER BY year, month) AS prior_year_revenue,
  -- Calculate percentage growth year-over-year
  ROUND(
    (monthly_revenue - LAG(monthly_revenue, 12) OVER (ORDER BY year, month))
    / LAG(monthly_revenue, 12) OVER (ORDER BY year, month) * 100,
    2
  ) AS yoy_growth_pct
FROM monthly_revenue
ORDER BY year, month;

What does this query do?

The CTE groups your fact table by year and month (from dim_date) to get a single revenue total per month.

The outer SELECT uses LAG(monthly_revenue, 12) to reach back exactly 12 rows. Because the data is ordered by year and month, 12 rows back lands on the same month of the prior year. The growth percentage is then calculated from those two values.

• Click Run in the query editor.
• You should see a confirmation message that the view was created successfully.
• Verify the view works by running this query:

SELECT * FROM `ecommerce_dwh.vw_monthly_revenue_yoy`
WHERE prior_year_revenue IS NOT NULL
LIMIT 10;

You should see rows with year, month, monthly_revenue, prior_year_revenue, and a yoy_growth_pct value showing the percentage change.

Seeing an error about the view?

Make sure you are running the query against the correct project. The dataset ecommerce_dwh must already exist in your project from the previous step.

If you see a "not found" error for dim_date or fact_order_items, confirm these tables exist in your ecommerce_dwh dataset by expanding it in the Explorer panel on the left.

Help me debug my vw_monthly_revenue_yoy view creation error

Create the product category performance report

The next question stakeholders ask is "which product categories drive most of our revenue?" A Pareto analysis answers this by ranking categories and showing their cumulative contribution to total revenue.

• Create the category performance view by running this query:

CREATE OR REPLACE VIEW `ecommerce_dwh.vw_category_performance` AS
-- Product category revenue ranked with cumulative percentage (Pareto analysis)
WITH category_revenue AS (
  SELECT
    p.category,
    -- Total revenue per category
    SUM(f.sale_price) AS total_revenue
  FROM `ecommerce_dwh.fact_order_items` f
  JOIN `ecommerce_dwh.dim_products` p
    ON f.product_id = p.product_id
  GROUP BY p.category
),
ranked AS (
  SELECT
    category,
    total_revenue,
    -- Rank categories by revenue (highest first)
    RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank,
    -- Grand total across all categories
    SUM(total_revenue) OVER () AS grand_total
  FROM category_revenue
)
SELECT
  category,
  total_revenue,
  revenue_rank,
  -- Each category's share of total revenue
  ROUND(total_revenue / grand_total * 100, 2) AS pct_of_total,
  -- Running cumulative percentage (Pareto curve)
  ROUND(
    SUM(total_revenue) OVER (ORDER BY total_revenue DESC) / grand_total * 100,
    2
  ) AS cumulative_pct
FROM ranked
ORDER BY revenue_rank;

What does this query do?

The first CTE sums revenue by product category by joining fact_order_items to dim_products. The second CTE applies RANK() to order categories from highest to lowest revenue and calculates the grand total using SUM() OVER () with an empty window (meaning all rows).

The final SELECT calculates each category's percentage of total and a running cumulative percentage. When cumulative_pct reaches 80%, you have identified the categories that drive 80% of revenue. This is the classic Pareto principle in action.

• Click Run to create the view.
• Verify it by running:

SELECT * FROM `ecommerce_dwh.vw_category_performance`
LIMIT 10;

You should see categories ranked by revenue, with cumulative_pct increasing toward 100% as you move down the list.

View created but results look wrong?

If cumulative_pct jumps straight to 100% on the first row, check that your ORDER BY inside the window function matches the outer ORDER BY. Both should sort by total_revenue DESC.

Help me fix my category performance view

Create the customer lifetime value analysis

Identifying your most valuable customers is essential for retention and marketing strategy. This query calculates per-customer metrics and then uses NTILE(10) to split customers into ten equal groups (deciles) ranked by total revenue.

• Create the customer lifetime value view by running this query:

CREATE OR REPLACE VIEW `ecommerce_dwh.vw_customer_ltv` AS
-- Customer lifetime value with revenue decile ranking
WITH customer_metrics AS (
  SELECT
    f.customer_id,
    c.first_name,
    c.last_name,
    -- Frequency: how many orders
    COUNT(DISTINCT f.order_id) AS total_orders,
    -- Monetary: total spend
    SUM(f.sale_price) AS total_revenue,
    -- Average order value
    ROUND(AVG(f.sale_price), 2) AS avg_order_value,
    -- First and last purchase dates
    MIN(f.order_date) AS first_order_date,
    MAX(f.order_date) AS last_order_date,
    -- Days between first and last order
    DATE_DIFF(MAX(f.order_date), MIN(f.order_date), DAY) AS customer_lifetime_days
  FROM `ecommerce_dwh.fact_order_items` f
  JOIN `ecommerce_dwh.dim_customers` c
    ON f.customer_id = c.customer_id
  GROUP BY f.customer_id, c.first_name, c.last_name
)
SELECT
  *,
  -- Split customers into 10 equal groups by revenue (10 = highest)
  NTILE(10) OVER (ORDER BY total_revenue) AS revenue_decile
FROM customer_metrics
ORDER BY total_revenue DESC;

What does this query do?

The CTE joins fact_order_items to dim_customers and calculates per-customer metrics: order count, total revenue, average order value, first and last order dates, and lifetime in days.

The outer query applies NTILE(10) which divides all customers into 10 equally-sized buckets ordered by revenue. Customers in decile 10 are your top spenders. This is a common technique for identifying high-value customer segments without setting arbitrary revenue thresholds.

• Click Run to create the view.
• Verify it by querying the top decile:

SELECT * FROM `ecommerce_dwh.vw_customer_ltv`
WHERE revenue_decile = 10
ORDER BY total_revenue DESC
LIMIT 10;

You should see your top 10% of customers by revenue, with their order counts, lifetime days, and a revenue_decile value of 10.

Getting a column not found error?

Double check that your dim_customers table has columns named customer_id, first_name, and last_name. You can confirm by clicking the table name in the Explorer panel and checking its schema tab.

Help me debug my customer LTV view

✔️ Awesome, I've got all three views!

Great work. Double check that all three views appear under your ecommerce_dwh dataset in the Explorer panel. You may need to click the refresh icon next to your dataset name to see them.

ⓧ I'd like to double check the full code

Here are all three view creation queries for reference:

-- View 1: Monthly revenue with year-over-year comparison
CREATE OR REPLACE VIEW `ecommerce_dwh.vw_monthly_revenue_yoy` AS
WITH monthly_revenue AS (
  SELECT
    d.year,
    d.month,
    SUM(f.sale_price) AS monthly_revenue
  FROM `ecommerce_dwh.fact_order_items` f
  JOIN `ecommerce_dwh.dim_date` d
    ON f.order_date = d.full_date
  GROUP BY d.year, d.month
)
SELECT
  year,
  month,
  monthly_revenue,
  LAG(monthly_revenue, 12) OVER (ORDER BY year, month) AS prior_year_revenue,
  ROUND(
    (monthly_revenue - LAG(monthly_revenue, 12) OVER (ORDER BY year, month))
    / LAG(monthly_revenue, 12) OVER (ORDER BY year, month) * 100,
    2
  ) AS yoy_growth_pct
FROM monthly_revenue
ORDER BY year, month;

-- View 2: Product category performance with Pareto analysis
CREATE OR REPLACE VIEW `ecommerce_dwh.vw_category_performance` AS
WITH category_revenue AS (
  SELECT
    p.category,
    SUM(f.sale_price) AS total_revenue
  FROM `ecommerce_dwh.fact_order_items` f
  JOIN `ecommerce_dwh.dim_products` p
    ON f.product_id = p.product_id
  GROUP BY p.category
),
ranked AS (
  SELECT
    category,
    total_revenue,
    RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank,
    SUM(total_revenue) OVER () AS grand_total
  FROM category_revenue
)
SELECT
  category,
  total_revenue,
  revenue_rank,
  ROUND(total_revenue / grand_total * 100, 2) AS pct_of_total,
  ROUND(
    SUM(total_revenue) OVER (ORDER BY total_revenue DESC) / grand_total * 100,
    2
  ) AS cumulative_pct
FROM ranked
ORDER BY revenue_rank;

-- View 3: Customer lifetime value with revenue deciles
CREATE OR REPLACE VIEW `ecommerce_dwh.vw_customer_ltv` AS
WITH customer_metrics AS (
  SELECT
    f.customer_id,
    c.first_name,
    c.last_name,
    COUNT(DISTINCT f.order_id) AS total_orders,
    SUM(f.sale_price) AS total_revenue,
    ROUND(AVG(f.sale_price), 2) AS avg_order_value,
    MIN(f.order_date) AS first_order_date,
    MAX(f.order_date) AS last_order_date,
    DATE_DIFF(MAX(f.order_date), MIN(f.order_date), DAY) AS customer_lifetime_days
  FROM `ecommerce_dwh.fact_order_items` f
  JOIN `ecommerce_dwh.dim_customers` c
    ON f.customer_id = c.customer_id
  GROUP BY f.customer_id, c.first_name, c.last_name
)
SELECT
  *,
  NTILE(10) OVER (ORDER BY total_revenue) AS revenue_decile
FROM customer_metrics
ORDER BY total_revenue DESC;

You now have three saved views that demonstrate advanced SQL techniques interviewers love to ask about. Next up, you will connect these views to Looker Studio and build a live dashboard that visualizes the insights.

Build a Looker Studio Dashboard

Your data warehouse is loaded with analytics views that answer real business questions. The next step is making those insights accessible to anyone on a team without requiring them to write SQL.

In this step, you will connect your BigQuery warehouse to Looker Studio and build an executive dashboard with KPIs, trend charts, and interactive filters. This is how data analysts deliver insights in production.

In this step, get ready to:

• Connect Looker Studio to your BigQuery data warehouse.
• Build scorecard KPIs, a time series chart, a bar chart, and a customer table.
• Add interactive filters and publish the dashboard.

Connect Looker Studio to BigQuery

Looker Studio is a free BI tool from Google that connects natively to BigQuery. You will create a new report and point it at your ecommerce_dwh dataset so all your dimension tables and views become available as data sources.

• Open a new browser tab and navigate to lookerstudio.google.com.
• Sign in with the same Google account you use for BigQuery.
• Click Create in the top left.
• Select Report.
• The Add data to report panel opens automatically.
• Click the Connect to data tab.
• Select the BigQuery connector.
• Authorize access if prompted.
• Under MY PROJECTS, select your Google Cloud project.
• Select the ecommerce_dwh dataset.
• Select the fact_order_items table.
• Click Add in the lower right corner.

A default table appears on the report canvas. You will replace it with your dashboard components shortly.

Why start with fact_order_items?

The fact table is the central table in your star schema. It contains the measures (sale_price, profit) and foreign keys to all dimensions. Looker Studio can derive most of your KPIs from this single source.

You will add your saved views as additional data sources next for the charts that need pre-aggregated data.

• Now add your saved views as additional data sources. In the toolbar, click Add data.
• Select the BigQuery connector again.
• Navigate to your project, then ecommerce_dwh, and select vw_monthly_revenue_yoy.
• Click Add.
• Repeat this process to add vw_category_performance and vw_customer_ltv as data sources.

What should I see?

You should now have four data sources listed in your report. You can verify by clicking Resource in the menu bar and selecting Manage added data sources. You should see fact_order_items, vw_monthly_revenue_yoy, vw_category_performance, and vw_customer_ltv.

Don't see your dataset or views?

Make sure you are signed into Looker Studio with the same Google account that owns the BigQuery project. If you see no projects listed, check that billing is enabled on your Google Cloud project.

If your views do not appear in the table list, confirm they were saved as views (not just run as queries) in the previous step.

Still stuck? Help me connect Looker Studio to my BigQuery ecommerce_dwh dataset.

Build the dashboard with business KPIs

An executive dashboard answers the top questions a stakeholder asks at a glance. You will build four scorecard KPIs across the top row, then add visualization charts below them.

• Delete the default table that Looker Studio placed on the canvas by selecting it and pressing Delete on your keyboard.
• Name your report by clicking Untitled Report in the top left and typing Sales Analytics Dashboard.

Now add your first scorecard KPI for Total Revenue.

• In the toolbar, click Add a chart.
• Select Scorecard.
• Click the top-left area of your canvas to place the scorecard.
• In the Properties panel on the right, confirm the data source is set to fact_order_items.
• Set the Metric to sale_price with aggregation SUM.
• In the Style tab, add a label that reads Total Revenue.

Repeat this process to create three more scorecards.

• Add a second scorecard. Set the metric to order_id with aggregation Count Distinct. Label it Total Orders.
• Add a third scorecard. Set the metric to customer_id with aggregation Count Distinct. Label it Total Customers.
• Add a fourth scorecard. Set the metric to sale_price with aggregation Average. Label it Average Order Value.
• Arrange all four scorecards in a horizontal row across the top of your canvas.

What do these KPIs tell a stakeholder?

Total Revenue shows the business's top-line income. Total Orders measures transaction volume. Total Customers counts unique buyers. Average Order Value reveals how much each transaction is worth on average.

Together, these four numbers give an instant health check of the business.

Next, add a time series chart for the monthly revenue trend.

• Click Add a chart in the toolbar.
• Select Time series.
• Place it below the scorecards on the left half of the canvas.
• In the Properties panel, change the data source to vw_monthly_revenue_yoy.
• Set the Dimension (date) to the year and month fields from your view.
• Set the Metric to monthly_revenue.

Now add a bar chart for product category performance.

• Click Add a chart and select Bar (horizontal bar chart).
• Place it on the right half of the canvas, next to the time series.
• Change the data source to vw_category_performance.
• Set the Dimension to the product category field.
• Set the Metric to total revenue.
• In the Setup tab, set Sort to descending by revenue and limit to the top 10 rows.

Finally, add a table showing your top customers by lifetime value.

• Click Add a chart and select Table.
• Place it below the time series and bar chart.
• Change the data source to vw_customer_ltv.
• Add dimensions for customer name fields (first_name, last_name).
• Add metrics for total_revenue, total_orders, and avg_order_value.
• Sort by total_revenue descending to show your highest-value customers first.

What should I see?

Your canvas now has four scorecards across the top, a time series line chart on the left, a horizontal bar chart on the right, and a customer table at the bottom. All charts should display live data from your BigQuery warehouse.

Charts showing errors or no data?

Check that each chart's data source is set to the correct view or table. Click the chart, then look at the data source name in the Properties panel.

If a metric shows zero, confirm the aggregation type matches what you need (SUM for revenue, Count Distinct for unique counts, Average for averages).

Need more help? My Looker Studio charts are showing errors or no data from my BigQuery views.

Add interactivity and publish

Static dashboards are useful, but interactive filters let stakeholders explore the data themselves. You will add a date range control and a product category dropdown so viewers can slice the data without editing the report.

• In the toolbar, select Add a control.
• Select the Date range control option.
• Place it at the top of your canvas, above the scorecards.
• The date range control filters all charts on the page by their date dimension automatically.

• Click Add a control again.
• Select Drop-down list.
• Place it next to the date range control at the top of the canvas.
• In the Properties panel, set the Control field to the product category dimension from fact_order_items.

How do controls filter across charts?

Controls in Looker Studio apply to all charts on the page that share the same data source or a linked dimension. When a viewer picks a date range or selects a category, every chart updates instantly to reflect that filter.

Now verify the filters work and publish your dashboard.

• Click View in the top right to switch from edit mode to view mode.
• Test the date range control by selecting a specific year or quarter.
• Test the category dropdown by selecting a single product category.
• Confirm that all charts update to reflect your filter selections.
• Switch back to edit mode by clicking the pencil icon or Edit button.
• Click Share in the top right corner to open sharing settings.
• Take a screenshot of your completed dashboard for your portfolio.

Make it portfolio-ready

Before taking your screenshot, switch to View mode so the edit toolbar is hidden. This gives a cleaner presentation. Consider adding a text box with a title and your name at the top of the dashboard for a professional touch.

Filters not affecting all charts?

If a chart does not respond to the date range filter, click the chart in edit mode, open the Setup tab in Properties, and confirm a date dimension is set under Date Range Dimension. The category dropdown only filters charts that use the same data source or a blended source that includes the category field.

Still having trouble? Help me figure out why my Looker Studio filters are not updating all my charts.

Your data warehouse is now delivering insights through a live, interactive dashboard. Anyone with the link can explore revenue trends, product performance, and customer value without writing a single line of SQL. Next up, you will tackle the Secret Mission and build a customer segmentation model.

Secret mission

Build an RFM Customer Segmentation Model

Your data warehouse delivers revenue trends and product performance. But can it answer the most strategic question in e-commerce: which customers are slipping away, and which ones are your champions? Build an RFM segmentation model to find out.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely within BigQuery's free tier (10 GiB storage, 1 TiB queries per month) and Looker Studio is free. There are no ongoing costs.

Resources you used:

• BigQuery dataset ecommerce_dwh (4 tables + 4 saved views).
• Looker Studio report (Sales Analytics Dashboard).

Keep everything running

No action needed. Choose this if you plan to keep exploring the data or building on your dashboard.

• Your ecommerce_dwh dataset stays in BigQuery indefinitely within free tier storage limits.
• Your Looker Studio dashboard remains accessible at lookerstudio.google.com.
• No charges accrue. The TheLook-derived tables are well under 1 GiB total.

Pause - I'll come back to this later

Because BigQuery storage is serverless and falls within the free tier, there is nothing to pause. Your dataset persists at no cost until you choose to delete it.

• Leave the ecommerce_dwh dataset in place. It does not consume compute when idle.
• Your Looker Studio report remains saved in your account. Reopen it anytime from lookerstudio.google.com.

Delete - I don't want to use this again

Remove all project resources permanently. This cannot be undone.

Delete the BigQuery dataset:

• Open the BigQuery console.
• In the Explorer pane on the left, expand your project and click on the ecommerce_dwh dataset.
• Expand the View actions menu (three dots) and click Delete.
• In the Delete dataset dialog, type delete into the confirmation field and click Delete.

This removes all tables and views inside the dataset automatically.

Alternatively, run this SQL statement in the BigQuery query editor:

DROP SCHEMA IF EXISTS ecommerce_dwh CASCADE;

Delete the Looker Studio report:

• Go to lookerstudio.google.com.
• Locate your Sales Analytics Dashboard in the reports list.
• Hover over the report name, click the three-dot menu, and click Remove.
• On the left sidebar, click Trash.
• Hover over the report, click the three-dot menu, and click Delete forever.

Nice Work!

Nice Work!

You just built a complete sales analytics data warehouse from scratch. Starting with raw e-commerce data, you modeled a star schema, wrote advanced SQL analytics, and delivered a live dashboard that answers real business questions.

You've learned how to:

• Design and build a star schema data warehouse in BigQuery with dimension tables (dim_customers, dim_products, dim_date) and a fact table (fact_order_items) using CREATE TABLE AS SELECT patterns.
• Write advanced SQL analytics using window functions (LAG, RANK, NTILE), CTEs, and running totals to answer business questions like revenue trends, product performance, and customer lifetime value.
• Connect your warehouse to Looker Studio and build an interactive dashboard with KPI scorecards, time series charts, and filter controls that pull live data from BigQuery.
• Secret Mission: Built an RFM customer segmentation model using NTILE window functions to classify customers into actionable business segments like Champions, Loyal Customers, At Risk, and Lost.

Ready to quiz yourself?