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.
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.
To calculate the conversion rates as ratios, use:
ROUND(COUNT(FirstOrderId) / CAST(COUNT(*) AS float), 3)
