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.

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 [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.

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.

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 *