> ## Documentation Index
> Fetch the complete documentation index at: https://moengage-user-guide.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/user-guide/segment/reachability/user-reachability). Know in detail about [MoEngage Open Analytics here.](/user-guide/analyze/custom-analysis/open-analytics)

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

<CodeGroup>
  ```sql SQL wrap theme={null}
  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)
  ```
</CodeGroup>

Query to get the ID, MoEngage id, and email for Push reachable users on iOS.

<CodeGroup>
  ```sql SQL wrap theme={null}
  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
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL wrap theme={null}
  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
  ```
</CodeGroup>

The above query provides the common users who are reachable on both iOS and Web platforms. Replace **OR** with **AND**to 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.

<CodeGroup>
  ```sql SQL wrap theme={null}
  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'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL wrap theme={null}
  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'
  ```
</CodeGroup>

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.

<CodeGroup>
  ```sql SQL wrap theme={null}
  SELECT
  COUNT(doc\_id) AS Usercount
  FROM
  account\_name.usertable
  WHERE
  string\_u\_mb\_971 IS NOT NULL
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL wrap theme={null}
  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
  ```
</CodeGroup>

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.