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 -

  • users_base_table - This table have all the latest user attributes. This table gets updated everyday.
  • string_uid_125 - Client defined ID for app or site users in usertable.
  • user_id - MoEngage defined ID for app or site users in usertable.

Know more about Reachability here. Know in details 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 value as '200' that particular user is considered as reachable.

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

SQL
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 ID, Moengage id and email for push reachable users on iOS.

SQL
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 ID, Moengage id and email for users who are reachable on both iOS and Web for push notification.

SQL
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 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 - 

  • string_u_em_157 - Email address of user
  • bool_moe_hard_bounce_200 - Hard bounce
  • bool_moe_spam_104 - Spam
  • bool_moe_unsubscribe_128 - 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. 

SQL
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 ID, Moengage id and email for reachable users on email.

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

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

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

SQL
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 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