Push Device Status Data
This data set contains push devices associated with the company's account.
How much data is retained?
All data is retained unless it has been specifically purged.
What data can be pulled?
| Column Name | Type | Nullable | Default | Comment |
|---|---|---|---|---|
| DEVICE_ID | NUMBER(38,0) | FALSE | NULL | Unique id of the device |
| TENANT_KEY | VARCHAR(16777216) | FALSE | NULL | Vibes ID used to identify a tenant that is associated with the individual |
| APP_ID | VARCHAR(40) | TRUE | NULL | ID of the mobile app |
| VIBES_DEVICE_ID | VARCHAR(255) | TRUE | NULL | Vibes unique identifier for each device |
| DEVICE_OS | VARCHAR(255) | TRUE | NULL | Operating system of the push device (Android or IOS) |
| PERIOD_START | TIMESTAMP_TZ(9) | TRUE | NULL | Start of device validity period |
| PERIOD_END | TIMESTAMP_TZ(9) | TRUE | NULL | End of device validity period |
| PERSON_KEY | VARCHAR(40) | TRUE | NULL | Vibes UID for the recipient |
| PUSH_TOKEN | VARCHAR(16777216) | TRUE | NULL | Android or IOS Push token |
| OS_VERSION | VARCHAR(16777216) | TRUE | NULL | Version of the operating system |
| SDK_VERSION | VARCHAR(16777216) | TRUE | NULL | Version of the SDK |
| LAST_REGISTERED_DATE | TIMESTAMP_TZ(9) | TRUE | NULL | Last Registered |
| HARDWARE_MAKE | VARCHAR(255) | TRUE | NULL | Make of user’s device |
| HARDWARE_MODEL | VARCHAR(255) | TRUE | NULL | Model number of user’s device |
| APP_VERSION | VARCHAR(255) | TRUE | NULL | Version of the app user is on |
| ADVERTISING_ID | VARCHAR(255) | TRUE | NULL | Advertising ID |
| LOCATION_LAT | FLOAT | TRUE | NULL | Latitude (at registration) |
| LOCATION_LONG | FLOAT | TRUE | NULL | Longitude (at registration) |
| LOCALE | VARCHAR(255) | TRUE | NULL | Locale |
| LAST_OPENED_AT | TIMESTAMP_TZ(9) | TRUE | NULL | When the user opened the app |
| DW_CREATED_AT | TIMESTAMP_TZ(9) | TRUE | NULL | When this person was added |
| DW_UPDATED_AT | TIMESTAMP_TZ(9) | TRUE | NULL | When it was last updated |
| PERIOD_START_TTZ | TIMESTAMP_TZ(9) | TRUE | NULL | Date/time of the start of the validity period |
| PERIOD_END_TTZ | TIMESTAMP_TZ(9) | TRUE | NULL | Date/time of the end of the validity period |
| PERIOD_START_DATE_DIM_KEY | TIMESTAMP_TZ(9) | TRUE | NULL | Date of the start of the validity period |
| PERIOD_END_DATE_DIM_KEY | TIMESTAMP_TZ(9) | TRUE | NULL | Date of the end of the validity period |
| TIMEZONE | VARCHAR(16777216) | TRUE | NULL | Timezone of the device |
| PUSH_STATUS | VARCHAR(16777216) | TRUE | NULL | Was this person active in the app in the last 180 days? Values: ACTIVE, INACTIVE, or UNINSTALLED |
| PUSH_ADDRESSABLE | VARCHAR(16777216) | TRUE | NULL | Do we have a push token for this person? (ACTIVE/INACTIVE) |
| EXTERNAL_PERSON_ID | VARCHAR(16777216) | TRUE | NULL | Customer's unique identifier as assigned by an external system. It must be associated at registration or through an API integration. |
Sample Query
–- A query that will pull push messages from yesterday
SELECT
EXTERNAL_PERSON_ID
,PERSON_KEY
,DEVICE_ID
,APP_ID
,VIBES_DEVICE_ID
,DEVICE_OS
,PUSH_TOKEN
,OS_VERSION
,SDK_VERSION
,LAST_REGISTERED_DATE
,HARDWARE_MAKE
,HARDWARE_MODEL
,APP_VERSION
,TIME_ZONE
,LAST_OPENED_AT
FROM PUSH_DEVICE
WHERE CAST(DW_CREATED_AT AS DATE) >= CAST(DATEADD(day,-1, CURRENT_DATE()) AS DATE)
AND CAST(DW_CREATED_AT AS DATE) < CAST(CURRENT_DATE() AS DATE)
Updated almost 2 years ago