File Layouts for SMS

The following table describes the layouts of the SMS export files.

NOTE: All timestamps are presented in UTC with this format: DD-Mon-YYYY HH24:MI:SS

Clicked

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                 

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the Campaign

RIID

NUMBER

NO

Unique ID assigned for this specific List record.

CUSTOMER_ID

VARCHAR2(255)

 

A unique identifier of the recipient/record that matches a customer ID or unique identifier in your system of record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event.

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB.

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

LAUNCH_ID

NUMBER

NO

Numerical identifier of the launch instance

program_id                               

INTEGER

 

Numerical identifier of the Program. -1 if unknown

aggregator_id         

NUMBER

NO

Numerical identifier of the aggregator. 2 for Velti

country_code

VARCHAR2(2)

 

Mobile country code

mobile_code               

VARCHAR2(50)

NO

Short or long mobile code

mobile_number             

VARCHAR2(255)

NO

Mobile phone number
OFFER_NAME (LINK NAME) VARCHAR2(255) NO Details Offer Metadata

offer_number          

NUMBER

NO

Numerical identifier used for click tracking
OFFER_URL (LONG URL) VARCHAR2(4000) NO Details offer metadata

remote_addr             

VARCHAR2(15)

 

IP address of the device from which the recipient clicked.

USER_AGENT_STRING

VARCHAR2(512)

YES

Available only if enabled for your account.

The User Agent String, which identifies the user’s device and browser version.

OPERATING_SYSTEM

VARCHAR2(4)

YES

The user’s operating system.

NOTE: This field contains legacy device data and will be deprecated in a future release.

BROWSER

VARCHAR2(2)

YES

The user’s browser.

NOTE: This field contains legacy device data and will be deprecated in a future release.

BROWSER_TYPE

VARCHAR2(2)

YES

The user’s browser type.

NOTE: This field contains legacy device data and will be deprecated in a future release.

OPERATING_SYSTEM_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s operating system.

OS_VENDOR_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s operating system vendor.

BROWSER_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s browser.

BROWSER_TYPE_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s browser type.

DEVICE_TYPE_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s device type.

 

Received

Field Name

Type and Max Length

Null?

Description/Comments

EVENT_UUID

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

ACCOUNT_ID

NUMBER

NO

Account number

LIST_ID

NUMBER

 

Numerical identifier for the list object used by the campaign

RIID

NUMBER

NO

Unique ID assigned for this specific List record. -1 if unknown

CUSTOMER_ID

VARCHAR2(255)

 

A unique identifier of the recipient/record that matches a customer ID or unique identifier in your system of record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

LAUNCH_ID

NUMBER

NO

Numerical identifier of the launch instance

program_id                    

NUMBER

NO

Numerical identifier of the Program. -1 if unknown

aggregator_id         

NUMBER

NO

Numerical identifier of the aggregator. 2 for Velti

country_code     

VARCHAR2(2)

 

Mobile country code

mobile_code                

VARCHAR(50)

NO

Short or long mobile code

mobile_number             

VARCHAR2(255)

NO

Mobile phone number

MOBILE_KEYWORD

VARCHAR(50)    

NO

Mobile keyword, converted to upper or lowercase. ‘unknown’  if not in the system

MOBILE_CARRIER

VARCHAR2(250)

NO

Name of  the mobile carrier

AGGREGATOR_
MESSAGE_id

 

 

Numerical identifier from the aggregator

MSG_BODY

VARCHAR

YES

The content of received the message

Sent

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number

LIST_ID

NUMBER

 

Numerical identifier of the List object used by the campaign

RIID

NUMBER

NO

Unique ID assigned for this specific List record

CUSTOMER_ID

VARCHAR2(255)

 

A unique identifier of the recipient/record that matches a customer ID or unique identifier in your system of record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

LAUNCH_ID

NUMBER

NO

Numerical identifier of the launch instance

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

aggregator_id         

NUMBER

NO

Numerical identifier of the aggregator. 2 for Velti

country_code      

VARCHAR2(2)

 

Mobile country code

mobile_code               

VARCHAR2(50)

NO

Short or long mobile code

mobile_number             

VARCHAR2(255)

NO

Mobile phone number

mobile_channel        

VARCHAR2(50)

NO

The channel for the event, either SMS or PUSH

mobile_keyword                  

VARCHAR2(50)

 

Mobile keyword, converted to upper or lowercase. ‘unknown’  if not in the system

OFFER_SIGNATURE_ID

NUMBER

 

Offers that were sent

msg_split_count       

NUMBER

NO

Number of split messages

MESSAGE

VARCHAR

NO

Body of the sent message

CUSTOM_PROPERTIES

 

 

 

DLT_TEMPLATE_ID

VARCHAR2(50 BYTE)

YES

DLT template ID is a parameter in the MT payloads for SMS messages that use India country codes. This field will not have a value when the SMS messages use other country codes.

Converted

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                 

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the campaign

RIID

NUMBER

NO

Unique ID assigned for this specific List record

CUSTOMER_ID

VARCHAR2(255)

 

A unique identifier of the recipient/record that matches a customer ID or unique identifier in your system of record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

LAUNCH_ID

NUMBER

NO

Numerical identifier of the launch instance

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

aggregator_id         

NUMBER

NO

Numerical identifier of the aggregator. 2 for Velti

COUNTRY_CODE     

VARCHAR2(2)

 

Mobile country code

MOBILE_CODE                

VARCHAR(50)

NO

Short or long mobile code

MOBILE_NUMBER             

VARCHAR2(255)

NO

Mobile phone number

OFFER_NUMBER                       

NUMBER

NO

Numerical identifier used for click tracking

ORDER_ID

VARCHAR2(50)

 

The order ID value of the specific order as passed by you when registering the Conversion.

The value from the OrderID name-value pair when calling the Conversion Tracking image pixel

ORDER_TOTAL

NUMBER(15,2)

 

The total amount of the order as passed by you when registered in the Conversion.

The value from the OrderTotal name-value pair when calling the Conversion Tracking image pixel

ORDER_QUANTITY

NUMBER

 

The total number of items in the order as passed by you when registering the Conversion / Purchase

The value from the NumItem name-value pair when calling the Conversion Tracking image pixel

USER_AGENT_STRING

VARCHAR2(512)

YES

Available only if enabled for your account.

The User Agent String, which identifies the user’s device and browser version.

  

OPERATING_SYSTEM

VARCHAR2(4)

YES

The user’s operating system.

NOTE: This field contains legacy device data and will be deprecated in a future release.

  

BROWSER

VARCHAR2(2)

YES

The user’s browser.

NOTE: This field contains legacy device data and will be deprecated in a future release.

  

BROWSER_TYPE

VARCHAR2(2)

YES

The user’s browser type.

NOTE: This field contains legacy device data and will be deprecated in a future release.

  

OPERATING_SYSTEM_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s operating system.

  

OS_VENDOR_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s operating system vendor.

  

BROWSER_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s browser.

  

BROWSER_TYPE_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s browser type.

  

DEVICE_TYPE_INFO

VARCHAR2(1000)

YES

Available only if enabled for your account.

The user’s device type.

  

Skipped

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                

VARCHAR2(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number (for example. 1234)

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the Campaign

RIID

NUMBER

NO

Unique ID assigned for this specific List record

CUSTOMER_ID

VARCHAR2(255)

 

A unique identifier of the recipient/record that matches a customer ID or unique identifier in your system of record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event.

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB.

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

LAUNCH_ID

NUMBER

NO

Numerical identifier of the launch instance

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

aggregator_id         

NUMBER

 

Numerical identifier of the aggregator. 2 for Velti

COUNTRY_CODE      

VARCHAR2(2)

 

Mobile country code

MOBILE_CODE               

VARCHAR2(50)

NO

Short or long mobile code

MOBILE_NUMBER             

VARCHAR2(255)

NO

Mobile phone number

MOBILE_CHANNEL        

VARCHAR2(50)

NO

The channel for the event, either SMS or PUSH

MOBILE_KEYWORD                  

VARCHAR2(50)

 

Mobile keyword, converted to upper or lowercase. ‘unknown’  if not in the system

OFFER_SIGNATURE_ID

NUMBER

 

Offers that were sent

REASON

VARCHAR2(255)

 

Reason for skipping the message

CUSTOM_PROPERTIES

 

 

 

DLT_TEMPLATE_ID

VARCHAR2(50 BYTE)

YES

DLT template ID is a parameter in the MT payloads for SMS messages that use India country codes. This field will not have a value when the SMS messages use other country codes.

Failed

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number (for example. 1234)

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the Campaign

RIID

NUMBER

NO

Unique ID assigned for this specific List record

CUSTOMER_ID

VARCHAR2(255)

 

A unique identifier of the recipient/record that matches a customer ID or unique identifier in your system of record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event.

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB.

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

LAUNCH_ID

NUMBER

NO

Numerical identifier of the launch instance

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

aggregator_id         

NUMBER

 

Numerical identifier of the aggregator. 2 for Velti

COUNTRY_CODE      

VARCHAR2(2)

 

Mobile country code

MOBILE_CODE               

VARCHAR2(50)

NO

Short or long mobile code

MOBILE_NUMBER             

VARCHAR2(255)

NO

Mobile phone number

MOBILE_CHANNEL        

VARCHAR2(50)

NO

The channel for the event, either SMS or PUSH

MOBILE_KEYWORD                  

VARCHAR2(50)

 

Mobile keyword, converted to upper or lowercase. ‘unknown’  if not in the system

OFFER_SIGNATURE_ID

NUMBER

 

Offers that were sent

REASON

VARCHAR2(255)

 

Reason the message failed

CUSTOM_PROPERTIES

 

 

 

DLT_TEMPLATE_ID

VARCHAR2(50 BYTE)

YES

DLT template ID is a parameter in the MT payloads for SMS messages that use India country codes. This field will not have a value when the SMS messages use other country codes.

Delivered

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number (for example. 1234)

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the Campaign

RIID

NUMBER

NO

Unique ID assigned for this specific List record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event.

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB.

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

LAUNCH_ID

NUMBER

NO

Numerical identifier of the launch instance

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

aggregator_id         

NUMBER

 

Numerical identifier of the aggregator. 2 for Velti

COUNTRY_CODE      

VARCHAR2(2)

 

Mobile country code

MOBILE_CODE               

VARCHAR2(50)

NO

Short or long mobile code

MOBILE_NUMBER             

VARCHAR2(255)

NO

Mobile phone number

MOBILE_CHANNEL        

VARCHAR2(50)

NO

The channel for the event, either SMS or PUSH

mobile_carrier              

VARCHAR2(250)

NO

Name of  the mobile carrier

sms_sent_uuid                

VARCHAR2(36)

NO

Internal ID of the event from the sms_sent event that is sent to the aggregator

delivered_flag                   

CHAR(1)

NO

Indicates whether the SMS was delivered. 1 if delivered, 0 if undelivered

aggregator_
message_id    

VARCHAR2(36)

 

Numerical identifier from the aggregator

aggregator_
message_subid

NUMBER

 

The sub-ID of the message assigned by the aggregator

aggregator_
status_code

NUMBER

 

Aggregator-specific error code

aggregator_
status_desc

VARCHAR2(50)

 

Aggregator-specific description of the error code

Opted In

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number (for example, 1234)

RIID

NUMBER

NO

Unique ID assigned for this specific List record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB

aggregator_id         

NUMBER

 

Numerical identifier of the aggregator. 2 for Velti

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the Campaign

LAUNCH_ID

NUMBER

NO

Numerical identifier of the launch instance

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

MOBILE_CODE               

VARCHAR2(50)

NO

Short or long mobile code

MOBILE_NUMBER             

VARCHAR2(255)

NO

Mobile phone number

MOBILE_KEYWORD

VARCHAR(50)    

 

Mobile keyword, converted to upper or lowercase. ‘unknown’  if not in the system

mobile_COUNTRY

VARCHAR2(2)

 

The 2 character ISO2 country code

mobile_carrier              

VARCHAR2(250)

NO

Name of  the mobile carrier

SOURCE

CHAR(100)

 

Application / module that initiates the event

Exported value is “Source Value” found in the Source Identification Values table.

REASON

VARCHAR2(500)

 

Method used to add the recipient to the List (does not include bulk loads).

  • DV:MERGE (Data Viewer edit / merge)

  • FR:MERGE (Update / insert via a Form as a result of a form submission)

  • FR: CONFIRMED (Confirmed Opt In email recipient)

  • FR: AWAIT_CONFRIM (Acquired email recipient who was previously opted in and subscribes again, but is un-confirmed)

  • PR:MERGE

  • WS:MERGE

CUSTOMER_ID

VARCHAR2(255)

 

A unique identifier of the recipient/record that matches a customer ID or unique identifier in your system of record

 

Opted Out

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number (for example, 1234)

RIID

NUMBER

NO

Unique ID assigned for this specific List record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB

aggregator_id         

NUMBER

 

Numerical identifier of the aggregator. 2 for Velti

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the Campaign

LAUNCH_ID

NUMBER

YES

Numerical identifier of the launch instance

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

MOBILE_CODE               

VARCHAR2(50)

NO

Short or long mobile code

MOBILE_NUMBER             

VARCHAR2(255)

NO

Mobile phone number

MOBILE_KEYWORD

VARCHAR(50)    

 

Mobile keyword, converted to upper or lowercase. ‘unknown’  if not in the system

mobile_COUNTRY

VARCHAR2(2)

 

The 2 character ISO2 country code

mobile_carrier              

VARCHAR2(250)

NO

Name of  the mobile carrier

SOURCE

CHAR(100)

 

Application / module that initiates the event

Exported value is “Source Value” found in the Source Identification Values table.

REASON

VARCHAR2(500)

 

Method used to add the recipient to the List (does not include bulk loads).

  • DV:MERGE (Data Viewer edit / merge)

  • FR:MERGE (Update / insert via a Form as a result of a form submission)

  • PR:MERGE (Program)

  • WS:MERGE

CUSTOMER_ID

VARCHAR2(255)

 

A unique identifier of the recipient/record that matches a customer ID or unique identifier in your system of record

MO Forward Sent

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number (for example, 1234)

RIID

NUMBER

NO

Unique ID assigned for this specific List record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB

aggregator_id         

NUMBER

 

Numerical identifier of the aggregator. 2 for Velti

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the Campaign

LAUNCH_ID

NUMBER

YES

Numerical identifier of the launch instance

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

MOBILE_CODE               

VARCHAR2(50)

NO

Short or long mobile code

MOBILE_NUMBER             

VARCHAR2(255)

NO

Mobile phone number

MOBILE_KEYWORD

VARCHAR(50)    

 

Mobile keyword, converted to upper or lowercase. ‘unknown’  if not in the system

COUNTRY_CODE

VARCHAR2(2)

 

Mobile country code

MO_MSG

VARCHAR2(255)

NO

The forwarded message

MO Forward Failed

Field Name

Type and Max Length

Null?

Description/Comments

event_uuid                

VARCHAR(36)

NO

Internal ID of the event

EVENT_TYPE_ID

NUMBER

NO

Numerical value that denotes the event type for the given event transaction

See Contact Event Type IDs for more information.

ACCOUNT_ID

NUMBER

NO

Account number (for example, 1234)

RIID

NUMBER

NO

Unique ID assigned for this specific List record

EVENT_CAPTURED_DT

TIMESTAMP

NO

Date and time the system was informed about the event

EVENT_STORED_DT

TIMESTAMP

NO

Date the event transaction was stored into the Event DB

aggregator_id         

NUMBER

 

Numerical identifier of the aggregator. 2 for Velti

LIST_ID

NUMBER

 

Numerical identifier for the List object used by the Campaign

LAUNCH_ID

NUMBER

YES

Numerical identifier of the launch instance

CAMPAIGN_ID

NUMBER

NO

Numerical identifier of the specific campaign

program_id            

NUMBER

 

Numerical identifier of the Program. -1 if unknown

MOBILE_CODE               

VARCHAR2(50)

NO

Short or long mobile code

MOBILE_NUMBER             

VARCHAR2(255)

NO

Mobile phone number

MOBILE_KEYWORD

VARCHAR(50)    

 

Mobile keyword, converted to upper or lowercase. ‘unknown’  if not in the system

COUNTRY_CODE

VARCHAR2(2)

 

Mobile country code

MO_MSG

VARCHAR2(255)

NO

The forwarded message

FAILURE_REASON

VARCHAR2(255)

YES

The reason for failure