End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Conversion rates
7. Conversion rates in weekly cohorts
Time to first order
Conversion charts


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:

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


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.


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)