Sample queries for AIS Data Warehouse
Overview
This article contains sample queries for retrieving useful insights from the AccelByte Intelligence Service (AIS) Data Warehouse, such as game matchmaking, players, orders, lobby, and more. We encourage you to experiment with these samples to get the insights that you need.
The potential of the AIS Data Warehouse if limitless. We recommend that you explore and experiment with queries to determine what will work with your environment and operation needs. For assistance or advice, contact the AccelByte Support Team.
Prerequisites
Get insight on user activities
To gain comprehensive insight into player activities, the AIS data warehouse has furnished accessible summary tables designed for easy querying. The data was categorized across time intervals, including hourly, daily, weekly, and monthly increments. Furthermore, these tables delineate a spectrum of activities, encompassing essential actions of the users.
These are the aggregated data and query-ready tables that can be useful for obtaining Hourly Active Users, Daily Active User Count, Weekly Active Users, Monthly Active Users, Average Hourly Active Users, and Peak Hourly Active Users.
bd_activity_type_t: consists of absolute activity types such as Agreement, Entitlement, Fulfillment, Order, Payment, Auth, Ban, New User, Wallet, and more.bd_daily_count_t: contains derived daily counts of distinct user activity: DAU, WAU and MAU, AHAU (average over the day) and PHAU (top HAU in the .day)bd_hourly_count_t: contains derived hourly counts of distinct user activity type.
We will provide you with a sample of a certain type of activity as a representation. Learn more about bd_activity_type_t to understand available types and conduct experiments on your own with the other types.
Get daily, weekly, and monthly user activities
To get insight on the daily, weekly, and monthly user activities, use a combination of the namely bd_daily_count_t, namespace_t, and studio_t tables.
SELECT
bdct.*
FROM
bd_daily_count_t AS bdct
INNER JOIN
namespace_t AS nt ON nt.id = bdct.namespace_id
INNER JOIN
studio_t AS st ON st.id = nt.studio_id
WHERE
bdct.activity_type_id = '10' AND
bdct.day_ts BETWEEN '2024-01-01' AND '2024-01-31' AND
nt.is_production = TRUE AND
st.name = 'YourStudioName' AND
nt.name = 'YourNamespace';
The above query will provide insights into the user activity from 2024-01-01 to 2024-01-31. However, since we defined activity_type_id with 10, the query will display a set of New Users data.
The query will provide information on Activity Type, Day, DAU, WAU, MAU, AHAU, and PHAU.
Sample query to get hourly user activity
To get insight on the hourly user activity, use a combination of the bd_hourly_count_t, namespace_t, and studio_t tables.
SELECT
bhct.*
FROM
bd_hourly_count_t AS bhct
INNER JOIN
namespace_t AS nt ON nt.id = bhct.namespace_id
INNER JOIN
studio_t AS st ON st.id = nt.studio_id
WHERE
bhct.activity_type_id = '8' AND
DATE(bhct.hour_ts) = '2024-01-01' AND
nt.is_production = TRUE AND
st.name = 'yourStudioName' AND
nt.name = 'yourNamespace';
The query will give you 24 rows of specific user activity in 2024-01-01. And since we defined activity_type_id with 8, the User Auth counts in that day for 24 hours.
Get a total number of users
To get the total number of users in a certain namespace, use a combination of the user_t, namespace_t, and studio_t tables.
SELECT
count(ut.id) AS "Total Users"
FROM
user_t AS ut
INNER JOIN
namespace_t AS nt ON nt.id = ut.namespace_id
INNER JOIN
studio_t AS st ON st.id = nt.studio_id
WHERE
nt.is_production = TRUE AND
st.name = 'yourStudioName' AND
nt.name = 'yourNamespace';
Get matchmaking insight
To gain Matchmaking session insight, you can use the following tables get the matchmaking summaries of players:
bd_session_daily_t: contains derived daily counts of distinct match sessions and lobby sessions.- `bd_session_hourly_t: contains derived hourly counts of distinct match sessions and lobby sessions.
Get hourly matchmaking sessions
To get the hourly count of matchmaking activity, use a combination of the bd_session_hourly_t, namespace_t, and studio_t tables.
SELECT
bsht.*
FROM
bd_session_hourly_t AS bsht
INNER JOIN
namespace_t AS nt ON nt.id = bsht.namespace_id
INNER JOIN
studio_t AS st ON st.id = nt.studio_id
WHERE
DATE(bsht.ts_hr) = '2024-01-01' AND
nt.is_production = TRUE AND
st.name = 'yourStudioName' AND
nt.name = 'yourNamespace';
The result will show you 24 rows of hourly count of lobby sessions and match sessions.
Get daily matchmaking session and lobby
To get the daily count of matchmaking activity, use a combination of the bd_session_daily_t, namespace_t, and studio_t tables.
SELECT
bsdt.*
FROM
bd_session_daily_t AS bsdt
INNER JOIN
namespace_t AS nt ON nt.id = bsdt.namespace_id
INNER JOIN
studio_t AS st ON st.id = nt.studio_id
WHERE
bsdt.day_ts >= '2024-01-01' AND bsdt.day_ts < '2024-02-01' AND
nt.is_production = TRUE AND
st.name = 'yourStudioName' AND
nt.name = 'yourNamespace';
The result will show you the daily count of lobby sessions and match sessions in a month.
Get weekly matchmaking session and lobby
To get the weekly count of matchmaking activity, use a combination of the bd_session_daily_t, namespace_t, and studio_t tables.
SELECT
bsdt.namespace_id,
EXTRACT(WEEK FROM bsdt.day_ts) AS week_number,
MIN(bsdt.day_ts) AS week_start_date,
MAX(bsdt.day_ts) AS week_end_date,
SUM(bsdt.match_session_count) AS match_session_count,
SUM(bsdt.lobby_session_count) AS lobby_session_count
FROM
bd_session_daily_t AS bsdt
INNER JOIN
namespace_t AS nt ON nt.id = bsdt.namespace_id
INNER JOIN
studio_t AS st ON st.id = nt.studio_id
WHERE
day_ts >= '2023-01-01' AND day_ts < '2024-01-01' AND
nt.is_production = TRUE AND
st.name = 'yourStudioName' AND
nt.name = 'yourNamespace'
GROUP BY
bsdt.namespace_id,
EXTRACT(WEEK FROM bsdt.day_ts)
ORDER BY
week_number;
The result will show the weekly count of lobby sessions and match sessions in 2023.
Get monthly matchmaking session and lobby
To get the monthly count of matchmaking activity, use a combination of the bd_session_daily_t, namespace_t, and studio_t tables.
SELECT
bsdt.namespace_id,
EXTRACT(MONTH FROM bsdt.day_ts) AS month_number,
MIN(bsdt.day_ts) AS month_start_date,
MAX(bsdt.day_ts) AS month_end_date,
SUM(bsdt.match_session_count) AS match_session_count,
SUM(bsdt.lobby_session_count) AS lobby_session_count
FROM
bd_session_daily_t AS bsdt
INNER JOIN
namespace_t AS nt ON nt.id = bsdt.namespace_id
INNER JOIN
studio_t AS st ON st.id = nt.studio_id
WHERE
day_ts >= '2023-01-01' AND day_ts < '2024-01-01' AND
nt.is_production = TRUE AND
st.name = 'yourStudioName' AND
nt.name = 'yourNamespace'
GROUP BY
bsdt.namespace_id,
EXTRACT(MONTH FROM bsdt.day_ts)
ORDER BY
month_number;
The result will show the monthly count of lobby sessions and match sessions in 2023.
Get E-commerce insights
AIS data warehouse has many tables that keep the in-game e-commerce data, such as items, transactions, orders, entitlements, fulfillment, and more.
Sample query to get most popular ordered items
This query will provide insights into the all-time top five most frequently ordered items in the game.
SELECT
it.item_number AS "item_number",
ilt.title AS "item_title",
ist.item_status_name AS "item_status",
SUM(oit.qty) AS "quantity"
FROM item_t AS it
INNER JOIN
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY ist.id ORDER BY ist.start_ts DESC
) AS "row_num",
ist.item_id AS "item_id",
istt.name AS "item_status_name"
FROM
item_status_t AS ist
INNER JOIN
item_status_type_t AS istt ON istt.id = ist.status_id
) AS ist ON it.id = ist.item_id AND ist.row_num = 1
INNER JOIN
item_localization_t AS ilt ON ilt.item_id = it.id
INNER JOIN
order_item_t AS oit ON oit.item_id = it.id
INNER JOIN
namespace_t AS nt ON nt.id = it.namespace_id
INNER JOIN
studio_t AS st ON st.id = nt.studio_id
WHERE
st.name = 'yourStudioName' AND
nt.name = 'yourNamespace'
GROUP BY
item_number, item_title, item_status
ORDER BY
quantity DESC
LIMIT 5;