End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Conversion rates
Time to first order
Conversion charts
13. Custom classifications
Summary

Instruction

Excellent! To create the last report type in this part, we'll first need to learn how to create our own classifications.

Suppose we want to show each customer along with a custom category label. The label will indicate how long they took to make their first order.

SELECT
  CustomerID,
  RegistrationDate,
  FirstOrderDate,
  CASE
    WHEN FirstOrderDate IS NULL THEN 'no order'
    WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) <= 7 THEN '1 week'
    WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) <= 14 THEN '2 weeks'
    ELSE 'more than 2 weeks' 
  END AS TimeToFirstOrder    
FROM Customers;

Result:

CustomerID RegistrationDate FirstOrderDate TimeToFirstOrder
1 2017-08-25 2017-08-25 1 week
... ... ... ...
8 2016-09-30 2016-10-10 2 weeks
9 2016-10-16 2016-10-16 1 week
... ... ... ...
22 2017-01-01 NULL no order
... ... ... ...

The new part in this query is the last column named TimeToFirstOrder. It uses a SQL concept known as CASE WHEN. It checks the expressions introduced after each WHEN keyword. When it finds the first true expression, the corresponding value in the THEN part is assigned to the specified field.

In our query, we first check if the FirstOrderDate column is NULL. If it is, we set the TimeToFirstOrder value to 'no order'. If it isn't, we check if the period between registration and first purchase is equal to or less than 7 days. If it is, we set the TimeToFirstOrder value to '1 week'. If it's not, we check the same period for equal to or less than 14 days (the value '2 weeks'). If this expression doesn't match either, we use the default value from the ELSE clause ('more than 2 weeks'). Note that each CASE WHEN expression must end with the word END.

Exercise

Our e-store has used three versions of the registration form:

  1. 'ver1' – introduced when the e-store started.
  2. 'ver2' – introduced on March 14, 2017.
  3. 'ver3' – introduced on January 1, 2018.

For each customer, select the CustomerId, RegistrationDate, and the form version the user filled in at the time of registration. Name this third column RegistrationForm.

Stuck? Here's a hint!

Use the following construction:

CASE
  WHEN RegistrationDate < '20170314' THEN 'ver1'
  WHEN RegistrationDate < '20180101' THEN 'ver2'
  ELSE 'ver3' 
END