Subscription Status Data
This data set contains all subscriptions and their current status associated with lists of the company's account.
How much data is retained?
This data set contains the current status of all subscribers and former subscribers, including whether they are opted in or opted out currently.
What data can be pulled?
| Column Name | Type | Nullable | Default | Description |
|---|---|---|---|---|
| ACQUISITION_ACTIVITY_NAME | VARCHAR(4000) | TRUE | NULL | The acquisition campaign that a subscriber used to opt-in to the list |
| ACQUISITION_ACTIVITY_ID | VARCHAR(255) | TRUE | NULL | Vibes UID for the acquisition campaign that the subscriber used to opt-in to the list |
| CARRIER_CODE | NUMBER(38,0) | TRUE | NULL | The carrier code indicating specific carrier of the subscriber's mobile phone number. See Appendix - Carrier Codes for all valid carriers. |
| CARRIER_NAME | VARCHAR(50) | TRUE | NULL | The name of the carrier that is associated with the subscriber's mobile phone number |
| COMPANY_KEY | VARCHAR(50) | TRUE | NULL | Vibes ID used to identify a company that is associated with the list that the subscriber opted into |
| CURRENT_SUBSCRIPTION_STATUS | VARCHAR(20) | TRUE | NULL | Indicates the current status of a subscription |
| 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 |
| EXTERNAL_PERSON_ID | VARCHAR(16777216) | TRUE | NULL | A subscriber specific identifier that is set by an external system outside Vibes. Must be associated via an API call. |
| LAST_STATUS_CHANGE_AT | TIMESTAMP_TZ(9) | TRUE | NULL | The last time the status of the subscription changed. This field will be blank for people who opt in, and will be filled out after an opt-out. The time zone is central time for North America customers and UTC for European Customers |
| OPT_IN_AT_TTZ | TIMESTAMP_TZ(9) | TRUE | NULL | The date timestamp of when the subscriber opted into a list. This timestamp will be in the time zone of the company. |
| OPT_OUT_AT_TTZ | TIMESTAMP_TZ(9) | TRUE | NULL | The date timestamp of when the subscriber opted out of a list. This timestamp will be in the time zone of the company. |
| OPT_OUT_REASON | VARCHAR(8000) | TRUE | NULL | The type of opt-out that occurred. For opt-in subscription events, this column will remain null - see Subscription API Callback Events for the list of Opt Out reasons |
| PERSON_KEY | VARCHAR(40) | TRUE | NULL | Vibes UID for the subscriber |
| PHONE_NUMBER | VARCHAR(255) | TRUE | NULL | The mobile phone number of the subscriber |
| PHONE_NUMBER_E164_FORMAT | VARCHAR(255) | TRUE | NULL | The mobile phone number of the subscriber that also includes country code |
| SUBSCRIPTION_LIST_NAME | VARCHAR(255) | TRUE | NULL | Name given to the list that the subscriber opted into |
| SUBSCRIPTION_LIST_ID | NUMBER(38,0) | TRUE | NULL | Vibes UID that signifies what list the subscriber opted into |
| LEGACY: For use with legacy Public APIs | ||||
| LEGACY: For use with legacy data sharing integrations | ||||
| LEGACY: For use with legacy data sharing integrations | ||||
| LEGACY: For use with legacy data sharing integrations | ||||
| LEGACY: For use with legacy data sharing integrations |
Sample Query
-- A query that will pull all opted-in subscriptions
SELECT
PERSON_KEY
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,CARRIER_CODE
,SUBSCRIPTION_LIST_NAME
,ACQUISITION_ACTIVITY_NAME
FROM SUBSCRIPTION_STATUS
WHERE opt_out_reason IS NULL
-- A query that will pull all opt-out subscriptions
SELECT
PERSON_KEY
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,CARRIER_CODE
,OPT_OUT_REASON
,SUBSCRIPTION_LIST_NAME
FROM SUBSCRIPTION_STATUS
WHERE opt_out_reason IS NOT NULL
Updated 9 months ago