Census Use Case

Dynamic Personalization and Campaign Segmentation in Klaviyo

Learn how to increase your customer lifetime value with customer data in your data warehouse.


While paid channels like Facebook and Google are great for customer acquisition, email is ideal for retargeting existing customers so that you don’t have to keep paying to reactivate existing customers. Email marketing performance depends on segmentation, and segmentation depends on the quality of your first-party data. In this recipe, we’ll show you how to increase your customer lifetime value with customer data in your data warehouse.

Campaign Segmentation with RFM Segments

RFM analysis is a data driven customer behavior segmentation technique. RFM stands for recency, frequency, and monetary value. The idea is to segment customers based on when their last purchase was, how often they've purchased in the past, and how much they've spent overall.

There are a number ways you can use RFM segments in a marketing automation, for instance, you coul send promo codes to low frequency customers, send upsell emails to people with low monetary scores but high recency or frequency, or send reminder emails to people who have high monetary scores but haven't purchased lately.  

Why RFM? One advantage of RFM scores over LTV is that it adapts over time - a $40+ LTV might be a sign of your best customers now, but only middling after your store takes off.  A Monetary score of 4, on the other hand, will always represent your highest spending customers.

A better approach to Klaviyo data integration

Klaviyo has native integrations with eCommerce platforms (like Shopify) that send order data into Klaviyo, but there are a few big reasons to also send data from your data warehouse with Census:

  1. You want to create dynamic marketing segments based on analysis in your data warehouse like calculating a recency, frequency, and monetary (RFM) scores to band together your best and worst customers.
  2. You want to do more advanced data science on your customers using tools like Snowpark or DataBricks.
  3. You use a custom eCommerce or subscription platforms or want to incorporate offline data into your segmentation

Once you see how easy it is to integrate your first-party data into Klaviyo, you'll open up infinite possibilities for personalization and segmentation.

Use Case

To get started, you'll need a SQL query or pre-existing Customer summary table in your data warehouse that aggregates purchase metrics and RFM scores with a row for every customer email. Using a table in you data warehouse rather than an ad-hoc query is recommended because it will allow you to analyze the activity of customers in each segment using the same segment definitions across your data warehouse and Klaviyo.

Note: you’ll also need to add custom properties for any field that doesn’t already exist in your Klaviyo instance.

An End-to-End Example

The example below works for customers with Shopify data brought into the data warehouse by the Fivetran Shopify connector to create RFM segments. You can use this code to create a SQL model in Census that includes RFM segments and the corresponding data listed above.

with rfm_raw as (
date_diff(current_date(), date(max(created_at)), day) as r,	
count(*) as f,	
sum(total_price) as m
from supple-comfort-359322.shopify.order o
group by customer_id
calc_r as (
ntile(4) over (order by r asc) r_score
from rfm_raw
calc_f as (
ntile(4) over (order by f desc) f_score
from rfm_raw
calc_m as (
select	customer_id,	
ntile(4) over (order by m desc) m_score
from rfm_raw
m as ltv,	
f as order_count,	
m_scorefrom calc_r
left join calc_f
on calc_r.customer_id = calc_f.customer_id
left join calc_m
on calc_r.customer_id = calc_m.customer_id
left join supple-comfort-359322.shopify.customer c
on calc_r.customer_id = c.id

Here’s how you sync it to Klaviyo:

And then you can create a Klaviyo segment, such as ‘Low Frequency users.’

Now that you have your RFM segments in Klaviyo, you can setup automatically triggered campaigns as your customer data changes over time and new customers move dynamically from one segment to another.

Destination Guides

Unsure how to implement your use case?
Schedule a call with us to discuss!