Skip to main content

SQL Data Explorer: Game Overrides

Updated

This article explains how to query Game Overrides campaign and variant assignment data using SQL Data Explorer. It covers two approaches (the USERS table's UNITY_CAMPAIGNS JSON column, or the EVENTS table's userAssignment event), and includes sample queries for common needs: pulling a user's variant and attribution date for a specific campaign, counting users per campaign, and counting users per campaign/variant per day.

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!

Related to

Related articles