Rates each new lead in your spreadsheet against the customer profile you describe and appends the rating with its reasoning.
Uses
Google Sheets
OpenAI
Rate new leads against your ideal customer profile
Run every hour.Run every hour.
Set leadsSheetUrl to blank.Set leadsSheetUrl to blank.Set leadsTabName to blank.Set leadsTabName to blank.Set ratingsTabName to blank.Set ratingsTabName to blank.Set idealCustomerProfile to blank.Set idealCustomerProfile to blank.Set maxRowsRead to 300.Set maxRowsRead to 300.Set maxLeadsPerRun to 20.Set maxLeadsPerRun to 20.Set ratedKeys to an empty list.Set ratedKeys to an empty list.Set ratedEmails to an empty list.Set ratedEmails to an empty list.
If a step below fails, save the error as ratingsTabErrorIf a step below fails, save the error as ratingsTabErrorTry
Google SheetsRead column 'A' of ratingsTabName in leadsSheetUrl. Save it as ratingsRead.Read column 'A' of ratingsTabName in leadsSheetUrl. Save it as ratingsRead.
Change ratedEmails to ratingsRead.values.Change ratedEmails to ratingsRead.values.On error
Stop and report a problem: Could not read the '@ratingsTabName' tab, so there is no way to tell which leads have already been rated. Add that tab to the spreadsheet with the headers Email, Company, Rating, Why and Rated at, then run it again.Stop and report a problem: Could not read the '@ratingsTabName' tab, so there is no way to tell which leads have already been rated. Add that tab to the spreadsheet with the headers Email, Company, Rating, Why and Rated at, then run it again.
For each loggedEmail in ratedEmails:For each loggedEmail in ratedEmails:If a step below fails, save the error as loggedEmailErrorIf a step below fails, save the error as loggedEmailErrorTry
Add loggedEmail in lowercase to the end of ratedKeys and save it as ratedKeys.Add loggedEmail in lowercase to the end of ratedKeys and save it as ratedKeys.On error
Show in the output: Ignored an entry in the '@ratingsTabName' tab that could not be read as an email address.Show in the output: Ignored an entry in the '@ratingsTabName' tab that could not be read as an email address.Google SheetsRead the rows of leadsSheetUrl. Save it as leadsRead.Read the rows of leadsSheetUrl. Save it as leadsRead.Set ratedThisRun to 0.Set ratedThisRun to 0.For each lead in leadsRead.rows:For each lead in leadsRead.rows:
If ratedThisRun is greater than or equal to maxLeadsPerRunIf ratedThisRun is greater than or equal to maxLeadsPerRunYes
Stop repeating.Stop repeating.No
No steps
Set company to blank.Set company to blank.Set leadEmail to blank.Set leadEmail to blank.Set leadNotes to blank.Set leadNotes to blank.Set leadIsReadable to false.Set leadIsReadable to false.If a step below fails, save the error as rowErrorIf a step below fails, save the error as rowErrorTry
Trim lead.Company and save it as company.Trim lead.Company and save it as company.Trim lead.Email and save it as leadEmail.Trim lead.Email and save it as leadEmail.Trim lead.Notes and save it as leadNotes.Trim lead.Notes and save it as leadNotes.Change leadIsReadable to the length of company is greater than 0 and the length of leadEmail is greater than 0 and the length of leadNotes is greater than 0.Change leadIsReadable to the length of company is greater than 0 and the length of leadEmail is greater than 0 and the length of leadNotes is greater than 0.On error
Show in the output: Skipped a row in the '@leadsTabName' tab: it has no Company, Email or Notes column. All three are needed to rate a lead.Show in the output: Skipped a row in the '@leadsTabName' tab: it has no Company, Email or Notes column. All three are needed to rate a lead.Skip to the next item.Skip to the next item.If leadIsReadable is noIf leadIsReadable is noYes
Show in the output: Skipped a lead whose Company, Email or Notes cell is empty. Fill them in and it will be rated on the next run..Show in the output: Skipped a lead whose Company, Email or Notes cell is empty. Fill them in and it will be rated on the next run..Skip to the next item.Skip to the next item.No
No steps
If leadEmail is a valid email address is noIf leadEmail is a valid email address is noYes
Show in the output: Skipped a lead because '@leadEmail' is not a valid email address.Show in the output: Skipped a lead because '@leadEmail' is not a valid email address.Skip to the next item.Skip to the next item.No
No steps
Make leadEmail lowercase and save it as leadKey.Make leadEmail lowercase and save it as leadKey.If ratedKeys contains leadKeyIf ratedKeys contains leadKeyYes
Skip to the next item.Skip to the next item.No
No steps
If a step below fails, save the error as ratingErrorIf a step below fails, save the error as ratingErrorTry
Set prompt to You are helping a sales rep decide which leads to work first. You are rating how well one lead fits the customer profile below. You are not deciding anything about the company itself.Set prompt to You are helping a sales rep decide which leads to work first. You are rating how well one lead fits the customer profile below. You are not deciding anything about the company itself.
Reply in exactly this shape, and nothing else:
RATING: one of Strong fit, Possible fit, Weak fit
WHY: what in this lead's details lines up with the profile, and what does not
UNKNOWN: what the details do not say that would change the rating
Rules you must follow:
- The first line must be RATING: followed by exactly one of those three phrases and nothing else.
- Judge only what the details below actually say. Call a thing unknown rather than assuming it, and never invent facts about the company.
- Rate fit with the profile, not the worth of the business. A weak fit is a lead for somebody else, not a bad company.
The ideal customer profile:
idealCustomerProfile
The lead:
Company: company
Email: leadEmail
What we know: leadNotes.OpenAIAsk OpenAI: prompt. Save it as answered.Ask OpenAI: prompt. Save it as answered.Trim answered.response_text and save it as answer.Trim answered.response_text and save it as answer.If answer is emptyIf answer is emptyYes
Show in the output: No rating came back for company. It will be tried again on the next run.Show in the output: No rating came back for company. It will be tried again on the next run.No
Split answer by a new line and save it as answerLines.Split answer by a new line and save it as answerLines.Make the first item of answerLines with RATING: replaced by ratingWord.Make the first item of answerLines with RATING: replaced by ratingWord.Set rating to Unclear.Set rating to Unclear.If ratingWord is strong fitIf ratingWord is strong fitYes
Change rating to Strong fit.Change rating to Strong fit.No
No steps
If ratingWord is possible fitIf ratingWord is possible fitYes
Change rating to Possible fit.Change rating to Possible fit.No
No steps
If ratingWord is weak fitIf ratingWord is weak fitYes
Change rating to Weak fit.Change rating to Weak fit.No
No steps
Trim the items from index 1 onward of answerLines joined by a new line and save it as reasoning.Trim the items from index 1 onward of answerLines joined by a new line and save it as reasoning.If rating is Unclear or reasoning is emptyIf rating is Unclear or reasoning is emptyYes
Change reasoning to answer.Change reasoning to answer.No
No steps
Google SheetsAdd a row to ratingsTabName in leadsSheetUrl.Add a row to ratingsTabName in leadsSheetUrl.Add 1 to ratedThisRun.Add 1 to ratedThisRun.Add leadKey to the end of ratedKeys and save it as ratedKeys.Add leadKey to the end of ratedKeys and save it as ratedKeys.On error
Show in the output: Could not rate company: ratingError as text.Show in the output: Could not rate company: ratingError as text.