How to Build a Customer Database in Excel

Winning a new customer usually costs more than keeping one. A good customer list shows who your best buyers are and who is drifting away. This guide shows how to build a customer database in Excel that does exactly that.

1. Decide what to store

Use Customer ID, name, city, segment, phone, email and, if customers agree, birthday. Collect only what you will use, and keep the file secure.

2. Track purchase history

Add first purchase date, last purchase date, number of orders and lifetime spend. Average order value = lifetime spend ÷ orders.

Example

Customer	Orders	Lifetime spend	Days since last	Status	Tier
Riya Shah	9	₹18,400	21	Active	Gold
Arjun Mehta	3	₹4,200	140	At risk	Silver

3. Flag active, at-risk and lapsed customers

Days since last purchase = today − last purchase. Set limits such as 90 days for at risk and 180 for lapsed, and use IF to label each customer.

4. Assign loyalty tiers

Set spend thresholds for tiers such as Silver, Gold and Platinum. Top customers by spend deserve personal attention; lapsed ones a win-back offer.

Use it well

Plan a win-back routine

Each month, filter at-risk customers and send a personal message or offer.

Respect privacy

Store contact details only with consent and limit who can open the file.

Avoid duplicates

Search before adding a new customer and use one ID per person.

FAQs

What is customer lifetime value?

The total a customer has spent with you so far, or is expected to spend over the relationship.

How do I choose at-risk limits?

Look at how often your typical customer buys and set the limit a little beyond that.

Is this the same as a CRM?

A CRM focuses on leads and deals in progress; a customer database focuses on existing customers and their history.

Get the template

The ReadySheets Customer Database tracks spend and last purchase, flags active, at-risk and lapsed customers, assigns loyalty tiers and shows top customers on a dashboard. Pair it with the Customer CRM.