Development Spec
VesselVanguard → HubSpot Usage Analytics Integration Pathway
Metrics to Track
- logins_30_days: Count of successful login events by users belonging to the company within the last 30 days.
- last_login: The most recent timestamp a user from the company successfully logged in.
- monthly_active_users (MAU): Number of distinct users from the company who have logged in within the last 30 days.
- number_vessels_of_client: Count of active vessels associated with the client inside VesselVanguard.
- task_count_delta: The number of new tasks completed in last 30 days
- task_completion: The number of tasks completed in last 30 days
- usage_health_score (0–100)*: A composite score based on:
- Login activity
- Vessel engagement
- Critical feature usage
- Recency
- churn_prediction*
* defined below
Architecture Overview
VesselVanguard Web App
→ Event Logging
→ Analytics Service
→ HubSpot CRM
[Web App]
→ [Event Collector API]
→ [Analytics Database]
→ [Nightly Usage Processor]
→ [HubSpot Sync Service]
This pipeline avoids performance issues, rate-limits risk, and ensures HubSpot receives aggregated, business-friendly data.
Pushing Data into HubSpot (Code Pattern)
Generic API call
await
hubspotClient.crm.companies.basicApi.update(companyId, {
properties: {
vv_logins_30_days: 13,
vv_last_login: “2025-01-15T11:32:00Z”,
vv_feature_engines_used: true
}
})
Which means we only need:
- HubSpot Private App token
- The Company ID (store that in the user records)
Example Event Tracking Layer for LOGIN events
- DAN: Is the login handler AWS component or Next.JS API routing
EventLogger.track({
userId,
companyId, event: ‘user_login’,
timestamp: new Date(),
})
Database Table Structure (events)
Column
- id
- user_id
- company_id
- event
- metadata
- timestamp
Type
- uuid
- varchar
- varchar
- varchar
- jsonb
- timestamp
Nightly Analytics Processor (Cron Job Runs Once Per Night)
Compute logins_30_days example:
SELECT COUNT(*)
FROM events
WHERE company_id = $companyId
AND event = ‘user_login’
AND timestamp > NOW() – INTERVAL ’30 days’;
Compute last_login example:
SELECT MAX(timestamp)
FROM events
WHERE company_id = $companyId AND event = ‘user_login’;
Compute monthly_active_users example:
SELECT COUNT(DISTINCT user_id)
FROM events
WHERE company_id = $companyId
AND event = ‘user_login’
AND timestamp > NOW() – INTERVAL ’30 days’;
Compute number_vessels_of_client example:
SELECT COUNT(*)
FROM vessels
WHERE company_id = $companyId AND archived = false;
Dan to do:
- need to compute number_of_task example
- need compute number_of_completed_tasks example
Compute usage_health_score (formula)
Suggested formula (modifiable later) Dan to add tasks count:
Score =
- (MAU_weight * normalized_MAU) +
- (Login_weight * normalized_logins_30_days) +
- (Vessel_weight * normalized_vessel_count) +
- (Recency_weight * recency_score)
Proposed weights:
Metric
- MAU
- login_30_days
- Number_vessels
- new_tasks
- completed_tasks
- recency
Weight
- 20%
- 15%
- 5%
- 20%
- 30%
- 10%
Normalize values to 0–1 scale. Output: 0–100
HubSpot API Integration
HubSpot API endpoint: https://api.hubapi.com/crm/v3/objects/companies/{companyId}
Payload: (Dan add tasks):
{
“properties”: {
“vv_logins_30_days”: 24,
“vv_last_login”: “2025-01-28T08:10:00Z”,
“vv_monthly_active_users”: 7,
“vv_number_vessels”: 4,
“vv_usage_health_score”: 82
}
}
HubSpot Setup Required
We will need to create as many custom properties on the Company object as we want to measure:
HubSpot Property Name
- vv_login_30_days
- vv_last_login
- vv_monthly_active_users
- vv_number_vessels
- vv_usage_health_score
- Add tasks to model
Label
- Logins ( 30 days )
- Last login
- Monthly active users
- Number of vessels
- Usage health score
Type
- Number
- Datetime
- Number
- Number
- Number
Security & Performance Notes
- Batch HubSpot updates (1 API call per company per night).
- Retry failed requests (HubSpot rate-limits apply).
- Store HubSpot Company IDs in our DB.
- Never expose HubSpot tokens to client-side code.
Success Deliverables
- Add event logging to login flow
- Add event table to DB
- Implement nightly analytics cron job
- Compute the 7 metrics
- Implement HubSpot sync service
- Create custom HubSpot properties
- Build internal dashboards for Sales/CS
- Optional: add alert workflows