Why Aren't My Sales Numbers Adding Up?

How do I fix the issue of my sales numbers adding up differently across systems?

A sales data discrepancy is the quantitative variance between two or more reporting systems tracking the same underlying financial events. When you find your sales numbers adding up to different totals in your CRM compared to your data warehouse, the issue usually stems from inconsistent filtering logic, timezone misalignment, or upstream data duplication.

In our experience working with mid-market SaaS companies, this "trust gap" is the single biggest blocker to data-driven decision making. If the Chief Revenue Officer (CRO) sees $1.2M in HubSpot but the Chief Financial Officer (CFO) sees $1.15M in the BI tool, neither leader will trust the dashboard. To fix this, you must establish a rigid source of truth and document every transformation step between the raw API export and the final visualization.

Why do sales numbers dont add up between HubSpot and the warehouse?

When we audit a client's data stack, we frequently find that the most common reason sales numbers dont add up is a mismatch in how "Close Date" is defined. In many CRM setups, the close date defaults to the day a deal moves to a specific stage, but manual overrides by sales reps can move that date across fiscal quarters. If your ETL (Extract, Load, Transform) process only captures the current state without tracking history, your historical reports will shift every time a rep touches an old record.

Another culprit is the "soft delete" problem. When a record is deleted in a CRM, some ETL tools like Fivetran or Airbyte mark a column as is_deleted = true rather than removing the row from your BigQuery table. If your SQL models do not explicitly filter for these deleted records, your warehouse totals will always be higher than your CRM totals.

To fix this, we recommend implementing a standard stg_ model in dbt that handles these basic filters before any business logic is applied.

-- Example dbt staging model to fix common discrepancies
SELECT
    id AS deal_id,
    property_dealname AS deal_name,
    property_amount AS amount,
    CAST(property_closedate AS TIMESTAMP) AS closed_at,
    _fivetran_deleted AS is_deleted
FROM {{ source('hubspot', 'deal') }}
WHERE _fivetran_deleted IS FALSE
  AND property_amount > 0

By standardizing these filters, you ensure that the raw data entering your analytics pipeline matches the operational reality of the sales team.

How to reconcile sales data discrepancy across multiple platforms

To reconcile sales data discrepancy, our team follows a "Grain-to-Aggregate" audit. We start by picking a specific day or week where the numbers diverge and exporting the raw list of transaction IDs from both systems. We then perform a FULL OUTER JOIN in SQL to identify which records exist in one system but not the other.

In many cases, the discrepancy is caused by multi-currency conversions. If your CRM converts Euros to Dollars using a daily spot rate, but your warehouse uses a monthly average rate, the totals will never align perfectly. This is why we often suggest our clients perform an AI Stack Audit to identify these architectural cracks before layering on complex forecasting models.

The following SQL pattern is what we use to find missing records between a CRM source and a reporting table:

WITH cm_data AS (
  SELECT deal_id, amount FROM `raw_crm.deals`
  WHERE date = '2026-06-01'
),
warehouse_data AS (
  SELECT deal_id, amount FROM `prod_analytics.fct_sales`
  WHERE sale_date = '2026-06-01'
)
SELECT 
  COALESCE(c.deal_id, w.deal_id) AS deal_id,
  c.amount AS crm_val,
  w.amount AS wh_val,
  (c.amount - w.amount) AS delta
FROM cm_data c
FULL OUTER JOIN warehouse_data w ON c.deal_id = w.deal_id
WHERE c.amount != w.amount 
   OR c.deal_id IS NULL 
   OR w.deal_id IS NULL;

This query highlights exactly which deals are causing the mismatch, allowing your data team to investigate the specific ETL or logic failure for those rows.

Steps to fix sales reporting accuracy in production environments

To fix sales reporting accuracy, you must move away from "black box" metrics and toward a transparent, version-controlled modeling layer. In our work, this almost always involves dbt (data build tool). By defining your metrics in code rather than in the BI tool's interface, you ensure that the same logic is applied whether someone is looking at a Looker dashboard or a raw SQL query.

We recommend a three-tier validation framework:

1. Source Freshness: Ensure the data in your warehouse is no more than 6 hours old.
2. Schema Validation: Use dbt tests to ensure that amount columns are never null and deal_id is always unique.
3. Business Logic Checks: Compare the sum of deals in your final fact table against the sum of deals in the raw staging table.

If you are looking to build this kind of robust foundation, we cover these exact workflows in our Data Foundation track, where we teach teams how to use Terraform and dbt to eliminate manual reporting errors.

The impact of timezone settings on revenue reporting

Timezones are the silent killer of data integrity. If your CRM is set to UTC but your BI tool defaults to Eastern Standard Time (EST), a deal closed at 11:00 PM on the last day of the month will appear in two different months depending on the system. This creates a massive headache during end-of-quarter reconciliations.

Our standard practice is to store all data in UTC within the warehouse and only apply timezone offsets at the very last layer of the visualization tool. This prevents "double counting" or "missing days" when aggregating data across global sales teams. When we build reporting autopilots for our clients, we make timezone normalization a non-negotiable step in the ELT pipeline.

Managing manual data entry and its effect on totals

No amount of sophisticated SQL can fix bad data entry at the source. If a sales rep enters a deal amount as "0" because they do not know the final price yet, your conversion rate and average deal size metrics will be skewed.

We often suggest creating "Data Health" dashboards that highlight missing fields or outliers. For example, a dashboard that shows all "Closed-Won" deals with an amount of $0 or a close date in the future. By surfacing these errors to sales managers, you can fix the problem at the root before it ever reaches your executive reports. This is a key part of maintaining a healthy MDS (Modern Data Stack).

Frequently Asked Questions About Sales Reporting Discrepancies

Why does my CRM show more revenue than my SQL database?

This usually happens because of "soft deletes" or sync delays. Many ETL tools only sync changes, so if a record was deleted or its status changed during a sync outage, the warehouse might still hold the old value. Additionally, check if your SQL queries are filtering for "Closed-Won" status in the same way your CRM reports are.

How do I handle currency fluctuations in my sales reports?

The best practice is to store both the "Local Currency" and the "Corporate Currency" for every transaction. Use a dedicated currency exchange rate table in your warehouse and join it to your sales table based on the date of the transaction. Avoid using the "converted amount" provided by the CRM API, as it is often difficult to audit how that conversion was calculated.

When should we hire a consultant to fix our sales data?

If your team is spending more than 4 hours a week manually reconciling spreadsheets, it is time for professional intervention. Discrepancies in sales data lead to incorrect commissions, bad budget allocations, and a loss of trust from leadership. A focused sprint to fix your data foundation can save hundreds of hours of manual labor annually.

Can AI help reconcile these sales numbers automatically?

AI can assist in identifying patterns in discrepancies (e.g., "all mismatches occur with European deals"), but the core fix is usually architectural. Large language models (LLMs) are excellent at writing the SQL queries needed for reconciliation, but you still need a solid data pipeline to ensure the LLM is looking at accurate, up-to-date information.

Ready to stabilize your revenue reporting?

If your team is struggling with data trust or manual reporting cycles, we can help you build a source of truth that actually stays in sync. Our AI Stack Audit provides a comprehensive look at your data gaps and a roadmap to fix them in weeks, not months.

Whether you need to clean up your dbt models or automate your entire reporting suite, our team brings the practitioner expertise to get it done right the first time. Book a free consultation to discuss your specific data challenges and see how we can help your sales numbers add up correctly every single time.