Data Diaries: Are we using the same approaches to get different answers?

Data has become dynamic, exponentially huge and overwhelming in our estimation, management and understanding. Yet our data processing and usage patterns do not move much beyond basic excel functionalities.

Is it time to review and replace our processes with a more robust data management system?

In our current scenario, we have more data coming from increased data sources with far higher levels of complexities.

The number of data sources that we deal with has increased -- we have digital, which is different from client data that looks vastly different from BARC and ADEX. Then there are IRS, Aircheck, Broadview, distribution data, RAM, CRM data, etc. Our job is to somehow integrate snippets of information from all of these and generate meaningful insights.

Which is where the problem lies… the data files are heavy, and all in different formats. Some have different nomenclatures for the same fields, like advertisers, brands and channels so mapping is a task.

Our report formats haven’t evolved much.

We look for relevant answers with the same resources that existed since our bosses were trainees!

Which brings us to our question...

Can Excel handle all this data?

Excel rules our machines and our lives. Especially on the day data is released every week. Our machines groan under the weight of all those folders and files that store data coming in with amazing regularity.

We all know the pros of using Excel. It is quick and easy, there are formulae shortcuts. Cell referencing allows for easy modelling of scenarios. It has great visualisation techniques and data filters. You can also link between worksheets for multiple calculations.

More advanced applications include Macros for automation of tasks and functionality enhancing (VBA), and extended database capabilities with Power BI/View/Pivot.

Our teams are bigger and quicker at Excel and we have also built our capabilities with varying degrees of Excel Automation (Pivots, Macros, VBA, etc.).

If it’s so great, why is data management a challenge?

Simply put, Excel is an offline spreadsheet platform with limited data storage capacities.

1. File size limitations

Row numbers ranges from 1 to 1048576

Columns range from A to XFD: Total 16384 columns

To give you the scale of media data: One peak activity month of a heavy category like toilet soaps for eight markets and 1 TG has ~4 lakh of rows of data.

Add more months to this. Or TGs/markets/categories. And other data sources where the format and nomenclatures are also different. And try merging them.

Then think of the effort that will go into making this a quarterly or an annual review. The data will not even fit into one excel file, and multiple files would be needed.

2. Hard to consolidate

This links to the point made above. As your organisation grows, so do the number of Excel spreadsheets, which spreads your information further and further apart.

Hence when reports have to be compiled, the spreadsheets must be consolidated. Which is slow and painful, involving collection via emails, USBs, or copying to a commonly shared folder.

3. The file that hangs!

Memory and CPU usage can be cumbersome, especially in formulas that recalculate a large number of cells.

4. Data accuracy

Studies by PricewaterhouseCoopers and KPMG find nearly 90% of spreadsheets have errors. And those errors can be extremely costly. Users can make mistakes in how data is calculated, filtered and sorted, such as copy-pasting, cell entry, and range specification.

5. Data doesn’t refresh dynamically, and the same jobs are repeated every week

Without real-time input and refresh, reports are outdated the moment they are produced. Hence next week’s BARC data will need the same work to be done all over again for a new weekly report that’s mostly tables with lots of numbers.

6. Other problem areas

a) Blank cells create problems in the calculations

b) Cell selection is manual

c) Hidden cells in columns and rows

This article is not to dissuade you from using Excel but to give you a perspective. We all need to move towards bigger and more robust platforms that will manage our data, do the initial clean-up/merging, automate the reports generation and give us time to grasp the hidden truths.

Let’s explore that further in my next article!

(Disclaimer: The opinions expressed in this article are those of the author. The facts and opinions appearing in the article do not reflect the views of BestMediaInfo.com and we do not assume any responsibility or liability for the same.)

Info@BestMediaInfo.com