Customer Register Template

About this template

When to use it

One trading customer per row — company, contact, tier, last order, annual revenue, and how long you are allowed to keep their details. Three sheets — Dashboard, Input data, Usage guide — and typing happens only on Input data. The subtitle in B3 describes the template rather than your business, so replace or delete it first.

What the dashboard holds

Everything on the Dashboard is live except one block. Key metrics in B7:E7 — rows entered, Annual revenue total, Annual revenue average, Annual revenue max — read column F of Input data. Summary by customer tier in B12:F15 groups that same column by the tier in column D. The New customers registered per quarter table (B19:C22) is typed in by hand and never moves on its own.

Filling order

1. Rows 2–21 of Input data take Customer code, Company / Contact, Phone, Customer tier, Last order date, Annual revenue, Consent scope, Retention until (destroy by) (A–H). Eight sample rows ship, C-2026-001 to C-2026-008, and the formula ranges run to row 21.
2. Keep the customer code unique. It is the only thing in the sheet that catches a duplicate registration.
3. Column F takes plain numbers (note 2). The shipped figures are supply values in KRW, VAT excluded. A unit typed into the cell turns it into text and it drops out of the total.
4. The tier labels on the Dashboard are Key account, Standard, New, Dormant (B12:B15). The formulas use those cells as their criterion, so retyping a label re-targets the row — but it has to match column D character for character.
5. Replace the four quarterly counts (Q1–Q4: 24 · 31 · 27 · 38) with your own.

Why the retention column is there

This register holds a named contact and a phone number, and that is personal data. Korean privacy practice lets you keep it only while the purpose lasts, and what goes wrong in practice is that nobody writes down how long that was agreed to be. So column G records what the person consented to and column H the date you will destroy it. With both on the same row you can tell from the register alone when the row has to go. There is deliberately no resident registration number field, and no other identifying number belongs here — the customer code is enough to tell accounts apart. Once the date in column H passes, destroy the record, delete the row, and keep only the fact of destruction in a separate disposal log (notes 6 and 7).

How the formulas calculate

• =COUNT(‘Input data’!$F$2:$F$21) counts numeric revenue figures only.
• Total, average and maximum run over the same range; the last two are wrapped in IFERROR and return 0 while the sheet is empty.
• The tier block is =COUNTIF(‘Input data’!$D$2:$D$21,$B12), =SUMIF(…) and =IFERROR(AVERAGEIF(‘Input data’!$D$2:$D$21,$B12,’Input data’!$F$2:$F$21),0), with a share of =IFERROR(C12/COUNT(‘Input data’!$F$2:$F$21),0).
• Under the quarterly table, =SUM(C19:C22) and =AVERAGE(C19:C22) — 120 and 30 as shipped.
• The ranges stop at row 21, so a 21st customer is not counted. Copy the last row down and widen $F$21 and $D$21.
• Nothing groups by Last order date. The shipped dates are ISO text rather than true date values, so a dormancy flag needs DATEVALUE around them: =IF(DATEVALUE(E2)<TODAY()-180,”Dormant”,D2) in a spare column, or retype column E as real dates first.

Examples

• Row 2 — C-2026-001 · Cheongrim Industrial / Kim ○○, Manager · Key account · 2026-06-18 · 184,000,000 · Name · Phone · 2029-06-18.
• The eight shipped rows total 648,600,000, so Annual revenue average reads 81,075,000 and the max reads 184,000,000.
• Two of the eight are Key account, so that tier shows a share of 25.0% and a total of 335,800,000.
• The one Dormant row, C-2026-006, is held for a year after its last order (2026-09-05) while the active rows run three years out.

Common mistakes

• Writing a tier as “VIP” or “Key” instead of “Key account”. It counts in no row and the tier stays at zero.
• Deleting rows for customers who stopped buying. Move them to Dormant instead, so the history survives if they come back.
• Leaving rows in place after the date in column H. The register is then holding personal data past its agreed life.
• Adding a resident registration number, passport number or card number column because it “makes matching easier”.