Checkpoint 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
first_build	string	first game build the user was 'seen' with	1.0.1
build	string	game build	1.0.1
builds	string	game builds string array	1.0.1, 1.0.2
app_versions	string	app_versions string array (ios_app_version for ios and android_app_version for android)	1.0.1, 1.0.2
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_title	string	A/B Testing experiment name in case the player is participating in an A/B Test	Test button size
ab_test_variant_id	string	A/B Testing variant identifier in case the player is participating in an A/B Test	ctrl
ab_test_variant_name	string	A/B Testing variant identifier in case the player is participating in an A/B Test	MyBaseline
ab_admission_date	string	Date the player was enrolled in the A/B Test	2025-01-01
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