End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer activity
5. Number of active customers in time-period cohorts
Finding good customers
Summary

Instruction

Perfect! The general number of active customers is a good start, but now we'd like to see how many active customers there are in each weekly registration cohort. We can then compare these numbers against the registration campaigns we ran each week and see which marketing activities yielded the greatest number of active customers. Take a look:

SELECT
  DATE_PART('year', registration_date) AS year,
  DATE_PART('week', registration_date) AS week,
  COUNT(*) AS active_customers
FROM customers
WHERE CURRENT_DATE - last_order_date < INTERVAL '30' day
GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date)
ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);

Compared to the previous report, we added two DATE_PART() invocations (to extract years and weeks) in three places each (in SELECT, GROUP BY, and ORDER BY clauses). This shows us the number of active customers in each cohort.

Exercise

Find the number of active customers (those who placed an order within the last 30 days) that registered in 2017. Use monthly registration cohorts and show two columns: month and active_customers. Order the rows by month.

Does any month stand out?

Stuck? Here's a hint!

Use:

DATE_PART('month', registration_date)