Customer Segmentation

Customer Segmentation

Overview

Customer segmentation divides your customer base into groups with similar purchase behavior so marketing campaigns, promotions, and product recommendations can be precisely targeted. Klaviyo, Omnisend, and Metorik all provide RFM-style segmentation out of the box for Shopify and WooCommerce without custom SQL. Only build a custom segmentation system if your platform's tools don't support the segment logic you need.

When to Use This Skill

• When personalizing email campaigns by lifecycle stage (new, active, at-risk, lapsed)
• When building suppression lists to avoid wasting ad spend on already-converted customers
• When identifying "champion" customers for VIP programs and early access campaigns
• When analyzing which acquisition cohort has the best 90-day retention
• When syncing behavioral segments to advertising platforms (Meta, Google)

Core Instructions

Step 1: Determine platform and choose the right segmentation tool

---

Step 2: Platform-specific setup

---

Shopify

Option A: Shopify Admin segments (basic, free)

1. Go to Admin → Customers 2. Use the filter bar to create segments based on:

• Order count, total spent, last order date
• Email subscription status, tags, location
• Product purchased

3. Save the filter as a customer segment 4. Export the segment to CSV for use in ads or email campaigns

Option B: Klaviyo (recommended for lifecycle segmentation)

Klaviyo syncs automatically with Shopify and provides RFM-style segmentation built on real purchase data.

Key segments to create in Klaviyo:

1. Champions (high-value, frequent, recent):

• Rule: Ordered at least 3 times AND Last order within 60 days AND Total spent > $200
• Action: Early access to new products, VIP perks, referral program invites

2. At Risk — High Value:

• Rule: Total spent > $200 AND Last order 90–180 days ago
• Action: Win-back flow with personalized offer

3. Recent First-Time Buyers:

• Rule: Order count equals 1 AND First order within 30 days
• Action: Onboarding sequence, encourage second purchase

4. Lapsed Customers:

• Rule: Last order more than 180 days ago AND Order count >= 2
• Action: Re-engagement campaign with "We've missed you" messaging

5. Subscribers who never purchased:

• Rule: Email subscriber AND Order count equals 0
• Action: Welcome series with social proof and first-order incentive

To create segments in Klaviyo: 1. Go to Lists & Segments → Create Segment 2. Add conditions using the filter builder — Klaviyo has 150+ pre-built filter types including Shopify-specific events 3. Name the segment clearly (e.g., "At Risk High Value — 90-180 days") 4. Use the segment in a Flow trigger or as an audience for a Campaign

Klaviyo Predictive Analytics (paid plans):

• Klaviyo automatically calculates Predicted CLV, Expected Date of Next Order, and Churn Risk per customer
• Find these under Analytics → Predictive Analytics
• Use these predictions as segment filter criteria without any custom code

---

WooCommerce

Option A: Metorik (recommended analytics platform)

1. Install Metorik (connects via WooCommerce REST API) 2. Go to Metorik → Segments → Create Segment 3. Build rule-based segments using purchase history, product affinity, geography, and more 4. Metorik calculates RFM scores automatically 5. Export segment to CSV or sync directly to Klaviyo/Mailchimp

Option B: Klaviyo for WooCommerce

1. Install Klaviyo: Email Marketing for WooCommerce from WordPress.org 2. Klaviyo syncs your WooCommerce order history and creates profiles for all customers 3. Build the same lifecycle segments described in the Shopify section above

---

BigCommerce

Customer Groups for tier-based segmentation:

1. Go to Customers → Customer Groups → Add Group 2. Create groups based on purchase behavior rules:

• "VIP" — customers with lifetime spend > $500
• "Repeat Buyers" — customers with 3+ orders

3. Assign group-specific pricing, category access, or shipping rules

Klaviyo for behavioral segmentation:

• Install the Klaviyo for BigCommerce app
• Connect your BigCommerce store
• Build behavioral segments in Klaviyo using purchase history and event data

---

Custom / Headless

Build RFM scoring in SQL and sync to an ESP for activation:

-- PostgreSQL: Calculate RFM scores for all customers
WITH customer_rfm AS (
  SELECT
    customer_id,
    EXTRACT(EPOCH FROM (NOW() - MAX(created_at))) / 86400 AS recency_days,
    COUNT(id) AS frequency,
    SUM(subtotal_cents) / 100.0 AS monetary
  FROM orders
  WHERE status NOT IN ('cancelled', 'refunded')
  GROUP BY customer_id
),
rfm_scored AS (
  SELECT
    customer_id,
    recency_days, frequency, monetary,
    NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,  -- Lower recency = higher score
    NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
    NTILE(5) OVER (ORDER BY monetary ASC) AS m_score
  FROM customer_rfm
)
SELECT
  customer_id, r_score, f_score, m_score,
  r_score + f_score + m_score AS rfm_total,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'champions'
    WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'loyal_customers'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'recent_customers'
    WHEN r_score <= 2 AND f_score >= 4 AND m_score >= 4 THEN 'cannot_lose_them'
    WHEN r_score <= 2 AND f_score >= 3 THEN 'at_risk'
    WHEN r_score = 1 AND f_score <= 2 THEN 'lost'
    ELSE 'needs_attention'
  END AS segment
FROM rfm_scored;

// Sync a segment to Klaviyo — batched at 100 profiles per request
export async function syncSegmentToKlaviyo(segmentCustomers: Customer[], klaviyoListId: string) {
  const BATCH_SIZE = 100;
  for (let i = 0; i < segmentCustomers.length; i += BATCH_SIZE) {
    const batch = segmentCustomers.slice(i, i + BATCH_SIZE);
    await fetch(`https://a.klaviyo.com/api/lists/${klaviyoListId}/relationships/profiles/`, {
      method: 'POST',
      headers: {
        Authorization: `Klaviyo-API-Key ${process.env.KLAVIYO_PRIVATE_KEY}`,
        'Content-Type': 'application/json',
        revision: '2024-10-15',
      },
      body: JSON.stringify({
        data: batch.map(c => ({
          type: 'profile',
          attributes: { email: c.email, first_name: c.firstName, last_name: c.lastName },
        })),
      }),
    });
  }
}

// Export suppression list for Meta Ads — hash emails before sending
export async function exportSuppressionListForMeta(lookbackDays = 30): Promise<string[]> {
  const recentBuyers = await db.orders.findMany({
    where: { createdAt: { gte: new Date(Date.now() - lookbackDays * 86400000) }, status: 'completed' },
    select: { customerEmail: true },
    distinct: ['customerEmail'],
  });

  return recentBuyers.map(({ customerEmail }) => {
    const { createHash } = require('crypto');
    return createHash('sha256').update(customerEmail.toLowerCase().trim()).digest('hex');
  });
}

---

Step 3: Map segments to actions

Every segment should have a clear marketing action:

---

Step 4: Build suppression lists for paid ads

Upload your most recent buyers as suppression lists on Meta and Google to avoid wasting acquisition budget:

• Meta Business Manager: Audiences → Create Audience → Customer List → Upload hashed email CSV
• Google Ads: Audience Manager → Customer Match → Upload email list
• Refresh these suppression lists monthly

Best Practices

• Refresh segments nightly — Klaviyo and Metorik do this automatically; for custom builds, run the RFM SQL nightly
• Start with RFM, then layer behavioral signals — purchase recency/frequency/spend is the most reliable foundation; add category affinity and channel preference as you collect more data
• Always build suppression lists alongside targeting lists — sending re-engagement campaigns to active customers wastes budget and annoys them
• Validate segment sizes before campaign sends — a segment returning 0 customers usually indicates a logic error; set a minimum threshold check
• Track which segments respond best to each type of offer — champions rarely need discounts; at-risk customers may respond to free shipping more than percentage off

Common Pitfalls

Related Skills

• @customer-lifetime-value
• @personalization-engine
• @referral-program

Customer Retention Analytics and Behavioral Targeting System

Problem Description

UrbanThreads, a direct-to-consumer apparel brand, has been running for four years and recently raised a Series A. Their new Head of Growth needs two things to plan the next marketing push: (1) a clear picture of how customers acquired in different months are retaining over their first year, and (2) a flexible system for defining targeting segments based on customer behavior and properties — so the team can create segments like "customers who viewed product pages 3+ times in the last 14 days but haven't purchased" without waiting on an engineer.

The engineering team uses PostgreSQL. The orders table has columns: customer_id, id, created_at (timestamp), status ('completed', 'cancelled', 'refunded', 'pending'), subtotal_cents. The customer_events table has columns: customer_id, event (string event name), created_at (timestamp). The customers table has columns: id, email, acquisition_channel, plan, and other profile properties.

For the retention analysis, the team wants a single SQL query they can run in their BI tool showing each acquisition cohort alongside the number of customers still purchasing at defined retention milestones.

For the behavioral segment system, the team wants a TypeScript module that evaluates whether a given customer belongs to a segment, where segments are defined as a list of rules with a combining operator. The system should handle checking event counts, customer property comparisons, and membership in other segments. Performance matters — the platform has millions of events per day — and the engineering lead wants any database recommendations documented in a db_recommendations.md file.

Output Specification

Produce the following files:

1. cohort_analysis.sql — A complete SQL query against the orders table that shows cohort retention. For each acquisition cohort, output: cohort_month, cohort_size, and columns showing how many customers from that cohort made additional purchases at each defined retention milestone. Order results with most recent cohorts first.

2. behavioral_segments.ts — A TypeScript module containing:

• Type definitions for a segment and its rules, supporting rules based on events, customer properties, and other segment membership
• An evaluateBehavioralSegment(customerId, segment) async function that evaluates all rules for a customer and returns a boolean
• The function should handle the segment operator (how rules are combined)

3. db_recommendations.md — A markdown document listing database optimizations recommended when using this behavioral segment system at scale, with the specific index/approach for the events table.

Customer Scoring System for E-Commerce Analytics

Problem Description

GreenLeaf Commerce, an online plant and gardening retailer, has grown to over 50,000 customers over the past three years. Their marketing team currently sends the same promotional emails to all customers and is burning significant budget on customers who either just purchased last week or haven't purchased in over a year. The CTO has asked you to build a customer scoring and classification system that will allow the marketing team to target different groups of customers with appropriate messages.

The company uses PostgreSQL and their orders table has the following schema: customer_id, id (order id), created_at (timestamp), subtotal_cents (integer), status (string, e.g. 'completed', 'cancelled', 'refunded', 'pending'). They want to score every customer based on their purchase history and automatically categorize them so campaigns can be tailored by customer lifecycle stage. The scoring should work correctly even as the customer base grows or spending patterns shift over time.

The engineering team will schedule this scoring to run automatically, and they need a TypeScript function that reads the computed scores and assigns each customer to a named lifecycle segment. A CRM dashboard will display a summary of all segments with counts and average monetary scores, along with a recommended action per segment.

Output Specification

Produce the following files:

1. rfm_scoring.sql — A complete SQL query that computes per-customer RFM scores (recency, frequency, monetary) using the orders table. The query should output: customer_id, r_score, f_score, m_score, rfm_total, rfm_cell.

2. rfm_classifier.ts — A TypeScript module containing:

• A TypeScript type for all possible segment names
• A classifyRFMSegment(r, f, m) function that maps scores to a segment name
• A refreshRFMScores() async function that runs the scoring query and persists the results (assume a db object is available as shown in examples)
• A getSegmentSummary() function that returns segment counts, average monetary score, and recommended action per segment

3. SEGMENT_ACTIONS.md — A markdown table mapping each segment name to the recommended marketing action for that segment.

Marketing Platform Sync and Audience Management

Problem Description

PeakPulse Athletics, an activewear e-commerce brand, is scaling up their paid and owned marketing. Their marketing operations team needs to connect their customer segmentation system to two external platforms: Klaviyo (for lifecycle email campaigns) and Meta (for paid social advertising). Right now, the team manually exports CSVs and uploads them — a process that's error-prone and runs once a week at best.

The engineering team has a database with a customerSegmentMemberships table that stores which customers belong to which segments (with a segmentId and customerId), a customers table with fields: id, email, firstName, lastName, phone, and an orders table with customer_id, created_at, status ('completed', 'cancelled', 'refunded', 'pending'). Each marketing segment in Klaviyo corresponds to a Klaviyo List ID.

One critical requirement: the team wastes significant ad budget on customers who already purchased recently. The Meta export must handle privacy requirements correctly. The engineering lead also warned that when the team previously synced to Klaviyo, duplicate customer profiles appeared — this must be documented with a recommended fix.

The team wants two TypeScript modules implementing the sync logic, plus a runbook explaining the duplicate profile issue and how to resolve it.

Output Specification

Produce the following files:

1. klaviyo_sync.ts — A TypeScript module with a syncSegmentToKlaviyo(segmentId, klaviyoListId) function that:

• Fetches all customer IDs in the segment and their profile data
• Syncs the customers to the specified Klaviyo list
• Uses the correct Klaviyo API endpoint structure and authentication format
• Uses process.env.KLAVIYO_PRIVATE_KEY for the API key
• Includes logic to handle the case where a segment has 0 members (guard against empty syncs)

2. meta_suppression.ts — A TypeScript module with an exportSuppressionListForMeta() function that:

• Exports recent purchasers (last 30 days, completed orders only) as a suppression list
• Applies the required privacy transformation to email addresses before export
• Returns an array of objects suitable for Meta Custom Audiences

3. klaviyo_runbook.md — A markdown document explaining the duplicate profile problem that can occur when syncing to Klaviyo and the recommended approach to resolve it, including which field should be used as the primary key.