A (Slightly) Different Way to Calculate Net Promoter Score

I spent some time today thinking about Net Promoter Score, which is a metric that’s at the heart of Customer Experience Management. I wrote about it in a post titled The Most Important Number in Customer Experience Management, but in a nutshell, it’s the percent of a company’s customer base comprised of promoters less the percent comprised of detractors:

Net Promoter Formula

I don’t take issue with this representation, but I’ve wondered in what way  ’re-jigging’ the numbers could make the traditional NPS formula read in a new way. For instance, with simple algebra you can factor out a (1/# of Customers) from both terms on the left side of the equation, leaving:

The Net Promoter formula with (1/# of Customers) factored out

If I like anything about this representation, it’s the subtly with which it encourages you to calculate the difference between “# of Promoters” and “# of Detractors” first. That becomes our base, and (1/# of Customers) our scaling term.

It’s nothing earth shattering, but it’s a different way of thinking. For that reason it may be a valuable tool in the CXM practitioner’s toolkit.

How to Make Your Own Business School Rankings

[Depending on the path you took here, you may only be interested in downloading the spreadsheet for your own use. Download the Business School Weights & Rankings spreadsheet. Read on to learn how I made it as this process may prove useful to you in your own development.]

Anyone who has applied to business school knows that rankings tend to dominate the dialogue around the merits of one program versus the next. Right or not (and I tend to agree that they’re a one-dimensional answer to a complex question), they’re darn good fodder for conversation.

For a while I flirted with the idea of tailoring a ranking that would allow me to assign weights to the things that mattered most to me, like driving distance from Pittsburgh and the reputation of its Information Technology concentration. Incidentally I never got around to it, and today don’t feel so inclined as I’m pleased to be attending Tepper in Fall 2013.

However, I knew this would be a good topic for a mixed Excel Hacks/Business School blog post. I nosed around the web til I found a small data set from Wikipedia which conveniently lists current B-school rankings in table form. Leave it Wikipedia to make our work *almost* too easy.

I grabbed the table via simple copy and paste, then dumped it in Excel. I’m anal about formatting so I left-clicked the box in the top-left corner of the spreadsheet to select all, then right-clicked in the highlighted area and clicked “Remove Hyperlinks”.

Remove hyperlinks

Next I gave myself some room above the table by highlighting the top three rows of the spreadsheet, right-clicking, and selecting “Insert.” I then copied/pasted the column headers there exactly as they appear in the table.

Copy headers

Finally, I created a column header called “Score” and another called “Ranking,” and appended these to the end of the table.

Now that the table was set up, I needed to massage the data to make it work with this kind of weighted analysis. The source table assigns a blank space when a school is unranked in a particular ranking. Because of the nature of this “weighted” analysis, we need to replace those blanks — which excel will interpret as “0″ — with the ‘opposite’ of a blank. In the interest of time I uniformly applied a score of 108 (1 more than the lowest-ranked 107) to each blank space.

The easiest way I found to do this was via an IF() formula that reads like so:

Replacing blanks in Excel

In English terms, “If the corresponding US News ranking isn’t 0, print the US News ranking. Otherwise return 108.” With the new values still highlighted, I dragged the auto-fill handle to fill the 8 rows across, then double-clicked the handle to auto-fill the rest of the table. Then I copied the new table (identical to the previous, save the replacement of blanks with the number 108), clicked the top-leftmost cell of the first table (Cell D9) and then right-clicked to select “Paste Special” -> “Values”. I then deleted that new table, which by this point had served its purpose of helping me replace the blanks with my designated 108 value.

At this point I needed to assign values to the ranking publishers as I saw fit (If you’re following along, take this time to assign your own). I showed the most favor to The Economist, which I trust as an unbiased news source. I followed with US News, then Business Week and the Financial Times followed by Forbes, America Economica, CNN Expansion, and Business Insider.

With my weights assigned I created a SUMPRODUCT() as follows:

Excel's SUMPRODUCT() formula applied to our weighting exercise.

If you don’t already know, SUMPRODUCT() works by taking two or more arrays of equal dimensions, multiplying their corresponding values, and summing their products. It’s exactly the type of calculation required for a weighted average, so it served me well here.

I then double-clicked the handle to auto-fill all of the scores (If you are following along and get surprising results here, verify that only the first array in your initial calculation is an absolute reference ($D$2:$K$2) and that the other is a relative reference (D5:K5). This allows the school array to ‘follow’ the auto-fill while the reference to your weights remains unchanged.).

List of scores based on our weights

Now that I had a completed list of scores I resorted the table along the “Score” column.

As a finishing touch I applied numeric rankings by placing a 1 in the top spot, then putting this logic in the cell that followed:

Excel ranking formula in an IF() statement

In English it reads, “If the corresponding Score does not equal the Score that precedes it, increment the preceding Ranking by 1. Otherwise, print the preceding ranking.” This handles the fringe case of ties, for which my ranking had 7 (mostly among lower-ranked programs).

After a little cleaning up your resulting ranking table should look something like this:

Piece of the resulting table from this personalized business school ranking exercise

For anyone who’s curious, I’m including my complete rankings for your viewing pleasure. Please note that these rankings are the fruits of an analysis performed in the spirit of learning. As such they’re provided for demonstration purposes only, and in no way reflect my opinions or those of any institutions of which I am a part.

Enjoy!

Business School SCORE NEW RANK
Harvard Business School 2.65 1
Booth School of Business 4.275 2
Stanford Graduate School of Business 4.45 3
The Wharton School 6.425 4
Columbia Business School 7.4 5
MIT Sloan School of Management 8.425 6
Tuck School of Business 8.65 7
Haas School of Business 10.675 8
Kellogg School of Management 11.475 9
Stern School of Business 13.475 10
Darden Graduate School of Business Administration 16.5 11
Fuqua School of Business 17.325 12
Yale School of Management 17.55 13
Ross School of Business 19.75 14
Tepper School of Business 21.325 15
UCLA Anderson School of Management 21.925 16
Samuel Curtis Johnson Graduate School of Management 22.425 17
Goizueta Business School 27.025 18
McCombs School of Business 27.775 19
Marshall School of Business 33.375 20
Kenan-Flagler Business School 33.9 21
McDonough School of Business 35.025 22
Owen Graduate School of Management 35.7 23
Kelley School of Business 38 24
Mendoza College of Business 42.025 25
Jesse H. Jones Graduate School of Management 43.175 26
Wisconsin School of Business 43.35 27
Fisher College of Business 45.95 28
Foster School of Business 46.3 29
Robert H. Smith School of Business 46.7 30
Smeal College of Business 48.45 31
Boston University School of Management 49.35 32
Carlson School of Management 53.35 33
Olin Business School 54.1 34
Mays Business School 57.725 35
Cox School of Business 57.95 36
Tippie College of Business 61 37
Krannert School of Management 64.4 38
Simon School of Business 64.725 39
Marriott School of Management 65 40
UIUC College of Business 66.45 41
UC Davis Graduate School of Management 66.55 42
Scheller College of Business 67.1 43
Carroll School of Management 70.2 44
Terry College of Business 70.2 44
Eli Broad College of Business 71.15 45
Thunderbird School of Global Management 71.3 46
F.W. Olin Graduate School of Business 71.425 47
Babcock Graduate School of Management 71.45 48
W. P. Carey School of Business 72.3 49
The George Washington University School of Business 73.6 50
Freeman School of Business 74.45 51
Merage School of Business 75.875 52
Hult International Business School 77.25 53
Moore School of Business 82.3 54
Mason School of Business 82.6 55
Northeastern Graduate School of Business Administration 82.9 56
Eller College of Management 83.1 57
Warrington College of Business Administration 83.25 58
Katz School of Business 85.45 59
University at Buffalo School of Management 86.1 60
Weatherhead School of Management 86.55 61
Rutgers Business School 87.3 62
Fox School of Business 87.55 63
Trulaske College of Business 90 64
Naveen Jindal School of Management 91 65
University of Connecticut School of Business 91.15 66
Sam M. Walton College of Business 93.75 67
Graziadio School of Business and Management 93.95 68
Howard University School of Business 95.5 69
Hankamer School of Business 96 70
Isenberg School of Management 96 70
University of Tennessee College of Business Administration 96 70
Zicklin School of Business 96.45 71
Auburn University College of Business 96.75 72
Culverhouse College of Commerce 96.75 72
E. Philip Saunders College of Business 96.75 72
Peter F. Drucker and Masatoshi Ito Graduate School of Management 96.75 72
University of Miami School of Business Administration 97.35 73
Iowa State University College of Business 98.25 74
Leeds School of Business 98.25 74
Martin J. Whitman School of Management 98.25 74
E.J. Ourso College of Business 98.85 75
McCallum Graduate School of Business 99.25 76
Pamplin College of Business 99.25 76
Carl H. Lindner College of Business 99.75 77
Leavey School of Business 99.75 77
Fordham Graduate School of Business 99.95 78
David Eccles School of Business 101 79
Neeley School of Business 101 79
Crummer Graduate School of Business 101.55 80
Brandeis International Business School 103.2 81
Kogod School of Business 104 82
John Cook School of Business 104.5 83
Lubin School of Business 105.25 84
Bennett S. LeBow College of Business 107.75 85

The Most Important Number in Customer Experience Management

As part of developing the Pittsburgh Customer Experience Management practice at SDLC I’ve had the pleasure of reading article after article and white paper after white paper on the key metrics attached to CXM. Concerning the latter, the one that has consistently stuck out most to me is a metric called NPS, or Net Promoter Score.

It’s a concept that was originally advanced by Frederick Reichheld in his 2003 Harvard Business Review article, The One Number You Need to Grow. It has since been adopted by Bain (co-owner of its trademark) and CXM strategy consulting firms everywhere.

Here’s how it works: Survey your customers (typically a well-targeted constituency) and ask them to answer the question, “How likely are you to recommend this service to family, friends, or colleagues?” based on a 10-point scale. Those responding 9 or 10 belong to your “promoter” group. Those responding 7 and 8 are assumed “passively satisfied”. Those reporting scores of 6 or lower are presumed “detractors” and likely to share negative experiences or defect altogether.

I find this number so compelling for its correlation with economic performance. Consider Charles Schwab, which saw disappointng annual growth of -3.6% between 2003 and 2005. The firm rebounded significantly in the years that followed, growing an average +17.5% until 2008 when it resumed its place atop the industry. Over that same period the firm’s NPS shifted from an uninspiring -34% to an impressive +42% (Bain). Perhaps more importantly they have sustained their position, and in 2012 were ranked Highest Customer Satisfaction in the industry by JD Power and Associates.

But the highest NPS in absolute terms shouldn’t necessarily be a firm’s end game. According to Bain, “…a business with a low absolute NPS that nonetheless scores higher than all of its direct competitors is indisputably best in class and empirically likely to gain market share.”

The takeaway: When it comes to Customer Experience Management, you don’t have to be perfect. You just have to be better. Those who are better over the long term will achieve the most market share, and by extension, the most excess profits vis a vis their competition.

Customer Experience and Stock Price

I believe that any conversation around Customer Experience Management should hinge on facts and data. That’s why I am always pleased to read passages like the following:

A portfolio of companies whose American Customer Satisfaction Index (ACSI) scores had risen over the past year and were above the national average far outperformed the market, gaining an average value of 1.08 percent per month. Over the 10-year period, the portfolio more than tripled, gaining 212 percent while the Standard & Poor’s 500-stock index rose 105 percent. Accenture, B2B Customer Experience Bluepring. [Emphasis added].

That nontrivial jump in stock price speaks to a truly tremendous advantage awarded those organizations which thoughtfully manage customer experiences. To put the numbers in perspective I created an Excel web app.

Update the Year 1 ACSI value to see the way the stock prices vary with a different start value.

The Hour That Changed My Mind About Scrum

This afternoon I had the pleasure of attending a Lunch & Learn presentation by Kim Hardy (Founder, Salt Productions) on the Principles of Agile Development. In particular, she spoke to the tenets of Scrum development and her predictions for its increasing role in Pittsburgh software development in the coming years.

Allow me to begin by sharing that I take everything I learn with a heaping dose of skepticism. If you’re like me, you too may have thought, “Scrum is iterative like RUP, and all iterative development methodologies are cut from the same cloth.” It won’t surprise those who know me to learn that – fine! – I was wrong.

To begin, Kim demonstrated that Scrum is delightfully easy to understand. There are 4 major project events (called “ceremonies”), 3 project deliverables, and 3 roles. Compare that to the Rational Unified Process, which outlines 4 phases, 6 project disciplines, and more roles, artifacts, and deliverables than you can shake a stick at.

Next, I learned that its success is measured by the production of business value in the form of shipped code. I found that emphasis pleasantly similar to the ethic of most software startups, which some may say contrasts sharply with RUP and its use-case driven development.

Moreover, I discovered that it welcomes requirements changes (late or not). From the Agile Manifesto:

[We] welcome changing requirements, even late in development. Agile processes harness change for the customer’s competitive advantage.

That contrasts sharply with a central tenet of RUP, which calls for the early mitigation of project risk in the form of well-managed requirements and limited change, especially in the later phases of the development lifecycle.

Finally, Kim shared that Scrum places 100% faith in its most important role, the team. That individuals, left to their devices, will self-organize to achieve the goals of the project. (Somewhat related: This put me to mind of McGregor’s Theory Y, which is a management theory I’ve preferred if not one I’ve fully bought into).

In light of all these lessons, today became the first in what I expect will be many investigations into the merits of agile development via the Scrum framework. Thanks, Kim, for opening my mind to this exciting world!

Visualizing Government Agencies by IT Effectiveness

Starting with a data set called IT Investment Operational Performance Metrics (freely available at data.gov) I created a pivot table using Agency Name in the Rows, Actuals have Met/Not Met Targets in the Columns, and Count of Actuals have Met/Not Met Targets in the Values.

I then created a column to capture the agency’s efficiency percentage, styled it as a “%” and added conditional formatting color scales for visual impact.

After a little cleaning up the resulting table looked like so:

From this very simple analysis we can glean the following information:

  • The US Department of International Development, NASA, the Department of Energy, and the Nuclear Regulatory Commission are the most effective in their implementation of IT initiatives.
  • The National Archives and Records Administration, the Small Business Administration, the General Services Administration, and the Department of Housing and Urban Development are the least effective.
  • These 26 agencies achieve their IT initiative targets roughly 3 times out of 4.
  • The Department of Homeland Security and Department of Defense are the most aggressive investors in IT, and their initiatives represent 24% of those captured among the 26 agencies in the set.
And with just a little more digging we can uncover that:
  • Among those initiatives with 10 or more reported targets, the least effective were Commerce Business Systems (CBS) (7 targets met : 21 missed), GLOBAL COMMAND AND CONTROL SYSTEM – ARMY (2 targets met : 10 missed), IRS End User Systems and Services (EUSS) (1 target met : 9 missed), and USCIS – Infrastructure (End User Support) (3 targets met : 7 missed).

Of course this analysis raises a lot of important questions: How can we be confident that the source of our data is accurate? How can we be sure target measurements were applied uniformly? Is it better for an agency invest aggressively with high efficiency or cautiously with less efficiency? Etc, etc…

It’s worth noting that this visualization, like many others, raises at least as many questions as it answers. Fortunately for all ye lovers of data, that’s part of the fun.

What Customer Experience Management Is Not

While performing some market research and competitive analysis on the topic of Pittsburgh Customer Experience Management (CXM) I was directed to an insight piece from Bain, the teaser text of which captured my attention:

The secret to profitable organic growth? Deliver a customer experience that your competitors can’t match.

With buy-in from most of the world’s top strategy consulting firms it’s no question that CXM will long be a part of the dialogue around long-term competitive advantage and organizational value. Yet the topic is burdened by so much misinformation that I thought some clarifying points may be instructional.

In particular, CXM IS NOT…

  • User Experience and Content Strategy repackaged/repurposed for the sake of an easy upsell to extend a consulting engagement.
  • intended for a small corner of an organization. CXM must pervade every reach, from customer service to leadership and governance, in order for the performing organization to realize its value.
  • about any one technology. Adobe (Adobe Digital Enterprise Platform), IBM (IBM Customer Experience Suite), and others have compelling offerings in this space. Moreover, good CXM is as much about company culture and organizational adoption as it is about any tool or technique.
  • a silver bullet or a quick fix. Only when CXM is institutionalized is its greatest value realized. This occurs not in the early phases of its rollout, but later, once ‘buy-in’ is achieved by employees at every level and its consistent application extended.
  • a noncritical, half-step evolution from Customer Relationship Management. Where CRM looks from the organization to the customer, CXM starts with the customer, then looks inward to align critical technologies and business processes in the promotion of positive customer experiences.

Anything to add to the list? Let me know in the comments, or email me at brianlash [at] live [dot] com. And for more great reading on CXM see The State of Customer Experience, 2012 (Forrester article – membership required), or visit the SDLC Partners blog where I will continue the conversation about CXM fact and fiction.

Using Pivot Tables to Extract Meaningful Data from Project Time Collection Systems

Anyone responsible for tracking enterprise time from pre-canned reports from the likes of Cognos knows how much of a hassle it can be to repurpose the data to fit your particular needs. You may, for instance, only be interested in project time charged to a list of projects, or you may need to conditionally display time based on an employee’s function or the relevant reporting period.

Enter pivot tables (or “PivotTables” in Microsoft-speak). Pivot tables are crosstab reports which give meaning to complex data in literally seconds, with the ability to modify the presentation as required by your unique situation.

Technically speaking, pivot tables are data visualization tools which give meaning to “flat” data, or two-dimensional tables. Their most powerful feature is their ability to quickly summarize (or “cross-tabulate”) data as it appears in multiple columns. They answer questions of the variety, “How many households voted Republican in the 15th precinct?” and “In which month is my net cash flow the highest?” An example will highlight their benefits.

How Pivot Tables Work

To begin, imagine a restauranteur who must keep an inventory of fresh fruits. If she were to keep a record of her puchases, the first 10 sales may look like so:

sample flat file for a pivot table

You can imagine that this data has the potential to become unwieldy in a real world scenario (with more fruits, variable $/unit prices, higher quantities, etc…). This is a great opportunity to use a pivot table to perform some analysis and bring meaning to the information here. The one that follows uses “Fruits” as a “Row”, and “Price Paid” as its “Value” (More on this language a little later).

Sample pivot table generated from flat file

With just a few clicks this presentation lets the restaurateur know exactly what she spent on Apples, Bananas, and Oranges for the period in question. If the data were more complex it would lend to even more insight, however this example should give you the background you need to understand the role pivot tables play as a data visualization tool in the business intelligence world.

Creating Your Own

(The example we use relies on the following data set: Sample Project Time. Note that I’ve included formulas to randomize projects and hours charged. It’s a great demonstration of the flexibility of pivot tables, but it may be jarring for first-timers. If so, highlight the table and press “Ctrl +C”. Without un-highlighting press the “Delete” key on your keyboard, then right-click the upper-leftmost cell and select “Paste Special”. From that dialog select “Values”, then “OK”. The variables will be replaced by static numbers.)

If you read the first paragraphs of this post closely, you should expect to begin your pivot table development with all of your project time in a single spreadsheet likened to a “flat” (i.e., single table) database. Depending on your time collection system and BI reporting tool the first row of your data set may or may not include column headers. If it doesn’t, go ahead and add them.

Next we need to tell Excel what data we’ll be using. Use your keyboard or mouse to highlight the contents of your table including column headers. Then, in the ribbon (Excel 2007 and later) or program menu select Insert -> PivotTable. Excel will ask you where you’d like to put your report, and the default option is usually fine. You may, however, prefer to keep your table on the same sheet as its source. If so, select the “Existing Location” radio button, then click the grid icon. Target the cell where you want the upper-leftmost corner of your pivot table to be, then click the grid icon once more to return to the “Create PivotTable” dialog. Click “OK”.

Create PivotTable dialog box

Now the fun begins. First, you’ll want to think about what type of information you’re trying to extract from your project time. It may be hours by week by employee, overtime hours by employee, or hours by project by function. In any event you may generate your report on the fly as long as the data exists in your original download (i.e., your pivot table’s data source). Incidentally, this is one of my absolute favorite things about working with pivot tables: They encourage you to roll up your sleeves and work with the data, massaging it and manipulating it to tell a story that informs managerial action. This, not the technology itself, is the most challenging yet exciting part of learning to use pivot tables effectively.

Making the Information Work for You

Let’s imagine that you want to view project time by “Week Ending” date, regardless of the projects to which that time was charged. To accomplish this you would select “Projects” from the “PivotTable Fields” panel and drag it to the “Rows”, then “Week Ending” date and drag it to the “Columns” quadrant.

The resulting pivot table should look something like this.

Notice that even before inputting any time date in your report, you can glean some valuable insight. Specifically, on the left you can see an alpha-sorted list of all projects captured in your source table. In a table of 10 or 100 rows this may not be particularly useful, but the  value increases manyfold when you’re working with data of 1000′s or 10,000′s of rows. This analysis has has broad applications, but that’s a topic for another blog.

Next, drag your “Hours” field to the “Values” area to evaluate “Hours” at the intersection of “Projects” and “Week Ending” dates.

(IMPORTANT: Verify that the field reads “Sum of” followed by your field name (some installations of Excel default to “Count”, which is useful in other contexts but less so here). If it doesn’t you can right-click the field or select its dropdown depending on the version of Excel you’re running, then click “Value Field Settings…”, then “Sum” under the “Summarize Values By” tab. Select OK and have a look at your data.)

Your pivot table should look like so:

If you’re new to pivot tables, spend a little time arranging and rearranging data in this view. Get comfortable sorting, applying filters, and manipulating the “Values”. Once you have the basics down, try adding additional fields to your “Rows” and “Columns” to increase the granularity of your analysis. Consider the following (Remember that your time will not match mine because of the randomization feature included in the sample set):

The above view shows projects by resource by week ending date. You can recreate it by dragging the “Employee” field into the “Row” quadrant, placing it above the “Project” field. Notice that the “Grand Totals” column conveniently shows resource hours along with a breakout by project.

BONUS: If you followed along with the sample set, go to the ribbon/program menu and select Data -> Refresh All to observe the way that your pivot table updates with changes to its source table.

Closing Remarks

Now that you’re acquainted with pivot tables, you’re ready for some more advanced analysis. Do a little online reading, or stop back to read the next Excel Hacks post in which I’ll be covering Excel’s powerful GETPIVOTDATA() formula.

Til next time.

Top 7 Reasons to Bookmark This Blog

Hey.

I’m Brian Lash, and this is my personal weblog. I’m going to use this space as an outlet to share some valuable lessons and observations from life and work, and troll people I disagree with post counterpoints to other ideas and philosophies I find around the web.

I’m glad to have your attention, and I think you’re going to like what you find here. But before we get started on this harrowing journey, let’s level with each other: This blog is one of a hundred thousand quintillion others you could invest your precious time reading. Why should you give my humble blog a chance?

  1. We don’t have to impress each other. Let’s keep this (business) casual.
  2. I know your time is limited. I don’t believe in writing for writing’s sake, so when I post, I’ll post because I think you’re going to be interested in what I have to say. I won’t always get it right, but I’ll make the effort.
  3. You can expect twice- or thrice-weekly posts about information technology leadership, customer experience management (CXM), lessons learned from Carnegie Mellon’s Tepper School of Business, and everything in between.
  4. The name of the site speaks to my intentions; I’ve blogged, vlogged, and podcasted at random intervals, but I’ve spent most of the past year consuming. Knowledge Transferred will be a modest attempt at repaying an intellectual debt to the Interwebs.
  5. There is no number 5.
  6. In the spirit of CXM I recognize that I need you more than you need me.
  7. I’ve logged your IP address. (And yes, the bears are working for me.)

So bookmark this URL or subscribe to the RSS feed. There’s lots more to come.

Thanks for stopping.

Switch to our mobile site