Cohort Retention Analysis – Looker Studio

Overview

This project demonstrates end-to-end cohort retention analysis – from writing SQL queries in BigQuery to building a self-service Looker Studio dashboard. The goal is to transform raw transactional data into retention insights that support marketing, product, and growth decisions.

Business Context

Retention is one of the most important levers for sustainable growth. Acquiring new customers is expensive; understanding how long they stay, when they churn, and which channels produce the most durable customers allows teams to allocate resources more effectively. This analysis simulates the role of an analyst supporting growth and marketing teams at an e-commerce company.

Objectives

• Size monthly acquisition cohorts and track new customer volume over time
• Build a full retention matrix showing how each cohort retains month-over-month
• Measure revenue retention to understand how cohort spending evolves over time
• Compare retention rates across acquisition channels (traffic sources)
• Segment customers into lifecycle stages (Active, At-Risk, Churned) and detect reactivations
• Track cumulative revenue per customer to understand long-term lifetime value by cohort
• Deliver all insights through an interactive Looker Studio dashboard

Dataset

thelook_ecommerce is a public BigQuery dataset simulating an online retail business. It contains transactional order data, line-item purchases, product attributes, and customer information. Only completed orders (status = 'Complete') with created_at < '2025-01-01' are included to ensure a clean, bounded analysis window.

Core Tables Used

• orders – one row per order (timestamps, status, user_id)
• order_items – one row per item purchased (sale_price, product_id, order_id)
• users – customer attributes (traffic_source, demographics)

SQL Techniques Demonstrated

• Common Table Expressions (CTEs) for modular, readable query design
• Window functions (LAG, SUM() OVER) for gap detection and running totals
• DATE_TRUNC and DATE_DIFF for cohort assignment and period calculation
• Multi-table joins (orders, order_items, users)
• CASE statements for lifecycle segmentation
• Self-joins for baseline comparisons (revenue retention)

KPI Definitions

• Cohort Month – the month of a customer's first completed order
• Period Number – months elapsed since cohort month (0 = first month)
• Retention % – active_users / cohort_size × 100
• Revenue Retention % – period_revenue / baseline_revenue × 100
• Customer Status – Active (≤90 days since last order), At-Risk (91–180 days), Churned (>180 days)
• Cumulative Revenue per Customer – running total of revenue divided by cohort size

Tools Used

• Google BigQuery (SQL)
• Google Looker Studio (dashboard & visualization)
• GitHub (version control)
• GitHub Pages (documentation & portfolio publishing)

Business Question

How many new customers are acquired each month, and how has acquisition volume changed over time?

Method

Each customer is assigned to a cohort based on the month of their first completed order. This query counts distinct customers per cohort month to establish baseline cohort sizes – the foundation for all downstream retention analysis.

SQL Query

SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS new_customers
FROM (
  SELECT
    user_id,
    DATE(DATE_TRUNC(MIN(created_at), MONTH)) AS cohort_month
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
  GROUP BY user_id
)
GROUP BY cohort_month
ORDER BY cohort_month

Results

The query returned 72 monthly cohorts spanning January 2019 through December 2024, totaling 14,997 unique customers. The table below highlights cohort sizes at the start and end of each year, showing consistent acquisition growth over the analysis window.

Full results: cohort-analysis-query-0-results.csv

Insights

• Acquisition grew from 2 customers in January 2019 to 550 in December 2024 – a 275x increase reflecting strong, sustained business growth.
• Year-over-year acceleration is clear: 2019 averaged ~26 new customers/month, while 2024 averaged ~458 – with no signs of plateauing.
• December consistently produces the largest cohorts each year (47 → 107 → 168 → 301 → 430 → 550), suggesting seasonal acquisition spikes worth leveraging.

Business Recommendations

• Monitor acquisition trends: the current trajectory shows healthy acceleration – track cohort sizes monthly to detect any growth slowdowns early.
• Correlate with spend: overlay acquisition costs per cohort to understand whether the 275x growth in volume is cost-efficient.
• Capitalize on seasonality: December cohorts are consistently the largest – consider aligning campaigns to amplify this pattern.

Business Question

What percentage of each monthly cohort returns to make a purchase in subsequent months?

Method

This is the core retention analysis. Four CTEs work together: (1) assign each customer to their cohort month, (2) capture every distinct month each customer was active, (3) join cohorts to activity and calculate the period number (months since first purchase), and (4) compute cohort sizes. The final output is a full retention matrix with retention percentage for every cohort-period combination.

SQL Query

WITH customer_cohorts AS (
  -- CTE 1: Get each customer's cohort month (first order month)
  SELECT
    user_id,
    DATE(DATE_TRUNC(MIN(created_at), MONTH)) AS cohort_month
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
  GROUP BY user_id
),

monthly_activity AS (
  -- CTE 2: Get every distinct month each customer placed an order
  SELECT DISTINCT
    user_id,
    DATE(DATE_TRUNC(created_at, MONTH)) AS activity_month
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
),

retention_data AS (
  -- CTE 3: Join cohorts to activity, calculate period number
  SELECT
    c.cohort_month,
    DATE_DIFF(DATE(a.activity_month), DATE(c.cohort_month), MONTH) AS period_number,
    COUNT(DISTINCT c.user_id) AS active_users
  FROM customer_cohorts c
  JOIN monthly_activity a
    ON c.user_id = a.user_id
  GROUP BY c.cohort_month, period_number
),

cohort_sizes AS (
  -- Get total customers per cohort for retention % calculation
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM customer_cohorts
  GROUP BY cohort_month
)

-- Final query: retention matrix
SELECT
  r.cohort_month,
  r.period_number,
  r.active_users,
  s.cohort_size,
  ROUND(r.active_users / s.cohort_size * 100, 2) AS retention_pct
FROM retention_data r
JOIN cohort_sizes s
  ON r.cohort_month = s.cohort_month
WHERE r.period_number >= 0
ORDER BY r.cohort_month, r.period_number

Results

The query produced 937 cohort-period pairs across 72 monthly cohorts. The sample below shows the July 2024 cohort (464 customers) – one of the largest cohorts with enough elapsed time to observe early retention patterns.

Full results: cohort-analysis-query-1-results.csv

Insights

• Retention drops dramatically after Period 0 – the July 2024 cohort falls from 100% to just 1.29% at Period 1, and this pattern is consistent across all cohorts.
• The retention matrix is sparse: most customers make a single purchase and do not return in consecutive months. When they do return, it's often months or years later.
• Larger recent cohorts (2024) show slightly higher absolute return counts but retention percentages remain consistently below 2%, confirming this is a product-wide pattern rather than a cohort-specific issue.

Business Recommendations

• Focus on Month 1 activation: with Period 1 retention below 2% across all cohorts, post-purchase engagement (emails, incentives, reminders) is the highest-leverage investment.
• Benchmark cohorts: set retention targets per period and flag cohorts that underperform for root-cause investigation.
• Track improvement over time: newer cohorts retaining better than older ones would signal that product or CX investments are working – the current data shows this remains a challenge.

Business Question

How does cohort revenue evolve over time relative to the first month, and are retained customers spending more or less?

Method

This query extends retention analysis from headcount to revenue. For each cohort-period, it calculates total revenue and compares it to the cohort's Period 0 (baseline) revenue. Revenue retention above 100% means retained customers are spending more per period than the original cohort did in their first month.

SQL Query

WITH customer_cohorts AS (
  SELECT
    user_id,
    DATE(DATE_TRUNC(MIN(created_at), MONTH)) AS cohort_month
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
  GROUP BY user_id
),

monthly_revenue AS (
  SELECT
    oi.user_id,
    DATE(DATE_TRUNC(oi.created_at, MONTH)) AS activity_month,
    SUM(oi.sale_price) AS monthly_revenue
  FROM `bigquery-public-data.thelook_ecommerce.order_items` oi
  JOIN `bigquery-public-data.thelook_ecommerce.orders` o
    ON oi.order_id = o.order_id
  WHERE o.status = 'Complete'
    AND o.created_at < '2025-01-01'
  GROUP BY oi.user_id, activity_month
),

revenue_by_period AS (
  SELECT
    c.cohort_month,
    DATE_DIFF(r.activity_month, c.cohort_month, MONTH) AS period_number,
    SUM(r.monthly_revenue) AS period_revenue
  FROM customer_cohorts c
  JOIN monthly_revenue r
    ON c.user_id = r.user_id
  GROUP BY c.cohort_month, period_number
),

period_zero_revenue AS (
  SELECT
    cohort_month,
    period_revenue AS baseline_revenue
  FROM revenue_by_period
  WHERE period_number = 0
)

SELECT
  r.cohort_month,
  r.period_number,
  ROUND(r.period_revenue, 2) AS period_revenue,
  ROUND(p.baseline_revenue, 2) AS baseline_revenue,
  ROUND(r.period_revenue / p.baseline_revenue * 100, 2) AS revenue_retention_pct
FROM revenue_by_period r
JOIN period_zero_revenue p
  ON r.cohort_month = p.cohort_month
WHERE r.period_number >= 0
ORDER BY r.cohort_month, r.period_number

Results

The query produced 918 cohort-period pairs with revenue retention metrics. Baseline revenue scales with cohort size – from $83 (January 2019) to $47,454 (December 2024). The sample below shows the July 2024 cohort.

Full results: cohort-analysis-query-2-results.csv

Insights

• Period 1 revenue retention typically ranges from 2–6% – slightly higher than customer retention (~1–2%), suggesting that returning customers tend to place moderately larger orders.
• Some cohorts show sporadic high-revenue returns in later periods (e.g., September 2020's Period 51 at 8.41%), indicating occasional large purchases by long-dormant customers.
• Baseline revenue grew from $83 (January 2019) to $47,454 (December 2024), reflecting both larger cohorts and higher average order values over time.

Business Recommendations

• Separate volume from value: revenue retention consistently outpaces customer retention, meaning retained customers are disproportionately valuable – track both metrics side by side.
• Invest in retained customers: since returning customers spend more than average, loyalty programs and personalized recommendations should generate outsized returns.
• Identify revenue decay: cohorts where revenue drops faster than headcount may signal pricing pressure or reduced engagement – flag these for investigation.

Business Question

Which acquisition channels produce the most durable customers, and how does retention vary by traffic source?

Method

This query joins the users table to bring in traffic_source as the acquisition channel dimension. Instead of grouping by cohort month, retention is calculated per channel across all cohorts – revealing which channels produce customers with the best long-term retention.

SQL Query

WITH customer_cohorts AS (
  SELECT
    o.user_id,
    u.traffic_source,
    DATE(DATE_TRUNC(MIN(o.created_at), MONTH)) AS cohort_month
  FROM `bigquery-public-data.thelook_ecommerce.orders` o
  JOIN `bigquery-public-data.thelook_ecommerce.users` u
    ON o.user_id = u.id
  WHERE o.status = 'Complete'
    AND o.created_at < '2025-01-01'
  GROUP BY o.user_id, u.traffic_source
),

monthly_activity AS (
  SELECT DISTINCT
    user_id,
    DATE(DATE_TRUNC(created_at, MONTH)) AS activity_month
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
),

retention_data AS (
  SELECT
    c.traffic_source,
    DATE_DIFF(a.activity_month, c.cohort_month, MONTH) AS period_number,
    COUNT(DISTINCT c.user_id) AS active_users
  FROM customer_cohorts c
  JOIN monthly_activity a
    ON c.user_id = a.user_id
  GROUP BY c.traffic_source, period_number
),

channel_sizes AS (
  SELECT
    traffic_source,
    COUNT(DISTINCT user_id) AS channel_size
  FROM customer_cohorts
  GROUP BY traffic_source
)

SELECT
  r.traffic_source,
  r.period_number,
  r.active_users,
  s.channel_size,
  ROUND(r.active_users / s.channel_size * 100, 2) AS retention_pct
FROM retention_data r
JOIN channel_sizes s
  ON r.traffic_source = s.traffic_source
WHERE r.period_number >= 0
ORDER BY r.traffic_source, r.period_number

Results

The query produced 204 channel-period pairs across 5 acquisition channels. Search dominates acquisition with 70% of all customers. The table below compares each channel's size and early-period retention rates.

Full results: cohort-analysis-query-3-results.csv

Insights

• Search dominates acquisition with 10,523 customers (70% of total), followed by Organic (2,258), Facebook (860), Email (751), and Display (605).
• Facebook shows the highest Period 1 retention (0.70%) despite smaller volume, suggesting these customers may have higher initial engagement.
• All channels show very low post-acquisition retention (<1%), indicating that churn is a product-wide challenge rather than channel-specific – no single channel materially outperforms the others.

Business Recommendations

• Shift budget toward durable channels: factor retention-adjusted LTV into CAC calculations – Facebook's higher early retention may justify higher acquisition costs despite smaller volume.
• Improve onboarding across all channels: since no channel exceeds 1% retention at Period 1, post-purchase engagement is a universal opportunity rather than a channel-specific one.
• Set channel-specific targets: benchmark each channel's retention curve and flag underperformance relative to its baseline.

Business Question

What proportion of customers are currently Active, At-Risk, or Churned – and how many have reactivated after a long gap?

Method

This analysis takes a snapshot approach. Using recency (days since last order as of 2024-12-31), each customer is classified into a lifecycle segment: Active (≤90 days), At-Risk (91–180 days), or Churned (>180 days). A separate CTE detects reactivations – customers who returned after a gap of 90+ days between consecutive orders.

SQL Query – Lifecycle Segments

WITH customer_activity AS (
  SELECT
    user_id,
    DATE(MIN(created_at)) AS first_order_date,
    DATE(MAX(created_at)) AS last_order_date,
    COUNT(DISTINCT order_id) AS total_orders,
    DATE_DIFF(
      DATE('2024-12-31'),
      DATE(MAX(created_at)),
      DAY
    ) AS days_since_last_order
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
  GROUP BY user_id
),

customer_segments AS (
  SELECT
    user_id,
    first_order_date,
    last_order_date,
    total_orders,
    days_since_last_order,
    CASE
      WHEN days_since_last_order <= 90 THEN 'Active'
      WHEN days_since_last_order <= 180 THEN 'At-Risk'
      ELSE 'Churned'
    END AS customer_status
  FROM customer_activity
),

-- Bonus: detect reactivations (customers who returned after a 90+ day gap)
order_gaps AS (
  SELECT
    user_id,
    DATE(created_at) AS order_date,
    LAG(DATE(created_at)) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_date,
    DATE_DIFF(
      DATE(created_at),
      LAG(DATE(created_at)) OVER (PARTITION BY user_id ORDER BY created_at),
      DAY
    ) AS days_between_orders
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
),

reactivations AS (
  SELECT
    COUNT(DISTINCT user_id) AS reactivated_customers
  FROM order_gaps
  WHERE days_between_orders > 90
)

-- Main output: segment counts
SELECT
  customer_status,
  COUNT(*) AS customer_count,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct_of_total,
  ROUND(AVG(total_orders), 2) AS avg_orders,
  ROUND(AVG(days_since_last_order), 0) AS avg_days_since_last_order
FROM customer_segments
GROUP BY customer_status
ORDER BY customer_count DESC

SQL Query – Reactivated Customers

SELECT
  COUNT(DISTINCT user_id) AS reactivated_customers
FROM (
  SELECT
    user_id,
    DATE_DIFF(
      DATE(created_at),
      LAG(DATE(created_at)) OVER (PARTITION BY user_id ORDER BY created_at),
      DAY
    ) AS days_between_orders
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
)
WHERE days_between_orders > 90

Results

The lifecycle segmentation query classified all 14,997 customers into three segments as of December 31, 2024. The reactivation query identified customers who returned after a 90+ day gap.

Lifecycle Segments

Reactivated Customers

1,015 customers reactivated after a gap of 90+ days between orders – approximately 6.8% of the total customer base.

Full results: cohort-analysis-query-4a-results.csv | cohort-analysis-query-4b-results.csv

Insights

• 78% of customers are Churned (11,697) with an average of 755 days since their last order and only 1.08 average orders – the vast majority never return after their first purchase.
• 11.81% are Active (1,771 customers) with an average of 44 days since their last order, representing the current engaged base that drives ongoing revenue.
• 1,015 customers (6.8%) reactivated after a 90+ day gap, proving that churn is not always permanent – win-back campaigns have a proven, viable audience.

Business Recommendations

• Prioritize the 1,529 At-Risk customers: with an average of 134 days since last order, these customers are still recoverable – deploy targeted re-engagement campaigns before they cross into Churned.
• Build win-back flows: 1,015 customers already reactivated organically – structured campaigns should significantly improve this rate.
• Track segment movement: monitor shifts between Active, At-Risk, and Churned monthly to detect systemic changes in retention health.

Business Question

How does cumulative revenue per customer grow over time by cohort, and which cohorts generate the highest long-term value?

Method

This query calculates period-level revenue per cohort, then uses a window function (SUM() OVER) to compute a running cumulative total. Dividing cumulative revenue by cohort size produces cumulative revenue per customer – a proxy for lifetime value (LTV) at each period.

SQL Query

WITH customer_cohorts AS (
  SELECT
    user_id,
    DATE(DATE_TRUNC(MIN(created_at), MONTH)) AS cohort_month
  FROM `bigquery-public-data.thelook_ecommerce.orders`
  WHERE status = 'Complete'
    AND created_at < '2025-01-01'
  GROUP BY user_id
),

cohort_sizes AS (
  SELECT
    cohort_month,
    COUNT(DISTINCT user_id) AS cohort_size
  FROM customer_cohorts
  GROUP BY cohort_month
),

monthly_revenue AS (
  SELECT
    c.cohort_month,
    DATE_DIFF(DATE(DATE_TRUNC(oi.created_at, MONTH)), c.cohort_month, MONTH) AS period_number,
    SUM(oi.sale_price) AS period_revenue
  FROM customer_cohorts c
  JOIN `bigquery-public-data.thelook_ecommerce.order_items` oi
    ON c.user_id = oi.user_id
  JOIN `bigquery-public-data.thelook_ecommerce.orders` o
    ON oi.order_id = o.order_id
  WHERE o.status = 'Complete'
    AND o.created_at < '2025-01-01'
  GROUP BY c.cohort_month, period_number
)

SELECT
  r.cohort_month,
  r.period_number,
  ROUND(r.period_revenue, 2) AS period_revenue,
  ROUND(
    SUM(r.period_revenue) OVER (
      PARTITION BY r.cohort_month
      ORDER BY r.period_number
    ), 2
  ) AS cumulative_revenue,
  s.cohort_size,
  ROUND(
    SUM(r.period_revenue) OVER (
      PARTITION BY r.cohort_month
      ORDER BY r.period_number
    ) / s.cohort_size, 2
  ) AS cumulative_revenue_per_customer
FROM monthly_revenue r
JOIN cohort_sizes s
  ON r.cohort_month = s.cohort_month
WHERE r.period_number >= 0
ORDER BY r.cohort_month, r.period_number

Results

The query produced 918 cohort-period pairs tracking cumulative revenue and per-customer LTV over time. The sample below shows the July 2024 cohort's LTV progression over its first 6 months.

Full results: cohort-analysis-query-5-results.csv

Insights

• Cumulative revenue per customer starts between $77–$95 at Period 0 for most cohorts, with the bulk of lifetime value captured in the first purchase.
• The LTV curve is shallow – mature cohorts (e.g., March 2022) reach ~$105 per customer over 30 periods, suggesting a long-term LTV ceiling around $100–$110 for this dataset.
• Newer cohorts start with higher Period 0 revenue per customer ($80–$90 in 2024 vs. $40–$70 in 2019), reflecting either higher AOV or a shift in product mix over time.

Business Recommendations

• Set CAC limits from LTV: with LTV plateauing around $100–$110, acquisition costs should stay well below this threshold to maintain unit economics.
• Compare cohort trajectories: newer cohorts tracking below older ones at the same period would indicate a retention or monetization problem – monitor this quarterly.
• Forecast revenue: use the mature cohort LTV curve shape to project revenue from recent, larger cohorts and inform growth planning.

Interactive Looker Studio Dashboard

The dashboard below connects directly to the BigQuery query results and provides interactive exploration of all retention analyses. Use the filters and controls to drill into specific cohorts, time periods, and acquisition channels.

Open dashboard in Looker Studio →

This project demonstrates a complete cohort retention analysis pipeline – from SQL query design in BigQuery to an interactive Looker Studio dashboard. Across six analyses, I built retention matrices, measured revenue retention, compared acquisition channels, segmented customer lifecycles, and tracked cumulative lifetime value.

Key Takeaways

• Early churn is the biggest lever: across all 72 cohorts and 14,997 customers, Period 1 retention consistently falls below 2% – onboarding and first-repeat activation deserve the most investment.
• Revenue retention tells a different story than headcount: Period 1 revenue retention (2–6%) consistently outpaces customer retention (~1–2%), meaning returning customers spend more and customer retention alone understates the value of loyalty.
• Channel quality varies but churn is universal: Search drives 70% of acquisition (10,523 customers), but no channel exceeds 1% retention at Period 1 – improving post-purchase engagement is a product-wide opportunity.
• Lifecycle segmentation is actionable: 78% of customers are Churned, but 1,529 At-Risk customers and 1,015 proven reactivations demonstrate clear opportunities for targeted win-back campaigns.
• LTV curves drive strategy: cumulative revenue per customer plateaus around $100–$110 for mature cohorts, providing a clear ceiling for acquisition cost planning.

Together, these analyses show how SQL-based cohort analysis paired with a self-service dashboard transforms raw transactional data into retention insights that support marketing, product, and growth decisions.