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 firstname.lastname@example.org.
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.
WHEREclause of your query. Localytics data is partitioned by
app_idhelps ensure your query is efficient, even when writing cross-app queries.
WHEREclause of your profiles queries, leveraging the
WHEREclause of your query. Including a date range using
client_datehelps 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
WHEREin your query.
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:
- Session starts, found in
- Events, found in
- Session closes, found
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
select * from localytics_applications
There are a few common fields in our schema that will come in handy to know:
|CUSTOMER_ID||Customer identifier (if not set by customer, set by Localytics automatically)|
|HASHED_CUSTOMER_ID||The SHA-1 hash of
|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|
See the below schemas for more details.
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.
These tutorials will help you get up and running quickly with your analysis. Here is a recommended order:
- Common Metrics
- Retention Cohorts
- Combining Data from other sources (self-service option only)
- 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.
- Always include
- In profiles queries, always include
- Always include
- When querying
fact_events, additionally filtering on
event_namewill result in the best performance.
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.
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.
Your Localytics data also consists of several smaller lookup tables.
|localytics_applications||Application properties by app_id|
|localytics_event_attribute_mappings||Event attribute mappings by
|localytics_custom_dimensions||Custom dimension names by
|metro_codes||Metro properties by metro|
|cities||City properties by city|
|browsers||Browser properties by
In order to view credit usage for warehouses, please refer to the Snowflake Documentation.