You know who your best customers are. You just cannot see them.
Your store platform tells you how many orders came in this month. It tells you total revenue. It might even tell you your average order value. But it does not tell you the one number that actually predicts growth: how often your best buyers come back.
A purchase frequency dashboard fixes this. It shows you, at a glance, which customers buy every 30 days, which ones buy every 90, and which ones have gone quiet. That is the difference between sending a generic discount to everyone and sending a targeted nudge to the people who are most likely to buy again this week.
You do not need a loyalty plugin, a CRM, or a paid app to build this. You need a spreadsheet, your order export, and about 45 minutes.
What You Need
- Your store platform order export (CSV or XLSX) with customer email, order date, and order total
- Google Sheets or Microsoft Excel (free works fine)
- 30 minutes to set up, 5 minutes per week to maintain
Step 1: Export Your Order Data
Go to your store admin and export your orders. Every platform has this option:
- Shopify: Orders page, click Export, choose All Orders, CSV format
- WooCommerce: Orders page, click Export, or use the built-in CSV export tool
- Squarespace: Commerce panel, Orders, Export to CSV
- Etsy: Shop Manager, Orders, Download CSV
You need at minimum: customer email (or name), order date, and order total. The more history you export, the better. Aim for 12 months minimum.
Step 2: Clean and Sort in Your Spreadsheet
Open the CSV in Google Sheets. Delete the columns you do not need. Keep only:
- Email (this is your unique customer identifier)
- Order date
- Order total
Create a new sheet tab called Frequency. In this tab, you will build a simple pivot table that groups orders by customer.
Insert a Pivot Table using your cleaned data. Set:
- Rows: Email
- Values: Count of Order Date (this gives you total orders per customer)
- Values: Sum of Order Total (this gives you total spend per customer)
- Values: Max of Order Date (this gives you last purchase date)
- Values: Min of Order Date (this gives you first purchase date)
Step 3: Calculate Frequency Score
Next to your pivot table, add four calculated columns:
Days Between First and Last Purchase: Subtract Min Order Date from Max Order Date. This tells you how long the customer has been active.
Average Days Between Orders: Divide Days Between First and Last Purchase by (Total Orders minus 1). If a customer has only ordered once, leave this blank.
Days Since Last Order: Subtract Max Order Date from today. This tells you how long it has been since they last bought.
Frequency Tier: Use this simple formula:
If Average Days Between Orders is less than 30 = High Frequency
If Average Days Between Orders is 31 to 60 = Medium Frequency
If Average Days Between Orders is 61 to 120 = Low Frequency
If Average Days Between Orders is over 120 = Occasional
If only 1 order = First-Time
Step 4: Build the Dashboard View
Create a third sheet tab called Dashboard. This is where you turn the data into something you can actually read in 10 seconds.
Add these summary cards at the top:
- Total Customers: Count of unique emails
- High Frequency Customers: Count where Frequency Tier = High Frequency
- At Risk Customers: Count where Days Since Last Order is greater than 1.5 times their Average Days Between Orders
- Average Customer Lifetime Value: Sum of Order Total divided by Total Customers
Below the summary cards, create a simple bar chart showing how many customers fall into each Frequency Tier. This is your distribution. Most stores will see a large bar for First-Time and a small bar for High Frequency. That is normal. The goal is to grow the High Frequency bar over time.
Add a second chart showing total revenue by Frequency Tier. You will likely find that High Frequency customers, even if they are a small group, generate a disproportionate share of revenue. That is the insight that changes how you spend your marketing time.
Step 5: Set Your Alert Triggers
The dashboard is only useful if you act on it. Set three simple rules:
Rule 1: At Risk Alert. Any customer whose Days Since Last Order exceeds 1.5 times their average is at risk of churning. Check this list weekly. Send these customers a personal email, not a discount code. Something simple: “Hi, noticed you have not been back in a while. Is there anything we can help with?”
Rule 2: Milestone Alert. When a customer places their 5th or 10th order, send a thank-you note. Not an automated email. A real one from you. These are your best buyers and they deserve a human touch.
Rule 3: First-Time Follow-Up. Any customer in the First-Time tier who has not placed a second order within 45 days should get a targeted follow-up. Not a discount. A product recommendation based on what they bought. “You picked up the candle last month. Here are the three most popular scents this season.”
How to Maintain It
Refresh your order export once a week. Paste it into the data sheet. The pivot table and dashboard update automatically. The whole process takes 5 minutes.
If you want to automate it further, you can connect your store to Google Sheets using Zapier or Make. Every new order appends a row to your sheet. The dashboard stays live without any manual export. But the manual version works perfectly fine to start.
What This Dashboard Tells You That Your Store Platform Does Not
Your store platform reports tell you what happened. This dashboard tells you what is about to happen.
When your High Frequency tier shrinks for two weeks in a row, that is an early warning. When your At Risk list grows faster than your First-Time list, your acquisition is outpacing your retention and you are leaking value. When your Average Days Between Orders drops from 45 to 35, something you did worked and you should figure out what.
These are the patterns that separate stores that grow from stores that plateau. And you do not need a plugin to see them.
Want More Like This?
Want more like this? Join MOKU Club for free. Weekly resources, early access to new guides, and occasional templates you can actually use. Join below.
