Message Data
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 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 | Description |
|---|---|---|---|---|
| ACTIVITY_NAME | VARCHAR(4000) | TRUE | NULL | The campaign name that is correlated to the message |
| ACTIVITY_TYPE | VARCHAR(16777216) | TRUE | NULL | Type of campaign the message data 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 | VARCHAR(50) | 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 |
| MESSAGE_FORMAT | VARCHAR(10) | TRUE | NULL | The format type of the message (MMS or SMS) |
| 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 sms messages from yesterday
SELECT
PERSON_KEY
, EXTERNAL_PERSON_ID
, PHONE_NUMBER
, MESSAGE
, MESSAGE_ID
,CARRIER_NAME
,MESSAGE_STATUS
,MESSAGE_TYPE
,FAILURE_REASON
,ACTIVITY_TYPE
,SHORTCODE
,DW_CREATED_AT
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)
Updated 9 months ago