Last Updated: Tuesday, January 17, 2023
This data set contains subscription events associated with lists of the company's account.
How much data is retained?
This data set will contain 7 days worth of data starting from the previous day. For example, if you are looking at the data set today, 7/18/2019, it will contain data from 7/11/2019 to present.
What data is included?
Column Name | Type | Nullable | Default | Comment |
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 | NUMBER(38,0) | TRUE | NULL | Vibes ID used to identify a company that is associated with the list that the subscriber opted into |
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 the company |
LAST_STATUS_CHANGE_AT | TIMESTAMP_TZ(9) | TRUE | NULL | The date timestamp that indicates when the subscription status of a subscriber changed. 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_EVENT | VARCHAR(6) | TRUE | NULL | Indicates the whether the most recent subscription event was an opt-in or an opt-out |
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 |
ACQUISITION_ACTIVITY_UID | VARCHAR(255) | TRUE | NULL | LEGACY: For use with legacy data sharing integrations |
COMPANY_UID | NUMBER(38,0) | TRUE | NULL | LEGACY: For use with legacy data sharing integrations |
| PERSON_ID | INT | TRUE | NULL | LEGACY: For use with legacy Public APIs |
PERSON_UID | VARCHAR(40) | TRUE | NULL | LEGACY: For use with legacy data sharing integrations |
SUBSCRIPTION_LIST_UID | NUMBER(38,0) | TRUE | NULL | LEGACY: For use with legacy data sharing integrations |
What are some sample queries to pull this data?
–- A query that will pull all opt_ins from yesterday
SELECT
PERSON_UID
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,CARRIER_CODE
FROM subscriptions
WHERE opt_out_reason IS NULL
AND 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)
-- A query that will pull opt_outs from yesterday
SELECT
PERSON_UID
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,CARRIER_CODE
FROM subscriptions
WHERE opt_out_reason IS NOT NULL
AND CAST(LAST_STATUS_CHANGE_AT AS DATE) >= CAST(DATEADD(day,-1, CURRENT_DATE()) AS DATE)
AND CAST(LAST_STATUS_CHANGE_AT AS DATE) < CAST(CURRENT_DATE() AS DATE)
-- A query that will pull all subscription events from yesterday
SELECT
PERSON_KEY
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,CARRIER_CODE
FROM subscriptions
WHERE opt_out_reason IS NULL
AND 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)
UNION ALL
SELECT
PERSON_KEY
,EXTERNAL_PERSON_ID
,PHONE_NUMBER
,CARRIER_CODE
FROM subscriptions
WHERE opt_out_reason IS NOT NULL
AND CAST(LAST_STATUS_CHANGE_AT AS DATE) >= CAST(DATEADD(day,-1, CURRENT_DATE()) AS DATE)
AND CAST(LAST_STATUS_CHANGE_AT AS DATE) < CAST(CURRENT_DATE() AS DATE)
Overview
Content Tools