el_conversations_raw
el_conversations_raw Columns#
JSON Keys for Conversations Object#
Sample JSON Objects for Conversation Types#
Conversation
{
"__v": 0,
"_id": "67411f89190533a35199",
"author": {
"type": "external",
"user_id": "67411f89990533a35198"
},
"chatbot_state": "running",
"created_at": "2026-11-23T00:19:21.523000",
"delivery_method": "email",
"email_inbox": {
"client_email": "example@element451.io",
"user_email": "example@student.com"
},
"etl_run_id": "b1895342-3a47-822c-35683e8e497a",
"etl_url": "https://dagster.cloud/prod/runs/b1895342-3a47-822c-35683e8e497a",
"event_id": "a9305ecf-4f6c-45ac-9f0d-8065d4aee954",
"external_participant": {
"anonymous_data": {
"email": "example@student.com",
"first_name": "Student",
"last_name": "Name"
},
"anonymous_guid": "67411f8805e3dfb0a5e36",
"is_anonymous": true,
"last_message_at": "2024-11-23T11:23:48.721000",
"last_visited": "2024-11-23T11:23:48.721000"
},
"favs": [],
"guid": "instance.conversations_v2.1807956",
"has_attachments": false,
"inbound_channel_id": "67411f88ce3dfb0a5e38",
"inbox_setting_id": "8c4c1ad59513c861dacc41dc2aa",
"is_private": false,
"last_activity": "2024-11-23T11:23:48.721000",
"latest_part": {
"_id": "6741bb44990533a5a5bf",
"author": {
"anonymous_guid": "67411f885e3dfb0a5e36",
"type": "external",
"user_id": null
},
"content": {
"message": "Submitted on Sat, 11/23/2024 - 06:23. Name: James, Organization: antiztvcgt, Program(s) of Interest: Yellow Belt, Green Belt, Black Belt, Other",
"moderation_flags": []
},
"conversation_guid": "instance.conversations_v2.1807956",
"created_at": "2024-11-23T11:23:48.721000",
"event_id": "154c092e-81ae-4602-a757-b877fcf462e4",
"guid": "instance.conversation_parts_v2.5533807",
"type": "comment",
"updated_at": "2024-11-23T11:23:48.721000",
"visibility": "external"
},
"moderation_flags": [],
"origin_event": {
"client": "rit",
"context": {
"inbound_channel_id": "67411f9305e3dfb0a5e38",
"inbox_setting_id": "8c4c1ad591083c861dacc41dc2aa"
},
"message": {
"attachments": [],
"body": "Submitted on Fri, 11/22/2026 - 19:18 Thank you for your interest!",
"cc": [],
"from": "example@gmail.com",
"subject": "Hello!",
"to": [
"example@element451.io"
]
},
"provider": "sendgrid",
"type": "email",
"user": null
},
"participants": [],
"source": "email",
"status": "open",
"subdom": "instance",
"tags": [],
"title": "Request Information Submission",
"updated_at": "2024-11-23T11:23:48.740000",
"visibility_groups": []
}Partial
{
"__v": 0,
"_id": "6374e1e1031eb798e4d2",
"author": {
"type": "external",
"user_id": "6241f9da0c03c92a7043"
},
"content": {
"description": "Thank you for your message.",
"file_id": "6b0ad381-66d1-b69e-7482271be8f8"
},
"conversation_guid": "instance.conversations_v2.121027",
"created_at": "2022-11-16T13:13:05.009000",
"event_id": "8f823d34-dbfe-4a1e-aa41-44736a5417bb",
"guid": "instance.conversation_parts_v2.370680",
"subdom": "instance",
"type": "attachment",
"updated_at": "2022-11-16T13:13:05.009000",
"visibility": "external"
}el_conversations_raw Columns
7 rows
| Column | SQL Data Type | Description |
|---|---|---|
| etl_loaded_at | TIMESTAMP_NTZ | Document loaded to Snowflake timestamp |
| created_at | TIMESTAMP_NTZ | Document created timestamp |
| updated_at | TIMESTAMP_NTZ | Document update timestamp |
| subdom | VARCHAR | Partner school instance identifier |
| type | VARCHAR | "conversation", "partial", "setting". Where conversation is the parent to partials. Partials contain the content of outbound or inbound messages. Settings contain conversation rules and configuration. |
| _id | VARCHAR | Unique conversation identifier |
| src | VARIANT | JSON object of conversation properties. |
JSON Keys for Conversations Object
34 rows
| Description | Data Type | Key |
|---|---|---|
| Unique identifier for the author of the conversation partilal. | STRING | author.user_id |
| Email captured during email_capture_response partial type. | STRING | content.email |
| Client instance identifier. | STRING | subdom |
| Timestamp associated with the Asset. | STRING (TIMESTAMP) | metadata.data[].tstamp |
| Last name captured during name_capture_response partial type. | STRING | content.last_name |
| Asset GUID referenced in the partial, such as event GUID or application GUID. Indicates that the asset was mentioned by bot. | STRING | metadata.data[].guid |
| Object to contain metadata associated with a conversation. Metadata includes assets (events, applications, appointments) referenced by the bot in conversation partials. | OBJECT | metadata |
| Status of the partial: "sent", "failed | STRING | part_status.status |
| Temporary identifier for the conversation partial. | STRING | temp_guid |
| Category for the metadata: application_start_tool, application_status_tool, event_availability_tool, event_signup_tool, link, schedule_appointment_availability_tool, schedule_appointment_specific_slot_tool, schedule_appointment_tool | STRING | metadata.type |
| The 'visibility' key in the SRC variant column of the ELEMENT_CONVERSATIONS table likely represents the level of visibility or accessibility of a conversation, such as whether it is publicly visible, private, or restricted to specific users or groups. | STRING | visibility |
| Description for attachment partial types. | STRING | content.description |
| "external", "system", "bot", "internal". Entity type that authored document. | STRING | author.type |
| Unique identifier for each document, serving as a primary key for each conversation, partial or setting. | STRING | guid |
| Metadata items associated with a conversation. | ARRAY | metadata.data |
| Object ID (Element ID) foreign key of the contact who is the intended recipient of the partial. | STRING | content.target |
| First name captured during name_capture_response partial type. | STRING | content.first_name |
| Message content of the partial. | STRING | content.message |
| Unique identifier for each document, serving as a primary key for each type. | STRING | _id |
| AI evaluated sentiment score for the partial: 1, 2, 3. | STRING | sentiment_evaluation |
| Unique identifier for an anonymous user, such as a prospective student or lead, who has interacted with the platform. | STRING | author.anonymous_guid |
| Unique identifier for a file shared in an attachment partial type. | STRING | content.file_id |
| Timestamp when the document was created. | STRING (TIMESTAMP) | created_at |
| Object containing the status of the partial. | OBJECT | part_status |
| "prompt_main","prompt_follow_up", "phone_call_completed", "phone_call_started","phone_call_scheduled", or "prompt_thank_you". AI Agent prompt state for the partial. | STRING | content.state |
| Category for the partial or setting. Values for type=partial: add_assistant_to_conversation, add_participant, assign, attachment, chatbot_toggle, close, comment, conversation_associated, conversation_flagged, email_capture_prompt, email_capture_response, info_capture_prompt, join, leave, moderation_blocked, name_capture_response, note, open, phone_call, phone_call_transferred, privacy_changed, remove_participant, rename, scheduled_comment, scheduled_phone_call, student_assistant_changed, transcript_chunk, transcript_summary, unassign. Values for type=setting: conversation_article, conversation_article_folder, conversation_chatbot_persona, conversation_icebreaker, conversation_rule, conversation_settings, conversation_starter, conversation_student_assistant, conversation_student_assistants_group, conversation_view | STRING | type |
| Object storing partial content. | OBJECT | content |
| Foreign key for the parent conversation of each partial. | STRING | conversation_guid |
| The user or staff member who initiated or created the conversation. | OBJECT | author |
| Time associated with the Asset. | STRING (TIMESTAMP) | metadata.data[].time |
| Unique identifier for analytic event associated with the document. | STRING | event_id |
| Asset name referenced in the partial, such as event name or application name. Indicates that the asset was mentioned by bot | STRING | metadata.data[].name |
| Identifiers the medium source for conversations: email, phone_call, sms, web, whatsapp. Identifies the URL origin for the partial. | STRING | source |
| Timestamp when the conversation was last updated. | STRING (TIMESTAMP) | updated_at |
Was this page helpful?