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 (
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
DATEPART(Year, RegistrationDate) AS Year,
DATEPART(Week, RegistrationDate) AS Week,
COUNT(*) AS GoodCustomers
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.