How I turned our company’s accounts receivable data into a dashboard that’s improving our business performance

I’m not an accountant or a bookkeeper. I’m a “Customer Loyalty Guru” at Klipfolio, which in the simplest of terms means I’m responsible for keeping our customers.

I care about our accounts receivable because in many cases overdue accounts are a lagging indicator of customer dissatisfaction.

I was still relatively new to this position when my boss dumped a bunch of customer account data on me and asked me to make sense of it. In Klipfolio terms he was asking me to put together a report or Salesforce dashboard on the data that would help us get better as a Customer Success team.

Of course the request made sense.

We’re a dashboarding company. We’re all about monitoring data to improve the data as we go. But I had never built a dashboard with Klipfolio before. And more daunting still, I wasn’t sure what metrics or KPIs my boss wanted to see.

As I saw it, my options were:

1) Run and hide (ie. do nothing and hope that he didn’t follow up)

2) Fumble around with the project (ie. kick it around and get back to him with something, anything, so I could get back to my other tasks)

3) Embrace the data and build a kick ass dashboard that exceeds expectations

I’m sure you know I wouldn’t be writing this if I had proceeded with options 1 or 2.

So here’s what I built, how and why.

Our Accounts Receivable Dashboard

The dashboard I ended up building is a weekly view of customer accounts that owe us money.

What’s really important to point out in this section is that each KPI is given context - they are shown relative to the results of previous periods. The top most numbers are the number of accounts in each category. The middle number is the relative increase or decrease in accounts in the category; I’ve included indicators to highlight positive or negative developments from period to period. The bottom number is the total dollar value (in monthly recurring revenue terms) of the accounts in each category.

The visualization in the middle of the dashboard complements the KPIs up top. It shows the weekly trend lines of accounts for each of the three categories: Forced Cancels, Arrears and Grace.

The colors of the trend lines for each category match the corresponding KPI up top for quick and easy association. Hovering over any of the dots on the chart gives us the specific account count for that day of the week.

The tables at the bottom of the dashboard show what specific accounts are “at risk” (ie. they have an outstanding balance owing), with each table of accounts associated with a specific sales rep.

To the right of the account names are columns listing the total amount of monthly recurring revenue generated by the account, the status of the account (Active, Grace, Arrears), and the amount owing.

As the title of the report suggests, this dashboard gets published weekly, and its shared via a private url with the Customer Success team and with our accounting and operations teams. A number of team members also get the report as an automated email each week - the report is attached as a pdf.

I’ll save how and why we use this accounts receivable report for the final section of this post. First, let me tell you how I built it.

Building an Accounts Receivable Dashboard with Klipfolio

About Klipfolio

I should start by mentioning that Klipfolio is a cloud app for building real-time business dashboards with whatever data you want.

You can connect a wide variety of data sources to Klipfolio (including data in web apps like Zuora, Xero and Salesforce, Excel files from your computer, or data that lives in a database or data warehouse etc.), and then build custom data visualizations referencing that data, which you can then publish to a dashboard. Learn more here.

Connecting my data

I started this story with “my boss “dumped” a bunch of data on me and asked me to make it useful”. The truth is he shared a Google Sheet with me, which in effect is a report exported from our Salesforce account - a report which includes data from Zuora, our subscription and billing application (Salesforce is our account hub - we write a lot of account information to Salesforce records).

So the process of building my dashboard started by connecting this Google Sheet to Klipfolio.

Klipfolio has a “Connector Gallery” where you can select and connect to various web services, including files in Google Drive.

I selected the Google Drive connector, then authenticated with my credentials, and selected the Google Sheet I wanted to reference to build my dashboard.

Then I set the data ‘refresh rate’ to every 30 minutes (you can set the data to refresh as quickly as every minute depending on the data source you’re working with).

What this means is that Klipfolio will go back to my data source every 30 minutes to pull in fresh data, and any data visualizations which are referencing my data will automatically get updated.

Building data visualizations in the Editor

Once I connected my Google Sheet to Klipfolio, I could go into the Editor to build custom data visualizations, referencing the data in my Google Sheet.

Here’s an image of the Editor:

My data source (a Google Spreadsheet) can be seen down below (BTW I can also add multiple different data sources to this work space and work with them simultaneously - they would be shown as multiple tabs at the bottom of the work space).

Visualizations, which in Klipfolio are called “components”, can be dragged and dropped into the “Klip preview” area in the center of the screen, from the component menu on the right hand side.

The “formula bar” above my data is where I effectively specify what data I want to pull into the visualization, and how - with Excel-like functions and math operations. Here’s a video on the basics.

Two Morrissey albums and a couple cups of coffee later, I had built a fully functioning dashboard that made sense of the jumbled mess that was my Google Sheet. One more Radiohead album and a third cup of coffee after that, I was able to turn the dashboard into a visual work of art that Andy Warhol probably wouldn't dismiss entirely.

Or so I thought.

Picking the right KPIs for my Accounts Receivable Report

In fact the process of picking the best KPIs for this dashboard, and of creating the right visualizations to tell the story behind the numbers, was not a quick one.

I actually went back and forth with my boss several times, presenting various iterations of the dashboard, and getting feedback which I’d incorporate into new designs.

In the end what helped was constantly thinking:

What are we going to do with what we’re seeing on the dashboard?

Will it inspire us to act?

Does it give us enough context to improve what we’re doing?

This post on picking the right KPIs for your business was also helpful.

I’m proud of the final product, but the process was almost as important.

The back and forth with my boss and the team on what KPIs we should include on the dashboard, and the thinking that this project inspired, was helpful in and of itself. It forced us to think really hard about what KPIs we wanted to monitor, why and how.

Why an accounts receivable dashboard or report is so important - especially in SaaS

I’ve covered what I built and how, now let’s circle back to the initial request I received from my boss and I’ll explain the “why”. By the way, my boss is the Chief Customer Success Officer here at Klipfolio, Rupert Bonham-Carter.

I’ve been back and forth with Rupert quite a bit throughout this process, so here’s more of the context behind the initial “data dump”.

When Rupert was relatively new to Klipfolio, he didn’t have confidence that our process for engaging unpaid accounts, and of collecting outstanding balances, were optimal.

From the perspective of a SaaS executive in charge of Customer Success and revenue generation, we weren’t doing as much as we could or should to engage and save “at risk” accounts, and with respect to lost accounts, we weren’t collecting enough of the money that was owed to us.

As our support and optimization of the customer life cycle matured under Rupert’s leadership, new processes were put in place surrounding the cadence, channels and forms of communication we would deploy with at risk customers.

This was the context for my dashboard building project - Rupert and the team wanted visibility into the effectiveness of these new processes of dealing with “at risk” accounts.

He wanted to know:

Are we gradually reducing the number of forced cancellations, especially for high value accounts?

What is the total number of accounts owing this week, and how much is owed?

What account reps are responsible for what accounts with outstanding balances? Can they help accounting engage and save these accounts before we have to force a cancel?

The dashboard I built helps us answer these questions. Now we can now see patterns and trends in the charts and tables, and we can identify and prioritize accounts that deserve more attention.

I say “us” and “our” because as I mentioned earlier, this dashboard report is not just used by me, or by Rupert. A snapshot of the dashboard is sent by email to my fellow team members, my boss's boss, and my boss's boss's boss, every Friday morning.

Now we can look at the data visualizations together and determine which actions are the most appropriate to take. By the way, I was able to schedule that recurring email send in a matter of seconds and it was insanely easy to do.

Who would have thought that raw data in a Google Sheet could yield so much power when paired with the right tool?

I didn't, until I tried it.