End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
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
  DATE_PART('year', registration_date) AS year,
  DATE_PART('week', registration_date) AS week,
  ROUND(COUNT(first_order_id) * 100.0 / COUNT(*), 2) AS conversion_rate
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);

Result:

year week conversion_rate
... ... ...
2017 2 83.33
2017 3 85.71
2017 4 66.67
... ... ...

We used the DATE_PART('period', column) function twice to extract the year and the week from the registration_date 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 (not percentages) rounded to three decimal places. Show the following columns: year, month, and conversion_rate. Order the results by year and month.

Stuck? Here's a hint!

To calculate the conversion rates as ratios, use:

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