Open Analytics using AWS

 

Growth teams need to perform a lot of data analysis for their daily operations. For this analysis, they need to have the right data access at the right time. MoEngage Open Analytics enables growth teams to access the latest data on the MoEngage platform in real-time directly from the data storage. MoEngage enables customers to directly query on the data warehouse and get desired data.

This article strictly explains Open Analytics using AWS. If you are looking for Open Analytics using Google BigQuery, click here.

 

Capabilities of Open Analytics

MoEngage open analytics can help with  -

  1. Connecting to Visualization tools and generate all the reports on one dashboard
  2. Read all of the data using SQL queries through console or code
 

MoEngage open analytics is not suitable for data exports. For data export please use MoEngage Streams or S3 exports.

 

Enable Open Analytics

MoEngage require 2 sets of information to enable open analytics - 

  1. Email ids of users
  2. The static IP address. This can be your VPN IP.
    (Make sure you provide static IP. IP provided by your network provider will not work.)

 

Data Categories

All data received at the MoEngage system can be categorized into 3 kinds –

  1. Device data,
  2. User Data, and
  3. Event data

The data is being collected using the SDK and S2S events and processed and stored with events.

 

Data Schema

At MoEngage AWS Warehouse, we process all the device attributes & event attributes and store them in one event table. Each Event has its own table consisting of Event Attributes and Device Attributes.

Tables Name - Event Name

attribute 1 attribute 2 attribute 3 --  attribute n-1 attribute n
... ... ... ... ... ...
... ... ... ... ... ...

 

Time-window for analysis can be selected using attribute - day. This partition time works for all tables. Must always use this in your queries to limit the costs. For Example

Select server_time, count(server_time) as metric
FROM
"account_name"."moe_app_opened_828"
where day >= '2018-09-23'
group by 1
order by 1

 

Event Tables can be joined using common key/attributes such as - user_id

select user1
FROM
(Select user_id as user1 from "account_name"."uptete_spa") table1 INNER JOIN
(Select user_id as user2 from "account_name"."moe_user_merge" ) table2
ON table1.user1 = table2.user2

 

Event & Attribute Name Structure

To avoid data loss from similar names and few security purposes, the names of events and attributes present in MoEngage Warehouse will be cryptic.

Event names have structure as ‘EventName_XXX’, for example - moe_app_opened_828
Attribute names have structure as ‘Datatype_AttributeName_XXX’, for example - string_activity_name_880

We have provided an excel file with all the readable names and names present in the warehouse for ease of use.

 

User Attributes

MoEngage open analytics have a user table. While event tables have user attributes of that specific event execution time, this user table has all the updated & latest user attributes. This table refreshes once per day. 
Table Name - user_table_view 
Column for MoEngage generated User Id - user_id

attribute 1 attribute 2 attribute ... attribute n
... ... ... ...
... ... ... ...

 

Info

Please request a Data Dictionary from your CSM for your account. This will have all the readable names for events, event attributes, and user attributes.

 

Important Attribute Information

(These attributes should be present in all of the events)

  1. day - Time-window for analysis can be selected using this attribute. This partition time works for all tables. This should be considered as the date attribute for Tables.
  2. server_time - UTC/GMT time at which events reach MoEngage servers.
  3. user_id - This is a MoEngage generated User Id.
  4. string_uid_125 - This is the User Id generated by your App/Site.
  5. original_event_name - Event Name
  6. user_time - Event time in local time zone

 

Open analytics Usage Best Practices

  1. Must always use day in your queries to keep the cost minimum. If the day is not used, MoEngage will scan all the data, and scanning costs will be higher.
  2. MoEngage works on a columnar database, hence use only the columns ( attributes ) that you require for analysis. If unnecessary columns are used, cost increases and query performance decreases.
  3. Please do read this information in detail
    https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/
  4. A general guide to writing Athena SQL queries - 
    https://docs.aws.amazon.com/athena/latest/ug/querying-athena-tables.html
    https://prestodb.io/docs/0.217/functions.html
    https://prestodb.io/docs/0.217/language.html

    https://prestodb.io/docs/0.217/sql.html

 

Service Account Access

To read data using python or java code, Growth teams will require service account access from MoEngage. Get in touch with your CSM to know how to access it from the current open analytics dashboard.

 

Connecting with Visualization Tools

Query console of MoEngage open analytics also provides various charts, which can be pinned to dashboards to save your analysis. Data can also be accessed using multiple visualization tools using service account access.

 

Data Export

To transfer data out of the MoEngage Warehouse, you can use MoEngage streams, details are given in the link below.

https://help.moengage.com/hc/en-us/articles/360045896572-MoEngage-Streams

 

Sample Queries

To get started with open analytics few of the queries are given above in the doc. Below are the links for a few of the frequently asked queries.

  1. Key Metrics Queries here
  2. Reachability Queries here

 

Open Analytics is an add-on feature to MoEngage analytics. Get in touch with the Support Team and CSM to get it enabled for your account.

Was this article helpful?
0 out of 0 found this helpful