Customer sequence and life

Once orders are grouped into a customer, CCC numbers them. The number answers "was this their first order, their second, their tenth", which is what every new-versus-returning metric rests on.

How the numbering works

Orders within a customer are numbered by transaction date, then sales order id as a tiebreak.

That basis matters: it is trandate, not createddate. Transaction listing screens default to a creation-date window, so a screen and a sequence can legitimately disagree about which order came first. See dates, currency and signs.

Which orders do not count

Cancelled and closed orders are excluded from the numbering. Both statuses mean the order did not happen: C for cancelled, H for closed, where closed means written off rather than shipped.

A surface that means "cancelled" uses both codes. Treating C alone as cancelled silently counts every written-off order as real.

Those orders keep their membership in the customer group. After a completed reconcile they carry zero for every sequence, life and new flag. A newly inserted row also starts at zero before the numbering pass finishes, so check reconcile completion before interpreting a zero as cancelled.

A metric that counts rows in customersalesorders rather than numbered orders therefore counts cancelled and closed orders as real. Filter on customerordersequence > 0.

Zero and NULL are different here. After numbering finishes, a zero sequence means an order CCC deliberately did not count. A NULL in the comparison table means the old pipeline never numbered that order at all. Reading either as "no value" produces a wrong count.

Lives

A customer can lapse and come back, and CCC detects that as a new life. Sequence is numbered separately within each life, while the overall customer order sequence continues increasing.

An uncounted order does not bridge a gap. A cancelled order placed in the middle of a dormant stretch does not keep the life alive, because it did not happen.

Two lives run in parallel, on a one year and a two year gap. A new life begins when an order falls more than that long after the previous one. The schema names them explicitly: customerlife_1y, customerlifesequence_1y and newlife_1y, with a _2y set beside them.

So a customer can be new under one definition and returning under the other. Three columns answer "is this new", and they answer differently:

A figure quoted as "new customers" without naming the column is ambiguous, and the three do not agree.

A life is a run of orders with no gap strictly greater than the threshold since the previous counted order, and the life sequence restarts at 1 whenever a gap breaks the run. Both readings are stored because both are wanted and neither is derivable from the other.

Worked example

On row three a 19 month gap breaks the one year run and not the two year one, so the two pairs report different lives for the same order.

Because the test is strictly greater, an order exactly one year later continues the run. Leap years follow the calendar, so a year after 2020-02-29 is 2021-02-28. On every row customerlife_2y is less than or equal to customerlife_1y.

Asking for the three states

Substitute _2y throughout for the two year reading. The customerordersequence > 0 in the retained test is what excludes the uncounted orders.

Reading a new-customer figure

Before quoting one, settle three things: which life definition, which date basis, and whether uncounted orders are excluded from the denominator as well as the numerator. Two people can compute "new customers last month" from the same tables and disagree entirely.

Boundary examples

These examples are synthetic and concern counted orders belonging to one customer.

newlife_1y = 1 includes the first-ever order as well as reactivations. To count reactivations, require customerlife_1y > 1 too; use the analogous pair for two-year lives. Overall customerordersequence does not restart when a life starts. Only customerlifesequence_1y or _2y restarts for its respective life.

Related

• Customer matching, which produces the grouping
• Customer merges and splits
• Customers
• Dates, currency and signs

Evidence

Numbering basis from app/models/customers.php:1190-1192, ROW_NUMBER() OVER (PARTITION BY n.customer_id, n.life1 ORDER BY n.trandate ASC, n.salesorder_id ASC), with a second life partition alongside it. Uncounted statuses from customers.php::$uncountedstatuses and the exclusion at customers.php:1221. Status meanings from config/reference/ns_transactionstatusses.json. Life accumulation from the windowed sum at customers.php:1200. Basis: code-checked against the current implementation.

The two windows are INTERVAL 1 YEAR and INTERVAL 2 YEAR in app/models/customers.php, which is what customerlife_1y and customerlife_2y are named after. They are deliberately not configurable: changing a window would make the column name lie.