Subscriptions Data
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 most recent data refresh. Data is refreshed once per day. For example, if you are looking at the data set on 7/18, it will contain data from 7/11 to present.
What data can be pulled?
| Column Name | Type | Nullable | Default | Comment |
|---|---|---|---|---|
| ACQUISITION_ACTIVITY_NAME | VARCHAR(4000) | TRUE | NULL | The name of 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 |
| 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. This must be associated via an API integration. |
| 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_EVENT | VARCHAR(6) | TRUE | NULL | Indicates 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 |
| LEGACY: For use with legacy data sharing integrations | ||||
| LEGACY: For use with legacy data sharing integrations | ||||
| LEGACY: For use with legacy Public APIs | ||||
| LEGACY: For use with legacy data sharing integrations | ||||
| LEGACY: For use with legacy data sharing integrations |
Sample Query
–- 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)
Updated 9 months ago