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: