SQL Data Explorer: Game Overrides

Overview

In this article, you will find some helpful information regarding Game Overrides, and how to query against them.

Data Structure

Campaign data is stored in the USERS table, under the UNITY_CAMPAIGNS column in a variant (JSON) format. This data consists of 2 unique keys for every campaign the user is part of:

• variant<campaignId> - The value of this key is the display name of the variant the user is in.
• attributionDate<campaignId> - The value of this key is the timestamp that the user entered the campaign.

For example, to parse the variant the user is in for a campaign, you would select the field:

UNITY_CAMPAIGNS:"variant<campaignId>"::string variant

There is Snowflake documentation on parsing semi-structured data (like JSON) here.

Replace <campaignId> with the actual campaignID of whichever Game Override you're trying to query, for example, if the campaignID is 123456the query would look like this:

select 
   user_id, 
   UNITY_CAMPAIGNS:"attributionDate123456"::string attribution_date, 
   UNITY_CAMPAIGNS:"variant123456"::string variant 
from 
   users 
where  
   users.UNITY_CAMPAIGNS != '{}' 
   and UNITY_CAMPAIGNS:"variant<campaignId>" is not null 
limit 
   10

You can find more information in the SQL Data Explorer documentation for the USERS table.

Sample Queries

Count of users per campaign

Returns a simple count of users for each campaign.

with user_keys as (
   select 
     user_id,
     keys.value::string key
   from 
     users,
     table(flatten(input => object_keys(users.UNITY_CAMPAIGNS))) keys
   WHERE 
     users.UNITY_CAMPAIGNS != '{}'
)
   select 
     substr(key,8) campaign,
     count(distinct user_id) users 
   from 
     user_keys
   where 
     STARTSWITH(key, 'variant')
   group by 
     campaign

Sample user data for a campaign

Returns the variant and attribution date for each user for a given campaign.

select 
   user_id,
   UNITY_CAMPAIGNS:"attributionDate<campaignId>"::string attribution_date,
   UNITY_CAMPAIGNS:"variant<campaignId>"::string variant 
from 
   users
where 
   users.UNITY_CAMPAIGNS != '{}'
   and UNITY_CAMPAIGNS:"variant<campaignId>" is not null
limit 
   10

User campaign data CTE

Returns a row for each user_id and campaign, along with their variant and attribution date for that campaign.

with user_campaigns as (
   select 
     user_id, 
     substr(keys.value::string,8) campaign,
     get(UNITY_CAMPAIGNS, keys.value::string)::string variant,
     get(UNITY_CAMPAIGNS, 'attributionDate'||substr(keys.value::string,8))::timestamp attributionDate
   from
     users,
     table(flatten(input => object_keys(UNITY_CAMPAIGNS))) keys
   where 
     UNITY_CAMPAIGNS != '{}'
     and STARTSWITH(keys.value::string, 'variant')
)

If you'd rather query against the Events table, there are some parameters there within the userAssignment event that can be used, similarly:

select
   EVENT_JSON:rcEnvironmentID::STRING,
   EVENT_JSON:rcCampaignID::STRING,
   EVENT_JSON:rcAssignmentID::STRING,
   EVENT_JSON:rcRuleID::STRING,
   EVENT_JSON:rcVariantID::STRING
from
   EVENTS
where
   EVENT_JSON:rcCampaignID::STRING is not null

Count of users per campaign (and variant)

Returns the number of unique users per campaign/variant, per day.

select 
   EVENT_DATE,
   EVENT_JSON:rcCampaignID::STRING CAMPAIGNID,
   EVENT_JSON:rcVariantID::STRING VARIANTID,
   count(distinct USER_ID) 
from 
   events 
where 
   CAMPAIGNID = '<campaignId>'
group by 
   EVENT_DATE,
   CAMPAIGNID,
   VARIANTID
order by 
   EVENT_DATE,
   CAMPAIGNID,
   VARIANTID

If you have any feedback/questions/thoughts about this, we encourage you to reach out to our support team!