customer-analytics

Use NTILE(5) window functions over recency days (descending), frequency, and monetary value to assign 1-5 scores per dimension, then map score combinations to named segments like champions, at_risk, and lost. Exclude cancelled and refunded orders before computing any metrics.

Shopify's built-in customer segments handle basic filters like order count and days since last order. Klaviyo adds RFM-style behavioral segments and flows, while Lifetimely or Triple Whale provide true cohort LTV and retention curves by acquisition channel.

Compute a z-score of how many standard deviations the customer is past their average purchase interval, then apply a sigmoid function to produce a 0-1 churn score. Single-purchase customers use a simpler linear formula based on days since their order.

Yes, Metorik connects to WooCommerce via API and provides customer cohort retention reports, RFM segmentation, and churn identification. Klaviyo for WooCommerce also offers cohort analysis under its Analytics section.

The query is likely including cohorts that have not yet had six months of data. Filter with a HAVING clause or date condition to exclude cohorts acquired too recently to measure retention at the period being reported.

Apply a seasonal purchase pattern flag before running churn analysis so customers who only buy in specific periods, like Q4 gift buyers, are not marked as churned during their off-season. This prevents false alarms in win-back campaigns.