Custom SQL User Predictor

General

There are two tables: users and events. The users table contains all static user properties, such as first visit, referring traffic source, etc and are prefixed with u_col_. The events table contains a log of all actions that users made, such as a purchase, pageview, etc and all properties are prefixed with e_col_.

User and event properties are prefixed with their table names. For example when selecting browser_name from the an property, it becomes event.e_col_browser_name

To insert user or event column names use the Add SQL menu option.

SQL User Predictor Requirements

To create a valid custom SQL User Predictor, five conditions have to be met:

  • A column with the user id should always be returned from the results, either from events.user_id or from users.id.
  • A user predictor always needs to GROUP by the user id, either from events.user_id or from users.id.
  • Return the result by inserting the marker @result_value. You can have multiple field names returned in the first part after SELECT SUM(price) AS @result_value, .. AS .. but one of them needs to be the result value marker @result_value which you can insert manually or from the Insert SQL Marker menu.
  • Insert moment filter marker @manifest_moment_filter_marker. This makes sure we only analyze events that are before a certain absolute timestamp using events.created_at.
  • Insert moment parent marker @from_criteria. Right before WHERE and the join condition of events and users table.

All three conditions need to be met, otherwise the analysis will be incorrect.

Note that because a custom SQL Intelligence goal returns a non-aggregate events.created_at field, we cannot use a simple ORDER BY ... LIMIT 1, but we need to use PARTITION. See example.

Examples

Example to count all download events, including all three markers:

SELECT users.id, COUNT(events.name) AS @result_value FROM users JOIN events ON users.id = events.user_id @from_criteria WHERE @manifest_moment_filter_marker AND (events.name = 'download') GROUP BY users.id

Example to get name of last event of user, including all three markers:

SELECT id, MAX(alias1) AS @result_value FROM( SELECT users.id, (FIRST_VALUE(events.name) OVER (PARTITION BY users.id ORDER BY events.created_at DESC)) AS alias1 FROM users JOIN events ON users.id = events.user_id @from_criteria WHERE @manifest_moment_filter_marker ) GROUP BY id

Example to get timestamp of last event of user, including all three markers:

SELECT id, FROM_UNIXTIME(MAX(alias1)) AS @result_value FROM(SELECT users.id, (FIRST_VALUE(events.name) OVER (PARTITION BY users.id ORDER BY events.created_at DESC)) AS alias1 FROM users JOIN events ON users.id = events.user_id @from_criteria WHERE @manifest_moment_filter_marker ) GROUP BY id