Add Hubspot Deals, Company, and Activity data into the Warehouse

[AnthonyRollins]

User story / feature request

As a Customer Success/CRM administrator, I want activity data (emails, phone calls, meetings, etc.) for companies and contacts and deals pipeline data sent to Metabase through the warehouse so that we can build dashboards that show customer success account managers snapshot of activity, GTFS status and deals status for an agency. This would allow one place to show all data about an agency instead of having a dashboard in Hubspot and Metabase for Customer Success managers to look at.

Acceptance Criteria

I want to build dashboards for each transit agency that include activity data, deals data from Hubspot, and GTFS status from the warehouse.

Notes

In Hubspot, a company is considered any organization and not only transit agencies, so to filter the companies that are transit agencies, we’ve also added a property, Company Type, which has the option for Transit Agency This is an important property to bring in because our reports on activity, only show data related to Transit Agencies in CA and not all Company interactions.

In Hubspot, we have several dashboards that report on data we use in our monthly reports. We compiled a list of questions detailing the kind of data we would like to reproduce in Metabase.

Hubspot API keys are produced by a feature called Private Apps, we can create a new API key when needed. Additional Information

• General Hubspot API Documentation
• Deals Pipeline API Documentation
• Properties API Documentation
• Email Activity API Documentation
• Meetings Activity API Documentation

If you need a user account in Hubspot, fill out the New User Request form, and @AnthonyRollins can help you.

Timeilne

There is no urgent timeline for getting this data in, but hoping to start these conversations to see where this can fit in the data team's timeline

[lauriemerrell]

Reference links/notes per conversation today with @thekaveman @AnthonyRollins @hunterowens @j-meelah and myself:

• Suspect it will be most useful to map a Hubspot company to its corresponding Airtable organization record -- see dim_organizations for how organizations end up in the warehouse (they already have a Hubspot identifier on them from prior work), and bridge_organizations_x_services_managed for an example of managing a versioned relationship.
• Our dbt docs overview page has some general terminology overviews that may be useful to orient modeling considerations.
• For integrating the Hubspot data with Airtable data, see the semi-deprecated Transit Database docs page and the active-but-less-ERD-like Airtable Data Documentation doc.

[AnthonyRollins]

Adding some additional information in here about Metabase and Hubpsot to look into.