Skip to main content

Getting Started with SQL Data Explorer in Unity Analytics

Updated

 

Procedure:

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.

 

 

Sections:

  1. Overview of SQL Data Explorer
  2. SQL Queries to Get you Started
    • Daily Sessions Started
    • New Players Per Day
    • Daily Transaction Revenue
    • Transactions by Country
    • Event Counts
  3. Tips for Working with Unity Analytics Data
  4. Using Unity’s SQL Cookbook
  5. Explore More with Unity’s SQL Data Explorer Guide

 

1. Overview of 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:

  • 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.


2. SQL Queries to Get you Started

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;

 


3. Tips for Working with Unity Analytics Data

  • Accessing Event Parameters: Use EVENT_JSON:parameterName::dataType to extract parameters from events. For example:
    sql
     
    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.


4. Using 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.

 


5. Explore More with Unity’s SQL Data Explorer Guide

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