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 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
- A user predictor always needs to
GROUPby the user id, either from
- 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_valuewhich you can insert manually or from the
Insert SQL Markermenu.
- 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, 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.
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: