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 the reachability queries, which are used to derive the reachable users on the platform. Table and attributed to be used here -
  • users\_base\_table - This table has all the latest user attributes. This table gets updated every day.
  • string\_uid\_125 - Client-defined ID for app or site users in the user table.
  • user\_id - MoEngage defined ID for app or site users in the user table.
Know more about Reachability here. Know in detail about MoEngage Open Analytics here.

​
Push Reachability

User attributes to be used for push reachability -
  • Android - double\_moe\_rsp\_android\_144
  • iOS - double\_moe\_rsp\_ios\_157
  • Web - double\_moe\_rsp\_web\_107
If these flags have the value ‘200’ that particular user is considered as reachable. Query to get the count of users who are reachable for push notifications. 
SELECT
COUNT(user\_id) AS Usercount
FROM
account\_name.users\_base\_table
WHERE
(double\_moe\_rsp\_android\_144 = 200 OR double\_moe\_rsp\_ios\_157 = 200 OR double\_moe\_rsp\_web\_107 = 200)
Query to get the ID, MoEngage id, and email for Push reachable users on iOS. 
SELECT
string\_uid\_125 AS ID,
user\_id AS MoEID,
string\_u\_em\_157 AS email
FROM
account\_name.users\_base\_table
WHERE
double\_moe\_rsp\_ios\_157 = 200
Query to get the ID, MoEngage id, and email for users who are reachable on both iOS and Web for push notification. 
SELECT
string\_uid\_125 AS ID,
user\_id AS MoEID,
string\_u\_em\_157 AS email
FROM
account\_name.users\_base\_table
WHERE
double\_moe\_rsp\_ios\_157 = 200 AND double\_moe\_rsp\_ios\_157 = 200
The above query provides the common users who are reachable on both iOS and Web platforms. Replace OR with ANDto get users who are reachable on at least one of the iOS and Web platforms.

​
Email Reachability

User attributes to be used for email reachability -
  • string\_u\_em\_157 - Email address of the user
  • bool\_moe\_hard\_bounce\_200 - Hard bounce
  • bool\_moe\_spam\_104 - Spam
  • bool\_moe\_unsubscribe\_128 - Unsubscribe
For a user to be reachable via email -
  • The email id should be present & valid (should have ’@’ in the email address)
  • Hard bounce, Spam, and Unsubscribe should be false.
Query to get the count of users who are reachable via email. 
SELECT
COUNT(user\_id) AS Usercount
FROM
account\_name.users\_base\_table
WHERE
string\_u\_em\_157 LIKE '%@%'
AND
bool\_moe\_hard\_bounce\_200 = 'false' AND bool\_moe\_spam\_104 = 'false' AND bool\_moe\_unsubscribe\_128 = 'false'
Query to get the ID, MoEngage id, and email for reachable users on email. 
SELECT
uid\_M03C\_0\_s AS ID,
user\_id AS MoEID,
string\_u\_em\_157 AS email
FROM
account\_name.users\_base\_table
WHERE
string\_uid\_125 LIKE '%@%'
AND bool\_moe\_hard\_bounce\_200 = 'false' AND bool\_moe\_spam\_104 = 'false' AND bool\_moe\_unsubscribe\_128 = 'false'
If need be, more checks can be used to validate the email address. Here we are only checking if the email id has ’@’ present in it.

​
SMS Reachability

User attributes to be used for SMS reachability -
  • string\_u\_mb\_971 - Mobile Number of user
For a user to be reachable on SMS, the mobile number should be present and valid. Query to get the count of users who are reachable on SMS. 
SELECT
COUNT(doc\_id) AS Usercount
FROM
account\_name.usertable
WHERE
string\_u\_mb\_971 IS NOT NULL
Query to get the ID, MoEngage id, and mobile number for reachable users on SMS. 
SELECT
string\_uid\_125 AS ID,
user\_id AS MoEID,
string\_u\_mb\_971 AS MobileNumber
FROM
account\_name.usertable
WHERE
string\_u\_mb\_971 IS NOT NULL
If need be, more checks can be used to validate the mobile number. Here we are only checking if the mobile number value is present for the user.