User Repurchase Interval Days Pivot Table

I. Calculator Introduction

The User Repurchase Interval Days Pivot Table is a specialized tool for grouping users by registration month and analyzing the repurchase interval rhythm of users with orders on registration day. By calculating the median interval days between 1st-2nd, 2nd-3rd, ..., 6th-7th purchases for users in different registration periods, this tool forms a pivot table to help you understand the repurchase rhythm of new customers in different periods, providing data support for developing new customer activation and repurchase strategies.

Core Features

• Repurchase Interval Pivot Table: Grouped by registration month, displaying median interval days for users in different registration periods across repurchase frequency ranges (1st-2nd, 2nd-3rd, ..., 6th-7th)
• Total User Count: Statistics on total users with orders on registration day per registration month
• Repurchase User Count: Statistics on total users with at least 2 purchases per registration month (deduplicated)
• Median Calculation: Uses median (not average) to avoid extreme value impact, more accurately reflecting typical repurchase interval levels

Use Cases

• Evaluate repurchase rhythm differences of new customers in different registration periods, identify high-quality acquisition channels
• Identify patterns in repurchase interval changes (e.g., which purchase interval significantly lengthens), develop targeted operation strategies
• Compare repurchase rhythm changes of newly registered users before and after marketing activities or product updates
• Observe trends in user repurchase intervals as purchase frequency increases, providing data basis for repurchase activation strategies

Applicable Clients

This calculator is suitable for all industries and scenarios that need to analyze user repurchase intervals and have user registration dates and order dates, especially catering, e-commerce, retail, lifestyle services, subscription services, and other B2C businesses.

Prerequisites: Your business can provide user ID, order date, and registration date data; and can distinguish whether users have orders on registration day (this tool only counts users with orders on registration day).

---

II. Algorithm Introduction

2.1 Core Concepts

Grouping Label: Registration Month

• Cohort Grouping Label: Registration Month (YYYY-MM). Users belong to the month of their registration date, forming cohorts.
• Only Count Users with Orders on Registration Day: If a user has no order records on registration day, that user is excluded from analysis.

Repurchase Intervals

• 1st-2nd Purchase Interval: Days between user's 1st and 2nd purchase dates; similarly for 2nd-3rd, ..., 6th-7th.
• Deduplication: Multiple orders by the same user on the same day count as one purchase; intervals calculated using deduplicated order date sets.

Median and User Scope

• Median: Sort interval days and take the middle value, unaffected by extreme values.
• Total Users: Total users with orders on registration day (grouped by registration month).
• Repurchase Users: Total users with at least 2 purchases (grouped by registration month, deduplicated).

2.2 Calculation Logic

The system identifies users with orders on registration day, deduplicates order dates and sorts by date, calculates days between consecutive purchases (1st-2nd, 2nd-3rd, ..., 6th-7th), groups by registration month and calculates median for each cohort×interval type; and counts total users and repurchase users.

2.3 Data Filtering Rules

• Records missing user ID, registration date, or order date are excluded; records with order date earlier than registration date are excluded.
• Only count users with orders on registration day; multiple orders by same user on same day count as one purchase.

---

III. Usage Instructions and Notes

3.1 Data Preparation

Required fields: User ID (user_id), Order Date (order_date), Registration Date (register_date). Supports CSV, Excel; UTF-8 encoding recommended.

3.2 Result Interpretation

Pivot table: rows=registration months, columns=total users, repurchase users, 1st-2nd (days) to 6th-7th (days); cells=median interval days for that cohort×interval. Repurchase users ≤ Total users. As purchase frequency increases, interval days generally increase; compare same interval type across cohorts to assess repurchase rhythm differences.

3.3 Important Notes

• Only count users with orders on registration day; intervals calculated after deduplicating order dates.
• Uses median not average; if a cohort has no data for an interval type, the corresponding cell is empty (—).

3.4 Frequently Asked Questions

Q: Why must users have "orders on registration day"? To define a clear cohort starting point, ensuring all cohorts compare repurchase rhythm from the same starting point.

Q: Difference between Total Users and Repurchase Users? Total Users=users with orders on registration day (including those with only 1 purchase); Repurchase Users=users with at least 2 purchases, so Total Users ≥ Repurchase Users.

Q: Why use median? Median is unaffected by extreme values, better reflecting typical user repurchase interval levels.

---

IV. Summary

The User Repurchase Interval Days Pivot Table helps you analyze repurchase rhythm of new customers in different periods through median repurchase intervals grouped by registration month. Proper use can quickly identify repurchase interval differences, discover change patterns, and optimize acquisition and repurchase strategies. Contact system administrator for questions.