Handlebars.registerPartial('myPartial', '');

Industries

Retail

Ecommerce

Travel & Hospitality

SaaS

Roles

Product

Engineering

Product

Pipelines

Connector

image Hubspot
image Redshift

Connect your Hubspot CRM data with your BI tool

In this use case, we will analyze how you can extract, transform and load (ETL) your data from Hubspot CRM to Redshift data warehouse. Raw data loaded to the data warehouse will be later used with a BI tool (like Looker or Tableau) to get data visualization (e.g., the yearly revenue metric).

Extract

Select Hubspot CRM as a source dataset for the pipeline.

Transform

Once source data is loaded, it is necessary to transform the data so that the raw output data would be ready to be used with a BI tool. To do this, we will need to apply join, filter, mask and phone transformations.

1.Field selector. This processor will allow you to choose which collections and their fields will be sent to the destination. You can also define the input and output fields.

Here are the fields that we will send to Redshift destination:

  • firstname;
  • lastname;
  • city;
  • createdate;
  • company;
  • state;
  • email;
  • website;
  • jobtitle;
  • lifecyclestage;
  • Phone;
  • revenuetotal;
  • subscriptiontype;
  • industry;
  • country;

2. Filter by. Filters fields based on selected conditions and passes data to the next step of the pipeline. In this case, we will filter all the contacts who are sent to the warehouse by their subscription type (only “Enterprise” subscription users.)

You will need to select the field on which you want to apply a filter (subscription type) and enter the value that will be used to filter (Enterprise).

3. Masking. This transformer will anonymize sensitive information (phone and email address) so it would not be accessible to unauthorized users. The original data will be hidden by modifying it through hashing. This will be done by the data hashing processor that will alter the value of your data.

To use the masking transformer, select the fields, which you want to apply the hash data function (SHA-256 algorithm).

If needed, select “Create a new column” to create new fields after applying the function.

4. Phone. This data transformer will extract country information from the phone code number. In our example, we have some contacts that do not have “country” information, but all contacts have a phone number attached to them. In order to have complete information for this property, we will apply phone transformation to fill the missing “Country” values by extracting phone number code to a country name in a new column.

For example: Select Extract phone number information”, select fields to process (“Phone”) “Function name” and function to applied (Phone number country).

So the “Phone” field would be transformed from:

Input

Phone: +37061592353

to

Output:

Phone: +37061592353

Country: Lithuania

Once all of the transformations in the pipeline are processed, the data is loaded to the Redshift data warehouse.

Now you can apply data visualizations to understand which customers generated the most revenue – analyzing by country, job title, or industry.

image

User Lifecycle
Automation Platform

Acquire, Engage & Retain throughout the User Lifecycle

Create Free Account

The data driven way to
Acquire, Engage and Retain your customers

By clicking “Start free” I agree to Intempt’s
Terms of Service & Privacy Policy

Get a demo