How to access conversation history data in Botmaker for querying SQL or BI reports?

Updated: 8/8/2024

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