Getting started: Event Data

All of your event datapoints are collected in a table called fact_events. You can compute event occurrences in the past 30 days like this:

set app_id=12345;
set lookback_period_in_days=-30;

SELECT event_name, count(*) as occurrences
FROM fact_events
WHERE client_date >= dateadd(day, $lookback_period_in_days, current_date())
AND app_id = $app_id
GROUP BY event_name
ORDER BY occurrences desc;

Which will give you a result like this:

name occurrences
ArticleViewed 100,000
VideoPlayed 60,000
… …

You can use a custom date range by modifying the WHERE clause with:

WHERE client_date BETWEEN '2017-01-15' AND '2017-01-17'

You can trend a specific event’s occurrences over time like so:

set app_id=12345;
set event_name='VideoPlayed';
set data_start_date='2017-01-01';
set data_end_date='2017-01-17';

SELECT client_date,
       count(*) as occurrences
FROM fact_events
WHERE client_date BETWEEN $data_start_date AND $data_end_date
AND app_id = $app_id
AND event_name = $event_name
GROUP BY client_date
ORDER BY occurrences desc;

You can filter on a dimension such as app_version by adding another AND condition:

set app_id=12345;
set event_name='VideoPlayed';
set data_start_date='2017-01-01';
set data_end_date='2017-01-17';
set app_version='4.4.2';

SELECT client_date,
       app_version,
       count(*) as occurrences
FROM fact_events
WHERE client_date BETWEEN $data_start_date AND $data_end_date
AND app_id = $app_id
AND event_name = $event_name
AND app_version = $app_version
GROUP BY client_date, app_version;

Which will result in:

client_date app_version occurrences
‘2017-01-01’ 4.4.2 134,234
2017-01-02 4.4.2 156,436
2017-01-03 4.4.2 131,632
… … …

Besides occurrences, we are oftentimes interested in what are the number of unique users (or devices) completing an event over time:

set app_id=12345;
set event_name='VideoPlayed';
set lookback_period_in_days=-30;

SELECT client_date,
       COUNT(DISTINCT localytics_device_id) as users
FROM fact_events
WHERE client_date >= dateadd(day, $lookback_period_in_days, current_date())
AND app_id = $app_id
AND event_name = $event_name
GROUP BY client_date;

Advanced Tip: Try using APPROXIMATE_COUNT_DISTINCT(localytics_device_id) for faster performance. The error will only be within ~2%.

Attributes

Event attributes are a powerful way to tag your events with more detail.

Accessing Attributes via the attr fields

In fact_events each event is associated with to 50 attributes in columns attr_0...attr_49. Let’s say you wanted to analyze the ContentType attribute of ArticleViewed event for Galaxy S7 devices. According to the LOCALYTICS_EVENT_ATTRIBUTE_MAPPINGS table, its code is attr_1:

app_id event_name attribute_name attribute_name_code
40957 VideoPlayed Orientation attr_0
40957 ArticleViewed ContentType attr_1
40957 ArticleViewed ArticleLength attr_2
… … … …

You can now count unique customers split by ContentType for Galaxy S7 devices as follows:

set app_id=12345;
set lookback_period_in_days=-30;
set event_name='ArticleViewed';
set device_model='Galaxy S7';

SELECT client_date,
       event_name,
       attr_1 as ContentType,
       COUNT(DISTINCT localytics_device_id) as users
FROM fact_events
WHERE client_date >= Dateadd(day, $lookback_period_in_days, current_date())
AND app_id = $app_id
AND event_name = $event_name
AND device_model = $device_model
GROUP BY client_date, event_name, attr_1;
ORDER BY client_date

Which gives:

client_date event_name ContentType users
2017-01-05 ArticleViewed Arts 10,000
2017-01-06 ArticleViewed Sports 30,000
…   … …

Advanced Tip: Numeric attributes are stored in columns n_attr_0...n_attr_49. Try using AVG,SUM,MEDIAN functions to aggregate these attributes

Accessing Attributes via the attributes object

Another way of accessing attributes which is more human readable is via the attributes object. Please reference Snowflake’s Documentation on querying Semi-structured data for more in-depth information.

Here is the same example as above but expressed with the attributes object:

set app_id=12345;
set lookback_period_in_days=-30;
set event_name='ArticleViewed';
set device_model='Galaxy S7';
set attribute_split_name='ContentType';

SELECT client_date,
       event_name,
       attributes[$attribute_split_name] as split_attribute,
       COUNT(DISTINCT localytics_device_id) as users
FROM fact_events
WHERE client_date >= dateadd(day, $lookback_period_in_days, current_date())
AND app_id = $app_id
AND event_name = $event_name
AND device_model = $device_model
GROUP BY client_date, event_name, attributes[$attribute_split_name]
ORDER BY CLIENT_DATE;

Aggregating attributes across events

Let’s say you have two events Sports Article Read and Business Article Readand they share an Article Length attribute. You want to calculate the average Article Length across events.

The easiest way to calculate this is going to be to leverage the attributes field like so:

set app_id=12345;
set lookback_period_in_days=-30;
set event_name='ArticleViewed';
set device_model='Galaxy S7';
set attribute_split_name='Article Length';

SELECT
  AVG(attributes[$attribute_split_name]) AS average_numeric_attribute
FROM
  fact_events
WHERE app_id = $app_id
  AND client_date >= dateadd(day, $lookback_period_in_days, current_date())
  AND event_name in ('Sports Article Read','Business Article Read');

If you prefer to use the attr_* & n_attr_* fields, where each numbered field corresponds to an individual attribute, you cannot expect attributes to be aligned across different event types. That is, the corresponding table columns generally won’t be the same. For example, Article Length might correspond to n_attr_4 for Sports Article Read but correspond to n_attr_7 for Business Article Read. In order to combine them, you would write your average article length query like so:

set app_id=12345;
set lookback_period_in_days=-30;

SELECT
  AVG(
    CASE
      WHEN event_name = 'Sports Article Read'
        THEN n_attr_4
      WHEN event_name = 'Business Article Read'
        THEN n_attr_7
    END
  )
  AS average_article_length
FROM
  fact_events
WHERE app_id = $app_id
  AND client_date >= dateadd(day, $lookback_period_in_days, current_date())
  AND event_name in ('Sports Article Read', 'Business Article Read');