Skip to main content

Getting Started with SQL Data Explorer in Unity Analytics

Updated

SQL Data Explorer lets you run SQL queries directly against your Unity Analytics data (stored in Snowflake) to analyze player behavior, revenue, and engagement. This guide covers the core tables, five starter queries, a few practical tips, and where to go for more advanced examples.

This guide introduces SQL Data Explorer in Unity Analytics, offering practical query examples and tips to help users get started with analyzing their game data. Whether you're new to SQL or a seasoned pro, this guide provides an accessible entry point to uncover insights from your Unity Analytics data.

What is SQL Data Explorer?

With SQL Data Explorer, the possibilities are endless—you can uncover detailed insights into player behavior, revenue trends, engagement patterns, and so much more by tapping into raw event data and aggregated tables. Whether you're exploring the smallest details or high-level metrics, this tool empowers you to ask questions and find answers tailored to your game's unique needs.

Before diving into queries, familiarize yourself with Unity Analytics' database structure (see the full SQL Data Explorer queryable tables reference for every table and column):

  • EVENTS Table: Contains granular event data, including parameters and timestamps. This is the primary table for detailed analysis.
  • USERS Table: Provides aggregated user metrics, such as lifetime activity and key performance indicators.
  • FACT_USER_SESSIONS_DAY Table: Aggregates daily session data at the user level, ideal for analyzing session trends over time.
  • FACT_EVENT_TYPE_USERS_DAY Table: Tracks event counts by user and event type on a daily basis, useful for understanding user behavior patterns.
  • FACT_WAU_USERS Table: Tracks weekly active users (WAU), enabling you to monitor user engagement on a weekly basis.
  • FACT_MAU_USERS Table: Tracks monthly active users (MAU), helpful for assessing long-term user activity and retention trends.

What SQL queries can I start with?

Daily Sessions Started (Last 30 Days):

SELECT
     EVENT_DATE,
     COUNT(DISTINCT EVENT_JSON:sessionID::STRING) AS SessionCount
FROM 
     EVENTS
WHERE 
     EVENT_NAME = 'sdkStart'
     AND EVENT_DATE > DATE(CURRENT_DATE - 30)
GROUP BY 
     EVENT_DATE
ORDER BY 
     EVENT_DATE;

New Players per Day (Last 30 Days):

SELECT 
     EVENT_DATE,
     COUNT(*) AS NewPlayers
FROM 
     EVENTS
WHERE 
     EVENT_NAME = 'newPlayer'
     AND EVENT_DATE > DATE(CURRENT_DATE - 30)
GROUP BY 
     EVENT_DATE
ORDER BY 
     EVENT_DATE;

Daily Transaction Revenue (Last 30 Days):

SELECT 
     EVENT_DATE,
     COUNT(*) AS TotalTransactions,
     SUM(EVENT_JSON:convertedProductAmount::INT) / 100 AS TotalRevenue 
     -- Amount is divided by 100 to accomodate decimal points
FROM 
     EVENTS
WHERE 
     EVENT_NAME = 'transaction'
     AND EVENT_DATE > DATE(CURRENT_DATE - 30)
GROUP BY 
     EVENT_DATE
ORDER BY 
     EVENT_DATE;

Transactions by Country (Last 30 Days):

SELECT 
     USER_COUNTRY, 
     COUNT(*) AS TransactionCount
FROM 
     EVENTS
WHERE 
     EVENT_NAME = 'transaction'
     AND EVENT_DATE > DATE(CURRENT_DATE - 30)
GROUP BY 
     USER_COUNTRY
ORDER BY 
     TransactionCount DESC;

Event Counts (Last 30 Days): (Counts all event types)

SELECT 
     EVENT_NAME, 
     COUNT(*) AS EventCount
FROM 
     EVENTS
WHERE 
     EVENT_DATE > DATE(CURRENT_DATE - 30)
GROUP BY 
     EVENT_NAME
ORDER BY 
     EventCount DESC;

What tips help when working with Unity Analytics data?

  • Accessing Event Parameters: Use EVENT_JSON:parameterName::dataType to extract parameters from events. For example:

    EVENT_JSON:levelName::STRING

    Replace "levelName" and "STRING" with the actual parameter name and appropriate data type.

  • Understanding the Data Schema: The EVENTS Table is your go-to for granular data, while aggregated tables like USERS provide high-level summaries.
  • Experiment with Queries: Combine Snowflake SQL features, such as JSON parsing and analytical functions, to perform more complex analyses.
  • Non-SELECT statements are limited: SQL Data Explorer runs on Snowflake, but non-SELECT statements and $SYSTEM commands have limited functionality there, stick to SELECT queries.

What's in Unity's SQL Cookbook?

Unity's SQL Cookbook is your go-to resource for mastering SQL in Unity Analytics. It features:

  • Prebuilt queries for common scenarios.
  • Advanced use cases for deeper insights.
  • Best practices for query optimization and schema exploration.

If you're stuck or want to refine your approach, the Cookbook is a treasure trove of guidance. Don't forget to bookmark it as you explore SQL Data Explorer.

Where can I learn more?

Unity provides an in-depth guide on using SQL Data Explorer to take your analysis further. This resource covers everything from basic queries to advanced techniques for visualizing and optimizing your data. Highlights include:

  • Filtering and grouping your data by platform, country, or version.
  • Using visualizations to tell compelling stories with your data.
  • Tips for increasing query speed and efficiency.

👉 Check out the full guide here: Use SQL Data Explorer to analyze game data.

If you're struggling to make heads or tails of any of this, feel free to Reach Out to our support team, and we'll be happy to help you out!

Related to

Related articles