Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Customer Behavior Analysis in SQL Server

Introduction
Conversion rates
Time to first order
Conversion charts
16. Conversion rate report – exercise
Summary

Instruction

Nice work! Time for an additional exercise.

Exercise

Create a conversion chart for monthly registration cohorts from 2017 for each channel. Show the following columns:

  • Month – The registration month.
  • ChannelName – The registration channel.
  • RegisteredCount – The number of users registered.
  • NoSale – The number of users who never made a purchase.
  • OneWeek – The number of users who made a purchase within 7 days.
  • AfterOneWeek – The numbers of users who made a purchase after the first week.

Order the results by month.

Stuck? Here's a hint!

  1. Use DATEPART(Month, RegistrationDate) to get the month.
  2. Join the Customers table with the Channels table to get channel names.
  3. Group by month, channel ID, and channel name.
  4. Use the following code to calculate the OneWeek column: 
    COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) < 7 THEN CustomerID END) AS OneWeek