Sunday, May 11, 2014

Excel as a powerful reporting tool for digital marketing

In my previous, opening post I announced the launch of so it may feel like an abrupt change of topic to talk about Excel's role in digital marketing. Still I believe Excel is worth a post being the unsung hero of analysts worldwide, be it market research firms or financial analysts and of course digital analysts/data scientists.

Digital marketing opened up the possibility to get detailed statistics into how the campaign budget is spent and how to spend it better. With Google's acquisition of Urchin Google Analytics (GA) was born; this has spurred an entirely new breed of people into action who were less programmers and more familiar with numbers. More and more people started using GA in the industry looking at a variety of graphs and specialisation occurred: data scientists entered the offices of PPC and digital marketing firms and analytical marketing was born.

Then came services like Loganis that offer a simplified way of looking at, exporting and analysing web data and with them Excel came back into the picture.

Excel is a great tool due to the unparalleled flexibility to process and visualize data. 

Let me give you a real-life example to underline this point. Suppose you want focus your spending on the medium (organic search, ppc, etc.) that performs best. A measure of what good performance is, of course, a bit subjective, assume you are interested in engagement for now and plan to quantify it as sessions with high conversion rates and pages viewed. So what you are interested in is

conversion rate and average number of pageviews per session by medium type.

Wait a second: what about highly engaging mediums that bring in almost no visitors? We need another metric, the raw number of conversions. Our query now consists of 3 metrics and 1 dimension. This looks quite complicated, indeed so much so that it will not be possible to get the numbers out of Analytics via logging into it.

Loganis, on the other hand allows one to harvest the data quickly. One can simply open the Loganis example Excel sheet, and type in the query

:met "ch0:ga:visits,ch0:ga:pageviews,ch0:ga:goalCompletionsAll"
:dim "ga:medium"
:per "last_60_days"

into the Generic Query tab, press Refresh and get the raw data. Then all is left to get Excel to transform pageview into average pageviews, goal completions into goal completions per visit (i.e. conversion rate) and plot a chart, for example a bubble chart like this one:

The query above looks admittedly daunting for the uninitiated. For those not wanting to learn this language (which is, by the way based on the GA API, see an intro here) there is always the possibility to use one of the pre-defined dashboard templates and copy queries from there.

As this example highlights using Excel and Loganis one can focus on Information instead of data and spend time not on creating reports for customers but gaining actionable insights that reduce wasteful spending in campaigns.

Another exciting feature of Excel is its pivot table functionality which allows one to manipulate complex data on the fly. I will discuss this in the context of multi-dimensional web data analysis in my next post.