Customer Database TemplateThe fields, statuses and formulas that keep it clean
A structured spreadsheet can hold your whole customer list for a while. Here is how to build one in Excel or Google Sheets, keep it GDPR safe, and the moment it should become real software.
CRM··6 min read
Key takeaways
A unique customer ID is the one field most templates skip, and the one that makes dedup, joins and reporting reliable
11 core fields cover the whole record: ID, company, contact, email, phone, address, status, owner, value, last contact and consent date
Five status values (Lead, Active, At risk, Churned, Lost) plus COUNTIF and SUMIF turn a flat list into a live report
A spreadsheet stays GDPR safe only with a lawful basis, access control and a retention routine, and stops scaling around 500 to 1,000 records
Definition
What is a customer database template?
A customer database template is an Excel or Google Sheets file with one row per customer and columns for a unique ID, contact details, status, owner and value. It gives a small team a single, structured list of who their customers are and how to reach them, with zero software cost, until the data outgrows it.
It is the entry-level version of a database, just rendered in rows and columns instead of tables and keys. Most businesses build one before they ever pay for tooling, and for good reason: it is free, familiar and instantly editable. The discipline that keeps it useful is the same discipline behind any real customer relationship management system: one record per customer, clean fields and a single source of truth.
The difference between a database and a pile of contacts is structure. According to Vonsel internal data (2026), a typical paying team works across roughly 14 industries and dozens of cities in its first months, so without a unique ID and consistent fields the list fragments fast, long before volume becomes the problem.
The fields
The 11 fields every customer database needs
Field
What it holds
Why it matters
Customer ID
Unique code per record
The key for dedup, joins and clean lookups
Company
Business name
The account you actually serve
Contact
Main person
Who you speak to and address
Email / Phone
Verified contact details
The reason the database exists
Address
City, region, country
Powers segmentation and local sales
Status
Lead to Churned dropdown
Drives every report formula
Owner
Account manager
Stops customers falling between people
Lifetime value
Total revenue to date
Ranks who matters most
Last contact + consent date
When, and the GDPR basis
Your activity and compliance anchor
The field people skip is the first one. Without a unique ID you end up matching on company names, which change, repeat and get misspelled, and your duplicates multiply. Add the ID column first and never reuse a number, the same principle that separates a real database from a flat list and the foundation of any customer database software.
Skip the blank template, start with real customers
Search Maps, collect published website contacts and organise your prospects in the CRM. Free extraction covers 200 businesses a month; AI and export depend on your plan.
Keep statuses simple. Every customer should sit in one, and only one, status at a time so your COUNTIF math always adds up to your total:
Lead›Active›At risk›Churned/Lost
The point of a status column is triage: how many accounts are At risk this month, how many Churned last quarter, who is still just a Lead. If you also track open deals, keep that in a separate sheet using the stages from our lead tracking spreadsheet template, and resist inventing ten micro-statuses: the more granular the list, the less reliable the data entry.
Build it
How to build it, step by step
You can build a working customer database in under 15 minutes. These six steps map one to one to the HowTo above:
1
Define the core fields
One row per customer. Add ID, company, contact, email, phone, address, status, owner, lifetime value, last contact date and consent date.
2
Add a unique customer ID
Number every record (1001, 1002, and so on) and never reuse a value. This single column is what makes deduplication and lookups reliable later.
3
Set status values with a dropdown
Turn status into a Data Validation dropdown. Free-typing "Active" and "active" breaks every formula; a dropdown keeps values clean and filterable.
4
Add reporting formulas
Count customers per status, total lifetime value and flag accounts gone quiet. The formulas are in the table below; copy them straight in.
5
Add GDPR fields and access control
Record a lawful basis, consent date and source for each contact, restrict who can edit, and store only the personal data you genuinely need.
6
Run a monthly cleanup
Each month, dedupe, update statuses, delete records past their retention period and back up the file. The data is only as accurate as your last cleanup.
The formulas
5 formulas that turn a list into a report
These work identically in Excel and Google Sheets. Assume your status column is G, lifetime value is H and last contact date is I:
Goal
Formula
Customers in a status
=COUNTIF(G:G,"Active")
Total lifetime value
=SUMIF(G:G,"Active",H:H)
Churn rate
=COUNTIF(G:G,"Churned")/COUNTA(G2:G9999)
Gone quiet (90 days)
=COUNTIF(I:I,"<"&(TODAY()-90))
Find duplicates
=COUNTIF(A:A,A2)>1
Then add one conditional formatting rule: highlight any row where the last contact date is more than 90 days ago. Neglected accounts now turn amber on their own.
11
core fields to describe any customer end to end
€20M
maximum GDPR fine, or 4% of global turnover, whichever is higher (EU regulation)
500-1k
records before a spreadsheet starts costing more than it saves
A customer database template is free to create and expensive to trust. The cost is not the file, it is the duplicate that emailed the same client twice, the consent you cannot prove, and the copy a former employee still has on a laptop. Structure and access control are what turn a list into an asset.
Compliance
Keeping it GDPR safe (the parts that matter)
A spreadsheet can be perfectly compliant, the risk is human, not technical. Under the EU General Data Protection Regulation, you hold personal data, so four habits matter most:
Lawful basis and consent
Record why you hold each contact (consent, contract, legitimate interest) and the date. A consent column is your proof if anyone ever asks.
Data minimisation
Store only what you use. A birthday or ID number you never act on is extra risk sitting in a file with no upside.
Access control
Share with named people, not a public link. Uncontrolled copies are the single biggest weakness of a spreadsheet versus a database.
Retention and erasure
Set a retention period and honour deletion requests. A spreadsheet has no audit trail, so erasure has to be a deliberate monthly habit.
For where these ceilings start to bite at scale, our guide on the CRM Excel template and its limits walks through each one with examples.
A spreadsheet is a brilliant place to start your customer database. It is a risky place to scale it.
How Vonsel helps
From a template to a real database
Search Google Maps and press Start. Vonsel gathers the business results as you explore the map, going beyond the pins you can see at a glance. Pause, continue and save your search when you are ready. Google Maps does not provide business emails. Vonsel visits the available business websites to find published emails, additional phone numbers and links to up to 118 supported social networks and other platforms. What it finds depends on what each website publishes. Keep businesses on a map, organise them by stage, edit their details and use a Kanban board, calendar, notes, actions, tags and custom fields. Basic holds 400, Pro 2,000 and Scale 10,000.
In short:
Start with a unique ID, the 11 fields, five statuses and five formulas above. It is free and fast.
Keep it GDPR safe with a lawful basis, access control and a monthly retention cleanup.
Move to software once you pass roughly 500 to 1,000 records or add a second editor.
Find local businesses with Vonsel
Search Maps, collect published website contacts and organise your prospects in the CRM. Free extraction covers 200 businesses a month; AI and export depend on your plan.
If you would rather fill the sheet from Google Maps than type it by hand, watch the video tutorial to see a full extraction from search to export. The plans and what each one includes are on the pricing page.
A customer database template is an Excel or Google Sheets file with one row per customer and columns for a unique ID, contact details, status, owner and value. It gives a small team a single, structured list of who their customers are and how to reach them without buying software.
What fields should a customer database have?
The core fields are a unique customer ID, company, contact name, email, phone, address, status, owner, lifetime value, last contact date and consent date. Optional fields include industry, city, acquisition source and notes for richer segmentation.
How do I make a customer database in Excel?
Put one customer per row, add a unique ID column, turn it into a Table, make status a Data Validation dropdown, and use COUNTIF and SUMIF for reporting. Save it in a shared, access controlled location and back it up regularly.
Is a spreadsheet GDPR compliant for customer data?
A spreadsheet can be compliant if you record a lawful basis and consent date, limit who can open it, store only the data you need and delete records past their retention period. The risk is human: uncontrolled copies, no audit trail and easy oversharing.
What status values should I use?
A clear, reliable set is Lead, Active, At risk, Churned and Lost. Each customer sits in exactly one status so you can count them with a single COUNTIF and quickly see how many accounts need attention this month.
When should I move from a template to customer database software?
Move when more than one person edits the file, when you pass roughly 500 to 1,000 records, when you need a permission trail and consent log, or when manual deduplication and reporting start eating more time than they save.
Sources
Database, Wikipedia: defines a database and how data is organised in tables, rows and keys.
