Mastering ETL, ELT, and Reverse ETL for RevOps & HubSpot CRM Success

If you are as many other RevOps professionnals I know, you are always looking to improve your processes and your data. Putting in place a data warehouse as a central repository for all your ops platforms and some ETL (or ELT) and Reverse ETL could be a nice addition to your tech stack. This blog post will guide you through your first steps.

First of all, a few definitions:

What is RevOps?

RevOps, also known as Revenue Operations, is a strategic approach that aligns sales, marketing, and customer success teams to drive revenue growth and improve overall business performance. It focuses on breaking down silos between departments and creating a seamless customer experience throughout the entire buyer's journey. By integrating tools, data, and processes, RevOps enables teams to collaborate effectively, optimize revenue generation, and enhance customer satisfaction.

What is a data warehouse?

A data warehouse is a centralized repository that stores and organizes large amounts of structured and sometimes unstructured data. It is designed to support business intelligence and reporting activities by providing a consolidated and historical view of data from various sources. A data warehouse integrates data from multiple operational systems, such as customer relationship management (CRM) systems, sales systems, and marketing systems, into a single, unified view. This allows organizations to analyze and gain insights from their data in a more efficient and effective manner. By providing a consistent and reliable source of data, a data warehouse helps businesses make informed decisions, improve operational efficiency, and enhance overall business performance.

What is ETL and ELT?

ETL and ELT are two commonly used approaches in data integration and processing. ETL stands for Extract, Transform, and Load, while ELT stands for Extract, Load, and Transform. The main difference between the two lies in the order in which the transformation step is performed.

In ETL, the data is first extracted from various sources, then transformed to fit the desired format or structure, and finally loaded into a data warehouse or target system. This approach is ideal for handling large volumes of data and complex transformations. However, it can be time-consuming and resource-intensive, as the transformation step often requires significant computational power.

On the other hand, ELT flips the order of operations. In this approach, the data is first extracted and loaded into the target system as is, without any transformation. The transformation is then performed within the target system using the power of modern database technologies. ELT is gaining popularity due to its ability to leverage the processing capabilities of modern cloud-based data warehouses and data lakes. It allows for faster loading of data and more flexible and scalable transformations.

Both ETL and ELT have their advantages and are suitable for different use cases. ETL is often preferred for traditional data warehousing scenarios, where data is processed and transformed before being loaded into a structured database. ELT, on the other hand, is well-suited for big data and analytics projects, where the focus is on loading large volumes of data quickly and performing transformations on the fly.

In conclusion, ETL and ELT are two approaches used in data integration and processing, with the main difference being the order in which the transformation step is performed. Understanding the strengths and limitations of each approach is crucial for organizations to effectively manage and analyze their data.

What is reverse ETL?

Reverse ETL, also known as Extract, Transform, and Load in reverse, is a data integration process that enables organizations to extract data from their data warehouse or target system and load it into operational systems or applications. Unlike traditional ETL or ELT processes, where data flows from operational systems to a data warehouse, reverse ETL allows organizations to push data from their centralized repository back into the systems where it originated. This approach is particularly useful for real-time data synchronization, enabling businesses to keep their operational systems up to date with the latest information from their data warehouse. With reverse ETL, organizations can leverage the insights and analytics derived from their data warehouse to drive actions and decisions in their operational systems, ultimately improving business operations and customer experiences.

How ETL and reverse ETL can serve RevOps by increasing the data quality in your CRM?

By implementing ETL and Reverse ETL processes in your RevOps strategy, you can significantly enhance the data quality in your CRM system. ETL allows you to extract, transform, and load data from various sources into your data warehouse, ensuring that the information is standardized and cleansed before being integrated into your CRM. This helps eliminate duplicates, inconsistencies, and inaccuracies, leading to a more reliable and comprehensive view of your customer data. On the other hand, Reverse ETL enables you to push insights and analytics derived from your data warehouse back into your CRM, ensuring that your operational systems are always up-to-date with the latest information. By leveraging these data integration techniques, you can streamline processes, improve decision-making, and ultimately drive revenue growth within your organization.

How to get started with implementing ETL and Reverse ETL?

Now that you have a better understanding of RevOps, data warehouses, ETL, ELT, and Reverse ETL, it's time to dive into implementing these processes. The first step is to assess your current data infrastructure and identify the areas where ETL and Reverse ETL can add value. Determine the sources of data you want to extract, transform, and load into your data warehouse, as well as the operational systems where you want to push insights and analytics back. Next, choose the right tools and technologies that align with your organization's needs and goals. Whether you opt for a traditional ETL approach or decide to leverage the benefits of ELT, make sure to design a data integration strategy that streamlines processes, improves data quality, and drives revenue growth. By taking these initial steps, you'll be well on your way to implementing ETL and Reverse ETL successfully in your RevOps tech stack.

A concrete example: bring data from Quickbooks to HubSpot CRM

Let's imagine all your billing data is in an accounting software such as Quickbooks, and your Customer Success (CS) team works in a CRM such as HubSpot.

The CS team wants to schedule Quarterly Business Reviews (QBR) with customers who have a minimum Annual Recurring Revenu (ARR) of $100 000.

Even though the Sales team works in HubSpot CRM as well, the CRM doesn't contain the actual billing data and even less payment data. So, from the CRM we can see that we've sold a $100,000 annual contract to a company, but we can't know if it's actually been invoiced, let alone paid for.

Therefore, the CS team can't have access to an accurate list of customers with an ARR of $100,000 or more.

This is where ETL/ELT and Reverse ETL can become handy.

In this situation you could:

• Extract the following data with an ETL/ELT and Reverse ETL tool such as Weld or Hightouch:
  • Customers from the CRM (i.e. HubSpot)
  • Invoices from the accounting software (i.e. Quickbooks)
  • Payments from the accounting software (i.e. Quickbooks)
• Load the data to a data warehouse such Google BigQuery or Snowflake 
• Transform the data to create a table with the following fields in your data warehouse using the ETL/ELT and Reverse ETL tool:
  • CRM Company record unique ID
  • Total amount invoiced in the last 365 days
  • Total payment received in the last 365 days
  • Total revenus in the last 365 days
• Send the data back to HubSpot company records using the Reverse ETL tool you chose.

The following lineage chart represents what is described above and was done using Weld:

The transformation of data is done in Weld using SQL: 

Now go explore on your own

In conclusion, implementing ETL and Reverse ETL processes in your RevOps strategy can greatly improve the data quality in your CRM system. By extracting, transforming, and loading data from various sources into your data warehouse, you can ensure standardized and cleansed information is integrated into your CRM, eliminating duplicates and inaccuracies. Leveraging Reverse ETL allows you to push insights and analytics back into your operational systems, keeping them up-to-date with the latest information. These data integration techniques streamline processes, enhance decision-making, and ultimately drive revenue growth within your organization. Get started by assessing your current data infrastructure, identifying areas for improvement, and choosing the right tools to align with your goals. With a strategic approach to data integration, you'll be on the path to success in optimizing your RevOps tech stack.

Contact us if you need help implementing ETL and Reverse ETL processes in your RevOps strategy!