There are two tables:
users table contains all static user properties, such as first visit, referring traffic source, etc and are prefixed with
events table contains a log of all actions that users made, such as a purchase, pageview, etc and all properties are prefixed with
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
- A user property always needs to
GROUPby the user id, either from
- 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_valuewhich you can insert manually or from the
Insert SQL Markermenu.
All three conditions need to be met, otherwise the analysis will be incorrect.
Example to count all download events:
Example to get name of last event of user, but without access to user properties from the users table columns:
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
- Insert moment parent marker
@from_criteria. Right before
WHEREand 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:
Example to get name of last event of user, including all three markers:
Example to get timestamp of last event of user, including all three markers: