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
Customer registration
Customer cohorts
Summary

Instruction

Let's start with customer acquisition. We'll begin by counting how many new registrations occurred in a given period. By observing how the registration count changes over time, we'll be able to assess whether our promotional campaigns have worked or not.

We'll discuss the following reports:

  • The number of registrations in the given period.
  • The number of registrations in the current period of time (current week, current month, etc.).
  • The number of registrations over time, tallying registrations in each month or in each week.

To count the number of registrations in 2016, we can use the following query:

SELECT COUNT(CustomerID) AS RegistrationCount2016
FROM Customers
WHERE RegistrationDate >= '20160101'
  AND RegistrationDate <  '20170101';

The query is quite simple. Just remember proper time filtering: use the 'yyyymmdd' format (year, month, day) when you write the dates.

Exercise

How many customers registered in the first six months of 2017? Name the column RegistrationCount.

Stuck? Here's a hint!

To get the first six months of 2017, use the following WHERE clause:

WHERE RegistrationDate >= '20170101'
  AND RegistrationDate <  '20170701';