Reachability 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 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.

​Push Reachability

​Email Reachability

​SMS Reachability

Push Reachability

Email Reachability

SMS Reachability