# Churn Prediction[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#churn-prediction "Permalink to this headline")

Contents

* Outline

* Loading the data

* Defining the problem and creating the target

* Building a first model

* Deploying the model

* Going deeper into churn modeling

* Wrap-up

## Outline[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#outline "Permalink to this headline")

Churn prediction is one of the most well known applications of machine learning and data science in the Customer Relationship Management (CRM) and Marketing fields. Simply put, a **churner** is a user or customer that stops using a company’s products or services.

Churn applications are common in several sectors:

* Subscription business companies (think internet and telephone services providers): customers that are most likely to churn at the end of their subscription are contacted by a call center and offered a discount.

* E-commerce companies (think Amazon and the like): automatic e-mails are sent to customers that haven’t bought anything for a long time, but may respond to a promotional offer.

To read more about how Dataiku’s customers fight churn, feel free to consult our Churn and Lifetime Value success stories.

In this tutorial, you will learn how to use Dataiku DSS to create your own churn prediction model, based on your customer data. More precisely, you will learn how to:

* **Define churn** as a data science problem (i.e. create a variable or “target” to predict)

* **Create basic features** that will enable you to detect churn

* **Train your first model** and deploy it to predict future churn

* Learn how to **deal with time-dependent features** and modeling, a refined concept of machine learning and data science.

### Prerequisites[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#prerequisites "Permalink to this headline")

This is an *advanced tutorial* so if you are lost at some point, please refer to the documentation or to the Basics courses. We will rely a lot on a PostgreSQL database, so you will need to set up one and the proper DSS connection as well. Also, a basic knowledge of SQL is required.

## Loading the data[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#loading-the-data "Permalink to this headline")

We will work with a toy dataset emulating the data that an e-business company (and more precisely an internet market place) might have.

First, create a new project, then import the two main datasets into DSS:

* Events are a log of what happened on your website: what page users see, what product they like or purchase…

* Product is a look-up table containing a product id, and information about its category and price. Make sure to set *product\_id* column as bigint in the schema.

As with any data science project, the first step is to explore the data. Start by opening the **events** dataset: it contains a user id, a timestamp, an event type, a product id and a seller id. For instance, we may want to check the distribution of the event types. From the column header, just click on **Analyze**:

Not very surprisingly, the most common event type is viewing a product, followed by viewing a seller page (remember it is a market place e-business company). The purchase event is way less frequent with only 3.5 % of all records in the sample.

When looking at this dataset again, we can see that it contains a timestamp column which is not stored in a standard ISO format. To make it more suitable for analysis, we can quickly parse the **event\_timestamp** column (using the format yyyy-MM-dd HH:mm:ss) using a visual data preparation script. Make sure the resulting column is stored in the Date format. Once done, you can *deploy* the script and directly store the resulting dataset into PostgreSQL.

On the other hand, the **product** dataset contains an id, a hierarchy of categories attached to the product, and a price. You may want to check for instance the distribution of the **price** column, by clicking on the header and selecting **Analyze** in the drop-down menu. Again, load the dataset into PostgreSQL, this time using a **Sync** recipe.

Your flow should now look like this:

## Defining the problem and creating the target[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#defining-the-problem-and-creating-the-target "Permalink to this headline")

When building a churn prediction model, a critical step is to define churn for your particular problem, and determine how it can be translated into a *variable* that can be used in a machine learning model. The definition of churn is totally dependent on your business model and can differ widely from one company to another.

For this tutorial, we’ll use a simple definition: a churner is someone who hasn’t taken any action on the website for 4 months. We have to use a short time frame here, because our dataset contains a limited history (1 year and 3 months of data). Since many customers visit the website only once, we will focus on the “best” clients, those who generate most of the company revenue.

Our definition of churn can then be translated into the following rules:

* the *target* variable (i.e the one we want to model) is built by looking ahead over a 4 month period from a reference date, and flagging the customers with a “1” if they purchased a product on this time frame and “0” otherwise

* we’ll restrict our data to the customers who made at least one purchase in the previous 4 months, and total purchases at least 30 in value. This rule is to make sure we only look at the high value customers.

To create our first *training dataset*, we will first need to join the **Products\_psql** and **Events\_psql** datasets that have been synced to PostgreSQL. To do so, you can either create a new SQL recipe and write a join query yourself, or use a visual join recipe and let DSS do the work for you.

For this tutorial, we will use the visual **Join** recipe. From the Flow screen, left click on **Events\_psql**. From the right panel, choose the *Join with…* recipe. When prompted, choose **Products\_sql** as the second dataset, name the output **events\_complete**, and make sure it will be written in your PostgreSQL connection again. Run the recipe.

Note that you may have been able to convert the visual join recipe into an editable SQL code recipe, by navigating to the *output section* (from the left bar) of the recipe, clicking on **View query**, then **Convert to SQL recipe**:

We can now create our target **churn** variable. For the sake of the example, let’s consider 2014-08-01 as the starting point in time, the reference date. The first step is to select our “best” clients, those who bought for more than 30 dollars of products over the past 4 months. Create a SQL recipe by clicking on the “events\_complete” dataset, then on the SQL icon from the right panel:

Select **SQL query**. DSS first asks you to create the output dataset. Name it **train\_active\_clients** and store it into your PostgreSQL connection. Click on **Create** to complete the creation of the recipe:

A template query is automatically generated in the code editor window. Select the best customers using the following query:

§ -- Customers who bought for more than $30 in the past 4 months

§ SELECT

§ \*

§ FROM (

§ SELECT

§ user\_id,

§ SUM(price::numeric) AS total\_bought

§ FROM

§ events\_complete events

§ WHERE

§ event\_timestamp <  TIMESTAMP '2014-08-01 00:00:00'

§ AND event\_timestamp >= TIMESTAMP '2014-08-01 00:00:00' - INTERVAL '4 months'

§ AND event\_type = 'buy\_order'

§ GROUP BY

§ user\_id

§ ) customers

§ WHERE

§ total\_bought >= 30

Note

**Key concept: naming rules**

If your PostgreSQL connection uses a Schema, Table prefix, or Table suffix, you will need to replace events\_complete in this SQL with the appropriate table reference. For example, if you have the ${projectKey}\_ Table prefix set and named the project “Churn Tutorial”, you need to use “CHURNTUTORIAL\_events\_complete” to reference the table in the SQL code.

This SQL query is the first component of our rule to identify churners: it simply filters the data on the purchase events occuring in the 4 months before our reference date, then aggregates it by customer to get their total spending over this period.

Click on **Validate**: DSS will ask you if you want to modify the output dataset schema, accept by clicking on **Update schema**. Finally, click on **Run** to execute the query. When the query has completed, you can click on *Explore dataset train\_active\_clients* to see the results.

The second component of our rule is to find whether or not our best customers will make a purchase in the 4 months after the reference date.

Create a new SQL recipe, where the inputs are **train\_active\_clients** (the one we just created above) and **events\_complete**, and the output will be a SQL dataset named **train**. Copy the code below and run the query:

§ SELECT

§ customer.user\_id,

§ CASE

§ WHEN loyal.user\_id IS NOT NULL THEN 1

§ ELSE 0

§ END as target

§ FROM

§ train\_active\_clients customer

§ LEFT JOIN (

§ -- Users that actually bought something in the following 4 months

§ SELECT distinct user\_id

§ FROM events\_complete

§ WHERE event\_timestamp >= TIMESTAMP '2014-08-01 00:00:00'

§ AND event\_timestamp <  TIMESTAMP '2014-08-01 00:00:00' + INTERVAL '4 months'

§ AND event\_type = 'buy\_order'

§ ) loyal ON customer.user\_id = loyal.user\_id

The idea here is to join the previous list of “best” customers with the ones that will buy something in the next four months (found using the subquery “loyal”). The churners are actually flagged using the CASE statement: if the best customers don’t make a purchase in the next 4 months, they will not be found in the list built by the subquery, hence the NULL value. Each best customer will end up flagged with a “0” if they don’t repurchase (churner), or “1” otherwise.

This marks the end of the first big step: defining what churn is, and translating it into an actual variable that can be used by a machine learning algorithm.

## Building a first model[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#building-a-first-model "Permalink to this headline")

Now that our target variable is built, we need to create the complete train set that will store a list of **features** as well. The features are the variables we’ll use to try to predict churn.

Let’s create a new SQL recipe, with the **train** and **events\_complete** datasets as inputs, and a new SQL dataset named **train\_enriched** as output. The recipe is made of the following code:

§ SELECT train.\*

§ , nb\_products\_seen

§ , nb\_distinct\_product

§ , nb\_dist\_0 , nb\_dist\_1, nb\_dist\_2

§ , amount\_bought , nb\_product\_bought

§ , active\_time

§ FROM train

§ LEFT JOIN (

§ -- generate features based on past data

§ SELECT user\_id

§ , COUNT(product\_id) AS nb\_products\_seen

§ , COUNT(distinct product\_id) AS nb\_distinct\_product

§ , COUNT(distinct category\_id\_0) AS nb\_dist\_0

§ , COUNT(distinct category\_id\_1) AS nb\_dist\_1

§ , COUNT(distinct category\_id\_2) AS nb\_dist\_2

§ , SUM(price::numeric \* (event\_type = 'buy\_order')::int ) AS amount\_bought

§ , SUM((event\_type = 'buy\_order')::int ) AS nb\_product\_bought

§ , EXTRACT(

§ EPOCH FROM (

§ TIMESTAMP '2014-08-01 00:00:00' - MIN(event\_timestamp)

§ )

§ )/(3600\*24)

§ AS active\_time

§ FROM events\_complete

§ WHERE event\_timestamp < TIMESTAMP '2014-08-01 00:00:00'

§ GROUP BY user\_id

§ ) features ON train.user\_id = features.user\_id

This query adds several features to the train dataset (containing the target variable). An intuition is that the more a person is active, the less likely she is to churn.

We hence create the following features for each user:

* total number of products seen or bought on website

* number of distinct product seen or bought

* number of distinct categories (to have an idea of the diversity of purchases of a user)

* total number of products bought

* and total amount spent

Once this first limited set of features is created, our training set is ready. As in the Machine Learning Basics course, create a baseline model to predict the target:

This AUC scores are not that bad for a first model. Let’s deploy it.

## Deploying the model[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#deploying-the-model "Permalink to this headline")

The model being created and trained, we may now want to be able to use it to “score” potential churners on a regular basis (every month for example). To make sure the model will remain robust over time, and to replicate what is going to happen when using it (new records arrive on a regular basis), we can create the “test” set to score.

This is where the modeling process, and notably the train/test split strategy, gets a little bit more tricky.

Let’s say our current timestamp is T, then all the features created for the test set must depend on times t <= T. Since we need to keep 4 months worth of future data to create the target variable in our training set (did the customer repurchase?), the target will be generated from the data in the [T, T + 4 months] time interval, and the train features will use values in the [T - 4 months, T] interval, this way data “from the future” won’t leak in the features. Since we originally choose the reference date to be 2014-08-01, let’s choose T as 2014-12-01 for our test set to stay aligned with what we have already done. To sum up:

* train set target is based on data between 2014-08-01 and 2014-12-01

* train set features are based on data before 2014-08-01

* test set features are based on data before 2014-12-01

* test set optional target can be computed with data between 2014-12-01 and 2015-03-01

Also, to make our scripts more flexible, we will set these train and test reference dates as DSS global parameters, as described here. Click on the *Administration* menu on the top right of your screen, then select *Settings* on the top right and *Variables* on the top left:

The content of the variables editor is the following:

§ {

§ "churn\_test\_date": "2014-12-01 00:00:00",

§ "churn\_duration" : 4,

§ "active\_amount" : 30

§ }

The first parameter is our reference date, when we want to actually score our clients. In a production setting, this could be replaced by “today” for example (we refresh the churn model to predict what is going to happen everyday). The second parameter defines the number of months to use to build our features. The last parameters is the threshold we used for our “best” client definition.

Now let’s update our 3 sql recipes using these new global variables. For example the first one (selecting the customers to use for our training set) becomes:

§ SELECT \* -- select customers with total >= active\_amount

§ FROM (

§ -- select active users and calculate total bought in the period

§ SELECT

§ user\_id ,

§ SUM(price::numeric) AS total\_bought

§ FROM

§ events\_complete events

§ WHERE

§ event\_timestamp <  TIMESTAMP '${churn\_test\_date}'

§ - INTERVAL '${churn\_duration} months'

§ AND event\_timestamp >= TIMESTAMP '${churn\_test\_date}'

§ - 2 \* INTERVAL '${churn\_duration} months'

§ AND event\_type = 'buy\_order'

§ GROUP BY

§ user\_id

§ ) customers

§ WHERE

§ total\_bought >= '${active\_amount}'

§ ;

Modify the two others recipes accordingly.

Moving to the test set, you should also be able to create the 3 required datasets using these SQL recipes:

§ -- Test set population

§ SELECT

§ \*

§ FROM (

§ SELECT

§ user\_id,

§ SUM(price::numeric) AS total\_bought

§ FROM

§ events\_complete events

§ WHERE event\_timestamp <  TIMESTAMP '${churn\_test\_date}'

§ AND event\_timestamp >= TIMESTAMP '${churn\_test\_date}'

§ - INTERVAL '${churn\_duration} months'

§ AND event\_type = 'buy\_order'

§ GROUP BY

§ user\_id

§ ) customers

§ WHERE

§ total\_bought >= '${active\_amount}'

§ -- Test set target variable

§ SELECT

§ client.user\_id,

§ CASE

§ WHEN loyal.user\_id IS NULL THEN 1

§ ELSE 0

§ END as target

§ FROM

§ test\_active\_clients client

§ LEFT JOIN (

§ SELECT DISTINCT user\_id

§ FROM events\_complete

§ WHERE event\_timestamp >= TIMESTAMP '${churn\_test\_date}'

§ AND event\_timestamp <  TIMESTAMP '${churn\_test\_date}'

§ + INTERVAL '${churn\_duration} months'

§ AND event\_type = 'buy\_order'

§ ) loyal ON client.user\_id = loyal.user\_id

§ -- Complete test set

§ SELECT

§ test.\*,

§ nb\_products\_seen,

§ nb\_distinct\_product,

§ nb\_dist\_0,

§ nb\_dist\_1,

§ nb\_dist\_2,

§ amount\_bought,

§ nb\_product\_bought,

§ active\_time

§ FROM

§ test

§ LEFT JOIN (

§ SELECT

§ user\_id,

§ COUNT(product\_id) as nb\_products\_seen,

§ COUNT(distinct product\_id) as nb\_distinct\_product,

§ COUNT(distinct category\_id\_0) as nb\_dist\_0,

§ COUNT(distinct category\_id\_1) as nb\_dist\_1,

§ COUNT(distinct category\_id\_2) as nb\_dist\_2,

§ SUM(price::numeric \* (event\_type = 'buy\_order')::int ) as amount\_bought,

§ SUM((event\_type = 'buy\_order')::int ) as nb\_product\_bought,

§ EXTRACT(

§ EPOCH FROM (

§ TIMESTAMP '${churn\_test\_date}' - MIN(event\_timestamp)

§ )

§ ) / (3600\*24) as active\_time

§ FROM

§ events\_complete

§ WHERE

§ event\_timestamp < TIMESTAMP '${churn\_test\_date}'

§ GROUP BY

§ user\_id

§ ) features ON test.user\_id = features.user\_id

Once this is done, you just have to go back to your model and use it to score the test dataset. In the end, your DSS flow should look like this:

Even though we made great progress, we still have work to do. The major problem is that we did not take time into account to separate (by design of churn) our train and test sets, neither in the evaluation of our model, nor in the features engineering. Let’s see why this can be a big mistake.

## Going deeper into churn modeling[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#going-deeper-into-churn-modeling "Permalink to this headline")

In the previous section, we generated a test set containing the target variable, we should then be able to evaluate our model on this dataset.

Doing so will give us a better approximation of the real model performance, since it takes time into account. We used previously the default random 80% (train) / 20% (test) split, but this assumes that our flow and model are time-independent, an assumption clearly violated in our construction.

Let’s go back to our model, and go to the settings screens. We are going to change the Train & validation policy. Let’s choose “Explicit extracts from two datasets”:

Choose **test\_enriched** as the Test Set and save. DSS will now train the models on the train set, and evaluate it using an auxiliary test set (test\_enriched):

Click on “train” to start the calculations. The summary model list should now be updated with new runs:

Note that the performance (here measured via the AUC) decreased a lot, even though we are using more data (because there is no train split, we have 1.25 times more data in our train set). The reason of this decrease is the combination of 2 factors:

* Things inherently change with time. The patterns observed in our train set may have changed in the test set, since this one uses fresher data. Even though this effect can be rather small, it will however always exist when dealing with time dependent data (and train/test split strategy), and this is something that needs to be taken into account when creating such models.

* Our features are poorly designed, since we created them using the whole history of data. The amount of data available to build the features increases over time: here the test set would rely on a much longer history than the train set (because the train set uses “older” data), so our features distributions would differ a lot between the train set and the test set.

This issue can be solved by designing smarter features, like count of product seen in the past “k” weeks from the reference date, or by creating ratios such as the total amount spent divided by total duration of user activity. We can then transform the code used to generate the train\_enriched dataset this way:

§ SELECT

§ -- Basic features

§ basic.\*,

§ -- "Trend" features

§ one.nb\_products\_seen::numeric / basic.nb\_products\_seen AS rap\_nb\_products\_seen ,

§ one.nb\_distinct\_product::numeric / basic.nb\_distinct\_product AS rap\_nb\_distinct\_product,

§ one.nb\_dist\_0::numeric / basic.nb\_dist\_0 AS rap\_nb\_nb\_dist\_0 ,

§ one.nb\_dist\_1::numeric / basic.nb\_dist\_1 AS rap\_nb\_dist\_1 ,

§ one.nb\_dist\_2::numeric / basic.nb\_dist\_2 AS rap\_nb\_dist\_2 ,

§ one.nb\_seller::numeric / basic.nb\_seller AS rap\_nb\_seller ,

§ one.amount\_bought::numeric / basic.amount\_bought AS rap\_amount\_bought ,

§ one.nb\_product\_bought::numeric / basic.nb\_product\_bought AS rap\_nb\_product\_bought ,

§ -- global features

§ glob.amount\_bought / active\_time AS amount\_per\_time ,

§ glob.nb\_product\_bought::numeric / active\_time AS nb\_per\_time ,

§ glob.amount\_bought AS glo\_bought ,

§ glob.nb\_product\_bought AS glo\_nb\_bought ,

§ -- target

§ target

§ FROM

§ train

§ LEFT JOIN (

§ SELECT

§ user\_id,

§ COUNT(product\_id) AS nb\_products\_seen,

§ COUNT(distinct product\_id) AS nb\_distinct\_product,

§ COUNT(distinct category\_id\_0) AS nb\_dist\_0,

§ COUNT(distinct category\_id\_1) AS nb\_dist\_1,

§ COUNT(distinct category\_id\_2) AS nb\_dist\_2,

§ COUNT(distinct seller\_id) AS nb\_seller ,

§ SUM(price::numeric \* (event\_type = 'buy\_order')::int ) AS amount\_bought,

§ SUM((event\_type = 'buy\_order')::int ) AS nb\_product\_bought

§ FROM

§ events\_complete

§ WHERE

§ event\_timestamp <  TIMESTAMP '${churn\_test\_date}'

§ - INTERVAL '${churn\_duration} months'

§ AND event\_timestamp >= TIMESTAMP '${churn\_test\_date}'

§ - 2 \* INTERVAL '${churn\_duration} months'

§ GROUP BY

§ user\_id

§ ) basic ON train.user\_id = basic.user\_id

§ LEFT JOIN (

§ SELECT

§ user\_id,

§ COUNT(product\_id) AS nb\_products\_seen,

§ COUNT(distinct product\_id) AS nb\_distinct\_product,

§ COUNT(distinct category\_id\_0) AS nb\_dist\_0,

§ COUNT(distinct category\_id\_1) AS nb\_dist\_1,

§ COUNT(distinct category\_id\_2) AS nb\_dist\_2,

§ COUNT(distinct seller\_id) AS nb\_seller,

§ SUM(price::numeric \* (event\_type = 'buy\_order')::int ) AS amount\_bought,

§ SUM((event\_type = 'buy\_order')::int ) AS nb\_product\_bought

§ FROM

§ events\_complete

§ WHERE

§ event\_timestamp <  TIMESTAMP '${churn\_test\_date}'

§ - INTERVAL '${churn\_duration} months'

§ AND event\_timestamp >= TIMESTAMP '${churn\_test\_date}'

§ - INTERVAL '${churn\_duration} months'

§ - INTERVAL '1 months'

§ GROUP BY

§ user\_id

§ ) one ON train.user\_id = one.user\_id

§ LEFT JOIN (

§ SELECT

§ user\_id,

§ SUM(price::numeric \* (event\_type = 'buy\_order')::int ) AS amount\_bought,

§ SUM((event\_type = 'buy\_order')::int ) AS nb\_product\_bought,

§ EXTRACT(

§ EPOCH FROM (

§ TIMESTAMP '${churn\_test\_date}'

§ - INTERVAL '${churn\_duration} months'

§ - MIN(event\_timestamp)

§ )

§ ) / (3600\*24) AS active\_time

§ FROM

§ events\_complete

§ WHERE

§ event\_timestamp < TIMESTAMP '${churn\_test\_date}'

§ - INTERVAL '${churn\_duration} months'

§ GROUP BY

§ user\_id

§ ) glob ON train.user\_id = glob.user\_id

This SQL recipe looks rather cumbersome, but if we look at it more in details, we see that we generated three kinds of variables:

* “basic” features, which are the exact same counts and sums as in the previous section, except that they are now based only on the previous rolling month. This way, our variables are less dependent on time.

* “trend” variables, which are the ratio between the features calculated on one rolling week, and those calculated on one rolling month. With these variables, we intend to capture if the consumer has been recently increasing or decreasing its activity.

* “global” features, which capture the information on the whole customer life. These variables are rescaled by the total lifetime of the customer so as to be less correlated with time.

By adapting in the same way the **test\_enriched** dataset (modifying table names and dates in the query), you should be able to retrain your model, and have performances similar to this:

We managed to improve our performance by more than 0.01 point of AUC, doing some fairly basic feature engineering.

## Wrap-up[¶](https://knowledge.dataiku.com/latest/courses/use-cases/churn-prediction/index.html#wrap-up "Permalink to this headline")

Congratulations if you made it until the end :) This tutorial was rather long and technical, but gives you an idea about how complex this could get to create a churn model. We saw some of the major difficulties:

* defining properly what is churn and how to translate it into a “target” variable

* creating features depending on time, and relatively to a reference date

* choosing the proper cross-validation strategy

Of course, handling this kind of models in real life will take you much further. It is very frequent to include hundreds of features, or even spend a few days trying to properly scope the churn definition.

And this might only be a first step. Indeed, you’ll probably want to model the propensity to respond to different kind of anti-churn offers, or model the true incremental effect of these systems (“lift modeling”).
