Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Conversion rates
Time to first order
Conversion charts
16. Conversion rate report – exercise


Nice work! Time for an additional exercise.


Create a conversion chart for monthly registration cohorts from 2017 for each channel. Show the following columns:

  • Month – The registration month.
  • ChannelName – The registration channel.
  • RegisteredCount – The number of users registered.
  • NoSale – The number of users who never made a purchase.
  • OneWeek – The number of users who made a purchase within 7 days.
  • AfterOneWeek – The numbers of users who made a purchase after the first week.

Order the results by month.

Stuck? Here's a hint!

  1. Use DATEPART(Month, RegistrationDate) to get the month.
  2. Join the Customers table with the Channels table to get channel names.
  3. Group by month, channel ID, and channel name.
  4. Use the following code to calculate the OneWeek column:
    COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) < 7 THEN CustomerID END) AS OneWeek