Custom SQL Goal

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 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 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.

    This result marker should be the events.created_at column 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.

Examples

Example custom goal that returns the first timestamp of the event where main goal is to have at least 2 referring events:

SELECT id, MAX(first_timestamp) AS @result_value FROM (SELECT users.id, (NTH_VALUE(events.created_at, 2) OVER (partition BY users.id ORDER BY events.created_at ASC)) AS first_timestamp FROM users JOIN events ON users.id = events.user_id WHERE name = '_referrer_event') GROUP BY id