How to access conversation history data in Botmaker for querying SQL or BI reports?
In this article, you will learn how to request read access to all conversation tables on the platform.
Estimated reading time: 06 minutes
Through Botmaker Support support@botmaker.com, you can request access to this information. You only need to provide a Google account email address ( Gmail or GSuite ) (example@gmail.com). This account will have access to view the tables currently available to you.
What is the platform on which the information will be available for queries?
You will be able to view it through Google's BigQuery platform.
What options do I have to access this information?
- BigQuery Console.
- BigQuery APIs.
- Activate it as DataSource as data input for a BI platform (DataStudio, PowerBi, etc).
What kind of information is displayed in the table?
The table is divided into three sections: session metrics, events, and messages.
- Sessions refer to the conversations between the user and the bot or live chat agent, from the beginning to the end.
- Events refer to actions in the session, such as logout, muting the bot, and assigning to the agent (operator).
- Messages refer to the exchange of information within the session, whether texts, multimedia files, surveys answered by the user, type of conversation (reason for which the conversation was initiated: inquiries, sales, support, etc.), and notifications received by the user. Metrics are always linked to a specific session.
- Variables rrefer to the relationship between variables and tags for each session.
All metrics are always linked to a specific session, the relationship between the tables is made by session_id
Below you will see a typical table with the type of information it shows.
| Session Metrics |
|---|
| Column name | Type | Example | Description |
|---|---|---|---|
| session_id | STRING | ENYZ7WZWJZ5XYQKFUOE7_2021-03-18T14:46:27.534Z | Session identifier |
| session_creation_time | TIMESTAMP | 2021-03-18 14:46:27.534 UTC | Session creation date |
| starting_cause | STRING | “WhatsAppTemplate”, “Organic”, etc | Reason for session login (Organic means that the user initiated the conversation). |
| starting_cause_info | RECORD | {“name”:“wa_template_123”} | Extra information for session login reason |
| channel_id | STRING | whatsapp-1559999999 | Channel identifier |
| channel_name | STRING | 1559999999 | Channel name or information |
| user_platform_contact_id | STRING | 5491133952476 | User ID in the channel (phone number on WhatsApp) |
| user_id_on_business | STRING | null | User identifier on the client (not mandatory) |
| user_creation_time | TIMESTAMP | 2021-02-22 20:26:38.082 UTC | Date of user/chat creation |
| user_email | STRING | test@test.com | User email address |
| user_first_name | STRING | Carlos | User first name |
| user_last_name | STRING | Carlos Gómez | User last name |
| user_picture_url | STRING | https://platform-lookaside.fbsbx.com/platform/profilepic/?psid=000000000000001&width=1024&ext=1626975350&hash=AeSW6AV-t-PJHZbIvgQ | User profile picture |
| user_whatsapp_curated_number | STRING | 5491100000000 | User WhatsApp number |
| Event Metrics |
|---|
| Column name | Type | Example | Description |
|---|---|---|---|
| session_id | STRING | ENYZ7WZWJZ5XYQKFUOE7_2021-03-18T14:46:27.534Z | Session ID |
| session_creation_time | TIMESTAMP | 2021-03-18 14:46:27.534 UTC | Session creation date |
| creation_time | TIMESTAMP | 2021-03-03 22:44:26.723 UTC | Event date |
| name | STRING | user-locked | Event type |
| info_name | STRING | operator_name | Field name |
| info_value | STRING | Juan | Field value |
| Message Metrics |
|---|
| Column name | Type | Example | Description |
|---|---|---|---|
| session_id | STRING | ENYZ7WZWJZ5XYQKFUOE7_2021-03-18T14:46:27.534Z | Session ID |
| session_creation_time | TIMESTAMP | 2021-03-18 14:46:27.534 UTC | Session creation date |
| id | STRING | O20520HK7CY7EY82YXHP | Message unique ID |
| creation_time | TIMESTAMP | 2021-03-19 20:10:04.447 UTC | Message date |
| msg_from | STRING | “user”, “operator”, “bot” | Message source |
| operator_id | STRING | TR9QLm7Kw4eslNYMcKRUQ8AFMAl2 | Operator ID |
| operator_name | STRING | Juan López | Operator name |
| operator_email | STRING | example@botmaker.io | Operator email |
| operator_role | STRING | ADMIN | Operator role |
| operator_alias | STRING | Jhon | Operator alias |
| message_type | STRING | Text | Message type (text, image, audio, etc.) |
| message | STRING | hello | Message body |
| images_urls | STRING | [“https://storage.googleapis.com/m-infra.appspot.com/public/res/GuilleBot/20210221-TR9QLm7Kw4eslNYMcKRUQ8AFMAl2-8N8QY-.png”] | URLs of images sent |
| audios_urls | STRING | null | Urls of audios sent |
| videos_urls | STRING | null | Urls of videos sent |
| files_urls | STRING | null | Urls of files sent |
| queue | STRING | Sales | Service queue (if referred to a particular one) |
| nps | NUMERIC | 5 | Response to the customer satisfaction survey |
| typification | STRING | Inquiry about product received | Reason for user contact |
| notification_name | STRING | Change in prices | Name of the notification received |
| notification_complete_name | STRING | New price list | Full name of the notification received (example: new price list as of June 2021) |
| Variable metrics |
|---|
| Column name | Type | Example | Description |
|---|---|---|---|
| session_id | STRING | ENYZ7WZWJZ5XYQKFUOE7_2021-03-18T14:46:27.534Z | Session Id |
| session_creation_time | TIMESTAMP | 2021-03-18 14:46:27.534 UTC | Session creation date |
| creation_time | TIMESTAMP | 2021-03-03 22:44:26.723 UTC | Event date |
| var_name | STRING | client_name | Variable name |
| var_value | STRING | John | Variable value |
| Event types |
|---|
| Event type | Description | Event structure |
|---|---|---|
| user-unlocked | User unassigned | {“name”:“time”,“value”:“2022-11-23T15:18:35.362Z”} |
| user-locked | User assigned to agent | {“name”:“time”,“value”:“2022-11-23T15:18:55.156Z”} |
| user-clicked-url | Click on a button URL | {“name”:“url”,“value”:“www.url.com”} |
| conversation-close | End of conversation | {“name”:“typification”,“value”:“PENDING”} |
| user-waiting-lock | User is waiting on queue | {“name”:“queue”,“value”:“test_agents”} |
| user-note | Note creation | {“name”:“value”,“value”:“🧑🏻 CUSTOMER: BotMakerTest\n📝 CASE: password change\n🪪 ID: 99999999\n🖥️ SUBSCRIPTION:”} |
| user-ban | Mark user as spam | {“name”:“queue”,“value”:“QueueName”} |
| change-bot | Bot change | {“name”:“reason”,“value”:“Run Flow Manager to assign a general intent [none]”} |
| bot-unmuted | Bot unmuted | {“name”:“queue”,“value”:“test_agents”} |
| bot-muted | Bot muted | {“name”:“queue”,“value”:“test_agents”} |
| queue-assigned | Assigned to queue | {“name”:“time”,“value”:“2022-11-23T15:18:53.919Z”} |
| archived | Archived | {“name”:“queue”,“value”:“test_agents”} |
Note : The usage of this information will have a cost per amount of gigabytes spent. For additional details, please contact your Account Manager or our Online Support.
Remember to visit our Help Center for further information.
Written by: Botmaker Team
Updated: 7/05/2023