Lead scoring in Google Sheets, written back to your CRM
· SyncYak team
Most small teams already score leads somewhere. It is a column in a spreadsheet: a number or a label like "hot" next to a name, filled in by hand or by a formula. The problem is that the score lives in the sheet, and the people who work leads live in the CRM. Nobody opens the sheet when the phone rings.
This post shows how to keep the sheet as the place where scoring happens and still have the result appear on each lead in your CRM, with no scripts to maintain.
Why a sheet is a good place to score leads
A spreadsheet is the one tool everyone on a sales team can edit. A sheet gives you three things a CRM's built-in scoring often does not:
- Formulas you can read. Anyone can open the cell and see why a lead scored 80.
- Easy changes. Moving a threshold or adding a column takes seconds, with no admin ticket.
- Your own data. You can combine CRM fields with things the CRM does not hold, such as a price band from your market, a pre-approval status from your lender, or a campaign cost.
The weak spot is delivery. A score nobody sees does not change who gets called first.
Step 1: get your leads into a sheet with their CRM id
Scoring needs a stable key, and the CRM lead id is the best one. Names change, emails get typos, and phone numbers are shared by couples. The id does not change.
SyncYak CRM copies leads from Follow Up Boss, Lofty, HighLevel and Cloze into a Google Sheet you own: one tab per object, one row per lead, updated by CRM id. Your own columns are left alone when the sync refreshes a row, so you can add scoring columns to the right of the synced ones.
A layout that works well:
| id | firstName | stage | source | days_since_contact | score | priority |
|---|---|---|---|---|---|---|
| (synced) | (synced) | (synced) | (synced) | formula | formula | formula |
Step 2: write the scoring formulas
Keep the rules simple enough to explain in one sentence each. A starting point for a real-estate team:
days_since_contact: =TODAY() - DATEVALUE(lastActivity)
score: =IF(stage="Hot", 40, 0)
+ IF(source="Zillow", 20, 10)
+ IF(days_since_contact < 3, 30, 0)
priority: =IF(score>=70, "A", IF(score>=40, "B", "C"))
Three habits keep a scoring sheet useful:
- Write the rules in a notes cell at the top. Six months from now, someone will ask why a lead is a B.
- Score on behavior, not on guesses. Recent contact and stage say more than a hunch.
- Keep the output small. One number and one letter are enough. Agents will not read ten scoring columns.
Step 3: map the columns to CRM fields
Create the custom fields in your CRM first, for example "Lead score" and "Priority". Then map each sheet column header to the matching CRM field in SyncYak. When a row is added or changed, SyncYak writes those values into the lead, matched on the id column.
The write-back is a feature of the CRM module's paid plans, starting on Hobby ($29 a month; see pricing). The free plan syncs leads one way, into the sheet or your database.
Two details to know:
- Changed scores are written. When a row changes, its mapped values go into the matching CRM lead.
- Write-back runs at your plan's pace. Scores arrive in batches, not instantly. That is fine for a priority label, and it is why you should not use this for something that must update the second a lead replies.
Step 4: put the score where agents will see it
Once the score is a CRM field, use it where agents already work:
- Add "Priority" as a column in the lead list and sort by it.
- Create a smart list for priority A leads with no contact in the last two days.
The point is that nobody should have to open the sheet to act on it.
A worked example
A team of eight agents syncs about 3,000 leads into a sheet. Monday morning, the team lead tightens the rule: leads with no contact in seven days drop 20 points. They edit one formula. Within the next write-back cycle, every affected lead has a new priority in the CRM, and the "priority A, not contacted" smart list shrinks to the leads that matter this week. No developer, no export, no import.
Scoring new leads before they reach the CRM
A different case is a lead that has not been created yet. Parse, SyncYak's email parsing module, can run a lookup against a second sheet before delivering a lead: it finds the row whose column matches a parsed field (email, phone, zip code, listing or source) and copies the columns you pick into the lead. That is how you attach the agent who owns a zip code, or a campaign name for a source, at the moment the lead arrives. Lookups start on Hobby and the number of lookups depends on your plan, as listed on pricing.
The two features fit together: lookups enrich leads on the way in, and sheet write-back scores the leads you already have. The lead enrichment page shows both side by side.
How SyncYak helps
SyncYak CRM keeps your leads in a Google Sheet or your own Postgres or MySQL database and writes your computed fields back to the CRM. If you prefer SQL over spreadsheets, the same write-back works from a from_syncyak_ table. For the sync side, see CRM to Google Sheets, and for the full set of options see the CRM product page.
Next step
Connect one CRM, let the first sync fill a sheet, and add a single score column. Create a free SyncYak account and see your own leads in a sheet in a few minutes.