Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Customer Behavior Analysis in SQL Server

Introduction
Conversion rates
3. Global lifetime conversion rate – step 1
Time to first order
Conversion charts
Summary

Instruction

Good job! Let's say we want to calculate the global lifetime conversion rate. In other words, we want to know the percentage of registered customers who've made at least one purchase. This is a good indicator of the general condition of our business.

To calculate the rate, we first need to find the total number of customers and the number of customers who've made at least one purchase. That's quite straightforward:

SELECT
  COUNT(FirstOrderId) AS CustomersWithPurchase,
  COUNT(*) AS AllCustomers
FROM Customers;

Recall that COUNT(*) counts all rows in a table (in this case, all the customers), while COUNT(Column) only counts non-NULL values in the specified column. Customers who didn't make any purchases have NULL FirstOrderId values, so they won't be added to the CustomersWithPurchase column.

Exercise

Among customers registered in 2017, show how many made at least one purchase (name the column CustomersWithPurchase) and the number of all the customers registered in 2017 (name the column AllCustomers).

Stuck? Here's a hint!

Use the query from the explanation. Add the following clause:

WHERE RegistrationDate >= '20170101'
  AND RegistrationDate < '20180101';