email_sms_table
The Email/SMS table provides a comprehensive overview of email and SMS campaign metrics, including send times, click-throughs, and conversion data. Querying this table enables you to evaluate the effectiveness of your communication strategies, track customer engagement, and refine your marketing efforts for better performance.
Note
event_dateis a required field for queries on this table.
Dimensions
Dimensions are immutable properties that can be used for grouping data.
| Name | Type | Description | Source |
|---|---|---|---|
event_date | date | The date the email/SMS action occurred (e.g. sent, opened, received). Based on the time zone of the shop at the moment the email/SMS action occurred. | Klaviyo, Yotpo |
event_date.day | date | The day the email/SMS action occurred. Derived from event_date. | Klaviyo, Yotpo |
event_date.week | date | The Sunday of the week during which the email/SMS action occurred. Derived from event_date. | Klaviyo, Yotpo |
event_date.month | date | The month during which the email/SMS action occurred. Derived from event_date. | Klaviyo, Yotpo |
event_date.quarter | date | The first month of the quarter during which the email/SMS action occurred. Derived from event_date. | Klaviyo, Yotpo |
event_date.year | date | The year during which the email/SMS action occurred. Derived from event_date. | Klaviyo, Yotpo |
event_hour | string | The hour of the day the email/SMS action occurred, according to a 24-hour clock. Based on the time zone of the shop at the moment the email/SMS action occurred. Example values: 07, 16, 21 | Klaviyo, Yotpo |
channel | string | The platform used to send email/SMS. Example values: klaviyo, Smsbump | Klaviyo, Yotpo |
channel_type | string | The type of communication channel used. Example values: email, sms | Klaviyo, Yotpo |
report_type | string | The type of report generated. Example values: flow, campaign | Klaviyo, Yotpo |
campaign_id | string | The unique ID of the email/SMS campaign, assigned by the email/SMS platform. Example value: R8cpwN | Klaviyo, Yotpo |
campaign_name | string | The name of the email/SMS campaign. Example value: Father's Day Sale 50% Off | Klaviyo, Yotpo |
campaign_status | string | The current status of the campaign associated with the email/SMS. Example value: Sent, Cancelled | Klaviyo |
flow_id | string | The unique ID for the automation flow. Assigned by the email/SMS platform. Example value: 726935 | Klaviyo, Yotpo |
flow_name | string | The name of the automation flow. Example value: Abandoned cart reminder: 1-3 days | Klaviyo, Yotpo |
flow_status | string | The current status of the automation flow. Example values: draft, live | Klaviyo |
flow_action_id | string | The unique ID for a specific action within an automation flow. Assigned by the email/SMS platform. Example value: 49497165 | Klaviyo |
flow_action_name | string | The name of the specific action within an automation flow. Example values: SEND_EMAIL, SEND_SMS, AB_TEST, TIME_DELAY, WEBHOOK | Klaviyo |
flow_action_status | string | The current status of a specific action within an automation flow. Example values: draft, live, manual | Klaviyo |
message_id | string | The unique ID for the individual message sent as part of a campaign or flow. Assigned by the email/SMS platform. Example value: UKDRM6 | Klaviyo |
message_name | string | The name of the individual message sent. Example value: Welcome Series: Email 11 | Klaviyo |
Measures
Measures are numeric fields that can be aggregated and/or combined to calculate new metrics.
| Name | Type | Description | Source |
|---|---|---|---|
clicks | numeric | The total number of times recipients clicked links within the email/SMS. | Klaviyo |
conversion_value | numeric | The total revenue generated from purchases attributed to the campaign. | Klaviyo, Yotpo |
delivered | numeric | The total number of messages successfully delivered to the targeted recipient. | Yotpo |
opened | numeric | The total number of times the email was opened by recipients. | Klaviyo |
purchases | numeric | The total number of purchases made as a direct result of the campaign. | Klaviyo, Yotpo |
received | numeric | The total number of emails successfully received by the target audience. | Klaviyo |
sent | numeric | The total number of messages sent. Note: This field does not contain data for Klaviyo email. (It is relevant for Klaviyo SMS, and Yotpo Email and SMS.) | Klaviyo, Yotpo |
spend | numeric | The total amount spent on the campaign. | Yotpo |
subscribed_to_list | numeric | The total number of recipients subscribed to any list. Not associated with a campaign (campaign_id will be null). | Klaviyo, Yotpo |
unsubscribed | numeric | The total number of recipients who unsubscribed from any communication as a result of the campaign. | Klaviyo, Yotpo |
cumulative_opened | numeric | The running total of email opens over time. Note: All opens are counted, not unique opens. To count unique opens, use the opens_unique field in the Email/SMS settings table. | Klaviyo |
cumulative_received | numeric | The running total of messages received over time. | Klaviyo |