Email Analytics with Klaviyo’s API

Table of Contents

Klaviyo Email Marketing

Email marketing is an essential marketing channel for most businesses. It’s a great way to keep existing customers engaged and acquire new ones. Klaviyo is one of the most commonly used email marketing platforms. Quite a few of my clients use it and therefore, I need API access to the data for analytics and dashboards. Klaviyo’s API is relatively powerful but the data is paginated and requires numerous calls to query a single metric. This is only a minor drawback of the platform, otherwise, it is one of the most robust email marketing services. 

My goal is to be able to show date range campaign and flow performance with metrics, such as revenue, open rate, click rate and order rate. Due to the structure of Klaviyo’s API, these simple queries are not that simple to perform on the fly. My solution is to create a relational database to store the data from the API, using python to pull and insert/update the database. The library klaviyo_data is a simple wrapper that I developed for automating a small ETL pipeline for Klaviyo’s performance data. The rest of this article is a brief summary of the steps needed to get up and running as quickly as possible. To dive in further, check out the repository here: https://github.com/webdjoe/klaviyo_data 

Setting up Klaviyo_data

The klaviyo_data app can be directly installed using pip and run with any Microsoft SQL Server instance. The quickest way to automate this is through a docker container that hosts the SQL Server and the klaviyo_data python application. If you followed the directions in this article to set up the pyshopify container, adding the klaviyo_data app is as simple as installing via pip in the container since all of the SQL drivers and requirements are already installed. 

Running MS SQL Server in Docker

Docker makes it easy to spin up a new instance of SQL Server, while automating the configuration. This is an example of running Microsoft SQL Server in a container via docker compose. If you need instructions on installing docker & docker compose, click here. The SQL Server administrator password is defined by the environment variable MSSQL_SQ_PASSWORD and must meet the minimum complexity requirements: an uppercase letter, a lowercase letter, a number and a non-alphanumeric character. The volume is the server’s data folder. Allowing database & configuration to be persisted across containers. If you need to run on a different port, edit the first part of the ports line. 

services:
    mssql:
        container_name: mssql
        image: mcr.microsoft.com/mssql/server:2019-latest
        ports:
            - "1433:1433"
        environment:
            ACCEPT_EULA: "Y"
            MSSQL_SA_PASSWORD: "StrongPassword1!"
        volumes:
            - "./data:/var/opt/mssql"

NOTE: If running on Docker on Windows, you may receive an error “** ERROR: [AppLoader] Failed to load LSA: 0xc0070102”.  This is a Docker volume mounting issue with MS SQL and Windows. The solution is to run container as root and breakout the mssql data directory:


services:
    mssql:
        container_name: mssql
        image: mcr.microsoft.com/mssql/server:2019-latest
        ports:
            - "1433:1433"
        user: root
        environment:
            ACCEPT_EULA: "Y"
            MSSQL_SA_PASSWORD: "StrongPassword1!"
        volumes:
            - "./mssql/data:/var/opt/mssql/data"
            - "./mssql/log:/var/opt/mssql/log"
            - "./mssql/secrets:/var/opt/mssql/secrets"

Installing Dependencies

Before installing the klaviyo_data app, the Microsoft ODBC drivers need to be installed. On windows, the Microsoft ODBC Driver executable can be downloaded from Microsoft. On Linux, use the following instructions. This can be done directly in the container running MS SQL Server by attaching, via docker exec.


# If running in the docker container from the above docker-compose.myl
$ sudo docker exec -it mssql bash

# Install curl and gnupg2 if not already available
# Use sudo for the following commands if not already root
$ apt update && apt install -y curl gnupg2

# The following add the Microsoft apt repository. This can be skipped if using the MS SQL Docker container
$ curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
$ curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

# Install ODBC Driver and unixodbc-dev headers
$ apt update && apt install -y msodbcsql18 unixodbc-dev

Installing klaviyo_data

Once the ODBC driver is installed, install the package via pip or from the code directly. Python 3.9 is required. The following process installs Python 3.9 and the required packages on Ubuntu. Windows users can install from the python executable or by using Anaconda to create a virtual environment.


# These are the directions to install python 3.9 on Ubuntu. 
# Use sudo if not already root
$ apt update && apt install python3.9 python3.9-dev python3-pip python3.9-venv

# Create a virtual environment & activate
# On Powershell or cmd, there is an activate.ps1/bat in the ~/venv/Scripts folder
$ python3.9 -m venv ~/venv && source ~/venv/bin/activate

# Ensure venv is active
$ which python
# Should return ~/venv/bin/python or venv in the current home directory

# Install klaviyo_data python library
$ python -m pip install klaviyo_data
# Or from source
$ cd ~ && git clone https://github.com/webdjoe/klaviyo_data.git
$ python -m pip install klaviyo_data/

Configuring the Application

The application is configured with an-ini based file that stores the Klaviyo private key, database credentials and date range. Note, that you don’t have to and shouldn’t use the database administrative password to run the script. A special database user can be set up to avoid this. I’ll demonstrate this in the following steps.


[klaviyo]
# Klaviyo Database Name, Schema, User & Password
database = klaviyo
schema = dbo
db_user = kv_user
db_pass = TheStrongestpassword1!
windows_auth = False
# Driver for pyodbc to use (The latest driver is 18)
driver = ODBC Driver 17 for SQL Server
# Database server & port - localhost if run inside docker container
server = localhost
port = 1433

# Pull last days of data
days = 30
# Pull data between start and end dates
# start = 20210101
# end = 20211231

# Klaviyo Public and Private Key
public_key = ****KLAVIYO PUBLIC KEY****
private_key = ****KLAVIYO PRIVATE KEY****

Building the Database

In order to run klaviyo_data, the database and table structure must be already in place. The database structure can be built using the command line application build_klaviyo or the SQLCMD style script. Both are able to build the database, tables and create a new user to access the Klaviyo database.

Build Database via Command Line

The Klaviyo database, tables and user can be built directly via command line. This command has the flexibility of building a new database or adding tables to an existing database. It can also add a new database user, which is recommended. It can also build a date dimension table if --date-dimension-start is specified 

# Activate virtual environment
$ source ~/venv/bin/activate

# Run the build_klaviyo command
$ build_klaviyo --help
Usage: build_klaviyo [OPTIONS]

   Builds database, tables and user for Klaviyo Data App.

    Add --tables-only to only build tables with existing --db.

    Include --db_user and --db_pass to create a user for the database.

    Include --date-dimension-start date to build a date dimension table.
    Date format is yyyy-MM-dd.

    Without --db_user and --db_pass, the admin user or existsing --db 
    user needs to be used to run the app

Options:
  --server TEXT                   Server to connect to
  --port TEXT                     Port to connect to
  --sa_user TEXT                  SQL Server admin user
  --sa_pass TEXT                  SQL Server admin password
  --driver TEXT                   SQL Server driver
  --db TEXT                       SQL Server database
  --tables-only / --db-and-tables
                                  Only build tables
  --date-dimension-start          Start date for date dimension table
  --help                          Show this message and exit.

# Build the tables and database. Use the --tables-only flag to use an existing database
# The --date-dimension-start argument builds a date dimension table starting from that date
$ build_klaviyo --server localhost --port 1433 \
    --sa_user sa --sa_pass TheStrongestpassword1! \
    --driver ODBC Driver 17 for SQL Server \
    --db klaviyo \
    --date-dimension-start 2019-01-01

Build Database via SQLCMD Script

The database and tables can also be built using the klaviyo.sql SQLCMD script. The only downside is that it does not have the flexibility to add tables to an existing database. The DateStart argument specifies the starting date for the DateDimension table. 

# Download the script from the repo
$ cd ~ && wget https://raw.githubusercontent.com/webdjoe/klaviyo_data/master/scripts/klaviyo.sql

# Run SQLCMD with all of the variables defined
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "SA_User_Password" -d master \
-v DBName="klaviyo" \
-v Schema="dbo" -v DBUser="kv_user" \
-v DBPassword="Strongerpassword1!" \
-v DateStart="20190101" \
-i ./klaviyo.sql

Running the Klaviyo Data App

Download the config.ini template and populate with the respective info. The date range passed in the command line argument overrides the date range defined in the config.ini. The current working directory is the default directory that the app attempts to find config.ini. Ensure the virtual environment is active before running.


# Download and edit config.ini template
$ wget https://raw.githubusercontent.com/webdjoe/klaviyo_data/master/config.ini
$ nano config.ini

$ klaviyo_cli --help
Usage: klaviyo_cli [OPTIONS]

  Run Klaviyo Data App CLI.

Options:
  -d, --days INTEGER     get days of history
  -b, --between TEXT...  get between 2 dates - yyyy-MM-dd, ex -b
                         2020-01-01 2020-01-02
  --config TEXT          Relative location of config.ini -
                         defaults to config.ini in currect directory
  --help                 Show this message and exit.

# Start with a small time period to ensure everything is configured correctly
$ klaviyo_cli -d 5
Pulling flow data from 5 days ago
Pulling campaign data from 5 days ago

That’s all there is to it. The process to pull the data does take some time depending on the number of flows, campaigns and time periods but after running you should have your email marketing data available in a relational database for easy querying.

Table Structure

The CampaignList and FlowList tables contain information regarding the respective campaign/flow, including the id, sent and last modified date. The CampaignList table also stores the subject line in Unicode, so emojis are preserved. The FlowMetrics and CampaignMetrics have similar structures, except for the id, which is flow_id and campaign_id, respectively.

The CampaignMetrics and FlowMetrics tables contain all of the metric data for each measure and each flow/campaign. The FlowList and CampaignList contain all of the flows and campaign details. CampaignList also includes the subject/preview lines for each of the Campaigns. The data type for strings is NVARCHAR, so emojis should still show as long as the platform used to query supports unicode.

idnamesubjectstatusstatus_labelstatus_idnum_recipientsis_segmentedmessage_typetemplate_idsent_atsend_time
TxCXT4XCampaign Name 1👀 Unicode subject linesentSent1800000emailXtxX4XD8/16/22 21:418/16/22 21:40
idnameobjectstatusarchivedcreatedupdated
FLOW_IDFlow Nameflowdraft032:20.052:26.0
idnameobjectstatusarchivedcreatedupdated
FLOW_IDFlow Nameflowdraft032:20.052:26.0
flow_iddatemetric_idvaluesmeasure
FLOW_ID2020-01-01METRIC_ID25count
campaign_idobjectidnamelist_typefolderperson_countcampaign_sent_at
CAMPAIGN_IDlistLIST_IDLIST_NAMEsegmentNULL1000008/25/2022 22:02
campaign_idobjectidnamelist_typefolderperson_countcampaign_sent_at
CAMPAIGN_IDlistLIST_IDLIST_NAMEsegmentNULL1000008/25/2022 22:02

Example Queries

The following sections include some common queries that may be useful for performing analytics on email marketing campaigns and flows. The queries are demonstrative in purpose and not optimized for performance.

Get Available Metric Names and ID's

Metrics are defined in the CampaignMetrics and FlowMetrics tables by the metric ID and measure. The measure can be count or value. The place order metric with value measure is used for campaign revenue. The placed order metric with count measure would be the number of orders. Other metrics, such as clicks, opens & recipients use only count. The full list of metrics, metric ID's and measures available can be pulled with the following query.

Retrieve Available Metric Names and ID's

SELECT DISTINCT cm.[metric_id],
                met.[name],
                cm.[measure]
FROM   [klaviyo].[dbo].[campaignmetrics] cm
       INNER JOIN klaviyo.[dbo].metrics met
               ON met.[id] = cm.[metric_id]
Metric IDMetric NameMeasure
J8wEZQPlaced Ordercount
J8wEZQPlaced Ordervalue
K8EZcUReceived Emailcount
KdifyiClicked Emailcount
KHctvUMarked Email as Spamcount
LxXB8WOrdered Productcount
LxXB8WOrdered Productvalue
MvCm2pUnsubscribedcount
My7Vk2Opened Emailcount

Daily Revenue Over Last 30 Days

Here is a query to retrieve the email campaign revenue over the last 30 days. The @measure and @metric_id specify the value of the revenue metric. Changing the @measure variable will query the number of orders per day. Other metrics such as clicks or opens can be substituted using their respective metric ID's in the @metric_id variable. Most other metrics require count as a measure. The script defaults to getting the last thirty days of data but the start and end date can also be set via their variables.

Daily Revenue Query
DECLARE @start_date DATE = DATEADD(day, -30, GETDATE())
DECLARE @end_date DATE = GETDATE()
DECLARE @measure NVARCHAR(10) = 'value'
DECLARE @metric_id NVARCHAR(10) = 'J8wEZQ'

SELECT cm.2025 AS [Date],
       sum(cm.[values]) AS Revenue
FROM [dbo].[CampaignMetrics] cm
WHERE  cm.2025 BETWEEN  @end_date 
        AND cm.2025 >= @start_date
    AND cm.[metric_id] = @metric_id
    AND cm.[measure] = @measure
GROUP  BY cm.2025
Daily Revenue Data
DateRevenue
2022-01-011580.46
2022-01-024678.25
2022-01-031255.56
2022-01-042587.25
2022-01-054158.28
2022-01-063752.3
2022-01-073589.45
2022-01-084258.25

Metrics & Rates by Month in a Single Table

Querying revenue or other metrics by day or month is only marginally useful. What if I want to get all of the metrics in a single query? The following query is overly comprehensive to demonstrate the range of metrics that can be queried. I’ve included the metrics for recipients, opens, clicks, marked as spam, unsubscribes, orders and revenue. These are then used to get the monthly rates. Since orders and revenue use the same metric ID, I had to combine the metric_id and measure to give unique column names when pivoting. Metrics can be easily added or removed by editing the main SELECT statement, the WHERE clause in the src table and the WHERE clause in the pvt table. The pvt table used the metric_id and measure concatenated with an underscore. I hardcoded all of the metric ID’s and measures, while making the final column names more descriptive. 

Monthly Campaign Performance Query

Click to expand full query

SELECT CAST([Month] AS [DATE]) AS [Month]
	  , [K8EZcU_count] AS [Received]
	  , [My7Vk2_count] AS [Opens]
	  , [Kdifyi_count] AS [Clicks]
	  , [KHctvU_count] AS [MarkedSpam]
	  , [MvCm2p_count] AS [Unsubscribes]
	  , [J8wEZQ_count] AS [Orders]
	  , [J8wEZQ_value] AS [Revenue]
	  , [My7Vk2_count]/[K8EZcU_count] AS [OpenRate]
	  , [Kdifyi_count]/[K8EZcU_count] AS [ClickRate]
	  , [Kdifyi_count]/[My7Vk2_count] AS [ClickToOpenRate]
	  , [KHctvU_count]/[K8EZcU_count] AS [SpamRate]
	  , [MvCm2p_count]/[K8EZcU_count] AS [UnsubscribeRate]
	  , [J8wEZQ_count]/[K8EZcU_count] AS [OrdersPerEmail]
	  , [J8wEZQ_count]/[Kdifyi_count] AS [ConversionRate]
	  , [J8wEZQ_value]/[K8EZcU_count] AS [RevenuePerRecipient]
	  , [J8wEZQ_value]/[J8wEZQ_count] AS [AverageOrderValue]
FROM (
	SELECT  [Month] = DATEADD(MONTH, DATEDIFF(MONTH, 0, 2025), 0)
	       ,[metric_id] + '_' + [measure] AS metric_measure,
		    [values]
		FROM dbo.CampaignMetrics
		WHERE [metric_id] 
			IN ('K8EZcU', 'My7Vk2', 'Kdifyi', 'J8wEZQ', 'KHctvU', 'MvCm2p')
) AS src
PIVOT
(
	SUM([values]) FOR metric_measure
		IN (
		    [K8EZcU_count]
		   ,[My7Vk2_count]
		   ,[Kdifyi_count]
		   ,[J8wEZQ_count]
		   ,[J8wEZQ_value]
		   ,[KHctvU_count]
		   ,[MvCm2p_count])
) pvt
ORDER BY [Month]
MonthReceivedOpensClicksMarkedSpamUnsubscribesOrdersRevenueOpenRateClickRateClickToOpenRateSpamRateUnsubscribeRateOrdersPerEmailConversionRateRevenuePerRecipientAverageOrderValue
11/1/202110000050000500011050010000.000.50.20.80.0000010.000010.020.52.575

Ideally, I would query the aggregate data in daily form and use whichever platform I am on to calculate rates based on the date context. This query can easily be changed to a daily basis by replacing the DATEADD function in both places with the 2025 column. The daily rates aren’t of much use for campaigns without aggregating over a time period, so I could remove all of the columns that perform arithmetic. 

From this query, I can table and chart open rates, click rates, etc over time. Another option to get multiple metrics in a single table is to use inner joins but that becomes messy depending on how many metrics I need. Also, I would have to account for the fact that some days might be null for some metrics and not others.

Overall Flow Performance by Month

The query above can easily be edited to retrieve flow data. Simply replace dbo.CampaignMetrics with dbo.FlowMetrics in the src table. The same modifications in the prior query can be used to query data on a daily basis.

Click to expand full query

SELECT CAST([Month] AS [DATE]) AS [Month]
	  , [K8EZcU_count] AS [Received]
	  , [My7Vk2_count] AS [Opens]
	  , [Kdifyi_count] AS [Clicks]
	  , [KHctvU_count] AS [MarkedSpam]
	  , [MvCm2p_count] AS [Unsubscribes]
	  , [J8wEZQ_count] AS [Orders]
	  , [J8wEZQ_value] AS [Revenue]
	  , [My7Vk2_count]/[K8EZcU_count] AS [OpenRate]
	  , [Kdifyi_count]/[K8EZcU_count] AS [ClickRate]
	  , [Kdifyi_count]/[My7Vk2_count] AS [ClickToOpenRate]
	  , [KHctvU_count]/[K8EZcU_count] AS [SpamRate]
	  , [MvCm2p_count]/[K8EZcU_count] AS [UnsubscribeRate]
	  , [J8wEZQ_count]/[K8EZcU_count] AS [OrdersPerEmail]
	  , [J8wEZQ_count]/[Kdifyi_count] AS [ConversionRate]
	  , [J8wEZQ_value]/[K8EZcU_count] AS [RevenuePerRecipient]
	  , [J8wEZQ_value]/[J8wEZQ_count] AS [AverageOrderValue]
FROM (
	SELECT  [Month] = DATEADD(MONTH, DATEDIFF(MONTH, 0, 2025), 0)
	       ,[metric_id] + '_' + [measure] AS metric_measure,
		    [values]
		FROM dbo.FlowMetrics
		WHERE [metric_id] 
			IN ('K8EZcU', 'My7Vk2', 'Kdifyi', 'J8wEZQ', 'KHctvU', 'MvCm2p')
) AS src
PIVOT
(
	SUM([values]) FOR metric_measure
		IN (
		    [K8EZcU_count]
		   ,[My7Vk2_count]
		   ,[Kdifyi_count]
		   ,[J8wEZQ_count]
		   ,[J8wEZQ_value]
		   ,[KHctvU_count]
		   ,[MvCm2p_count])
) pvt
ORDER BY [Month]
MonthReceivedOpensClicksMarkedSpamUnsubscribesOrdersRevenueOpenRateClickRateClickToOpenRateSpamRateUnsubscribeRateOrdersPerEmailConversionRateRevenuePerRecipientAverageOrderValue
11/1/202110000050000500011050010000.000.50.20.80.0000010.000010.020.52.575

Metrics by Campaign

Now that I’m able to get date range metrics, I’d like to look at campaign-level metrics. This builds on the prior query that pivots the metric table by month. Here, I instead pivot using the campaign_id, not the date.  Then, the metrics table is joined with the campaign list table. It returns in descending order of the date and time the campaign was sent. I was overly comprehensive here again to demonstrate the variety of metrics and columns that can be pulled with this query. I added the condition to filter out campaigns less than 10,000. This is to eliminate any special or one-off email campaigns that don’t have the same weighting as the large scale campaigns.

Click to expand full query

SELECT cl.[name]
       ,cl.[subject]
       ,cl.[status]
       ,cl.[num_recipients]
       ,cl.[sent_at]
       ,cl.[send_time]
       ,cm.[Recipients]
       ,cm.[AverageOrderValue]
       ,cm.[ClickRate]
       ,cm.[ClickToOpenRate]
       ,cm.[ConversionRate]
       ,cm.[OpenRate]
       ,cm.[OrdersPerEmail]
       ,cm.[RevenuePerRecipient]
       ,cm.[SpamRate]
       ,cm.[UnsubscribeRate]
FROM [klaviyo].[dbo].[CampaignList] cl
    LEFT JOIN (
		  SELECT [campaign_id]
			    ,[K8EZcU_count] AS [Recipients]
			    ,[My7Vk2_count]/[K8EZcU_count] AS [OpenRate]
			    ,[Kdifyi_count]/[K8EZcU_count] AS [ClickRate]
			    ,[Kdifyi_count]/[My7Vk2_count] AS [ClickToOpenRate]
			    ,[KHctvU_count]/[K8EZcU_count] AS [SpamRate]
			    ,[MvCm2p_count]/[K8EZcU_count] AS [UnsubscribeRate]
			    ,[J8wEZQ_count]/[K8EZcU_count] AS [OrdersPerEmail]
			    ,[J8wEZQ_count]/[Kdifyi_count] AS [ConversionRate]
			    ,[J8wEZQ_value]/[K8EZcU_count] AS [RevenuePerRecipient]
			    ,[J8wEZQ_value]/[J8wEZQ_count] AS [AverageOrderValue]
    FROM (
		SELECT [campaign_id], [metric_id] + '_' + [measure] AS metric_measure, [values]
        FROM dbo.CampaignMetrics
        WHERE [metric_id] IN ('K8EZcU', 'My7Vk2', 'Kdifyi', 'J8wEZQ', 'KHctvU', 'MvCm2p')
		) AS src
		PIVOT
		(
		SUM([values]) FOR metric_measure IN ([K8EZcU_count], [My7Vk2_count], [Kdifyi_count], [J8wEZQ_count], [J8wEZQ_value], [KHctvU_count], [MvCm2p_count])
		) pvt
  ) cm
ON cl.[id] = cm.[campaign_id]
WHERE cl.[status] = 'sent' AND cm.[Recipients] > 10000
ORDER BY cl.[sent_at] desc
idnamesubjectnum_recipientssent_atsend_timeRecipientsAverageOrderValueClickRateClickToOpenRateConversionRateOpenRateOrdersPerEmailRevenuePerRecipientSpamRateUnsubscribeRate
CAMPAIGN_IDCampaign NameCampaign Subject1000008/16/2022 21:418/16/2022 21:401000075.000.010.010.50.750.0050.150.0000010.00001

Top 10 Subject Lines with the Best Open Rate

A common analysis is what were the top 10 subject lines of campaigns by open rate? This can help find trends or patterns that make recipients open an email. Using the previous query, this is simple to do by adding the TOP function and sorting by open rate in descending order.

Click to expand full query

SELECT cl.[name]
       ,cl.[subject]
       ,cl.[status]
       ,cl.[num_recipients]
       ,cl.[sent_at]
       ,cl.[send_time]
       ,cm.[Recipients]
       ,cm.[AverageOrderValue]
       ,cm.[ClickRate]
       ,cm.[ClickToOpenRate]
       ,cm.[ConversionRate]
       ,cm.[OpenRate]
       ,cm.[OrdersPerEmail]
       ,cm.[RevenuePerRecipient]
       ,cm.[SpamRate]
       ,cm.[UnsubscribeRate]
FROM [klaviyo].[dbo].[CampaignList] cl
    LEFT JOIN (
		  SELECT [campaign_id]
			    ,[K8EZcU_count] AS [Recipients]
			    ,[My7Vk2_count]/[K8EZcU_count] AS [OpenRate]
			    ,[Kdifyi_count]/[K8EZcU_count] AS [ClickRate]
			    ,[Kdifyi_count]/[My7Vk2_count] AS [ClickToOpenRate]
			    ,[KHctvU_count]/[K8EZcU_count] AS [SpamRate]
			    ,[MvCm2p_count]/[K8EZcU_count] AS [UnsubscribeRate]
			    ,[J8wEZQ_count]/[K8EZcU_count] AS [OrdersPerEmail]
			    ,[J8wEZQ_count]/[Kdifyi_count] AS [ConversionRate]
			    ,[J8wEZQ_value]/[K8EZcU_count] AS [RevenuePerRecipient]
			    ,[J8wEZQ_value]/[J8wEZQ_count] AS [AverageOrderValue]
    FROM (
		SELECT [campaign_id], [metric_id] + '_' + [measure] AS metric_measure, [values]
        FROM dbo.CampaignMetrics
        WHERE [metric_id] IN ('K8EZcU', 'My7Vk2', 'Kdifyi', 'J8wEZQ', 'KHctvU', 'MvCm2p')
		) AS src
		PIVOT
		(
		SUM([values]) FOR metric_measure IN ([K8EZcU_count], [My7Vk2_count], [Kdifyi_count], [J8wEZQ_count], [J8wEZQ_value], [KHctvU_count], [MvCm2p_count])
		) pvt
  ) cm
ON cl.[id] = cm.[campaign_id]
WHERE cl.[status] = 'sent' AND cm.[Recipients] > 10000
ORDER BY cl.[sent_at] desc
namesubjectsent_atRecipientsOrdersAverageOrderValueClickRateClickToOpenRateConversionRateOpenRateOrdersPerEmailRevenuePerRecipientSpamRateUnsubscribeRate
Campaign Name 1🎉 Subject 17/18/2022 20:57100000120750.0030.0060.40.80.011.80.0000010.0001
Campaign Name 2🎉 Subject 28/7/2022 19:08105000472.5650.0090.010.50.750.0091.70.000010.0001
Campaign Name 3🎉 Subject 36/17/2022 22:11100000200520.0040.0050.50.730.0021.60.000050.00015
Campaign Name 4🎉 Subject 47/7/2022 21:52110000132850.0030.0050.40.70.0091.20.000010.0005
Campaign Name 5🎉 Subject 56/12/2022 18:331200005401050.0050.0080.90.690.0091.50.0001765070.0004
Campaign Name 6🎉 Subject 66/10/2022 21:111300002081110.0080.0090.20.690.0010.9NULL0.0003
Campaign Name 7🎉 Subject 712/7/2021 16:44100000640280.0080.0090.80.680.0081.20.0000120.0001
Campaign Name 8🎉 Subject 87/1/2022 19:41110000495980.0090.0090.50.670.0090.90.0001766240.0001
Campaign Name 9🎉 Subject 97/4/2022 21:39120000288450.0080.0050.30.650.0090.90.000050.0001
Campaign Name 10🎉 Subject 106/19/2022 16:42105000735800.010.0050.70.640.0010.9NULL0.0001

Time of the Day Performance

The time of day an email is sent can have a huge impact on performance. Klaviyo has it’s own Smart Sending feature that tries to optimize by the time of day an email is sent but I want to perform my own analysis based not only on open rate, but other metrics as well. The following query pulls performance metrics of campaigns by the hour of the day that it is sent. I’ve included a filter to remove hours with low volume and added a column that shows a count of emails sent during that hour. Klaviyo datetime values are in UTC, so they must be converted to whichever timezone is desired. The min_recipients variable filters out any campaigns that had less than that amount of recipients.

Click to expand full query

DECLARE @tz varchar(30) = 'Eastern Standard Time';
DECLARE @min_recipients int = 10000;
DECLARE @min_count int = 30;

WITH metric_cte AS
    (
        SELECT       [campaign_id]
                    ,[K8EZcU_count] AS [Recipients]
                    ,[My7Vk2_count] AS [Opens]
                    ,[Kdifyi_count] AS [Clicks]
                    ,[KHctvU_count] AS [MarkedSpam]
                    ,[MvCm2p_count] AS [Unsubscribes]
                    ,[J8wEZQ_count] AS [Orders]
                    ,[J8wEZQ_value] AS [Revenue]
        FROM (
		SELECT  [campaign_id]
		       ,[metric_id] + '_' + [measure] AS metric_measure
		       ,[values]
            FROM dbo.CampaignMetrics
            WHERE [metric_id] IN ('K8EZcU', 'My7Vk2', 'Kdifyi', 'J8wEZQ', 'KHctvU', 'MvCm2p')
		) AS src
		PIVOT
		(
		SUM([values]) FOR metric_measure IN ([K8EZcU_count], [My7Vk2_count], [Kdifyi_count], [J8wEZQ_count], [J8wEZQ_value], [KHctvU_count], [MvCm2p_count])
		) pvt
    )

SELECT       DATEPART(HOUR, cl.[sent_at] AT TIME ZONE 'UTC' AT TIME ZONE @tz) SentHour
            ,COUNT(*) EmailCount
            ,SUM(cm.[Opens])/SUM(cm.[Recipients]) [OpenRate]
            ,SUM([Clicks])/SUM([Recipients]) AS [ClickRate]
	    ,SUM([Clicks])/SUM([Opens]) AS [ClickToOpenRate]
	    ,SUM([Orders])/SUM([Recipients]) AS [OrdersPerRecipient]
	    ,SUM([Orders])/SUM([Clicks]) AS [ConversionRate]
	    ,SUM([Revenue])/SUM([Orders]) AS [AverageOrderValue]

FROM [klaviyo].[dbo].[CampaignList] cl
    LEFT JOIN metric_cte cm
    ON cl.[id] = cm.[campaign_id]
WHERE cl.[status] = 'sent' AND cm.[Recipients] > @min_recipients

GROUP BY  DATEPART(HOUR, cl.[sent_at] AT TIME ZONE 'UTC' AT TIME ZONE @tz)

HAVING COUNT(*) > @min_count

ORDER BY DATEPART(HOUR, cl.[sent_at] AT TIME ZONE 'UTC' AT TIME ZONE @tz)
SentHourEmailCountOpenRateClickRateClickToOpenRateOrdersPerRecipientConversionRateAverageOrderValue
8650.550.01250.0580.00120.5678.1

Conclusion

This is just a small sample of the analysis that can be performed and the ease of generating reports from data in relational tables. This is continues to be a work in progress. There are numerous optimizations and refinements that can be made to the database structure, the klaviyo_data application and the queries above. The next article on klaviyo_data will be on using the templates endpoint to pull html and generate screenshot of the campaign emails. This can be extremely useful for generating reports and finding trends on what design aspects are working and what isn’t.