HubSpot Integration Guide

Overview

Pivot integrates with HubSpot to provide a comprehensive view of all customer company data. This integration syncs company information from HubSpot to BigQuery, enabling powerful analytics, filtering, and visualization through the Pivot KPI dashboard.

What Does the HubSpot Integration Do?

• Sync company data: Automatically sync all 26,000+ companies from HubSpot to BigQuery
• Hourly updates: Cloud Scheduler triggers sync every hour to keep data fresh
• Rich company profiles: Access 24+ company properties including POS systems, payroll, territory, and CSM assignments
• Advanced filtering: Group, sort, and filter companies by any field using AG Grid
• Schema versioning: Automatic full sync when data schema changes

Architecture

System Overview

Data Flow

BigQuery Schema

Table: hubspot.companies

Column	Type	Description
hubspot_id	STRING	HubSpot company ID
name	STRING	Company name
domain	STRING	Company website domain
pos	STRING	POS system (Couleur POS field)
couleur	STRING	API POS status
paie	STRING	Payroll system
api_paie	STRING	API Payroll status
lifecycle_stage	STRING	Customer, Lead, Opportunity, etc.
territoire	STRING	Sales territory
responsable_du_succes_client	STRING	CSM name (resolved from owner ID)
city	STRING	Company city
state	STRING	State/Province
country	STRING	Country
industry	STRING	Industry classification
phone	STRING	Phone number
reservation	STRING	Reservation system
api_reservation	STRING	API Reservation status
pourboire	STRING	Tip management
number_of_employees	INTEGER	Employee count
annual_revenue	FLOAT	Annual revenue
prix_annuel	FLOAT	Annual pricing
prix_par_mois	FLOAT	Monthly pricing
hubspot_updated_at	TIMESTAMP	Last modified in HubSpot
hubspot_created_at	TIMESTAMP	Created in HubSpot
synced_at	TIMESTAMP	Last synced to BigQuery
sync_version	INTEGER	Schema version number

Schema Migration

The integration uses a SYNC_VERSION constant to handle schema changes:

// Current version - increment when schema changes
const SYNC_VERSION = 2;

// During sync, all records are tagged with version
const record = {
  ...companyData,
  sync_version: SYNC_VERSION,
  synced_at: new Date().toISOString()
};

When schema changes:

1. Increment SYNC_VERSION in the Cloud Function
2. Deploy the function
3. Next sync automatically detects version mismatch
4. Triggers full table rebuild with new schema
5. Re-syncs all companies with updated fields

Dashboard Features

KPI Dashboard Page

URL: /hubspot

The HubSpot page in Pivot KPI provides:

16 Pre-configured Views

View	Grouped By	Sorted By
All Companies	-	Name (A-Z)
By POS	pos	Name
By API POS	couleur	Name
By Payroll	paie	Name
By API Payroll	api_paie	Name
By Territory	territoire	Name
By Stage	lifecycle_stage	Name
By CSM	responsable_du_succes_client	Name
Customers Only	-	Revenue (desc)
Opportunities	-	Name
Leads	-	Created date (desc)
By City	city	Name
By Country	country	Name
By Industry	industry	Name
With Pricing	-	Annual price (desc)
Recent	-	Updated date (desc)

Interactive Features

• Column Picker: Sidebar panel to show/hide any of the 24 columns
• Drag-Drop Grouping: Drag columns to "Row Groups" panel to group data
• Multi-Column Sorting: Click column headers to sort
• Quick Search: Filter companies by name in real-time
• Set Filters: Filter by unique values in each column

AG Grid Configuration

// Key AG Grid modules used
import {
  SetFilterModule,
  RowGroupingModule,
  RowGroupingPanelModule,
  ColumnsToolPanelModule,
  MenuModule
} from 'ag-grid-enterprise';

// Grid configuration
<AgGridReact
  rowGroupPanelShow={'always'}
  sideBar={{
    toolPanels: [{
      id: 'columns',
      toolPanel: 'agColumnsToolPanel',
    }],
  }}
  defaultColDef={{
    enableRowGroup: true,
    sortable: true,
    filter: true,
    resizable: true,
  }}
/>

Cloud Function

Location

pivot-kpi/functions/hubspot-bq-sync/
├── index.ts          # Main function entry
├── package.json      # Dependencies
└── tsconfig.json     # TypeScript config

Environment Variables

Variable	Description
HUBSPOT_API_KEY	HubSpot Private App access token
GCP_PROJECT_ID	Google Cloud project ID
BIGQUERY_DATASET	Dataset name (hubspot)
BIGQUERY_TABLE	Table name (companies)

Deployment

# Deploy the function
gcloud functions deploy hubspot-bq-sync \
  --runtime nodejs18 \
  --trigger-http \
  --allow-unauthenticated \
  --region us-central1 \
  --project pivot-inc

Cloud Scheduler

Job Configuration

Setting	Value
Name	hubspot-sync-hourly
Region	us-central1
Schedule	0 * * * * (every hour)
Target	HTTP POST to Cloud Function
Timezone	America/Toronto

Manual Trigger

# Trigger sync manually via gcloud
gcloud scheduler jobs run hubspot-sync-hourly \
  --project pivot-inc \
  --location us-central1

HubSpot Configuration

Required Scopes

The HubSpot Private App requires these scopes:

• crm.objects.companies.read - Read company data
• crm.objects.owners.read - Read owner information

Property Mapping

HubSpot properties are mapped to BigQuery columns:

HubSpot Property	BigQuery Column
name	name
domain	domain
couleur_pos	pos
couleur	couleur
paie	paie
api_paie	api_paie
lifecyclestage	lifecycle_stage
territoire	territoire
hubspot_owner_id	Resolved to responsable_du_succes_client
city	city
state	state
country	country
industry	industry
phone	phone
reservation	reservation
api_reservation	api_reservation
pourboire	pourboire
numberofemployees	number_of_employees
annualrevenue	annual_revenue
prix_annuel	prix_annuel
prix_par_mois	prix_par_mois

Owner Resolution

The CSM field (responsable_du_succes_client) requires special handling:

// HubSpot stores owner as ID, we resolve to name
async function resolveOwnerName(ownerId: string): Promise<string> {
  const owners = await hubspot.crm.owners.ownersApi.getPage();
  const owner = owners.results.find(o => o.id === ownerId);
  return owner ? `${owner.firstName} ${owner.lastName}` : '';
}

Monitoring

Sync Status

Check the last sync time in BigQuery:

SELECT
  COUNT(*) as total_companies,
  MAX(synced_at) as last_sync,
  MAX(sync_version) as current_version
FROM `pivot-inc.hubspot.companies`

Cloud Function Logs

# View recent logs
gcloud functions logs read hubspot-bq-sync \
  --project pivot-inc \
  --limit 50

Common Log Messages

Message	Meaning
Starting HubSpot sync...	Sync job initiated
Fetched X companies from HubSpot	API fetch complete
Resolved Y owner names	Owner lookup done
Inserted X records to BigQuery	Sync successful
Schema version mismatch, rebuilding table	Full resync triggered

Troubleshooting

No Data in Dashboard

1. Check if Cloud Scheduler job is enabled
2. Verify HubSpot API key is valid
3. Check Cloud Function logs for errors
4. Query BigQuery directly to verify data exists

Missing Companies

1. Verify company exists in HubSpot CRM
2. Check HubSpot company properties are populated
3. Ensure company is not archived in HubSpot

Stale Data

1. Check Cloud Scheduler job last run time
2. Verify Cloud Function deployment succeeded
3. Check for rate limiting in HubSpot API
4. Manual trigger: gcloud scheduler jobs run hubspot-sync-hourly

Owner Names Not Resolving

1. Verify crm.objects.owners.read scope is granted
2. Check if owner is active in HubSpot
3. Owner might have been deleted/deactivated

API Reference

Pivot KPI API Endpoint

GET /api/hubspot

Returns all companies from BigQuery.

Response:

{
  "companies": [
    {
      "hubspot_id": "12345",
      "name": "Acme Restaurant",
      "domain": "acme.com",
      "pos": "Lightspeed",
      "paie": "ADP",
      "lifecycle_stage": "customer",
      "responsable_du_succes_client": "John Smith",
      ...
    }
  ],
  "total": 26915,
  "lastSync": "2025-11-26T14:00:00Z"
}

Related Documentation

• Stripe Integration - Subscription billing integration
• CSM Dashboard Metrics - Customer health metrics
• Cloud Run Architecture - Deployment infrastructure