Solved

How to obtain Open Rate, Click Rate, Klaviyo Revenue through API

  • 2 June 2023
  • 3 replies
  • 912 views
I
Rank
Badge

My client has been downloading Klaviyo reports through the UI by hand. She wants to automate this by exporting the data to Google Sheets through the API. I am a dev new to Klaviyo who got was task. The metrics needed are Revenue, Open Rate, Click Rate. 

I have been at this for over 5 days. I got through the Get Started Steps. Then I had to select which API to use, etc. etc. 

I found a Help Center article How to Export Conversion Data. It seems to cover the subject that I need. However, it gives v1 APIs which are no longer current. 

Per this article, my first step was to export all of the campaigns. As the list of campaigns turned out to be multi-page, I had to take care of fetching all the pages. I got through that. I am sharing a Google Apps Script function I ended up with, in case it might help anyone else or be improved. 

I also fetched all the metrics available in the account through the /metrics endpoint. I also found a trick, not prominently displayed: "you can find the metric_id in your Klaviyo account by navigating to the page for a given metric. 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)." Maybe this datum would help other readers of "How to export conversion data" article; at least, I was baffled about this at first. 

Now I have the following metrics and their IDs:
Opened Email
Opened Push
Clicked Email
Clicked SMS
Received Email
Received Push
Placed Order

Where do I go from here? I need to end up with a Google Sheet that lists these metrics for each campaign. And then I need to be able to update this Google Sheet periodically. And do the same for the flows as well; it's only for simplicity that I am concentrating on campaigns now. 

Which API should I use to fetch this data? 

If I understand the docs correctly, the new Query Metric Aggregates endpoint would give me the aggregated data over a period. But I need the data aggregated based on a campaign or flow, not based on time. Can it do this? 

The description in "How to Export Conversion Data" seems to mean that I have to make an API request to API/metric endpoint for each metric for each campaign. If I have 7 metrics and 100 campaigns that would be 700 requests. This has to be minimized. 

Can I supply an array of campaigns in my API request for a metric and receive an array of metric data corresponding to the campaigns? If so, how?

Or vice versa, can I fetch 7 metrics related to one campaign or flow in one API call? 

In fetching metrics, can I specify the "measurement"? The "How to Export Conversion Data" article says that the API calls should have measurement='count' or measurement='unique' or measurement='value'. Does this work in the current version of API? I don't see "measurement" in query params. 

I can keep on trying to invent a wheel in navigating the docs, but if anyone can share a simple explanation, it would help me a lot, and probably help the future users as well. 

icon

Best answer by Brian Turcotte 16 June 2023, 18:26

View original

3 replies

Brian Turcotte
Rank
Userlevel 7
Badge +36

Hi @IrinaK!

This sounds like it could be accomplished via our Query Metrics Aggregates endpoint. If you haven’t already seen it, here is an updated guide to using the endpoint:

 

Since you already have the Metric ID’s, you’re almost there! To answer your concerns about using this endpoint - yes, the endpoint aggregates data over time, but you can group this data by flow, Campaign Name, and other supported attributes by using the by filter:

 

By using this endpoint, you can eliminate the need for making an API call for each metric, then for each campaign, and instead make one call for each metric that’s grouped by the different Campaigns. Here are some more Community threads that discuss using this endpoint:

 

I hope this helps, and thanks for using the Community!

- Brian

I
Rank
Badge

Hi Brian, 

Thank you very much for the answer and the helpful links. I have made some test calls to this API endpoint. Let me clarify:

  1. I can make a call for one metric at a time, so far as I understand. Correct? 
  2. I have to set the “interval”, and the longest possible one is “month,” correct? Thus, if I need the amount of “unique opens” in total rather than by a month, I should calculate it myself as part of processing the API output, right? 
  3. I see the list of Supported Attributes but I am not sure which one answers my purpose, and I did not find an explanation beyond the list itself. I need to export the data for all flows and all campaigns. If I use by: ['$message'], will this export the data for all messages, including all those in the flows and campaigns? If not, which by attribute(s) should I use? If I were to get data by the flows and campaigns, can I include a list of them (an array), and with which attribute? 
  4. I understand that the longest time period I can obtain the data on is one year. If I am getting the data for 2022 and then for 2023, I understand that an email sent on 29 Dec 2022 might end up in both outputs because some people would’ve opened it before December 31st and some others would’ve opened it after. Similarly, if I have gotten the API output on May 31st and then I update the output in June, I will get some of the results in May and some in June. Correct? If so, whenever I fetch the data for a new period, I have to program in de-duplication of this data as part of joining it to the earlier data, don’t I? Or is there a simpler route? 
  5. Once I obtained the data on one metric, let’s say “unique opens,” I would obtain the data on the next metric the same way, correct? I guess I cannot expect that the next API call would give me the exact same list of “dimensions” even if all the other query params are the same: in the time intervening between the two API calls someone could’ve clicked an email or triggered another event... But if the params are the same, most of dimensions in the outcome would be the same, right? Thus it seems that in order to combine the data from API calls for different metrics I have to use “dimension” as the key and then use it to set the values for different metrics. Is this correct, or is there a more optimum way to do this? 
     

    Thank you very much for your help!

Brian Turcotte
Rank
Userlevel 7
Badge +36

  

Hi @IrinaK!


My apologies for the delay here! To address each question:

  1. That’s right, you’ll only be able to include one metric per call. However, you will be able to receive the aggregates for each metric from all campaigns, and then group those aggregates by the individual campaigns. So instead of one call per metric per campaign, it’s reduced to one call per metric.
  2. That’s right, the longest interval is month, but that means that the aggregate data will just be grouped by month intervals. See this example in the developer guide:
  3. Yes, by: ['$message'] will only group the response by message ID, but will include all sent messages (see link above in #2)
  4. From our Help Center about the Klaviyo attribution model:

For both email and SMS, Klaviyo looks at message attribution per day. As a result, whatever day you send a message will be the day where attributed rates and conversions are counted. For example, if you email all recipients on a Friday and they open this email on both Friday and Saturday, those opens will be attributed to Friday.

  1. This would depend on how you are hoping to organize your data, but theoretically yes, since the dimension is the ID of the object you grouped your metrics by (in this case, Flow ID).

 

I hope this helps!

- Brian

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Links & Resources

Platforms

Partners

Useful Things

About

  • Terms and Privacy
    © 2024 Klaviyo All rights reserved. Klaviyo and the Klaviyo logo are trademarks or registered trademarks of Klaviyo, Inc. or its affiliates.