Skip to main content

SOL analysis recipes

This page provides a list of common Sequence Operations Language (SOL) queries. They can be easily accessed inside Motif by typing \ into the SOL query box.

Define a funnel​

// 📗 Define a funnel
// Match 'event1' optionally followed by 'event2' optionally followed by 'event3' with any events in between
match event1 >> * >> event2? >> * >> event3?
// For strict funnels, only match 'event3' if 'event2' was matched
  if nested(event2, event3)
// Replace sequences by only 'event1', 'event2' and 'event3'
replace SEQ with event1 >> event2 >> event3

Filter to matched sequences​

// 📗 Filter to matched sequences
// Filter to sequences with the 'MATCHED' tag, which is automatically assigned to matched events
filter MATCHED

Compute duration between events​

// 📗 Compute duration between events
// Match 'event1' followed by 'event2' with any events in between
match event1 >> * >> event2
// Create a sequence dimension equal to duration between 'event1' and 'event2'
set duration_1_to_2 = duration(event1, event2)
// 🔎 View average duration across all sequences in the Metrics tab

Filter to specific events​

// 📗 Filter to specific events
// Match all ‘event1’, ‘event2’ and ‘event3’ events, then split sequences before each match
match split UsefulEvent(event1 | event2 | event3)
// Replace sequences with matched events to remove other events
replace SEQ with UsefulEvent
// Combine sub-sequences into original sequences
combine

Find when something didn’t happen​

// 📗 Find when something didn’t happen
// Match subsequences of 'event1' followed by 0 or more non-'event2' events
match event1 >> (^event2)*
// ...if 1st event after the match is >1 hour after 'event1' or no events after the match
  if duration(event1, SUFFIX[0]) > 1h

View sequence for a specific user​

// 📗 View sequence for a specific user
// Filter to sequences with 'actor' dimension of the 1st event being 'user_id'
filter SEQ[0].actor = 'user_id'

Explore before or after a specific event​

// 📗 Explore before or after a specific event
// Match 'event1', following events up to 1 hour after and up to 20 events prior
match Before()* >> event1 >> After()*
  if (duration(event1, After) <= 1h) and (length(Before) <= 20)

Truncate sequences​

// 📗 Truncate sequences
// Match 'event1'
match event1
// Remove all events before 'event1'
replace PREFIX with null
// Remove all events after 'event1'
replace SUFFIX with null

Split sequences on time gap between events​

// 📗 Split sequences on time gap between events
// Match sub-sequences of 1+ events where duration between the last event of each match and the next event is >1 hour, then split sequence before each match
match split Session()+
  if duration(Session[-1], SUFFIX[0]) > 1h

Remove specific events​

// 📗 Remove specific events
// Match all 'event1' and 'event2' events, then split sequences before each match
match split UnwantedEvent(event1 | event2)
// Replace matched events with null to remove
replace UnwantedEvent with null
// Combine sub-sequences into original sequences
combine

Remove consecutive repeated events​

// 📗 Remove consecutive repeated events
// Match all consecutive events with the same name, then split sequences before each match
match split RepeatedEvents()+
  if RepeatedEvents.name = RepeatedEvents[0].name
// Replace matches with the 1st matched event and wrap it into a tag 'CombinedEvent'
replace RepeatedEvents with CombinedEvent(@RepeatedEvents[0])
// Set dimension on the new event equal to the number of matched events
  dims CombinedEvent.num_events = length(RepeatedEvents)
// Combine sub-sequences into original sequences
combine

Change event names or other dimensions​

// 📗 Change event names or other dimensions
// Change event names to use other event dimensions
set SEQ.name = concat(SEQ.name, '_', SEQ.another_dim)
// Add 'gap' dimension by subtracting timestamp of the next event from the current one, using Python-like array slicing and element-by-element array operations
set SEQ[:-1].gap = SEQ[1:].ts - SEQ[:-1].ts

Compute attribution​

// 📗 Compute last touch attribution
// Match the last 'event1' or 'event2' preceding 'event3' within 5 minutes of it
match Exposure(event1 | event2)? >> (^event1, event2)* >> Conversion(event3)
  if duration(Exposure, Conversion) < 5min
// Create sequence dimension equal to the matched event's name or 'none'
set attributed_to = coalesce(Exposure.name, 'none')
// 🔎 View attribution distribution across all sequences in the Metrics tab

Measure retention​

// 📗 Measure retention
// Find 'signup' events for each user
match signup
// Compute user cohorts as a sequence dimension equal to the signup week
set user_signup_week = time_bucket(1w, signup.ts)
// Match 'event1' optionally followed by another 'event1' and then another one with any events in between
match Action1(event1) >> * >> Action2(event1)? >> * >> Action3(event1)?
// Only match 'Action3' if 'Action2' was matched
  if nested(Action2, Action3)
// ...and if duration between consecutive events is between 1 and 2 weeks
  and (duration(Action1, Action2) between 1w and 2w) and (duration(Action2, Action3) between 1w and 2w)
// Replace sequences by only 'Action1', 'Action2' and 'Action3'
replace SEQ with Action1 >> Action2 >> Action3

Label moments of churn​

// 📗 Label moments of churn
// Set a sequence dimension to 3 days to use as the time gap defining churn
set churn_gap = 3d
// Match all subsequences of 'event1' followed by 0 or more non-'event1' events
match split event1 >> (^event1)*
// ...if event after the match is more than 3 days after 'event1'
  if (SUFFIX and duration(event1, SUFFIX[0]) > churn_gap)
// ...or if there are no events after the match and 'event1' was over 3 days ago
  or (not(SUFFIX) and (now() - event1.ts > churn_gap))
// Insert a new event called 'churned' after matches
replace MATCHED with MATCHED >> Churn(churned)
// Set its timestamp to be timestamp of prior 'event1' plus the churn gap
dims Churn.ts = event1.ts + churn_gap
// Combine sub-sequences into original sequences
combine