Key Metrics Queries for Open Analytics

MoEngage Open Analytics provide capabilities to directly access data from the MoEngage data warehouse. Using SQL queries any data which is present in MoEngage can be accessed.

This document provides all queries for the key metrics provided in MoEngage dashboard. These queries can be used to get all the data using Open Analytics.

Know more about dashboard Key Metrics here. Know in details about MoEngage Open Analytics here.

Daily Active Users

To calculate daily active users, table and attributes required are -

  • Event used - App/Site Opened. Table name for this event is MOE_APP_OPENED_M03C_15295.
  • Attribute for Platform in App/Site Opened - DEVICE.os_key_M03C_0_s
  • Time Attribute in App/Site Opened - EVENTS.serverTime_MOETS 
  • Change __PARTITIONTIME for the required date selection.

Query to get the daily active users count -

SELECT
DATE(EVENTS.serverTime_MOETS) AS Date,
COUNT(DISTINCT(USER._id_M03C_0_s)) AS Usercount
FROM
account_name.MOE_APP_OPENED_M03C_15295
WHERE
_PARTITIONTIME >= "2020-01-01"
AND _PARTITIONTIME <= "2020-01-31"
GROUP BY
1
ORDER BY
1

 

Query to get the daily active users count for each platform-

SELECT
DATE(EVENTS.serverTime_MOETS) AS Date,
DEVICE.os_key_M03C_0_s AS Platform,
COUNT(DISTINCT(USER._id_M03C_0_s)) AS Usercount
FROM
account_name.MOE_APP_OPENED_M03C_15295
WHERE
_PARTITIONTIME >= "2020-01-01"
AND _PARTITIONTIME <= "2020-01-31"
GROUP BY
1, 2
ORDER BY
1

 

Monthly Active Users

To calculate monthly active users, table and attributes required are -

  • Event used - App/Site Opened. Table name for this event is MOE_APP_OPENED_M03C_15295.
  • Attribute for Platform in App/Site Opened - DEVICE.os_key_M03C_0_s
  • Time Attribute in App/Site Opened - EVENTS.serverTime_MOETS 
  • Change __PARTITIONTIME for the required date selection.

Query to get the monthly active users count -

SELECT
FORMAT_DATE("%B, %Y", DATE(EVENTS.serverTime_MOETS)) AS Date,
COUNT(DISTINCT(USER._id_M03C_0_s)) AS Usercount
FROM
account_name.MOE_APP_OPENED_M03C_15295
WHERE
_PARTITIONTIME >= "2020-01-01"
AND _PARTITIONTIME <= "2020-12-31"
GROUP BY
1
ORDER BY
1

 

Query to get the monthly active users count for each platform-

SELECT
FORMAT_DATE("%B, %Y", DATE(EVENTS.serverTime_MOETS)) AS Date,
DEVICE.os_key_M03C_0_s AS Platform,
COUNT(DISTINCT(USER._id_M03C_0_s)) AS Usercount
FROM
account_name.MOE_APP_OPENED_M03C_15295
WHERE
_PARTITIONTIME >= "2020-01-01"
AND _PARTITIONTIME <= "2020-12-31"
GROUP BY
1, 2
ORDER BY
1

 

New Users

To calculate daily new users, table and attributes required are -

  • Table for all the latest user attributes (This table gets updated everyday) - usertable
  • MoEngage defined ID for app/site users in usertabel - doc_id
  • User Created Date in usertabel - cr_t_M03C_0_d
  • Change cr_t_M03C_0_d for the required date selection 

Query to get the daily new users count -

SELECT
DATE(cr_t_M03C_0_d) AS Date,
COUNT(DISTINCT(doc_id)) AS Usercount
FROM
account_name.usertable WHERE cr_t_M03C_0_d >= "2020-01-01"
AND cr_t_M03C_0_d <= "2020-01-31"
GROUP BY
1
ORDER BY
1

 

Uninstalled Users

To calculate daily uninstalled users table and attributes required are -

User table checks the fine instal status of user.

  • Table for all the latest user attributes (This table gets updated everyday) - usertable
  • MoEngage defined ID for app/site users in usertabel - doc_id
  • Install Status - installed_M03C_0_s

Device uninstall event to get uninstall action and date time - 

  • Event used - Device Uninstalled - Device_Uninstall_M03S_7a_M03C_33024
  • Attribute for Platform in App/Site Opened - DEVICE.os_key_M03C_0_s
  • Time Attribute in App/Site Opened - EVENTS.serverTime_MOETS 
  • Change __PARTITIONTIME for the required date selection.

Query to get the daily uninstalled users count -

SELECT
DATE(EVENTS.serverTime_MOETS) AS Date,
COUNT(DISTINCT(USER._id_M03C_0_s)) AS Usercount
FROM
account_name.Device_Uninstall_M03S_7a_M03C_33024
WHERE
_PARTITIONTIME >= "2019-09-26 00:00:00"
AND _PARTITIONTIME <= "2019-09-27 00:00:00"
AND USER._id_M03C_0_s IN (
SELECT
DISTINCT(doc_id)
FROM
account_name.usertable
WHERE
installed_M03C_0_s = "false" )
GROUP BY
1
ORDER BY
1

 

Query to get the daily uninstalled users count for each platform-

SELECT
DATE(EVENTS.serverTime_MOETS) AS Date,
DEVICE.os_key_M03C_0_s as Platform,
COUNT(DISTINCT(USER._id_M03C_0_s)) AS Usercount
FROM
account_name.Device_Uninstall_M03S_7a_M03C_33024
WHERE
_PARTITIONTIME >= "2019-09-26 00:00:00"
AND _PARTITIONTIME <= "2019-09-27 00:00:00"
AND USER._id_M03C_0_s IN (
SELECT
DISTINCT(doc_id)
FROM
account_name.usertable
WHERE
installed_M03C_0_s = "false" )
GROUP BY
1, 2
ORDER BY
1

 

New Web Push Subscribers

To calculate daily active users, table and attributes required are -

  • Event used -Subscribed to Web Push. Table name -MOE_USER_SUBSCRIBED
  • Time Attribute in Subscribed to Web Push - EVENTS.serverTime_MOETS 
  • Change __PARTITIONTIME for the required date selection.

Query to get the new web push subscribers -

SELECT
DATE(EVENTS.serverTime_MOETS) AS Date,
COUNT(DISTINCT(USER._id_M03C_0_s)) AS Usercount
FROM
account_name.MOE_USER_SUBSCRIBED
WHERE
_PARTITIONTIME >= "2020-01-01"
AND _PARTITIONTIME <= "2020-01-31"
GROUP BY
1
ORDER BY
1

 

Aggregate Web Push Subscribers

To calculate Aggregate Web Push Subscribers, table and attributes required are -

  • Table for all the latest user attributes (This table gets updated everyday) - usertable
  • MoEngage defined ID for app/site users in usertabel - doc_id
  • Web Subscription Status in usertable- moe_sub_w_M03C_0_s

Query to get the aggregate web push subscribers -

SELECT
COUNT(doc_id) AS TotalCount
FROM
Adda_247_M03S_5M_M03C_128.usertable
WHERE
moe_sub_w_M03C_0_s = "true"

 

Conversion Goal & Revenue  

To calculate total count of conversions and unique converted users, table and attributes required are -

  • Event used - Goal Event specified in App Settings.
  • Attribute for Revenue - Revenue attribute specified in App Settings.
  • Attribute for Platform in Goal Event - DEVICE.os_key_M03C_0_s
  • Time Attribute in Goal Event - EVENTS.serverTime_MOETS
  • Change __PARTITIONTIME for the required date selection.

Query to get the daily conversion count -

SELECT
DATE(EVENTS.serverTime_MOETS) AS Date,
COUNT(EVENTS.serverTime_MOETS) AS Conversioncount
FROM
account_name.goal_event
WHERE
_PARTITIONTIME >= "2020-01-01"
AND _PARTITIONTIME <= "2020-01-31"
GROUP BY
1
ORDER BY
1

 

Query to get the daily unique converted users-

SELECT
DATE(EVENTS.serverTime_MOETS) AS Date,
COUNT(DISTINCT(USER._id_M03C_0_s)) AS Usercount
FROM
account_name.goal_event
WHERE
_PARTITIONTIME >= "2020-01-01"
AND _PARTITIONTIME <= "2020-01-31"
GROUP BY
1
ORDER BY
1

 

Query to get the daily revenue-

SELECT
DATE(EVENTS.serverTime_MOETS) AS Date,
SUM(revenue_attribute) AS Revenue
FROM
account_name.goal_event
WHERE
_PARTITIONTIME >= "2020-01-01"
AND _PARTITIONTIME <= "2020-01-31"
GROUP BY
1
ORDER BY
1

  

 

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