Skip to main content
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

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

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

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

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

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

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

SELECT
DATE(server_time) AS Date,
o
s
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

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

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

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

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

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