Overview
Sheets is currently in beta and is only available to select people. Apollo automatically enrolls users as the feature becomes available.
Your marketing team hands off a fresh batch of inbound prospects every week. How much of that week does someone spend cleaning the list before anyone can work it?
Inbound marketing qualified leads (MQLs), the prospects your marketing team has flagged as ready for sales, rarely arrive ready to work. Company names come through in mixed casing, some records carry a personal email and no work email, employee counts sit in a field nobody has segmented, and no one has decided which rep owns what. A sheet collapses that work into columns that run once per row, so the cleanup, the routing, and the outreach all happen in one place and then repeat on their own.
Check out the following sections to turn a raw MQL export into a routed, sequence-ready set of contacts.
Understand the Scenario
Maya is a RevOps manager at a software company with a steady inbound motion. Every Monday, roughly 300 new MQLs land in HubSpot from webinar signups, content downloads, and demo requests.
The records arrive uneven. Some carry a work email, and some have no email at all. Company names come through however the prospect typed them. Nobody has decided which rep owns what, so the batch sits in a queue until someone sorts it by hand.
Maya's team exports the batch to a spreadsheet, cleans it manually, decides ownership row by row, writes openers one at a time, and pastes the result back into HubSpot. It takes most of a day, and by the time the sequence goes out, the prospects have cooled.
In this use case, Maya builds one sheet that handles all of it: import from HubSpot, clean with formula columns, fill gaps with enrichment columns, compute an owner with conditional logic, draft a personalized opener with an AI column, and enroll the qualifying records in a sequence. Once it runs, next Monday's batch handles itself.
Step 1: Import Your MQLs
Maya starts by pulling the records out of the CRM and into a sheet. This example uses HubSpot, but the flow is the same for Salesforce and Zoho.
- Launch Apollo and click Sheets.
- Click Create Spreadsheet, then select HubSpot.
- Select your HubSpot account, or click Add account to connect one.
- Select Contact as your target object.
- Select the fields you want in your sheet. This workflow uses email, first name, last name, company name, company website, job title, employee count, and the asset that generated the MQL.
- Click Continue to criteria.
- Click Based on custom filters, then click Add filter and narrow to the records you want, such as every contact with a marketing qualified lead lifecycle stage created in the last week that no one has worked yet.
- Click Import to new sheet.
Your CRM needs to be connected before the import flow can reach it. If no connection exists, click Add account during the import and authenticate, or check out the relevant CRM integration article to set it up first.
You have now imported your MQLs into a sheet.
Step 2: Clean and Enrich the Records
Formula columns do the tidying that people usually do by hand. Every formula references other columns through dynamic variables: type {{ anywhere in a formula and select the column you want to pull from.
To add a formula column, click Add column > Create formula > Create from scratch, enter your formula, name the column, choose an output type, then click Save. Check out Transform Data with Columns for the full column reference.
These formulas handle the most common problems in an inbound list:
| Formula | What it does here |
|---|---|
lower({{Email}}) |
Normalizes email casing so matching and deduplication work against a consistent value. |
title({{Company Name}}) |
Fixes company names that arrived in all caps or all lowercase, so they read correctly in outreach. |
substitute({{Website}}, "https://", "") |
Strips the protocol off a website so the value works as a company domain for enrichment. |
append({{First Name}}, " ", {{Last Name}}) |
Builds a full name from two columns, which several enrichment options accept as an input. |
coalesce({{Work Email}}, {{Enriched Email}}, {{Personal Email}}) |
Returns the first email that isn't blank, so one column always holds the best address available. |
empty({{Email}}) |
Returns true for records with no email at all, which gives Maya something to filter and sort on. |
Once the values are consistent, add enrichment columns to fill what the CRM never had. Click Add column, click Enrich Email, then map the inputs Apollo matches on, such as the cleaned full name and company domain. Add Enrich Phone, Enrich Job Title, and Enrich Employee Size the same way. Each one runs waterfall enrichment, so the more inputs you map, the better your match rate.
You have now cleaned and enriched your MQL records.
Step 3: Route Each Record to an Owner
Routing is a decision, which means a formula can make it. Rather than assigning records one at a time, Maya computes a segment from what's already in the row, then looks up the rep who owns that segment.
Add a formula column named Segment with a nested condition:
Each record now carries a segment, computed the same way every time. To turn that segment into a named owner, build a second sheet with two columns, one for the segment and one for the rep's email, then pull the owner across with a lookup column:
- Click Add column > Lookup row from another sheet.
- Select your rep assignment sheet on Select sheet.
- Select the segment column on Select a column.
- Select an operator, then enter
{{Segment}}as the row value so each row matches its own computed segment. - Select First match as your output shape.
- Enter a column name, then click Save & run.
A few more formulas make the routed list easier to work:
| Formula | What it does here |
|---|---|
and(not(empty({{Verified Email}})), not({{Do Not Contact}})) |
Returns true only for records that have an email and aren't flagged do not contact. This is the gate everything downstream checks. |
ifnot(empty({{Verified Email}}), "Ready", "Needs email") |
Labels each row so Maya can sort the incomplete records to the top and fix them first. |
adddays({{MQL Date}}, 7) |
Sets a follow-up date seven days after the record came in. |
diffindays({{MQL Date}}, {{Last Touch Date}}) |
Shows how long a record has been sitting untouched, which is worth sorting on before a weekly review. |
You have now routed each record to an owner.
Step 4: Write and Send the Outreach
An AI column drafts a personalized opener for every row. Dynamic variables are what make it personal: each {{ reference pulls that row's own value into the prompt, so one prompt produces a different result per record.
Click Add column > AI column, select a model, then enter a prompt built around your columns:
Write a two-sentence email opener for {{First Name}}, who is {{Job Title}} at {{Company Name}}.
Context:
- What the company does: {{Company Description}}
- Their segment: {{Segment}}
Rules:
- Reference the download in the first sentence.
- Keep the whole thing under forty words.
- Do not mention pricing and do not ask for a meeting.
Name the column, select text as your output type, then toggle on Only run when condition is met and enter your gate so the column skips records that aren't ready:
and(not(empty({{Verified Email}})), equals({{Segment}}, "Mid-Market"))
Check out AI Prompt Best Practices to sharpen the prompt itself.
Every column and export offers Save & run first ten rows under ▾. Use it on a new prompt or a new gate, read what comes back, then run the rest. A prompt that reads well in your head can still return something odd on row four.
Finally, enroll the qualifying records in a sequence. Click Export data > Add to sequence, then:
- Select Use existing sequence and pick the sequence that matches the segment you gated on.
- Map your verified email column to the email field, and your AI opener column to the custom field your sequence template reads from.
- Toggle on Only run when condition is met and enter the same gate you used on the AI column, so nothing enrolls without an email.
- Toggle on Auto-run column so new MQLs enroll as they land in the sheet.
- Click Save & run.
You have now enrolled your routed MQLs in a sequence.
Now It's Your Turn
Maya's Monday morning is now a glance at the sheet instead of a day of cleanup. The import runs on a schedule, the formula and enrichment columns fill themselves in, the routing formula assigns an owner, and the export enrolls anyone who clears the gate. Nobody opens the spreadsheet unless something looks wrong.
Build your version one column at a time. Import a single week of records, add one formula column, and watch what it returns before you add the next. Once the columns behave, set them to auto-run and check out Export and Automate a Sheet to put the whole thing on a schedule.