An Example of Predicting Churn in the Music Industry
Here the dataset consists of transactions of a bunch of users on a music platform. Some of these users will eventually cancel their subscription. My aim is to predict those who will churn. This project is also an opportunity to learn and use Spark in a real world project

Most of the datasets I have worked with so far has been formatted where each row is a sample and has a label that I would like to predict. But here I have to extract the users and engineer the features from the transactions data to build a model. So most of the time in this project has been spend on Data understanding and feature engineering

Data Understanding

Total entries in the data are 286500. Different columns and the number of unique values in each column are given below

artist 17656

auth 4

firstName 190

gender 3

itemInSession 1322

lastName 174

length 14866

level 2

location 115

method 2

page 22

registration 226

sessionId 2354

song 58481

status 3

ts 277447

userAgent 57

userId 226

userId column represents the 226 unique users in the dataset. So Aim to create many features for these users. Few other descriptions of the data are

+----------+------+

| auth| count|

+----------+------+

|Logged Out| 8249|

| Cancelled| 52|

| Guest| 97|

| Logged In|278102|

+----------+------+

+-----+------+

|level| count|

+-----+------+

| free| 58338|

| paid|228162|

+-----+------+

Later we will be focussing on only entries whose level column is paid and auth column is Logged In.

The page column tells where does the user click. Here are the different actions users have performed

+--------------------+------+

| page| count|

+--------------------+------+

| NextSong|228108|

| Home| 14457|

| Thumbs Up| 12551|

| Add to Playlist| 6526|

| Add Friend| 4277|

| Roll Advert| 3933|

| Login| 3241|

| Logout| 3226|

| Thumbs Down| 2546|

| Downgrade| 2055|

| Help| 1726|

| Settings| 1514|

| About| 924|

| Upgrade| 499|

| Save Settings| 310|

| Error| 258|

| Submit Upgrade| 159|

| Submit Downgrade| 63|

| Cancel| 52|

|Cancellation Conf...| 52|

+--------------------+------+

So my first task to find who are the churned users. There are two categories: first choice is who clicked ‘Cancellation Confirmation’ and second choice is who clicked ‘Submit Downgrade’ and changed from paid to free account

I’ve used Spark SQL queries extensively in this project to extract data. Below is an example to get the churned userId’s

query="""

select userId,page

from spotify

where page='Cancellation Confirmation'

"""

spark.sql(query).show(5)

..... +------+--------------------+

|userId| page|

+------+--------------------+

| 18|Cancellation Conf...|

| 32|Cancellation Conf...|

| 125|Cancellation Conf...|

| 105|Cancellation Conf...|

| 17|Cancellation Conf...|

+------+--------------------+

only showing top 5 rows

There are 52 such users. But I found that some of these users in the free category as shown below. I would be considering only those users who are in the paid category

+-----+---------+--------+

|level| auth|count(1)|

+-----+---------+--------+

| paid|Cancelled| 31|

| free|Cancelled| 21|

+-----+---------+--------+

In order to find the users who changed from paid to free category. I ran the following query

query="""

select userId,page,level,lead_level

from

(select userId,page,level,lead(level) over (partition by userId order by ts) as lead_level

from spotify

where userId !=''

order by userId) sub

where level='paid' and lead_level='free'

"""

spark.sql(query).show(5)

..... +------+----------------+-----+----------+

|userId| page|level|lead_level|

+------+----------------+-----+----------+

| 100|Submit Downgrade| paid| free|

|100004|Submit Downgrade| paid| free|

|100004|Submit Downgrade| paid| free|

|100008|Submit Downgrade| paid| free|

|100009|Submit Downgrade| paid| free|

+------+----------------+-----+----------+

only showing top 5 rows

Here level and lead_level are levels corresponding to the two adjacent transactions of a user. One can see that once a user hit Submit Downgrade his level changes from paid to free. I would consider these users as churned users as well. There are 49 users belonging to this category. Combining these two categories of churned users led to 76 unique users

Data Cleaning

In the dataset, several userId doesn’t have a value. They are associated with ‘Logged out’ entries. I’ve kept only ‘Logged in’ entries. Since the aim here is to predict which of the paid users are going to churn, I removed all the entries corresponding to ‘free’ in the level column. It also make sense since free users might have some restrictions on using the content. Some of the churned user have ‘free’ entries associated with them after they have cancelled their subscription. This entries should not be used in predicting the churn

After cleaning, there are 222402 entries. Number of churned users are 76 and number of non-churned users are 88

Feature Engineering

Now I can divide the dataset into two: one belonging to the churned users and other belonging to the non-churned users. Then I could compare various features between two groups and see whether they will help to distinguish two types of users

Skip Ratio

This is ratio of number of times a user has skipped to next page before 2 minutes. My assumption is that if someone doesn’t like a song, he may skip faster. However, this ratio was similar between churned and non-churned users. But it could be relevant in combination with other features.

query="""

With t3 as

(With t1 as (

select userId userId1,count(*) cn1 from spotify_cleaned group by userId order by userId

),

t2 as (

select userId userId2,count(*) cn2 from spotify_cleaned where length<120 group by userId order by userId

)

Select *

from t1

left Join t2

on t2.userId2=t1.userId1)

Select userId1 as userId,cn2/cn1 skip_ratio

from t3

order by skip_ratio desc """

user_skip_ratio=spark.sql(query)

user_skip_ratio=user_skip_ratio.fillna(0)

user_skip_ratio.show(5) output:

+------+--------------------+

|userId| skip_ratio|

+------+--------------------+

| 55| 0.06896551724137931|

|300005| 0.03939393939393939|

| 79|0.036585365853658534|

| 93| 0.03614457831325301|

|300004| 0.03289473684210526|

+------+--------------------+

only showing top 5 rows

Gender

Number of males and females in both categories are nearly equal. But entries by females are much higher in the churned group. So Gender could be important variable

Non-churned Users

+------+--------------------+

|gender|gender_count_entries|

+------+--------------------+

| F| 59857|

| M| 54581|

+------+--------------------+

Churned Users

+------+--------------------+

|gender|gender_count_entries|

+------+--------------------+

| F| 65036|

| M| 42927|

+------+--------------------+

Thumbs up/ Thumbs down ratio

This is the ratio of total number of ‘thumbs up’ to the total number of ‘thumbs down’ for a users. My assumption is that satisfied users have higher values for this ratio. It appears that non-churned users have slightly higher ratio

query="""

With t3 as

(With t1 as (

select userId userId1,count(*) t_up from spotify_cleaned Where page='Thumbs Up' group by userId order by userId

),

t2 as (

select userId userId2,count(*) t_down from spotify_cleaned where page='Thumbs Down' group by userId order by userId

)

Select *

from t1

left Join t2

on t2.userId2=t1.userId1)

Select userId1 as userId,t_up/t_down updown_ratio

from t3

order by updown_ratio desc """

user_updown_ratio=spark.sql(query)

user_updown_ratio=user_updown_ratio.fillna(0)

user_updown_ratio.show(5) Output: ------+------------+

|userId|updown_ratio|

+------+------------+

| 155| 24.5|

| 18| 20.0|

|300013| 19.0|

|300020| 18.0|

|100015| 14.0|

+------+------------+

only showing top 5 rows

Page category counts

As shown previously, there are different types of page views where ‘Next Song’ dominates. For each user, I calculate the frequency of a page category as a fraction of total visit by the user. Then I compared this fraction for all users in the churned and non-churned category. I selected three categories where it looked different between churned and non-churned users. They are NextSong, Roll Advert & Error

Top 10 Artist Fraction

I believe that dedicated music fans have favourite artists. And the presence of songs of their favourite artist’s is something that make customers happy. So I calculated the total entries of top 10 artists for each user and divided by the total of entries for the same user.

Fraction of distinct artist and songs

This has some similarity with the previous feature. Some users might stick with few artists while others might go from one to another randomly. This is true for songs as well. This feature calculate the total number of unique artists or songs a user has visited as a fraction of total entries of that user

Active days fraction and number of sessions per active days

How many days and how many times a day a user login are important factors to predict the churn. Both these features correlate with activity of the user and active users are less likely to churn. To find the active days fraction, the number of distinct days a user has logged in is determined. This is divided by the difference between minimum and maximum day of a user to get the fraction. To find the sessions per active days, total number of sessions for each user is divided by the number of active days

Item per sessions

This is roughly proportional to the duration of each session. For each user, average of items in each sessions is determined. Active users might have more items per session

Model Building

The Final data frame has 163 rows and 14 columns. We need to convert ‘gender’ column into numerical. For this purpose, I used StringIndexer followed by OneHotEncoderEstimator. For model building, all the features need to be converted to single of column of features. I used VectorAssembler for that. All these procedures were implemented using a pipeline after splitting data into training and test sets

# Split the Data

user_churn_df_train,user_churn_df_test=user_churn_df.randomSplit([.8,.2],seed=125) # Create a pipeline vec_assembler1 = VectorAssembler(inputCols=['skip_ratio','updown_ratio','Error','NextSong',

'Roll Advert','top10_frac','dist_artist_frac','dist_song_frac','session_act_days','act_days_frac',

'avg_items_sessions'],outputCol='features1') scaler = StandardScaler(inputCol="features1", outputCol="scaledFeatures",

withStd=True, withMean=False)

stringIndexer = StringIndexer(inputCol="gender",outputCol="genderIndex")

encoder = OneHotEncoderEstimator(inputCols=["genderIndex"],outputCols=["genderVec1"])

vec_assembler2 = VectorAssembler(inputCols=["scaledFeatures",'genderVec1'],outputCol='features')

pipeline=Pipeline(stages=[vec_assembler1,scaler,stringIndexer, encoder,vec_assembler2]) # fit and transform using the pipeline to produce train and test sets model=pipeline.fit(user_churn_df_train) train=model.transform(user_churn_df_train)

test=model.transform(user_churn_df_test)

Three different models has been evaluated using f1-score. They logistic Regression, Linear SVC and Random Forests

Logistic Regression

lr = LogisticRegression(labelCol="Churn",featuresCol="features")

lrModel = lr.fit(train)

train_predictions=lrModel.transform(train)

evaluator =evals.MulticlassClassificationEvaluator(labelCol="Churn", predictionCol="prediction",metricName='f1') print(evaluator.evaluate(train_predictions))

output:.6798808588254699

test_predictions=lrModel.transform(test)

print(evaluator.evaluate(test_predictions))

output:0.6664576802507837

Performance on the the training set and testing set are good. This model could be optimised further by tuning the hyper parameters or adding polynomial features

Linear SVM

lsvc = LinearSVC(labelCol="Churn",featuresCol="features")

lsvcModel=lsvc.fit(train)

train_predictions=lsvcModel.transform(train)

evaluator =evals.MulticlassClassificationEvaluator(labelCol="Churn", predictionCol="prediction",metricName='f1') print(evaluator.evaluate(train_predictions))

output:0.6531008945141741 test_predictions=lsvcModel.transform(test)

print(evaluator.evaluate(test_predictions))

output:0.7064935064935065

Performance on the test is better than Logistic Regression. However model took long time to train

Random Forests

rf = RandomForestClassifier(labelCol="Churn", featuresCol="features", numTrees=25,maxBins=5,maxDepth=4,featureSubsetStrategy='onethird')

rf_model=rf.fit(train)

train_predictions=rf_model.transform(train)

evaluator =evals.MulticlassClassificationEvaluator(labelCol="Churn", predictionCol="prediction",metricName='f1') print("Train set f1 score is ",evaluator.evaluate(train_predictions))

output:0.8416119429121793 test_predictions=rf_model.transform(test)

print("Test set f1 score is ",evaluator.evaluate(test_predictions))

output:0.5454545454545454

This Model has a tendency to overfit. Even though I tried minimising the over-fit by lowering the maxDepth parameter, f1 score is much lower compared to the training set

Tuning the model

Finally, I have decided to select the Logistic Regression model and further optimise it. I used the CrossValidator function which will use 5-fold cross validation and parameter grid search to find the best model. The metric used is f1-score.

lr = LogisticRegression(labelCol="Churn",featuresCol="features")

evaluator =evals.MulticlassClassificationEvaluator(labelCol="Churn", predictionCol="prediction",metricName='f1')

grid = tune.ParamGridBuilder()

grid = grid.addGrid(lr.elasticNetParam,[0.0, 0.5, 1.0])

grid = grid.addGrid(lr.maxIter,[10, 100, 200])

grid = grid.addGrid(lr.threshold,[.4,.5,.6])

grid = grid.build()

cv = tune.CrossValidator(estimator=lr,

estimatorParamMaps=grid,

evaluator=evaluator,numFolds=5)

models = cv.fit(train)

best_lr= models.bestModel

Best model’s performance on the training and test set are below

train_predictions=best_lr.transform(train)

print(“Train set f1 score is“,evaluator.evaluate(train_predictions))

Train set f1 score is 0.6818801279376998 test_predictions=best_lr.transform(test)

print("Test set f1 score is ",evaluator.evaluate(test_predictions))

Test set f1 score is 0.6462891946762914

Summary

The engineered features and the models does a decent job in predicting the churned customers considering that the data set is quite small. With a large data set, model might generalise well and improve its performance on the test data. Additionally, I could add polynomial features to add complexity into the model
['Vineeth Mukundan']
['Data Science', 'Churn Rate', 'Pyspark', 'Spotify', 'Machine Learning']