Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
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(first_order_id) AS customers_with_purchase,
  COUNT(*) AS all_customers
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 first_order_id values, so they won't be added to the customers_with_purchase column.

Exercise

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

Stuck? Here's a hint!

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

WHERE registration_date >= '2017-01-01'
  AND registration_date <  '2018-01-01';