Graphed LogoGraphed
Login

How to Create an Accounts Receivable Dashboard in Looker

Cody Schneider•

Having a clear view of your accounts receivable transforms it from a source of stress into a powerful lever for your company's financial health. An Accounts Receivable (AR) dashboard in Looker organizes all those moving pieces - invoices, due dates, payments - into a single, scannable command center. This article will guide you through the key metrics, data preparation steps, and visualization techniques to build a powerful and actionable AR dashboard in Looker.

Why an Accounts Receivable Dashboard is a Game-Changer

If you're still relying on manually updated spreadsheets to track who owes you what, you know the process is slow, inefficient, and prone to errors. It's nearly impossible to get a real-time pulse on your cash flow. An automated AR dashboard built within a business intelligence tool like Looker changes that entirely.

Here are the key benefits:

  • Improved Cash Flow Visibility: Instantly see how much cash is expected, when it's due, and where your collection efforts need to be focused. This clarity moves you from a reactive to a proactive financial stance.

  • Faster Identification of Overdue Invoices: An AR Aging Report on your dashboard immediately highlights invoices that are 30, 60, or 90+ days past due. The sooner you see them, the sooner you can act.

  • Enhanced Client Payment Tracking: Quickly identify clients who are habitually late payers. This arms you with the data needed to adjust payment terms or have necessary conversations.

  • Data-Driven Forecasting: By tracking trends in metrics like Days Sales Outstanding (DSO), you can more accurately forecast future cash flow and make smarter business decisions.

Step 1: Get Your Data Ready for Looker

Before you build any charts, your raw data needs to be in good shape. Great dashboards are built on a foundation of clean, well-structured data connected to Looker. This is arguably the most critical step.

Gather Your Data Sources

Your accounts receivable data likely lives across a few different platforms. You’ll need to centralize it in a data warehouse (like BigQuery, Snowflake, or Redshift) that Looker can connect to. Common sources include:

  • Accounting Software: This is your primary source. Systems like QuickBooks, Xero, or NetSuite contain all invoice and payment information.

  • CRM Platform: Your CRM (e.g., Salesforce, HubSpot) contains customer-level information like account owners, contact details, and company size, which adds valuable context.

  • ERP Systems: Larger organizations might pull invoice data from a broader Enterprise Resource Planning system.

Identify Key Data Fields

Your raw data table or view should include, at a minimum, the following fields for each invoice:

  • Invoice ID: A unique identifier for each invoice.

  • Customer Name / ID: To link invoices to specific customers.

  • Invoice Date: The date the invoice was issued.

  • Due Date: The date payment is contractually expected.

  • Invoice Amount: The total value of the invoice.

  • Amount Paid: The portion of the invoice that has been paid.

  • Date Paid: The date the final payment was received.

  • Status: A field that categorizes the invoice as 'Paid', 'Current', 'Overdue', etc.

Model Your Data in LookML

This is where Looker's magic begins. Looker uses a language called LookML to define your business logic. You aren't coding from scratch, but creating a "semantic layer" that tells Looker how your data fields relate and how to calculate your metrics. In your LookML project, you would define:

  • Dimensions: Attributes or "group by" fields, like Customer Name, Invoice Date, and Status.

  • Measures: Calculations or aggregations, like a sum of Invoice Amount or an average of Days to Pay. Defining these in LookML ensures consistency across reports.

For an AR dashboard, you’d create a new custom dimension for your Aging Buckets (e.g., a "case" statement that categorizes an invoice into '0-30 days', '31-60 days', etc., based on how many days it's overdue).

Step 2: Choosing Your Key Accounts Receivable Metrics

Once your data is modeled, it's time to select the Key Performance Indicators (KPIs) that provide the most insight. A good dashboard presents information from a high-level overview down to detailed specifics.

Core AR KPIs

  • Total Outstanding AR: This is a simple count of all money currently owed by unpaid invoices. It’s your top-line AR health number, perfect for a single-value scorecard at the top of your dashboard.

  • Accounts Receivable Aging: This visualization is non-negotiable. It breaks down your outstanding AR into time-based buckets to show how late payments are. A typical setup includes:

    • Current (Not yet due)

    • 1-30 days past due

    • 31-60 days past due

    • 61-90 days past due

    • 91+ days past due

    Seeing a large amount in the "91+ days" bucket is an immediate red flag requiring action.

  • Days Sales Outstanding (DSO): DSO measures the average number of days it takes to collect payment after a sale. A lower DSO is better, indicating faster collections. The general formula:

    (Total Accounts Receivable / Total Credit Sales) * Number of Days in Period

    Tracking DSO on a line chart over time shows whether your collection processes are improving or worsening.

  • Top 10 Customers by Outstanding Balance: A simple table listing your biggest debtors helps prioritize collection efforts on the most impactful accounts.

Advanced AR KPIs

  • Collection Effectiveness Index (CEI): Measures your team's ability to collect receivables during a period. It answers, "What percentage of owed money did we actually collect?" Higher percentage is better, with 100% ideal.

  • Predicted Payment Date: Advanced models using historical customer behavior predict when outstanding invoices will be paid, aiding cash flow forecasting.

Step 3: Building Your AR Dashboard in Looker, Tile by Tile

With your model defined and your KPIs selected, it's time to create visualizations, called "Looks" in Looker, and assemble them into a dashboard. Each Look is a specific query you save as a 'Tile'.

Creating a "Total Outstanding AR" Scorecard

  1. Click "Explore" from your AR data view.

  2. Select your “Total Invoice Amount” measure.

  3. Apply a filter where Invoice Status is 'Unpaid' or 'Overdue'.

  4. Click "Run" to generate results.

  5. Under the “Visualization” tab, select "Single Value" chart type for a clean KPI card.

  6. Click the gear icon and choose "Save to Dashboard." Place it prominently at the top.

Building an AR Aging Bar Chart

  1. Start a new analysis in your AR "Explore".

  2. Select your “AR Aging Bucket” dimension (created in LookML).

  3. Select the “Total Invoice Amount” measure.

  4. Click "Run".

  5. Choose a “Bar Chart” or "Column Chart".

  6. Customize colors, for instance making the "91+ days" bucket red for urgency.

  7. Save the visualization to your dashboard next to the total AR scorecard.

Visualizing DSO Over Time

  1. In your AR Explore, select “Invoice Month” and pivot by it.

  2. Choose your pre-calculated “DSO” measure.

  3. Click "Run".

  4. Select a "Line Chart" to visualize DSO trends.

  5. Add a reference line for your target DSO (e.g., 45 days).

  6. Save this Look to your dashboard.

Extra Credit: Tips for an Actionable Dashboard

A good dashboard shows what’s happening. A great one helps you understand why and guides what to do next.

  • Enable Drill Downs: Configure Looks so clicking on chart parts (like "61-90 days") reveals detailed lists of invoices and customers contributing to that total.

  • Add Dashboard-Level Filters: Add filters such as Customer Name, Account Owner, or Due Date. Users can dissect data without building new reports—for example, a manager might view only their team's AR.

  • Set Up Scheduled Reports and Alerts: Automate emailing the dashboard every Monday morning. Use alerts to notify the right person when a significant condition is met, such as an invoice over $10,000 being 60 days overdue.

Final Thoughts

Creating a dedicated Accounts Receivable dashboard in Looker turns complex financial data into clarity. It offers real-time insights to improve cash flow, reduce late payments, and strengthen your business’s financial health. From selecting the right metrics to crafting actionable visuals, the goal is to go beyond reporting and drive strategic action.

Perfecting your data model and building tiles can be complex and often needs dedicated analyst effort. We designed Graphed to simplify this process dramatically. Connecting tools like QuickBooks or Salesforce, you can describe what you need in plain language—e.g., "create an AR aging bar chart from our live QuickBooks data"—and see an interactive dashboard in seconds. Instead of setup headaches, we help your entire team get instant answers and focus on what truly matters: collecting revenue and growing your business.