Reachability 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 the reachability queries, which are used to derive the reachable users on the platform.

Table and attributed to be used here -

  • usertable - This table have all the latest user attributes. This table gets updated everyday.
  • uid_M03C_0_s  - Client defined ID for app/site users in usertabel.
  • doc_id - MoEngage defined ID for app/site users in usertabel.

Know more about Reachability here. Know in details about MoEngage Open Analytics here.

Push Reachability

User attributes to be used for push reachability - 

  • Overall reachability - moe_rsu_M03C_0_f
  • Android -  moe_rsp_android_M03C_0_f
  • iOS - moe_rsp_ios_M03C_0_f
  • Web - moe_rsp_web_M03C_0_f 

If these flags have value as '200' that particular user is considered as reachable.

Query to get the count of user who are reachable for push notification.

SELECT
COUNT(doc_id) AS Usercount
FROM
account_name.usertable
WHERE
moe_rsu_M03C_0_f = 200

 
Query to get ID, Moengage id and email for push reachable users on iOS.

SELECT
uid_M03C_0_s AS ID,
doc_id AS MoEID,
u_em_M03C_0_s AS email
FROM
account_name.usertable
WHERE
moe_rsp_ios_M03C_0_f = 200


Query to get ID, Moengage id and email for users who are reachable on both iOS and Web for push notification.

SELECT
uid_M03C_0_s AS ID,
doc_id AS MoEID,
u_em_M03C_0_s AS email
FROM
account_name.usertable
WHERE
moe_rsp_ios_M03C_0_f = 200
AND moe_rsp_web_M03C_0_f = 200


The above query provide the common users who are reachable on both iOS and Web platform. Replace OR with AND to get users who are reachable on at least one of the iOS and Web platform.

 

Email Reachability

User attributes to be used for email reachability - 

  • u_em_M03C_0_s - Email address of user
  • moe_hard_bounce_M03C_0_s - Hard bounce
  • moe_spam_M03C_0_s - Spam
  • moe_unsubscribe_M03C_0_s - Unsubscribe

For a user to be reachable on email, the email id should be present & valid and Hard bounce, Spam and Unsubscribe, all of them should be false.

Query to get the count of user who are reachable on email. 

SELECT
COUNT(doc_id) AS Usercount
FROM
account_name.usertable
WHERE
u_em_M03C_0_s LIKE '%@%'
AND moe_hard_bounce_M03C_0_s = 'false'
AND moe_spam_M03C_0_s = 'false'
AND moe_unsubscribe_M03C_0_s = 'false'

 
Query to get ID, Moengage id and email for reachable users on email.

SELECT
uid_M03C_0_s AS ID,
doc_id AS MoEID,
u_em_M03C_0_s AS email
FROM
account_name.usertable
WHERE
u_em_M03C_0_s LIKE '%@%'
AND moe_hard_bounce_M03C_0_s = 'false'
AND moe_spam_M03C_0_s = 'false'
AND moe_unsubscribe_M03C_0_s = 'false'

If need be, more checks can be used to validate 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 - 

  • u_mb_M03C_0_s - 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 user who are reachable on SMS. 

SELECT
COUNT(doc_id) AS Usercount
FROM
account_name.usertable
WHERE
u_mb_M03C_0_s IS NOT NULL

 
Query to get ID, Moengage id and mobile number for reachable users on SMS.

SELECT
uid_M03C_0_s AS ID,
doc_id AS MoEID,
u_mb_M03C_0_s AS MobileNumber
FROM
account_name.usertable
WHERE
u_mb_M03C_0_s IS NOT NULL

If need be, more checks can be used to validate mobile number. Here we are only checking if the mobile number value is present for the user.

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