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.
- 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!
Updated 6 months ago