Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Customer Behavior Analysis in SQL

Introduction
Conversion rates
Time to first order
Conversion charts
16. Conversion rate report
Summary

Instruction

Great! We now want to create a conversion chart. This report should show weekly registration cohorts in rows and the number of conversions (i.e., first purchases) within the first week, the second week, etc. We want the end report to look like this:

year week registered_count no_sale first_week second_week after_second_week
2016 7 20 0 20 0 0
2016 8 14 0 14 0 0
2016 9 10 0 9 1 0
... ... ... ... ... ... ...

Here's the query:

SELECT
  DATE_PART('year', registration_date) AS year,
  DATE_PART('week', registration_date) AS week,
  COUNT(*) AS registered_count,
  COUNT(CASE
    WHEN first_order_id IS NULL
    THEN customer_id
    END) AS no_sale,
  COUNT(CASE
    WHEN first_order_date - registration_date <  INTERVAL '7' day
    THEN customer_id
    END) AS first_week,
  COUNT(CASE
    WHEN first_order_date - registration_date >= INTERVAL '7' day
     AND first_order_date - registration_date <  INTERVAL '14' day
    THEN customer_id
    END) AS second_week,
  COUNT(CASE
    WHEN first_order_date - registration_date >= INTERVAL '14' day
    THEN customer_id
    END) AS after_second_week
FROM customers
GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date)
ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);

As usual, we used the DATE_PART() function to extract the year and week of registration. Note that we also used the COUNT(CASE WHEN...) technique – this way, we could include multiple metrics in a single report.

Exercise

Create a conversion chart for monthly registration cohorts. Show the following columns:

  • year
  • month
  • registered_count
  • no_sale
  • three_days – the number of customers who made a purchase within 3 days from registration.
  • first_week – the number of customers who made a purchase during the first week but not within the first three days.
  • after_first_week – the number of customers who made a purchase after the 7th day.

Order the results by year and month. Be careful the case of column names and spaces in them.

Stuck? Here's a hint!

To calculate the three_days column, use

COUNT(CASE WHEN first_order_date - registration_date < INTERVAL '3' day THEN customer_id END) AS three_days

because the first three days are those for which the interval equals to 0, 1 or 2 days.

Remember there is no "week" interval, so you need to use '7' day instead.

When grouping the results use DATE_PART('year', registration_date) and DATE_PART('month', registration_date).