Documentation

Support

Analytics

Analytics

Query parameter values with SQL Data Explorer

Learn how to query custom event parameters in SQL Data Explorer with practical examples.
Read time 3 minutesLast updated a day ago
Use the SQL Data Explorer tool to analyze the values of specific parameters. Below are multiple ways of analyzing a parameter value with practical examples and comments on the queries to help you better understand how to replicate those for your game.
  • Every example below uses the same context: an RPG game where the player can select a character, start and complete dungeons, and use potions while they’re in a dungeon to recover health.
  • When replicating those queries, make sure to replace the highlighted query items to adapt them to your event names and parameters.

Queries

1. Average potions used over a specific time range (day by day)

with potionsStats as (selectEVENT_DATE,EVENT_JSON:potionsUsed::Integer as potionsFROM EVENTS where EVENT_DATE > CURRENT_DATE-7 and EVENT_DATE < current_date and EVENT_NAME='dungeonCompleted')select EVENT_DATE as "Date", round(avg(potions)) as "Average Potions Used"from potionsStatsgroup by EVENT_DATEorder by EVENT_DATE
Notes: 
  • potionsStats
    is our sub-query.
  • potionsUsed
    is the parameter sent with the 
    dungeonCompleted
    event to log how many potions were used in the dungeon.
  • potions
    is the name we're giving to 
    EVENT\_JSON:potionsUsed::Integer
  • 7
    defines the time range we want to analyze. In this case, the last 7 days
  • On line 8 we call:
    • The event date on the X axis.
    • A round average of 
      potions
      to get clean numbers, but you can remove it if you want to look into decimals.
  • Average Potions Used
    to have a clean name for our round average operation on the Y axis.

2. Average potions used over a specific time range (total time range)

Use the below example if you want to understand the average potions used per player to see consumption at one point in time. 
with potionsStats as (selectEVENT_NAME,EVENT_JSON:potionsUsed::Integer as potionsFROM EVENTS where EVENT_DATE > CURRENT_DATE-7 and EVENT_DATE < current_date and EVENT_NAME='dungeonCompleted')select EVENT_NAME as "Dungeon Completed", round(avg(potions)) as "Average Potions Used"from potionStatsgroup by 1

3. Average potion used per dungeon name

Use the below example to better understand where players are using potions in the game to balance your dungeons. 
with potionsAndDungeons as (selectEVENT_JSON:dungeonName::string as dungeonName,EVENT_JSON:potionsUsed::integer as potionsFROM EVENTS where EVENT_DATE > CURRENT_DATE-7 and EVENT_NAME='dungeonCompleted')select dungeonName as "Dungeon", avg(potions) as "Average potions used"from potionsAndDungeonsgroup by dungeonNameorder by dungeonName
Notes:
  • We keep the same base as the two previous queries with 
    potionsUsed
    and 
    dungeonCompleted
    .
  • We're using 
    dungeonName
    , which is the parameter (logging the name of the completed dungeon) sent with the 
    dungeonCompleted
    event. It will be used on the Y axis.

4. Percentage of character use in dungeons per character name

Use the below example to see the most popular characters in the game. 
selectEVENT_JSON:characterName::STRING as character,((count(EVENT_JSON:characterName::STRING)) / ( select count(*) from eventswhere EVENT_NAME = 'dungeonCompleted' and EVENT_DATE > current_date-7)) as percentagefrom eventswhere EVENT_NAME = 'dungeonCompleted' and EVENT_DATE > current_date-7group by characterorder by percentage desc
Notes:
  • We call 
    characterName
    as 
    character
    . This is the parameter sent with the 
    dungeonCompleted
    event which logs the chosen character. It will be used on the X axis.
  • On line 3 and 4 we divide the total number of each character name per the total number of dungeons completed to get the percentage needed on the Y axis.

5. Pivot table to count the character used per dungeon name

Use the below example to have a clear view of the number of times a character is used per dungeon name. This will help better balance characters and dungeons. 
selectEVENT_JSON:characterName::INTEGER,EVENT_JSON:dungeonName::STRING,count(EVENT_JSON:dungeonName::STRING) as dungeonCountfrom eventswhere EVENT_NAME = 'dungeonCompleted'and EVENT_DATE > current_date-7group by 1,2
Notes:
  • The SQL Data Explorer doesn't have pivot table visualizations yet, so we need to export the results to a CSV and create the visualizations on a spreadsheet.
  • We call the character name, dungeon parameters, and the count of dungeon names.
  • After processing this query, you need to:
    1. Export the results as a CSV using the "Share" button.
    2. Open the CSV file in a spreadsheet.
    3. Create a pivot table out of the data.
    4. Create a chart out of the pivot table.