Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Conversion rates
Time to first order
11. Average time to first order in weekly cohorts
Conversion charts
Summary

Instruction

Very well done! Previously, we created a report showing conversion rates in weekly registration cohorts. In a similar way, we can create a report showing the average time to first order in weekly registration cohorts. Take a look:

SELECT
  DATEPART(Year, RegistrationDate) AS Year,
  DATEPART(Week, RegistrationDate) AS Week,
  AVG(1.0 * DATEDIFF(Day, RegistrationDate, FirstOrderDate)) AS AvgDaysToFirstOrder
FROM Customers
GROUP BY 
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate)
ORDER BY
  DATEPART(Year, RegistrationDate),
  DATEPART(Week, RegistrationDate);

Result:

Year Week AvgHoursToFirstOrder
2016 28 0
2016 29 19
2016 30 14
... ... ...

The report looks very similar to the previous one. Once again, we used DATEPART() to group and order the rows by weekly registration cohorts. The only difference is the AvgDaysToFirstOrder column.

Exercise

Calculate the average number of days that passed between registration and first order in quarterly registration cohorts. Show the following columns: Year, Quarter, and AvgDaysToFirstOrder. Order the results by year and quarter.

Stuck? Here's a hint!

You'll have to use:

DATEPART(Quarter, RegistrationDate)