Gilligan on Data by Tim Wilson

I’ve had a pretty good run of theoretical posts about the nature of marketing measurement of late, so it seemed like I was due for a more down-in-the-weeds-Excel-efficiency-tactics write-up.  This blog isn’t really focussed on all of the myriad ways that Excel can be contorted to represent data effectively, but I’m a big believer in using tools as effectively as possible to remove as much rote report generation as possible. There are lots of blogs devoted entirely to Excel tips and tricks. My favorite on that front is Jon Peltier’s (if you get intrigued by this post, hop over and peruse a slew of other ways to have charts dynamically update).

This post describes (and includes a downloadable file of the example) a technique that we use extensively to make short work of updating recurring reports. Here are the criteria I was working against:

• User-selectable report date
• User-selectable range of data to include in the chart
• Single date/range selection to update multiple charts at once
• No need to touch the chart itself
• Reporting of the most recent value (think sparklines, where you want to show the last x data values in a small chart, and then report the last value explicitly as a number)
• No use of third-party plug-ins — one of these days, I’ll get around to playing with the various Excel add-ons like those offered by Tableau Software and XLCubed (or even the Peltier Tech add-ins, which are targeted but made by one of the top 3 most authoritative Excel resources on the ‘net), but that adds just the slightest of barriers and, again, isn’t needed for this exercise
• No macros used — I don’t have anything against macros, but they introduce privacy concerns, version compatibility, odd little warnings, and, in this case, aren’t needed

The example shown here is pretty basic, but the approach scales really well.

Sound like fun?

Setting Up the Basics

One key here is to separate the presentation layer from the data layer. I like to just have the first worksheet as the presentation layer — let’s name it Dashboard — and the second worksheets as the data layer — let’s call that Data. (Note: I abhor many, many things about Excel’s default settings, but, to keep the example as familiar as possible, I’m going to leave those alone. This basic approach is one of the core components in the dashboards I work on every day, and it can be applied to a much more robust visualization of data than is represented here. See An Excel Dashboard Widget for a look at my thoughts on dashboard visualization.)

Data Tab Setup — Part 1

This is a slightly iterative process that starts with the setup of the Data tab. On that worksheet, we’ll use the first column to list our dates — these could be days, weeks, months, whatever (they can be changed at any time and the whole approach still works). For the purposes of this example, we’ll go with months. Let’s leave the first row alone — this is where we will populate the “current value,” which we’ll get to later. I like to use a simple shading schema to clearly denote which cells will get updated with data and which ones never really need to be touched. And, in this example, let’s say we’ve got three different metrics that we’re updating: Revenue, Orders, and Web Visits. This approach can be scaled to include dozens of metrics, but three should illustrate the point. That leaves us with a Data tab that looks like this:

While we’re on this tab, we should go ahead and defined some named cells and some named ranges. We’ll name the cell in the first row of each metric column as the current value for that metric (the cells don’t have to be named cells, but it makes for easier, safer updating of the dashboard as the complexity grows). Name each cell by clicking on the cell, then clicking in the cell address at the top left and typing in the cell name. It’s important to have consistent naming conventions, so we’ll go with <metric>_Current for this (it works out to have the metric identified first, with the qualifier/type after — just trust me!). The screen capture below shows this being done for the cell where the current value for Orders will go, but this needs to be done for Revenue and Web Traffic as well (I just remove the space for Web Traffic — WebTraffic_Current).

And, we’re definitely going to want to have the whole range of data on the tab available to us. Let’s call this MainData and define it by going to Formulas » Name Manager and clicking on New (this is Excel 2007 — it’s somewhere else easier to find in Excel 2003). Define a new range with a Workbook scope that encompasses all the columns and all of the rows of data (starting at row 3):

There are lots of ways to dynamically define MainData. You can just drag a big area if you want, but this is a slightly more elegant approach. I’m not going to go into the nuts and bolts of why this formula works, but you can look up the OFFSET and COUNTA functions and figure it out if you’re so inclined:

=OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2,COUNTA(Data!$2:$2))

We’ll also want a named range that just includes the list of months — create that the same way as MainData, but call it DateSelector and use a slightly different formula: 

=OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2,1)

And, of course, we’ll actually need data — this would come later, but I’ve gone ahead and dropped some fictitious stuff in there:

That’s it for the Data tab for now…but we’ll be back!

Dashboard Tab Setup — Part 1

Now we jump over to the Dashboard worksheet and set up a couple of dropdowns — one is the report period selector, and the other is the report range (how many months to include in the chart) selector. Start by setting up some labels with dropdowns (I normally put these off to the side and outside the print range…but that doesn’t sit nice with the screen resolution I like to work with on this blog):

Then, set up the dropdowns using Excel data validation:

First, the report period. Click in cell C1, select Data » Data Validation, choose List, and then reference the named range of months we set up earlier, DateSelector:

When you click OK, you will have a dropdown in cell C1 that contains all of the available months. This is a critical cell — it’s what we’ll use to select the date we want to key off of for reporting, and it’s what we’ll use to look up the data. So, we need to make it a named cell — ReportPeriod:

Now, let’s do a similar operation for the report range — this tells the spreadsheet how many months to include in each chart. Click in cell C3, select Data » Data Validation, choose List, and then enter the different values you want as options (I’ve used 3, 6, 9, and 12 here, but any list of integers will work):

And, let’s name that cell ReportRange:

Does this seem like a lot of work? It can be a bit of a hassle on the initial setup, but it will pay huge dividends as the report gets updated each day, week, or month. Trust me!

Before we leave this tab, go ahead and select a value in each dropdown — this will make it easier to check the formulas in the next step.

Data Tab Setup — Part 2

Now is where the fun begins. We’re going to go back over to the Data worksheet and start setting up some additional named ranges. We’ve got MainData, which is the full range of data. We want to look at the currently selected Report Period (a named range called ReportPeriod) and find the value for each metric that is in the same row as that report period. That will give us the “Current” value for each metric. All you need to do is put the exact same formula in each of the three “Current” cells:

=VLOOKUP(ReportPeriod,MainData,COLUMN())

In this example, these are the values for each of the three arguments:

• ReportPeriod – Jul-09, the value we selected on the Dashboard tab
• MainData — this is the full set of data, including the list of months in column A
• COLUMN() — this is 2, the column that the current metric is listed in (this function resolves to “3″ for Orders and to “4″ for Web Traffic)

So, the formula simply takes the currently selected month, finds the row with that value in the data array, and then moves over to the column that matches the current column of the formula:

Slick, huh? And, because the ReportPeriod data validation dropdown on the Dashboard worksheet is referencing the first column of the data on the Data tab, the VLOOKUP will always be able to find a matching value. (Read that last sentence again if it didn’t sink in — it’s a nifty little way of ensuring the robustness of the report)

This little bit of cleverness is really just a setup for the next step, which is setting up the data ranges that we’re going to chart. Conceptually, it’s very similar to what we did to find the current metric value, but we want to select the range of data that ends with that value and goes backwards by the number of months specified by ReportRange. So, in the values we selected above, Jul-09 and “6,” we basically want to be able to chart the following range of data:

We’ll do this by defining a named range called Revenue_Range (note how this has a similar naming convention to Revenue_Current, the name we gave the cell with the single value — this comes in handy for keeping track of things when setting up the dashboard). We can’t use VLOOKUP, because that function doesn’t really work with arrays and ranges of data. Instead, we’ll use a combination of the MATCH function (which is sort of like VLOOKUP on steroids) and the INDEX function (which is a handy way to grab a range of cells). Pull your hat down and fasten your seatbelt, as this one gets a little scary. Ultimately, the formula looks like this:

=INDEX(MainData,MATCH(ReportPeriod,DateSelector)-ReportRange+1,COLUMN(Revenue_Current)):INDEX(MainData,MATCH(ReportPeriod,DateSelector),COLUMN(Revenue_Current))

It’s really not that bad when you break it down. I promise!

Working from the outside in, you’ve got a couple of INDEX() functions. Think of those as being INDEX(First Cell) and INDEX(Last Cell).

The range is defined, in pseudocode, as simply:

=INDEX(First Cell):INDEX(Last Cell)

The Last Cell calculation is slightly simpler to understand. As a matter of fact, this is really just trying to identify the cell location (not the value in the cell) of the current value for revenue — very similar to what we did with the VLOOKUP function earlier. The INDEX function has three arguments: INDEX(array,row_num,column_num). Here’s how those are getting populated:

• array — this is simply set to MainData, the full range of data
• row_num — this is the row number within the array that we want to use; we’ll come back to that in just a minute
• column_num — we use a similar trick that we used on the Revenue_Current function, in that we use the COLUMN() formula; but, since we set up this range simply as a named range (as opposed to being a value in a cell), we can’t leave the value of the function blank; so, we populate the function with the argument of Revenue_Current — we want to grab the column that is the same column as where the current revenue value is populated in the top row.

Now, back to how we determine the row_num value. We do this using the MATCH function, which we need to use on a 1-dimensional array rather than a 2-dimensional array (MainData is a 2-dimensional array). All we want this function to return is the number of the row in the MainData array for the currently selected report period, which, as it turns out, is the same row as the currently selected report period in the DataSelector range. The formula is pretty simple:

MATCH(ReportPeriod,DateSelector)

The formula looks in the DateSelector range for the ReportPeriod value and finds it…in the seventh row of the array. So, row_num is set to 7.

INDEX(First Cell) is almost identical to INDEX(Last Cell), except the row_num value needs to be set to 2 instead of 7 — that will make the full range match the ReportRange value of 6. So, row_num is calculated as:

MATCH(ReportPeriod,DateSelector)-ReportRange+1

(The “+1″ is needed because we want the total number of cells included in the range to be ReportRange inclusive.)

Now, that’s not all that scary, is it? We just need to drop the full formula into a named range called Revenue_Range by selecting Formulas » Name Manager » New, naming the range Revenue_Range, and inserting the formula:

=INDEX(MainData,MATCH(ReportPeriod,DateSelector)-ReportRange+1,COLUMN(Revenue_Current)):INDEX(MainData,MATCH(ReportPeriod,DateSelector),COLUMN(Revenue_Current))

The whole formula is there, even if you can’t see it!

Repeat this last step to create two more named ranges with slightly different formulas (the differences are in bold):

• Orders_Range: =INDEX(MainData,MATCH(ReportPeriod,DateSelector)-ReportRange+1,COLUMN(Orders_Current)):INDEX(MainData,MATCH(ReportPeriod,DateSelector),COLUMN(Orders_Current))
• WebTraffic_Range: =INDEX(MainData,MATCH(ReportPeriod,DateSelector)-ReportRange+1,COLUMN(WebTraffic_Current)):INDEX(MainData,MATCH(ReportPeriod,DateSelector),COLUMN(WebTraffic_Current))

Tip: After creating one of these named ranges, while still in the Name Manager, you can select the range and click into the formula box, and the current range of cells defined by the formula will show up with a blinking dotted line around them.

You’re getting sooooooo close, so hang in there! In order for the chart labels to show up correctly, we need to make one more named range. We’ll call it Date_Range and define it with the following formula (this is just like the earlier _Range formulas, but we know we want to pull the dates from the first column, so, rather than using the COLUMN() formula, we simply use a constant, “1″:

=INDEX(MainData,MATCH(ReportPeriod,DateSelector)-ReportRange+1,1):INDEX(MainData,MATCH(ReportPeriod,DateSelector),1)

If you want, you can fiddle around with the different settings on the Dashboard tab and watch how both the “Current” values and (if you get into Name Manager) the _Range areas change.

OR…you can move on to the final step, where it all comes together!

Dashboard Tab Setup — Part 2 (the final step)

It’s back over to the Dashboard worksheet to wrap things up.

Insert a 2-D Line chart and resize it to be less than totally obnoxious. It will just be a blank box initially:

Right-click on the chart and select Select Data. Click to Add a new series and enter “Revenue” (without the quotes — Excel will add those for you) as the series name and the following formula for the series values:

=DynamicCharts_Example.xlsx!Revenue_Range

(Change the name of the workbook if that’s not what your workbook is named)

Click to edit the axis labels and enter a similar formula:

=DynamicCharts_Example.xlsx!Date_Range

You will now have an absolutely horrid looking chart (thank you, Excel!):

Tighten it up with some level of formatting (if you just can’t stand to wait, you can go ahead and start flipping the dropdowns to different settings), drop “=ReportPeriod” into cell E6 and “=Revenue_Current” into cell E7, and you will wind up with something that looks like this:

Okay, so that still looks pretty horrid…but this isn’t a post about data visualization, and I’m trying to make the example as illustrative as possible. In practice, we use this technique to populate a slew of sparklines (no x-axis labels) and a couple of bar charts, as well as some additional calculated values for each metric.

To add charts for orders and web traffic is a little easier than creating the initial chart. Just copy the Revenue chart a couple of times (if you hold down <Ctrl>-<Shift> and then click and drag the chart it will make a copy and keep that copy aligned with the original chart).

Then, simply click on the data line in the chart and look up at the formula box. You will see a formula that looks something like this:

=SERIES(“Revenue“,DynamicCharts_Example.xlsx!Date_Range,DynamicCharts_Example.xlsx!Revenue_Range,1)

Change the bolded text, “Revenue,” to be “Orders” and the chart will update.

Repeat for a Web Traffic chart, and you’ll wind up with something like this:

And…for the magic…

<drum rollllllllllll>

Change the dropdowns and watch the charts update!

So, is it worth it? Not if you’re going to produce one report a couple of times and move on. But, if you’re in a situation where you have a lot of recurring, standardized reports (not as mindless report monkeys — these should be well-structured, well-validated, actionable performance measurement tools), then the payoff will hit pretty quickly. Updating the report is simply a matter of updating the data on the Data tab (some of which could even be done automatically, depending on the data source and the API availability), then the Report Period dropdown on the Dashboard tab can be changed to the new report period, and the charts get automatically updated! You can then spend your time analyzing and interpreting the results. Often, this means going back and digging for more data to supplement the report…but I’m teetering on the verge of much larger topic, so I’ll stop…

As an added bonus, you can hide the Data tab and distribute the spreadsheet itself, enabling your end users to flip back and forth between different date ranges — a poor man’s BI tool, if ever there was one (in practice, there will seldom be any real insight gleaned from this limited number of adjustable dropdowns, and that’s not the reason to set them up in the first place).

I was curious as to what it would take to create this example from scratch and document it as I went. As it’s turned out, this is a lonnnnnnngggg post. But, if you’ve skimmed it, get the gist, and want to start fiddling around with the example used here, feel free to download it!

Happy dynamic charting!

All sorts of agencies, social media technology companies, and analyst firms have hit on a lead generation gold mine: write a paper, conduct a webinar, or host an event that includes “ROI” and “social media” in any combination with any set of connecting articles and prepositions, and the masses will come! The beauty of B2B marketing is that the title and description of any such content is all that really needs to be compelling to get someone to fill out a registration form — the content itself can totally under-deliver…and it’s too late for the consumers of it to remove themselves as leads when they realize that’s the case!

Of the dozens of webinars I’ve attended, blog posts I’ve read, and white papers I’ve perused that fall into this “social media ROI” bucket, not a single one has actually delivered content about calculating a true return on investment in a valid and realistic way based on social media investments. That’s not to say they don’t have good content, but they all wind up with the same basic position: have clear objectives for your social media efforts, establish a set of relevant KPIs/metrics based on those objectives, and then measure them!

When a paper titled The ROI of Social Media Marketing (available behind a registration form from Crowd Factory — see the first paragraph above!) written by Forrester analyst Augie Ray (and others) came across my inbox by way of eMarketer last week, I had low expectations. I scanned it quickly and honed in on the following tip late in the paper:

Don’t use the term “ROI” unless you are referencing financial returns. ROI has an established and understood meaning — it is a financial measure, not a synonym for the word “results.” Marketers who promise ROI may be setting expectations that cannot be delivered by social measures.

Bingo! But, then, what is up with the title of the paper? Was there intense internal pressure at Forrester to write something about calculating social media ROI? Did Ray protest, but then finally cave and write a spot-on paper with an overpromising title…and then slip in an ironic paragraph to poke a little fun? I don’t know, but I loudly read out the above when I saw it (to the mild chagrin of everyone within 50 feet of my desk; I’m known in the office for periodic rants about the over-hyping of ROI, so I mostly just generated bemused eyerolls).

The idea the paper posits is to take inspiration from the balanced scorecard framework — not taken to any sort of extreme, but pointing out that social media impacts multiple differing facets of a brand’s performance. Ray neither presses to have a full-blown, down-to-the-individual-performer application of balanced scorecard concepts, nor does he stick to the specific four dimensions of a pure balanced scorecard approach. What he does put forth is highly practical, though!

The four dimensions Ray suggests are:

• Financial perspective (the only dimension that does map directly to a classic balanced scorecard approach) — revenue and cost savings directly attributable to social media
• Brand perspective — classic brand measures such as awareness, preference, purchase intent, etc.
• Risk management perspective — “not about creating positive ROI but reducing unforeseen negative ROI in the future” — a social media presence and engaged customers improve a brand’s ability to respond in a crisis; in theory, this has real value that can be estimated
• Digital perspective — measuring the impact of social media on digital outcomes such as web site traffic, fan page growth, and so on; Ray points out, “In isolation, digital metrics provide a weak assessment of actual business results, but when used in concert with the other perspectives within a balanced marketing scorecard, they become more powerful and relevant.” Right on!!!

The paper is chock full of some fantastic little gems.

Which isn’t to say I agree with everything it says. One specific quibble is that, when discussing the financial perspective, the paper notes that media mix modeling (MMM) is one option for quantifying the financial impact of individual social media channels; while Ray notes that this is an expensive measurement technique, that’s actually an understatement — MMM is breaking down with the explosion of digital and social media…but that’s a subject for a whole other post!

At the end of the day, social media is complicated. It’s not measurable through a simple formula. It can strengthen a brand and drive long-term results that can’t be measured in a simplistic direct response model. Taking a nuanced look at measuring your social media marketing results through several different perspectives makes sense!

At least once a week in my role at Resource Interactive, I get asked some flavor of this basic question: “How do I measure the impact of my digital/social media investment?” It’s a fair question, but the answer (or, in some cases, the impetus for the question) is complicated and, often, is related to the frustration gap — the logical leap that, since digital marketing is the most measurable marketing medium of all time, it enables a near-perfect linkage between marketing investments and financial results.

It’s no fun to be the bearer of Reality Tidings when asked the question, especially when it’s easy to sound like the reason we can’t make a clean linkage is because it’s really hard or we just aren’t smart enough to do so. There are countless sharp, well-funded people in the marketing industry trying to answer this exact question, and, to date, there is a pretty strong consensus when you get a group of these people together:

1. We all wish we had “the answer”
2. The evolution of consumers and the growth of social media adoption has made “the answer” more elusive rather than less
3. “The answer” is not something that is just around the corner — we’re chipping away at the challenge, but the increasing fragmentation of consumer experiences, and the explosion of channels available for marketers to engage with those consumers, is constantly increasing the complexity of “the question”

That’s not an easy message to convey.

So, How’s That Explanation Working Out for Ya’?

It’s a tough row to hoe — not just being a data guy who expends a disproportionate amount of energy, time, and brainpower trying to find a clean way to come at this measurement, but trying to concisely explain the complexity. Of late, I’ve landed on an analogy that seems to hold up pretty well: measuring marketing is like measuring the Mississippi River.

If you are tasked with measuring the Mississippi, you can head to New Orleans, don hip waders, load up a rucksack with instruments, and measure all sorts of things at the river’s mouth: flow volume, fish count, contaminants, etc. That’s analogous to measuring a brand’s overall marketing results: brand awareness, share of voice in the industry, customer satisfaction, revenue, profitability, etc. The explosion of digital and social media actually makes some of this measurement easier and cheaper than ever before through the emergency of various online listening and social media analytics platforms.

While these “mouth of the river” measures are useful information — they are measures of the final outcome that really matters (both in the case of the Mississippi and brand marketing) — how actionable are they, really? As soon as results are reported, the obvious questions come: “But, what’s causing those results?”

What causes the Mississippi River to flow at a certain rate, with a certain number of a fish, with a certain level of a certain contaminant where it empties into the Gulf of Mexico? It’s the combination of all that is happening upstream…and the Mississippi’s headwaters reach from Montana (and even western Canada) all the way to Pennsylvania! The myriad headwaters come together many times over — they interact with each other just as different marketing channels interact with and amplify each other — in thousands of ways over time.

If we’re looking to make the Mississippi cleaner, we could travel to western Kansas and check the cleanliness of the Smoky Hill River. If it’s dirtier than we think it should be, we can work to clean it up. But, will that actually make the Mississippi noticeably cleaner? Logic tells us that it certainly can’t hurt! But, rational thought also tells us that that is just one small piece in an almost incomprehensibly puzzle.

With marketing, we have a comparably complex ecosystem at work. We can measure the growth of our Facebook page’s fans, but how is that interacting with our Twitter feed and our web site and our TV advertising and blog posts that reference us and reviews of our products on retailer sites and our banner ads and our SEO efforts and our affiliate programs and our competitors’ presence in all of these areas and… ugh! At a high level, a marketer’s Mississippi River looks like this:

Not only does each of the “managed tactics” represent dozens or even hundreds of individual activities, but environmental factors can be a Mack truck that dwarfs all of the careful planning and investment:

• Cultural trends — do you really think that the Silly Bandz explosion was carefully orchestrated and planned by Silly Bandz marketers (the CEO of Silly Bandz certainly thinks so — I’m skeptical that there wasn’t a healthy dose of luck involved)
• Economic factors — during a global recession, most businesses suffer, and successful marketing is often marketing that manages to simply help keep the company afloat
• Competition — if you are a major oil producer, and one of the top players in your market inadvertently starts dumping an unfathomable amount of crude into the Gulf of Mexico, your brand begins to look better by comparison (although your industry as a whole suffers on the public perception front)

“It’s complicated” is something of an understatement when trying to accurately measure either the Mississippi River or marketing!

So, We Just Throw Up Our Hands and Give Up?

Just because we cannot practically achieve the Holy Grail of measurement doesn’t mean that we can’t be data driven or that we can’t quantify the impact of our investments — it just means that we have to take a structured, disciplined approach to the effort and accept (and embrace) that marketing measurement is both art and science. In the Mississippi River example, there are really three fundamentally different measurement approaches:

• Measure the river where it flows into the Gulf of Mexico
• Measure all (or many) of the tributaries that feed into each other and, ultimately, into the main river
• Model the whole river system by gathering and crunching a lot of data

The first two approaches are reasonably straightforward. The third gets complex, expensive, and time-consuming.

For marketers — and I’m just going to focus on digital marketing here, as that’s complex enough! — we’ve got an analogous set of options (as it should be…or I wouldn’t be calling this an analogy!):

Measuring the direct and cross-channel effect of each tactic on the overall brand outcomes is nirvana — that’s what we’d like to be able to do in some reasonably reliable and straightforward way. And, we’d like that to be able to factor in offline tactics and even environmental factors. For now, the most promising approach is to use panel-based measurement for this — take a sufficiently large panel of volunteers (we’re talking 10s or 100s of thousands of people here) who voluntarily have their exposure to different media tracked, and then map that exposure to brand results: unaided recall of the brand, purchase intent, and even actual purchases. But, even to do this in an incomplete and crude fashion is currently an expensive proposition. That doesn’t mean it’s not an investment worth making — it just means it’s not practical in many, many situations.

However, we can combine the other two approaches — measurement of tactics (tactics include both always-on channels such as a Facebook page or a web site, as well as campaigns that may or may not cut across multiple channels) and measurement of brand results. The key here is to have clearly defined objectives at the brand level and to align your tactic-level measurement with those same objectives. I’m not going to spend time here expanding on clear definition of objectives, but if you’re looking for some interesting thinking there, take a look at John Lovett and Jeremiah Owyang’s white paper on social marketing analytics. They list four basic objectives that social media can support. At the overall brand level, I think there are basically eight possible objectives that a consumer brand might be tackling (with room for any brand to have one or two niche objectives that aren’t included in that list) — and, realistically, focusing in on about half that many is smart business. But I said I wasn’t going to expand on objectives…

What is important is to apply the same objectives at the brand and the tactic level — each tactic isn’t necessarily intended to drive all of the brand’s objectives, so being clear as to which objectives are not expected to  be supported by a given tactic can help set appropriate expectations.

Just because the objectives should align between the tactic and the brand-level measurement does NOT mean that the measures used to track progress against each objective should be the same. For instance, if one of your objectives is to increase engagement with consumers, at the brand level, this may be measured by the volume and sentiment of conversations occurring online about the brand (online listening platforms enable this measurement in near real-time). For the brand’s Facebook page (a tactic), which shares the objective, the measure may, instead, be the number of comments and likes for content posted on the page.

But…How Does That Really Help?

By using objectives to align the measurement of tactics and the measurement of the brand, you wind up with a powerful performance measurement tool:

As simplistic and extreme examples, consider the situation where all of your tactics are performing swimmingly, but the brand overall is suffering. This might be the result of a Mack truck environmental factor — which, hopefully, you are well aware of because you are a savvy marketer and are paying attention to the environment in which you are operating. If not, then you should consider revisiting your overall strategy — do you have the wrong tactics in place to support the brand outcomes you hope to achieve?

On the other hand, consider a situation where the brand overall is suffering and the tactics as a whole are suffering. In that case, you might have a perfectly fine strategy, but your tactical execution is weak. The first order of business is to get the tactics clicking along as designed and see if the brand results improve (in a sense, this is a preferable situation, as it is generally easier to adjust and improve tactics than it is to overhaul a strategy).

In practice, we’re seldom working in a world where things are as black and white (or as green and red) as this conceptual scenario. But, it can certainly be the case that macro-level measurement of an objective — say, increasing brand awareness — is suffering while the individual tactics are performing fine. Let’s say you heavily invested in your Facebook page as the primary tactic to drive brand awareness. The page has been growing total fans and unique page views at a rapid clip, but your overall brand awareness is not changing. You may realize that you’re starting from a very small number of fans on Facebook, and your expectation that that tactic will heavily drive overall brand awareness is not realistic — you need to introduce additional tactics to really move the brand-level awareness needle.

In the End, It’s Art AND Science

Among marketing measurement practitioners, the phrase “it’s art and science” is oft-invoked. It sounds downright cliché…yet it is true and it’s something that many marketers struggle to come to terms with. Look at marketing strategy development and execution this way:

“The data” is never going to generate a strategy — knowing your customers, your company, your competition, and a bevy of other qualitative factors should all be included in the development or refinement of your strategy. Certainly, data can inform and influence the strategy, but it cannot generate a strategy on its own. Performance measurement, though, is all about science — at its best, it is the quantitative and objective measurement of progress towards a set of objectives through the tracking of pre-defined direct and proxy measures. Dashboards can identify trouble spots and can trigger alerts, but their root causes and remediation may or may not be determined from the data — qualitative knowledge and hypothesizing (“arts”) are often just as valuable as drilling deeper into the data.

It’s a fun world we live in — lots of data that can be very valuable and can drive both the efficiency and effectiveness of marketing investments. It just can’t quite deliver nirvana in an inexpensive, easy-to-use, web-based, real-time dashboard!

Hubspot released a new ebook a couple of weeks ago, compiled and edited by the Who’s Blogging What folk, with yours truly contributing the Facebook measurement chapter.

It’s behind a registration page, but it’s a good 30-page read. Topics covered include:

• Creating a Facebook Page
• Examples of Effective Pages
• Six Ways to Get Found on Facebook
• Getting People to “Like” Your Facebook Page
• Developing Content/Inbound Marketing for Facebook
• Leveraging Facebook for Ecommerce
• Facebook’s Potential to Make Sales Become Viral
• Analyzing Facebook Traffic

It was a fun little project to contribute to! Check it out!

I attended the Foresee Results user summit last month, and John Lovett of Web Analytics Demystified was the keynote speaker. It’s a credit to my general lack of organization that I wasn’t aware he was going to be speaking, much less keynoting!

John showed this diagram when discussing the importance of recognizing your capabilities:

The diagram starts to get at the never-ending quest to obtain a “360 degree customer view.” A persistent misperception among marketers when it comes to web analytics is that behavioral data alone can provide a comprehensive view of the customer. It really can’t — force your customers to behave in convoluted ways and then only focus on behavioral data, and you can draw some crazily erroneous conclusions (“Our customers appear to visit our web site and then call us multiple times to resolve a single issue. They must like to have a lot of interactions with us!”).

Combining multiple data sources — behavioral and attitudinal — is important. As it happened, Larry Freed, the Foresee Results CEO, had a diagram that came at the same idea:

This diagram was titled “Analytics Maturity.” It’s true — slapping Google Analytics on your web site (behavioral data) is cheap and easy. It takes more effort to actually capture voice-of-the-customer (attitudinal) data; even if it’s with a “free” tool like iPerceptions 4Q, there is still more effort required to ensure that the data being captured is valid and to analyze any of the powerful open-ended feedback that such surveys provide. Integrating behavioral and attitudinal data from two sources is tricky enough, not to mention integrating that data with your e-mail, CRM, marketing automation, and ERP systems and third-party data sources that provide demographic data!

It’s a fun and challenging world we live in as analysts, isn’t it?

(On the completely off-topic front: I did snag 45 minutes one afternoon to walk around the University of Michigan campus a bit, as the conference was hosted at the Ross School of Business; a handful of pictures from that moseying is posted over on Flickr.)

I was flipping through the slides from a workshop that Teradata put on at The Ohio State University several months ago, and one of the diagrams jumped out and resonated with me. As I did some digging, it turns out this diagram has been floating around since at least 2004, if not for longer. It was created by Dr. Richard Hackathorn of Bolder Technology Inc. (BTI).

There are a slew of lousy recreations of the diagram (the original diagram wasn’t so hot, either). Rather than recreating it myself, I just snagged one of the cleaner ones, which came from a 4-year-old TDWI article:

The point of the diagram, as well as of most of the derivative works that reference it, is that the value of information has a direct relationship to the speed with which you can react to it. And, there are three distinct things that have to happen between the business event that triggers the information and ation actually being taken.

I don’t know if there is any real math or science behind the shape of the curve. As diagrammed, this says that you’ve already lost most of your value by the time you get to the “decision latency” point in the process. I don’t know that that is necessarily true in most cases. The diagram supports the assertions by all of the various BI/data tool vendors that data needs to be available in near real-time (and, of course, that’s something that all of the vendors claim they are better at than their competition).

But, is the data latency and analysis latency really the big value driver for marketers? In some cases, the data latency is a structural issue — conducting a campaign where the people exposed to it are likely to not convert for anywhere from 1 to 30 days…means you really need to wait for 30 days to see how the campaign played out. Analysis latency is real…but this really can be broken into two pieces: 1) the time to do the analysis and get it packaged for delivery, and 2) the time to schedule/coordinate the information delivery. And, then, certainly the decision latency is real.

In short, the “action time” components totally make sense, and it’s good to understand them. The shape of the curve, though, doesn’t necessarily stand up to scrutiny when looked at through a marketer’s lens.

Columbus Web Analytics Wednesday was last week — sponsored by Omniture, an Adobe company, and the topic wound up being “Facebook Measurement” (deck at the end of this post).

For some reason, Monish Datta cropped up — prominently — in half of the pictures I took while floating around the room. In my never-ending quest to dominate SEO for searches for Monish, this was well-timed, as I’m falling in the rankings on that front. You’d think I’d be able to get some sort of cross-link from http://www.monishdatta.com/, but maybe that’s not to be.

We had another great turnout at the event, AND we had a first for a Columbus WAW: a door prize. Omniture provided a Flip video camera and a copy of Adobe Premier Elements 8 to one lucky winner. WAW co-organizer Dave Culbertson presented the prize to the lucky winner, Matt King of Quest Software:

Due to an unavoidable last minute schedule change, I wound up pinch-hitting as the speaker and talked about Facebook measurement. It’s been something I’ve spent a good chunk of time exploring and thinking about over the past six months, and it was a topic I was slated to speak on the following night in Toronto at an Omniture user group, so it wound up being a nice dry run in front of a live, but friendly crowd.

I made some subsequent updates to the deck (improvements!), but below is substantially the material I presented:

In June, Columbus Web Analytics Wednesday is actually going to happen in Cincinnati — we’re planning a road trip down and back for the event. We’re hoping for a good showing!

We’ve been talking about trying to pull this off for a while, but it looks like it’s going to finally happen!

We’re excited to announce that for June’s event, we’ll be bringing together the Columbus and Cincinnati Web Analytics Wednesday groups! Join us on Wednesday, June 23, 2010 on the roof of 720 E. Pete Rose Way in Cincinnati for Web Analytics Wednesday. This month’s event is sponsored by Bridge Worldwide, CRM Metrix, and Resource Interactive. Our guest speaker is Scott Beck, VP of Communications & Media – Kroger at dunnhumby. He’s also an Adjunct Professor in the Graduate School of the Williams College of Business at Xavier University.

If you’re planning to attend, here’s what you need to do:

• Cincinnati residents — click over to the Cincinnati WAW event site and RSVP (Note: parking will be $5)
• Columbus residents — click over to the meetup posted on the Techlife Columbus meetup.com site and RSVP there. You’ll be prompted as to whether you are driving yourself or interested in carpooling when you register. Then, click over to the Columbus WAW event site and register there as well. (I know it’s a pain, but we really need both systems to effectively coordinate in this case.)

And now, a word about our sponsors:

Bridge Worldwide

Bridge Worldwide is a top 50 digital and relationship marketing agency. Bridge is a recent Gold Lion winner at Cannes, and the company follows a belief in Marketing With Meaning. Clients include Procter & Gamble, Abbott, ConAgra Foods, Kroger, Luxottica, Johnson & Johnson and Red Bull. Bridge Worldwide has locations in Cincinnati and New York City, and is part of WPP, the world’s largest agency holding company with over 100,000 employees.

CRM Metrix

CRM Metrix offers a full range of digital market research solutions to help clients make the best decisions at all stages of the digital marketing development process and to maximize the return on their digital marketing investment. Their market research solutions are designed for all aspects of digital marketing such as brand website, online ads, online campaigns, email CRM programs, social media, widgets, blogs, online sponsorships, and micro sites. A global one-stop shop.

Resource Interactive

Resource Interactive is one of the nation’s top-rated agencies with offices in Columbus, Cincinnati, and Palo Alto and more than 300 associates. Founded and headquartered in Columbus, Ohio, their Midwestern work ethic keeps them grounded without limiting their global reach. Now in their 29th year, Resource leads Fortune 500 companies around the world through an ever-evolving internet economy. They bring research-driven insights to our work, creating bold opportunities for consumer engagement and competitive advantage

We’re hoping for a good turnout for the event — strengthening the web analytics, SEO, SEM, and digital marketing community that already exists between Cincinnati and Columbus.

Anyone who has been working with web analytics for more than a week or two has inevitably asked or been asked to explain why two different numbers that “should” match don’t:

• Banner ad clickthroughs reported by the ad server don’t match the clickthroughs reported by the web analytics tool
• Visits reported by one web analytics tool don’t match visits reported by another web analytics tool running in parallel
• Site registrations reported by the web analytics tool don’t match the number or registrations reported in the CRM system
• Ecommerce revenue reported by the web analytics tool doesn’t match that reported from the enterprise data warehouse

In most cases, the “don’t match” means +/- 10% (or maybe +/- 15%). And, seasoned analysts have been rattling off all the reasons the numbers don’t match for years. Industry guru Brian Clifton has written (and kept current) the most comprehensive of white papers on the subject. It’s 19 pages of goodness, and Clifton notes:

If you are an agency with clients asking the same accuracy questions, or an in-house marketer/analyst struggling to reconcile data sources, this accuracy whitepaper will help you move forward. Feel free to distribute to clients/stakeholders.

It can be frustrating and depressing, though, to watch the eyes of the person who insisted on the “match” explanation glaze over as we try to explain the various nuances of capturing data from the internet. After a lengthy and patient explanation, there is a pause, and then the question: “Uh-huh. But…which number is right?” I mentally flip a coin and then respond either, “Both of them” or “Neither of them” depending on how the coin lands in my head. Clifton’s paper should be required reading for any web analyst. It’s important to understand where the data is coming from and why it’s not simple and perfect. But, that level of detail is more than most marketers can (or want to) digest.

After trying to educate clients on the under-the-hood details…I almost wind up at a point where I’m asked the “Well, which number is right?” question. That leads to a two-point explanation:

• The differences aren’t really material
• What matters in many, many cases is more the trend and change over time of the measure — not its perfect accuracy (as Webtrends has said for years: “The trends are more important than the actual numbers. Heck, we put ‘trend’ in our company name!”

This discussion, too, can have frustrating results.

I’ve been trying a different tactic entirely of late in these situations. I can’t say it’s been a slam dunk, but it’s had some level of results. The approach is to list out a handful of familiar situations where we get discrepant measures and are not bothered by it at all, and then use those to map back to the data that is being focussed on.

Here’s my list of examples:

• Compare your watch to your computer clock to the time on your cell phone. Do they match? The pertinent quote, most often attributed to Mark Twain, is as follows: “A man with one watch knows what time it is; a man with two watches is never quite sure.” Even going to the NIST Official U.S. Time Clock will yield results that differ from your satellite-synched cell phone. Two (or more) measures of the time that seldom match up, and with which we’re comfortable with a 5-10 minute discrepancy.

Photo courtesy of alexkerhead

• Your bathroom scale. You know you can weigh yourself as you get out of the shower first thing in the morning, but, by the time you get dressed, get to the doctor’s office, and step on the scale there, you will have “gained” 5-10 lbs. Your clothes are now on, you’ve eaten breakfast, and it’s a totally different scale, so you accept the difference. You don’t worry about how much of the difference comes from each of the contributing factors you identify. As long as you haven’t had a 20-lb swing since your last visit to the doctor, it’s immaterial.

Photo courtesy of dno1967

• For accountants…”revenue.” If the person with whom your speaking has a finance or accounting background, there’s a good chance they’ve been asked to provide a revenue number at some point and had to drill down into the details: bookings or billings? GAAP-recognized revenue? And, within revenue, there are scads of nuances that can alter the numbers slightly…but almost always in non-material ways.

Photo courtesy of alancleaver_2000

• Voting (recounts). In close elections, it’s common to have a recount. If the recount re-affirms the winner from the original count, then the results is accepted and moved on from. There isn’t a grand hullabaloo about why the recount numbers differed slightly from the original account. In really close races, where several recounts occur, the numbers always come back differently. And, no one knows which one is “right.” But, once there is a convergence as to the results, that is what gets accepted.

Photo courtesy of joebeone

That’s my list. Do you have examples that you use to explain why there’s more value in picking either number and interpreting it rather than obsessing about reconciling disparate numbers. I’m always looking for other analogies, though. Do you have any?

At Resource Interactive, our clients frequently ask us, “What social media tools do you recommend?” It’s a tricky question for a couple of reasons:

• Every time we’re asked, the request seems to come from a client with an entirely unique set of social media “tool” needs
• There are somewhere between an oodle and a gazillion social media tools out there (most of them less than a couple of years old) that bill themselves as some form of social media listening/moderation/response/management tool

The truth is, even if we dive into the first issue deeply, there are so many tools out there, and their capabilities blur and blend into each other so much, that it’s a monumental task to rattle off a solid short list of tools with any confidence.

As such, we’re conducting a broad assessment of social media monitoring and publishing platforms to determine an appropriate classification of tool types, understand the capabilities of the platforms that are available in each area, and identify platforms with whom we would like to engage in deeper discussions so we can make appropriate recommendations to our clients.

Who We’re Evaluating

We’re casting as broad a net as we can, ranging from focused channel-specific “management” tools all the way to broad “pick up the pulse of the ‘net” listening platforms. Jeremiah Owyang compiled a list of the former, while Marshall Sponder published a list of the latter late last year. And, at least once a week, we hear of a new tool or two that fits somewhere in this world. Our current list is at the end of this post.

Broad or Deep?

We’re aiming for this survey to be biased towards broader rather than deeper. As such, we’ve put together an online survey that should take ~15 minutes for a company to complete that covers eight main areas. We do not expect any single tool to cover all areas, but that’s part of the point of this exercise – we want to group these tools according to their core capabilities:

• Targeted Listening – the ability to monitor conversations occurring in specific places where a brand may have a presence online, such as a Facebook fan page, a specific Twitter account, or a specific web site
• Broad Listening – the ability to comprehensively monitor conversations and content posted across the internet (with filter/query configuration narrowing down the content being monitored based on keywords or other criteria)
• Listening Features – the specific capabilities and analysis that the tool performs in an automated fashion (sentiment, keyword-based tagging, etc.)
• Publishing Targets – the ability to publish content from within the tool to various social media targets
• Publishing Features – the specific capabilities available for publishing content (scheduling, preview, approval workflow, etc.)
• Brand Response – the ability to react/respond to specific comments, tweets, or posts that are identified through the targeted listening described earlier
• Analytics – the ability to provide dashboards and reports
• Pricing Options – the different pricing models available for the tool (not specific costs – just how their pricing works)

Response Solicitation

As a first cut, we’re looking to spread the word of this effort via social media itself. Ideally, some of the tools (the “broad listening”-oriented ones) will pick up the mentions of this survey (for the smaller tools, I would expect even a Google Alert would pick this post up for them). So, please, help us get the word out, and leave a comment if you spot any tools missing from our list.

If you are a company that has a platform in this list (or one that isn’t in this list but that we should include), ping me on Twitter at @tgwilson (if you follow me, I’ll follow back within 24 hours and we can DM). Or, you can take the “g” out of my Twitter username and tack “@resource.com” on the end of it to go old-school and shoot me an e-mail. I’ll send you a link to the online survey (we’re not publishing the link so that we can make sure that only official company representatives respond for their products).

Tools List

Below is the list of tools we’re hoping to include:

• Alterian SM2
• Awareness Networks, Social Marketing Hub
• Buddy Media
• Buzz Logic
• Collective Intellect
• Context Optional
• Converseon
• CoTweet
• Cymfony
• Distributed Engagement Channel by DEC
• ebiquity NewsLive
• Envisional
• Feedback Ferret
• InsideView
• Interwoven Social Media Analysis
• Jive Market Engagement (formerly Filtrbox)
• Jodange
• KeenKong
• KnowEm
• Marchex Reputation Management
• MarkMonitor Brand Protection
• Market Sentinel
• MediaFunnel
• Meltwater Buzz
• Mutual Mind
• NetBase ConsumerBase
• Nielsen BlogPulse
• Nielsen BuzzMetrics
• Objective Marketer
• Overtone OpenMic
• Perception Metrics
• Postling
• Radian6
• Reputica
• Reputrace
• SAS Social Media Analytics
• Scout Labs
• SEER
• Social Mention
• SocialTalk
• Spiderfly
• SpredFast
• Sprinklr
• Trackur
• Trupulse
• Viralheat
• Vitrue
• Vocus
• Who’s Talkin

Stay tuned!