1. User Guide
  2. Analyze
  3. Analytics - How to
Key Metrics Queries for Open Analytics

MoEngage Open Analytics provides 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 the MoEngage dashboard. These queries can be used to get all the data using Open Analytics.

Know more about dashboard Key Metrics here. Know detail about MoEngage Open Analytics here.

Daily Active Users

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

  • Event used - App/Site Opened. Table name for this event is - moe_app_opened_828.
  • Attribute for Platform in App/Site Opened - os
  • Time Attribute in App/Site Opened - server_time
  • Change day for the required date selection.

Query to get the Daily Active Users count -

SQL
SELECT
 DATE(server_time) AS Date,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.moe_app_opened_828
WHERE
 day >= "2020-01-01"
 AND day <= "2020-01-31"
GROUP BY
 1
ORDER BY
 1

 

Query to get the Daily Active Users count for each platform-

SQL
SELECT
 DATE(server_time) AS Date,
 os AS Platform,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.moe_app_opened_828
WHERE
 day >= "2020-01-01"
 AND day <= "2020-01-31"
GROUP BY
 1, 2
ORDER BY
 1

 

Monthly Active Users

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

  • Event used - App/Site Opened. Table name for this event is - moe_app_opened_828.
  • Attribute for Platform in App/Site Opened - 
  • Time Attribute in App/Site Opened - server_time 
  • Change day for the required date selection.

Query to get the Monthly Active Users count -

SQL
SELECT
 FORMAT_DATE("%B, %Y", DATE(server_time)) AS Date,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.moe_app_opened_828
WHERE
 day >= "2020-01-01"
 AND day <= "2020-12-31"
GROUP BY
 1
ORDER BY
 1

 

Query to get the Monthly Active Users count for each platform-

SQL
SELECT
 FORMAT_DATE("%B, %Y", DATE(server_time)) AS Date,
 os AS Platform,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.moe_app_opened_828
WHERE
 day >= "2020-01-01"
 AND day <= "2020-12-31"
GROUP BY
 1, 2
ORDER BY
 1

 

New Users

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

  • Table for all the latest user attributes (This table gets updated every day) - users_base_table
  • MoEngage defined ID for app/site users in usertable - user_id
  • User Created Date in user table - datetime_cr_t_469
  • Change datetime_cr_t_469 for the required date selection 

Query to get the daily New Users count -

SQL
SELECT
 DATE(datetime_cr_t_469) AS Date,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.users_base_table WHERE datetime_cr_t_469 >= "2020-01-01"
 AND datetime_cr_t_469 <= "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 install status of the user.

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

Device uninstall event to get uninstall action and date time - 

  • Event used - Device Uninstalled - device_uninstall_985
  • Attribute for Platform in Device Uninstalled - os
  • Time Attribute in App/Site Opened - server_time 
  • Change day for the required date selection.

Query to get the daily Uninstalled Users count -

SQL
SELECT
 DATE(server_time) AS Date,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.device_uninstall_985
WHERE
 day >= "2019-09-26 00:00:00"
 AND day <= "2019-09-27 00:00:00"
 AND user_id IN (
 SELECT
 DISTINCT(user_id)
 FROM
 account_name.users_base_table
 WHERE
 bool_installed_178 = "false" )
GROUP BY
 1
ORDER BY
 1

 

Query to get the daily Uninstalled Users count for each platform-

SQL
SELECT
 DATE(server_time) AS Date,
 os as Platform,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.device_uninstall_985
WHERE
 day >= "2019-09-26 00:00:00"
 AND day <= "2019-09-27 00:00:00"
 AND user_id IN (
  SELECT
  DISTINCT(user_id)
  FROM
   account_name.user_base_table
  WHERE
   bool_installed_178 = "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 - server_time 
  • Change day for the required date selection.

Query to get the New Web Push Subscribers -

SQL
SELECT
 DATE(server_time) AS Date,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.MOE_USER_SUBSCRIBED
WHERE
 day >= "2020-01-01"
 AND day <= "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) - user_base_table
  • MoEngage defined ID for app/site users in usertabel - user_id
  • Web Subscription Status in usertable- bool_moe_sub_w_106

Query to get the Aggregate Web Push Subscribers -

SQL
SELECT
 COUNT(user_id) AS TotalCount
FROM
 account_name.user_base_table
WHERE
 bool_moe_sub_w_106 = "true"

 

Conversion Goal & Revenue  

To calculate the total count of conversions and unique converted users, the 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 - os
  • Time Attribute in Goal Event - server_time
  • Change day for the required date selection.

Query to get the daily Conversion Count -

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

 

Query to get the daily Unique Converted Users-

SQL
SELECT
 DATE(server_time) AS Date,
 COUNT(DISTINCT(user_id)) AS Usercount
FROM
 account_name.goal_event
WHERE
 day >= "2020-01-01"
 AND day <= "2020-01-31"
GROUP BY
 1
ORDER BY
 1

 

Query to get the daily Revenue-

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

 

 

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

How can we improve this article?