Getting started: Usage Data

All of your session datapoints are collected in a table called fact_sessions. You can compute total sessions, and total users over time like so:

set app_id=12345;
set lookback_period_in_days=-30;

SELECT client_date,
       count(*) as sessions,
       count(distinct localytics_device_id) as users
FROM fact_sessions
WHERE app_id = $app_id
AND client_date <= dateadd(day ,$lookback_period_in_days, current_date())
GROUP BY client_date
ORDER BY client_date asc;

Which will give you a result like this:

client_date sessions users
2017-01-05 869,296 205,226
2017-01-06 803,181 195,378
… … …

We can then split by new / returning user between a certain data range like so:

set app_id=12345;

SELECT client_date, is_new_device, count(*) as sessions, count(distinct localytics_device_id) as users
FROM fact_sessions
WHERE client_date BETWEEN '2017-01-01' AND '2017-01-17'
AND app_id = $app_id
GROUP BY client_date, is_new_device
ORDER BY client_date asc;


Custom dimensions

Localytics provides up to 20 custom dimensions custom_0...custom_19 in fact_sessions depending your subscription plan. You can lookup these dimensions in localytics_custom_dimensions:

app_id colname description
40957 custom_0 SubscriptionType
40957 custom_1 City
40957 custom_2 FavoriteSportsTeam
… … …

Using this, we can filter our query above for SubscriptionType=Premium like so:

set app_id=12345;

SELECT client_date, is_new_device, count(*) as sessions, count(distinct localytics_device_id) as users
FROM fact_sessions
WHERE client_date BETWEEN '2017-01-01' AND '2017-01-17'
AND app_id = $app_id
AND custom_0 = "Premium"
GROUP BY client_date, is_new_device
ORDER BY client_date asc;


Advanced Tip: The column first_session_date marks the first time we saw a given device. This designates which “cohort” the user is in. You can split by monthly cohort by simply adding Month(first_session_date)

set app_id=12345;

SELECT client_date,
       month(first_session_date),
       is_new_device,
       count(*) as sessions,
       count(distinct localytics_device_id) as users
FROM fact_sessions
WHERE client_date BETWEEN '2017-01-01' AND '2017-01-17'
AND app_id = $app_id
AND custom_0 = 'Premium'
GROUP BY client_date,
         is_new_device,
         month(first_session_date)
ORDER BY client_date asc;