SQL Report

SQL Report

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.

ColumnDescription
timestamp_msTimestamp of the record in milliseconds.
event_nameThe internal event name. For example: "visited site"
personThe internal person ID, e.g. 45678.
person_idThe person's identifier string, e.g. 1ebd5dad-d185-4834-8ca5-c60b5f07bee6 or [email protected].
emailA copy of the person's identifier, but only if it's an email address. If not, this is NULL.
orig_personWhen aliasing (/a) this is the original person.
dest_personWhen aliasing (/a) this is the person after the alias operation.
yearThe year extracted from the record timestamp.
monthThe month extracted from the record timestamp.
channelThe KM Channel property or NULL if this is not applicable.
channel_sourceThe KM Channel Source property or NULL if this is not applicable.
channel_with_sourceThe KM Channel with Source property or NULL if this is not applicable.
previous_pageThe KM Previous Page property or NULL if this is not applicable.
referrerThe KM Referrer property or NULL if this is not applicable.
new_vs_returningThe 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.

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.

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.

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 *