ETL tools are very popular in the data-driven economy. They enable companies to free up data from their silos and consolidate it into a data warehouse, where it can be analyzed centrally. Here are some modern solutions from the cloud that startups and SMBs can benefit from.
Extract, Transform, and Load
How many users visit our website each month? How many signups do we have every day? Which features are used most often? You can quickly get the answers to these questions with suitable tools, such as Google Analytics or Mixpanel. Or with simple queries to the database. However, sooner or later you will be pleased with such simple metrics.
As the company grows or faces situations that make it difficult to acquire and retain customers, more complex questions arise. And they are not easy to answer. For instance, how high is the customer satisfaction of US users who were acquired through the ongoing AdWords campaign? Which customer segment has the highest customer retention rate? Which features drive the highest conversion rates during onboarding for customers with 20-50 users? How does customer support affect our churn rate? Anyone looking for answers to these questions with the goal of understanding their customers and optimizing their product or marketing campaigns must gather metrics from different specialized departments and systems to evaluate them centrally.
According to a current study by Siftery, companies use an average of 37 different software solutions.
However, in practice, this is far from easy. According to a current study by Siftery, a portal in which companies list the apps they use, companies use an average of 37 different software solutions. For large companies, the average is around 90. “Best of breed” approach offer several benefits. For example, cost-effective solutions with quick and easy implementation. However, companies must accept a decisive disadvantage: data silos. Above all, since each department works with its own favorite tools, important data often remains inaccessible to other teams.
How ETL Tools Work
ETL systems allow companies to combine data from a wide variety of sources. Whether it’s company website traffic collected with Google Analytics, customer feedback obtained through the help desk system, or user actions, logs, and events that can be recorded in their own application with tools such as Mixpanel or Kissmetrics. Such data can be freed from its silos (extract). Then, it is processed (transform) using tools in the ETL system. This way, companies con bring data into a uniform format and eliminate data redundancies.
Next, the extracted data is exported to a data warehouse, such as Google BigQuery or Amazon Redshift. Or to a data lake (load), where it can then be processed for central analysis. In addition, some solutions offer data export to relational database systems such as MySQL. Or in simple files (CSV, JSON, etc.) that can be saved locally or on a cloud storage system.
In other words, ETL tools automate these processes. This saves time and resources. You don’t have to manually export all of your data from different systems and bring it into a data warehouse. As a result, developers also benefit from ETL solutions. They do not need to implement any data integration through solution providers, APIs, or cron jobs for regular data queries.
Likewise, companies can store data in the data warehouse. And be confident of its continuous update. Therefore, they can use BI solutions, such as Google Data Studio or Klipfolio to create dashboards and reports. These allow employees to flexibly query, visualize, and evaluate relevant KPIs (Key Performance Indicators).
Modern ETL Tools for Beginners
Certainly, anyone who thinks ETL systems are only for large companies is mistaken. Today, there are several modern cloud solutions that are also targeting SMBs. For instance, Stitch, a modern ETL service launched in 2016. And promises an easy entry into the world of automated data integration. Its goal is to enable companies to provide data for data analysts and specialized departments within days instead of weeks.
Over 80 online services are supported as data sources. Including marketing tools such as Google Analytics, Marketo, and Mixpanel. As well as productivity apps such as Jira and Trello, and time tracking tools like Harvest.
With the help of a modern user interface, users create their “data pipelines” simply by dragging and dropping. They can configure the extraction, transformation, and loading processes in a fully automated way according to their own requirements. For example, they can set the system so that Google Analytics data is extracted, processed, and exported to BigQuery every three hours every day.
Certainly, these tools are convenient. Instead of loading the data into a data warehouse, they can export it directly to BI solutions, such as Chartio or Klipfolio. Another advantage for beginners is the freemium model solution. For example, if you want to get a first impression of the capabilities of the system, you can use it for free with up to five data sources, as long as you don’t exceed the limit of five million data rows per month. Companies that have to process between five and 250 million lines per month spend anywhere from $100 to $1,000 per month.
Alooma is marketed as an “Enterprise Data Pipeline Platform”. With a flexible pricing model that starts at $20 per million rows per month, the Redwood City, California-based solution isn’t just for large companies. Above all, it enables users to extract any data from databases and several cloud applications. As well as from XML, JSON, and CSV files stored in cloud storage systems such as Box and Google Drive. Besides, the connection of on-premise servers is also possible via FTP.
Additionally, there are APIs and SDKs for popular programming languages like Java and Python. And mobile systems (iOS and Android) that customers can use to send data from their applications to Alooma. With Alooma Live there is also an interesting feature available that cannot be found in all ETL tools. This is a real-time visualization tool that data scientists and developers can use to monitor and control live data streams.
Fivetran is another professional solution. Also from California and founded in 2013 by the renowned startup incubator Y Combinator. The startup now has more than 100 employees and well-known clients, such as payment provider Square. In addition to native connectors for over 70 cloud applications, users can also extract data from their databases. It supports almost all popular database systems. From classic SQL systems like MySQL, PostgreSQL, and Microsoft’s SQL Server, to document-oriented systems like MongoDB. And modern cloud databases like Amazon Aurora and DynamoDB.
Certainly, the system is convenient. It automatically generates a clear entity-relationship diagram (ERD) for each connected data source. As a result, this ensures that the data is easy to understand and query. In addition, it offers the option of creating custom connectors in a serverless environment like Amazon Lambda or Google Cloud Functions. Then, Fivetran calls the serverless functions by at user-defined intervals and loads the data into the data warehouse. Customers can choose from BigQuery, Redshift, Azure SQL Data Warehouse, and Snowflake.
Another solution that startups and SMBs can benefit from is Panoply. The product shows that innovative software doesn’t have to come from Silicon Valley. The service from Tel Aviv presents itself as a “Smart Data Warehouse” that brings ETL tools and a data warehouse solution to a common denominator.
This means that users can not only connect different data sources and extract and transform data automatically. They also can store it directly in Panoply. The extracted data can be used to create configurable data tables that are ready for immediate use. Therefore, users who choose Panoply don’t need to export data to Redshift, BigQuery, or another data warehouse. In addition, it integrates with popular BI tools, like Bime or Tableau software, to evaluate data. Monthly prices start at around $250 (25 million rows and 12.5 GB of storage).
Xplenty is another professional ETL solution that also comes from Tel Aviv. Larger companies with higher security and compliance requirements can use enterprise-class solutions such as Talend, Matillion, Nexla, and Adeptia.
Alternative Data Integration Solutions
In addition ETL systems designed for a wide audience for typical data integration and analysis use cases, there are many other alternatives on the market. They address a niche market or map use cases. For example, StarfishETL focuses on CRM data integration and migration, while Dataloader.io specializes in importing and exporting Salesforce data.
Skyvia is an affordable data integration tool. It allows to extract data from many cloud applications. But only allows exporting it in simple files (CSV, JSON, etc.). Cloud giants Amazon and Google, in turn, offer data pipelines and cloud dataflow respectively. Cost-effective data integration services enable automatic export of stored data from the respective cloud to Redshift or BigQuery. Practical connectors for external vendors SaaS services are not available.
Segment Focuses on Customer Data
Segment is not an ETL solution in the strict sense either. But it is an attractive alternative for beginners who want to better understand their customers. Unlike typical ETL systems, the startup founded in San Francisco, with around 100 million dollars raised to date, focuses exclusively on customer data. Well-known companies like Levi’s, Trivago, and IBM use Segment to achieve a company-wide and uniform understanding of their customers. However, with prices starting at $120 a month, Segment is also an option for smaller businesses.
The solution is marketed as a “Customer Data Infrastructure” platform. It allows companies to bring all customer information, regardless of where it originates and where they store it, to a common denominator. The service enables automated data integration with more than 200 systems. Developers can also integrate Segment’s native SDKs (Software Development Kit) into their apps. And record all user actions and logs themselves.
Transform Your Business with ETL Tools
In conclusion, leading companies in a wide variety of industries have one thing in common. They have a clear and detailed understanding of their customers. Therefore, they continually adapt when customer behavior changes in order to continually optimize their products, services, sales, and marketing initiatives.
However, it is easier said than done to achieve this understanding and make it available to all business departments. If you want to understand the entire “customer journey”, you must build an infrastructure to collect relevant data from each channel. Then store it in a central location, and analyze it with a business intelligence tool.
Innovative ETL cloud solutions featured in this article can help. Because they allow companies, regardless of whether they are small startups or established corporations, to fully automate these complex processes.
ETL tools enable companies to free up data from their silos and consolidate it into a data warehouse, where it can be analyzed centrally.
Data is extracted from silos, then processed to be consistent and non-redundant, and finally exported into a data warehouse or data lake for central analysis.