Tracking leads: a spreadsheet, statuses and when to move to a CRM

The Meta test for the IELTS prep centre is over: 11 leads, seven replied on WhatsApp, three came to the trial lesson. A week later the owner asks two things: which ads brought those three, and why the other eight did not come. The manager scrolls through the chats. The phone numbers are there, the ads are not, and what each person said he only roughly remembers.

A spreadsheet started on the first day of the test would have answered both questions in a minute. We will build one from the same 11 leads (the test figures are an example), and then look at when a spreadsheet stops being enough and it is time to move to a CRM: a system where every lead has a card with its history and the next step.

Which columns you need

One sheet in Google Sheets or Excel is enough. One row per lead:

Add notes, payment amount and owner when you need them. A short table filled in on every row is worth more than a long one filled in now and then.

The table holds people's phone numbers, so share it with named people, not with "anyone with the link".

Source: from the link tags to the table

In the week 2 test the ad links are tagged following the UTM scheme. Someone who tapped the problem ad on Instagram opened the page with utm_source=ig, utm_medium=paid_social, utm_campaign=ielts_trial and utm_content=problem.

For the tags to reach the table, the form has to keep them. You add hidden fields to the form and fill them from the page address, with the site builder's tools or a short script. The tags then arrive in the lead email together with the name and phone, and the manager copies them into the row.

If the form did not keep the tags, the source is gone. You can ask the person how they heard about the centre, but "from Instagram" will not tell you which ad worked. So before launch, submit a test form through a tagged link and see whether the tags made it into the email.

Copy the values as they are: ig, not "Instagram" or "insta". A filter will see three different sources where you meant one. For leads without tags (someone called, someone was referred), set up a couple of your own values, such as phone and referral, and use only those.

Statuses: one list for everyone

The status says which step the person is at. For the centre the steps are:

A status changes on a fact, not on an impression. "Booked for the trial" goes in when the person agreed to Thursday at 19:00, not when they wrote "interesting, I'll think about it". Otherwise the table shows five people booked, two turn up, and you start fixing the lesson when it was the booking that broke.

People who go quiet need one rule shared by every manager. For example, three messages over two days with no reply means a rejection with the reason "no reply". If one manager closes a lead after the first message and another keeps writing for a week, there is no comparing their no-reply rates, and the table will not tell you which of them does the job better.

Reason for rejection: from a list, no guessing

The reason is picked from a short list, not written in your own words. "Kinda pricey", "too expensive!!" and "price didn't work" become three separate reasons in a filter. Six are enough for the centre:

• no reply;
• too expensive;
• group times did not fit;
• needs an online format;
• exam is a long way off;
• other.

If "other" starts showing up often, open those chats and add a new reason to the list.

The reason comes from the person, not from the manager. If someone went silent right after the message with the price, the table still says "no reply", not "too expensive". It's easy to mistake a guess for a fact: a month later the table holds twenty "too expensive" rejections, the owner cuts the price, and people were actually leaving over the timetable. The last message to someone who has gone quiet can end with a question about what did not suit them. That is easy to answer in one word.

Make the status and the reason dropdowns. In Google Sheets, select the column and choose Insert → Dropdown. By default the sheet rejects a value that is not on the list, so typos do not multiply.

Example: the 11 leads from the week 2 test

Here are the test leads in the table. Names, phones and first-reply times are removed. utm_medium and utm_campaign are the same for everyone (paid_social and ielts_trial), so those columns are left out too.

Now a filter answers the owner. Of the three who attended the lesson, two came from the result_price ad and one from problem. All four who never replied came from problem as well. In Ads Manager that ad had the lowest CPL, 700 ₸, and the table shows that of its seven leads, one reached the lesson.

Eleven rows cannot tell you why. Apart from "no reply", no reason appears twice, and with three people at the lesson any pattern could be coincidence. But the table keeps growing. In a couple of months there will be dozens of rejections, and you will see where the centre loses more people: on price, on the timetable or on the format.

For those three, the status will change to "Paid" or "Rejected" after the conversation about the course. Then you will see which ad brings students, not just leads.

When a spreadsheet is no longer enough

While there are a few leads a day and one person answers them, a spreadsheet will do. It is time for a CRM when at least one of these four happens:

• Two or more people handle leads. A spreadsheet does not assign leads and will not warn you that a colleague has already written, so one person hears from both managers and another from nobody.
• Leads come from several places: the site form, Instagram Direct, WhatsApp, calls. They are copied over by hand, and some get lost on the way.
• You need reminders. A spreadsheet won't remind you to message on Thursday or to get back to someone after their exam in March, while a CRM sets a task with a date.
• People come back. Six months later a graduate asks about a speaking intensive. A new row appears in the table, and the history of the first course stays in another one.

That is why a CRM keeps the person and their requests separately. A contact is the person: name, phone, the whole history. A deal is one of their requests, with its own stage and outcome. The graduate has one contact and two deals: the course is complete, the intensive is still being discussed, and the new request does not overwrite the outcome of the old one. The diagram shows the two deals for this graduate.

amoCRM and Bitrix24 to start with

For first projects in Kazakhstan, take two CRMs with a Russian interface and prices in tenge: amoCRM and Bitrix24.

In amoCRM, contacts are linked to a deal, and a deal has a pipeline, a stage and a separate field for the reason for rejection (deal fields in the amoCRM API). amoCRM has no free plan; there is a 14-day trial (amoCRM pricing).

Bitrix24 has a Free plan that includes the CRM and does not limit the number of users (Bitrix24 pricing). By default its CRM runs in simple mode: any request, call or email immediately becomes a deal and a contact (CRM modes in Bitrix24). A deal has standard fields for tags, one for each of the five UTM tags (deal fields in Bitrix24).

Paid plan prices change, so look them up on these pages before you talk to a client.

If the spreadsheet was kept tidy, the move is simple. Statuses become pipeline stages, reasons for rejection become the list of reasons, and the tag columns become deal fields. For the site form to create a deal with its tags on its own, you need to connect the CRM to the site through the API. Once it is set up, submit a test form through a tagged link: the CRM should show a contact and a deal at the first stage, with all the tags in the deal. Then submit the same form again from the same phone. A repeat a couple of minutes later is the same request, not a graduate coming back six months on, and it should not create a new deal: the CRM should still have one contact and one deal. If a second contact or a second deal appears, sort out duplicates before the ads go live, not after a hundred leads.

Exercise: the lead table for your test

Take the leads from your week 2 test on the Start track and enter them into a spreadsheet or a free CRM plan.

• Columns: date, first reply, name and phone, utm_source, utm_campaign, utm_content, status, reason for rejection.
• Statuses and reasons for rejection are dropdowns.
• Every lead is entered with its date and its source from the tags.
• Every lead has a status, and every rejection has a reason.
• Under the table you have counted how many people replied and came from each ad.

If some leads have no tags, there is no getting them back. Write "no tags" as the source and fix the form before the next test. If rejections have no reasons, the manager did not ask. Do not guess after the fact: mark people who went silent as "no reply", people who declined without explaining as "other", and add a question about the reason to the last message. And if Ads Manager shows noticeably more leads than the table, see whether Lead fires on the button click or twice for one submission: the lead event in the Meta test should fire once, after the form is successfully submitted.