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 Name	Type	Description	Example
checkpoint	date	date for which subsequent fields are aggregated	2022-01-01

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

Field Name	Type	Description	Example
game_id	int	game's unique identifier	123456
user_id	string	device identifier of the player (note the same user_id might be linked to multiple game_ids)	01234abc-999z-a1a1-b2b2-dcba98765432
player_id	string	player'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 Name	Type	Description	Example
checkpoint	date
game_id	int
user_id	string
player_id	string
event_count	int	number of events of any type for that player that day	3
is_paying	boolean	flag indicating whether the player has ever made a payment (since we have history of it) (true/null)	TRUE
is_paying_today	boolean	flag indicating whether the player has made a payment on the checkpoint date (true/null)	TRUE
is_converting_today	boolean	flag 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_paid	date	date of the player's first payment	2020-01-01
revenue_usd	float	total amount of the player's payments that day, in USD cents	99
transaction_count	int	number of payment transactions that day	1
transaction_num_count	int	number of transactions with transaction number that day	1
transaction_num_min	int	minimum transaction number that day	1
transaction_num_max	int	maximum transaction number that day	1
origin	string	how the player came into the game, e.g. acquired, organic	organic
install_ts	date	date the player installed the game	2020-01-01
cohort_week	date	first day of the week the player installed the game	2020-01-01
cohort_month	date	first day of the month the player installed the game	2020-01-01
install_publisher	string	information about installation regarding publisher	TikTok
install_site	string	information about installation regarding site	13456
install_campaign	string	information about installation regarding campaign	Earn ALL the rewards
install_adgroup	string	information about installation regarding advertisement group	Example_v433
install_ad	string	information about installation regarding advertisement	Test+moreinfo+randomstring
install_keyword	string	information about installation type as a keyword	download+mytest
country_code	string	country code for the player's country based on events (please note this may change day on day if the player is travelling)	IT
ios_id	string	IOS id	XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
ios_idfa	string	IOS identifier for advertisers	XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
ios_idfv	string	IOS identifier for vendor	XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
android_id	string	Android id	XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
google_aid	string	Android advertising id	XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
limited_ad_tracking	boolean	if True, it means the player does not want to be targeted, preventing attribution of installs to any advertising source	true
logon_gamecenter	boolean	whether the player is logged to Apple's Game Centre	true
sdk_version	string	SDK version	unity 5.1.11
engine_version	string	engine version	unity 2019.3.7
os_version	string	device's OS version	ios 15.5
manufacturer	string	device's manufacturer	apple
device	string	device model	iPhone8,1
platform	string	platform e.g. ios, android	ios
build	string	game build	1.0
connection_type	string	connection, e.g. lan, wwan, wifi, offline	wwan
jailbroken	boolean	whether the player has jailbreaking (process of removing all restrictions imposed on an IOS device) enabled	false
session_count	int	number of unique sessions for that player that day	4
session_length_min	int	minimum session length that day	2
session_length_max	int	maximum session length that day	12
session_length_sum	bigint	total playtime for that player that day	24
session_length_sum2	bigint	sum of each squared session length that day	576
session_num_min	int	minimum session number that day	1
session_num_max	int	maximum session number that day	3
error_debug_count	int	number of debug level errors that day	43
error_info_count	int	number of info level errors that day	2
error_warning_count	int	number of warning level errors that day	2
error_error_count	int	number of error level errors that day	1
error_critical_count	int	number of critical level errors that day	0
ab_test_id	string	A/B Testing experiment identifier in case the player is participating in an A/B Test	ABCDefghIJKLmnopLKJIhgef
ab_test_variant_id	string	A/B Testing variant identifier in case the player is participating in an A/B Test	ctrl
ad_impressions	int	number of ads shown that day	5
ad_clicks	int	number of ads clicked that day	2
ad_ilrd_count	int	number of ad events having publisher revenue greater than 0	4
ad_ilrd_revenue	float	ad revenue attributed from the publisher data (in cents)	1
validated_revenue_usd	float	total amount of the player's validated payments that day, in USD cents	99
validated_transaction_count	int	number of validated payment transactions that day	1
completed_session_count	int	number of unique sessions (with a session_end event) for that player that day	4
user_id_ext	string

payment_checkpoint

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

Field Name	Type	Description	Example
checkpoint	date
game_id	int
user_id	string
player_id	string
is_converting	boolean	flag indicating whether it's the first time the player is making a payment (since we have history of it)	TRUE
first_paid	date	date of the player's first payment	2020-01-01
currency	string	currency code (e.g. GBP) in which the payment was made	GBP
amount	float	amount of the payment transaction, in local currency cents	199
amount_usd	float	amount of the payment transaction, in USD cents	247.34
cart_type	string	cart type, e.g. IAP	iap
item_type	string	item type bought, depending on the game, e.g. "LivesPack"	LivesPack
item_id	string	item id bought, depending on the game, e.g. "life_x48"	life_x48
transaction_num	int	number of the transaction	1
receipt_valid	boolean	whether 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_num	int	session number in which the payment occurred	5

adactivity_checkpoint

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

Field Name	Type	Description	Example
checkpoint	date
game_id	int
user_id	string
player_id	string
ad_sdk_name	string	Name of the ad provider e.g. "MoPub"	MoPub
ad_type	string	Type of Ad, e.g. "Video"	interstitial
ad_placement	string	Placement/ identifier of ad within the game, e.g. "end_of_game"	missionEnd
ad_impressions	int	number of ads where ad_action is "show", i.e ads shown to a player	24
ad_clicks	int.	number of ads where ad_action is "clicked", i.e ads clicked by a player	0

impression_checkpoint

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

Field Name	Type	Description	Example
checkpoint	date
game_id	int
user_id	string
player_id	string
ad_network_name	string	the ad network’s name, 'undisclosed' if we do not have permission from the ad networks to share the information.	mopub
adunit_name	string	Ad unit name	Mr Bean: SD (BN) (Placement - GamePlay)
adgroup_name	string	Ad Group (line item) name 'undisclosed' if we do not have permission from the ad networks to share the information.	undisclosed
adgroup_type	string	An enumeration of the possible ad group types	network
adunit_format	string	Format of the ad unit as defined by the Ad network	Banner
ad_ilrd_count	int	Total number of impression events as seen on a given day	36
ad_ilrd_revenue	float	sum of publisher revenue in usd cents	0.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 Name	Type	Description	Example
checkpoint	date
game_id	int
user_id	string
player_id	string
flow_type	string	either Sink (virtual currency lost) or Source (virtual currency gained)	Sink
resource_currency	string	name of the virtual currency used, e.g. Gems	Gems
item_type	string	item type, depending on the game, e.g. "Armour"	Armour
item_id	string	item id, depending on the game, e.g. "IronArmour"	IronArmour
resource_amount_sum	float	sum of virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")	-60
resource_amount_avg	float	average virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")	-20
resource_amount_min	float	min virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")	-30
resource_amount_max	float	max virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")	-10
resource_transaction_count	int	number 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 Name	Type	Description	Example
checkpoint	date
game_id	int
user_id	string
player_id	string
attempt_num	int	maximum attempt number to complete that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")	6
score	int	average score for that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")	70
score_count	int	number of score measurements recorded while trying that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")	2
score_min	int	minimum score obtained while trying that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")	50
score_max	int	maximum score obtained while trying that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")	91
score_sum	int	sum of all the scores obtained while trying that particular event id (event_id e.g. "Fail:Universe1:Planet1:Quest1")	141
progression_status	string	either Start, Complete or Fail (the level)	Complete
progression_1	string	first progression level aggregation as per the game, e.g. Universe1	Universe1
progression_2	string	second progression level aggregation as per the game, e.g. Planet1	Planet1
progression_3	string	third progression level aggregation as per the game, e.g. Quest1	Quest1

session_checkpoint

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

Field Name	Type	Description	Example
checkpoint	date
game_id	int
user_id	string
player_id	string
session_id	string	session's unique identifier	abcdefgh-ij12-klm3-45no-6789pqrstuvw
session_start_ts	timestamp	arrival timestamp from the first/earliest event within that session	2020-01-02 00:00:00 UTC
session_length	int	length of that session in seconds	181
session_num	int	session number for that player	3

Events

adactivty_event

Name	Type	Description	Example
event_dt	date	Date of the events	2022-01-01
event_client_ts	timestamp	Timestamp for which the event occurred	2022-01-01 00:01:01 UTC
event_arrival_ts	timestamp	Timestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)	2022-01-01 00:01:01 UTC
game_id	int	game's unique identifier	123456
user_id	string	device identifier of the player (note the same user_id might be linked to multiple game_ids)	01234abc-999z-a1a1-b2b2-dcba98765432
player_id	string	player'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_id	string	Session's unique identifier	8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
ad_sdk_name	string	Name of the ad provider	default
ad_placement	string	Placement/identifier of the ad within the game	default
ad_type	string	Type of ad	(video, rewarded_video, playable, interstitial, offer_wall, banner)
ad_action	string	Action made in relation to the ad	(clicked, show, failed_show, reward_received)
ad_fail_show_reason	string	Why the ad failed to show	(unknown, offline, no_fill, internal_error, invalid_request, unable_to_precache)
ad_duration	int	Duration in milliseconds that the ad was shown for	5000
custom_01	string	Custom field 1	online
custom_02	string	Custom field 2	multiplayer
custom_03	string	Custom field 3	coop
custom_fields	string	JSON string	{"ball_color":"red"}

design_event

Name	Type	Description	Example
event_dt	date	Date of the events	2022-01-01
event_client_ts	timestamp	Timestamp for which the event occurred	2022-01-01 00:01:01 UTC
event_arrival_ts	timestamp	Timestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)	2022-01-01 00:01:01 UTC
game_id	int	game's unique identifier	123456
user_id	string	device identifier of the player (note the same user_id might be linked to multiple game_ids)	01234abc-999z-a1a1-b2b2-dcba98765432
player_id	string	player'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_id	string	Session's unique identifier	8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
event_id_01	string	First layer of custom event	engage
event_id_02	string	Second layer of custom event	boss
event_id_03	string	Third layer of custom event	id
event_id_04	string	Fourth layer of custom event	coop
event_id_05	string	Fifth layer of custom event	hard
custom_01	string	Custom field 1	online
custom_02	string	Custom field 2	multiplayer
custom_03	string	Custom field 3	coop
value	float	Custom value	33
custom_fields	string	JSON string	{"ball_color": "red"}

error_event

Name	Type	Description	Example
event_dt	date	Date of the events	2022-01-01
event_client_ts	timestamp	Timestamp for which the event occurred	2022-01-01 00:01:01 UTC
event_arrival_ts	timestamp	Timestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)	2022-01-01 00:01:01 UTC
game_id	int	game's unique identifier	123456
user_id	string	device identifier of the player (note the same user_id might be linked to multiple game_ids)	01234abc-999z-a1a1-b2b2-dcba98765432
player_id	string	player'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_id	string	Session's unique identifier	8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
severity	string	Type of error	(debug, info, warning, error, critical)
message	string	Message attached to the error	AndroidJavaException: (...)
custom_01	string	Custom field 1	online
custom_02	string	Custom field 2	multiplayer
custom_03	string	Custom field 3	coop
value	float	Custom value	33
custom_fields	string	JSON string	{"ball_color": "red"}

impression_event

Name	Type	Description	Example
event_dt	date	Date of the events	2022-01-01
event_client_ts	timestamp	Timestamp for which the event occurred	2022-01-01 00:01:01 UTC
event_arrival_ts	timestamp	Timestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)	2022-01-01 00:01:01 UTC
game_id	int	game's unique identifier	123456
user_id	string	device identifier of the player (note the same user_id might be linked to multiple game_ids)	01234abc-999z-a1a1-b2b2-dcba98765432
player_id	string	player'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_id	string	Session's unique identifier	8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
ad_network_name	string	the ad network’s name, 'undisclosed' if we do not have permission from the ad networks to share the information.	mopub
adunit_name	string	Ad unit name	Mr Bean: SD (BN) (Placement - GamePlay)
adgroup_name	string	Ad Group (line item) name 'undisclosed' if we do not have permission from the ad networks to share the information.	undisclosed
adgroup_name	string	Ad Group (line item) name 'undisclosed' if we do not have permission from the ad networks to share the information.	undisclosed
adunit_format	string	Format of the ad unit as defined by the Ad network	Banner
publisher_revenue_usd_cents	float	Publisher revenue in usd cents	0.378
custom_01	string	Custom field 1	online
custom_02	string	Custom field 2	multiplayer
custom_03	string	Custom field 3	coop
value	float	Custom value	33
custom_fields	string	JSON string	{"ball_color": "red"}

payment_event

Name	Type	Description	Example
event_dt	date	Date of the events	2022-01-01
event_client_ts	timestamp	Timestamp for which the event occurred	2022-01-01 00:01:01 UTC
event_arrival_ts	timestamp	Timestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)	2022-01-01 00:01:01 UTC
game_id	int	game's unique identifier	123456
user_id	string	device identifier of the player (note the same user_id might be linked to multiple game_ids)	01234abc-999z-a1a1-b2b2-dcba98765432
player_id	string	player'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_id	string	Session's unique identifier	8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
is_converting	boolean	Flag indicating whether it's the first time the player is making a payment	true
first_paid date	date	Date for which the event occurred	2022-01-01
currency	string	Currency code (e.g. GBP) in which the payment was made	USD
amount	float	Amount of the transaction, in local currency cents	199.0
amount_usd	float	Amount of the payment transaction, in USD	199.0
cart_type	string	e.g IAP	iap
item_type	string	Item type bought, depending on the game	LivesPack
item_id	string	Item id bought, depending on the game	life_x48
transaction_num	int	Number of the transaction	1
receipt_valid	boolean	Whether the transaction receipt has been validated or not (might not be available at the time of the event)	true
custom_01	string	Custom field 1	online
custom_02	string	Custom field 2	multiplayer
custom_03	string	Custom field 3	coop
value	float	Custom value	33
custom_fields	string	JSON string	{"ball_color": "red"}

progression_event

Name	Type	Description	Example
event_dt	date	Date of the events	2022-01-01
event_client_ts	timestamp	Timestamp for which the event occurred	2022-01-01 00:01:01 UTC
event_arrival_ts	timestamp	Timestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)	2022-01-01 00:01:01 UTC
game_id	int	game's unique identifier	123456
user_id	string	device identifier of the player (note the same user_id might be linked to multiple game_ids)	01234abc-999z-a1a1-b2b2-dcba98765432
player_id	string	player'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_id	string	Session's unique identifier	8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
progression_status	string	Either Start, Complete or Fail	Start
progression_1	string	First progression level aggregation as per the game	Universe1
progression_2	string	Second progression level aggregation as per the game	Planet1
progression_3	string	Third progression level aggregation as per the game	Quest1
score	int	Average score for that particular event id	45
attempt_num	int	Maximum attempt number to complete that particular event id	5
custom_01	string	Custom field 1	online
custom_02	string	Custom field 2	multiplayer
custom_03	string	Custom field 3	coop
value	float	Custom value	33
custom_fields	string	JSON string	{"ball_color": "red"}

resource_event

Name	Type	Description	Example
event_dt	date	Date of the events	2022-01-01
event_client_ts	timestamp	Timestamp for which the event occurred	2022-01-01 00:01:01 UTC
event_arrival_ts	timestamp	Timestamp for which the event arrived at GA (discrepancy might be for users being offline, for example)	2022-01-01 00:01:01 UTC
game_id	int	game's unique identifier	123456
user_id	string	device identifier of the player (note the same user_id might be linked to multiple game_ids)	01234abc-999z-a1a1-b2b2-dcba98765432
player_id	string	player'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_id	string	Session's unique identifier	8xxxxxxx-4xxx-4xxx-4xxx-12xxxxxxxxxx
flow_type	string	either Sink (virtual currency lost) or Source (virtual currency gained)	Sink
currency	string	name of the virtual currency used, e.g. Gems	Gems
amount	float	Amount of virtual currency for that particular event id (event_id e.g. "Sink:Gems:Armour:IronArmour")	-20
item_type	string	item type, depending on the game, e.g. "Armour"	Armour
item_id	string	item id, depending on the game, e.g. "IronArmour"	IronArmour
custom_01	string	Custom field 1	online
custom_02	string	Custom field 2	multiplayer
custom_03	string	Custom field 3	coop
value	float	Custom value	33
custom_fields	string	JSON 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.