Article body
Full article
Consider a simulated question: “What is the 30-day repeat purchase rate for new customers acquired in Q2?” The business lead gets 40%, operations calculates 35%, and the data team reports 47.5%. This does not necessarily mean somebody made a calculation error. They may be answering three different questions: averaging monthly percentages, placing all customers in one denominator, or including only customers who have completed a 30-day observation period. A number has no meaning independent of its definition. For ChatBI, mapping a question to the right metric is only the beginning. Time boundaries, the entity being deduplicated, whether groups are mutually exclusive, and changes in row counts after joins all determine whether the result can be explained and checked.
Turn “repeat purchase rate” into a reproducible contract
A repeat purchase rate definition must fix at least five choices. First, what event identifies a new customer—for example, the first successful payment rather than registration, order placement, or shipment. Second, the enrollment interval in which the first order occurs. Third, what counts as a repeat purchase—for example, a second successfully paid order after the first—and whether refunds, cancellations, and test orders are excluded. Fourth, whether the observation window starts at the first order’s timestamp or a calendar-day boundary, and whether its right endpoint is inclusive. Fifth, the data cutoff and the treatment of customers whose first order was less than 30 days ago.
The following business pseudocode makes these choices explicit. It checks the definition; it is not executable HQL or SQL.
cohort = unique(customer_id), where first_paid_at falls within the enrollment interval
eligible = customers in cohort where first_paid_at + 30 days <= data_cutoff_at
repeaters = customers in eligible with a successfully paid order where
paid_at > first_paid_at and paid_at <= first_paid_at + 30 days
rate = unique_count(repeaters.customer_id) / unique_count(eligible.customer_id)If the eligible set is empty, display “insufficient observation time” or “no calculable result yet.” A zero denominator must not become a 0% repeat purchase rate. The definition deliberately separates the first order from repeat orders. Including the first order in the repeat-event set would classify every customer with a first order as a repeater. Counting all repeat orders in the numerator while counting new customers in the denominator also changes the unit. HENGSHI v6.2 Data Agent documentation recommends defining complex, reusable business calculations as metrics in advance, with descriptions that clarify time rules, filters, and easily confused concepts, rather than assembling essential calculations in a single conversation. Data Agent guide
One simulated dataset, three results
All numbers below are illustrative. Suppose the population is customers whose first successful payment occurred in Q2 2025, and the repeat event is another successful payment within 30 days of that first order. The data cutoff is July 15, 2025. April and May customers have completed observation. This example additionally assumes that all 80 June customers made their first payment between June 20 and June 30, so none has completed 30 days by the cutoff. This assumption serves only the example. Real data must assess maturity customer by customer, rather than excluding an entire month.
| First-order month | New customers | Customers observed to repurchase within 30 days by cutoff | Observed proportion at cutoff | Full 30-day observation? |
|---|---|---|---|---|
| April 2025 | 120 | 66 | 55% | Yes |
| May 2025 | 40 | 10 | 25% | Yes |
| June 2025 | 80 | 20 | 25% | No |
An equally weighted average of the three monthly percentages gives (55% + 25% + 25%) ÷ 3 = 35%. It answers “the average proportion for a typical month,” with equal weight for each month. Pooling all enrolled customers gives (66 + 10 + 20) ÷ (120 + 40 + 80) = 40%. It answers “the proportion of all Q2 new customers with a repeat purchase observed by the cutoff,” with equal weight for each customer, although June customers are still under observation.
If the question promises a “30-day repeat purchase rate,” customers with incomplete observation cannot share a denominator with fully observed customers. Under this example’s June first-order date assumption, only April and May customers qualify as eligible: (66 + 10) ÷ (120 + 40) = 47.5%. This is neither a simple average of monthly percentages nor a complete result for an immature cohort. It answers “the repeat purchase rate for Q2 new customers who have completed 30 days of observation as of July 15.” Dashboards, ChatBI answers, and metric descriptions should expose the data cutoff and cohort maturity rule; otherwise readers may mistake 40% for the final 30-day result.
“Weighting” has another often overlooked prerequisite. When aggregating by store, channel, or membership tier, deduplicated customer counts may be added across groups only if their customer sets are mutually exclusive. A customer who buys in two stores would be counted twice when store denominators are summed. The overall rate must deduplicate the global customer set again. The numerator must also come from that same enrolled set. Repeat events across stores cannot simply be added and divided by the deduplicated customer count.
Identify the customer before counting the order
Repeat purchase rate usually measures the proportion of new customers who repurchase. Its numerator should count unique customers with at least one qualifying repeat event. It is neither the number of repeat orders nor the number of repeat purchases per new customer. Suppose a small cohort contains three customers: A repurchases twice within the window, B once, and C never. There are two repeat customers, so the customer repeat purchase rate is 2 ÷ 3 = 66.7%. There are three repeat orders; divided by three new customers, that is one order per customer. Calling that ratio a 100% repeat purchase rate confuses order frequency with customer proportion.
The data team should make customer ID the counted entity and retain repeat order count as a separate diagnostic metric. Displaying both helps readers distinguish more customers starting to repurchase from a few customers ordering more frequently. If the business question concerns frequency, define “repeat orders per new customer” separately instead of borrowing the name “repeat purchase rate.” HENGSHI documentation lists advanced calculations related to repeat behavior and retention, but the appropriate calculation still depends on the business event, time window, and denominator definition. Advanced calculation functions
“Metric granularity” and row grain are different concepts
Troubleshooting often invokes “inconsistent granularity” without clarifying whether this means data rows or metric configuration. One row in an order-line table may represent a product line; one row in an order table represents an order; one row in a customer cohort table should represent a customer. This is row grain. It determines the entity at which deduplication and aggregation must occur. Counting directly from order lines would multiply customers according to the number of product lines.
HENGSHI’s “metric granularity” is a separate configuration layer: it centrally manages a business metric’s analysis dimensions, time axis, and qualifying conditions for metrics to reference. Changes propagate to referencing metrics. Current documentation also states that a granularity configuration supports only one dataset, so it cannot carry multi-table business metric configuration in this way. It makes configurations reusable, but it is neither a frozen historical snapshot nor automatic isolation between old and new definitions. Metric management: metric granularity
A repeat purchase metric therefore needs two separate records: whether calculation starts at customer, order, or order-line level; and which dimensions, time axis, and conditions its metric configuration references. The former prevents duplicate counting. The latter lets analysis entry points reuse the same business setting. Combining both into “calculate at customer granularity” makes later changes difficult to diagnose.
Six joined rows do not mean six customers
A one-to-many join expands rows on the other side. Suppose customer A has one cohort row, two customer-tag rows (“new member” and “high potential”), and one repeat order with three product lines. Joining the tables directly on customer or order keys can expand A into 2 × 3 = 6 rows. Counting rows or non-distinct customer_id values inflates both numerator and denominator. If both grow by the same factor, the percentage may look normal; different filters can introduce different join paths and suddenly change the rate.
HENGSHI’s data model configures join conditions, join types, cardinality, and activation mechanisms as parts of a relationship. Official documentation explains that the “many” side can expand data on the other side, and cardinality affects measure calculation paths. Modelers must check key uniqueness, fact-table and dimension-table grain, and whether calculations need to be separated. This mechanism does not replace the business definition of the repeat-customer set. Data model management · Relationship descriptions
- Enrolled set: Compare distinct customer_id counts before and after joins. If adding tags or product tables changes the population, check keys, filters, and whether the cohort should be fixed before joining.
- Repeat numerator: Display distinct repeat customers beside repeat order count. If they have been treated as one metric, deduplicate by customer_id and report order frequency separately.
- Joined rows: Check each customer’s row-count distribution before and after joins. If multiple rows participate in aggregation, aggregate facts first or adjust the relationship and calculation layer.
- Time maturity: Compare eligible customers with all new customers. Exclude immature cohorts when a complete window is required, or explicitly label results as observed by the cutoff.
Make ChatBI answers verifiable
ChatBI should not have to guess a team’s repeat purchase definition. HENGSHI Data Agent documentation states that identical inputs do not guarantee identical responses. When question answering is ineffective, the official FAQ recommends improving dataset, field, and metric names and descriptions, knowledge management, and synonyms before adding prompts. Data preparation for Data Agent · Data Agent and ChatBI FAQ
After defining a “30-day new-customer repeat purchase rate” metric, document the first-order event, repeat event, customer key, window endpoints, refund treatment, data cutoff, immature-cohort rule, and whether customers can be counted in more than one store. When a ChatBI user asks “break it down by store,” clarify whether they want independent store cohorts, a weighted result based on mutually exclusive attribution, or an attribution view of globally deduplicated customers. The questions sound similar, but their denominators differ.
Before rollout, use a small manually calculable dataset for regression checks. List eligible and repeating customers using the pseudocode, then apply the same filters in a chart or ChatBI. Add a one-to-many table and check expected changes in customer and row counts. Finally move the cutoff and verify that immature-cohort states and labels change accordingly. These checks do not promise that AI will never misunderstand a question. They put definitions, models, and results on one checklist.
HENGSHI’s view: Repeat purchase rate is a calculation contract specifying customer sets, time windows, observation cutoffs, and join paths, rather than merely a term for a model to recognize. Establish reproducible metrics from that contract and let ChatBI use them in clear context. Teams can then explain why two answers differ and decide which answers the current business question.