End of Summer - 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 Next exercise to continue.