How to Combine Data from Different Sources for Efficient Reporting and Analytics

Just several years ago, a typical company used about 16 different apps on average. Today, this number has skyrocketed to an impressive 110+ applications. And each of these hundred apps accumulates immense amounts of data on a daily basis. Bringing this fragmented data together for reporting and analysis ensures transparency which is vital for informed decisions. But even if your organization doesn’t use that many business tools, combining data from multiple sources is still necessary to obtain a complete picture and extract actionable insights from your data.

Gathering information in one place allows you to conduct advanced analysis, identify hidden patterns and correlations, build predictive models, create comprehensive dashboards, and much more.

In this article, we’ll explain how you can combine data from different sources using various methods – from manual to automated. We’ll also provide some real-life examples to show you in practice how you can do this.

What are the methods of combining data from different sources?

There are two ways to piece together data from multiple data sources – manually and automatically. Each of these two methods has a couple of possible implementations. Let’s take a closer look at each of them: 

Manual methods 

• Copy-pasting or manual entry. The easiest way of combining data manually is simply copying it from different data sources and inserting it into the new destination. For smaller datasets, manual entry could also be a feasible option. However, when you need to work with medium-size or large datasets, this method will be too time-consuming and inefficient.

• Importing as a CSV file. Another way to combine data manually is by exporting data from various sources in the CSV format and then importing it into a new location. This process is pretty simple, straightforward, and accessible since most apps work with CSV files. Important: this method only works if datasets in all your CSV files have the same structure – the same set of columns which are named the same. For example, if you want to combine two contact lists in this way, both lists should have such columns as Name, Phone, etc. If one of the lists has two separate columns for name and surname, while another list contains both in the Name column, then such datasets won’t be combined correctly. So, you might need to spend some time editing your datasets before (or after) merging them. 

Whether it’s the right method for you, depends on the scale and regularity. If there are dozens of applications to export data from and you need to do it every couple of days, then it’s better to consider automation. But if you don’t need to do it very often, and the number of data sources is manageable, then it could be a decent option.

Automated methods with data integration solutions

• Data stitching. You can combine data from different sources automatically using a data integration solution. Some tools allow you to merge, or stitch data from different apps in one destination file or database. Stitching implies that your data is automatically organized in a logical way when being imported. For instance, you need to merge two datasets from different ecommerce stores, and your data integration solution automatically puts product descriptions from both stores into the Product description column, prices – into the Price column, and so on. As a result, you get one table, or dataset, containing information from both stores, and every piece of information is placed into the corresponding column. Once this is done, you have one unified dataset.

In the example below, you can see data from surveys conducted with three different tools. The data is stitched together in one table in Google Sheets with the help of Coupler.io, a data analytics and automation platform. We’ll explain in detail how to combine data with this method in the example section.

• Combining data without stitching. Data stitching is not always necessary if you need to combine data from several sources in an automated report or dashboard. You can use a data integration solution to connect multiple data sources to your dashboard/report template without actually blending data in one file. So, technically, you will have several separate datasets connected to your report. But the report/dashboard itself will present a holistic view, combining data from multiple sources.

Take a look at the dashboard below. It combines metrics from Google Ads and LinkedIn Ads and allows you to analyze them together, as a single dataset. This dashboard is built in Looker Studio and connected directly to Google Ads and LinkedIn Ads with the help of Coupler.io. When information in the data sources changes, Coupler.io refreshes the data on the dashboard according to the specified schedule. As a result, the dashboard updates automatically without manual effort. Data sources can be connected in a similar way for building a non-visualized report – for instance, a tabular report in a spreadsheet.

So, we’ve explored the available methods in general, now let’s dive in and see them in more detail. We will skip the copy-pasting and manual entry method since it’s self-evident, but we will cover all the other methods and provide examples for them.

How to integrate data from multiple sources using CSV files

CSV is one of the most commonly used data formats: most apps allow you to easily export and import data from the CSV file. Let’s see how we can use this to merge data from two different sources into one place.

In our example, we will combine data from Pipedrive and Airtable in Excel, but the process is more or less the same with other apps as well. To combine data from different sources successfully, first of all, make sure that the column order and names correspond in both sources. And, of course, it’s best to combine data of the same type.

Here, we have a dataset in Airtable. It contains a list of contacts with various information, such as name, organization, phone, etc.

And we have a similar dataset with contact information in Pipedrive.

First, we need to export both datasets in the CSV format.

In Airtable, we go to the view menu and select Download CSV. The file will then be saved to the computer.

In Pipedrive, we also need to go to the settings of the corresponding page, press Export filter results, and select CSV from the suggested formats.

Now, we have two CSV files with the exported data. Let’s merge them into a spreadsheet. In Excel, go to the Data tab, then select Get data, From text.

In the next step, select your first CSV file and press Get data. Then, select Delimited -> Next. Specify the delimiter type. In our case, it’s Comma.

Then, press Next -> Finish, and select the cell where your data should be inserted, and press Import. Data from Airtable has been imported.

Now, we need to repeat the same process for the second CSV file. The only difference is that the cell for importing should be different in order to keep the previously imported data. In our case, we can select A21.

Now, Pipedrive data has been imported as well, so we have a single dataset.

At this point, you might need to edit your data manually – for example, to remove duplication or edit the format of some information. You can also add data from other sources to this combined dataset.

However, as we’ve already mentioned, this method is not very efficient if you need to extract data from many different applications and/or if you need to do this regularly. In this case, you can consider automation.

Collecting and combining data from multiple sources automatically

Automating your data flows for reporting and analytics allows you to save a significant amount of time, optimize processes, and increase efficiency. With the help of a data integration solution, combining data from multiple data sources can be a relatively simple task. And more importantly, it will not require repetitive manual work. You will only need to connect your data sources once and specify what information you need to combine and how. After that, your reports or dashboards comprised of different data sets can be updated automatically. This is especially handy if you need to get data from multiple sources in one place on a regular basis.

There are various data integration solutions that can help you with that – for instance, Coupler.io, SheetGo, Stitch, and some others.

In the examples below, we will show you how to combine data with the help of Coupler.io, an all-in-one data analytics and automation platform. It can help you collect, analyze, and understand your data to turn it into action points. In particular, Coupler.io offers a data integration solution with the ETL functionality that allows you to automatically extract data from 60+ sources and merge it in Excel, Google Sheets, BigQuery, or Looker Studio. Such reports combining data from multiple data sources can be updated automatically on a custom schedule.

Now, let’s see how to combine data from different sources in practice. We’ll explore two main cases: when you need to merge data in one file or database (data stitching) and when this is not necessary.

Example 1. Combining data sets from multiple sources automatically (with merging data) 

You might need to merge data from different databases in many cases – for instance, if your store sells goods on two different platforms or if you run ads in different apps, or if you need to merge contacts from several sources into one contact list. 

We’ve already shown how to combine contacts from Airtable and Pipedrive in one spreadsheet using CSV files. Now, we’ll see how to do this with automation. Let’s import the same data sets into Google Sheets to create a self-updating contact list. It will look like this:

Although in this case, we combine data from two different apps, the number of sources is not limited. 
If you prefer watching to reading, then you can find out how this works in our 3-minute video tutorial.

Here’s the step-by-step explanation for combining data from multiple sources automatically. Please note that you can use this flow for other apps you need to connect.

• Sign up for Coupler.io and add your first importer on the My importers tab. 

Select your first data source among the available 60+ apps. Then, specify the destination. You can transfer your data to a spreadsheet (Excel or Google Sheets), data warehouse (BigQuery), or data visualization app (Looker Studio – available for beta users). In this example, we are going to connect Airtable and Google Sheets.

• Connect your first source – to do so, usually you just need to log in to the app and give Coupler.io permission to extract data. For Airtable, it’s enough to provide the shared URL for the view containing the necessary information.

• Once the first source is connected and all the settings you need are specified, click + Add one more source.

• Then, select the second data source and connect your account in the same way as for the first source.

• In the next step, you will need to select the data entity you need among the suggested data types. As we want to export Contacts, we select Persons. But we don’t need all the data on Persons as we only want to export the contact details. So, we specify the columns we need to extract and their order.

• Once both data sources are set up, you can connect more apps, if needed, just click +Add one more source. Soon, you will also be able to preview the result, check how your data will merge, rename and rearrange columns, and filter data before exporting (this functionality is currently in the beta-testing stage).
• When the sources are ready, select the destination app and connect the account.  We will also need to specify the file and the sheet where to place data.

• Then, select the import mode. This will be important when your dataset updates automatically. If you select Append, each fresh version of the dataset will be inserted under the previous one. If you choose Replace, you will only have the latest version of the dataset in your destination file.

• And the last step is setting the schedule. You can specify when your data should be refreshed, choose the time zone, and select the interval for the updates (from every month to every 15 minutes). This is very convenient for continuous monitoring as it allows you to track processes in near real time. Once this is done, Coupler.io will automatically keep your dataset in the destination up to date. When the contacts in Airtable and/or Pipedrive change, the information in the spreadsheet will be adjusted accordingly during the next scheduled update. This allows you to always have access to the latest data from multiple sources in one place.

Now, let’s run the importer and view the results.

Here’s the sheet with data from two apps merged together. As you can see, email addresses, names, phone numbers, etc. from both sources are placed into corresponding columns. This worked smoothly because the columns in both sources were named the same before the data extraction.

In Airtable we also had the links to the contacts’ LinkedIn profiles, but this information was missing from the Pipedrive dataset, so this column is only filled in for one source. As we’ve already mentioned, you can connect any number of sources to blend data like this in a spreadsheet or database with the help of Coupler.io. 

The whole process takes a few minutes, and you will only need to do this once. This is a simple and convenient way to automate your reports without coding.

Example 2. Combining data from various sources in a report or dashboard automatically (without merging)

If you don’t need to merge data in one table to create a seamless dataset, then combining information from different sources automatically is even simpler. The reason for this is that, in this case, you don’t need to prepare data in advance or rename the columns so that they match. This method also allows you to combine different data types in one place. For example, a dashboard can represent many types of data, as each of them can be depicted with a separate visual element.

Combining data in a spreadsheet or database 

For combining data from multiple data sources without blending, the flow is quite similar to what we described in the previous section. You need to choose the apps to integrate, connect the accounts, and specify what data you want to extract. The only difference is that you will need to set up a separate importer for each data source.

Let’s have a look at an example. We’ll combine campaign data from LinkedIn Ads and Google Ads in a spreadsheet. Data from each source will be placed on a separate sheet of a worksheet so that you can use it to build a cross-channel report on a new sheet.

• We begin with configuring the first source, LinkedIn Ads. Here, we select Google Sheets as a destination, but the flow is the same for Excel and BigQuery as well.

• Once the LinkedIn Ads account is connected, we select the data entity. In this case, it’s Campaigns.

• Then, we need to specify the start date for the report. Once the source is configured, press Continue.
• In this case, we don’t need to add the second source, so we can go directly to connecting the destination.
• When specifying the location for the imported data, create a separate sheet for your dataset. For LinkedIn data, we add a sheet named “LinkedIn – Campaigns”.

• Proceed to the other settings, specify your preferences for the update schedule, and save the importer once everything is ready.
• After this, you will need to create a new importer for the second source. In our example, it’s Google Ads.

• After connecting the account, we need to select the data entity to export. In our example, it’s the same as for the first source – Campaigns.

• Now, let’s move to the destination. The Google Sheets account is already connected from before, so it will be even faster. We are going to transfer Google Ads data to the same spreadsheet as LinkedIn Ads data, but to a different sheet.

• Then, select the import mode and set up the schedule for the updates. When everything is ready, save and run the importer.
• As you can see in the picture below, you will eventually have two separate importers – one for each data source. And, of course, you can use this method to connect as many sources as you need, not just two.

• As a result, you will get a spreadsheet with datasets from separate sources on different sheets. Then, you can use this raw data to build a custom report on a new sheet.

• Coupler.io will update your information from multiple sources in the spreadsheet according to your schedule, and the metrics in your custom report will be adjusted automatically. So, you will have a self-updating report featuring data from multiple data sources.

If you want to visualize your data, you can use Excel’s or Google Sheets’ native functionality to build a dashboard directly in the spreadsheet.

Another option is to connect this spreadsheet to a dedicated data visualization tool – for example, PowerBI, Looker Studio, or Tableau. If you combine your data in BigQuery instead of a spreadsheet, you can link your database to these viz tools as well.

As a result, you can build an automated self-updating dashboard like this one. It combines data from Google Ads and LinkedIn Ads so that you can work with this as with one dataset. Such visualizations are very handy for presenting your results to the stakeholders, as well as for data analysis. If needed, more data sources can be connected for a comprehensive overview.
Check the interactive version of the Ad campaigns report in Looker Studio.

You can also watch our short video tutorial showing how this dashboard was created with this method.

Connecting multiple data sources directly to Looker Studio

In the previous section, we’ve explained how you can combine multiple data sources in a spreadsheet or database and then connect this data repository to a data visualization tool of your choice.

However, if you don’t need to perform any preliminary manipulations with your data, you can use Coupler.io to directly connect your multiple data sources to Looker studio and build a compelling visualization. 

To do so, just select Looker Studio as your destination, connect your accounts, and then create a new data source in Looker Studio, as shown in the video above. Once your data is imported, you can start building your dashboard. With this method, you can connect as many data sources as you need.

Looker Studio as a destination is already available for beta users. In several weeks, this functionality will be available for all Coupler.io users.

See more examples of visualized reports created with Coupler.io in the Actionable dashboards section.

If you need help with data analytics or creating advanced dashboards connected to multiple data sources, then Coupler.io’s data analytics consultancy team can help you with this.

Combining data from multiple sources: manually or automatically?

In this article, we’ve explored several methods you can use to combine data from different sources into a single report. These methods vary from simply importing CSV files into a spreadsheet to creating self-updated visualized reports, automatically connected to various data sources. For the purposes of data analytics, business intelligence, and continuous monitoring, the latter option is more efficient. It offers constant access to the latest data from across the organization, eliminates manual effort, and provides the necessary basis for informed data-driven decisions. Having said that, combining data manually is also an optimal choice if you only need to combine data from 2-4 sources and this need doesn’t arise often.