Skip to main content

Schemas

Checkpoint

All tables are updated daily, adding the aggregated results from the previous day. That is the first common denominator/column between all tables, the date of the event:

Field NameTypeDescriptionExample
checkpointdatedate for which subsequent fields are aggregated2022-01-01

There are an extra three columns which are also common between all tables:

Field NameTypeDescriptionExample
game_idintgame's unique identifier123456
user_idstringdevice identifier of the player (note the same user_id might be linked to multiple game_ids)01234abc-999z-a1a1-b2b2-dcba98765432
player_idstringplayer's unique identifier related to a single game, combination of game_id and user_id (note the same player_id will only be linked to a single game_id)0a1b1c2d3e5f8g13h21i34j55k89l144

The remaining columns are specific to each table, and the aggregation levels are also different (apart from the daily aggregation common denominator). Below you can find the details for each of them:

player_checkpoint

This is the main table and it’s aggregated at the player level, containing the daily summary stats for each player.

Field NameTypeDescriptionExample
checkpointdate
game_idint
user_idstring
player_idstring
event_countintnumber of events of any type for that player that day3
is_payingbooleanflag indicating whether the player has ever made a payment (since we have history of it) (true/null)TRUE
is_paying_todaybooleanflag indicating whether the player has made a payment on the checkpoint date (true/null)TRUE
is_converting_todaybooleanflag indicating whether the checkpoint date is the first time the player is making a payment (since we have history of it) (true / false [paid today but not for the 1st time] / null [did not pay today, hence can't be converting today])TRUE
first_paiddatedate of the player's first payment2020-01-01
revenue_usdfloattotal amount of the player's payments that day, in USD cents99
transaction_countintnumber of payment transactions that day1
transaction_num_countintnumber of transactions with transaction number that day1
transaction_num_minintminimum transaction number that day1
transaction_num_maxintmaximum transaction number that day1
originstringhow the player came into the game, e.g. acquired, organicorganic
install_tsdatedate the player installed the game2020-01-01
cohort_weekdatefirst day of the week the player installed the game2020-01-01
cohort_monthdatefirst day of the month the player installed the game2020-01-01
install_publisherstringinformation about installation regarding publisherTikTok
install_sitestringinformation about installation regarding site13456
install_campaignstringinformation about installation regarding campaignEarn ALL the rewards
install_adgroupstringinformation about installation regarding advertisement groupExample_v433
install_adstringinformation about installation regarding advertisementTest+moreinfo+randomstring
install_keywordstringinformation about installation type as a keyworddownload+mytest
country_codestringcountry code for the player's country based on events (please note this may change day on day if the player is travelling)IT
ios_idstringIOS idXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
ios_idfastringIOS identifier for advertisersXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
ios_idfvstringIOS identifier for vendorXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
android_idstringAndroid idXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
google_aidstringAndroid advertising idXXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
limited_ad_trackingbooleanif True, it means the player does not want to be targeted, preventing attribution of installs to any advertising sourcetrue
logon_gamecenterbooleanwhether the player is logged to Apple's Game Centretrue
sdk_versionstringSDK versionunity 5.1.11
engine_versionstringengine versionunity 2019.3.7
os_versionstringdevice's OS versionios 15.5
manufacturerstringdevice's manufacturerapple
devicestringdevice modeliPhone8,1
platformstringplatform e.g. ios, androidios
buildstringgame build1.0
connection_typestringconnection, e.g. lan, wwan, wifi, offlinewwan
jailbrokenbooleanwhether the player has jailbreaking (process of removing all restrictions imposed on an IOS device) enabledfalse
session_countintnumber of unique sessions for that player that day4
session_length_minintminimum session length that day2
session_length_maxintmaximum session length that day12
session_length_sumbiginttotal playtime for that player that day24
session_length_sum2bigintsum of each squared session length that day576
session_num_minintminimum session number that day1
session_num_maxintmaximum session number that day3
error_debug_countintnumber of debug level errors that day43
error_info_countintnumber of info level errors that day2
error_warning_countintnumber of warning level errors that day2
error_error_countintnumber of error level errors that day1
error_critical_countintnumber of critical level errors that day0
ab_test_idstringA/B Testing experiment identifier in case the player is participating in an A/B TestABCDefghIJKLmnopLKJIhgef
ab_test_variant_idstringA/B Testing variant identifier in case the player is participating in an A/B Testctrl
ad_impressionsintnumber of ads shown that day5
ad_clicksintnumber of ads clicked that day2
ad_ilrd_countintnumber of ad events having publisher revenue greater than 04
ad_ilrd_revenuefloatad revenue attributed from the publisher data (in cents)1
validated_revenue_usdfloattotal amount of the player's validated payments that day, in USD cents99
validated_transaction_countintnumber of validated payment transactions that day1
completed_session_countintnumber of unique sessions (with a session_end event) for that player that day4
user_id_extstring

payment_checkpoint

This table is aggregated at the event level and contains all Business Events (such as Transactions)

Field NameTypeDescriptionExample
checkpointdate
game_idint
user_idstring
player_idstring
is_convertingbooleanflag indicating whether it's the first time the player is making a payment (since we have history of it)TRUE
first_paiddatedate of the player's first payment2020-01-01
currencystringcurrency code (e.g. GBP) in which the payment was madeGBP
amountfloatamount of the payment transaction, in local currency cents199
amount_usdfloatamount of the payment transaction, in USD cents247.34
cart_typestringcart type, e.g. IAPiap
item_typestringitem type bought, depending on the game, e.g. "LivesPack"LivesPack
item_idstringitem id bought, depending on the game, e.g. "life_x48"life_x48
transaction_numintnumber of the transaction1
receipt_validbooleanwhether the transaction receipt has been validated or not (note, in many cases the validation might happen later on, hence at the time of the event it might not be available)FALSE
session_numintsession number in which the payment occurred5

adactivity_checkpoint

This table is aggregated at the event level and contains all ad events, i.e. ad activity

Field NameTypeDescriptionExample
checkpointdate
game_idint
user_idstring
player_idstring
ad_sdk_namestringName of the ad provider e.g. "MoPub"MoPub
ad_typestringType of Ad, e.g. "Video"interstitial
ad_placementstringPlacement/ identifier of ad within the game, e.g. "end_of_game"missionEnd
ad_impressionsintnumber of ads where ad_action is "show", i.e ads shown to a player24
ad_clicksint.number of ads where ad_action is "clicked", i.e ads clicked by a player0

impression_checkpoint

This table is aggregated at the event level and contains all impression events, i.e.impressions associated with ads.

Field NameTypeDescriptionExample
checkpointdate
game_idint
user_idstring
player_idstring
ad_network_namestringthe ad network’s name, 'undisclosed' if we do not have permission from the ad networks to share the information.mopub
adunit_namestringAd unit nameMr Bean: SD (BN) (Placement - GamePlay)
adgroup_namestringAd Group (line item) name 'undisclosed' if we do not have permission from the ad networks to share the information.undisclosed
adgroup_typestringAn enumeration of the possible ad group typesnetwork
adunit_formatstringFormat of the ad unit as defined by the Ad networkBanner
ad_ilrd_countintTotal number of impression events as seen on a given day36
ad_ilrd_revenuefloatsum of publisher revenue in usd cents0.378

resource_checkpoint

This table is aggregated at the event ID level and contains all Resource Events (such as in game currency spent and gained).

Field NameTypeDescriptionExample
checkpointdate
game_idint
user_idstring
player_idstring
flow_typestringeither Sink (virtual currency lost) or Source (virtual currency gained)Sink
resource_currencystringname of the virtual currency used, e.g. GemsGems
item_typestringitem type, depending on the game, e.g. "Armour"Armour
item_idstringitem id, depending on the game, e.g. "IronArmour"IronArmour
resource_amount_sumfloatsum of virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")-60
resource_amount_avgfloataverage virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")-20
resource_amount_minfloatmin virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")-30
resource_amount_maxfloatmax virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")-10
resource_transaction_countintnumber of events with that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")3

progression_checkpoint

This table is aggregated at the event ID level and contains all Progression Events (such as level complete and level fail)

Field NameTypeDescriptionExample
checkpointdate
game_idint
user_idstring
player_idstring
attempt_numintmaximum attempt number to complete that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")6
scoreintaverage score for that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")70
score_countintnumber of score measurements recorded while trying that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")2
score_minintminimum score obtained while trying that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")50
score_maxintmaximum score obtained while trying that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")91
score_sumintsum of all the scores obtained while trying that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")141
progression_statusstringeither Start, Complete or Fail (the level)Complete
progression_1stringfirst progression level aggregation as per the game, e.g. Universe1Universe1
progression_2stringsecond progression level aggregation as per the game, e.g. Planet1Planet1
progression_3stringthird progression level aggregation as per the game, e.g. Quest1Quest1

session_checkpoint

This table is aggregated at the session level and contains all events with session_id.

Field NameTypeDescriptionExample
checkpointdate
game_idint
user_idstring
player_idstring
session_idstringsession's unique identifierabcdefgh-ij12-klm3-45no-6789pqrstuvw
session_start_tstimestamparrival timestamp from the first/earliest event within that session2020-01-02 00:00:00 UTC
session_lengthintlength of that session in seconds181
session_numintsession number for that player3

Events

adactivty_event

NameTypeDescriptionExample
event_dtdateDate of the events2022-01-01
event_client_tstimestampTimestamp for which the event occurred2022-01-01 00:01:01 UTC
event_arrival_tstimestampTimestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)2022-01-01 00:01:01 UTC
game_idintgame's unique identifier123456
user_idstringdevice identifier of the player (note the same user_id might be linked to multiple game_ids)01234abc-999z-a1a1-b2b2-dcba98765432
player_idstringplayer's unique identifier related to a single game, combination of game_id and user_id (note the same player_id will only be linked to a single game_id)0a1b1c2d3e5f8g13h21i34j55k89l144
session_idstringSession's unique identifier8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
ad_sdk_namestringName of the ad providerdefault
ad_placementstringPlacement/identifier of the ad within the gamedefault
ad_typestringType of ad(video, rewarded_video, playable, interstitial, offer_wall, banner)
ad_actionstringAction made in relation to the ad(clicked, show, failed_show, reward_received)
ad_fail_show_reasonstringWhy the ad failed to show(unknown, offline, no_fill, internal_error, invalid_request, unable_to_precache)
ad_durationintDuration in milliseconds that the ad was shown for5000
custom_01stringCustom field 1online
custom_02stringCustom field 2multiplayer
custom_03stringCustom field 3coop
custom_fieldsstringJSON string{"ball_color":"red"}

design_event

NameTypeDescriptionExample
event_dtdateDate of the events2022-01-01
event_client_tstimestampTimestamp for which the event occurred2022-01-01 00:01:01 UTC
event_arrival_tstimestampTimestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)2022-01-01 00:01:01 UTC
game_idintgame's unique identifier123456
user_idstringdevice identifier of the player (note the same user_id might be linked to multiple game_ids)01234abc-999z-a1a1-b2b2-dcba98765432
player_idstringplayer's unique identifier related to a single game, combination of game_id and user_id (note the same player_id will only be linked to a single game_id)0a1b1c2d3e5f8g13h21i34j55k89l144
session_idstringSession's unique identifier8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
event_id_01stringFirst layer of custom eventengage
event_id_02stringSecond layer of custom eventboss
event_id_03stringThird layer of custom eventid
event_id_04stringFourth layer of custom eventcoop
event_id_05stringFifth layer of custom eventhard
custom_01stringCustom field 1online
custom_02stringCustom field 2multiplayer
custom_03stringCustom field 3coop
valuefloatCustom value33
custom_fieldsstringJSON string{"ball_color": "red"}

error_event

NameTypeDescriptionExample
event_dtdateDate of the events2022-01-01
event_client_tstimestampTimestamp for which the event occurred2022-01-01 00:01:01 UTC
event_arrival_tstimestampTimestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)2022-01-01 00:01:01 UTC
game_idintgame's unique identifier123456
user_idstringdevice identifier of the player (note the same user_id might be linked to multiple game_ids)01234abc-999z-a1a1-b2b2-dcba98765432
player_idstringplayer's unique identifier related to a single game, combination of game_id and user_id (note the same player_id will only be linked to a single game_id)0a1b1c2d3e5f8g13h21i34j55k89l144
session_idstringSession's unique identifier8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
severitystringType of error(debug, info, warning, error, critical)
messagestringMessage attached to the errorAndroidJavaException: (...)
custom_01stringCustom field 1online
custom_02stringCustom field 2multiplayer
custom_03stringCustom field 3coop
valuefloatCustom value33
custom_fieldsstringJSON string{"ball_color": "red"}

impression_event

NameTypeDescriptionExample
event_dtdateDate of the events2022-01-01
event_client_tstimestampTimestamp for which the event occurred2022-01-01 00:01:01 UTC
event_arrival_tstimestampTimestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)2022-01-01 00:01:01 UTC
game_idintgame's unique identifier123456
user_idstringdevice identifier of the player (note the same user_id might be linked to multiple game_ids)01234abc-999z-a1a1-b2b2-dcba98765432
player_idstringplayer's unique identifier related to a single game, combination of game_id and user_id (note the same player_id will only be linked to a single game_id)0a1b1c2d3e5f8g13h21i34j55k89l144
session_idstringSession's unique identifier8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
ad_network_namestringthe ad network’s name, 'undisclosed' if we do not have permission from the ad networks to share the information.mopub
adunit_namestringAd unit nameMr Bean: SD (BN) (Placement - GamePlay)
adgroup_namestringAd Group (line item) name 'undisclosed' if we do not have permission from the ad networks to share the information.undisclosed
adgroup_namestringAd Group (line item) name 'undisclosed' if we do not have permission from the ad networks to share the information.undisclosed
adunit_formatstringFormat of the ad unit as defined by the Ad networkBanner
publisher_revenue_usd_centsfloatPublisher revenue in usd cents0.378
custom_01stringCustom field 1online
custom_02stringCustom field 2multiplayer
custom_03stringCustom field 3coop
valuefloatCustom value33
custom_fieldsstringJSON string{"ball_color": "red"}

payment_event

NameTypeDescriptionExample
event_dtdateDate of the events2022-01-01
event_client_tstimestampTimestamp for which the event occurred2022-01-01 00:01:01 UTC
event_arrival_tstimestampTimestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)2022-01-01 00:01:01 UTC
game_idintgame's unique identifier123456
user_idstringdevice identifier of the player (note the same user_id might be linked to multiple game_ids)01234abc-999z-a1a1-b2b2-dcba98765432
player_idstringplayer's unique identifier related to a single game, combination of game_id and user_id (note the same player_id will only be linked to a single game_id)0a1b1c2d3e5f8g13h21i34j55k89l144
session_idstringSession's unique identifier8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
is_convertingbooleanFlag indicating whether it's the first time the player is making a paymenttrue
first_paid datedateDate for which the event occurred2022-01-01
currencystringCurrency code (e.g. GBP) in which the payment was madeUSD
amountfloatAmount of the transaction, in local currency cents199.0
amount_usdfloatAmount of the payment transaction, in USD199.0
cart_typestringe.g IAPiap
item_typestringItem type bought, depending on the gameLivesPack
item_idstringItem id bought, depending on the gamelife_x48
transaction_numintNumber of the transaction1
receipt_validbooleanWhether the transaction receipt has been validated or not (might not be available at the time of the event)true
custom_01stringCustom field 1online
custom_02stringCustom field 2multiplayer
custom_03stringCustom field 3coop
valuefloatCustom value33
custom_fieldsstringJSON string{"ball_color": "red"}

progression_event

NameTypeDescriptionExample
event_dtdateDate of the events2022-01-01
event_client_tstimestampTimestamp for which the event occurred2022-01-01 00:01:01 UTC
event_arrival_tstimestampTimestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)2022-01-01 00:01:01 UTC
game_idintgame's unique identifier123456
user_idstringdevice identifier of the player (note the same user_id might be linked to multiple game_ids)01234abc-999z-a1a1-b2b2-dcba98765432
player_idstringplayer's unique identifier related to a single game, combination of game_id and user_id (note the same player_id will only be linked to a single game_id)0a1b1c2d3e5f8g13h21i34j55k89l144
session_idstringSession's unique identifier8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
progression_statusstringEither Start, Complete or FailStart
progression_1stringFirst progression level aggregation as per the gameUniverse1
progression_2stringSecond progression level aggregation as per the gamePlanet1
progression_3stringThird progression level aggregation as per the gameQuest1
scoreintAverage score for that particular event id45
attempt_numintMaximum attempt number to complete that particular event id5
custom_01stringCustom field 1online
custom_02stringCustom field 2multiplayer
custom_03stringCustom field 3coop
valuefloatCustom value33
custom_fieldsstringJSON string{"ball_color": "red"}

resource_event

NameTypeDescriptionExample
event_dtdateDate of the events2022-01-01
event_client_tstimestampTimestamp for which the event occurred2022-01-01 00:01:01 UTC
event_arrival_tstimestampTimestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)2022-01-01 00:01:01 UTC
game_idintgame's unique identifier123456
user_idstringdevice identifier of the player (note the same user_id might be linked to multiple game_ids)01234abc-999z-a1a1-b2b2-dcba98765432
player_idstringplayer's unique identifier related to a single game, combination of game_id and user_id (note the same player_id will only be linked to a single game_id)0a1b1c2d3e5f8g13h21i34j55k89l144
session_idstringSession's unique identifier8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
flow_typestringeither Sink (virtual currency lost) or Source (virtual currency gained)Sink
currencystringname of the virtual currency used, e.g. GemsGems
amountfloatAmount of virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")-20
item_typestringitem type, depending on the game, e.g. "Armour"Armour
item_idstringitem id, depending on the game, e.g. "IronArmour"IronArmour
custom_01stringCustom field 1online
custom_02stringCustom field 2multiplayer
custom_03stringCustom field 3coop
valuefloatCustom value33
custom_fieldsstringJSON string{"ball_color": "red"}

Billing

Data Warehouse has a billing view, allowing you to track your query spending.

This view is a subset of the BigQuery billing table, and you can find all the fields and definitions in their documentation.