🎉 Bedrock Data is now Formstack Sync! · Learn More 🎉

Jeremy Martin | July 12, 2018

How to Analyze Your HubSpot SQL Warehouse in Tableau

The Problem(s):

1. HubSpot is a cutting-edge CRM with few reporting features — not a cutting-edge analytics tool with few CRM features — limiting the kinds of questions HubSpot customers can answer about their contacts and leads.

2. Analytics juggernauts seek to circumvent #1 with content packs and connector libraries — business intelligence front-ends that pull data directly from APIs — that quickly exhaust rate limits and force analysts to ration how much data they download. Fetching data from APIs slows query return time and increases the lag for dashboard refreshes.

3. Accepting of #1, perceiving #2 as utterly “hopeless”, tech-savvy mavericks venture to build custom API integration. Each is story without a happy ending. 

 

The Solution:

1. Populate a SQL warehouse with HubSpot data. Connect that SQL warehouse to an analytics tool like Tableau. Query. Create dashboards. Share reports. Get fast results without hitting API limits.

 

The Denouement:

Assuming you’re hip to The Solution, you read a blog article that teaches you two simple ways to report on HubSpot data — about site visitors, leads, and closed customers — in Tableau. The article, written in a wry, self-aware tone atypical of most internet blogs, focuses on Contacts and Opportunities as a point of departure. By the end, you are much smarter. You know how to drag HubSpot tables on Tableau's interface. You can create joins for SQL queries. And the reports you can create will please your boss, whom you consider a tough nut to crack, with little effort required on your part.

 

HubSpot Tableau 

 

HubSpot Data in Tableau: A primer

Fusion both replicates raw HubSpot data and structures it for you in a central warehouse. The next step is to connect your data warehouse to Tableau, which will return a variety of HubSpot tables for querying. To wit:

 

  • Contact
  • Lists
  • Campaign
  • Event
  • Company
  • Deal (called Opportunity)
  • Owner

 

And, for more detailed analysis, Fusion gives you access to other HubSpot objects such as:

 

  • urlMapping: get a specific URL mapping by ID.
  • dealPipeline: lets you access options for the 'deal stage' and 'pipeline' properties within your instance of the HubSpot CRM.
  • stage: returns a list of stages for a specific pipeline, along with other properties.
  • dealPropertyGroup: stores specific properties for your deal records for a specific deal group.
  • engagement: data about a note, task, or activity on an object in HubSpot.
  • form: returns form submission data.
  • remote: refers to the remoteId or remoteType for the Owner object.
  • broadcastMessage: provides details about a specific broadcast (e.g. a tweet, Facebook post, or specific message posted to a single social network).
  • content: gets events from the calendar.

 

Setting up a Contacts Report in Tableau

Now you attempt to set up a report in Tableau using the Contacts table. And because you can associate any contact in your HubSpot database with a persona or lead source, you could easily retrieve a count of lead sources, by persona, in Tableau, using the HubSpot contact table as your data source.

Here’s how:

1. Select the database (topmost arrow).

2. Double-click the table hubspot1_contact (middle arrow). Once you have,

you’ll see the contact table’s field names.

3. Click "new worksheet" or "sheet 1" (bottom arrow). This will bring you to

the second screenshot, where you can visualize data from the selected

table(s). 

 

 

 

Nota bene: most BI tools use dimensions (strings) and measures (numbers) which you must combine in order to produce your analytical output. In this case, persona and lead source are both dimensions, so we need to create a measure. 

Fortunately, we can use HubSpot’s “is-contact” field (an API-only property not visible in the UI) as a measure because it sets a contact’s value to 1 and is the same for every contact in the database.

With this in mind, now drag:

1. The persona dimension to the columns section.

2. The lead source dimension to the rows section.

3. The is-contact measure into the columns section.

The default output should be a table with counts of personas by lead source.

 

 

 

 

Report on Opportunities fused with Contacts in Tableau

Now imagine you want to look at opportunity performance, based on data originating from contact records. We can use fused objects and Fusion’s links tables, which allow you to create relationships across the various objects, to connect the two in Tableau. 

To create visualizations using multiple fused_ tables, all we need to do is use a links_ table, which joins the two fused_ tables together. Here’s how:

1. Drag fused_opportunity into the top middle section

2. Drag links_contact_opportunity into the same section. A join should automatically be created that looks like the below

 

 

 

3. Drag fused_contact into the same section. A join is automatically created, but not the one we want, so we need to edit it.

4. Click the join between fused_opportunity and fused_contact

5. From the dropdown on the left scroll down to the links_contact_opportunity section and choose contact_id

 

 

 

6. From the dropdown on the right select contact_id

This should place all three tables in a row in this order: (1) fused_opportunity (2) links_contact_opportunity (3) fused_contact. This is pictured below

7. Click “sheet 1”

 

 

Here we can now create a visualization of the data using all of the fields in the fused_contact and fused_opportunity tables.

 

 

 

 

Want to test drive HubSpot data in Tableau? You can create a SQL warehouse in just a couple clicks and try it for 14 days, totally free.

 

HubSpot-Tableau

 

Subscribe Here!