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.

Capabilities of Open Analytics

MoEngage open analytics can help with  -

  1. Read and Query data using SQL queries through console or Programatically via API
  2. Visualise the data on Open Analytics console through in-build visualisation methods available
 

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 as well. Make sure you provide the static IP, IP's 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
  3. Event data

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

 

Data Schema

MoEngage processes and stores all the device attributes and event attributes in separate event tables for each event.

Table Name - account_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 - 

SQL
SELECT server_time, COUNT(server_time) as metric
FROM
"account_name"."moe_app_opened_828"
WHERE 
day >= '2022-01-01'
GROUP BY 1
ORDER BY 1

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

SQL
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 Naming Convention

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

 

User Attributes

User attributes are stored in user table and the table refreshes one time every day.
Table Name - users_base_table 
Column for MoEngage generated User Id - user_id

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

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. Use day in your queries to optimise query performance otherwise MoEngage will scan the entire table 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. To optimise queries, refer to best practices.
  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

Programmatic Access to Data

To read data using python or java code, Open Analytics makes APIs available to generate the report utilising queries already available. Learn more about using APIs here.

 

Sample Queries

  1. Key Metrics Queries
  2. Reachability Queries

Reach out to us directly from your MoEngage Dashboard -> Need Help? -> Contact Support or send an email to support@moengage.com.

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