)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
The HubSpot API’s history endpoints show when properties changed but don’t provide property values at arbitrary timestamps. You’d need complex logic to reconstruct what deal scores or custom properties were at specific moments like “2:30 PM on January 15th.”
Here’s how to build a queryable time-series dataset that lets you find property values at any specific timestamp without API complexity.
Query timestamp-based property data using Coefficient
Coefficient creates a time-series dataset by importing your HubSpot deals data every 30-60 minutes with automatic timestamps. Instead of parsing API change events to reconstruct historical states, you get actual property values captured at regular intervals. This approach lets you query any timestamp and find the closest captured values, with spreadsheet functions handling the lookup logic without any coding required.
How to make it work
Step 1. Build automated time-series dataset.
Configure a HubSpot import that runs every 30-60 minutes and includes all deal properties you want to query historically. Enable append mode so each import creates a timestamped record, building a comprehensive time-series dataset of property values.
Step 2. Implement timestamp precision capture.
Coefficient automatically adds precise import timestamps to each appended row. Increase import frequency to every 30 minutes for better timestamp precision, ensuring you can find property values within 30 minutes of any specific moment you want to query.
Step 3. Create timestamp query functions.
Use spreadsheet functions like =INDEX(MATCH()) to find property values at specific timestamps. For example, create a formula that finds the deal score closest to “2024-01-15 14:30:00” by matching the nearest timestamp in your historical dataset.
Step 4. Build advanced querying capabilities.
Create lookup formulas that return property values for multiple deals at the same timestamp, or build queries that show how properties changed over specific time ranges. Use FILTER functions to find all deals in a specific stage at any given timestamp.
Get true timestamp-based queries
This approach creates a queryable historical database that’s impossible to achieve through standard API calls. You can find property values at any timestamp, compare multiple time points, and analyze property changes over specific periods. Start building your timestamp-queryable dataset with Coefficient today.
