Travel & Hospitality
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).
Select Hubspot CRM as a source dataset for the pipeline.
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:
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:
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.