6 Challenges of Spreadsheet Analytics for Web and Social Media Reporting

Even with all the advancements in online and enterprise web and social media analytics services, the requirement to download or export to Microsoft Excel hasn’t diminished. People feel the need to personalize the results, performing additional analysis, applying the emphasis and terminology appropriate to the organization, and blending in situational information not contained in the data sources. The process of bringing that data into Excel is fraught with challenges, but new products are emerging that significantly reduce the effort and potential for error.These products leverage the application programming interfaces, or API’s, from the likes of Google Analytics, Facebook, Twitter and LinkedIn to automatically download information from those services, delivering the results directly into a Microsoft Excel workbook for reporting. The best of these products go far beyond the simple download, though, overcoming the major challenges to personalized reporting.

1 - Simplify the query experience

Making a query through a programming interface is never a simple task. There are accounts, keys, codes, rules and limits that must be followed and they tend to use terminology that differs from the service’s web interface. The application must buffer the user from the bulk of this, helping them through the choices and maintaining their environment for them. This means support of accounts and passwords, translating technical choices into familiar terminology, and preventing choices that violate rules.Image 1: Google Analytics query in Next Analytics – grayed out choices are not valid with current selections

2 - Automate the download for ongoing use

Once the query has been constructed, it is not enough to perform a one-time download. Reports are run daily, weekly and monthly and frequently include multiple metrics. Any time period specified in the query should automatically adjust as time passes, for example: always reporting on the past 14 days or the last calendar month. If the results are truncated because of some technical limit in the programming interface, multiple queries should be performed transparently and the results merged. If the report requires information from multiple different queries, it should be possible to update them all in a single action. The user should not be faced with managing the details of the queries every time they are run – they should just happen.Image 2: Defining a floating time period in Next Analytics for Excel

3 - Automate data transformations

When you get raw data out of a programming interface, it is usually not formatted for display, but instead it is meant to be interpreted by another computer. The application needs to make this transformation of raw information into meaningful information. The most common example of this is with dates; Google Analytics likes to use dates that look like “2010-11-15”, while Twitter uses longer date-time combinations like “2010-11-15T12:46:32Z”, and Facebook’s rendition “1289819311” doesn’t even look like a date.  Not only does the application need to adapt to these various source formats, but it also needs to provide options for how the information should be displayed when it is brought into the report.Image 3: Changing the incoming date format for a Twitter query with Next Analytics

4 - Assist with clean up and categorization

In a perfect world, you could just report the data as it was delivered, but in real life, there is garbage, spam, testing traffic, irrelevant data, and more in the raw data and this needs to be filtered out. Some of the information may also need to be grouped or categorized, or the terminology changed to be more understandable by others in the organization, especially if you are reporting web page addresses. The last thing you want to do is clean it up manually in Excel because it can be extremely tedious work. The application needs to provide an automatic filter capability and some sort of lookup or mapping capability to translate from the raw technical data into meaningful descriptions.Image 4: Next Analytics provides automatic search-and-replace rules and filter capabilities

5 - Simplify segmentation and analysis

While downloading the data automatically is a major step forward, the automation can’t stop there. The broad demand for improved analytics requires more than simple reporting of totals – the data must be segmented, trended, filtered, sorted, normalized and compared. While Excel is a widely used and highly flexible tool, the cell-orientation makes the needed analytics dependent upon creating and maintaining potentially thousands of fragile formulas across multiple worksheets, a situation commonly referred to as “spreadsheet hell”. The application should significantly reduce the need for Excel formulas by providing a flexible analytics capability that is independent of the size of the dataset. With such a capability coded, or scripted, into the worksheet, both the download and the analytics can be fully automated.Image 5: Scripted analytics provide complete analysis automation in Next Analytics for Excel

6 - Don’t lock me in

The final challenge is typically the hardest for application builders to handle; after simplifying the query, automating the download, adapting to the data formats, making the data meaningful and assisting with the analytics, remember that the data belongs to the user and it should not be locked into the tool. The reason they report in Excel is because it gives them complete control over the numbers, the terminology and the presentation. The application must give the data back to the user for final presentation in Excel. They need to be able to modify the results as they see fit, regardless of the terrific analysis just performed. If they want to switch to Excel to complete their analysis, there should not be additional steps necessary to extract their data – it should just be there.Image 6: Next Analytics delivers query and analysis results directly into an Excel worksheet

Let the computer do the work

In summary, as the demand for personalized analytics reporting in Microsoft Excel continues unabated, applications promising to download web and social media data must provide much more than a data retrieval function. People should be buffered from the technical underpinnings of web service programming interfaces, and wherever possible, the application should transparently do what is necessary to deliver the desired results directly into their worksheet.Mike Sullivan, VP Support & Engineering at Nextanalytics Corporation