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

Instruction

Well done! We can also introduce more features into the previous report. We'll use these features to analyze if good customers have anything in common (other than high average order values). Check it out:

WITH GoodCustomers AS (
  SELECT
    C.CustomerID,
    RegistrationDate,
    C.ChannelID,
    AVG(TotalAmount) AS AvgOrderValue
  FROM Customers C
  JOIN Orders O
    ON C.CustomerID = O.CustomerID
  GROUP BY C.CustomerID, RegistrationDate, C.ChannelID
  HAVING AVG(TotalAmount) > 1636
)

SELECT 
  DATEPART(Year, RegistrationDate) AS Year, 
  DATEPART(Week, RegistrationDate) AS Week,
  ChannelID,
  COUNT(*) AS GoodCustomers
FROM GoodCustomers
GROUP BY
  DATEPART(Year, RegistrationDate), 
  DATEPART(Week, RegistrationDate),
  ChannelID
ORDER BY
  DATEPART(Year, RegistrationDate), 
  DATEPART(Week, RegistrationDate);

In the query above, we've added a new customer field in the CTE: the acquisition channel. We used the ChannelId column from the inner query and showed it in the outer query to get additional information about customers.

This way we can check if any channels are particularly good at attracting good customers. If we identify good channels, we can put more marketing efforts into these channels to attract even more good customers.

Exercise

We want to find out if there are any countries that are particularly good. The template code contains the query from the previous exercise. It shows the columns Year, Quarter, and GoodCustomers. Extend the report so that it contains four columns: Year, Quarter, Country, and GoodCustomers. Order the results by year and quarter.

Stuck? Here's a hint!

You will first need to add the Country column in the CTE before you can use it in the outer query.