Skip to main content

Game Engine Analytics

Overview​

SparkLogs has specialized support for ingesting, processing, aggregating, and analyzing metrics from games. The scale and flexibility of SparkLogs makes it a great choice for analytics, allowing you to focus on building your game and not on building and maintaining your own analytics infrastructure.

Capturing analytics events allows you to understand how players are behaving so you can optimize for various outcomes (daily activity, playtime, progression, in-app purchases and ads) and for balance (resource grants and sinks), etc.

Competing analytics solutions are often very expensive (10x) and have field, cardinality, and event count limits. SparkLogs delivers a cost-effective solution that supports unlimited fields, unlimited values, and can effortlessly scale to petabytes, supporting games with 100s of millions of daily active users (DAU) and beyond. We also provide automatic user geolocation (country), currency conversion for purchase events, and auto extracts structured fields from your unstructured logs.

Open-source plugins are available for popular game engines to make it easy to capture and send analytics events and backend logs from your games.

Our rich analytics data model lets you capture key events, including including session start and end, design, progression, real money purchases, ads, resource grants and sinks, and error or other textual events.

Once configured, daily aggregate analytics snapshots are automatically generated and retained for 7 years (paid plan) or 3 months (free plan), so you can analyze daily active users (DAU), average session and ad statistics, progression funnels, and other key game metrics.

Direct access to your aggregate and granular analytics data is available, to make it easy to visualize trends on your own dashboards and analyze with your own ML models.

Free SparkLogs accounts are available to ingest up to 25 GB/month. That's enough free quota for over 1 million analytics sessions of data every month (assuming each session records 16 events and each event is about 1.5 KiB).

Setup and Configuration​

First activate game engine analytics within your SparkLogs workspace. Login to the app, go to Workspace Settings, and click the Enable Game Engine Analytics button. This will activate automatic daily aggregate analytics snapshots.

Next, determine how you will ingest game engine analytics events into SparkLogs.

Data Ingestion and Plugins​

You can generate and ingest analytics events into SparkLogs using one of our open-source plugins for supported game engines:

  • Unreal Engine plugin: This plugin supports Unreal Engine 4 and 5, and captures analytics events and/or backend logs.

If you're using another game engine that does not yet have a supported plugin, contact us to request support for your game engine.

Also, even without a plugin you can record analytics events from your player's game clients and get the benefits of the custom analytics analysis that SparkLogs performs (e.g., daily snapshot summarization of analytics data, etc.), provided that you ingest data into SparkLogs with the same schema for analytics events.

There are a number of options to ingest analytics events:

  • Work with us to adapt a plugin's source code to your game engine. We'd like to develop plugins for other popular game engines, so chat with us on discord to discuss.
  • Submit analytics JSON events using any of our APIs (such as the HTTP+JSON API). The schema of submitted events must match known analytics event types. See also the example JSON data to understand how these events are structured in practice. Make sure to batch your events (details below).
  • Generate JSON data for analytics events as described above, but instead of submitting directly via API, send the data to a log forwarding agent of your choice (e.g., append JSON data to a local NDJSON file and configure fluentbit to read this file, batch, and submit the data to SparkLogs).
warning

If you submit analytics events data directly via one of the APIs, you must batch your events rather than sending them one at a time. We recommend batching analytics events at the client-side and sending all events every 12 seconds and immediately at the end of a game session.

If you are using a forwarding agent such as fluentbit to send analytics events (e.g., from a local NDJSON file that you append to), make sure to configure it to batch events so that events are not sent more often than every 12 seconds. For example, for fluentbit, set the service flush setting to 12 (seconds).

tip

In addition to sending game engine analytics data from your player's game clients, if you want to ship logs from your backend game servers to SparkLogs, some of our plugins have built-in support for log shipping. For other platforms, you can log your game engine output to a (rotating) file and then use any log forwarding agent to ship your logs.

Whether using a plugin or an API, you will need authentication information for the agent(s) you want to use to ingest the data. You can setup the organizations and agents you want to use for your ingested data in the SparkLogs app. You can optionally configure different authentication credentials for the different build configurations (client, editor, server) of your game or use the same auth for all. You may want to use a different SparkLogs organization for each game you develop, and also have suborganizations for development (editor) and production environments.

Confirming Data Flow​

After configuring your plugin and adding logic to record analytics events, you can confirm that the data is flowing correctly. Play a test game session so that some analytics events will be generated and transmitted. Note that analytics events are typically transmitted to SparkLogs at the end of a session or when the game engine exits.

You can then use Explore UI with the following LQL query to show analytics events:

g_analytics.type!

Conceptual Model​

All analytics events happen within the context of an analytics session, which marks the beginning and end of when a player is actively playing the game. Sessions are globally identified by a unique random session ID. Each analytics event is associated with a session and is of a particular type (session start/end, purchase, ad, resource, progression, design, log).

Individual analytics events are ingested and stored individually to preserve detail (see data model), where in SparkLogs you can explore and export your data using LQL in the Explore UI. If you're using a private-cloud service plan you'll have direct access to your data in your own Google Cloud BigQuery dataset, and you can perform your own analysis directly using BigQuery. You should make sure to use a BigQuery Enterprise reservation that auto-scales from zero slots to optimize your BigQuery query costs.

Aggregate analytics snapshots are also automatically computed once a day shortly after midnight UTC, allowing you to visualize and analyze trends such as daily active users, playtime statistics, player revenue, and other key metrics.

User Segmentation​

You can optionally store one or more "user tags" with each analytics event to help segment your users. For example, you could include an A/B testing experiment ID as a user tag, or tags identifying whether or not a user is free-to-play or has made purchases. There is no limit to the number of unique tag values you can use.

Refer to the documentation for your specific game engine plugin how to set user tags.

User tags are stored in the user_tags and user_tags_array fields of the g_analytics object in each analytics event.

In addition to segmentation, you can group users into cohorts based on first installed date, first purchased date, or other characteristics. Use either the daily snapshot table (recommended) or the materialized view with granular event data.

Types of Analytics Events​

The following types of analytics events are supported:

  • Session Start: A player is actively playing the game.
  • Session End: A player has stopped playing the game.
  • Purchase: Represents real money purchases to obtain a certain status, item, or resource in the game. If the purchase granted certain amounts of virtual currencies, you may want to also record one or more resource source events.
  • Ad: Represents one or more ad impressions and a corresponding action taken by the player.
  • Resource: Represents the granting (resource source) or using up (resource sink) of a virtual currency (gems, lives, etc.).
  • Progression: Represents starting, failing, or completing a certain part of a game.
  • Design: A design event is any in-game event you wish to record that does not fit into the other categories.
  • Log: A log event is a textual event with a severity that you want to record and associate with a session, such as an error causing a game crash, or any other information that is not captured by the other event types.

Each analytics event includes certain common fields (see analytics data model). You can also optionally provide a textual reason why a given event occurred, and you can also provide unlimited custom JSON fields (including complex values such as objects or arrays) with additional information. See also the example JSON data for each event type.

Session Start Event​

Marks the beginning of a session. This event does not have an event_id field, and there are no additional event fields beyond common fields.

Session End Event​

Marks the end of a session. This event does not have an event_id field.

Additional event fields beyond common fields:

Field NameTypeDescription
session_endedTimestampThe timestamp when the session ended.
session_duration_secsNumericThe duration of the session in seconds, from the time the session started to the time it ended.

Purchase Event​

Marks the purchase of a certain item or resource in the game using real-world currency. This event has the following additional fields:

This event takes as input an item category and item ID to identify what was purchased, as well as the amount of real-world currency spent on the purchase.

If the purchase granted certain amounts of virtual currencies, you should also record one or more resource source events.

The generated event ID is derived as follows: <item_category>:<item_id>

Additional event fields beyond common fields:

Field NameTypeDescription
item_categoryStringOptional. The category of the item that was purchased (e.g., "starter_packs", "individual_resource").
item_idStringThe unique identifier for the item that was purchased. (e.g. "pack_1", "pink_gems", "lives")
currencyStringThe currency used for the purchase (e.g., "USD", "EUR"). This is the ISO 4217 currency code. Over 200 are supported.
amountNumericThe amount of real-world currency spent on the purchase (for example one cent would be 0.01).
amount_convertedNumericThe amount converted into your preferred target currency (e.g., USD) using the average exchange rate at the time of event ingestion. Will only be present if the source currency is supported and the conversion is successful.
amount_converted_currencyStringThe ISO 4217 currency code of the target currency used for conversion. Will only be present if the source currency is supported and the conversion is successful.
transaction_numNumericThe number of the transaction since the game was first installed, starting with 1.
first_purchasedTimestampThe timestamp when the first purchase was made since the game was installed.

You can customize plugin settings to change the target currency. See the currency conversion documentation for details.

Ad Event​

Marks one or more ad impressions and a corresponding action taken by the player. You can choose to either record one event for each ad impression, or you can aggregate multiple ad impressions into a single event.

This event takes as input the ad provider, ad placement ID, and ad type, as well as the action taken by the player. Optionally you can record revenue from the ad, if known.

The generated event ID is derived as follows: <ad_provider>:<ad_placement>:<ad_type>:<ad_action>

Additional event fields beyond common fields:

Field NameTypeDescription
ad_providerStringA string identifying the ad provider. For example, unity, admob, etc. or use unknown
ad_placementStringAn ID representing where the ad was placed within you app. For example, end_of_level, treasure_chest, etc.
ad_typeStringOptional. The type of ad that was shown. One of interstitial, video, rewarded_video, banner, native, playable, app_open, offerwall, cross_promo, other, or any custom string.
ad_actionStringOptional. The action taken by the player in response to the ad. One of completed, skipped, clicked, rewarded, failed_to_show, other, or any custom string.
ad_fail_reasonStringOptional. The reason the ad failed to show, if applicable. One of no_fill, offline, network_error, invalid_request, other, or any custom string.
revenueNumericOptional. The total revenue generated from these ad impression(s), if known. Use null or 0.0 if unknown.
currencyStringOptional. The ISO 4217 code of the currency used for the ad revenue, if known (e.g., USD).
duration_secsNumericOptional. The duration of the ad in seconds, if applicable. If this event represents multiple impressions, use the average duration.
countNumericOptional. The number of ad impressions recorded in this event. Defaults to 1 if not specified.

Based on the currency conversion logic, it will also calculate the revenue_converted and revenue_converted_currency fields if applicable.

Resource Event​

Marks the granting (resource source) or using up (resource sink) of a virtual currency (gems, lives, etc.). This event takes as input the flow type, an optional item category and item ID to identify what was purchased, and the type and amount of virtual currency granted or used.

Be thoughtful about the balance between recording every resource source/sink event as it happens (which could be very often) vs aggregating the information and recording it only at key points (e.g. when losing a life or ending a level). The system can handle sending frequent events, but this could generate a lot more data and could thus cost more at large scale. Be especially thoughtful when you have 100s of thousands of users or more.

If a resource was granted as the result of a purchase using real money, then make sure to also record a corresponding purchase event as well with the same item category and item ID.

The generated event ID is derived as follows: <flow_type>:<virtual_currency>:<item_category>:<item_id>

Additional event fields beyond common fields:

Field NameTypeDescription
flow_typeStringEither "Source" or "Sink".
virtual_currencyStringThe name of the virtual currency (e.g., "pink_gems", "lives").
item_categoryStringOptional. The category of the item associated with the resource being granted or used. e.g., "currency_purchase" or "pickup"
item_idStringOptional. The ID of the item associated with the resource being granted or used. e.g., "100_pink_gems_pack" or "ad_item"
amountNumericThe amount of virtual currency granted or used. This will be enforced to be positive for sources and negative for sinks.

Progression Event​

Marks the starting, failing, or completing of a certain part of a game. Progression events form a hierarchy and can have up to N arbitrary tiers (world, region, level, segment, etc.). Note that you do not have to record a start event for a given event ID, you can just record failure or success events as you wish.

There is no fixed number of tiers for progression events. There are convenience functions to generate progression events with 1-5 tiers.

While there is no limit on depth or cardinality of the combination of tier values, be smart about how many total possible values of unique progression event IDs you create based on your planned analysis.

It will automatically compute and record the number of attempts it took to complete a given event successfully.

Progression events may optionally be associated with a numeric value as well (e.g., score).

The generated event ID is derived as follows: <status>:<tier1>:...:<tierN>

Additional event fields beyond common fields:

Field NameTypeDescription
statusStringOne of Started, Failed, or Completed.
tiersStringThe flattened value of all tiers, each separated by : (e.g., addonpack:trial_master_sword)
tiers_arrayArray of StringThe JSON array of all tier values. (e.g., ["addonpack", "trial_master_sword"])
tier1 .. tierNStringThe value of the Nth tier. (e.g., could have a tier2 field with value trial_master_sword)
valueNumericThe optional numeric value associated with the event (e.g., score achieved for the attempt)
attemptNumericThe number of attempts for this given event ID before successfully completing it. Starts at 1.

Design Event​

Marks any custom event in your game, identified by a given event ID. Event ID is a colon delimited string that forms an event hierarchy of arbitrary depth (e.g., addonpack:quest:trial_master_sword:unlocked or achievements:single_player:beat_game).

While there is no limit on depth or cardinality of event ID, be smart about how many categories you create based on your planned analysis.

Design events may optionally be associated with a numeric value as well.

Additional event fields beyond common fields (including event ID):

Field NameTypeDescription
valueNumericOptional numeric value associated with the event.

Log Event​

Records a severity and textual event that is associated with an analytics session. For example, if the game encounters a fatal error that stops gameplay, you could record the internal technical details of the failure.

Log events are a way you can explicitly record especially critical log messages on game clients, without having to capture all logs from a client. You would typically record error events, but you can record a log event of any severity.

Log events have the standard common analytics fields that records information about the current session. It also stores information in the following standard fields in the root of the JSON object so that these events have a similar schema as regular log events:

Field NameTypeDescription
severitySeverityOne of Trace, Debug, Info, Notice, Warn, Error, Critical, Fatal, Alert, Panic, Emergency.
messageStringThe textual message (could be one or more lines of text). This will be processed by AutoExtract.

Analytics Data Model​

Each analytics event is stored as a JSON object that contains a root-level JSON object field g_analytics that contains all data for the analytics event. In this g_analytics object there are fields common to all event types, as well as fields specific to each type of event.

Each event will also contain the standard fields (for example, source with the hostname of the sending machine, and client_ip_location with the two-letter ISO country code associated with the client's IP address), and any other standard fields that might be added by your game engine specific plugin (e.g., the Unreal Engine plugin generates a game_instance_id).

Analytics Common Fields​

All analytics events will have the following common fields inside of the root-level g_analytics JSON object:

Field NameTypeDescription
typeStringOne of session_start, session_end, purchase, ad, resource, progression, design, log.
reasonStringA textual reason for the event (only present if provided for a given event).
session_idStringA randomly generated unique ID for the current session. 50 character alphanumeric.
session_numNumericThe chronological number of the session since the game was installed, beginning with 1.
session_startedTimestampThe timestamp when the current session started.
session_typeStringSpecifies the build configuration of the game engine for this session. One of client, server, editor.
app_idStringThe unique identifier for the game as configured in the plugin settings.
user_tagsStringA colon-separated list of user tags associated with this user, in alphabetical order (e.g., free_to_play:from_cross_promo).
user_tags_arrayArray of StringA JSON string array of user tags associated with this user, in alphabetical order (e.g., ["free_to_play", "from_cross_promo"]).
user_idString32-char ID for the user. Based on plugin settings is either generated once on first-run or is idvf (iOS) or Android ID.
player_idString32-char hash combining app_id and user_id. Should be unique for this game for this install.
first_installedTimestampThe timestamp when the game was first run.
meta.platformStringThe platform on which the game is running (e.g., ios, android, windows, linux).
meta.os_versionStringThe major and minor operating system version (e.g., 10.0).
meta.sdk_versionStringThe version of the sparklogs plugin that generated the event (e.g., unreal-plugin-1.0.1).
meta.engine_versionStringThe version of the game engine used by the game (e.g., unreal-4.27.2).
meta.buildStringThe build identifier for the game as returned by the game engine or can be customized.
meta.device_makeStringThe make of the device running the game.
meta.device_modelStringThe model of the device running the game.
meta.connection_typeStringThe type of network connection as returned by the game engine. One of None, AirplaneMode, Cell, WiFi, Ethernet.

Additionally, all purchase, ad, resource, progression, and design events will have the following common fields:

Field NameTypeDescription
event_idsArray of StringA JSON string array with the event hierarchy from higher-level to lower-level. For example, ["level", "highrise", "begin"]
event_idStringThe flattened form of event_ids, with each event ID separated by : -- for example, level:highrise:begin.

Note that event_id does not contain the event type. If you want a truly unique event ID, combine the type and event_id fields.

There are no cardinality limits on the possible values for event_id (or things used to calculate event_id such as item category and item ID), but in general be thoughtful about what values you use and how you will design your analysis.

Example LQL Queries​

Because all analytics events contain these fields, the following LQL query will match all analytics events:

g_analytics.type!

You can use LQL to filter on specific parts of the event ID. For example, to find all "level" events, you could use the query:

g_analytics.event_ids[0]=level

Or to find all events that mention the "highrise" level name in any part of the event hierarchy, you could use:

g_analytics.event_ids=highrise

This matches any event where any member of the event_ids array matches exactly the string "highrise". Looser matching on the flattened string field also works just fine:

g_analytics.event_id: highrise

Note that LQL queries are case-insensitive and use the search index by default, and so will search for whole terms (see details). You can search for partial terms (substring in a word) by surrounding a search term with * (e.g., *highrise*).

Example JSON Data​

This section provides JSON for examples of each type of analytics event. This is just for reference, as your game engine plugin will automatically generate the appropriate JSON for you when you record events using the plugin.

{
    "timestamp": "2025-07-15T20:32:53.450Z",
    "app": "MyGame",
    "hostname": "my-computer",
    "pid": 12345,
    "game_instance_id": "amqm3opzx4wa8i6ma9j7tzd2",
    "g_analytics": {
        "type": "session_start",

        "app_id": "ExampleGame",
        "user_tags": "free_to_play:from_cross_promo",
        "user_tags_array": ["free_to_play", "from_cross_promo"],
        "user_id": "A3A3E023442EF421D6C3DCBDBCDC0D3D",
        "player_id": "057D1F32934EA2240598BCC9590DAC4C",
        "session_id": "bofba3k7w4og4byc73u2lvxyn9f75wy0bfp2ndwmny3m7sn0os",
        "session_num": 5,
        "first_installed": "2025-07-04T15:45:05.581Z",
        "session_started": "2025-07-15T20:32:53.450Z",
        "session_type": "client",
        "meta": {
            "build": "++UE4+Release-4.27-CL-18319896",
            "platform": "windows",
            "os_version": 10,
            "device_make": "AuthenticAMD",
            "device_model": "AMD Ryzen 7 PRO 7840U w/ Radeon 780M Graphics",
            "sdk_version": "unreal-plugin-1.0.1",
            "engine_version": "unreal-4.27.2"
        }
    }
}

Analytics Daily Snapshots​

In addition to ingesting and querying real-time individual analytics events, SparkLogs will automatically generate daily aggregate snapshots of analytics data. The data is aggregated by day (UTC) and by org_id, app_id, user_id, player_id. Data is generated daily between 00:30 and 03:00 UTC and performs calculations for each day not yet previously aggregated (up to 90 days in the past).

These snapshots are stored in two tables: (workspace_id)_g_analytics_snapshot_users and (workspace_id)_g_analytics_snapshot_events and are an efficient way to store and query aggregate analytics data over longer time periods. With this aggregate data you can calculate key trends such as daily active users (DAU), monthly active users (MAU), retention, revenue, and progression. Aggregate snapshots are retained for 7 years (paid plan) or 3 months (free plan).

Users Daily Snapshot Table​

The (workspace_id)_g_analytics_snapshot_users table contains daily aggregate snapshots of analytics data for each unique organization, app, and user. Each row represents a single user's activity for a specific day (UTC), including counts of events, sessions, purchases, ad interactions, and other key metrics. This table is designed to efficiently support queries for user engagement, retention, monetization, and other trends over time.

You can compute several different kinds of cohorts by grouping on the appropriate field:

  • User segmentation based on the user_tags field
  • Retention cohorts based on the first_installed field (use TIMESTAMP_TRUNC to truncate to day, week, month, or year as desired to form a cohort)
  • Conversion cohorts based on the first_purchased field (similarly use TIMESTAMP_TRUNC as desired)
  • Location cohorts based on the client_ip_location field (two digit country code)
  • Platform cohorts based on hardware or software characteristics (e.g., build, platform, or device_model fields)

You can calculate statistics for numeric fields that aggregate values for session duration.

This table also allows you to capture and save custom field values from your session_begin and session_end events. For each user, it will randomly choose custom field data from one of the user's session_begin events on that day and store it in the field session_begin_custom within the JSON field data_json. Similarly, this will happen for the session_end_custom field, capturing custom field data from one of a given user's session_end events.

Field NameTypeDescription
t_dayTimestampThe day (UTC) that this snapshot corresponds to. Used for partitioning.
org_idStringGlobally unique organization ID. Used for clustering.
app_idStringThe analytics app ID.
user_idStringUnique ID of the analytics user. Used for clustering.
player_idStringUnique ID of the analytics player (hash of app_id:user_id).
user_tagsStringA colon-separated list of user tags associated with this user, in alphabetical order. If different user tags were used for a given user within a period it will non-deterministically use one of the observed values.
event_countIntegerNumber of events for this org:app:user on this day.
session_countIntegerNumber of sessions completed (session_end event) for this org:app:user on this day.
session_duration_secs_sumNumericSum of all durations of sessions (seconds) for this org:app:user on this day.
session_duration_secs_sum_sqNumericSum of squares of durations of sessions (seconds) for this org:app:user on this day. Useful for calculating variance and standard deviation.
session_duration_secs_minNumericMinimum duration of sessions (seconds) for this org:app:user on this day.
session_duration_secs_maxNumericMaximum duration of sessions (seconds) for this org:app:user on this day.
session_num_minIntegerMinimum session number for this org:app:user on this day.
session_num_maxIntegerMaximum session number for this org:app:user on this day.
resource_event_countIntegerNumber of resource events for this org:app:user on this day.
progression_started_countIntegerNumber of progression events where status is Started for this org:app:user on this day.
progression_failed_countIntegerNumber of progression events where status is Failed for this org:app:user on this day.
progression_completed_countIntegerNumber of progression events where status is Completed for this org:app:user on this day.
design_event_countIntegerNumber of design events for this org:app:user on this day.
first_purchasedTimestampTimestamp of user's first purchase; will be NULL if user has never made a purchase.
first_purchased_dayTimestampTimestamp of user's first purchase truncated to UTC day; will be NULL if user has never made a purchase.
is_converting_todayBooleanWhether the org:app:user made their first purchase on this day.
purchase_countIntegerNumber of purchase events for this org:app:user on this day.
purchase_amount_convertedNumericTotal amount of purchases for this org:app:user on this day in the converted currency.
purchase_amount_converted_currencyStringThe ISO 4217 currency code of the target currency used for converted purchase amounts.
transaction_num_minIntegerMinimum transaction number for this org:app:user on this day or NULL.
transaction_num_maxIntegerMaximum transaction number for this org:app:user on this day or NULL.
ad_shown_countIntegerNumber of ad impressions shown to this org:app:user on this day (excludes ads with action failed_to_show).
ad_completed_countIntegerNumber of ad impressions shown to this org:app:user on this day where ad_action is completed.
ad_skipped_countIntegerNumber of ad impressions shown to this org:app:user on this day where ad_action is skipped.
ad_rewarded_countIntegerNumber of ad impressions shown to this org:app:user on this day where ad_action is rewarded.
ad_clicked_countIntegerNumber of ad impressions shown to this org:app:user on this day where ad_action is clicked.
ad_failed_to_show_countIntegerNumber of ad impressions shown to this org:app:user on this day where ad_action is failed_to_show.
ad_revenue_converted_countIntegerNumber of ad impressions shown to this org:app:user on this day where ad revenue_converted was > 0.
ad_revenue_convertedNumericTotal amount of ad revenue for org:app:user on this day in the converted currency.
ad_revenue_converted_currencyStringThe ISO 4217 currency code of the target currency used for converted ad revenue amounts.
log_num_debugIntegerNumber of debug log entries for this org:app:user on this day.
log_num_infoIntegerNumber of info log entries for this org:app:user on this day.
log_num_warnIntegerNumber of warning log entries for this org:app:user on this day.
log_num_errorIntegerNumber of error log entries for this org:app:user on this day.
log_num_fatalIntegerNumber of fatal log entries for this org:app:user on this day.
first_installedTimestampTimestamp when the user first installed the app.
first_installed_dayTimestampTimestamp when the user first installed the app truncated to UTC day.
engine_versionStringGame engine version.
sdk_versionStringThe version of the app plugin used to send analytics data.
buildStringThe app build version.
platformStringThe platform on which the game is running (e.g., ios, android, windows, linux).
os_versionStringThe major and minor operating system version (e.g., 10.0).
device_makeStringThe make of the device running the app.
device_modelStringThe model of the device running the app.
connection_typeStringThe type of network connection as returned by the game engine. One of None, AirplaneMode, Cell, WiFi, Ethernet.
client_ip_locationStringThe two-letter ISO country code associated with the client's IP address.
data_jsonJSONOther custom fields for this org:app:user on this day, including: session_end_custom (custom fields sent with any one of the session_end events) and session_begin_custom (custom fields sent with any one of the session_begin events).

Events Daily Snapshot Table​

The (workspace_id)_g_analytics_snapshot_events table provides daily aggregate snapshots of analytics data for each unique organization, app, user, event type, and event ID. Each row represents a summary of all events of a specific event type and ID for a given user for a given app in a given organization on a given day (UTC). Summary information includes counts, sums, and other statistics for various fields.

Different event types will populate different fields in this table:

  • purchase events populate amount_converted_* and transaction_num_* fields. (The amount_ fields are also populate but can represent a mix of currencies, we recommend using the converted amounts instead.) The event_id indicates the item category and item ID.
  • ad events populate revenue_converted_*, count_*, and duration_secs_* fields. The event_id indicates the ad provider, placement, type, and action.
  • resource events populate amount_* fields. The event_id indicates if it is a Source or Sink event and for what virtual currency, item category, and item ID.
  • progression events populate attempt_* fields and if they were populated in the original events, the value_* fields. The event_id indicates the status (Started, Failed, or Completed) and tiers.
  • design events populate the value_* fields if they were populated in the original events. The event_id indicates the custom event ID that represents the design event hierarchy.

You can calculate statistics for numeric fields that aggregate values for value, attempt, transaction number, amount, converted amount, converted revenue, count, and duration_secs.

Note that you can retrieve a summary of the number of log analytics events per user per day from the (workspace_id)_g_analytics_snapshot_users table. Log events by severity are not summarized in this table.

Field NameTypeDescription
t_dayTimestampThe day (UTC) that this snapshot corresponds to (partitioned).
org_idStringGlobally unique organization ID (clustered).
app_idStringThe analytics app ID.
user_idStringUnique ID of the analytics user (clustered).
player_idStringUnique ID of the analytics player (hash of app_id:user_id).
user_tagsStringA colon-separated list of user tags associated with this user, in alphabetical order. If different user tags were used for a given user within a period it will non-deterministically use one of the observed values.
event_typeStringThe class of event, such as purchase, ad, resource, progression, design.
event_idStringThe flattened ID of the event, with each part separated by :.
event_id_1StringThe first part of the event ID or NULL.
event_id_2StringThe second part of the event ID or NULL.
event_id_3StringThe third part of the event ID or NULL.
event_id_4StringThe fourth part of the event ID or NULL.
event_id_5StringThe fifth part of the event ID or NULL.
event_id_6StringThe sixth part of the event ID or NULL.
event_id_7StringThe seventh part of the event ID or NULL.
event_countIntegerNumber of events with this type:event_id for this org:app:user on this day.
value_countIntegerCount of rows with non-null value fields for this type:event_id.
value_sumNumericSum of all value fields for this type:event_id.
value_sum_sqNumericSum of squares of value fields for this type:event_id.
value_minNumericMinimum value field for this type:event_id.
value_maxNumericMaximum value field for this type:event_id.
attempt_countIntegerCount of rows with non-null attempt fields for this type:event_id.
attempt_sumNumericSum of all attempt fields for this type:event_id.
attempt_sum_sqNumericSum of squares of attempt fields for this type:event_id.
attempt_minIntegerMinimum attempt field for this type:event_id.
attempt_maxIntegerMaximum attempt field for this type:event_id.
transaction_num_countIntegerCount of rows with non-null transaction_num fields for this type:event_id.
transaction_num_sumNumericSum of all transaction_num fields for this type:event_id.
transaction_num_sum_sqNumericSum of squares of transaction_num fields for this type:event_id.
transaction_num_minIntegerMinimum transaction_num field for this type:event_id.
transaction_num_maxIntegerMaximum transaction_num field for this type:event_id.
amount_countIntegerCount of rows with non-null amount fields.
amount_sumNumericSum of all amount fields.
amount_sum_sqNumericSum of squares of amount fields.
amount_minNumericMinimum amount field.
amount_maxNumericMaximum amount field.
amount_converted_currencyStringISO 4217 currency code for the target currency of converted amounts. Undefined if the target currency changed during the period.
amount_converted_countIntegerCount of rows with non-null amount_converted fields.
amount_converted_sumNumericSum of all amount_converted fields.
amount_converted_sum_sqNumericSum of squares of amount_converted fields.
amount_converted_minNumericMinimum amount_converted field.
amount_converted_maxNumericMaximum amount_converted field.
revenue_converted_currencyStringISO 4217 currency code for the target currency of converted revenue amounts. Undefined if the target currency changed during the period.
revenue_converted_countIntegerCount of rows with non-null revenue_converted fields.
revenue_converted_sumNumericSum of all revenue_converted fields.
revenue_converted_sum_sqNumericSum of squares of revenue_converted fields.
revenue_converted_minNumericMinimum revenue_converted field.
revenue_converted_maxNumericMaximum revenue_converted field.
count_countIntegerCount of rows with non-null count fields.
count_sumNumericSum of all count fields.
count_sum_sqNumericSum of squares of count fields.
count_minIntegerMinimum count field.
count_maxIntegerMaximum count field.
duration_secs_countIntegerCount of rows with non-null duration_secs fields.
duration_secs_sumNumericSum of all duration_secs fields.
duration_secs_sum_sqNumericSum of squares of duration_secs fields.
duration_secs_minNumericMinimum duration_secs field.
duration_secs_maxNumericMaximum duration_secs field.
data_jsonJSONCurrently NULL. Reserved for future use.

Analytics Materialized Views​

g_analytics view​

To make it convenient and efficient to query analytics data, SparkLogs generates a materialized view named (workspace_id)_g_analytics that contains only analytics events and flattens the g_analytics JSON object into top-level fields. The view is partitioned over the t field and clustered over (t, event_type, event_id).

The most common fields for each supported analytics event type are included in this view.

On the private cloud service plans you can directly access this materialized view in your BigQuery project. The schema is as follows:

Field NameTypeDescription
org_idStringThe ID of the organization that owns the data.
agent_idStringThe ID of the agent that ingested the data.
sourceStringThe hostname of the machine sending the data.
ingested_tTimestampThe timestamp when the data was ingested.
event_indexIntegerThe index of the ingested event within the batch of events submitted in one ingestion request. This allows to reconstruct the exact order of log events even if their timestamps are exactly the same.
tTimestampThe timestamp of the event, as indicated by the client. This is typically the time when the event was generated in the game.
severitySeverity(log events) The severity level of the log message. 1-24. Has the same meaning as SeverityNumber in the OpenTelemetry spec. (1=trace, 5=debug, 9=info, 13=warn, 17=error, 21=fatal)
appStringThe name of the application that generated the event.
app_idStringThe unique identifier for the game as configured in the plugin settings.
user_tagsStringA colon-separated list of user tags associated with this user, in alphabetical order.
user_idString50-char ID for the user. Based on plugin settings is either generated once on first-run or is idvf (iOS) or Android ID.
player_idString32-char hash combining app_id and user_id. Should be unique for this game.
game_instance_idStringThe unique identifier for the game instance, as generated by the plugin. This is typically a randomly generated ID that is unique for the lifetime of the game engine process.
session_idStringA randomly generated unique ID for the current session. 50 character alphanumeric.
session_typeStringSpecifies the build configuration of the game engine for this session. One of client, server, editor.
session_startedTimestampThe timestamp when the current session started.
session_endedTimestamp(session end events) The timestamp when the session ended.
session_duration_secsNumeric(session end events) The duration of the session in seconds, from the time the session started to the time it ended.
session_numNumericThe chronological number of the session since the game was installed, beginning with 1.
event_typeStringOne of session_start, session_end, purchase, ad, resource, progression, design, log.
event_idString(purchase, ad, resource, progression, and design events) The flattened form of event_ids, with each event ID separated by : -- for example, level:highrise:begin
event_id_(n)StringRepresents the nth value in the event_ids array, if any, up to event_id_7. For example, event_id_1=level or event_id_3=begin
statusString(progression events) One of Started, Failed, or Completed.
valueNumeric(progression events) The optional numeric value associated with the event (e.g., score achieved for the attempt)
attemptNumeric(progression events) The number of attempts for this given event ID before successfully completing it. Starts at 1.
flow_typeString(resource events) Either "Source" or "Sink".
virtual_currencyString(resource events) The name of the virtual currency (e.g., "pink_gems", "lives").
amountNumeric(resource and purchase events) For resource events, the amount of virtual currency granted or used. This will be enforced to be positive for sources and negative for sinks. For purchase events, the amount of real-world currency spent on the purchase (for example one cent would be 0.01).
amount_convertedNumeric(purchase events) The amount converted into your preferred target currency (e.g., USD) using the average exchange rate at the time of event ingestion. Will only be present if the source currency is supported and the conversion is successful.
amount_converted_currencyString(purchase events) The ISO 4217 currency code of the target currency used for conversion. Will only be present if the source currency is supported and the conversion is successful.
currencyString(purchase and ad events) The currency used for the purchase amount or ad revenue (e.g., "USD", "EUR"). This is the ISO 4217 currency code. Over 200 are supported.
transaction_numNumeric(purchase events) The number of the transaction since the game was first installed, starting with 1.
first_purchasedTimestamp(purchase events) The timestamp when the first purchase was made since the game was installed.
ad_providerString(ad events) A string identifying the ad provider. For example, unity, admob, etc. or use unknown
ad_placementString(ad events) An ID representing where the ad was placed within you app. For example, end_of_level, treasure_chest, etc.
ad_typeString(ad events) Optional. The type of ad that was shown. One of interstitial, video, rewarded_video, banner, native, playable, app_open, offerwall, cross_promo, other, or any custom string.
ad_actionString(ad events) Optional. The action taken by the player in response to the ad. One of completed, skipped, clicked, rewarded, failed_to_show, other, or any custom string.
ad_fail_reasonString(ad events) Optional. The reason the ad failed to show, if applicable. One of no_fill, offline, network_error, invalid_request, other, or any custom string.
revenueNumeric(ad events) Optional. The total revenue generated from these ad impression(s), if known.
revenue_convertedNumeric(ad events) Optional. The ad revenue converted into your preferred target currency (e.g., USD) using the average exchange rate at the time of event ingestion. Will only be present if the source currency is supported and the conversion is successful.
revenue_converted_currencyString(ad events) Optional. The ISO 4217 currency code of the target currency used for conversion of ad revenue. Will only be present if the source currency is supported and the conversion is successful.
duration_secsNumeric(ad events) Optional. The duration of the ad in seconds, if applicable. If this event represents multiple impressions, use the average duration.
countNumeric(ad events) Optional. The number of ad impressions recorded in this event. Defaults to 1 if not specified.
log_messageString(log events) The textual message of the analytics log event.
reasonStringA textual reason for the event (only present if provided for a given event).
first_installedTimestampThe timestamp when the game was first run.
engine_versionStringThe version of the game engine used by the game (e.g., unreal-4.27.2).
sdk_versionStringThe version of the sparklogs plugin that generated the event (e.g., unreal-plugin-1.0.1).
buildStringThe build identifier for the game as returned by the game engine or can be customized.
platformStringThe platform on which the game is running (e.g., ios, android, windows, linux).
os_versionStringThe major and minor operating system version (e.g., 10.0).
device_makeStringThe make of the device running the game.
device_modelStringThe model of the device running the game.
connection_typeStringThe type of network connection as returned by the game engine. One of None, AirplaneMode, Cell, WiFi, Ethernet.
client_ip_locationStringThe two-letter ISO country code associated with the client's IP address.
data_jsonJSONThe full JSON data from the original event, including all custom data (will be stored in $.g_analytics.custom).

Any fields that are not relevant for a given event type will be NULL. Note that the original array forms of certain data (e.g., event_ids) are preserved in the JSON data in data_json, so you can query the array forms if needed.

g_analytics_event_ids view​

Additionally, a second materialized view is generated (workspace_id)_g_analytics_event_ids that contains one row per organization and event ID per hour. This makes it efficient to get a distinct list of event IDs used in your game, as well as summary statistics per event ID per hour per organization. The view is partitioned over t_hour and clustered over (org_id, event_type, event_id).

You can calculate statistics for numeric fields that aggregate values for session duration, session number, value, attempt, transaction number, amount, converted amount, converted revenue, count, and duration_secs.

Table schema:

Field NameTypeDescription
t_hourTimestampThe timestamp of the hour containing the aggregated event data.
org_idStringThe ID of the organization that owns the data.
event_typeStringOne of session_start, session_end, purchase, ad, resource, progression, design, log.
event_idString(purchase, ad, resource, progression, and design events) The flattened form of event_ids, with each event ID separated by : -- for example, level:highrise:begin
event_id_(n)StringRepresents the nth value in the event_ids array, if any, up to event_id_7. For example, event_id_1=level or event_id_3=begin
event_countIntegerNumber of events within this hour.
session_countIntegerCount of sessions
session_duration_secs_sumFloatSum of all session durations in seconds
session_duration_secs_sum_sqFloatSum of squared session durations
session_duration_secs_minFloatMinimum session duration in seconds
session_duration_secs_maxFloatMaximum session duration in seconds
session_num_minIntegerMinimum session number
session_num_maxIntegerMaximum session number
value_countIntegerCount of rows with non-null values
value_sumFloatSum of all values
value_sum_sqFloatSum of squared of all values
value_minFloatMinimum value
value_maxFloatMaximum value
attempt_countIntegerCount of rows with non-null attempts
attempt_sumFloatSum of all attempt numbers
attempt_sum_sqFloatSum of squared of all attempt numbers
attempt_minIntegerMinimum attempt number
attempt_maxIntegerMaximum attempt number
transaction_num_countIntegerCount of rows with non-null transaction numbers
transaction_num_sumFloatSum of all transaction numbers
transaction_num_sum_sqFloatSum of squared of all transaction numbers
transaction_num_minIntegerMinimum transaction number
transaction_num_maxIntegerMaximum transaction number
amount_countIntegerCount of rows with non-null amounts
amount_sumFloatSum of all amounts
amount_sum_sqFloatSum of squared of amounts
amount_minFloatMinimum amount
amount_maxFloatMaximum amount
amount_converted_currencyStringISO 4217 currency code for converted amounts (undefined if target currency was changed during the period)
amount_converted_countIntegerCount of rows with non-null converted amounts
amount_converted_sumFloatSum of all converted amounts
amount_converted_sum_sqFloatSum of squared converted amounts
amount_converted_minFloatMinimum converted amount
amount_converted_maxFloatMaximum converted amount
revenue_converted_currencyStringISO 4217 currency code for converted revenue amounts (undefined if target currency was changed during the period)
revenue_converted_countIntegerCount of rows with non-null converted revenue amounts
revenue_converted_sumFloatSum of all converted revenue amounts
revenue_converted_sum_sqFloatSum of squared of converted revenue amounts
revenue_converted_minFloatMinimum converted revenue amount
revenue_converted_maxFloatMaximum converted revenue amount
count_countIntegerCount of rows with non-null count values
count_sumFloatSum of all count values
count_sum_sqFloatSum of squared of count values
count_minIntegerMinimum count value
count_maxIntegerMaximum count value
duration_secs_countIntegerCount of rows with non-null duration_secs values
duration_secs_sumFloatSum of all duration_secs values
duration_secs_sum_sqFloatSum of squared of duration_secs values
duration_secs_minFloatMinimum duration_secs value
duration_secs_maxFloatMaximum duration_secs value
log_num_debugIntegerNumber of debug log entries in period
log_num_infoIntegerNumber of info log entries in period
log_num_warnIntegerNumber of warning log entries in period
log_num_errorIntegerNumber of error log entries in period
log_num_fatalIntegerNumber of fatal log entries in period

Calculating Statistics​

The snapshot tables and event IDs materialized view store certain numeric fields that aggregate counts, sums, and sums of squares for a given period. Storing the data in this way allows you to reconstruct averages and standard deviations even when aggregating across multiple periods:

  • Average: mean = SUM(field_sum) / SUM(field_count)
  • Standard Deviation: stddev = SQRT((SUM(field_sum_sq) - ((SUM(field_sum) * SUM(field_sum)) / SUM(field_count))) / (SUM(field_count) - 1))

Dashboards and Visualizations​

With the private cloud service plans, you can connect your aggregate and granular analytics data to Looker Studio in your own Google Cloud project.

Simply connect Looker Studio to the BigQuery project that holds your SparkLogs data and add dashboard widgets with appropriate queries.

We recommend having your dashboards query the analytics snapshots tables for performance and query cost-optimization.

tip

For large-scale environments, we recommend that you setup a BigQuery Enterprise reservation that auto-scales from zero slots. This will optimize your BigQuery query costs and performance. Note that this does not require a long-term BigQuery commitment.

Querying Custom Data​

With the private cloud service plans, you have direct access to your data in your own BigQuery project, including aggregate data and individual events. Individual events store any custom JSON data you sent with each analytics event.

To query and filter on custom data in individual events, use the BigQuery JSON functions (e.g., JSON_QUERY and LAX_STRING) on the data_json field in the analytics materialized view that was automatically created for you. For example, to filter on a custom field named offer_id, you can use:

LAX_STRING(JSON_QUERY(data_json, '$.g_analytics.custom.offer_id'))

Use LAX_STRING, LAX_INT64, LAX_FLOAT64, or LAX_BOOL to convert the JSON value to a native SQL type (or returns NULL if the JSON value is incompatible with the desired type).

Note that any timestamps in your custom data will be stored in the JSON as the number of microseconds since the Unix epoch. You can convert these to a native SQL timestamp type using the TIMESTAMP_MICROS SQL function. For example:

TIMESTAMP_MICROS(LAX_INT64(JSON_QUERY(event_json, '$.g_analytics.custom.first_subscribed')))
tip

Use the TIMESTAMP_TRUNC SQL function to calculate cohorts from a timestamp. For example, to calculate a monthly cohort value from from the standard first_installed field:

TIMESTAMP_TRUNC(session_started, MONTH)

You can also do this with custom fields. For example, to calculate a weekly cohort value from the custom timestamp field first_subscribed do:

TIMESTAMP_TRUNC(TIMESTAMP_MICROS(LAX_INT64(JSON_QUERY(event_json, '$.g_analytics.custom.first_subscribed'))), WEEK)

Additional Resources​

Please share your questions, ideas, and feedback with us in our discord community. SparkLogs is under rapid development and we value your input. The game engine plugins are open source and we welcome bug reports and pull requests.