Skip to end of metadata
Go to start of metadata

Last Updated: Thursday, May 7, 2020



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_IDINTTRUENULLLEGACY: 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())

-- 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())

-- 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())

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())
  • No labels