Analytics - Guide

[BUSI-Analytics-02]

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.

  1. Both a thick & web client so you can access your Athena database from anywhere. Both are identical in every way.
  2. Very simple to setup
  3. There are both free & paid versions available.
  4. 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! Please Contact Us and we will provide you the following:

  • AWS Access Key ID
  • AWS Secret Access Key
  • 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.

  1. Navigate to your accounts menu and click manage connections
  2. Click Add new Connection button. It is located in the top right corner.
  3. Select Amazon Athena
  4. The database value is the CID of your title. You can find this in the Beamable portal and generally looks like DE_3298472394728. Note that when you populate it here in the database that the de_ needs to be lower case.
  5. Put the provided S3 Output Location, AWS Access Key ID & AWS Secret Access Key. You can leave everything else default.
  6. 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.

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

DBID 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!


Did this page help you?