CRM template for Excel – lead tracking for small businesses
When you’re just starting out, it’s tough to find new clients. But what if you suddenly have more leads than you know what to do with? It might seem like a blessing until you realize: your ability to capture people’s attention is only as good as your ability to retain it. Once you have your customers’ contact information, the next thing you should have is a plan to contact them!
Tracking your sales leads with a CRM template will help you do just that. You can also track where your customers are coming from and who they are, so you can focus your marketing efforts where it counts (and stop wasting time where it doesn’t). Having Excel skills helps, but even beginner Excel users can make use of a predesigned template. The first step is figuring out what your sales process looks like, so you can tailor your template to fit your sales goals.
Picture your process
Think about your customers’ journey from discovering your business to booking with you, and what steps you take to communicate with them. Your sales funnel is a good place to start. Picture all your potential customers at the top of the funnel, and what steps they take as they move down the four stages: awareness, interest, decision, and action.
1. Awareness stage:
At the top of your sales funnel are customers that know about you, usually called the “awareness” stage—maybe they’ve just done a Google search and found your website, or discovered you on social media. These are usually cold leads.
- How your customers find you are your lead sources. For example, you might get customers through referrals, Facebook, lead pages, directories, or paid ads.
- If you use a CRM template to track lead sources, over time you’ll be able to figure out what ones are getting you clients and what ones aren’t.Interest stage: a smaller subset of your customers might start reading your blog, looking through your website, or comparing you to some of your competitors—the “interest” stage. These are warm leads; they’ve shown interest in your service, but haven’t committed yet.
- This is the stage when you might collect some of their info—for example, if someone signs up for your email list or downloads an ebook on your website. Take note of any specialized info you gather, so you can add it to your sales lead template.
- Think about how you nurture leads in this stage of the process. When do you reach out to them? How often?
2. Decision stage:
a client might book a consultation or appointment in the “decision” stage. These are hot leads; they’ve made an active decision towards making a purchase.
- Keep in mind that your lead isn’t a customer until you get paid. It’s all too common for people to book an appointment and cancel before they go, so it’s still important to nurture leads in this stage.
- After the decision stage, give some thought to actions like follow up emails and appointment reminders that will give your clients that extra push to commit. Don’t let forgetfulness be the reason why you lose clients!
3. Action stage:
the final stage of the funnel is the “action” stage, when your clients make a purchase. After the action stage, they’ll be a customer instead of a lead. But your marketing efforts shouldn’t end there!
- Customer retention will look different for every business. For example, a hair salon might keep track of when their clients need a trim, and send a reminder email to each one when the time comes.
- How do you retain your customers? Do you send a thnk you note? Cards on holidays? For an extra personal touch, you might even decide to get creative and send your customers a special discount on their birthday.
Figuring out your process will make the most out of your CRM template. This way, you can adjust the template to reflect exactly how you want to classify your leads, which ones you want to invest in reaching out to, and when.
Introducing your CRM template
Enter the spreadsheet. You can download the spreadsheet as an Excel file or a Google sheets file, and edit it in whatever program you’re most comfortable with. You’ll notice a series of dropdown buttons next to each header title, which will allow you to sort or filter the information in any column. Each tab is designed with a specific CRM purpose in mind.
If you’re an Excel guru, it’s a jumping off point for adding formulas, tables, and tabs to give you the exact information you want. But beginners can get a lot out of it as is, too. Below is a summary of the information in each tab, and how you can use it:
The CRM tab is a record of your clients’ contact information, lead source, lead status, and contact dates. You can select a lead status by clicking any cell in the lead status column, and clicking the dropdown button that appears on the right. Entering the estimated sale for each customer means you can easily figure out how much money is in your sales pipeline: just highlight the column in Excel and the sum, average, and count will show up in the bottom right hand corner. When you make contact with a customer, enter the date in the “Last Contact” column and then plan what and when your next contact will be with the “Next Action” and “Next Contact” columns.
If you want more detailed information about client interactions, switch to the Contact Log tab. This gives you a place to enter the exact time, purpose, and method you used to contact your client, plus a larger area for notes. You can use the “Wrap Text” function to keep the notes visible within the cell, or for larger notes, use cell comments. You can also use this tab to record notes about the client’s preferences and talking points brought up during their appointment.
Once you’ve made sales, record them here: there’s columns for the amount, invoice number, and notes. You can use the sales log to generate sales reports using pivot tables, but even if you’re not that tech savvy, it’s easy to see a summary of sales by client. Just use the arrow to filter a specific client, then highlight their sales column to get the sum, average, and count of their sales. If you have monthly memberships rather than individual sales, you could add columns for the date their membership started, number of months they have subscribed, and amount they pay per month. From there, you could add a column with a function that multiplies the number of months by the amount to get their total sales.
The contact details tab gives you more detailed information about each client. When you enter client information into this tab, the same information will automatically pop up in the CRM tab once you type in their name. This is a good place to add columns of information if there are specifics about each client you’d like to record. For example, a gym might decide to create a column that tracks what personal trainer each client is paired with, or what their fitness goals are.
This tab is where you’ll find the settings for lead status, lead source, and date formats. From here, you can customize the lead status and source categories to better reflect your own sales process. When you do, they will show up differently in the CRM tab dropdown menus. For example, maybe “hot” and “cold” leads don’t mean much to you—in that case, you could update the terms to something else that makes more sense. Updating the Last Contact and Next Action day counts will change how the cells are color coded.
You can click on this tab for a brief explanation and links to more detailed help online. Since you can edit anything in the sheet, this is also a great place to put your own links, particularly if you’re new to Excel or Google Sheets. For example, you could paste in a link about how to use pivot tables or the VLOOKUP function if you want to add to your template and your Excel skills at the same time.
At this point, you might be thinking this sounds like a lot of data entry, particularly if you’ve got a lot of contacts already. Fortunately, there’s a shortcut: if your contacts are in Google, Outlook, or Yahoo, you can export your contact list as a .csv file, open the file in Excel or Google Sheets, and copy and paste the details into your spreadsheet. You’ll still need to manually add their lead status and other details, though.
When to consider CRM software
A CRM template in Excel or Google Sheets is a great way to start out if you have the time, or only a few clients. By working with something manually, you’ll get a sense of how a CRM functions and what features you really need.
If you’re not a fan of Excel or your contacts list has grown too big to manage manually, try a CRM platform like vcita. Your clients’ lead status will update automatically from “lead” to “customer” once they complete an appointment, make a payment, or get an invoice. You can customize their lead status to create targeted email and SMS campaigns at each stage of your sales funnel. If you’re fed up with working on a manual CRM template, sign up for a free trial of software that does the work for you. Or if you love experimenting with Excel, you can keep building your template. The choice is yours!