(New) Use the Query Metric Aggregates endpoint
Learn how to query and aggregate event data.
The Query Metric Aggregates endpoint allows for the querying and aggregation of event data, while grouping by related objects such as flows, campaigns, lists, etc. With this endpoint, you can dive deep into the performance of your Klaviyo account. Some examples include:
- Pull aggregate level data on any metric you store in Klaviyo.
- Pull email engagement or revenue impact per flow or campaign.
- Get list growth data broken down by month, per list.
- Count of any custom metric broken down by time period.
- Recreate campaign or flow reports you see in Klaviyo.
About Metric Aggregates and Klaviyo reporting
When exporting data via the Query Metric Aggregates endpoint, it is important to note that the dates you filter and group by are based on the time the event occurred, not the send date of the campaign or flow. For this reason, comparing your API data with send performance in-app may result in slightly different results. In-app performance is calculated based on send date.
For example, if you are querying for conversions using this endpoint and have the timeframe set between
2022-12-01and2022-12-31, an email sent on2022-11-28may be included in the results if it can be attributed to a conversion.
In Klaviyo, campaign and flow reports generally fall into one of two buckets around how they treat date ranges and metrics:
| Campaign or flow time range | Metric time range | |
|---|---|---|
| What it does | The date range represents when the campaign or flow message was sent. Opens, clicks, revenue, orders, etc. will be associated with the message, no matter when they happen. | The date range represents when the event (e.g., email opened) happened, not when the campaign or flow email was sent. |
| Example description | If the date range was Jan 1st - Jan 31st, a campaign was sent on Jan 30th, and there were clicks that happened in February, those clicks would still be counted in the report. | If the date range was Jan 1st - Jan 31st and there were clicks that happened in February, they would not be included in the report. |
| How to build the report | Campaign performance report, Flows performance report | Single metric deep dive report |
The Query Metric Aggregates endpoint is built to help with metric time range-style reports, although you can also use it to glean valuable insights around the performance of your campaigns and flows. This endpoint helps you understand more about events happening in your Klaviyo account and how they relate to other objects (flows, campaigns, messages, etc). With that perspective, and the examples below as a guide, you will unlock new insights about your Klaviyo performance while also being able to recreate existing reports outside of Klaviyo.
Because this endpoint allows for large scale event aggregation and grouping, it’s helpful to think of it as a way to execute SQL-style queries for data in Klaviyo.
For example, if you wanted to sum the revenue (using the Placed Order metric, for the metric-based report) by flow in your Klaviyo account for the last year using SQL, you would write something like this:
SELECT SUM(revenue)
FROM klaviyo_event_data
WHERE
metric="placed order"
AND timestamp>="2022-01-01"
AND timestamp\<="2023-01-01"
AND $flow!=null
GROUP BY $flow
The metric aggregates endpoint matches this pattern, so the query you’d write for the API call would look like this:
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"sum_value"
],
"by": [
"$attributed_flow"
],
"filter": [
"greater-or-equal(datetime,2022-01-01T00:00:00)",
"less-than(datetime,2023-01-01T00:00:00)",
"not(equals($attributed_flow,\"\"))"
],
"metric_id": "abc123",
"interval": "month",
"timezone": "US/Eastern"
}
}
}
Key concepts
Two specific concepts you should be aware of when working with the Metric Aggregates endpoint are:
- Grouping
Similar to aGROUP BYclause in SQL, this is a way to group events together by a particular attribute prior to aggregation. This is performed in thebysection of the metric aggregates payload. - Filtering
Similar to aWHEREclause in SQL, thefiltersection of the metric aggregates payload can be used to filter the set of events used to perform the aggregation by when they occurred, or by the value of supported aggregate attributes, such as"equals(URL,\"https://klaviyo.com/\")".
Limitations
This endpoint has the following limitations:
- Maximum date range of 1 year.
- Only 1 filter can be used per query in addition to date range.
- Consult our list of supported metrics and attributes for grouping and filtering limitations. Fields with a “$” prefix like “$message” are reserved fields generated by Klaviyo, and you will need to include the “$” when using such attributes.
Examples
Flow revenue performance reporting
To accomplish this, we’ll need to know what the metric_id is for the Placed Order event, how to filter by month, and how to group by flow. To get the Placed Order metric_id, use the Get Metric endpoint. Once returned, we’ll search for Placed Order and see that its metric_id is V9Ur7X, which we’ll add as the metric_id value in our metric aggregates payload.
Alternatively, you can find the corresponding metric_id in your Klaviyo account by navigating to the page for a given metric (see our article Getting started with metrics for more info). Once on the page for a given metric, you'll find the metric_id in the URL (e.g.,https://www.klaviyo.com/metric/METRIC_ID/metricname).
Aside from the metric_id, the other main sections of the metric aggregates payload are:
- Measurements
sum_value,count,unique. - By
The reserved fields listed in the important information section. - Filter
Must include date range that is no more than 1 year, can also include standard filtering on fields (equal,less-than,greater-than). - Interval
hour,day,week,month. - Timezone
The timezone used when processing the query.
So for our ask of getting flow revenue by month, we’d use the following payload:
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"sum_value"
],
"by": [
"$attributed_flow"
],
"filter": [
"greater-or-equal(datetime,2022-01-01T00:00:00)",
"less-than(datetime,2023-01-01T00:00:00)",
"not(equals($attributed_flow,\"\"))"
],
"metric_id": "PhqdJM",
"interval": "month",
"timezone": "US/Eastern"
}
}
}
Which would return a response with 3 important keys:
- Dates
The buckets of time you’re grouping your results by. - Dimension
The ID of the object you grouped your metrics by (in this case,Flow ID). - Measurements
The section with your aggregate metrics, grouped by the date ranges outlined in #1. In this case, it is the sum of all attributed order event revenue per month.
"attributes": {
"dates": [
"2021-12-01T05:00:00+00:00",
"2022-01-01T05:00:00+00:00",
"2022-02-01T05:00:00+00:00",
"2022-03-01T05:00:00+00:00",
"2022-04-01T04:00:00+00:00",
"2022-05-01T04:00:00+00:00",
"2022-06-01T04:00:00+00:00",
"2022-07-01T04:00:00+00:00",
"2022-08-01T04:00:00+00:00",
"2022-09-01T04:00:00+00:00",
"2022-10-01T04:00:00+00:00",
"2022-11-01T04:00:00+00:00",
"2022-12-01T05:00:00+00:00"
],
"data": [
{
"dimensions": [
"R9VbBr"
],
"measurements": {
"sum_value": [
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
17829.0,
21619.0,
126630.0,
505086.0
]
}
}
]
}
Daily Started Checkout event reporting
For this use case, the only new thing we’ll need to add to the request is the timezone of the account. With the metric_id for Started Checkout (“PPN222” in this example), the time window to run the request on, and the aggregation window, our payload would look like this:
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"count"
],
"filter": [
"greater-or-equal(datetime,2023-03-10T05:00:00)",
"less-than(datetime,2023-03-11T05:00:00)"
],
"metric_id": "PPN222",
"interval": "day",
"timezone": "US/Eastern"
}
}
}
This would return a payload with the attributes object below. Where the dates value is the day we’re counting the Started Checkout events, and the count value is the number of Started Checkout events for that day.
"attributes": {
"dates": [
"2023-03-10T05:00:00+00:00"
],
"data": [
{
"dimensions": [],
"measurements": {
"count": [
1679.0
]
}
}
]
}
Unique opens (unique_opens) for all sent messages
When exporting email metric data, you can calculate unique opens for all sent emails. To achieve this, run the cURL command shown below, filling in the items in brackets with your own information. The results will be grouped by message ID (using "by": ["$message"] as an attribute).
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"unique"
],
"filter": [
"greater-or-equal(datetime,2022-01-01)",
"less-than(datetime,2022-12-31)"
],
"by": ["$message"],
"interval": "month",
"timezone": "US/Eastern",
"metric_id": "QhyxVR"
}
}
}
This will return the total opens, broken down by month, for each message sent from Klaviyo in a payload similar to the one below. The dates break down which month the metrics are attributed to. There is an object for each message, with the opened emails associated with it. The dimension shows the message ID, like it has for other objects we’ve grouped by.
"attributes": {
"dates": [
"2021-12-01T05:00:00+00:00",
"2022-01-01T05:00:00+00:00",
"2022-02-01T05:00:00+00:00",
"2022-03-01T05:00:00+00:00",
"2022-04-01T04:00:00+00:00",
"2022-05-01T04:00:00+00:00",
"2022-06-01T04:00:00+00:00",
"2022-07-01T04:00:00+00:00",
"2022-08-01T04:00:00+00:00",
"2022-09-01T04:00:00+00:00",
"2022-10-01T04:00:00+00:00",
"2022-11-01T04:00:00+00:00",
"2022-12-01T05:00:00+00:00"
],
"data": [
{
"dimensions": [
"01GAN9XBEZ4F13FZFKPPCJ1WNW"
],
"measurements": {
"unique": [
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
34106.0,
0.0,
0.0,
0.0,
0.0
]
}
}, {more message objects}
]
}
Unique opens (unique_opens) for a specific message
You can also measure unique opens for a specific message ID if you don’t want to return all messages.
The request below will return the unique opens for a specific message ID (using "equals($message,\"MESSAGE_ID\")" as a filter).
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"unique"
],
"filter": [
"greater-or-equal(datetime,2022-01-01)",
"less-than(datetime,2022-12-31)",
"equals($message,\"01GAQWA31SZKABEZJNXDBGW2F1\")"
],
"interval": "month",
"timezone": "US/Eastern",
"metric_id": "QhyxVR"
}
}
}
This will return a response showing the opens for that specific message, broken down by month:
"attributes": {
"dates": [
"2021-12-01T05:00:00+00:00",
"2022-01-01T05:00:00+00:00",
"2022-02-01T05:00:00+00:00",
"2022-03-01T05:00:00+00:00",
"2022-04-01T04:00:00+00:00",
"2022-05-01T04:00:00+00:00",
"2022-06-01T04:00:00+00:00",
"2022-07-01T04:00:00+00:00",
"2022-08-01T04:00:00+00:00",
"2022-09-01T04:00:00+00:00",
"2022-10-01T04:00:00+00:00",
"2022-11-01T04:00:00+00:00",
"2022-12-01T05:00:00+00:00"
],
"data": [
{
"dimensions": [],
"measurements": {
"unique": [
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
0.0,
46261.0,
0.0,
0.0,
0.0,
0.0
]
}
}
]
}
Using the same approach as the examples above, you can measure non-unique opens, i.e. the number of total opens, which can include multiple opens per email address.
Total opens can be exported for all sent messages, or for a specific message ID. In the body of the API request, change the value of measurements from unique to count.
Total delivered or received (total_received)
To get the total number of emails received, the same approach referenced above can be applied by adding the Received Email metric ID to the metric_id field of the request body.
Open rate (open_rate)
To calculate open rate, take your total opened (either unique or non-unique) emails and divide that by the number of total delivered (received).
Unique clicks (unique_clicks)
When calculating who clicks into your emails, you can measure for unique clicks (the number of clicks, one per email address). In the metric_id field, use your Clicked Email metric ID. Then, use unique in the measurements field.
Total clicks (total_clicks)
You can also choose to measure for non-unique clicks (the number of total clicks, which can include multiple per email address). To achieve this, change the value in the measurements field to count.
Click rate (click_rate)
To calculate click rate, take the total clicks (either unique or non-unique) and divide that by the total delivered.
Conversions
To calculate conversions across all messages, use your desired conversion metric (e.g., Placed Order), as shown in the command below. The results will be grouped by Campaign ID.
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"count"
],
"filter": [
"greater-or-equal(datetime,YYYY-MM-DDTHH:MM:SS),less-than(datetime,YYYY-MM-DDTHH:MM:SS)"
],
"by": ["$attributed_message"],
"interval": "INTERVAL (hour,day,week,month)",
"timezone": "TIMEZONE",
"metric_id": "PLACED_ORDER_METRIC_ID"
}
}
}
To calculate conversions for a specific Campaign ID, filter by the $attributed_message value in the body of your request, as shown below.
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"count"
],
"filter": [
"greater-or-equal(datetime,YYYY-MM-DDTHH:MM:SS),less-than(datetime,YYYY-MM-DDTHH:MM:SS)",
"equals($attributed_message,\"Campaign_ID\")"
],
"interval": "INTERVAL (hour,day,week,month)",
"timezone": "TIMEZONE",
"metric_id": "PLACED_ORDER_METRIC_ID"
}
}
}
Revenue
To calculate total revenue across all messages, use your desired measurement value (e.g., Placed Order), as shown in the command below. The results will be grouped by Campaign ID. Remove the line containing "by": ["$attributed_message"] for unsorted results.
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"sum_value"
],
"filter": [
"greater-or-equal(datetime,YYYY-MM-DDTHH:MM:SS),less-than(datetime,YYYY-MM-DDTHH:MM:SS)"
],
"by": ["$attributed_message"],
"interval": "INTERVAL (hour,day,week,month)",
"timezone": "TIMEZONE",
"metric_id": "PLACED_ORDER_METRIC_ID"
}
}
}
To calculate revenue for a specific Campaign ID, use your desired measurement value (e.g., Placed Order), as shown in the example below.
{
"data": {
"type": "metric-aggregate",
"attributes": {
"measurements": [
"sum_value"
],
"filter": [
"greater-or-equal(datetime,YYYY-MM-DDTHH:MM:SS),less-than(datetime,YYYY-MM-DDTHH:MM:SS)",
"equals($attributed_message,\"Campaign_ID\")"
],
"interval": "INTERVAL (hour,day,week,month)",
"timezone": "TIMEZONE",
"metric_id": "PLACED_ORDER_METRIC_ID"
}
}
}
Updated over 1 year ago