Almost everything worth knowing about your marketing is already sitting in your CRM. It knows who booked, what they paid, what service they bought, and where they live. The problem is that it has never been added up the way an owner actually needs it: by type of customer, and by area. To get there, you need one clean file out of your system. This is how to pull it.

You only have to do this once to get started, and the same report can email itself on a schedule after that so your numbers stay current. No new software, no migration, no touching your books.

What the file needs to contain

The goal is simple. Pull the last two to three years of sales, with one row per paying service line, and these five things on every row:

  • Customer (a name or an account or location number is fine)
  • Service (the plan or work code: quarterly pest, mosquito, termite renewal, one-time rodent, and so on)
  • Date (the service or billing date)
  • Amount (the dollars on that line)
  • ZIP (or the address, so value can be read by area)

Export it as CSV or Excel. That is the whole ask. Everything downstream, from lifetime value by service to what a ZIP code is really worth, is built out of that one file.

The one thing that trips people up: grain

The single most common mistake is exporting one row per statement instead of one row per service line. If a customer got a quarterly treatment and a one-time rodent job in the same month and it comes out as a single combined line, you have lost the ability to tell those two services apart. And telling them apart is the entire point, because a recurring plan that renews for years and a one-time job that never comes back are worth wildly different amounts.

So when you pull the report, make sure it breaks out to the service line. If credits or refunds happen, let them show up as negative rows so the totals reconcile.

Where the report lives in each CRM

Every system calls it something a little different, but the report you want exists in all of them. Here are the paths for the ones we see most.

PestPac

Go to Reports, then All Reports, and open the Invoice List report. Set the date range to the last two to three years, then Export, one row per service line. This works on any PestPac license, so you do not need a premium reporting add-on.

FieldRoutes

Your reporting section can export sales to CSV. Set the date range back two to three years and make sure the output is one row per service line, not a summary. FieldRoutes also supports a direct data connection, so if you would rather not deal with exports at all, that route skips the file entirely.

Clypboard

Clypboard is the one to be careful with. A combined statement export will not work here, because it rolls multiple services into a single total and you lose the service-line grain. Instead, ask whoever runs your Clypboard for revenue by customer, one row per work code. Their custom reporting can build exactly that.

GorillaDesk

Go to Reports, open All Invoices, set the columns you want, and Export. Aim for one row per service line wherever the report lets you.

Jobber

Open Reports and find the report that breaks out invoice or transaction line items rather than one total per invoice, then export it as CSV. If the default report only gives you a single line per invoice, add the line-item detail before exporting so each service comes through on its own row.

Something else

Any system that can export sales at the service-line grain works. If you can produce a CSV with those five columns for the last two to three years, you are set. If you are not sure how to pull it from your system, that is a five-minute call, not homework.

Why two to three years

One year tells you what people bought. Two to three years tells you what they are worth, because that is the window where you can see renewals, retention, and which customers stuck around. Lifetime value is average revenue per year times how long that kind of customer tends to stay, and you cannot see the second half of that sentence in twelve months of data. Pull the longer range once and the picture gets a lot more accurate.

What to do with it

Once you have the file, the next question is what it tells you. That is the companion guide: what a customer is actually worth, by service and by area, and why that number should drive every ad dollar you spend.