Open Analytics

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 realtime directly from the data storage. MoEngage enables customers to directly query on data warehouse and get desired data.

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
  3. Export all of the desired data into your system

This doc helps customers in accessing the data from the MoEngage warehouse using queries.

 

Data Categories

All data received at 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 Warehouse, we process all the device attributes, user attributes, event attributes, and store them in one event table. Each Event has its own table consisting of Event Attributes, Device Attributes, and User Attributes.

Tables Name - Event Name

event.
attribute 1
event.
attribute n
device.
attribute 1
device.
attribute n 
user.
attribute 1 
user.
attribute n
... ... ... ... ... ...
... ... ... ... ... ...

 

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

Select event.serverTime, count( events.servertime) as metric
FROM
`account_name.moe_app_opened`
where _PARTITIONTIME >= '2018-09-23'
group by 1
order by 1

 

Event Tables can be joined using common key/attributes such as - USER._id_M03C_0_s

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. Names have structure as ‘Event_Name_M03C_XXXX’ and ‘Attribute_Name_M03C_XXXX’. For ease of use, we have provided an excel file which has all the readable names and names present in the warehouse.

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. _PARTITIONTIME - 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. EVENTS.serverTime_MOETS - GMT time at which events reach MoEngage servers.
  3. USER._id_M03C_0_s - This is a MoEngage generated User Id.
  4. USER.uid_M03C_0_s - This is User Id generated by App/Site.
  5. EVENTS.action_s - Event Name
  6. EVENTS.gTime_M03C_8_s - Device Time in GMT
  7. EVENTS.dateAndtime_M03C_64_s - Device Time in Local Time

 

Open analytics Usage Best Practices

  1. Must always use _PARTITIONTIME in your queries to keep the cost minimum. If _PARTITIONTIME is not used, then BigQuery will scan all the data and scanning costs will be higher for your account.
  2. BigQuery is a columnar database, hence using 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 details -
    BigQuery best practices: Controlling costs | BigQuery 

 

Connecting with Visualization Tools

All SQL queries work on MoEngage Warehouse. Data can be accessed using multiple platforms such as Tableau, ModeAnalytics, ClickView, Data Studio, etc. Customers can also query directly on Bigquery as well.

Connecting Data Studio (Free Visualization tool from Google) with MoEngage Warehouse -
https://cloud.google.com/bigquery/docs/visualize-data-studio

Connecting Tableau with MoEngage Warehouse -
https://onlinehelp.tableau.com/current/pro/desktop/en-us/examples_googlebigquery.html 

Connecting Mode Analytics with MoEngage Warehouse -
https://about.modeanalytics.com/big-query/

Querying directly on BigQuery -
https://bigquery.cloud.google.com/project/analyticsmainproject

A list of Supported Visualization tools can be found here.
https://cloud.google.com/bigquery/providers#tab2

 

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 get it enabled for your account. 

 

Data Export

To transfer data out of the MoEngage Warehouse, you need to have a Google Cloud account. This Google Cloud account should have Google Cloud Storage (GCS) in the same region, where MoEngage has stored data. MoEngage support team will help you know in which region your data is stored.

Data can be moved from MoEngage BigQuery account to your GCS, if data is stored in the same data region. You can access these links for data transfer.

https://cloud.google.com/bigquery-transfer/docs/transfer-service-overview

https://cloud.google.com/bigquery-transfer/docs/all-how-to

 

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