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.

Language
Authentication
Basic
base64
:
Click Try It! to start a request and see the response here!