Skip to main content

Churn analysis

info

View this example in Motif here.

This examples takes segments we created in the Behavioral Segmentation video and analyzes how they churn differently on usage in order to identify a churn gap.

Video​

SOL query​

// Divide user sequences into sessions
match split Session()+
if duration(Session[-1], SUFFIX[0]) > 1h

// Add up the number of watch_end event for each user
match split watch_end
combine session_movies_watched = max(split_count)

// Recombine sessions and calculate the average movies per session
combine avg_session_movies = avg(session_movies_watched)

// Assign a user segment based on the cutoff value
set segment= if(avg_session_movies>1.5,"binge","casual")

// Add a dimension to each event with the time gap to the following event
set SEQ[:-1].time_to_next_event = SEQ[1:].ts - SEQ[:-1].ts

// Divide user sequences into sessions again
match split Session()+
if duration(Session[-1], SUFFIX[0]) > 1h

// Take the time_gap from the last value in the session and make it a dimension
set time_gap = coalesce(
   Session[-1].time_to_next_event,
   if(now()-Session[-1].ts > 21d, 21d, now()-Session[-1].ts)
)

Key steps​

  1. We do some array math to calculate the time between events and store it on the event itself
// Add a dimension to each event with the time gap to the following event
set SEQ[:-1].time_to_next_event = SEQ[1:].ts - SEQ[:-1].ts
  1. We sessionize the user sequences and assign the time_to_next_event value from the last event in the session as a dimension on the sequence, replacing nulls with 21 days (if they occurred more than 21 days ago).
// Divide user sequences into sessions again
match split Session()+
if duration(Session[-1], SUFFIX[0]) > 1h

// Take the time_gap from the last value in the session and make it a dimension
set time_gap = coalesce(
   Session[-1].time_to_next_event,
   if(now()-Session[-1].ts > 21d, 21d, now()-Session[-1].ts)
)
  1. We plot the time gap by day, add a group-by for our segment and normalize by the population of the segment. This lets us see that users churn after about three weeks on average.

churn