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 Server

Introduction
Conversion rates
Time to first order
Conversion charts
15. 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 one week, two weeks, etc. We want the end report to look like this:

Year Week RegisteredCount NoSale OneWeek TwoWeeks OverTwoWeeks
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
  DATEPART(Year, RegistrationDate) AS Year,
  DATEPART(Week, RegistrationDate) AS Week,
  COUNT(*) AS RegisteredCount,
  COUNT(CASE WHEN FirstOrderId IS NULL THEN CustomerID END) AS NoSale,
  COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) < 7 THEN CustomerID END) AS OneWeek,
  COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) >= 7
              AND DATEDIFF(Day, RegistrationDate, FirstOrderDate) <  14 THEN CustomerID END) AS TwoWeeks,
  COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) >= 14 THEN CustomerID END) AS OverTwoWeeks
FROM Customers
GROUP BY 
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate)
ORDER BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate);

As usual, we used DATEPART() 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
  • RegisteredCount
  • NoSale
  • ThreeDays – the number of customers who made a purchase within 3 days from registration.
  • FirstWeek – the number of customers who made a purchase during the first week but not within first three days.
  • AfterFirstWeek – the number of customers who made a purchase after the 7th day.

Order the results by year and month.

Stuck? Here's a hint!

To calculate the ThreeDays column, use

COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) < 3 THEN CustomerID END) AS ThreeDays

because the first three days are those for which DATEDIFF() returns 0, 1 or 2.