Skip to end of metadata
Go to start of metadata

Last Updated: Thursday, May 7, 2020



This data set contains all SMS/MMS messages 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

ACTIVITY_NAME

VARCHAR(4000)

TRUE

NULL

The campaign name that is correlated to the message

ACTIVITY_TYPE

VARCHAR(16777216)

TRUE

NULL

Type of activity the message is associated with. See here for a list of activity types.

ACTIVITY_ID

VARCHAR(255)

TRUE

NULL

Vibes UID for the campaign that associated with the message

CARRIER_CODE

NUMBER(38,0)

TRUE

NULL

The carrier code indicating specific carrier of the recipient's/sender'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 recipient's/sender's mobile phone number

CLIENT_MESSAGE_ID

VARCHAR(16777216)

TRUE

NULL

Message ID that id defined by the company

COMPANY_KEY

NUMBER(38,0)

TRUE

NULL

Vibes ID used to identify a company that is associated with the message

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

FAILURE_REASON

VARCHAR(16777216)

TRUE

NULL

A smpp_error_code indicating whether the message was successfully sent to the Carrier or not. Value of 0 is success, all other codes are failure.

MESSAGE

VARCHAR(16777216)

TRUE

NULL

The text of the message

MESSAGE_STATUS

VARCHAR(16777216)

TRUE

NULL

Indicates whether the message was successfully received by the intended recipient or not. SUCCESS means the message was successfully received. FAILURE means the message failed to reach the recipient.

MESSAGE_TYPE

VARCHAR(16777216)

TRUE

NULL

Indicates whether the message was sent from or received by the short code. Mobile Originated (MO) is sent from the device to the short code. Mobile Terminated (MT) is sent from the short code to the device.

MESSAGE_ID

VARCHAR(16777216)

TRUE

NULL

UUID of the message

PERSON_KEY

VARCHAR(40)

TRUE

NULL

Vibes UID for the subscriber

PHONE_NUMBER

VARCHAR(255)

TRUE

NULL

The mobile phone number of the recipient/sender

PHONE_NUMBER_E164_FORMAT

VARCHAR(255)

TRUE

NULL

The mobile phone number of the recipient/sender that also includes country code

RECEIPT_AT_TTZ

TIMESTAMP_TZ(9)

TRUE

NULL

The date timestamp when a message was generated on our platform or received from the carrier. This timestamp will be in the time zone of the company.

SHORTCODE

VARCHAR(16777216)

TRUE

NULL

The short code that the message was sent on

ACTIVITY_UID

VARCHAR(255)

TRUE

NULL

LEGACY: For use with legacy Data Sharing integrations

PERSON_UID

VARCHAR(40)

TRUE

NULL

LEGACY:  For use with legacy Data Sharing integrations
PERSON_IDINTTRUENULLLEGACY: for use with legacy Public APIs

MESSAGE_UID

VARCHAR(16777216)

TRUENULLLEGACY:  For use with legacy Data Sharing integrations

COMPANY_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 sms/mms messages from yesterday
SELECT
PERSON_KEY
, EXTERNAL_PERSON_ID
, PHONE_NUMBER
, MESSAGE
, MESSAGE_ID
FROM MESSAGES
WHERE 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)
  • No labels