Person Data
This data set contains all person meta data of the company's account.
How much data is retained?
This data set will contain all persons created in the Vibes Platform associated with the company's account.
What data can be pulled?
| Column Name | Type | Nullable | Default | Comment |
|---|---|---|---|---|
| AREACODE_STATE | VARCHAR(255) | TRUE | NULL | The last known state correlated with the area code of the mobile phone number of the individual |
| CARRIER_CODE | NUMBER(38,0) | TRUE | NULL | The carrier code indicating specific carrier of the individuals mobile phone number. See Appendix - Carrier Codes for all valid carriers. |
| TENANT_KEY | NUMBER(38,0) | TRUE | NULL | Vibes ID used to identify a tenant that is associated with the individual |
| COUNTRY_CODE | VARCHAR(255) | TRUE | NULL | The last known country code of the mobile phone number |
| CUSTOM_FIELDS_JSON | JSON | TRUE | NULL | This is a JSON field that contains all custom field data associated with an individual |
| DW_CREATED_AT | TIMESTAMP_TZ(9) | TRUE | NULL | The date timestamp that indicates when the row was created in Vibes data warehouse. The time zone is central time for North America customers and UTC for European Customers |
| PHONE_NUMBER_E164_FORMAT | VARCHAR(255) | TRUE | NULL | The mobile phone number of the individual that also includes country code |
| EXTERNAL_PERSON_ID | VARCHAR(16777216) | TRUE | NULL | An individual specific identifier that is set by the company |
| PERSON_KEY | VARCHAR(40) | TRUE | NULL | Vibes UID for the individual |
| PHONE_NUMBER | VARCHAR(255) | TRUE | NULL | The mobile phone number of the individual |
| TIMEZONE | VARCHAR(16777216) | TRUE | NULL | The last known time zone of the individual |
| DW_UPDATED_AT | TIMESTAMP_TZ(9) | TRUE | NULL | The date timestamp that indicates the last time the record was updated |
| LEGACY: For use with legacy data sharing integrations | ||||
| LEGACY: For use with legacy data sharing integrations | ||||
| LEGACY: For use with legacy Public API Integrations |
Why do I have more person records than subscriptions?
A person is created whenever a mobile phone number interacts with the short code, regardless of whether they subscribed or not. It is typical to see far more person records than subscriptions.
Sample Query
-- A query that will pull all persons and some attributes. Custom fields can be added to pull in additional data points.
SELECT
PERSON_KEY
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,CARRIER_CODE
FROM PERSON_DATA
-- A query that will custom field information from individuals. Replace the CUSTOM_FIELD with your custom field's name.
SELECT CUSTOM_FIELDS_JSON:CUSTOM_FIELD::STRING
FROM PERSON_DATA
WHERE CAST(dw_created_at AS DATE) >= DATEADD(day, -1, cast(CURRENT_DATE() AS DATE))
AND CAST(dw_created_at AS DATE) < CAST(CURRENT_DATE() AS DATE)
-- A query that will return custom field JSON data from individuals and flatten them into readable table form. Each key/value pair will be represented as a row in the table. --
select person_key,
key as custom_field_name,
value as custom_field_value
from PERSON_DATA pa,
LATERAL flatten (INPUT => pa.custom_fields_json, OUTER => FALSE) a
WHERE CAST(dw_created_at AS DATE) >= DATEADD(day, -1, cast(CURRENT_DATE() AS DATE))
AND CAST(dw_created_at AS DATE) < CAST(CURRENT_DATE() AS DATE)
Updated 7 months ago