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 Goal Requirements
To create a valid custom SQL Intelligence goal, 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.
This result marker should be the
events.created_atcolumn of the first event that makes the goal happen.
For example a custom goal that converts to playing a song, then it will be the timestamp of the earliest play song event. Or in case of a retention goal, the first timestamp of the first event that made the user come back in the 2nd week.
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 custom goal that returns the first timestamp of the event where main goal is to have at least 2 referring events: