Introduction

Localytics customers participating in Direct Access can query their data via Snowflake Data Warehouse through SQL or a BI tool. Please use these guides and tutorials to help you in getting connected and understanding user behavior and trends. If you have any issues please email bi-feedback@localytics.com.

Note that the recommendations in this guide apply not only when writing raw SQL but also when using a BI tool, as a BI tool is ultimately just running queries behind the scenes.

Golden Rules

  1. Always include app_id in the WHERE clause of your query. Localytics data is partitioned by app_id and including app_id helps ensure your query is efficient, even when writing cross-app queries.

  2. Always include profiledb_id in the WHERE clause of your profiles queries, leveraging the profile_databases table.

  3. Always include client_date in the WHERE clause of your query. Including a date range using client_date helps ensure your query is efficient. For example, to filter for the last 30 days, include WHERE client_date >= Dateadd(days, -30, current_date()) in the WHERE in your query.

  4. Take advantage of the expansive Snowflake documentation.

Connecting to Localytics data

After you’ve received your Direct Access login, you should login in via https://localyticsdirectaccess.snowflakecomputing.com

We encourage you to familiarize yourself with the Snowflake UI. You will likely spend most of your time in the worksheet.

What’s what? A guide to your Localytics schema

There are three main types of Localytics data points, each corresponding to a single row in their associated database table:

  1. Session starts, found in fact_sessions.
  2. Events, found in fact_events.
  3. Session closes, found fact_session_closes.

Let’s say a user opens your media app, reads 3 articles, and then quits the app. This would log a session start, at least three article_read events, and session close with the length of the session. Each data point is associated with an application identifier (app_id) that you have access to. You can run the following query to find your organization’s app_ids:

 select * from localytics_applications

There are a few common fields in our schema that will come in handy to know:

Column Description
APP_ID Application id
APP_NAME Application NAME
LOCALYTICS_DEVICE_ID Device identifier
CUSTOMER_ID Customer identifier (if not set by customer, set by Localytics automatically)
HASHED_CUSTOMER_ID The SHA-1 hash of CUSTOMER_ID
FIRST_SESSION_DATE client_date of the first processed session for this device
CUMULATIVE_NUMBER_SESSIONS Cumulative number of sessions for this device
CLIENT_DATE The date in the App timezone that this datapoint was created at
CUSTOM_0…CUSTOM_19 Custom dimensions

See the below schemas for more details.

Schemas

Connecting to a BI Tool

Direct Access is compatible with any BI tool that has a Snowflake connector or a generic ODBC connector.

Further instructions for connecting Snowflake to Tableau can be found here.

Direct Access Self-service Option

If you already have your own Snowflake account and have selected the Direct Access self-service option, please read these setup instructions to get connected.

Getting started

Tutorials

These tutorials will help you get up and running quickly with your analysis. Here is a recommended order:

  1. Usage
  2. Events
  3. Common Metrics
  4. Engagement
  5. Segments
  6. Retention Cohorts
  7. Profiles
  8. Timezones
  9. Combining Data from other sources (self-service option only)
  10. Exporting data to S3

Writing optimized queries against fact_* tables

Queries against Localytics data should be written a certain way to achieve optimal query speed. This also applies if you are using a BI tool such as Tableau, Looker, PowerBI, etc.

  1. Always include APP_ID in the WHERE clause
  2. In profiles queries, always include PROFILEDB_ID in the WHERE clause
  3. Always include CLIENT_DATE in the WHERE clause
  4. When querying fact_events, additionally filtering on event_name will result in the best performance.

Profiles

Profiles data is also stored in Snowflake and is a bit different from your behavioral data. You can read more about the schema here. Your profiles data and usage data share hashed_customer_id in common, allowing you to segment your analytics data by profile attributes and vice-versa.

Event Taxonomy

Your app’s events and attributes are listed in localytics_event_attribute_mappings. Use this to review which event names along with custom attributes are available to query across apps. This table will also tell you which individual fact_events field is associated with a given attribute, but you can also refer to an attribute by name using the handy attributes field. While you will be able to see new events/attributes incoming immediately in fact_events, this taxonomy table is updated bi-daily. See our events tutorial for details.

Lookup tables

Your Localytics data also consists of several smaller lookup tables.

Table Description
localytics_applications Application properties by app_id
profile_databases profiledb_id mappings for each app / org
localytics_event_attribute_mappings Event attribute mappings by app_id event name, and column name (e.g. attr_0)
localytics_custom_dimensions Custom dimension names by app_id and column name (e.g. custom_0)
metro_codes Metro properties by metro
cities City properties by city
browsers Browser properties by browser_id

Monitoring Usage

In order to view credit usage for warehouses, please refer to the Snowflake Documentation.