Investigate customer history

Goal: explain why a set of orders is one customer, or why an order carries the sequence number it does.

Prerequisites: customer matching and sequence and life.

Steps

1. Establish what connects the orders. Grouping is transitive: A connects to B, B to C, so all three are one customer even if A and C share nothing. Look for the chain, not the pair.

2. Check the sequence basis. Numbering is by transaction date, then sales order id. Screens filter on creation date. The two can disagree about which order came first, and both are right.

3. Account for uncounted orders. Cancelled and closed orders belong to the customer but carry a zero sequence, and do not bridge a gap between lives. Both statuses mean it did not happen.

4. Check which life. Two life definitions run in parallel with different windows. A customer can be new under one and returning under the other, so say which you mean.

5. Compare with the old pipeline. Join CCC's sequence to the imported one on the sales order. CCC lower usually means history CCC does not hold; CCC higher means CCC grouped more orders together. NULL there means the old pipeline never numbered it, which is not CCC's zero.

Inspect an order's current assignment

There is no dedicated customer editor in CCC. An authorised reader can use a database query or the export builder. Replace the synthetic id below with the order being investigated:

SELECT so.id, so.trandate, so.createddate, so.salesorderstatus_id,
       cso.customer_id, cso.customerordersequence,
       cso.customerlife_1y, cso.customerlifesequence_1y,
       cso.customerlife_2y, cso.customerlifesequence_2y
FROM salesorders so
LEFT JOIN customersalesorders cso ON cso.salesorder_id = so.id
WHERE so.id = 123456;

A NULL assignment can mean reconciliation has not processed the order. Once you have its current customer id, inspect all its assigned orders in transaction-date/id order. Check the fingerprint families and exclusions for the shared paths. Work with private records only in the authorised session; published examples remain synthetic.

Do not change customersalesorders.customer_id by hand to make the result look right: the next reconcile reconstructs it from the source keys. Correct the source or a justified exclusion, then verify the rebuilt grouping with reconcile customers.

Expected result

An explanation of the grouping and the sequence that survives someone disagreeing with it.

Troubleshooting

Before you change a grouping

Historic figures will move. Sequences renumber, lives recompute, past new-customer counts change. Tell whoever consumes those numbers before you run it.

Related

• Customers
• Customer merges and splits
• Reconcile customers

Evidence

As cited in the linked concept documents. Basis: code-checked at 30f5313. Examples in this guide are synthetic, because the collection carries no live customer data.