End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer cohorts
12. Customer cohort report
Summary

Instruction

Good. We now want to create a report which will count registration values in annual customer cohorts based on the channel. To that end, we can use the following query:

SELECT
  DATEPART(year, RegistrationDate) AS RegistrationYear,
  ChannelName,
  COUNT(*) AS RegistrationCount
FROM Customers Cu
JOIN Channels Ch
  ON Cu.ChannelId = Ch.Id
GROUP BY DATEPART(year, RegistrationDate), ChannelId, ChannelName
ORDER BY DATEPART(year, RegistrationDate);

Result:

RegistrationYear ChannelName RegistrationCount
2016 Organic Search 48
2016 Direct 8
2016 Referral 4
...

The Customers table contains the channel ID for each user, but we join it with the Channels table to get channel names for the report. Note that we only show channel names in the report, but we group by both the channel IDs and channel names. If two channels with different IDs had the same name (for whatever reason), this trick would prevent us from summing up their values improperly.

Exercise

Create an extended version of the report shown in the explanation. Instead of annual customer cohorts per channel, show weekly customer cohorts per channel in each year.

Show the following columns: RegistrationYear, RegistrationWeek, ChannelName, and RegistrationCount.

Order the results by year and week.

Stuck? Here's a hint!

Create the second column in the following way:

DATEPART(week, RegistrationDate) As RegistrationWeek