Schema

All tracking requests are available in a single denormalized table: records.

The records table has the following schema:

timestamp_ms: Timestamp of the record in milliseconds.
event: The internal event ID, or NULL if the tracking request was to /s (set properties) or /a (alias a pair of identifiers).
person: The internal person ID, e.g. 45678.
person_id: The person's identifier string, e.g. 1ebd5dad-d185-4834-8ca5-c60b5f07bee6 or foo@bar.com.
email: A copy of the person's identifier, but only if it's an email address. If not, this is NULL.
orig_person: When aliasing (/a) this is the original person ID.
dest_person: When aliasing (/a) this is the person ID after the alias operation.
year: The year extracted from the record timestamp.
month: The month extracted from the record timestamp.
channel: The KM Channel property, or NULL if this is not applicable.
channel_source: The KM Channel Source property, or NULL if this is not applicable.
channel_with_source: The KM Channel with Source property, or NULL if this is not applicable.
previous_page: The KM Previous Page property, or NULL if this is not applicable.
referrer: The KM Referrer property, or NULL if this is not applicable.
new_vs_returning: The KM New Vs Returning property, or NULL if this is not applicable.

Example: count the number of tracking requests in 2017:

SELECT COUNT(*) FROM records WHERE year = 2017

Filtering By Tracking Call

To select aliasing rows (anything sent with the /a tracking request), use:

SELECT ... FROM records WHERE is_alias()

To select rows that were sent with a /s request, use:

SELECT ... FROM records WHERE is_set()

Use the is_event(nameOrID) function:

Use the property_value(nameOrID) function:

To select a numeric property value, use the numeric_property_value(nameOrID) function instead.

To check if a property exists, use the has_property(nameOrID) function.