Preparing Your Spreadsheet for Import

Who this is for: agencies loading their own data into NextAgency using the import tools in the portal.

Not sure this is your path? If our team is scripting your data in for you, read NextConcierge: Preparing Your Data for Migration instead. The advice differs in a few important places, so it is worth being on the right one.

How the importer thinks

Understanding this makes everything else in the guide obvious.

The importer takes one spreadsheet at a time. You tell it which of your columns goes to which NextAgency field, and it loads the rows. There is a template you can download, but you do not have to use it. If your own sheet has the right information in it, you can map your own headers.

One import can bring in a lot more than cases

A case import is not limited to cases. From a single sheet it can load:

• Cases
• Contacts
• Notes
• Tasks
• Dependents
• Benefits and policies
• Sub-agents, by name

If all of that is on one sheet, that is fine. It goes in on one pass.

Splitting your data across several sheets is not a requirement. Agencies do it for very large imports, or simply because their export came out of the old system that way. It is a choice, not a rule.

What always comes in on its own

• Commissions. Always a separate import, and always after your policies exist. Every commission row has to attach to a policy already in the portal.
• Documents and files. Handled separately every time, and specific to the job.

Sub-agents: two ways in

A case import can create a sub-agent from a first and last name, and that is all it does. It will not carry an address or the rest of a sub-agent's detail.

There is also a dedicated sub-agent importer. Use that one if you hold full records on your sub-agents and want them loaded properly.

Records are matched on values, not on hidden identifiers

When a row needs to attach to something that is already in your portal, the importer finds it by matching real field values. Those values have to match exactly.

A note on record IDs from your old system

If you have exported your data from another system, you may have columns full of internal ID numbers: client ID, policy ID, commission payment ID. The self-service importer does not use these, so there is no need to chase them down for this path.

Keep them in your file anyway if they are already there. They cost nothing, and they are extremely useful if you ever ask our team to clean up or reconcile your data later. Just do not hold up your import trying to obtain them.

1. Set up your portal before you import

Less needs to be in place ahead of time than people expect. Two things genuinely have to exist first.

Missing values do not stop you

If your sheet contains a sales status, carrier, or product type your portal has never seen, the importer does not silently fail. It stops, tells you the value does not exist, and asks whether you want to add it. Say yes and it carries on.

So settling these in advance is about consistency, not necessity. Three spellings of the same carrier will quietly become three carrier records, and you will be merging them later.

The real exceptions

• Dropdown custom fields. If you built a custom field with a fixed set of options, the values in your sheet have to match those options exactly. The importer will add a new carrier for you; it will not add a new option to your own dropdown. This one catches people out often, so check your sheet against your dropdown before you import.
• Business type and market segment. These lists are fixed: individual, small group, and large group. Where your value does not fit, either map it to the closest match or put your original value into a custom field so you do not lose it.

2. Build the sheet

Start from the template, or map your own headers

The import screen offers a template to download. It is the fastest route because the field names already match, and it marks which fields are required. If you would rather work from an export you already have, that is supported too. You will map your headers to our fields during the import.

There are two templates, Group and Individual, but three ways to run the import, because groups split by size:

• Individual, for individual and senior clients
• Small group
• Large group

Pick the one that matches the data in your sheet, and keep individual and group data in separate sheets.

Do not worry too much about group size. A group can be switched between small and large at any time after the import, so cleaning that up later is no trouble at all.

Required fields

Rows missing a required field will not import.

Sales status is where the record sits with you: client, prospect, and so on. Every case needs one. If you segment your book in a way our defaults do not cover, you can add your own statuses, either beforehand or when the importer prompts you.

Broker of record has to match the user name in your portal exactly.

If you are importing tasks along with your cases, a task needs a title and a due date.

Creating new records versus updating existing ones

You choose which you are doing when you start the import, and the requirements differ.

Creating new records needs the required fields above. Fast Start Step 4: Adding Cases walks through the import screen itself, and includes a bulk importing video.

Updating existing records does not need broker of record or sales status. It needs enough to identify the record you are updating, plus the fields you want to change. How To Bulk Update Case Records is the full guide to that path.

To make sure you are targeting the right record, the importer uses validating fields:

The safest way to build an update sheet is to export what you already have from NextAgency and edit that, rather than retyping. A record that does not exist, or a name spelled differently than it is in the portal, will simply not be found.

One row per record

This is the layout the importer expects, and it is where most homemade spreadsheets need work.

A case with three policies is three rows, repeating the case name on each row, with the benefit fields different on each. Not one row with three sets of benefit columns.

If your data is laid out the wide way and reshaping it looks like a big job, that is a good moment to talk to us about the managed path. NextConcierge is our team doing the reshaping and the loading for you.

Sheet hygiene

• Headers on row 1. No banner, logo, or title rows above them.
• No duplicate or empty column headers. Two columns with the same name, or a populated column with no header, will stop the import.
• No leading or trailing spaces in your values. A carrier called Aetna with a space on the end is not the same as Aetna. This one causes more failed rows than anything else on the list. Select your data and use Find and Replace, or run the values through TRIM, before you import.
• One tab of data. The importer reads a single tab, so put everything on one.
• No merged cells.
• No subtotal or total rows mixed in with the data.
• No blank rows at the bottom. Press Ctrl + End to see where your data really ends.
• No hidden rows or columns, and no filters left on.

3. The columns that cause the most trouble

Protecting a column from your spreadsheet program

Spreadsheet software converts anything that looks like a number into a number, which is what strips leading zeros and turns long numbers into things like 9.12E+08.

In Excel, before you type anything into a column: select it, then Home, then set the number format to Text.

If you are opening a CSV that already contains policy numbers or ZIP codes, do not double-click it. Instead:

1. Open a blank workbook
2. Data, then From Text/CSV
3. Choose the file, then Transform Data
4. Set the ID, policy number, phone, and ZIP columns to Text
5. Load

In Google Sheets, use File, then Import, and uncheck "Convert text to numbers, dates, and formulas."

4. Import in the right order

There are really only two imports here.

1. Your case import. Cases, contacts, dependents, notes, tasks, and benefits, all on one sheet. Put as much of your data on this as you have.
2. Commissions. These come afterward, because every commission row attaches to a policy that already exists in the portal.

Files and documents sit outside both of those and are handled on their own.

You can choose whether an import creates new records or updates existing ones. When you are updating, the values the importer matches on have to match what is already in your portal exactly, so it is worth exporting what you have and working from that rather than retyping. Benefit updates in particular match on carrier, plan name, policy number, and product type.

Get the policy records right on the way in

This is the single highest-value thing you can do while building your case sheet.

NextCommission works best when the policy information in NextAgency matches exactly what your carrier has. Same policy number, same product type. When it does, running commissions month to month is quick and largely hands-off. When it does not, every month starts with a round of matching by hand.

Your case import is the cheapest moment to get that right, because you are typing the policy details anyway.

Test with a small batch first

Do not load 4,000 rows on your first attempt.

1. Copy your sheet and delete all but about ten rows, choosing a mix: a simple case, a case with several policies, one with unusual characters in the name.
2. Import that.
3. Open the records in NextAgency and check them properly, including the fields you mapped by hand.
4. Fix whatever the test revealed in your full sheet, then import the rest.

That one habit prevents nearly every large cleanup job we see.

5. Commissions

Commission imports work differently from the rest, and it is worth reading this section before you build the file.

You import one carrier at a time. You choose the carrier, then load the statement data for it. So if you have one big spreadsheet with several carriers mixed together, split it into one file per carrier before you start. This comes up most often after a carrier acquisition, where the same book pays under two or three related carrier names.

Your commission sheet needs at least these columns:

• Product type
• Paid-to date
• Policy number
• Commission amount

Every commission row attaches to a policy that already exists in NextAgency. The link is made on three things:

There is no partial or approximate matching. If the policy number on your policy record does not match what the carrier put on the statement, the commission has nowhere to land.

Two things that commonly interrupt a commission import: a stray space before or after a policy number, and the same policy number appearing on more than one policy, in which case you will be asked to pick the right one yourself.

So the real work is in your policy records, not your commission sheet. Before your first commission import:

• Make sure your policies have policy numbers, and that they match the carrier statements.
• Make sure the product type on each policy is right, especially where a client holds more than one product with the same carrier.
• Check for clients or employees in your portal with no policies attached. A commission has nothing to match to on those.

This is why the first commission import takes longer than the ones that follow. It is policy cleanup, not the import itself. Once your policy records are right, monthly imports are quick.

Two situations worth flagging before you start:

• A statement that does not say which product a payment is for, where the client holds two products with that carrier. Nothing in the data can resolve that, so you will need to decide how you want it allocated.
• A long history of back commissions. Loading years of statements one carrier at a time is a large job by hand. Most agencies bringing over historical commission data have us do it through NextConcierge instead.

Splits and sub-agents can wait

You do not need to load a sub-agent before you load commission splits. Both can go in at the same time, and a split added or changed afterward applies to the commissions that are already in the system. Loading sub-agents first keeps your naming tidy, but it is not a sequencing requirement.

Each split needs a rate and a basis: percentage of commission, percentage of premium, or a flat amount.

If you hold full records on your sub-agents, addresses and the rest, load them through the dedicated sub-agent importer rather than letting a case import create them from a name.

6. What a spreadsheet import will not cover

Some things are not a spreadsheet job:

• Documents and attachments. Files cannot be attached to the right client through an import sheet.
• Activity logs from another system. Notes and tasks are fine, and both come in on the case import. What cannot come across is the log another platform keeps of who did what and when. Worth knowing if your current system calls its tasks "activities," because those are tasks to us, and they do import.
• Wide exports that need reshaping, where one client's data is spread across dozens of columns.
• Very large books. Past roughly a thousand cases, the volume alone makes a self-service pass impractical.

Any of those, or any combination of them, is what NextConcierge, our managed migration service, is for. It is also worth a conversation if you started down the self-service path and found your export is in worse shape than you expected. There is no penalty for switching, and the earlier you do it, the less rework there is.

Before you import: checklist

Printing this page gives you the checklist to work through while you build your sheet.

A few terms