Analytics - Guide

Guide Overview

In this guide we will cover how to get setup to query your Athena Database and how to write some simple queries to see that it is working.

📘
For this guide we will be using PopSQL
PopSQL is a delightful tool that makes querying the Athena Database a breeze. There are many other options out there to query Athena, but we like PopSQL for a few reasons.

Both a thick & web client so you can access your Athena database from anywhere. Both are identical in every way.

Very simple to setup

There are both free & paid versions available.

If you are using PopSQL with a Team, you can save, store & share queries across your entire team.

Signup for PopSQL to follow along with this guide!
Beamable developers and studios also get a discount on PopSQL subscriptions!

50% off Premium or Business for Indie teams (less than 2 years old, and fewer than 10 employees) for up to 1 year

25% off Premium or Business for everyone else for up to 1 year

All you have to do is email [email protected] or open up a ticket through our intercom chat widget and provide your Beamable CID.

Get Access

In order to access the Athena Database you will need 3 key pieces of information. The following can only be provided to you from the Beamable team and we are eager to get you setup! NOTE: You must have a REPORT tier subscription to get access to custom analytics queries. You can find out more at https://beamable.com/pricing. We will then provide you with the following:

AWS Access Key ID
AWS Secret Access Key
AWS Region ( in most cases us-west-2 )
S3 Output Location ( should look like s3://myBucket )

Configure PopSQL

Once you have downloaded PopSQL or have signed up and are using the web interface, and you have received your credentials from the Beamable team you are ready to configure PopSQL.

Navigate to your accounts menu and click manage connections
Click Add new Connection button. It is located in the top right corner.
Select Amazon Athena
The database value is a lowercase version of the project ID (PID) of your title. You can find this in the Beamable portal and generally looks like DE_1418422019508251. Note that when you populate it here in the database that the de_ needs to be lower case.
Put the provided S3 Output Location, AWS Access Key ID & AWS Secret Access Key. You can leave everything else blank or default. ( for example, AWS Session Token is not needed for this type of connection )
Click Save and Connect

Note: You may want to Test your connection before Saving & Connecting.

Your First Query

I like to start off fairly simple and just get a general list of events to see that things are working properly.

Be sure to set your connection if it is not already set. Do not pick a database if any are available. Those would be test databases and do not have your data in them.

The schemas and tables in PopSQL should show (0). You can query them, but you cannot browse them for permission reasons.

Here we are going to get the last 10 events from the platform_session_session table.

select * from platform_session_session limit 10;

As you can see you already get some very interesting data about your player. Let's take a moment to explore the schema of this table.

Getting the Table Schema

SELECT * FROM information_schema.columns WHERE table_schema = '[your de_ID goes here]' and table_name = 'platform_session_session'

The above query will yield the following results. And as you can see the information provided in this table is immensely valuable. Beamable by default calculates a bunch of information for you making it easy to extract MAU, DAU, Session Length, Spending habits and more.

Column Name	Data Type	Detail
event_id	bigint	Id of the event
act_date	varchar(256)	Date of the event as a string
act_time	timestamp	timestamp of the event
gamer_tag	bigint	PlayerId of the player
e.spend3d	varchar(256)	How much the player has spent in the last 3 days
e.sessions14d	varchar(256)	how many sessions the player has had in the last 14 days
e.corrid	varchar(256)	a correlation id which can be used across tables
e.sessiondays	varchar(256)	has played a session at least once for a total of N days. These are not consecutive.
e.spendtotal	varchar(256)	total amount spent
e.sessions28d	varchar(256)	how many session the player has had in the last 28 days
e.device.platform	varchar(256)	what platform they were using when this event was written
e.sessionstotal	varchar(256)	total number of sessions
e.purchasestotal	varchar(256)	total purchases
e.spend7d	varchar(256)	total amount spent in the last 7 days
e.timebetweensessionsminutes	varchar(256)	average time between sessions in minutes
e.firstdailysession	varchar(256)	was this event the first daily session?
e.sessions3d	varchar(256)	how many sessions in the last 3 days
e.purchases7d	varchar(256)	how many purchases in the last 7 days
e.dayssinceinstall	varchar(256)	how many days since the player installed
e.sessions7d	varchar(256)	how many session in the last 7 days
e.spend28d	varchar(256)	how much have they spent in the last 28 days
e.ip	varchar(256)	the ip address of the player for this session event
e.purchases14d	varchar(256)	total purchases in the last 14 days
e.purchases28d	varchar(256)	total purchases in the last 28 days
e.purchases3d	varchar(256)	total purchases in the last 3 days
e.spend14	varchar(256)	total spend in the last 14 days

What Tables are available?

As you use Beamable, more and more data will be available. That means more tables will also be available. By default there are a select few tables available.

platform
platform_session_session
platform_session_session_end
platform_session_install

But as you use Beamable features and as you write custom Telemetry events more data and tables will automatically be available. So it's important that you query to see what tables are available from time to time. The following query will provide you with a list of tables that are available to you at any given point in time.

SELECT distinct table_name FROM information_schema.columns WHERE table_schema = 'your de_ID goes here'

Conclusion

Now you have the basics you need in order to query your Athena database, see what tables are available and see which fields are available within those tables. The power is now in your hands to ask the questions you want to know about your players and query your data to find out the answers!