Jared Novack
← Writing

The database isn’t the hard part: starting a data program at your agency

Why it should start with revenue, the steps to get there, and why an automated, shared revenue report matters.

After a recent talk, a VP of operations from another agency asked my opinion on his team’s plan: feed their new CRM, ERP and scheduling software into one database, then layer reporting and AI on top. His theory was that building the database would be the hard part.

But actually, the hard parts are all the things surrounding the tech. Where do you start from? Politically, how do you align interests? Where are you headed and why?

Each one of these is itself a major question. For this post, I want to start with revenue: why it comes first, the steps to get there and what you end up with. Capacity, utilization and, most importantly, margin will come in later posts.

This is written for the CFO, COO or head of operations at an agency or any professional services firm. Use it as you get your own data program up and running.

Start with revenue

You and your CEO will want to include everything. Don’t. For agencies, revenue is the natural starting point. Why?

From this, the kinds of questions you might ask become clearer:

Crucially, when we say “we will make $1M next month,” we want full attribution for every dollar of that $1M. Each one should be associated with a certain contract which connects to a client (and ideally a distinct project) that allows us to connect to other metrics.

The steps to get there

The challenge is to bring together two systems here: the ERP (Sage, QuickBooks, etc.), which contains the historical information, and your CRM (HubSpot, Salesforce), with information on future opportunities.

Your team might already do this, but in a mix of Google Sheets or Excel files to (manually) bridge those gaps. I’ve seen entire roles dedicated to maintaining these fragile schedules — requiring a human to run an elaborate downloading of CSVs and gather soft context through meetings, Slacks and familiarity with the contracts.

This is the first area for automation. Unifying these two systems takes custom code and clear definitions of process and terms. From it, a reliable picture of your revenue serves as a basis for all the work to come.

To begin, your team will build data pipelines that extract data from source systems (CRM, ERP or even custom Google Sheets), load them into a central database (Google BigQuery, Microsoft Fabric) and transform them into company-owned structure or schema. It’s this top-level, fully cleaned data that’s so valuable (companies often call these ‘gold’ tables).

Diagram titled Two systems feed one table of revenue. A solid line from the ERP, which holds recognized revenue, and a dashed line from the CRM, which holds the pipeline, meet at Pipelines and run into one table of revenue by deal and month. Landed months are solid, months that can still move are hatched, and a copy of the table is saved every day. Two systems feed one table of revenue ERP QuickBooks, Sage What’s been recognized CRM HubSpot, Salesforce What’s in the pipeline Pipelines Extract, load and transform, by your business rules Nov 13 Nov 14 A copy saved every day Revenue by deal, by month Nov 15 ERP CRM ID Deal Prob. Sep Oct Nov Dec Jan 1042 Alder Museum 100% $40K $40K $40K $40K 1057 Kestrel Bank 100% $25K $25K $25K 1063 Marlow Health 75% $30K $30K 1071 Tidewater Co. 25% $20K Landed Can still move Paler is less likely
The ERP tells you what’s landed and the CRM tells you what’s still in the pipeline. Join them on deal ID and you get one table of revenue by month, with a snapshot saved every day. (Mock data: all clients and numbers are made up.)

This transformation is key. It’s where the business applies its unique decisions and rules. The person who’s been maintaining the manual workflow is instrumental in codifying that into an automated process. What are the rules? The exceptions? How do we know there’s an exception? What data do we need? What’s just nice to have?

To join between recognized/historical data (ERP) and prospective/future data (CRM), you’ll discover the power of ID numbers. Having a random number to identify each data item (a deal, a client, a project…) might feel unnecessary, but it actually frees people from the naming requirements that are hard to enforce and generally fail over time.

As you start, have your data expert turn on daily snapshotting of those gold tables. This is a low-cost thing to start doing on day one. You can’t report on historical trends or comparisons if you don’t keep your historical data.

The output

The result of these steps brings you to the first major milestone: a reliable and consistent revenue report. Every time a deal moves forward (or backward), you can see the exact impact on your year-end numbers. With common identifiers, you can automatically track the remaining open amount within a particular customer or project. With historical data, you can compare where you are today versus last week.

A page headed Revenue Report and dated July 1, 2026, with a Mock Data label beside the date. Left: revenue by month, solid bars January through June and hatched bars stacked by probability July through December, under a dotted budget pace line at $1.67M a month. Top right: a ring showing $18.00M weighted, 90% of a $20M budget, with a $2.00M gap to close. Bottom right: up $176K since last week, with a rising twelve-week line.
Here’s what comes out the other end: Solid bars show recognized revenue; hatched bars show open pipeline at various stages (as indicated by a stage-based color ramp). On the right, the donut helps us see how far we are to the yearly goal. At the bottom right: we show the change in overall value since last week. (Mock data: all clients and numbers are made up.)

Once automated, the cost of producing this report is trivial. Pennies. Run it once a week, once a day, even once an hour. When you can take what used to be a human exercise and make it all but instant and free, you’ll find your creativity opens up to ask more interesting questions and communicate to more members of your organization the details they need to know.

This is the human win from all these computer-based efforts. I found my human time went from juggling spreadsheets to thinking strategically about how I could share important data and information across the team and with certain individuals. Who needs to know what? What are the relevant comparison points? How do you attach accountability?

The signal

Consider the organizational signal this type of report provides. When the whole team rows in the same direction, you can achieve remarkable results. That requires an accurate picture of where you are. Few organizations have this; fewer share it beyond leadership.

“What’s going on” shouldn’t be a secret. Yet, it’s a major complaint I’ve heard over the years across our team. Understanding the state and health of the business is important for employees to contribute. I heard from a team member once how they were on the fence about suggesting a new workstream to a client, but knowing we were a bit behind goal for Q3 nudged them to do it. That became an important project last year.

A chat message in a channel called reports, on demo data. Jared Novack’s Monday post greets the channel, notes two big wins whose revenue is spread well into next year, asks the team to focus on shorter-term summer opportunities, and starts explaining how to read the donut before the text fades out. Under it is the top of the Revenue Report for July 1, 2026, labeled Mock Data: revenue by month against a $1.67M budget pace, and a ring at 90% of a $20M budget with a $2.00M gap to close.
Here’s the same report as shared with the team in Slack on a Monday morning. The part that people always tell me is the most important to them: the notes with themes and where to focus. (Mock data: all clients and numbers are made up.)

From here, we can use many of the same techniques to bring in data on your people: who they are, what they’re working on, etc., to create a corresponding picture of capacity and utilization. That’s coming.

Back to that VP’s theory. Bringing this into a database is necessary, but getting it to the right audiences, with the required understanding, at the proper cadence is the hard part.