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

Instruction

Good! Now let's dig into some customer activity analysis. The number of active customers is an important metric in any business, especially in businesses offering their products or services online. Sometimes it's easy to say who is an active customer: for a subscription service someone with an active subscription is an active user, for a mobile app someone who has the app installed and uses it once a week is an active user, etc.

For an e-store the definition of an "active customer" is not as straightforward. There are some customers who place orders every now and then, but there are also some who haven't been active for a long time. In our online supermarket, regular customers typically place one order a week, but we'll define "active customers" as all customers who've placed an order within the last 30 days. Let's count how many active customers we have:

SELECT COUNT(*) AS ActiveCustomers
FROM Customers
WHERE DATEDIFF(Day, LastOrderDate, GETDATE()) < 30;

In the WHERE clause, we used the DATEDIFF(TimeUnit, StartDate, EndDate) function. Inside, we used GETDATE() – which will return the current date – as the last argument. This lets us check if the given customer's most recent order was within the last 30 days.

Exercise

Find the number of active customers in each country. Show two columns: Country and ActiveCustomers. Do you see any major differences between countries?

Stuck? Here's a hint!