Translate

Monday, March 10, 2014

An Essay on Analysis

HOW TO ANALYSE DATA


This essay is a compilation of pointers detailing how to think and –where appropriate- how to best use Excel to implement desires to achieve a result. Before we begin however, I feel that I should provide you with a list of abbreviations and definitions:

-       AUDIENCE REACH: Traditionally the number of people to simply see an advertisement
-       CLICK: When a display advert (e.g. a banner) is clicked on by the user
-       CONVERSION: When a desired action is completed on the part of the user AFTER clicking the advert e.g. making a purchase.
-       IMPRESSION: Every time a person simply sees the advert on a webpage (See AUDIENCE REACH)
-       LEAD: A direct marketing term referring to communications seeking SIGN-UPS of some kind for future communications, as opposed to simply making a purchase for the present.

-       CPC (Cost per Click)
-       CTR (Click-through Rate)
-       CPA (Cost per Acquisition)
-       CPL (Cost per Lead)
-       CPM (Cost per Mille or Thousand)
-       DSP (Demand-side Platform)


PART I: THE ANALYTICAL MIND

The mind of the analytical is concerned with questions. These questions can be summed up in rather simple lexis such as “Why” and “What,” these chief two words being crucial to reaching conclusions and implementing reactions to them because they, A) Discover the reasons for a particular event and B) Discover the implications for its consequences, whereas other questions such as “How” don’t really tell us much about implications or reason, but rather about technicalities, explaining the conditions that caused the event, which is useful of course, but it is not the chief interrogative that gives us the path to solving a problem because it is chiefly concerned with the past or present and not the future (OUP, 2014: Online.)


So how do you think in those terms? Well I’m not going to patronise you and give you baby steps with which I’m sure you’re accustomed, but below is a simple flow chart that I’ve devised in order to visualise an analytical thought path.


As you can see, the “How” node takes us towards the causes of a particular occurrence but it is not the reasons behind the occurrence, for instance, let’s say that I want to know how a CPC became so high and the reason or CAUSE of such an event might be that fewer people are clicking the advert. Fair enough, but why aren’t they clicking, is the question that we need to ask and the answer might be that the creative is off-key, or the timing is wrong and from this information, we can think of solutions to the crux of the issue. But this is just the introduction to our study of analysis. How do we implement this into a concrete example, where we deduce a problem and think of a solution therefrom?


PART II: AN EXCEL EXERCISE

Using Excel we can split data and make it sing and dance as we please in order to get beneath the surface. The following example is taken from a coursework task, courtesy of my academic institution. Please note that none of these names and associated data are based on a real company or client list; all information is fictitious:

Our customer has a business that offers services to multiple clients across multiple sectors. They made £5405 in 2013, but £5710 in 2012, indicating a loss of business in terms of sales. This would be evaluated as a business weakness to the untrained eye, a blanket statement that any fool would be able to spot with ease as the image below demonstrates:


The problem with this logic is that it has failed to analyse the problem, instead just pointing out the obvious. What we need to do is look deeper, so let’s examine more about this client list: Who are the people in it and where do they operate? Well, by organising the list vis-à-vis sector and grouping the sales total for each one in which our client has contracts, we can see a very different picture that changes our influences. The following graph details the sales totals in column clusters, detailing the sales value per year between 2012-2013 for each individual sector, and I can tell you that the picture looks very different indeed!


Now let’s have a closer look at those categories. The Leisure and Sports Stadia sectors are clearly increasing in sales value, which gives us a good picture with which to solve our financial problems. Not only have we identified why the problem came into being (declining sales in councils and office refurbishment,) but also what we can do about it in response: Shift focus to those growing sectors like Leisure. In completing this minor analysis we already have an idea of what we can do with the future; if we analyse further into whom those clients are and what they need from us, in addition to discovering how we can deliver that promise and we can ultimately get from this:


To this . . .


And so we can see that as we learn to present more facts as numbers alongside one another, they can be most revealing, but the ways in which we just presented those figures is only a single way of interpretation. Data –like anything made of numbers- can be expressed differently, which is why we must also consider


PART III: THE KEY EXPRESSIONS

Expressions contain not only raw numbers, but fractions, percentages and a plethora of graphs and charts used to not only demonstrate a particular conclusion, but also try to express it in a way that is the easiest to understand because whenever data is needed, those who seek it want it set before them in a way that doesn’t make the brain hurt with confusion, but rather throb with excitement!

This means that not only does the analyst need to reach the conclusion but show it, which means performing Excel functions in many cases. I’ve already shown via my Excel 101 Tutorial on the VLOOKUP function, but I haven’t demonstrated the use of Pivot Tables, a highly necessary function of the Excel software and a staple in the toolkit of the analyst.


PART III: PIVOT TABLES

Firstly, I expect that you have a whole bunch of data like this:


Of course all that’s ugly and inconclusive, just raw data in other words, data that must be broken down and reassembled properly with the hopes of reaching a more satisfactory conclusion. But first what does this sheet show?

Taken from offline as a practice Pivot Table sheet, it shows numbers of people working and earning across the United States, showing their sex, race and earnings amongst other variables: Now onto the Pivot.


Click on the “Pivot Table” function found under the DATA tab (bear in mind that I am using Excel for Mac 2011,) and you should come across a screen like this one below, (though if this does not happen first time, you may have to manually select the relevant cells via highlighting their columns detailing what is displayed their by using the Command, Shift and Direction functions. Highlighting all that you want to include and then pivoting should do the trick.)


The “Builder” will appear alongside a –most likely- filled in Pivot and now you may play with it to your heart’s desire. Usually I deselect all of the stuff Excel’s already selected for me and begin again, so un-tick all the Builder’s boxes and reselect them. You may find that you get some variables appearing on certain axes that render the table awkward to work with, which is why it is important to be wary of these:


In order to make the table work for you, you will need to change the placement of variables by dragging and dropping them into the different categories to ensure that they work for you. For example, if one of my variables is “Website,” then I don’t want every website dealt with appearing across the top of the sheet, because there’s likely going to be large numbers of individual URLs, which is why they are better placed on the Y axis running lengthways, because it makes it all easier to see. This rule generally applies to any situation where one deals with metrics via which to judge (X axis) and the subject being judged (Y axis) which in my example was a URL. Please note also that in those individual boxes, you can also place them above and below one another so that –for example- you can see every URL on a particular day of the week and judge performance against another time period etc.

In any case, what you should be left with is something akin to this:


But this is of course insufficient because all the numbers are the same and are littered with decimals, which is why you must then adjust every new column to its own type of measurement.

This can be done very simply with a column edit, so click on the tab where the column is e.g. Column E containing –in my case- a column showing each stratum’s earnings for the average year and every cell should be highlighted like so.


From here we must alter the numerical value measured within the column, which means right-clicking and –from the drop-down menu, selecting “Format Cells.” From here you must click the “Number” tab if not already highlighted and select the most relevant category, which, for me, is the “Currency” category. Now under Currency, you must select both the currency used (US$ for me) and the decimal place that you want to apply (2 decimal places is usually sufficient,) after which you should be able to see something like this:


And that should be that. You’re ready to go and analyse that data, but in order to do that we must move onto another tutorial, so once you’ve set up all your Pivot Tables and have their numbers sorted out in the columns as appropriate, read on for the next tutorial, dealing with data manipulation.


PART IV: DATA MANIPULATION

To do this I’d advise inserting what is known as a filter. You can to this simply by highlighting the columns again and clicking onto your DATA tab. Under this there should be an option to “Insert Filter,” depicting a symbol like a silver funnel. If you’ve performed this correctly you should see arrows appear at the top of each column, which basically means you’ve just inserted your filter.

So once the arrows appear, you can play with the tool. In order to do this, click the arrow and examine the black box that drops down. In the image below you can see an ascending/descending function, which –obviously- organises numerical values into the highest and lowest orders. This function also applies to lexis with alphabetical organisation. Ultimately this is known as sorting the data however, what can often be more relevant is to filter unwanted variables. Given that tables featuring variables such as individual URLs tend to possess repetitive data, it makes sense to disregard some options entirely, to shrink a table of 10,000 rows to just 10. To do this, you can check and uncheck different boxes to select and deselect certain options found within a particular column.


To demonstrate the power of a simple filter in the world of analytics, here is the pre-filtered table:


Here is the table filtered by years in education to determine who exactly has spent under 100 years there (please remember this is aggregate averages by state, not individuals!) and you can see a clear difference.


In summary, if you combine these two powerful tools you can deduce the most and least positive results, the abnormalities within a body of data, with astounding ease, which is how analysts get through spreadsheets with potentially a million rows of data in them, within as little as a few hours sometimes.


PART V: ANALYTICS WITHIN AD AGENCIES

In my quite limited experience working within an ad agency I’ve obviously had to work extensively with these tools. But in terms of marketing and advertising performance, what kinds of formulas do we use and what expressions benefit certain situations?

-       Cost per Click (CPC) is the bog-standard cost for display ads and tends to be a primary KPI (Key Performance Indicator) for clients. It is calculated via: TOTAL COST / CLICKS

-       Click-through Rate (CTR) is another popular performance metric measuring the actual marketing performance of the ad, showing engagement via the number of people who actually clicked, expressed as a percentage. It is calculated via: CLICKS / IMPRESSIONS

-       Cost per Acquisition (CPA) is another costing metric used to discern the average cost for each conversion garnered by a particular advertisement, calculated by: TOTAL COST / CONVERSIONS

-       Cost per Thousand Impressions (CPM) is a less-common metric yet still important to some clients, detailing the average cost for every one thousand impressions the ad receives. This is calculated via: TOTAL COST / IMPRESSIONS * 1000. Please note that this is also used for traditional media such as Television and Radio, Impressions of course being merely a digital term for audience reach.

-       Cost per Lead (CPL) is a similar formula to CPA but with a very crucial difference: While CPA measures the average cost for each conversion, a LEAD is an interested party to whom the communication is sent, being a form of direct marketing with a response component and a more future opportunities-centric focus, as opposed to general display marketing with an awareness-centric approach, designed for simply making a single conversion without any kind of sign-up. This of course has implications for results expected, because it may be harder –depending on target markets- for some to part with money for online purchases, whereas others may be reluctant/inert when requested to sign up to something. Here is the re-modelled calculation: TOTAL COST / LEADS GENERATED

These are the most prominent of display calculations used in industry today as I have experienced them in my quite brief time at Carat. The experience has been a riveting one it has to be said, with varied days and many tasks that –in my rather long essay above- have only been explained in the slightest detail. I will be continuing my internship series of posts but the internship itself with be ending after March I think, meaning that I’ve only got another two or so weeks to work. On the whole it’s been great!

I hope that you enjoyed this essay and/or any of my weekly blog posts you’ve read and if you, too, found it all riveting, please, let me know!

Thank You,
Jake


BIBLIOGRAPHY

Oxford University Press (2014) How http://www.oxforddictionaries.com/definition/english/how?q=how [Online] [accessed January 18th 2014]

Russel, B. (1912) Problems of philosophy OUP Paperback Ed. [Reprint: 1971-1972]

Carat Media Internship: Journal Entry #20

This week revolved around a couple of long tasks, the first of which involved using PowerPoint for an evaluation of a Branding campaign that covered two clients, very similar to what I did last week in fact, except that this week there were no technical difficulties and as a result I finished both after some five hours of solid work. The presentations involved not only an analysis of site transparency, but also primarily of the creative size, the media exchange and the day-parts. Using all of these facets of the campaign performance I was able to identify some very strong pointers to campaign success, meaning a greater intelligence as a result. The slides will -I imagine- be sent upstairs to the team who reorganise the slides to make them presentable to the clients.

Presentation Slides

During the day I also attended a meeting with the rest of the team focusing on CRO. CRO (or Conversion Rate Optimisation) is the process concerned with increasing conversion rates via creating numerous landing pages on a client's website. From here, after running numerous tests with layout and creative etc. we can determine the best converters without spending unnecessary amounts on rounding up more clicks to aid conversions. It all looked very exciting I thought and in the near future we will be implementing such a service I was told, so hopefully we'll continue to offer a great service to our clients.

The second task for the day came right at the end as an addendum of sorts, considering that there were only twenty minutes left. I was asked to use MediaMath to pull some Geo Insights data on a client and using the pulled spreadsheet, I was able to sort the data to show the top three locations in the UK; naturally, London was the best performer as I and my colleague expected, with other major cities following as runner-ups. This ultimately concluded the day and hopefully next I'll be looking into some more DSP insights and since the unit is coming to a close, I will have to begin thinking about filming a short video on my placement, showing what I do every day and justifying taking the place if it was offered again; quite a bit to fit into a single minute I think!

Monday, March 3, 2014

Carat Media Internship: Journal Entry #19

Last week I completed a sizeable analysis of a campaign, which involved multiple spreadsheets, detailing the site transparency of our client, specifically for mobile devices and their operating systems. I made numerous discoveries along the way, everything from audience type deduced from the app engagement to the popularity of phones amongst such groups, in addition to whether they were more or less likely to convert via analysing the app type e.g. popular gaming apps such as Words with Friends would be more likely to result in accidental clicks due to the nature of the game whereas other, less fast-paced and emotionally-charged apps such as those offering individual utilities like unit converters or -as we found- torches, would likely drive more genuine activity. It was valuable experience, but took up a large bulk of the day meaning that there's little to say without repetition. Ultimately this analysis took up eight spreadsheets, for four months from November 2013 to February this year, with each month analysed across two sheets, one for OS, the other for device.

Placements for the Campaign

While the task wasn't too difficult or hard to organise, it was time-consuming, which is why I'll move on to my afternoon task involving PowerPoint. Essentially, I was required to use a DSP to discover the data surrounding our Branding Campaign that spanned two clients and to write the successes and failures into a presentation. Normally I'd use Excel to examine the data in spreadsheet format, but seeing as there was no need to actually pull it from the DSP, only to use it for research, it was much easier to use the display alone, which really helped and it offered quite the opportunity to learn more functions of this particular program. In any case, after writing our overall CPCs for each line item for the first client (by which point there was less than half an hour remaining,) I unfortunately couldn't move onto our second client since either the Internet, or possibly the DSP, went down, which ended the day rather abruptly.

Our Line Items

But the day was quite stretching in summary and provided an interesting span of tasks, for which I'm glad, the ending being quite unfortunate. But hopefully with some luck, next week should run much more smoothly and it is then that I will resume this blog. Thanks for reading.

Friday, February 28, 2014

25 Days Later . . .

Twenty-five days ago I wrote a post about a novel-length project I was undertaking and how I was planning to stick to a new writing strategy, detailed in a post before that. This post focused on writing quotas and the optimum time of the day during which one should write, in order to generate the most creativity. The image from the first post is below, showing how many words I had written of the novel thus far (in about 3 weeks.)

Before . . .

I gave myself -as you can see from the image- 25 days to complete a first draft, which I estimated to be at 70,000 words in length (sadly I overestimated this, but plan on fleshing out areas that I feel require such treatment) by writing 2200 words each and every day, as well as reading for at least one hour every day, a hobby that I am sustaining in the quite limited time that I have. Below is a new image that shows my progress.


After!

 I reached the ending -which I had already written- two nights ago, improving some areas and adding some scenes that provided a connection to the rest of the novel, given that when I had written the ending I had began right in the middle of the action, to keep myself interested and then wrote a plan around that before writing forward. I've found this a very useful strategy because when I wrote my first novel (currently in second-drafting stage) I hit walls constantly, and usually found myself subject to Writer's Block. in the entire course of this novel, I'm glad to report that only ONCE did I find myself staring at a blank screen unable to write anything in an evening. To give some context to the great progress this is for me, the post that I wrote announcing the completion of my first novel was entitled "700 Days Later . . ." which just highlights how slow I was, writing from August 2011 to July 2013, when the post was published. I'm also glad to say that as far as writing quality is concerned and plotting, this was a much better first-time effort! Time to improve!

PS. I'm also working on planning long-length ideas so that I don't end up forgetting them as soon as I go to sleep. Some are SF works whereas others are more mainstream (like this one.) I'll update on my work more in the future but until then, thanks for reading!

Monday, February 24, 2014

Carat Media Internship: Journal Entry #18

This week was more of the same initially, completing spend checks for a few campaigns, some government-based, others being those of private companies, but I was glad to see that they were all pacing quite well in general. Using these completed spend checks I went on to carry out some more site transparency reports and later I actually pulled the spend check from a Google-based DSP, which was a new experience. It was quite frustrating initially to be honest as I kept on trying to pull a spend check, but because Google requires you to tick boxes in order to show spend and the other metrics, in addition to selecting a time range i.e. "last 30 days" etc., this created some problems and so I ended up only pulling the data I needed on the third attempt, because I kept forgetting to tick all the relevant boxes, sometimes wondering which ones to tick. But it's still a lesson learned I guess and I did get what I needed without help in the end, so overall: Success!

One of My Better Presentations 

This was followed up with aiding in the design of another presentation focused on the engineering sector, highlighting the benefits of the social media platforms, obviously tailored to their requirements etc. I must also say here that I am really starting to get to grips with using PowerPoint creatively, which is a great deal better than just putting together the kinds of presentations I did at school, that pale in comparison quite frankly. In any case the presentation came out fine, I was asked for feedback before the end of the day actually, with the only criticisms being in certain misplacements of text boxes etc. that unbalanced the layout from time to time, problems that could be solved in next to no time, which was quite pleasing.

How You Can Target via Mobile Devices; 
(I gathered each image separately)

Finally, I aided another colleague in taking notes on LinkedIn as a potential platform, though I got no further than he did in discovering anything particularly new or exciting, it was still an experience (it reminded me of when I used to work for Tomi Clothing actually) and I did learn something about how LinkedIn do business with media buyers, their rates and costs etc., so hopefully such information will come in handy at some point in the future. 

Aside from this there isn't much else to say and so with this conclusion I will see you next week and hope that yours goes well in the meantime; I will hopefully be engaging with the DSPs that I have little experience of currently, so let's see how I fare!