SQL

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 [email protected].
• 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()

Filtering By Event

Use the is_event(nameOrID) function:

• SELECT COUNT(*) FROM records WHERE is_event("visited site"), or
• SELECT COUNT(*) FROM records WHERE is_event(23)

Selecting Property Values

Use the property_value(nameOrID) function:

• SELECT property_value("referrer") FROM records, or
• SELECT property_value(45) FROM records

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.