---
name: franconnect-to-hubspot
description: Turn FranConnect exports into HubSpot import files for Burgess Media. Every enquiry becomes a contact and a deal in the Franchise Sales Pipeline, co-applicants are associated, remarks become notes, and once the deals are in, brokers are matched and associated. Use when Monique uploads a FranConnect export (leads, co-applicants or remarks) or says the broker deals are in, or says "prep the FranConnect export", "get this ready for HubSpot", "import these into HubSpot", or "match the brokers".
---

# FranConnect to HubSpot

<!-- DRAFT v0.3, 4 Oct 2026. The Open questions section at the end is for Alyssa and Monique to settle. Fold each answer into the step it belongs to, then delete the section and this comment. -->

You prepare FranConnect exports for import into Burgess Media's HubSpot portal. You do not
import anything yourself. You hand back files that Monique imports, plus a short report of
what you changed and what needs her decision.

## How Burgess Media's HubSpot is set up

- **No leads. Deals only.** Every FranConnect enquiry becomes a deal, whatever its status,
  including dead, on hold and early stage enquiries.
- **One contact per person, one deal per enquiry.** If the same email enquires twice, or for
  both brands, that is one contact with two deals.
- **One pipeline:** `Franchise Sales Pipeline`. Every FranConnect Lead Status exists as a
  deal stage, so the status carries across unchanged.
- **Brand is a property on the deal**, not a separate pipeline. Two brands: `Another Nine`
  and `Menchies`. It's imported on the deal only. A HubSpot workflow appends it to the
  contact. When a new brand comes on, Monique adds it to the Brand dropdown on both the
  contact and the deal before the next import. If the export has a brand that isn't an
  option yet, stop and tell her, or those rows fail to import.
- **The candidate** is the person who enquired. They're a contact, associated to their deal
  with no label (the default association).
- **Co-applicants** are contacts, associated to the candidate with the label `Spouse`,
  `Business Partner` or `Relative`.
- **Broker contacts** are contacts with `Broker = Yes`. Associated to the broker deal only,
  never to the candidate contact (file 10 or 11).
- **Broker companies** are the broker's network (Franzy, The Perfect Franchise, etc.), with
  `Broker = Yes`. Associated to the broker deal (file 10 or 12).
- **The Broker association label is added automatically** as long as `Broker = Yes` is set
  on the contact or company. Every new broker contact and company in files 1 and 2 must
  carry `Broker = Yes`.

## Five types of import

1. **Add contacts and companies** (files 1 to 6). New broker networks (companies) and new
   brokers (contacts), both with Broker = Yes. New candidates and co-applicants (contacts).
   Then the co-applicant links, which only need the contacts to exist. First, so everything
   else can link to them.
2. **Create new deals** (files 7 and 8). Deals only, each linked to its candidate contact by
   email.
3. **Update existing deals** (file 9). Any deal already in HubSpot, broker or not. Deal data
   only: brings its stage, close date and lead source up to date from FranConnect. Its
   broker is already linked. Built from the FranConnect exports plus a HubSpot lookup.
4. **Update broker deals** (files 10 to 12). Attaches the broker contact and company to
   broker deals created in this round, once they exist. Built from a HubSpot lookup of
   those deals.
5. **Import notes** (file 13). Always last, after the other four, so every contact the
   notes attach to exists.

Part A covers types 1, 2, 3 and 5 (steps 1 to 8). Part B covers type 4 (step 9). The notes
file is built in Part A but imported last. If Monique uploads FranConnect exports, do
Part A. If she says files 1 to 9 are imported and asks to match the brokers, do Part B.

## What Monique gives you

**Part A**, straight out of FranConnect, CSV or Excel:
1. **All Leads export.** One row per enquiry. Required.
2. **Co-Applicants export.** Co-applicant, relationship, and the contact they belong to.
3. **Remarks export.** The activity history, one row per remark. Also the source of the
   close date (step 4), so ask for it even if notes aren't being imported this round.
4. **Brokers export.** One row per broker sourced candidate, with the broker's first name,
   last name and email. The only FranConnect file that holds broker emails.

Nothing else. Everything in HubSpot, you look up yourself (next section).

## Looking things up in HubSpot

Monique's Claude is connected to Burgess Media's HubSpot. Use that connection to **look
things up only**. Never create, change or delete anything in HubSpot. Monique does every
import herself.

| What you need | How to find it |
|---|---|
| Which deals already exist | Search deals in the Franchise Sales Pipeline. Take Record ID, Deal Name, Deal Stage, Close Date and the associated contact's email |
| Broker contacts | Search contacts where `Broker = Yes`. Take Record ID, name, email and associated company |
| Broker companies | Search companies where `Broker = Yes`. Take Record ID and name |
| New broker deals (Part B) | Search deals where Lead Source Category (Deal) is `Brokers` and created since the file 8 import. Take Record ID, Deal Name and the candidate's email |

**Broker = Yes is how you know who is a broker.** A contact or company is a broker only if
Broker = Yes. Never decide from the name, the email domain or the network. If a broker
in FranConnect can't be found with Broker = Yes, list them for Monique: either they're
missing from HubSpot, or Broker = Yes wasn't set.

**If the HubSpot connection isn't working**, stop and say so. Don't guess which deals
exist. Monique can export the same lists from HubSpot and upload them instead.

If something is missing, do what you can, leave the dependent columns blank, and say what
you need.

## Part A

Work on a copy. Never change an export as received.

### 1. Check each file before touching it

Report:
- row count and unique email count
- column headers, and any you don't recognise
- which brands appear, and how they're spelt
- every distinct Lead Status

Stop and ask if the shape is different from last time (missing columns, new columns, a lot
fewer rows). **The Remarks export is often a date window, not the full history.** Never
treat a smaller file as a replacement for an earlier one without saying so.

### 2. Clean

Apply to every file. Count each change for the report.

| Field | Rule |
|---|---|
| Email | Lowercase, trimmed |
| Phone | E.164. `+1` on 10 digit North American numbers. A number with its own country code keeps it. Anything you can't resolve stays exactly as exported |
| Dates | Text in `MM/DD/YYYY`. Strip times. Never leave a date as a number like `46358` |
| Names | Trim spaces. First name before last name (the Co-Applicants export has them the other way round). Don't change capitalisation |
| Brand | `Another Nine` or `Menchies`, exactly |
| Lead Status | Trim leading and trailing spaces. `Closed Lead- Sold` becomes `Closed Lead - Sold` |
| Placeholders | Clear a value only on an exact match to a known placeholder (`Not Contacted` in a date field, `None` in Lead Source Details). Never blank a value just because it looks wrong |
| Owner | Remove the owner columns (Lead Owner, Contact Owner) from every import file. There's no need to set an owner, it's all Tom |

**Watch for columns in the wrong block.** In the broker export, `Mobile` sits inside the
broker columns and is the broker's number. Imported as is, it lands on the candidate.
Rename it `Broker Mobile`. Check what a column sits next to before trusting its name.

### 3. Set aside, don't import

Move these to the **Set Aside** file with the reason and the original row number. Never
delete. **They're left out of every file**: contacts and deals, co-applicants, broker to
candidate and notes. Nothing in any import should point at a record that isn't there.

- **Test records:** Lead Killed Reason is `Test Lead`, or the name or email contains `test`.
- **Internal and brand people:** email on a brand, Burgess Media or agency domain
  (anothernine.com, menchies.com, burgessmedia).
- **Bots:** email on `mail.ru`, `inbox.ru`, `list.ru`, `bk.ru` or `yandex.ru`. Or no surname
  plus a run together first name (`BarryCaf`) or a known spam domain (`gmx.com`, `.online`).
  Flag these for Monique to confirm. Don't decide on your own.

People outside the US are imported like anyone else.

### 3b. Where every FranConnect column goes

Every column in the All Leads export goes to a HubSpot property on the contact or the deal,
a reference column, or nowhere. Name each import column after the HubSpot property label
below so the import screen matches it. Internal names are in brackets, for looking things
up through the HubSpot connection. A column not on this list is new: stop and ask.

**Contact**

| FranConnect column | HubSpot contact property |
|---|---|
| First Name | First Name (`firstname`) |
| Last Name | Last Name (`lastname`) |
| Email | Email (`email`) |
| Mobile | Mobile Phone Number (`mobilephone`) |
| Work Phone | Phone Number (`phone`) |
| Address1 | Street Address (`address`) |
| City | City (`city`) |
| State / Province | State/Province (`stateprovince`) |
| Zip / Postal Code | Postal Code (`zip`) |
| Country | Country/Region (`country`) |
| Company Name | Company Name (`company`) |
| Veteran | Veteran (`veteran`). Only option is Yes |
| LinkedIn | LinkedIn (`hs_linkedin_url`) |
| Inquiry Date | [Import] Inquiry Date (`inquiry_date`) |
| Lead Status | [Import] FC Lead Status (`import_fc_lead_status`) |
| Lead Killed Reason | [Import] Lead Killed Reason (`import_lead_killed_reason`) |
| Lead Source Category | [Import] Lead Source Category (`import_lead_source_category`) |
| Lead Source Details | [Import] Lead Source Details (`import_lead_source_details`) |
| Broker Network (from Lead Source Details) | [Internal reference only] Broker Network (`broker_network`) |
| Franchise Awarded | [Import] Franchise Awarded (`import_franchise_awarded`) |
| Cash Available | [Internal reference only] Cash Available (`cash_available`) |
| Previous Franchise Experience? | [Import] Previous Franchise Experience? (`previous_franchise_experience`) |
| How did you hear about us? | FC How did you hear about us? (`fc_how_did_you_hear_about_us`) |
| First Contacted | FC First Contacted Date (`fc_first_contacted_date`) |
| Last Contacted | FC Last Contacted Date (`fc_last_contacted_date`) |
| (co-applicants only) | Co-applicant (`coapplicant`) = Yes |
| (new brokers only) | Broker (`broker`) = Yes |

**Deal**

| FranConnect column | HubSpot deal property |
|---|---|
| (built, step 4) | Deal Name (`dealname`), Pipeline (`pipeline`), Deal Stage (`dealstage`), Close Date (`closedate`) |
| Lead Status | Deal Stage (`dealstage`) |
| Brand Name | Brand (`brand`). A workflow appends it to the contact |
| Inquiry Date | Inquiry Date (`inquiry_date_`). Date only, the time is dropped |
| State / Province | State/Province (`state_province`) |
| Lead Source Category | Lead Source Category (`lead_source_category`) |
| Lead Source Details | Lead Source Details (`lead_source_details`) |
| Broker Network (from Lead Source Details) | [Internal reference only] Broker Network (`broker_network`) |
| Lead Killed Reason | Lead Killed Reason (`lead_killed_reason`) |
| Franchise Awarded | Franchise Awarded (`franchise_awarded`) |
| Franchise Awarded Date | Franchise Awarded Date (`franchise_awarded_date`) |
| # of locations | Number of Locations (`number_of_locations`) |
| Discovery Day Date | Discovery Day Date (`discovery_day_date`) |
| Pre-Discovery Day Notes | Pre-Discovery Day Notes (`prediscovery_day_notes`) |
| Post Discovery Day Notes | Post-Discovery Day Notes (`postdiscovery_day_notes`) |
| Leadership Call Notes | Leadership Call Notes (`leadership_call_notes`) |
| Cash Available | Cash Available (`cash_available`) |
| Previous Franchise Experience? | Previous Franchise Experience? (`previous_franchise_experience`) |
| First Contacted | FC First Contacted Date (`fc_first_contacted_date`) |
| Last Contacted | FC Last Contacted Date (`fc_last_contacted_date`) |

**No HubSpot property yet**

| FranConnect column | What to do |
|---|---|
| Leadership Calls Status | No property on the contact or the deal. Keep as a reference column and tell Monique |
| Discovery Day Status | No property. The nearest is Discovery Day Registered (Yes or No), which is a different question. Reference column, tell Monique if it has data |
| Market, Qualified, Buying Percentage(%) | Nowhere. Empty in every export so far (Buying Percentage is always 0). If one ever has data, stop and ask |
| Lead Owner | Removed. No need to set an owner, it's all Tom |
| Broker First Name, Broker Last Name, Broker Email, Broker Mobile | Not on the candidate. Used for broker matching (steps 6 and 9) |
| Original row number, Close Date Source, any flag Claude adds | Reference column only |

**Check the dropdowns before handing back.** State/Province, Lead Killed Reason, Veteran,
Brand and Lead Source Category are dropdowns in HubSpot. A value that isn't an option fails
the row. Look up the options through the HubSpot connection, leave a non-matching value
blank in the import column, keep it in a reference column, and list it for Monique.

### 4. Build the deal columns

Add these at the end of the All Leads sheet:

| Column | Value |
|---|---|
| Deal Name | `Last First - Brand`, e.g. `Amiss Scott - Another Nine`. No last name: `First - Brand`, single spaces only |
| Pipeline | `Franchise Sales Pipeline` |
| Deal Stage | The Lead Status, cleaned |
| Brand Name (Deal) | The brand. Deal only |
| Close Date | See below |
| Inquiry Date (Deal) | The FranConnect Inquiry Date, copied onto the deal. See the note below |
| State / Province (Deal) | The candidate's state, copied onto the deal for the FDD registration check |
| Lead Killed Reason, Franchise Awarded, Franchise Awarded Date, # of locations, Discovery Day Status, Discovery Day Date, Pre-Discovery Day Notes, Leadership Calls Status, Leadership Call Notes | Straight from the export, onto the deal (step 3b) |
| Close Date Source | Which remark the close date came from, or `None found`. Reference column only, not imported |
| Lead Source Category (Deal), Lead Source Details (Deal), Broker Network (Deal) | The deal's own column for each. Same value as the contact's column. Broker Network is the part of Lead Source Details before the brackets, broker rows only |

**New or existing.** Match each enquiry to the deals already in HubSpot on three things:
the candidate's **email**, the **brand**, and the **inquiry date** (the day, not the time).
Never match on deal name: names change, and older deals use an earlier naming format.

- One match: the deal already exists. It goes in the Deal Updates file (file 9), never in
  7 or 8, or HubSpot creates a second deal.
- No match: it's new, and the deal goes in file 7 or 8.
- More than one match: HubSpot already has duplicates. Leave it out and list it for Monique
  to merge in HubSpot. Don't pick one.
- A HubSpot deal with no inquiry date: match on email and brand only if there's exactly one,
  otherwise list it.

**Same person, same brand, more than once.** Only a separate deal if it's a genuinely
separate enquiry, with a different inquiry date. Two rows with the same email, brand and
inquiry date are the same enquiry exported twice: keep one and list it. When there are
separate enquiries, add the inquiry date to the deal name of each so the names don't clash,
e.g. `Mohrbach Kyle - Another Nine (21 Jul 2026)` and `Mohrbach Kyle - Another Nine (24 Jul
2026)`. Only add the date when there's a clash. Enquiries a few days apart may be the same
person submitting twice: still two deals, but list them for Monique.

**Dates in HubSpot can be day-first.** Some inquiry dates from the September import went in
with day and month swapped (FranConnect 05/06/2026 became 5 June). When matching, treat
HubSpot's date as a match if it's the FranConnect date with day and month swapped, and put
the correct date in the Deal Updates file. If contacts' [Import] Inquiry Date is swapped
too, list them in the report. Fixing those is one-off work outside this process, not one of
the import files.

**New contacts** go in the Contacts file (file 3): candidates whose email isn't in HubSpot
yet, plus co-applicants who aren't contacts yet. A candidate who already exists in HubSpot
is not added again. Their new deal still goes in file 7 or 8 and links to them by email. Same Deal Name but a different email: list it and ask.

**Split new deals into two files** on Lead Source Category: `Brokers` goes in the broker sourced file
(file 8), everything else in the non-broker file (file 7). Same columns in both, plus
Broker Network (Deal) on the broker file. Keeping the broker deals in their own import means
they can be pulled back out of HubSpot as one list for Part B.

If a last name carries a tag (`Patel - TB`), leave the contact's last name as it is but drop
the tag from the Deal Name. List every one in the report.

**Lead source is in the files twice.** Lead Source Category, Lead Source Details and Broker
Network each have one column for the contact (file 3) and one for the deal (files 7 and 8,
ending in (Deal)). One column can only go to one place in an import, so each needs its own.

**Time taken is measured from the Inquiry Date, not HubSpot's create date.** Every imported
deal gets the import day as its create date in HubSpot, which says nothing about when the
enquiry actually came in. So any report on how long a deal took (to close, to reach a
stage) starts from Inquiry Date (Deal). That's why it's copied onto the deal.

**Close date is the date the deal moved to its current stage in FranConnect.** Find it in
the Remarks export, matching on email, and take the most recent of:

| Current stage | Remark to look for |
|---|---|
| Dead Lead | `Lead has been killed...` |
| Any other stage | `Lead Status has been changed to <that stage>` |

**Do Not Contact deals have no close date.** Leave it blank and set Close Date Source to
`Do Not Contact, left blank`. An opt-out date says when they stopped emails, not when the
deal closed.

The status change text sometimes runs straight into a typed note with no space
(`Lead Status has been changed to ValidationZoom validation invites SENT`). Match the stage
name at the start, longest stage name first.

If there's no matching remark, leave Close Date blank, set Close Date Source to
`None found`, and list the deal. Don't use another date in its place.

**If a Lead Status isn't a stage Monique has set up, stop and ask.** Don't map it to the
nearest one.

### 5. Co-applicants

From the Co-Applicants export. Each row is co-applicant, relationship label, then the
contact they belong to.

1. **Build the association files:** Contact Email, Co-Applicant Email. Join on email
   only. Drop the name columns, the contact file already has them. **One file per label**
   (Spouse, Business Partner, Relative), so the label is set once for the whole import.
2. **Labels** are `Spouse`, `Business Partner` or `Relative`. Anything else, ask.
3. **No contact given:** some rows have a label but the contact side is blank. Set them
   aside on their own tab. Don't fill them from the row above.
4. **Conflicting labels:** the same pair stated twice with different labels (Spouse one
   way, Business Partner the other). Flag for Monique. One label has to win.
5. **Co-applicant not in the contacts file:** list them. They need adding to the contacts
   file before the association can load.
6. If a newer export has rows with no label where an earlier export had one for the same
   pair, ask Monique whether to reuse the earlier label. Mark any reused label so it's
   visible.

### 6. New brokers

From the Brokers export. The broker is linked to the broker deal only, never to the
candidate contact. That happens in files 10 to 12, for new broker deals.
Here you only make sure every broker exists in HubSpot first.

1. **New brokers:** any broker email not already in HubSpot (Broker = Yes contacts) goes in
   the Broker Contacts file (file 2): first name, last name, email, company, Broker = Yes.
2. **New networks:** any network not already in HubSpot (Broker = Yes companies) goes in the
   Broker Companies file (file 1), with Broker = Yes.
3. **Company comes from the network, not the email domain.** Take the network from Lead
   Source Details and find that company in HubSpot (Broker = Yes). Put its Company Record
   ID on the new broker's row. Many brokers use their own domain, so if file 2 has no
   Company Record ID, HubSpot links them by domain instead and creates a one-person company
   for each. Independent brokers have no company: leave it blank.
4. **Network but no named broker:** if FranConnect only records the network (for example
   just `Franzy`) and not the broker's name, there's no broker contact to add. The deal is
   linked to the network company only.

### 7. Notes (remarks)

From the Remarks export.

1. **Columns:** Email, Notes body (from Remarks), Activity date (from Date Added),
   Activity assigned to (from Lead Owner), Created by (from Lead Owner). Drop the rest,
   including the co-applicant block.
2. **The export can be blocked:** the email only on the first row of each contact, blank
   below. Fill down only after checking it against an earlier flat export, and say so.
3. **Keep every remark.** Real notes and every FranConnect system entry (status changes,
   lead created, lead killed, lead updated, opt in and out, field and owner changes) all go
   in. Don't filter by type.
4. **Only new remarks.** The Remarks export holds the whole history, and most of it is
   already in HubSpot as notes. Look up the notes already on each contact through the
   HubSpot connection and leave out any remark that matches one on email, note text and
   date. Compare the text with HTML tags and extra spaces removed, since HubSpot stores
   notes as HTML. Never import the same remark twice.
5. **Duplicates inside the export:** the same email, note text and date twice. Keep one.
6. **Built now, imported last.** The notes file is file 13. It goes in after the broker
   updates, once every contact exists.

### 8. Check your own work

Before handing over, re-read the finished files and confirm:
- Every new Deal Name equals Last + " " + First + " - " + Brand from the same row, single spaces.
- Every row in files 1 and 2 has Broker set to `Yes`: on the company in file 1, and on both the contact and the company in file 2.
- Every row in files 7 and 8 has Deal Name, Pipeline, Deal Stage and the candidate's email.
- Every candidate email in files 7 and 8 is either in file 3 or already in HubSpot.
- No email in file 3 is already a contact in HubSpot.
- No deal in files 7 or 8 already exists in HubSpot (email, brand and inquiry date).
- No two deals in files 7 and 8 have the same name.
- Every row in file 8 has Lead Source Category `Brokers`, and none in file 7 does.
- Every Deal Stage equals that row's cleaned Lead Status.
- Every close date traces to a remark, named in Close Date Source.
- Every co-applicant and notes email is in file 3 or already in HubSpot.
- Row counts add up: imported + set aside = export, for every file.
- No date column holds a number.

Report anything that fails. Don't fix it quietly.

## Part B

### 9. Match the brokers to the deals

Look up the new broker deals in HubSpot (see "Looking things up in HubSpot"). Only deals
where Lead Source Category is `Brokers`. Don't parse anything else, or `Big Rush (Meta)` gets read as a person.

0. **Check Lead Source Details is filled on every deal.** If some are blank, file 8
   didn't map it to the deal. Ask for the FranConnect All Leads export, fill the blanks by
   matching the candidate's email, and hand back a Deal Updates file (file 9) with just
   those deals, to import before 10 to 12.
1. **Lead Source Details** looks like `Network (Broker Name)`, e.g. `Franzy (Caleb Clayton)`.
   The network is everything before the brackets and the broker is inside the **last**
   set of brackets. `Franpath (ifpg) (Emilio Soli)` is network `Franpath (ifpg)`, broker
   `Emilio Soli`.
2. **Match the broker by email first.** The FranConnect Brokers export gives each
   candidate's broker email (`Email ID`). Find that email among the Broker = Yes contacts in
   HubSpot. FranConnect spells some brokers more than one way (Tony Carella and Tony
   Carrella are the same person, same email), so the email is more reliable than the name.
   Only if there's no email, match by first and last name against the Broker = Yes contacts in HubSpot.
   If no match, try without `Sr.` or `Jr.`, but check HubSpot first, because some brokers are saved
   with it (`Michael Chiodo Jr.`).
3. Fill Broker First Name, Broker Last Name, Broker Email, Broker Contact Record ID, Broker
   Company Name, Broker Company Record ID.
4. **Network but no named broker** (`Franzy` on its own): fill the company from the network,
   using the same company ID as every other row for that network. No broker contact.
5. **Independent brokers** (`Independent (Joe Bailey)`): no company. Leave it blank and note
   "Independent broker, no company". Never create a company called Independent.
6. **More than one match, or no match:** leave blank and list it. Never guess.

**A broker's email domain often doesn't match their network.** Ifpg and Franserve brokers
run their own businesses on their own domains, e.g. `ray@gloverfranchising.com` is with
Franserve. That's expected, not an error. The network comes from Lead Source Details and
the broker's company in HubSpot, never from the email domain.

## The import files

Thirteen files at most, run in number order: six to add contacts and companies, two to
create new deals, one to update existing deals, three to update broker deals, then notes
last. Hand back every one that has rows, named and numbered as below, plus the Set Aside
file, which is never imported. Hand back **Google Sheets**, one per file, in a dated
subfolder of the import folder in Drive. If there's no Drive connection, use Excel (.xlsx).
Never CSV, which loses the highlighting. Keep dates and record IDs as plain text, so Sheets
doesn't turn them into numbers.

**Name every file by run order and what it does**, so Monique knows which to do first:
`NN - ACTION object - detail`, numbered 01, 02, 03 in the order to import, counting only
the files actually handed back. ACTION is CREATE, LINK or UPDATE. For example:
`01 - CREATE contacts - Brokers`, `03 - LINK co-applicants - Spouse`,
`06 - CREATE deals - Non-broker`, `09 - UPDATE deals - Stage, close date, lead source`.
Leave a number free for the broker deal files that come after the first round, and name the
notes file `NN - CREATE notes - Import last`. The set-aside file is
`Set Aside - DO NOT import`.

**Creating a deal needs Deal Name, Pipeline and Deal Stage in the file.** Without all three,
HubSpot can't create it. **Updating a deal needs only Deal Record ID and Deal Name**, plus
whatever is being added. Nothing else from the deal goes in an update file, so nothing
already on the deal gets overwritten.

### 1. Add contacts and companies (Part A)

| # | File | Objects | Columns |
|---|---|---|---|
| 1 | Broker Companies, new only | Company | Company Name, Company Domain, Broker (`Yes` on every row) |
| 2 | Broker Contacts, new only | Contact and Company | First Name, Last Name, Email, Broker (Contact) `Yes`, Company Record ID, Broker (Company) `Yes`. Both Broker columns on every row |
| 3 | Contacts, new only | Contact | New candidates and co-applicants. Every column step 3b sends to the contact |
| 4 | Co-Applicants, Spouse | Contact, association | Contact Email, Co-Applicant Email |
| 5 | Co-Applicants, Business Partner | Contact, association | Contact Email, Co-Applicant Email |
| 6 | Co-Applicants, Relative | Contact, association | Contact Email, Co-Applicant Email |

### 2. Create new deals (Part A)

| # | File | Objects | Columns |
|---|---|---|---|
| 7 | Deals, non-broker | Deal, associated to contact | Contact Email, Deal Name, Pipeline, Deal Stage, Close Date, and every column step 3b sends to the deal, including the (Deal) copies |
| 8 | Deals, broker sourced | Deal, associated to contact | Same columns as file 7 (Deals, non-broker), plus Broker Network (Deal). Lead Source Category is `Brokers` on every row |

### 3. Update existing deals (Part A)

| # | File | Objects | Columns |
|---|---|---|---|
| 9 | Deal Updates | Deal | Always: Deal Record ID, Deal Name. Plus what changed: Deal Stage, Close Date, Lead Source Category (Deal), Lead Source Details (Deal), Broker Network (Deal) |

Only deals already in HubSpot. Deal data only: Deal Record ID and Deal Name on every row,
with the Deal Name in the current Last First - Brand format (otherwise this import puts an
old name back),
plus the columns that changed. No association columns. An existing broker deal already has
its broker linked, so files 10 to 12 are only for broker deals created in this round
(file 8).

### 4. Update broker deals (Part B)

| # | File | Objects | Columns |
|---|---|---|---|
| 10 | Broker Deals, contact and company | Deal + contact + company | Deal Record ID, Deal Name, Broker Email, Broker Company Record ID |
| 11 | Broker Deals, contact only | Deal + contact | Deal Record ID, Deal Name, Broker Email |
| 12 | Broker Deals, company only | Deal + company | Deal Record ID, Deal Name, Broker Company Record ID |

**One file per combination, depending on what's known**, so every column in a file is
filled on every row. That keeps import errors down: no blanks for HubSpot to trip on.

- **Contact and company** (10): a named broker with a network. Most broker deals.
- **Contact only** (11): independent brokers, who have no company.
- **Company only** (12): FranConnect recorded the network but not the broker's name.

Unmatched deals go to Set Aside with the reason.

### 5. Import notes

| # | File | Objects | Columns |
|---|---|---|---|
| 13 | Notes | Note, associated to contact | Email, Notes body, Activity date, Activity assigned to |

**Notes always go last.** A note attaches to its contact by email. If the contact doesn't
exist yet, HubSpot creates one from the email alone, and you end up with a blank duplicate
next to the real record. Built in Part A, held back until everything else is in.

**Highlight what gets imported.** In every import file, fill the importable columns light
yellow (`#FFF2CC`), header and every row. Put them first, on the left. Reference columns
(the original row number, Close Date Source, broker names, notes on why a row is there,
anything else that only helps a person check the file) go after them, on the right, with
no fill. Monique maps every yellow column and sets every white column to **Don't import**.

### Mapping each file

HubSpot asks where each column goes. Every yellow column has a place, listed below. Every
white (reference) column is set to Don't import.

| # | Column | Map to |
|---|---|---|
| 1 | Company Name, Company Domain | Company properties |
| 1 | Broker | Company property Broker. Must be mapped: it's how brokers are found and how the Broker label gets added |
| 2 | First Name, Last Name, Email | Contact properties |
| 2 | Broker (Contact) | Contact property Broker. Must be mapped, as for file 1 |
| 2 | Broker (Company) | Company property Broker. Sets Broker = Yes on the network company the broker is linked to |
| 2 | Company Record ID | Company, Record ID. Always map it: it links the broker to their network. Left unmapped, HubSpot links by email domain and makes one-person companies |
| 3 | Every yellow column | **Contact** properties |
| 4 to 6 | Contact Email | Contact properties, Email |
| 4 to 6 | Co-Applicant Email | Association, Contact Email. Label: the file's label |
| 7 and 8 | Contact Email | Association, Contact Email (links the deal to the candidate, no label) |
| 7 and 8 | Every other yellow column | **Deal** properties |
| 9 | Deal Record ID, Deal Name | Deal properties, Record ID and Deal Name |
| 9 | Only the columns being updated | Deal properties. Remind Monique: whatever is in the file overwrites the deal, blanks included, so every column she doesn't want to change must come out before she imports |
| 10 to 12 | Deal Record ID, Deal Name | Deal properties, Record ID and Deal Name |
| 10 and 11 | Broker Email | Association, Contact Email. Label: Broker |
| 10 and 12 | Broker Company Record ID | Association, Company Record ID. Label: Broker |
| 13 | Email | Contact, Email (the note attaches to this contact) |
| 13 | Notes body, Activity date, Activity assigned to | Note properties |

### Import order

Always in number order. An association or note that runs before its records exist either
fails or creates a blank record from just an email.

1. Files 1 to 6, add contacts and companies.
2. Files 7 and 8, create new deals.
3. File 9, update existing deals.
4. Monique tells Claude files 1 to 9 are in. Part B looks up the new broker deals and
   builds 10 to 12.
5. Files 10 to 12, update broker deals.
6. File 13, notes, last.

## The report

Short, plain English, in this order:
1. What came in, and what's ready to import (counts per file).
2. What was set aside and why.
3. **Needs your decision:** each item on one line, with the row.
4. What was cleaned (counts only, e.g. "312 phone numbers formatted").

## Rules

- **Never infer.** A blank stays blank. If FranConnect didn't say it, you don't fill it.
- **Never delete.** Set aside with a reason.
- **Never import.** You prepare. Monique imports.
- **Keep the original row number** on every row, as a reference column, so
  anything can be traced back to the export.
- **Ask, don't map.** A stage, brand, label or network you haven't seen before is a
  question for Monique, not something to fit to the nearest match.

## Open questions

To settle before this skill is final. Until then, follow the default in brackets and list
the affected rows in the report.

1. **Column placement** (step 3b). Confirm the contact or deal split, especially the
   Discovery Day fields (deal), and dropping Market,
   Qualified, Post Discovery Day Notes and Buying Percentage. (Default: as listed.)
2. **Close date for Closed Lead - Sold** (step 4). Use the date the deal moved to that
   stage, like every other stage, or the Franchise Awarded Date? (Default: status change
   date.)