It's not enough to see total numbers in your QuickBooks, FreshBooks, Wave, Xero, etc. accounts. Understanding the revenue contribution of each client is essential for making strategic decisions about where to allocate your time and resources.

Applying the 80/20 rule, if you're spending 80% of your time on a client that's only generating 20% of your revenue, it's time to shift your focus to clients that require less time and contribute more to your bottom line.

If you're reading this article, you probably need help doing that, so let's dive in!

Client Revenue Dashboard with Airtable

The Airtable template we've created for you is one we used for years to track and manage client revenue efficiently. It's incredibly easy to set up and maintain, ensuring you have a clear view of your client-based income going forward.

Step 1: Copy the Airtable base

🔗 Start using the Airtable base now

Click “Copy base” in the top right corner. If you’re already an Airtable user, sign in to see your copied base. If you’re not, sign up!

(You can also use our Airtable referral code if you want, we get a $10 kickback if you decide you like it and sign up!)

This will now allow you to take our template and make your own edits.

Step 2: Add your Client Revenue

Let’s start by adding your client revenue! You’ll want to do that under the “All Payments” tab. Create a new row or “record” (as Airtable calls them) for each invoice or payment you’ve collected this year.

Fill out the date of the invoice, description, amount, along with the name of the client underneath the “Revenue Stream” column. (You can just type the client name and hit enter which will create a new client from the dropdown meaning you will be able to attribute any additional invoices/rows to the same client.)

Now here’s the cool part. The way we have the Airtable setup, there’s also a “Monthly Summary” tab which aggregates all the revenue for a given month. However, we need to tell that summary tab which records (rows) to add up under each month. The next step is how we do that!

Step 3: Setup the monthly summary

For each client invoice record, copy the client name under the “Revenue Stream” column and paste it in the “Log” column that corresponds to the month it was paid.

These log columns are linked to the Monthly Summary tab, so by doing this step, you’re effectively telling Airtable to attribute that amount for that particular client under that particular month.

Once you paste the client name under the appropriate log, the amount pops up in the Monthly Summary tab. Watch it in action:

Notice that because my third invoice was paid in February, I pasted the name of that client under the “February Log” column so it would show up in the February column over on the Monthly Summary.

Do this for every client invoice record, and then under the Monthly Summary tab, you can find your monthly totals in these summations at the bottom of your view.

The base also has a Total column that adds up the invoices for one client across every month. This allows you to see how much money total each client brought in for the whole year, and then the summation is how you know how much you made in TOTAL across the year.

Now, what if you sell digital products too?

We can track that revenue right here in the same sheet!

Step 4: Get your digital product sales data CSV

Download your sales data from your payment processor one month at a time. In this example, I’m filtering my payments in Stripe to export payments made in January that were successful.

Step 5: Import the raw data from your CSV into a new tab on your Airtable

Click the + button and select “Import a spreadsheet.”

Step 6: Now, format your raw data!

The import shows us the messy raw data of our product sales, with each row showing one transaction, but we need this data formatted to match perfectly with our “All Payments” tab setup.

To do this, we’ll need the first three columns to be Date, Description, and Amount…in that order.

*One important thing to note about Airtable: the first column is your Primary Field and is locked, meaning you can’t just drag and drop to rearrange like you can the other columns.

If your primary column is not your transaction date (mine is actually my Description in the example below), you’ll first want to copy that column, add a new column, and then paste that data like I did here. Then you’ll copy your date data and paste that over the primary column.

Finally, just drag and drop the amount column so you’re left with the correct order Date, Description, Amount. If that was confusing, see it in action here:

Now you’ll also want to select the correct Field Types for the Date and Currency columns. To do that, double-click on the column header and select each corresponding field type: Date and Currency.

Yay! Our data is now correctly formatted.

Step 7: Copy sales data in All Payments Tab

Now, copy/paste your first three columns of correctly formatted sales data into the All Payments tab.

As a reminder, each record or row represents a payment made to your business. So where in the context of clients each row was a paid invoice, now in the context of products, each row is a sales transactions.

Now we need to assign a revenue stream, but to make that easy, let’s group our transactions so that transactions from the same product are grouped together.

Step 8: Sort all your payments by “Description”

Choose Sort at the top of your tab, then from the dropdown select sort by “Description” and hit Apply.

This basically alphabetizes your transactions by description, which allows us to easy fill in the Revenue Stream column with the product name.

Step 9: Fill in the Revenue Stream column for each transaction with the product name

You should be able to tell based on the Description text. You only need to type it once, then you can drag the bottom right corner down to auto fill the rest of the rows for that product.

Step 10: Log the correct month to rollup the Monthly Summary

Now we need to “log” each sale in the correct month so it will rollup in our Monthly Summary tab. As you remember from the steps above, just copy the Revenue Stream column and paste in the correct “Log” column.

If you exported your sales data one month at a time, this will make things especially easy because you can just select every cell in the Revenue Stream column that corresponds to a January transaction and paste it in the January Log column.

Here’s a walkthrough video just for you on exactly how to use this Revenue Tracker Airtable Base step-by-step: