Can you merge different data sources into one report?

There’s more data every year and more information to report and digest. The need for being able to merge data together so that it is in one place is increasing all the time and, arguably, has become a necessity. Users of data frequently complain that they know the information is available but it can take too long to retrieve or utilise.

Where is your data?

One of the problems is that data may be available in a range of different forms and in intangible forms. It’s not so long ago that data used to arrive on an executive’s desk in the form of a printed report. The report could put into a filing cabinet so that it could be retrieved whenever it was needed. This is no longer a viable solution as there are much more data which are likely to be arriving

with increasing frequency and in different forms. Organising files neatly in folders on a computer can help an individual or a small team, but some people are tidy with electronic files and others are…..well, not so tidy.

What’s the solution?

There’s not one easy answer, but getting all the data in one accessible place is usually the first step. The best ‘one accessible place’ will be dependent on the sources of your data, the number of users of the data, the skills of the data users and the diversity of the needs of the data users. There are a range of hi-tech and lo-tech solutions, which, I believe, both have their place. A hi-tech solution might be a combined database, which skilled users can query to extract the information they need or a custom dashboard where all the needed data is available and a lo-tech solution might be an Excel workbook with hyperlinks, pivot tables, formulae etc. to provide the required information. But, firstly, it might be worth talking to the suppliers of the data, so let’s start there and then look at uses of the data.

Receiving data in a suitable form

You may or may not be in control of how you receive data, but it doesn’t take any time to ask suppliers of data if they can supply data in the form you want. We supply a lot of different research and marketing data to a range of companies which range from small research agencies to big multinational companies. The systems that are already in place by recipients of the data we provide range from Excel-based or small home-grown systems to sophisticated Business Information software systems. I try to make sure that we are providing data in the most convenient form. However, if you are receiving data from a supplier, it is worthwhile asking for data in the form that suits you or your systems best. They may usually provide data in PowerPoint, for example, but also can make the underlying data easily available in some other form if that is what you want. In my experience, I have found that suppliers of data are either very adept at supplying whatever is needed or, conversely, are not able to do so easily or will want to change a significant additional fee. Don’t ask me which type of supplier that I think every data supplier should be in this digital age!

Lo-tech solutions like Excel

Make no mistake, Excel is a brilliant piece of software. I would consider myself an expert in Excel, but like most other experts in Excel, I wouldn’t claim to know everything about Excel. However, Excel does a lot more than most people realise and, importantly, it can read many data formats, process information and is programmable. On the basis that most people have a basic knowledge of Excel, it can be a good platform to pull data together for analysis, charting or even connecting to external databases. The programmability of Excel means that companies like us can build systems and user interfaces in Excel that manage your data and give you easy access to the data using skills you probably already have. The downside of Excel is that output doesn’t look especially good, but that can be overcome as Excel can be programmed to drive out PowerPoint or PDF reports that look professional. However, it can be a limitation, but if your goal is information rather than high quality output, Excel may be the perfect solution. Ask us, we can answer most questions.

Hi-tech solutions like Custom Online Dashboards

A good way to combine data together is to have all your data available in a Custom Online Dashboard. This means that experts such as MRDC put together systems to manage all the data, so that you can access it for the purposes you need. You can either leave us to load the data or there can be automated or semi-automated systems for uploading the latest data, checking the data as it is uploaded, if necessary. It is important to have a clear idea of who will be using the dashboard and the goals of the user base. This means that it is important to do comprehensive preparatory work before the dashboard is developed. I’ve written a blog article on this topic – click here to read it. Where data and requirements are likely to change regularly, a custom dashboard will generally not be the right solution, but a well prepared and well developed dashboard can be a valuable tool when wishing to pull together different sources of data. Much of the cost will usually be in the development of the dashboard; the number of users will be less important unless there is a big or diverse user base which, perhaps, requires support.

Mid-tech solutions like Tableau, Dapresy and others

There are a number of software systems that allow dashboards to be built within the confines of the software system. Products such as Tableau and Dapresy have advanced rapidly over recent years and can provide good online dashboards. Most of the content above about custom online dashboards will be equally applicable. The pricing model is likely to be somewhat different and may be affected more by the number of users. The dashboard development can either be prepared by yourselves or by skilled users of the software. MRDC does not offer this service, but it is a viable route, particularly if you want to produce or supply a number of dashboards. Flexibility of data management and output will be restricted the scope of the software product you choose, but pulling data together from different sources should be achievable in many cases.

Hi-tech solutions like a combined database

I suspect some people would not call this a hi-tech solution as MRDC’s role would be no more than to pull data together into one big database so that it is accessible to database experts. And, there’s the rub. Whilst we would do all the work to pull the data together, you would need skilled staff that can interrogate the database using a programming language so that reports or other outputs could be produced. It’s hi-skilled, perhaps, rather than hi-tech. Unless you have programmers available who can work with a SQL server database (or you plan to recruit programmers with these skills), this is not an option. If you do have such skilled people, the sky is the limit – you should be able to produce data from different sources in any way you might want. It will give you the flexibility to produce custom reports as and when you want them – but, at a price – the cost of employing skilled programmers.

Working with market research data

I thought that a paragraph was warranted about the use of market research data with other sources of data. Market research data is usually different in structure from other sources of data. Most commercial data is comprised of lots of rows of data with a few columns. What do I mean by this? Sales data, for example, will typically be thousands (or millions) of transactions (the rows of your data) by a few categories – maybe the number ordered, the cost, the location, the date, the time etc. Market research data is the opposite shape usually. Market research is conducted on small samples – often around 1000 respondents, sometimes less – by many (or hundreds) of fields. Why am I making this point? Well, most companies that claim to manage and report data will not be familiar with working with research data that has hundreds of fields. Market research regularly has multiple response questions which may use many fields. It’s why our history in research means we can marry research data with other commercial data effectively. It might seem an unimportant point, but it is important when it comes to pulling different sources of data together or producing analysis and reports.

Who is using the data?

Another important question to ask yourself is ‘Who is using the data?’ If it is just yourself that means that there are completely different considerations from a reporting system that will be used by a lot of users, who have different analysis needs. The classic example is the CEO who just wants some topline figures to check business progress while someone at a junior level needs to know about the microdata to do his or her job effectively. Systems that pull data together from data sources need to look after all types of users well.

And, finally, the cost

Pulling data together costs money, but with modern systems that we have developed, these costs are significantly lower than they were, say, ten years ago. The benefit is not just that data is more readily accessible but that more value can be derived from the data you already have. Insights that might be missed can be unearthed. Success is dependent on having clear goals and having a well thought out path. We can help with that. An email to me would be a good starting point to start a conversation.