SQL

SQL Report is a powerful way to view your tracking data using the SQL language.

All tracking requests are available in a single table named records.

Column	Description
timestamp_ms	Timestamp of the record in milliseconds.
event_name	The internal event name. For example: "visited site"
person	The internal person ID, e.g. 45678.
person_id	The person's identifier string, e.g. 1ebd5dad-d185-4834-8ca5-c60b5f07bee6 or [e-mail].
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.
dest_person	When aliasing (/a) this is the person 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 Queries

The following examples are use cases for SQL Report that return data that is hard to retrieve using other Reports.

Property Dictionary Query

SELECT property_value('campaign terms'),
       COUNT(*)
FROM records
WHERE has_property('campaign terms')
      AND date_sub(from_unixtime(timestamp_ms/1000), 0) >= date_sub(current_timestamp(), 30)
      AND year in (year(date_sub(current_timestamp(), 0)), year(date_sub(current_timestamp(), 30)))
      AND month in (month(date_sub(current_timestamp(), 0)), month(date_sub(current_timestamp(), 30)))
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1000

This query retrieves the top 1000 most common values and their frequency for a property in the last 30 days.

Property Values Over Time for a Person

SELECT person,
       person_id,
       property_value('campaign name'),
       from_unixtime(timestamp_ms/1000) as property_datetime
FROM records
WHERE person IN (select person from records where person_id = 'IkpsKWXWQv6atHseDye7tHy6Tlc=')
      AND has_property('campaign name')
      AND date_sub(from_unixtime(timestamp_ms/1000), 0) >= date_sub(current_timestamp(), 180)
      AND year in (year(date_sub(current_timestamp(), 0)), year(date_sub(current_timestamp(), 180)))
      AND month in (month(date_sub(current_timestamp(), 0)), month(date_sub(current_timestamp(), 180)))
ORDER BY 3 DESC
LIMIT 100

This query shows property values and timestamps for campaign name for a single person over the last 180 days.

Device Type Analysis

SELECT concat_ws(', ', person_devices) as person_device_list,
       COUNT( DISTINCT person ) as person_cnt
FROM
(
    SELECT DISTINCT person,
                    collect_set(property_value('km device type')) as person_devices
    FROM records
    WHERE has_property('km device type')
          AND date_sub(from_unixtime(timestamp_ms/1000), 0) >= date_sub(current_timestamp(), 30)
          AND year in (year(date_sub(current_timestamp(), 0)), year(date_sub(current_timestamp(), 30)))
          AND month in (month(date_sub(current_timestamp(), 0)), month(date_sub(current_timestamp(), 30)))
    GROUP BY 1
) pd
GROUP BY 1
ORDER BY 2 DESC

This query returns device type values for people tracked in the last 30 days.

🚧Note:

Due to the way our data is stored we cannot do SELECT *