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
7. Conversion rates in weekly cohorts
Time to first order
Conversion charts
Summary

Instruction

Very well done! Global lifetime conversion rate is a useful indicator, but now we'd like to run a deeper analysis: we'd like to find the conversion rate for weekly registration cohorts. This way, we can analyze which weekly cohorts had the best conversion rates and compare them against the campaigns we ran at that time. Take a look:

SELECT
  DATEPART(Year, RegistrationDate) AS Year,
  DATEPART(Week, RegistrationDate) AS Week,
  ROUND(COUNT(FirstOrderId) * 100.0 / COUNT(*), 2) AS ConversionRate
FROM Customers
GROUP BY 
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate)
ORDER BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate);

Result:

Year Week ConversionRate
... ... ...
2017 2 83.33
2017 3 85.71
2017 4 66.67
... ... ...

We used the DATEPART(Period, Column) function twice to extract the year and the week from the RegistrationDate column. We included the year and the week in three places: the SELECT, GROUP BY, and ORDER BY clauses. This way, we can compare conversion rates for all weeks in chronological order.

Exercise

Create a similar report showing conversion rates in monthly cohorts. Display the conversion rates as ratios rounded to three decimal places. Show the following columns: Year, Month, and ConversionRate. Order the results by year and month.

Stuck? Here's a hint!

To calculate the conversion rates as ratios, use:

ROUND(COUNT(FirstOrderId) / CAST(COUNT(*) AS float), 3)