Tutorial: How to Measure Time from Subscribe to Purchase in Klaviyo + Google Sheets

This step-by-step guide will help you measure how long it takes subscribers to make their first purchase after signing up for your email list.

---

Step 1: Create a Segment in Klaviyo

Go to Lists & Segments and create a new segment with the following condition:

• Person has Subscribed to Email Marketing at least once in the last 365 days

Name the segment something like: Subscribed in Last 365 Days

---

Step 2: Export the Segment

Click into your new segment and export it to CSV. Make sure the export includes:

• Email
• First Active
• Your custom profile property for First Purchase Date
• Note: I've added this custom property to all my client accounts since 2017. It's included in the Reliable Revenue Klaviyo tutorials as well. If you don’t have this field in your account, you’ll need to retrieve the first purchase date from your ecommerce platform instead.

---

Step 3: Clean Your Data in Google Sheets

Open the CSV in Google Sheets. Remove:

• Test emails
• Any rows where First Purchase Date is blank

---

Step 4: Calculate Days to Purchase

Add a new column titled Days to Purchase. In the first row of that column, enter:

=IF(OR(ISBLANK(B2), ISBLANK(C2)), "", INT(C2) - INT(B2))

This calculates the number of full days between subscribing and purchasing.

Format the column as a Number (not Date).

---

Step 5: Group by Conversion Window

Add another column titled Conversion Window and enter this formula:

=IF(D2="", "No Purchase", IF(D2<=7, "0–7 days", IF(D2<=30, "8–30 days", IF(D2<=90, "31–90 days", "90+ days"))))

This creates readable categories for your data.

---

Step 6: Create a Pivot Table

1. Highlight your entire dataset
2. Go to Insert → Pivot table, choose “New sheet”
3. In the Pivot Editor:
   • Rows: Add Conversion Window
   • Values: Add Email and summarize by COUNTA

This gives you a count of how many people fall into each conversion window.

---

Step 7: Visualize with a Chart (Optional)

Click anywhere in the pivot table, then click the chart icon in the top toolbar. Choose a Pie chart or Bar chart.

---

Case Study: Sew Hungry Hippie

From 3352 subscribers who made a purchase in the last year:

• 56.4% converted within 0–7 days
• 67% converted within 30 days
• Only 22.5% converted after 90 days

This shows that most conversions happen in the first week, and the welcome flow plays a key role.

---

By measuring this for your own store, you can:

• Optimize your welcome flow timing
• Identify drop-off windows
• Create benchmark goals for list conversion

Track it quarterly to measure improvements over time.

If you want reliable revenue, this is where to start.

Natalie, founder of Sew Hungry Hippie, is a member of Reliable Revenue and the Mastermind Accelerator where I coach Shopify store owners how to get predictable and reliable revenue with step-by-step, proven email marketing strategies.