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
18. Summary

Instruction

Well done! It's time to wrap things up. Let's do 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 ratio of all customers who ever placed an order to all registered customers, use:
    SELECT
      ROUND(COUNT(first_order_id) / CAST(COUNT(*) AS float), 2) AS conversion_rate
    FROM customers;
    
  3. To show conversion rates (as percentages) in weekly registration cohorts, use the DATE_PART() function:
    SELECT
      DATE_PART('year', registration_date) AS year,
      DATE_PART('week', registration_date) AS week,
      ROUND(COUNT(first_order_id) * 100.0 / COUNT(*), 2) AS conversion_rate
    FROM customers
    GROUP BY
      DATE_PART('year', registration_date),
      DATE_PART('week', registration_date)
    ORDER BY
      DATE_PART('year', registration_date),
      DATE_PART('week', registration_date);
    
  4. To calculate the time from registration to first order, use:
    SELECT
      customer_id,
      first_order_date - registration_date AS days_to_first_order
    FROM customers;
    
  5. To create a conversion chart, use multiple COUNT(CASE WHEN...) instances:
    SELECT
      DATE_PART('year', registration_date) AS year,
      DATE_PART('week', registration_date) AS week,
      ...
      COUNT(CASE
        WHEN first_order_date - registration_date <  INTERVAL '7' day
        THEN customer_id
        END) AS one_week,
      COUNT(CASE
        WHEN first_order_date - registration_date >= INTERVAL '7' day
         AND first_order_date - registration_date <  INTERVAL '14' day
        THEN customer_id
        END) AS two_weeks,
      ...
    FROM customers
    GROUP BY
      DATE_PART('year', registration_date),
      DATE_PART('week', registration_date)
    ORDER BY
      DATE_PART('year', registration_date),
      DATE_PART('week', registration_date);
    

All right! How about a short quiz?

Exercise

Click Next exercise to continue.