ERA ETL — Connecting the data

Automatically combining your data together is one of the core elements of the ERA system implementation.

The process of gathering and combining the data is called ETL, which stands for Extract, Transform and Load.

Let’s answer the most common questions related to ETL:

• Which data sources to focus on?
• How to connect the data?
• What to watch out for?

Data sources

Having created the data dictionary earlier, you will have known what data you need for your reports and at what granularity.

I’d strongly advise to resist the temptation to bring together all your data. Most of it you will never use, it will slow the implementation down and it will require more resources to store, refresh and maintain. I find it easier to start small and ensure your system is easily extendable so you can add new fields and data sources as the need arises.

Here is types of data you might want to bring together:

• Sales data from your CRM,
• Marketing data from tools like Google AdWords, Facebook, etc.
• Customer service data,
• Possibly data from your other operational systems and from your invoicing system, and
• Manual data sources, such as targets, marketing costs, etc.

Systems

When it comes to actually connecting the data together, you will want to do it in a way that’s repeatable and automated, so you can refresh your data whenever you need without additional effort from your team.

There is a great selection of tools that enable you to do this. There are cloud solutions that are specifically built to connect to your various data sources through APIs, there are CRM systems that offer some of this functionality, there are powerful ETL tools that specialise in connecting to almost any data source and present the transformed data to a Business Intelligence (BI) tool of your choice and then there are BI tools (like Microsoft Power BI) that combine the ETL and reporting in a single package. And you can also do it old-school by getting your IT team to write lots of SQL queries if you prefer to do it in-house, although this might mean reinventing the wheel.

Tips

There are more and more data sources that you might want to connect to. When choosing a tool, go for one that’s flexible in this regard so it enables you to connect new data sources easily. Ideally pick a tool where, if needed, you can build your own data connectors, rather than waiting for your system provider to build them.

It’s good to use a tool which enables you to define metrics (for example a lead to quote conversion rate) once. The benefit is that whenever you use it in reports and analyses, it’s the same definition and if you ever needed to change the calculation you only do it in a single place.

Keep it flexible. There are tools that are powerful out of the box but when you want to customise the way data gets combined and presented, they are limited. You can either bend your company around a tool, or you can pick a tool that will flex around you. I highly recommend the latter, especially because almost every company has got some unique system or a unique way of doing things and when it comes to it, they really value the freedom of customisation.

Summary:

Getting your data together is a critical part of the ERA implementation, give it the attention and time it needs but keep it simple. Pick the right tool or a combination of tools and test quickly if they suit your specific situation.

If you need an assistance with choosing the right tool, just drop me a line.