This week saw me complete two long tasks: The first was yet another keywords report, a great list compiled for a phone company's website, targeting their Christmas deals as well as the generic brand names, contract durations and associated details, which slowly built out from specific lists to cover every facet of the four major categories, thereby creating the week's keyword list: This task took me to lunch, during which I finally boiled the kettle for myself and a colleague, as you should when in the office as an unwritten rule of courtesy!
After the break I was assigned a brand new task: An Exchange Report. These are basically spreadsheets (a prewritten template at Carat) into which data is placed from our chosen DSP. The DSP contains for several of our clients, the exchanges through which we buy our media and as I'm sure you can imagine, some of them outperform others; some are so bad that they have to be deleted. And that's where I come in!
Campaign layers: A quick diagram
In the spreadsheet you basically have multiple columns titled by metric, exchange name etc. in order to group your data. In the DSP you can select what metric or campaign aspect that you wish to see and for our task, we selected the CPC/CPA option because we need to know how much our clicks and conversions are costing us. From here, we can see which exchanges are performing well/not-so-well when compared to the average. The averages are calculated by the DSP for each individual strategy, which potentially has any number of exchanges within it (some had 1, others had around 21) and in analysing each piece of data, you can discover the overall effectiveness of the strategy based on how it currently stands regards to each individual exchange, a component of the strategy I guess; and if an exchange is performing badly as a 'damaged' component, you have to notify the architects of the strategy, which is essentially what the sheet is for.
Not the most exciting image I know, but I did make it through all assigned clients in the nick of time, I'm glad to say!
And after all of this, my fingers numb and my eyes stinging slightly from staring into the abyss of Excel for four hours or more, I was glad that I hadn't left earlier: Someone from downstairs came into the office with leaflets for Cafe Nero, a FREE coffee no less! It was strange seeing as I'd bought one on the way to work earlier so I guess I made some cash back the next day, walking back to the university with a Chestnut Latte, which I can highly recommend for the festive season!
This week I completed yet another keyword analysis (for a sports venture) in my first couple of hours. Thankfully I started to do something a little different and so we can return to a more exciting post!
After my keywords were drawn up, I completed some more Dayparting tasks. One was for a banking and financial services company (their mortgage campaign) and the second was for a company that produces and sells blinds. I used the Sort/Filter option once again to discover what the data said and put the most popular hours and days into a simple table. After this I wrote a strategy document where I recommended a plan of action. For example, the bank only had conversions during the week and lacklustre activity at weekend, which, I decided, was due to people using their free time at weekend to relax when it came to utilitarian pursuits of life, preferring to carry out such tasks during the week -and later at night- when they are in the midst of working for a living, the nature of the product residing within a similar state of mind where such purchases and brands dwell, in the consumer sub-conscious.
More excitingly, I updated the Carat "Best Practice Document" for Dayparting. This involved taking screenshots of pages and describing how to interact with a DSP we use in a step-by-step guide. I also had to delete the old section of the document that described processes on a platform that we no longer use, and had to replace it with a section on how to use DBM instead. This was really beneficial because by writing down how to go through the process, it was like I was teaching myself how to use it and as a result, I feel more confident now in using those platforms.
I then worked alongside another intern on Spendchecks, watching the process. Spendchecks are basically a type of document that tracks expenditure on different things associated with a campaign. In order to "check" the spends, you have to look at what Facebook Ad Manager is telling you and what the platforms are telling you, since Facebook is not completely reliable when it comes to specifying accurate monies etc. so the cross-reference is entirely necessary. I haven't actually carried one out yet, but hopefully in time I'll be sinking my teeth into one!
And that pretty much summarises the day. It's been more varied than the last couple of weeks, which is refreshing though I'm still lost for words when it comes to describing these things as it's getting to the point where I just start saying "today I carried out another Site Transparency/General/Keyword Report... you know, like the one I did in Week 5." This isn't really a criticism of the programme but more my admitting that I'm running out of ideas on how to present the same information of how to do something, in a novel way that's engaging. Alas, I'm not as good a blogger as I thought I was it seems :), though as FDR once said on failure, "...(But) above all, try something."
And I will leave you with that motivational quote from the ex-president (for this week's novelty) and hope it serves you well, along with this week's blog post, and the next one . . . (and the one after that, ad infinitum.)
This post serves as a note to self, seeing as I'm liable to forget a lot of the processes associated with this fairly complex tool, that few tend to know about.
So what is a VLOOKUP?
A VLOOKUP is a tool that allows a list of data to become dynamic in order to sift through large amounts of data very quickly. I'll show you how below.
STEP 1)
Bring up your data (I'm using a spreadsheet taken from the Guardian on opinion polls from 1984-2013)
STEP 2)
Copy the labels at the top of each column before you begin the actual lookup process and place them somewhere else on the sheet, like in the image above.
STEP 3)
Next we need to make a list out of the first column, so place your cell selector in the cell directly below the matching column title in the table you just created; bear in mind that I am using Excel for Mac (2011) and so there may be some differences in tool location. Select data validation; when doing this, you may need to select the source button shown here to select large amounts of it. Once you've selected the list and validated the data (Mac-users should find it under the DATA tab,) you should check the first column. If you've followed my advice, there should be a drop down box in the selected cell, which should include all selected data, as shown below.
STEP 4)
Now we can use the VLOOKUP. Firstly, select the cell below the next column label in the small table you created.
Now place =VLOOKUP into the box (it will appear in a drop down menu when you start typing, so you can just click on it when it comes up.)
In the parentheses that appear with the VLOOKUP tool, type in the cell reference for the list you just created, then place a comma after it.
STEP 5)
Now we need to select the relevant data: Highlight the first column of data from which we built the list AND the column next to it, which should match up with your created column labels. As you do this, a reference of data should appear like so:
STEP 6)
Now this is important. Now that all the necessary data has been highlighted, an option is available that wasn't previously. As you highlight, in the bottom left of the screen (on a Mac) you will see the letter C with a number. The C is a COLUMN reference and the number is the amount of columns highlighted. Seeing as I just highlighted the first three columns, I have "C3" written there, ignore the other reference in the image.
Using this column reference, you need to complete the argument by placing another comma after the data range and inserting the number of the column ONLY ("3" in this case.) After this, you should place a second comma and the number "0" before pressing enter to complete the argument. This is to solve an error of some kind that comes with completing such formulas. If everything has gone to plan, you should ideally see the data in all 'looked up' cells change, as different datums are selected from our dynamic list like the images below demonstrate.
The first piece of data: "15-06-1984"
The second piece of data "15-07-1984"
And that pretty much covers it, that's how to do a VLOOKUP on Excel the easy way, and if you didn't like the explanation, or if image-based guidance isn't really your learning style, here's a video tutorial:
I hope that you found this helpful. Have a nice day!
This week I continued with much of what I did previously. I entered more data into Excel for my previous client as well as another one that deals with large department store clients selling furniture, jewellery, clothing, technology etc. The keywords that I came up with -as you'd expect- were pretty varied though I did find myself becoming repetitive over time.
Within the first half of the day (after about an hour) I joined the others in a meeting in one of the boardrooms to listen to a discussion on keyword build-out best practice. We decided that the ideal way to carry one out for large websites was to find the smallest section number e.g. 4 tabs reading "home," "garden," "outdoors" and "activities," and from here go into each of those as a separate campaign or category. Within each of these you could create an Ad Group by the name of -for example- "living room" and within each Ad Group you would list your chosen keywords; (you could go even further into each specific product if you wanted, making very specific Ad Groups.) The idea is that you pick anything and everything that you can because, if you think to search for a string of words in hopes of getting a product, chances are that someone else does that too. On average though, we only allow 50 keywords max in any Ad Group, but if you split it down much further, you could potentially end up with many small Ad Groups that have average keyword numbers of only 10 or less. Ultimately it just depends on the client and campaign sometimes.
My second keywords chart was for a client that sells women's haircare products (driers, straighteners etc.) where I entered a fairly modest sum of keywords (around 100.) I really don't want to show the charts because there was no colour-coding of anything this week, so it all just looks pretty bland. But at the same time, if I put images on here it gives me more metatags to play with when it comes to SEO, so here:
Kitten!
After this, I conducted what was more of a research task than a keywords one, for a wine company. In this sheet I detailed the wine that was being sold, the origin of that wine, whether it was red, white or rose and finally, noted some key textual components as described in the product copy e.g. "drink young;" "full-bodied" etc. This was in aid of a new campaign that sees release fairly soon.
In all today was very enjoyable albeit being quite repetitive; the meeting was a good highpoint (and not just because of the filtered coffee!) where I learnt more about constructing keyword build-outs, and more importantly, how Carat are seeking to standardise how they place the data into Excel, so that every employee speaks the same language if you will, in terms of the column titles and how the data is written down for each platform where synonyms can potentially confuse.
My tasks -or should I say task- yesterday was a little simpler, which I enjoyed although I could easily imagine many finding such a thing monotonous and boring; I guess I've always been weird enjoying stuff like that! But it wasn't useless, no, far from it: I had to compile a spreadsheet from scratch for a client (one of those massive shopping centres, and all the brands and their stores, contained within.) I had to read the store descriptions and come up with keywords, which will be analysed once we've launched the campaign and as a result, we'll see the effect I had on success in my keyword choices! That's basically what I spent the whole day doing, colour-coding the stores to separate them easily when going through it to make the whole thing more readable. I'm about halfway through the first of the four sections of the website, here's how many rows of data I've inputted manually so far (in about six hours of work):
Yep; 615 rows, each with two columns! It's probably the most I've ever written into a spreadsheet in my whole life and I've only scratched the surface. Here's the colour code I used for the data:
Simple enough and very effective I found. I hope it continues to be fun next week! Aside from this I didn't really do anything else, but given that 615 rows were filled, 614 of which had manually inputted data, I'm sure you can see why! See you next week when I hope I'll have something more interesting to write about.
A couple of weeks ago I carried out some very interesting tasks on MediaMath and Double Click (the ad platforms.) I pulled the reports on the clients myself this time, the interfaces of both platforms being so easy to use and are quite intuitive, I found. All you have to do on MediaMath for example, is basically select your client and then the report type that you want to build (in my case, a keyword report, and then later a site transparency one,) and from here, you'll be prompted to select from a range of criteria to measure (such as CPC, CTR, CPA, CPM, Clicks/Impressions etc.) or alternatively, you can choose all the data available and export it as a simple (but often data-intense!) spreadsheet. When you've got the spreadsheet, you can then go and do what needs to be done with the data contained within.
As for Double Click, once you arrive at the main page, you simply select the "New Report" option and fill in the details i.e. client, report type etc. From here, Google will build a report featuring the data that you wanted to see and then you'll do the same as above. As you can probably see below, there's a very similar layout to both sites and both are very easy to use once you've gotten to grips with them, no different than the Facebook Ad Manager platform I used a couple of weeks ago and again this week.
I learnt a great deal from the analytics conducted, namely the difference between post-view conversions and post-click conversions; as the names imply, the former occurs only when a consumer sees an ad but does not click on it, but nevertheless, they then proceed to visit the page organically, thereby saving the company money and giving them a conversion at the same time; though that would also mean that content creators with the ad placed on their site would see a reduction in the amount they receive because, despite being part of the purchase path taken, they were not the sole way in which the customer was advertised to by Google etc. and the consumer did not click the ad; as a result, they would not receive the full amount for such an action. The latter conversion type is the bog-standard click the ad, pay for it, and then managed to get a conversion out of it as well, but at a cost to the client.
Without dwelling too much on the same tasks and becoming tiresome for you, I also completed another Facebook Ad Spreadsheet, but this time I decided to do something else that I wasn't asked for (largely out of wanting to spend the last twenty minutes or so productively.) When looking at the clicks for out client (their campaign had just come to an end on that particular day,) I separated the clicks into "social clicks" (a metric found within the data) and the normal clicks by using a calculator to take away the social clicks from the overall click total, thereby disclosing what amount of the clicks came from non-social platforms. I then placed this data into a simple pie chart, depicting the data as a percentage. Here are the results!
As you can see, most of our clicks came from outside of social platforms, so maybe out client's customers don't like being pestered when talking to friends! Not that you can really draw a conclusion from this data alone, but it does provide an interesting dynamic to look at don't you think?
Aside from the tasks, the work and the clients, I've also enjoyed the chit-chat over the last month that I've been here. It certainly livens up the atmosphere of the office that would probably be dull and lifeless without it. Finding things in common with people, talking about out lives, experiences, hobbies and anything else we can muster really places a nice slant on the experience of working, and just makes the hours go flying by without a clock fast enough to measure them! And until we meet again next week, I bid you a good day and hope that you can tune in again for the next chapter of my story, a romance about a marketing agency, because one can indeed love maths and analysis, (and this comes from someone who loathed the subject at school.) But in all seriousness, see you soon and have a good day . . .
Yes, I'm delving into another unconventional article that goes off the metaphorical track, on which the train of this blog rolls. Iain Duncan Smith is a Conservative Party politician in the United Kingdom; a member of the current government, the Cameron Ministry (a coalition between the Liberal Democrats and Conservatives,) as well as Conservative Party leader between 2001-2003 before he was defeated in a vote of no-confidence (BBC, 2003: Online,) thereby becoming the second Conservative leader to not contest an election since Austen Chamberlain in 1921-22 (Brown, 2001: Online;) the first being William Hague in 2001, although at least he contested an election while Duncan Smith on the other hand, failed to survive as leader of the party long enough to do that.
Iain Duncan Smith; Secretary of State for Work & Pensions
That is Smith's background as far as major positions are concerned. Most recently he has drawn considerable controversy since taking office, particularly regarding his attitudes to social security and its claimants (both working and non-working,) as well as controversies surrounding the DWP's iniquitous use of statistics (BBC, 2013: Online.) He has consistently claimed that his plans are working -using figures and evidence- even though when such evidence painted a picture to the contrary (according to the UK Statistics Authority, 2013,) he defended his claims by saying "I know I am right" (Wintour, 2013: Online.) Nothing exemplifies this kind of manoeuvring more than my own discovery a few months ago: Writing in The Guardian about his controversial Work Programme (a system wherein benefits claimants receive benefits from the government in exchange for working for companies such as Poundland- a particularly controversial case (see Morse, 2013,) IDS claimed in his humbly-titled article, 'I'm proud of our welfare reforms' which ran with the strap line: "I don't apologise for trying to make the welfare state fair- it's something only this government can do," that "Our Work Programme has launched and the industry tells us that so far 321,000 people have found jobs through it." The italics in that sentence represent a hyperlink to another Guardian article, the headline of which read: 'Welfare to work scheme failing to get people work, say figures' (my emphasis.) The strap line read: "Just 5.3% of people on incapacity benefit were helped into employment for at least six months by Work Programme." The article, from Butler (2013a) further stated that "Ministers were upbeat about the data, claiming that more than 130,000 jobseekers had "escaped long-term unemployment and found lasting work" as a result of the work programme." (My emphasis.) Far from being a good idea to want people to work for a private company for their state benefits, therefore degrading the value of the work carried out by current employees when those companies, like Poundland, have ample profitability to pay for such workers, which ultimately exacerbates our economic whims via more benefit claimants and low-paid workers, the fact that he linked to evidence running contrary to his opinion startles me beyond belief. How can a Cabinet Minister of Her Majesty's Government create such a transparent lie! Other exploits include Smith's 2012 Welfare Reform Act, specifically Section 69 Sub-section 344 Paragraph 2, which stipulates a means-tested approach to social housing (designated by local authorities) vis-a-vis bedroom size determinants, whereby 'extra' bedrooms not used by children or pensioners will suffer an "under occupancy penalty/subsidy," a decision commonly referred to as the "Bedroom Tax." This resonates memories with the so-called Poll Tax introduced at the end of, and which in part caused the downfall of, the Thatcher ministry in 1990; the Community Charge was amended to take account of individual bands and rates of social homes as opposed to being a simple flat tax that left some worse off than others. It was replaced by the Council Tax in 1993 by the Major ministry (BBC, 2005: Online.) The 'bedroom tax' has been criticised as being unfair by UN specialist Raquel Rolnik a couple of months ago (Johnston, 2013: Online) and has also been seen as a waste of money and government resources, as criticised by celebrity businessman Theo Paphitis on the BBC panel show Question Time in 2012. The main problem of course is that there isn't enough housing to move people into, with the DWP itself not knowing how many will need to be moved or where they will go; even if all pieces of such a jigsaw were fitted together optimally, the predicted £480 million saving (Butler, 2013b: Online- the figure is likely to be some £160 million less than predicted, according to a York academic) would be wasted on administrative costs associated with its implementation. Not only that but people will just not pay it. My source linking to the Poll Tax demonstrates this problem, with roughly 20% of people not paying the tax and simply not registering on the housing list or living in a 'single' room with a partner etc. This ill-thought out policy will, and has already in part, failed. Even a majority of the British public don't want it according to a ComRes poll (2013.) A recent criticism of IDS has been his failed IT infrastructure surrounding the implementation of his Universal Credit scheme, a method of rolling all benefits into one package (Kirkup, 2013: Online.) The system has cost the taxpayers £425 million so far but with failures so consistent and manifold, £140 million of that, including numerous computer systems, faces being written off (Johnstone, 2013: Online.) This is yet another failure of IDS and is testament to his deficiencies as a Secretary of State; he is unfit for office in my opinion.
I could go on with how much I personally detest IDS as a person, but I feel that this video taken from Question Time filmed in November of 2012 says more than I need to. If you need more evidence of how much of an unlikable person he is, search for his other defences of his workfare (the Work Programme) scheme and you'll see what I mean. It's much worse to hear it in person than to read his IQ-reducing, transparent, completely bullshit articles, that have about as much integrity as a desert has water. I hope you have found this critique enjoyable and informative. As a final criticism of Iain Duncan Smith, HERE, is where he said he could live on £53 a week. And HERE is the petition that urges him to prove it, by living in such circumstances for a whole year. He has yet to rise to the challenge . . . Thanks for reading this post and seeing as you're here, give this a watch:
I know, it's strange and a massive departure from what this blog usually focuses on, but I just had to put my thoughts on this down somewhere.
Fleshgod Apocalypse, the Italian technical death metal outfit (and please don't let the label put you off!) released the concept album Agony back in 2011. In August this year they released a second conceptual work entitled Labyrinth, which I have not yet heard in full and so cannot comment on.
So who are Fleshgod? They came to prominence quite recently, releasing their debut album Oracles in 2009 and later an EP entitled Mafia in 2010. They have been seen within the international metal community as a force to be reckoned with as a result, blending together the brutality of death metal with the ambience of classical music, with all five members of the band being classically trained in their instruments, as is apparently common in Italy according to the band. And nothing exemplifies such training as listening to this album. It's warm, it's symphonic, it's operatic yet it's also cold, destructive, explosive and even eerie to the point of chilling. This album, is all of this and more throughout its 10 tracks.
According to the band, the concept of Agony surrounds the idea of the destructiveness of the human condition, which always ends, inevitably, in agony for the perpetrators. The track listing is the first indication of what they were trying to encapsulate (and boy did they succeed!) with an obvious pattern: 1- Temptation [Instrumental] 2- The Hypocrisy 3- The Imposition 4- The Deceit 5- The Violation 6- The Egoism 7- The Betrayal 8- The Forsaking 9- The Oppression 10- Agony [Instrumental]
Bonus Track- Heartwork (a Carcass cover; it's kind of separated from the main body with some 10-15 seconds of silence.)
The Masterpiece that is Agony!
It's also worth noting that if you buy a physical copy (which I would highly recommend) the songbook, (which unfortunately doesn't have lyrics, but definitions of each word after which each song is named) has a quote from The Divine Comedy by Dante (Inferno Canto XXVI Verse 118-120.) The quote is written in Latin, but if you have copy of Dante's classic in English, then obviously look it up. This is a very nice touch I feel because it's almost like each track represents a circle of Hell e.g. Betrayal for the traitors like Mordred (from Arthurian legend) in the poem, (Inferno Canto XXXI: "him who, at one blow, had chest and shadow / shattered by Arthur's hand.")
But moving on from the overall concept, what is the music like? Well as you'd imagine, it's often frantic, fast-paced and the vocals are usually roared/grunted in low, animalistic pitches by frontman Tommaso Riccardi. But there are also high-pitched, operatic vocals emulating from bassist Paolo Rossi, which often weave rhythmically between the traditional tech death vocal style (you can see examples of this on the track, The Forsaking, a slower song.) However, there are also times where these vocals offer a whole chorus between the death-like verses a la The Violation or The Oppression. These are not standalone tracks or even the greatest on the album, because the whole work doesn't stand or fall on its singles. Why is this? To explain I'll now move onto the symphonic elements.
Symphonically, the album has a variety of ebbs and flows in its instruments, points where violins are more prominent or points where flutes and other instruments dance around the light-speed drumming. This is what makes the album doubly effective. As some tracks end with fairly slow outros, the next track will often pick up from that with a metallic sound, changing the orchestral arrangement to begin a new track that not only advances the work seamlessly but also sounds different enough to be recognisable. To put it simply, there are occasions on the album where an outro of one track acts as the intro of the next simultaneously, an effect epitomised by The Violation. The music video for this track below begins with the uncanny classical intro, before lurching into the thunderstorm of death metal delight! This is because that 'intro' for the video of The Violation is actually the end of The Deceit, but because it flows so brilliantly, they were able to take those two pieces of essentially different songs and merge them into one for a video. The same effect is also used oddly enough for the music video for the second single released, The Forsaking, with the intro, again, being in fact the outro of The Betrayal.
The Violation: A thrill-ride of insane drumming, shredding guitars, haunting woodwind sections and brutal vocals.
The Forsaking: The slow song with eerie vocals,
ambient echoes, and a beautiful piano overlay.
In terms of how this effect changes the album, it causes the whole work to flow so well that the whole thing on some occasions feels like a single track, a whole symphony, which is what they were going for to some extent. Some may find this to be too repetitive, but personally I don't really think it is because even though discerning the lyrics is quite laborious (here's a good site for the lyrics by the way,) you can tell that there's certainly a story being told, a journey being embarked upon, which is what gives this whole album a fantastic appeal to those who aren't even into popular varieties of metal (even my parents enjoyed it!) To put it short, the journey from a small Temptation through to blistering Agony is one that you will want to take again, and again, and again!
To finalise this review, I'm going to identify some standout points for each musician, the parts where they really show their talent!
Tommaso Riccardi: Although he has a brilliant voice throughout the entire duration of this album, the vocals are especially appealing due to the difference between this track and the others, which is why I'm identifying The Forsaking as the standout track for him, the echoes and reverberation are just so chilling when adjacent to the piano on this track, it's uncanny.
Paolo Rossi: Unfortunately, the bass work is often drowned out of the mix by the strings (a common criticism of the album in fact,) though there are some good moments across numerous tracks. But his operatic vocals are where he really shines! The Oppression, where he tackles the chorus is where he raises his voice so high yet unbelievably remains on key throughout, testament to his classical training no doubt.
Christiano Trionfera: The guitar work has very good standout parts in the track The Betrayal, where it penetrates the wall of drumming and symphonic parts with seamless shifts in pitch that match the surrounding sound in such a way that it just fits perfectly with the mix and makes a marked difference to that track compared to the rest of the album.
Francesco Paoli: The drumming is what really got me into the album, being a drummer myself! The fast-pace on The Violation in particular is what really got me sold on the whole thing, the sheer power and force just makes you want to quiver with adrenaline and break everything in sight, an effect that is maintained through the entire album, with a possible exception seen on The Forsaking.
Francesco Ferrini: The conductor of the orchestra and pianist; whilst his work with the whole symphonic arrangements is quite possibly the most innovative contribution to the work, his piano skills are fantastically exemplified on the outro track Agony. This classical track is so haunting and so skilful that upon listening to it I could tell that the journey had ended; I could just imagine it being played over a video of someone being tortured and therefore in agony, the epitome of the concept and a clearly marked difference to the intro, Temptation, which begins with a crescendo of drum rolls and strings, building tension throughout. The track Agony only has a piano on it, and makes full use of it's player!
I hope that you enjoyed this rather long review, and if you like it (the album or the review) please, get in touch!
.jpeg)