Custom SQL User Property Or 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 Property Requirements

To create a valid custom SQL User Property, three 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 property always needs to GROUP by the user id, either from events.user_id or from users.id.
  • 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.

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

Examples

Example to count all download events:

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

Example to get name of last event of user, but without access to user properties from the users table columns:

SELECT user_id AS id, MAX_BY(name, events.created_at) AS @result_value FROM events GROUP BY user_id

SQL User Property As Predictor Requirements

A SQL user property can also be used as a predictor for Insights that support them, such as the Conversion Patterns Insight.

Two extra conditions have to be met, to make a SQL user property usable as predictor:

  • 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, which includes using the @result_value marker from above, 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 SQL User Property As Predictor

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