Google Analytics Excel: Monitor Trend of Page Depth Above a Threshold

June 3 2011 by mike in Example | No Comments

The trend of page depth is one of many indicators of how well you’re doing as a web site designer and content author.

Page depth is an indication that people are interested in the content on your site. The more pages they view, the more they like what they see.  Or perhaps they can’t find what they’re looking for — it really does depend on your site’s intent. Either way, you want to know the trend.

This article shows you an easy way to see the trend of your page depth.  First, you need to log into your free trial account at cloud.nextanalytics.com.  This report can also be built using our PowerPack for Windows Excelusers.

Then you choose Build, and Google Analytics. You can now lay out any dimension or metric, but we’re going to choose to view page Depth.

google analytics excel page depth on report

Next, we want to create a Google Analytics Custom Segment.  For this segment, we want to include data that has only 6 or more pages viewed in a single visit. Since page depth is a text field, we need to use a regex statement to accomplish that. Here’s how we do it:

google analytics excel dynamic segment filter by page depth using regex

Our report is by default already trended, showing the last 7 days of data (can be changed by clicking “date”).

We want to sort our by the most recent day, to see which page depth had the highest frequency in the most recent time period. When we save this report in Excel, we want this report to automatically re-calculate for the most recent 7 days — which it does do.

google analytics excel sort by last column in trend

We click the View Data, and we see the results. This data has been segmented by the Google data servers to only contain visits that had more than six views.

This data was first trended, and then sorted according to the most recent date.  We weren’t able to use the Google Sort because the data had to be pivoted first (to have the dates on the column axis).

This gives us an indication if our recent work has been improving the number of page views among those who show they’re very interested in our work.

google analytics excel shows most frequent page depths on last date of trend period

Notice that, of the people who visited more than 6 times, the most popular frequency of page depths is higher than 10, followed closely by 8 then 6.

If you’re interested in this topic, then you might also be interested in some of our published dashboards relating to Engagement.  You can see the full list on our product page, but of particular note are the ones relating to engagement. Here’s two examples:

EngagementAnalysis.xls
EngagementAnalysis.xlsx

Pivot by Year-Month for a Trend

May 30 2011 by mike in Google Analytics | No Comments

Many common analytics reports are looking for one or two dimensions trended by date, usually month or week. With the Google Analytics API, the year, month and week dimensions are independant values. This article shows how to concatenate them and pivot the resulting column.

For this example, we’ll show the visitor type trend by month. Start with a query that includes the Year, Month, Visitor Type dimensions along with the Visits metric.

pivot1

You may notice that the Year and Month columns appear in Excel as numbers, and we need to turn them into text columns. On the Next Analytics Columns tab, select each field in turn and click the Text button to force them to be treated as text columns.

pivot2

At this point, we use some Next Analytics script commands to transform the data into what we want to see. The first step is to concatenate the Year and Month columns into a single date column. For this, we navigate to the Analyze tab, and expand the Change Text node to find the Concatenate command. By clicking on the command, we see the syntax (generic version of the command) looks like this:

ConcatenateTextColumns,<ColumnA>~<ColumnB>,<Character(not ~ or ,)>,<NewCaption>

To merge our Year and Month together into a ‘Date’ column, we would change the command as follows, and click the Add button to included the modified command in our execution list:

ConcatenateTextColumns,Year~Month,-,Date

pivot3

Now we no longer need the individual Year and Month columns, so we can use the DeleteTextColumn command (from the Arrange grouping) to remove each of them:

DeleteTextColumn,Year

DeleteTextColumn,Month

And finally, to pivot the results by the new Date column, we use the Next Analytics pivot function to swap the unique values in the Date column to columns of their own:

SwapTextColumnWithColumn,Date

pivot4

This gives us the pivoted table we were looking for. Note that a Count column has been added automatically, but since we don’t need it in this scenario, we can remove it for our final report:

Select,Column,First

RemoveSelected

At this point, remember to Save your analysis — this writes all the script commands to a GoogleData_actions worksheet. From this point on, you can simply click the Next Analytics Refresh menu button to get new data.

pivot5

Learn how Next Analytics deals with large Google Analytics data

May 24 2011 by mike in Example | No Comments

In modern SEO, you ride the long tail.  You create lots of domain specific content so the search engines find you.  You create variations of some base concepts, using lots of different keyword combinations. Believe it or not, after a year of doing this, it’s not hard to be at a thousand pages. And you will do it, because see your traffic rising.

So, what do you do when you have a thousand pages? How do you track and measure that?  If you run a report for all your unique urls, for each day the past two years, you’re probably looking at half a million records. Yikes!  And, since it’s long tail, it’s all important.  Nobody cares about your top ten or twenty pages. Those are the branded searches, by then your prospects are well into your funnel, out of the hands of the SEO analyst.

So,  here we are…..  half a million rows….  You have 365 days of data for about a thousand page urls. Like children, they’re the result of hard work and a big investment, you love them all equally, they’re all important.

What are some of the pitfalls? First and foremost, Google Analytics will send that much data only so fast.  It could be a two hour wait for Google Analytics to send that much data. Secondly, Excel starts to get sluggish as the number of rows rises, in Excel 2003 it will outright fail, and most importantly: You can’t use PivotTables because Excel requires you to load the data into a worksheet before you can pivot.  Finally, a serious one: If you ask for too many rows at once, Google will provide you with Sampled Data.

I’m going to tell you how Next Analytics solves these issues.

You are going to see how this Excel Addin was bulit for production demands.  We’ve successfully demonstrated that our Addin is capable of loading 500 million records, on a store-bought laptop, in 35 minutes (after it was downloaded and cached from Google Analytics – see below for how to do this).

In-Memory Analytics

One of the big advantages is that we don’t read large amounts of data straight into the Excel worksheet.  Instead, we have an in-memory analytics engine that lets you do things like:

- filtering (while loading into wks, e.g. filter to include only page-urls that contain the word video)

- fixing (essentially an automated search/replace, e.g. combining urls that contain the word newspaper into a single row)

- pivoting (putting dates onto a column axes)

- calculations (convert raw metrics into comparisons and various normalizations)

Automatic Pivoting of your Data

Even if you are downloading 500k individual records, once you pivot, it might only be 5,000 rows by 12 columns.  Why the shrinkage?

Because we’ve converted the 365 different days of the year into their month equivalents and aggregated the values, before the data was written to a worksheet.   By aggregation, I mean calculating sum, min, max, average, median and others?

Why can’t you just use Google’s month dimension? Because it calculates this up on the server, and its calculations ignores your individual settings down on your client such as Data Fixes, Filtering, Pivoting, and Calculations.  For many business contexts, aggregation has to be done locally, in conjunction with those things.

Auto-pivoting is one technique for letting you load data that would normally exceed Excel’s limits.

Batch Scheduling

GA sends data in 10k chunks. A million records can take an hour or two (several gigabytes).  This can’t be avoided.

So we offer a scheduler.  You create a dashboard that you know is going to take a long time, and that program will open and refresh the data in that dashboard for you, in the middle of the night, every day.  When you show up in the morning, the numbers are all done.  Even if you wanted “the data” there it is, in the CSV file.

Cached Data

You can avoid Google Analytics data sampling by creating smaller queries, caching them to your local PC, and concatenating the results before doing the math for your dashboard.  For older results, you don’t need to re-download, only the incrementally new data.

When you examine our scripts, you wil see that the GetGoogleAnalytics data command saves to a CSV file.   You may also notice that the verb that follows it is “ImportDataFiles”.  Notice the plural.  This will automatically load multiple CSV files, made from separate Google Analytics queries. Cool eh?

Conclusion

Not all Excel Addins are created equally. Too many thing that just connecting to the data and loading the data into a worksheet is enough.

Unlike those other Addins, we add a whole layer of business intelligence to the data.  This makes your dashboards more powerful, with metrics and KPIs that are more useful.

Google Analytics Excel: Analyze web page popularity

May 23 2011 by mike in Example | No Comments

The latest thinking in SEO is to invest in content.  It’s far more achievable to be a leader in a narrow well defined domain of expertise than to compete for broad topics. As such, successful companies are investing in content on their site. This blog article focuses on how to monitor and audit what you’ve already written.

Also, my advice is to not focus on your MOST POPULAR Pages. That’s boring and rarely does anything unusual happen. Deliver those kind of reports, and those are the ones that people never read.

Instead, you should focus on the LONG TAIL. That’s the pages which aren’t your top pages, but have a steady traffic. If you have many pages like that, then you’ve been doing a good job of investing in content. That’s the theory being put in practice. Build plenty of good quality content, and people will find you.

So how do you measure that? Everyone wants to measure the relative popularity of your pages. Long tail analytics means knowing more about the pages which are being landed less frequently than your Top 10 pages.

Here’s how. First, your page URLs identify your content. If you don’t use mnemonic names in your urls, you probably should use Page Titles.  Side point: Some sites might need to strip their tags from what Google Analytics reports so that every single visit is a unique URL. This is easily done using Google Analytic’s filters.

You want to see your URLS ranked by pageviews, unique pageviews, visitors, and unique visitors, and unique visits.

The following image shows you how to ask for that in our tool.

selecting google analytics pagepath pageviews uniquepageviews visitors visits

After that, you want to be able to ask for it by dates. The following dialog is how to ask for it either by a rolling time period or by specific range of dates.

selecting either rolling periods or fixed date range

And, finally, it you want to put the most recent URLs up at the top of the list so you can compare popularity.

always sort my data by most recent data column and other actions

So now you have a ranked list of your URLs according to your choice of measures.

This is your long tail content.

  • See  pages are trending up or down in popularity.
  • Which pages no longer get visits.
  • Which ones got visits in this period but not in last period, and vice-versa.

All this is available in just a few clicks.   Now you know how your long tail content is doing. You’ll see the trends and patterns. It will help you plan what to write about.

While you are executing on your long tail strategy, it would be good to know which authors, categories, and publish-dates were and still are being the most successful for you.

See how easy it is to add custom dimensions to Google Analytics

May 16 2011 by mike in Example | No Comments

How can you take the data being supplied by Google Analytics and add more dimensions and hierarchy?  This article provides a practical step by step explanation how to do that.

Many web sites have more than one content contributor so it’s useful to be able to evaluate the popularity of that author. Or, content can be categorized and subcategorized and it’s good to see which categories are popular. Finally, it is a good to get a sense of the relationship of popularity with the age of a content item.

GA logs metrics against pages but it does not store author’s name, creation date, category, and sub-category. You should be able to sort, filter, and summarize on those things. Fortunately, with Next Analytics, it’s easy to merge that information into the GA supplied data and offer you that analyses.

Lets walk through a simple example. Google can provide a list of pages visited by day.

blog 2011 05 16 sample data

We want to add Author, Category, Sub-Category to that data (and Google doesn’t have that information).

blog 2011 05 16 sample category

So, we issue a command to append one set of data to the other. Things with matching article-titles can be blended. The combined set looks like:

blog 2011 05 16 blended

Using Next Analytics, it’s easy to see that data trended and, once trended, is easy to create a chart, dashboard, or scorecard.

With trends, it’s easy to see which items are rising or declining in popularity.  We can also sort by author, date-published, category, and subcategory.

blog 2011 05 16 crosstabbed

What you’ve just witnessed is how to create custom dimensions and append them to dimensions supplied by Google Analytics.

This allows you to establish a whole new hierarchy of dimensions that are added to what’s coming from the servers.  You could summarize and sort on any of the new dimensions. For example, ask for a sub-total by Topic, Sub-topic, or Author.

For those of you who are familar with our scripting language, this is all it took:

blog 2011 05 16 script

Scripter’s notes:

1) the first row reads in a custom categories.csv file; you can supply your own.

2) the second row simulates a query that puts the data into file named smalldata.csv.  This could have been done offline in batch.

All of our scripts that issue queries save the data to a tempoary CSV file.  This includes:  Google Analytics, Facebook, Facebook Insights, Twitter, LinkedIn, Constant Contact, SQL, and even SAP BW.

3) The third line, AppendPage, blends the categories to the incoming transacitons.  It is looking for a match in the first data column in each incoming file.

4) The work in progress is saved to “appended.csv” and a crosstab is made to make the trend of dates visual and easily charted.

5) The data is sorted.  It could have been sorted by any column.

6) It finishes by saving the finished results in a worksheet.

Google Analytics Fiscal Reporting in Excel – Part II

May 11 2011 by mike in Google Analytics | No Comments

A very common challenge in corporate web reporting is determining the appropriate metrics for fiscal periods that differ from the calendar. This multi-part article discusses how we can use the Next Analytics Excel add-in to overcome our fiscal reporting challenges. In Part I, we looked at the simple case of reporting fiscal weeks where the week starts on Sunday. In this article, we look at a more generic solution for reporting time periods that start and end at arbitrary dates such as fiscal months or quarters, or fiscal weeks that start on Mondays.

Google Analytics offers dimensions to allow for grouping by calendar week, month or year, but if you have a date range that does not fit into one of those neat definitions, you will be forced to craft queries by specifying the start and end date. Rather than making one query and using the dimensions to split the periods apart, you have to make multiple queries, one for each period, and join the results. Thankfully, with Next Analytics, this is quite easy to do.

To start, you need a list of the periods and their respective start and end dates, and should include a label to be used in the reports. You can use Excel formulas to save you some time with this task – for example, your fiscal months can be calculated based on the number of weeks in each.

fiscal2 001

In a similar fashion, we can use Excel formulas to customize the queries, making cell references to the list previously entered. In a Google Analytics query, the date must be specified as a text string in a particular format, so we can’t make a simple cell reference to an Excel date cell. Instead, we use the Excel TEXT function to force the date to be expressed in the text format required. Note that the use of “yyyy” in this example for the year is country-specific – some regions use “aaaa” instead. Also note that if you want a double quote to appear in a text string that is part of an Excel formula, you must enter it in a formula as a pair of double quotes.

fiscal2 002

We then add an ImportData command to load the results of the query into memory, and assign a PageCaption (using our desired label) so it can be referenced by other script commands. Repeat this sequence of commands for each of the fiscal periods in the report (in this example, it is 12 fiscal months), and we have a series of pages in memory that can be combined using ConcatenatePagesWith into a single result set.

fiscal2 003

[In this example, the script commands have been modified to save each query to different file names, based on the fiscal period labels. While this is not necessary, it greatly aids in debugging any problems that may occur as you are developing or updating your scripts and queries.]

Note that you could save the results of each query to a worksheet, but that would force you to use Excel formulas for any additional analytics you might want to perform. By concatenating the pages together, the results are available for further analysis using Next Analytics. If your query involves simple metrics, you would have a single table to build charts or display as a table on your dashboard.

fiscal2 004

If your query includes dimensions, you can use the Next Analytics SwapTextColumnWithColumn command to make a pivot table by fiscal period. Of course, with Next Analytics you could continue to perform other analytics on the result set, such as calculating growth or difference from period-to-period, sort and filter to identify top 10 items, and many other analytic transformations.

fiscal2 005

Using the technique described here, you can build reports for any fiscal or special period defined by start and end dates. On an ongoing basis, simply update the dates and labels to be used – the rest is automatic.

The Excel workbook developed for this example is available here.

Google Analytics Fiscal Reporting in Excel – Part I

May 9 2011 by mike in Example | No Comments

A very common challenge in corporate web reporting is determining the appropriate metrics for fiscal periods that differ from the calendar. This multi-part article discusses how we can use the Next Analytics Excel add-in to overcome our fiscal reporting challenges.

Let’s start with fiscal week reporting. Most fiscal weeks start on Sunday, and if that is your case, you can use Google’s definition of week for the reports (Sunday to Saturday). If your fiscal weeks don’t start on Sunday, then you’ll have to use the more advanced techniques as described for fiscal months and quarters in part II of this article.

Fiscal Weeks Starting on Sunday

The dimensions you use to break the queries into weekly buckets are ga:week and ga:year. The ga:week dimension is the calendar week number, starting at Jan.1st, but you have to combine it with the ga:year dimension to differentiate one year form the next. There is also the ga:nthWeek dimension, which is the sequential week number in the time period of the query, starting with 0. You can use this sequence number for weekly trend reports where you only need an increasing number for the x-axis of a trend chart.

Note that when you import the week, month, year, or nth week, the columns all contain numbers and Next Analytics may automatically interpret them as such (1, 2, 3, etc.). If you want to force them to be treated as text strings (01, 02, 03, etc.), go to the Columns tab, select the dimension and click the Text button.

Changing week from numeric to text

If you concatenate the ga:year with ga:week, you can create a calendar year-week that can be mapped to a fiscal year-week number (e.g. 2010-32 becomes F2011-01). Caution: using the ga:week number can be problematic at the boundary from one calendar year to the next. Google starts the count at 1 on Jan.1st, even if it is the middle of the week. This means that the previous year would end in week number 53, which would also be a partial week (up to Dec.31st). This results in week 2010-53 being the same as week 2011-01, but the metrics are split across those two buckets. In a fiscal calendar, the week would normally be assigned to one year or the other, but it would not be split. Both rows of data would be mapped to the same fiscal week, and would need to be combined in the final report.

Year week nthWeek and actual date

Mapping a calendar year-week to a fiscal year-week

In order to map a calendar year-week dimension into a fiscal year-week dimension, we start by concatenating the year and week columns into a new column using the ConcatenateTextColumnsscript command (from the Analyze tab).

Concatenate year and week into one column

At this point, we could use a brute force approach and use a series of ChangeTextInTextColumn commands to convert each calendar value into a new fiscal value. The challenge then becomes maintaining all those script commands over time.

ChangeTextInTextColumn,year-week,2010-14,2011-01
ChangeTextInTextColumn,year-week,2010-15,2011-02
ChangeTextInTextColumn,year-week,2010-16,2011-03
ChangeTextInTextColumn,year-week,2010-17,2011-04
...

If you are good at Excel, you could dynamically build those commands using some Excel formulas. As a general adage, though, it’s best if you don’t have to manually ‘mess with the code’ on a regular basis because, if you are not careful, Excel formulas have a way of breaking as things move around over time.

A better approach would be to create a lookup table on a separate worksheet, and use that to do the mapping. With Next Analytics, we can automatically save the lookup worksheet, load it into memory, and copy the fiscal equivalent column into our results.

Joining fiscal week with calendar week

The script command sequence to do all of this is shown below. It uses a calendar year-week combination mapped to a fiscal week from a worksheet named “Lookup” and maps that into your query results. As a final step, it combines duplicate rows to merge the split week at the calendar year boundary.

; make a single column of year-week from the separate year and week columns
ConcatenateTextColumns,year~week,-,year-week
SwapTextColumnWithRowLabel,year-week
RemoveColumns,week~year
PageCaption,Query Results

; save the Lookup worksheet and load it as a reference page in memory
uiSaveWorksheetToCsv,Lookup,lookup.csv
ImportDataFiles,lookup.csv,,0,1,,,,,
PageCaption,Lookup Worksheet

; get the matching fiscal week from the Lookup page
Select,Page, Query Results
JoinTwoPagesByMatchingRowLabel, Lookup Worksheet,fiscal
SwapTextColumnWithRowLabel,fiscal

; get rid of the calendar week and combine split weeks at the calendar year boundary
RemoveColumns,year-week
CombineDuplicates,Sum
Select,Column,Count
RemoveSelected

Using this technique, you can automatically map calendar weeks into their fiscal equivalent. On an ongoing basis, simply update the Lookup worksheet to extend the mapping between the two – the rest is automatic.

In part II, we’ll look at other fiscal and special period reporting, where the date boundaries do not have  simple calendar equivalents.

Facebook Like and Comment Sources

April 28 2011 by mike in Example, Excel, Facebook | No Comments

On an Inside Facebook article this week, Josh Constine reveals how Facebook has recently improved the Facebook Insights API so it now reveals if page likes or comments came from a mobile device. With full access to the Facebook API, we thought we should show you how Next Analytics can pull this new information and make it readily available within Excel.

Using the FQL queries below, Next Analytics can make the queries to the Facebook API, which are available as daily metrics, and pull the results directly into Excel. In the example pictured above, we actually queried the past month and totaled the results using fully automated Next Analytics script commands to download and calculate the numbers for the column and pie charts.

SELECT metric, value FROM insights WHERE object_id=[FB_FAN_PAGE_ID]
  AND metric='page_like_adds_source' AND period=period('day')
  AND end_time=end_time_date('2011-12-31')
SELECT metric, value FROM insights WHERE object_id=[FB_FAN_PAGE_ID]
  AND metric='page_comment_adds_source'
  AND period=period('day') AND end_time=end_time_date('2011-12-31')

The workbook can be downloaded here and requires Next Analytics add-in for Excel to automate the API queries.

How to #Measure SEO Performance by @AniLopez

April 26 2011 by mike in Example | No Comments

Thought you’d like to know about a blog by @AniLopez of @CardinalPath (formerly VKI). It’s a very helpful explanation of key items when it comes to SEO Measurement.

See the blog aricle here:  http://dynamical.biz/blog/web-analytics/measuring-seo-performance-37.html

Ani also supplies a ready-to-use dashboard for turnkey “SEO Measurement”.  Here’s one of the charts you could get:

ani lopez measure seo resized 600

I’m proud to mention that he used our software to achieve these great dashboards.  Great Dashboard Ani !

6 Challenges of Web Analytics in Excel

April 22 2011 by mike in Commentary, FAQ, Google Analytics, Opinion | No Comments

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.

Initially published in IQ Workforce, Nov.2010

« Previous Page — Next Page »