How to Create a Reconciliation Report in Looker with AI

Trying to make sure the numbers from one platform match the numbers in another is a universal headache for any business. You get conversions in Google Analytics, but do they line up with the actual leads in Salesforce? You have sales data in Shopify, but does it perfectly match the payouts from Stripe? This process of matching two datasets to spot the differences is called reconciliation, and it's essential for keeping your data honest. This article will show you how to build a reconciliation report in Looker and how using an AI-powered approach can save you a massive amount of time.

What Exactly is a Reconciliation Report?

A reconciliation report is a simple but powerful tool: its only job is to compare two or more sets of data and highlight the discrepancies. Think of it as placing your bank statement next to your checkbook register to find out why the balances don’t match. In business, the stakes are just as high.

You might use a reconciliation report to:

Compare marketing ad spend from Facebook Ads to the influenced revenue recorded in your CRM.
Match lead counts from your marketing automation tool (like HubSpot) against newly created contacts in your sales CRM (like Salesforce).
Validate Shopify order revenue against the actual transaction payments deposited by Stripe or another payment processor.
Align website session data from Google Analytics with user activity logs in your internal database.

Failing to do this can lead to misallocated budgets, inaccurate performance reviews, and flawed strategic decisions. It's the technical equivalent of "trust but verify," and it’s a critical part of maintaining data integrity across your entire business.

Creating a Reconciliation Report in Looker: The Manual Way

Looker is a powerful BI tool, and you can absolutely build reconciliation reports with it. However, the process is technical and requires a solid understanding of Looker’s modeling language, LookML. For non-technical users, this is often where the project hits a wall.

If you're comfortable in LookML, here’s a high-level overview of the manual steps involved.

Step 1: Identify Your Data Sources and Common Key

First, decide exactly which two datasets you need to compare. Let's use a common example: comparing Shopify Orders with Stripe Transactions.

Next, you must find a common key - a unique identifier that exists in both datasets. This is the piece of information that links everything together. It could be an order_id, customer_email, transaction_id, or invoice_id. Without a reliable common key, reconciliation is nearly impossible.

Step 2: Create Views for Each Data Source in LookML

In your LookML project, you'll need to create a view file for each data source. One view will represent the table containing your Shopify order data (let’s call it shopify_orders), and another view will represent your Stripe transaction table (let's call it stripe_transactions).

Inside each view, you’ll define the dimensions and measures you need for the report, such as order_amount, transaction_value, order_date, and customer_id.

Step 3: Join the Views Together in an Explore

This is the most crucial part. In your model file, you'll create an Explore where you join shopify_orders and stripe_transactions together. You won’t use a simple LEFT JOIN. For reconciliation, you almost always need a FULL OUTER JOIN.

Why? A FULL OUTER JOIN will show you all records from both tables, regardless of whether there's a match. This is exactly what you need to find:

Records that exist in Shopify but not in Stripe (a potential missing payment).
Records that exist in Stripe but not in Shopify (a payment for a non-existent order, perhaps a refund or chargeback).

Your join logic in LookML would look something like this:

Step 4: Build Calculations to Find Discrepancies

With your Explore built, you can now query the data in the Looker UI. From here, you can create table calculations to compare the values from both sources.

For example, you could create a calculation called Revenue Difference:

${shopify_orders.order_total} - ${stripe_transactions.amount}

Any result that isn't zero points to a discrepancy between what Shopify recorded as the order total and what Stripe processed as the payment.

Step 5: Filter for Mismatches

Finally, you’ll filter your report to show only the problems. You can add two main filters:

Filter where Revenue Difference is not equal to 0. This finds matching orders with mismatched amounts.
Filter where the common key from either table is NULL. For instance, filtering where stripe_transactions.shopify_order_id is blank will show you all the Shopify orders that never had a corresponding Stripe transaction.

The result is a clean list of all the discrepancies you need to investigate.

The Problem with the Manual Approach

While the steps above work, they highlight the inherent challenges of this process:

It's Slow and Technical: Building and editing LookML requires specialized knowledge and dev access. For a marketing manager or founder, it’s not realistic. The cycle of requesting a report, waiting for the data team, and asking for tweaks can take days.
It’s Rigid: What if you get a follow-up question? "Can we see the discrepancies just for last month?" or "Can we group this by product SKU?" Each new question might require another edit to the LookML or a complex set of filters and table calculations in the UI.
It Doesn’t Scale Well: Reconciling two sources is tedious enough. What about three? Trying to connect Facebook Ads spend, Google Analytics campaigns, and Salesforce closed-won deals becomes an incredibly complex web of joins and logic.

This friction is why many teams either don't reconcile their data at all or rely on painful, error-prone CSV downloads and manual spreadsheet work. The whole process is frustrating because you know the answers are in your data, but getting to them is too complicated.

A Better Way: Using AI for Instant Reconciliation

Instead of manually writing code and building reports piece by piece, an AI-powered analytics tool allows you to describe the report you need in plain English. This approach abstracts away the LookML, the SQL joins, and the complex filter logic, letting you focus only on the question you want answered.

Here’s how the AI-driven process works.

Step 1: Connect Your Data Sources

First, you connect all your relevant data platforms - Shopify, Stripe, Salesforce, Google Ads, etc. - to the AI tool. This is typically done through simple, one-click integrations that don't require any technical setup. All your data becomes available in a single place.

Step 2: Describe the Report You Need in Plain English

Now, instead of opening a code editor, you open a chat interface and ask a question. The AI acts like a data analyst, translating your goal into the necessary technical steps.

For our Shopify vs. Stripe example, you could say:

"Compare total sales from Shopify against total payments from Stripe for last month, and show me a list of any orders that don't match."

For a marketing reconciliation, you might ask:

"Show me a list of leads from HubSpot form submissions in the last 7 days that do not have a matching contact in Salesforce."

Step 3: Get an Instant Report

The AI handles the heavy lifting in seconds:

It identifies that you're asking for reconciliation.
It figures out the correct common keys to link Shopify's data with Stripe's.
It automatically performs a FULL OUTER JOIN in the background.
It creates the necessary calculations to compare the amounts.
It filters the results to only show you the discrepancies, presenting them in a clean, interactive table or chart.

You’ve effectively skipped hours of LookML development and gone straight to the answer.

Step 4: Ask Follow-Up Questions to Dig Deeper

This is where AI really shines. The initial report is just the starting point. You can continue the conversation to diagnose the problems you've uncovered.

You: "Okay, for those mismatched Shopify orders, which products were they for?"

You: "Which ones happened over Black Friday weekend?"

You: "Filter this list to only show discrepancies over $50."

Each query is handled in real-time, allowing you to explore the data at the speed of thought. This turns data analysis from a static reporting task into a dynamic, interactive conversation, helping you find and fix the root causes of data mismatches much faster.

Final Thoughts

Reconciliation reports are a non-negotiable for any data-driven organization. Making sure your numbers align across different platforms protects your revenue, validates your marketing efforts, and builds institutional trust in your data. While traditional BI tools like Looker make this possible, the process often remains too technical and slow for business users who need answers now.

We built Graphed to solve this very problem. Instead of forcing you to learn LookML or SQL, we provide an AI analyst that you can talk to in plain English. Just connect your data sources - like Salesforce, Shopify, Google Ads, and more - and ask for the reconciliation report you need. Our tool automatically handles the complex joins and calculations, delivering a live, interactive dashboard in seconds so you can spend less time wrangling data and more time acting on it.