Customer Analytics

Customer Analytics

Overview

Customer analytics transforms raw order data into actionable insights about purchase patterns, lifecycle stages, and churn risk. The core analyses — RFM scoring, cohort retention, purchase frequency, and churn prediction — reveal which customers are loyal, which are at risk, and which channels produce the best long-term customers.

This skill guides you through running these analyses using your platform's built-in tools and dedicated analytics apps, with data warehouse approaches for stores that need deeper segmentation.

When to Use This Skill

• When the marketing team needs data-driven segments beyond simple demographic filters
• When calculating at-risk customer counts for quarterly business reviews
• When measuring the impact of loyalty programs on purchase frequency
• When identifying the acquisition channels that produce the highest-LTV customers
• When preparing customer health dashboards for account management or VIP programs
• When building cohort retention analysis to understand customer lifetime value trends

Core Instructions

Step 1: Choose the right tool for your platform

Step 2: Set up customer segmentation on your platform

---

Shopify

Using Shopify's built-in customer segments (all plans):

1. Go to Customers → Segments 2. Shopify provides pre-built segments including:

• Abandoned checkout in the last 30 days
• Customers who have purchased more than X times
• Customers who haven't purchased in 90 days (at-risk segment)
• High-spend customers (based on total spend threshold)

3. Create custom segments using the query editor with filters like:

• number_of_orders >= 3 (loyal customers)
• days_since_last_order > 90 (churn risk)
• total_spent > 500 (high-value)

4. Export segments to CSV or sync directly to Klaviyo for email campaigns

Using Klaviyo for RFM segmentation on Shopify:

1. Install Klaviyo from the Shopify App Store 2. Klaviyo automatically syncs all historical and new Shopify order data 3. Go to Segments → Create Segment and build RFM-style segments using:

• Recency: "Has placed an order in the last X days"
• Frequency: "Number of orders is greater than X"
• Monetary: "Total amount spent is greater than $X"

4. Pre-built segment examples:

• Champions: Ordered in last 30 days + 3+ orders + $200+ lifetime spend
• At Risk: No order in 90–180 days + previously placed 2+ orders
• Lost: No order in 180+ days

5. Use these segments to trigger flows in Klaviyo: win-back campaigns for at-risk, VIP rewards for champions

Using Lifetimely for cohort LTV on Shopify:

1. Install Lifetimely from the Shopify App Store 2. Go to Lifetimely → Cohorts to see a month-by-month retention matrix: what percentage of customers from each acquisition cohort are still buying at months 1, 3, 6, 12 3. Go to Lifetimely → Channels to compare 12-month LTV by acquisition source (Google, Meta, organic, email) 4. Go to Lifetimely → Customer Segments to see RFM distribution and predicted CLV per customer

---

WooCommerce

Using Metorik:

1. Connect Metorik to your WooCommerce store via API 2. Go to Metorik → Customers to browse all customers with filters:

• Last order date (identify churned/at-risk)
• Total spent (identify high-value)
• Order count (identify one-time vs. repeat buyers)

3. Go to Metorik → Reports → Customer Cohorts to see retention by monthly acquisition cohort 4. Go to Metorik → Segments to create saved customer segments (equivalent to RFM groups); export segments as CSVs for Klaviyo or Mailchimp

Using Klaviyo for WooCommerce:

1. Install the Klaviyo for WooCommerce plugin 2. Klaviyo syncs WooCommerce customers and orders, enabling the same RFM segments described for Shopify above 3. Go to Klaviyo → Analytics → Cohort Analysis to see retention curves and predicted CLV by acquisition date

---

BigCommerce

1. BigCommerce Customer Groups: Go to Customers → Customer Groups — create groups based on purchase history, spend thresholds, and geographic criteria 2. Install Glew.io from the BigCommerce App Marketplace — provides cohort retention analysis, RFM scoring, and CLV by acquisition channel 3. Install Klaviyo for BigCommerce for behavioral email segmentation and lifecycle automation

---

Step 3: Analyze purchase frequency and retention

Purchase frequency distribution (using any platform's export):

Export your customer order data to a CSV or Google Sheet and calculate:

• What % of customers have placed exactly 1 order?
• What % have placed 2–3 orders?
• What % have placed 4+ orders?

Industry benchmarks:

• Typical DTC brand: 60–70% of customers are one-time buyers
• Healthy subscription or consumable brand: 40–50% of customers reorder within 90 days
• Your second-purchase rate (% of first-time buyers who place a second order within 90 days) is the single most important leading indicator of long-term CLV

Cohort retention analysis:

A cohort retention grid shows what percentage of customers acquired in month X are still buying at months 1, 3, 6, 12:

• Shopify + Lifetimely: Available natively in the Cohorts view
• Klaviyo: Available under Analytics → Cohort Analysis
• Metorik: Available under Reports → Cohorts
• Manual (any platform): Export all orders to Google Sheets; create a pivot table with acquisition month as rows and "months since first order" as columns

What good retention looks like:

Step 4: Identify at-risk customers and act

At-risk customer identification:

The simplest at-risk definition: customers who previously ordered multiple times but have not ordered in longer than their typical interval.

• Shopify Segments: number_of_orders > 1 AND days_since_last_order > 90
• Klaviyo: Create a "Winback" segment: "Has placed more than 1 order" AND "Has not placed an order in the last 90 days"
• Metorik: Use the "Customers at risk of churning" pre-built filter

Action by segment:

Best Practices

• Run RFM scoring monthly — customer order history changes continuously; stale segments lead to wrong targeting
• Track second-purchase rate as a leading indicator — the conversion from one-time to repeat buyer is the highest-leverage retention metric; it predicts CLV far in advance of any LTV model
• Segment CLV by acquisition channel — customers from organic search, paid social, and email/SMS referrals often have dramatically different LTVs; measure them separately to inform budget allocation
• Build alerts for segment migration — when the "at risk" segment grows week over week, it signals a retention problem needing immediate action; set up alerts in Klaviyo or Lifetimely
• Flag seasonal buyers separately — customers who only buy in Q4 should not be marked as churned in Q2; apply a seasonal buyer tag before running churn analysis

Common Pitfalls

Related Skills

• @customer-segmentation
• @attribution-modeling
• @sales-reporting-dashboard
• @ab-testing-ecommerce
• @unit-economics-tracking

Marketing Channel ROI and Cohort Retention Report

Problem Description

Trove Market, an online home goods retailer, is heading into its annual marketing budget review. The CMO wants hard data to decide where to invest next year: which acquisition channels are actually bringing in customers who stick around and spend, not just one-time bargain hunters? The analytics team is being asked to produce two analyses: (1) a channel quality comparison showing revenue contribution per acquired customer, and (2) a cohort retention matrix showing what percentage of each month's new customers returned to buy in subsequent months.

The database is PostgreSQL and has four tables: customers (id, email, first_name, created_at), orders (id, customer_id, created_at, subtotal_cents, status), order_attribution (order_id, source), and customer_rfm_scores (for materialized scores). Not all orders are valid — cancelled and refunded orders should be excluded. Some customers came in via paid channels, others organically, and some with no tracking data at all.

The team has been burned before by analysis that mixed together channels from very different time periods — a channel that looked great three years ago may now be bringing in low-quality customers. They also want to understand which specific months are showing weak long-term retention so they can investigate what was happening with those cohorts.

Output Specification

Produce two SQL files:

`channel_quality.sql` — Queries CLV by acquisition channel for recently acquired customers, including:

• Acquisition source (with a fallback label for untracked customers)
• Number of customers acquired per channel
• Average and median 12-month revenue per customer
• Average order count per customer
• Repeat purchase rate (percentage who ordered more than once)

`cohort_retention.sql` — A monthly cohort retention matrix showing:

• The cohort month (month of first purchase)
• Cohort size
• How many months after first purchase each data point represents
• Count of retained customers and retention percentage at each period
• Only includes cohorts that have had sufficient time to accumulate data (exclude cohorts too recent to have meaningful retention at the period being measured)

Also produce `analysis_notes.md` documenting:

• What time window was used for the channel analysis and why
• Whether first-touch or last-touch attribution is used, labeled clearly
• What metric the team should track as the leading indicator of customer lifetime value

Customer Churn Risk Scoring Service

Problem Description

Pulse Commerce, a subscription-to-repurchase health supplements brand, has been losing high-value customers without warning. The customer success team only finds out a customer has churned after they've already gone 3 months without a purchase, at which point win-back campaigns have a very low success rate. The VP of Customer Success wants a proactive system that flags customers before they churn so the CS team can intervene with targeted offers.

The engineering team uses TypeScript and has a database abstraction layer (db) already set up. Order records have a createdAt timestamp and a status field (orders can be 'cancelled', 'refunded', or valid). The team's data scientists have noted that churn behavior is fundamentally different for first-time buyers (who may simply never return) versus repeat customers (whose churn can be detected by looking at whether they've gone unusually long past their normal repurchase interval).

The CS team also maintains a customerChurnScores table in the database where scores can be persisted for lookup, and they want to surface the top at-risk high-value customers to the CS team daily. The business has a mix of customers who only buy seasonally (e.g., gift buyers) and regular repeat purchasers, and the system should account for this to avoid false alarms.

Output Specification

Produce a TypeScript file churn-scoring.ts that implements: 1. A function scoreCustomerChurnRisk(customerId: string) that computes a churn risk score and category for a single customer 2. A function getHighValueAtRiskCustomers(limit?: number) that retrieves the top at-risk customers ordered by lifetime value

Also produce churn-design.md that explains:

• The scoring formula used for first-time buyers vs repeat buyers
• How the system avoids false positives for seasonal purchasers
• The recommended validation process to ensure the model stays accurate over time
• What action the system recommends based on lifetime value tier

Customer Segmentation for Loyalty Program Targeting

Problem Description

Finova, a mid-size direct-to-consumer fashion retailer, is launching a revamped loyalty program next quarter. The marketing team currently treats all customers the same — everyone gets the same newsletter, the same discounts, and the same reactivation emails. The head of retention has seen competitors use data-driven segmentation to dramatically improve campaign ROI and wants Finova to do the same.

The data team has a PostgreSQL database with two relevant tables: orders (columns: id, customer_id, created_at, subtotal_cents, status) and customers (columns: id, email, first_name). Not all orders should count toward analytics — some were cancelled or refunded. The team wants a SQL-based scoring pipeline that keeps segments up to date so marketing always has fresh data to target.

The analytics lead wants a self-contained SQL file that can be scheduled as a cron job. It should score every customer on how recently they purchased, how often they purchase, and how much they spend — then classify each into a named segment that marketing can act on directly. The results should reference each customer's email and first name for easy export to the marketing automation tool.

Output Specification

Produce a file rfm_pipeline.sql containing the complete SQL query that:

• Computes recency, frequency, and monetary scores for each customer
• Assigns each customer a percentile-based score from 1–5 on each dimension
• Classifies each customer into a named segment based on their scores
• Returns customer contact info alongside analytics fields

Also produce a short notes.md explaining:

• How the query handles problematic order statuses
• What the segment names represent and when they are assigned
• A recommendation for the appropriate scheduling cadence for this query and why it matters