Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Conversion rates
Time to first order
Conversion charts
Summary
17. Summary

## Instruction

Well done! It's time to wrap things up. Let's make a quick summary of what we've learned:

1. Conversion rate is the count of customers that performed a specific desired action divided by the count of all customers.
2. To calculate the global lifetime conversion (all customers who ever placed an order), use:
SELECT
ROUND(COUNT(FirstOrderId) / CAST(COUNT(*) AS FLOAT), 2) AS ConversionRate
FROM Customers;

3. To show conversion rates in weekly registration cohorts, use the DATEPART() function:
SELECT
DATEPART(Year, RegistrationDate) AS Year,
DATEPART(Week, RegistrationDate) AS Week,
ROUND(COUNT(FirstOrderId) * 100 / COUNT(*), 2) AS ConversionRate
FROM Customers
GROUP BY
DATEPART(Year, RegistrationDate),
DATEPART(Week, RegistrationDate)
ORDER BY
DATEPART(Year, RegistrationDate),
DATEPART(Week, RegistrationDate);

4. To calculate the time from registration to first order, use:
SELECT
CustomerID,
DATEDIFF(Day, RegistrationDate, FirstOrderDate) AS DaysToFirstOrder
FROM Customers;

5. To create a conversion chart, use multiple COUNT(CASE WHEN...) instances:
SELECT
DATEPART(Year, RegistrationDate) AS Year,
DATEPART(Week, RegistrationDate) AS Week,
...
COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) < 7 THEN CustomerID END) AS OneWeek,
COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) >= 7 AND DATEDIFF(Day, RegistrationDate, FirstOrderDate) < 14 THEN CustomerID END) AS TwoWeeks,
...
FROM Customers
GROUP BY
DATEPART(Year, RegistrationDate),
DATEPART(Week, RegistrationDate)
ORDER BY
DATEPART(Year, RegistrationDate),
DATEPART(Week, RegistrationDate);


All right! How about a short quiz?

## Exercise

Click to continue.