Mismatch in Record Count Between HubSpot and Looker Studio Filters

Looker Q&A Looker Studio
, ,
1

Overview: We are using Coupler as middleware to extract data from HubSpot and push it into Looker Studio. The setup allows us to create reports and visualizations in Looker Studio using HubSpot data. However, we’ve encountered an issue where the record count in Looker Studio does not match HubSpot reports when filters are applied, even though the unfiltered data is accurately pulled from HubSpot to Looker Studio.

Issue: The core problem arises when we apply filters in Looker Studio to mirror HubSpot report filters. The count of records displayed in Looker Studio after applying these filters does not match HubSpot’s data. We have tried several approaches to filter the data, including:

  • CONTAINS
  • EQUALS
  • REGEXP_CONTAINS
  • REGEXP_EQUALS
  • Custom formulas for calculated fields

Despite trying different filtering techniques, none have yielded the expected results, and the data in Looker Studio does not match HubSpot’s record count.

Analysis: Upon conducting a thorough analysis, we identified that:

  1. Raw Data Accuracy: The raw data is successfully being fetched from HubSpot into Looker Studio via Coupler without any loss of data.
  2. Filter Mismatch: The discrepancy occurs specifically when we apply filters in Looker Studio based on the HubSpot report criteria. It appears that Looker Studio’s filtering mechanisms may be interpreting the data differently from how HubSpot handles it natively.

Steps Taken:

  • We have checked the field types and formats to ensure consistency between HubSpot and Looker Studio.
  • We attempted using different types of filters (CONTAINS, EQUALS, REGEXP_CONTAINS, etc.) in Looker Studio to match HubSpot’s filtering logic.
  • We created custom fields and calculated formulas in Looker Studio to replicate HubSpot’s filter logic as closely as possible.

Request for Help:

  • Are there any best practices for filtering data in Looker Studio, especially when working with data passed through Coupler?
  • Is there any difference in how Looker Studio handles filters compared to HubSpot that we should be aware of?
  • Has anyone encountered a similar issue when using HubSpot data in Looker Studio, and if so, how was it resolved?

Any suggestions or guidance on how to resolve this discrepancy would be greatly appreciated.

1 Like
2

I just want to say that this is an amazing issue write-up. Thank you for being so thorough!

I’m bumping this in case anyone has experience using Coupler and/or HubSpot.