Custom SQL User Property

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