communication_history
Content: communications history
History of Omnichannel Communications
Overview
In this Data Type, you can define all the data about the History of Omnichannel Communications.
This table is structured specifically to support both simple and flat CSV data as well as rich and nested JSONL data. While we recommend to use the rich and nested format, we understand that the simple and flat CSV format might be more suitable for your projects capacities, especially for a first installment. We provide below example for both cases.
The field table below is quite long and complicated to understand, so we provide here first a break-down of the key ideas and groups of fields (we didn’t group fields in order to allow CSV data upload which would not support such nesting, even if it would have helped the understandability of the table).
Event fields
The events fields are the following in the table below:
event_id, event_type, event_category, datetime, sending_id, sending_sys_cd, channel_id, channel_name, run_id, communication_id, message_id, message_name, thread_id, thread_type, thread_name, campaign_id, campaign_name, external_id
They define what the event and the communication is about.
They are structured around the following key groups:
group | description | related fields |
---|---|---|
event n:1 relation with sending and every other group | describe what the communication history event is about (type of event (sent, open click, …), time of the event, …) | event_id, event_type, event_category, datetime |
sending | related to when a communication was sent to a particular receiver (this is about when your system actually sent a message out) | sending_id, sending_sys_cd, run_id, external_id |
channel | describe what is the channel of the sending | channel_id, channel_name |
communication 1:n relation with sending | describe a message communicated with (a) specific receiver(s), if you send a message several times (possibly in different channels) to the same receiver(s) the communication id will be the same while the sending id will not | communication_id |
message | describe a message which could be send to several people (communications) and possibly several times (sendings) | message_id, message_name |
thread | communication branch starting from a prior message (if not unique to (a) specific receiver(s)) or communication, then the branch will be ordered by event datetime of ‘sent’ event_type | thread_id, thread_type, thread_name |
campaign | describe a campaign which is a group of 1 or several messages | campaign_id, campaign_name |
Receivers fields
The receiver fields are the following in the table below:
receiver_id, receiver_type, receiver_title, receiver_firstname, receiver_lastname, receiver_email, receiver_visitor_ids, receivers, receiver_attribute_1_name, receiver_attribute_1_value, receiver_attribute_2_name, receiver_attribute_2_value, receiver_attribute_3_name, receiver_attribute_3_value, receiver_attribute_4_name, receiver_attribute_4_value, receiver_attribute_5_name, receiver_attribute_5_value
The receiver field describe who receives the communication.
Typically, you only need to specify values for the receiver fields in case of events others than ‘sent’, as all other events will refer to a pre-indicated sending_id (however, you can provide it with every event if you want)
Depending on if you use a flat (CSV) or nested (JSONL) structure to load the data and if you refer to the receiver by known ids (from the doc_user) or not (the information is only in the history table) you can use the adequate field as per the following use-case table:
| Flat (CSV) | Rich (JSONL) |
---|---|---|
Known (refer by id) | receiver_id, receiver_type | |
Unknown (full description) | receiver_type + (if applicable) + (optionally) | receiver_type + (if applicable) + (optionaly) |
This means that you can always provider the receiver_id and receiver type, as well as the receiver_email and receiver_visitor_ids if applicable. In addition, to provide more information about the receiver(s) you can either use the dedicated flat fields (CSV) or the receivers nested object (JSONL).
Senders fields
The receiver fields are the following in the table below:
senders, sender_id, sender_type, sender_title, sender_firstname, sender_lastname, sender_email, sender_attribute_1_name, sender_attribute_1_value, sender_attribute_2_name, sender_attribute_2_value, sender_attribute_3_name, sender_attribute_3_value, sender_attribute_4_name, sender_attribute_4_value, sender_attribute_5_name, sender_attribute_5_value
The receiver field describe who is sending the communication (sending in ER diagram above)
Typically, you only need to specify values for the senders fields in case of events others than ‘sent’, as all other events will refer to a pre-indicated sending_id (however, you can provide it with every event if you want)
The structure is very similar to the receiver fields (please refer to the description and the table) with the difference that there is also a sender_title, sender_firstname and sender_lastname you can indicate as part of the dedicated flat fields if you are using CSV (otherwise, prefer using the nested structure of the “senders” field)
Contents fields
The contents fields are the following in the table below:
contents, content_id, content_id_field, content_language, content_title, content_short_description, content_description, content_attribute_1_name, content_attribute_1_value, content_attribute_2_name, content_attribute_2_value, content_attribute_3_name, content_attribute_3_value, content_attribute_4_name, content_attribute_4_value, content_attribute_5_name, content_attribute_5_value
The receiver field describe what was the content of the communication
You can use the content_id, content_language (and if needed the content_id_field) if you want to refer to existing content you already exported in doc_content (however, we recommend to also indicate at least the content_title for clarity and possible changes of the values in doc_content).
If you can’t provide a content id (connected to doc_content) but can provide more information about the content, you can either use the nested “contents” fields (if you use a JSONL format) or provide content information in the other flat fields (content_title, content_short_description, …)
Attribute fields
The attribute fields are the following in the table below:
string_attributes, localized_string_attributes, numeric_attributes, localized_numeric_attributes, datetime_attributes, localized_datetime_attributes, attribute_1_name, attribute_1_value, attribute_2_name, attribute_2_value, attribute_3_name, attribute_3_value, attribute_4_name, attribute_4_value, attribute_5_name, attribute_5_value
You can provide additional information about your even in a flexible using either the typed and nested fields (string_attributes, localized_string_attributes, numeric_attributes, localized_numeric_attributes, datetime_attributes, localized_datetime_attributes, if you use a JSONL format) or the flatten string fields (attribute[1-n]_name and attribute[1-n]_value if you use a CSV format).
Important considerations about the tables naming structure
In case you load your data through the Boxalino Data API, you don’t need to worry about this part, but you might want to load your data by yourself in your (or Boxalino’s) BigQuery project.
In such a case, we highly recommend to consider the following table name postfix structure, as per the DDL link below indicating this table format: {{ YOUR_PROJECT }}.{{ YOUR_DATASET }}.communication_history_{{ YYYYMMDD }}_{{ SENDING_SYS_CD }}
Create one table per day and put the date as YYYYMMDD format as the postfix of the table
Append '_' and the value (unique and immutable) of the system which generates these data aftwerise
Why is it a good idea?
You can easily access or delete the data of one day without using any where close (“drop table communication_history_20230101_YOUR_SYSTEM_CODE”) and without risking deleting data of the same day from other systems
It is compatible with the * pattern for all source systems together
(“select * from communication_history_202301*” ==> returns all data for all systems for January 2023)
Is there any draw-backs?
Yes, as you can’t delete all data in one query, you have to drop each table date individually for your system
Example
In this example, we provide an example both for the simple and flat case in CSV and the rich and nested case in JSON.
Properties
Field name | Type | Mode | Description |
---|---|---|---|
event_id | STRING | NULLABLE | a unique communication event id (PK), optional (mainly diagnostic / debugging / maintenance) |
event_type | STRING | REQUIRED | the type of event, is one of the following values: 'sent', 'bounce', 'open', 'click', ... |
event_category | STRING | REQUIRED | the category of event, is one of the following values: 'inbound', 'outbound', ... |
datetime | TIMESTAMP | NULLABLE | the timestamp of the event |
sending_id | STRING | REQUIRED | the id of the sending of the communication (unique per sending and per communication, but can have several events, like sent, open, click, the sending id must be generated by the channel as a unique identifier of each sent event... |
sending_sys_cd | STRING | REQUIRED | the system code which sent the message (e.g.: 'NEWSLETTER', 'E-SHOP', ...) |
channel_id | STRING | NULLABLE | the id of the communication channel (optional) |
channel_name | STRING | REQUIRED | the channel (name) of the communication: 'email', ... |
run_id | STRING | NULLABLE | the id of the batch execution (can cover several sending ids) (optional) |
communication_id | STRING | REQUIRED | a unique communication id should be unique per message sent to a specific person and should match the communication_id of the communication_planning table |
message_id | STRING | REQUIRED | the id of the communication (unique per communication, but could be applied to many receipients, ...) |
message_name | STRING | NULLABLE | the name of the communication |
thread_id | STRING | NULLABLE | the id of the communication thread (should refer to a prior communication_id or message_id which is the root of the thread) |
thread_type | STRING | NULLABLE | the type of the communication thread (typically 'message' of 'communication', by default is is considered as a 'communication' thread) |
thread_name | STRING | NULLABLE | the name of the communication thread |
campaign_id | STRING | NULLABLE | the id of the campaign or communication group |
campaign_name | STRING | NULLABLE | the name of the campaign or communication group |
external_id | STRING | NULLABLE | an external id if provided by a separated system (for example, the request_id if provided by boxalino narrative response) |
receiver_id | STRING | NULLABLE | the persona_id as referenced in other tables |
receiver_type | STRING | NULLABLE | the type of persona (user, guest, ...) |
receiver_title | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
receiver_firstname | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
receiver_lastname | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
receiver_email | STRING | NULLABLE | the email address (can be null) |
receiver_visitor_ids | STRING | REPEATED | the visitor cookie ids from Boxalino of Google Analygtics (can be empty) |
receivers | REPEATED | typically not use in case the receive is only 1 persona defined by its persona_id, but available for other cases | |
receiver_attribute_1_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_1_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_2_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_2_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_3_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_3_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_4_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_4_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_5_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
receiver_attribute_5_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure receivers |
senders | REPEATED | information about the communication senders | |
sender_id | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_type | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_title | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_firstname | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_lastname | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_email | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_1_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_1_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_2_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_2_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_3_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_3_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_4_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_4_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_5_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
sender_attribute_5_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure senders |
subject | STRING | NULLABLE | the subject line of the communication |
contents | REPEATED | information about the content of the communication, typically only provided for the 'sent' event, as the other events should refer to the same message_id and persona_id / email so no need to indicate the content every time, only for sent | |
content_id | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_id_field | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_language | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_title | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_short_description | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_description | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_1_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_1_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_2_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_2_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_3_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_3_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_4_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_4_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_5_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
content_attribute_5_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure contents |
string_attributes | RECORD | REPEATED | additional string (not localized) attributes of the content |
localized_string_attributes | REPEATED | additional localized string attributes of the content | |
numeric_attributes | REPEATED | additional numeric (not localized) attributes of the content | |
localized_numeric_attributes | REPEATED | additional localized numeric attributes of the content | |
datetime_attributes | REPEATED | additional datetime (not localized) attributes of the content | |
localized_datetime_attributes | REPEATED | additional localized datetime attributes of the content | |
attribute_1_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_1_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_2_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_2_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_3_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_3_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_4_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_4_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_5_name | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
attribute_5_value | STRING | NULLABLE | use only if providing data in flat format (like CSV) if you skip the nested structure attributes |
creation_tm | DATETIME | REQUIRED | technical field |
client_id | INTEGER | REQUIRED | technical field |
src_sys_id | INTEGER | REQUIRED | technical field |
Resources
BigQuery JSON Schema
https://github.com/boxalino/data-integration-doc-schema/blob/master/doc/communication_history.json
BigQuery DDL
https://github.com/boxalino/data-integration-doc-schema/blob/master/ddl/communication_history.sql