Aug 022017
 

August 3, 2017

In my last blog post I pointed out that I wish I had put BANs (big-ass numbers) in the Churn dashboard featured in chapter 24 of the book (see http://www.datarevelations.com/iterate.html.)

I had a similar experience this week when I revisited the Net Promoter Score dashboard from Chapter 17.  I’ve been reading Don Norman’s book The Design of Everyday Things and have been thinking about how to apply many of its principles to dashboard design.

On thing you can do to help users decode your work is to ditch the legend and add a color key to your dashboard title.

Here’s the Net Promoter Score dashboard as we present it in the book.  Notice the color legend towards the bottom right corner.

Figure 1 -- Net Promoter Score dashboard from The Big Book of Dashboards.

Figure 1 — Net Promoter Score dashboard from The Big Book of Dashboards.

Why did I place the legend out of the natural “flow” of how people would look at the dashboard? Why not just make the color coding part of the dashboard title, as shown below?

Figure 2 -- Making the color legend part of the title. 

Figure 2 — Making the color legend part of the title.

I’m not losing sleep over this as this is probably a dashboard that people will be looking at on a regular basis; that is, once they know what “blue” means they won’t  need to look at the legend.

But…

Every user will have his / her “first time” with a dashboard, so I recommend that wherever possible make the legend part of the “flow.” For example, instead of the legend being an appendage, off to the side of the dashboard…

Figure 3 -- Color legend as an appendage.

Figure 3 — Color legend as an appendage.

Consider making the color legend part of the title, as shown here.

Figure 4 -- Color coding integrated into the title.

Figure 4 — Color coding integrated into the title.

 

Jan 112016
 

Overview

I spend a lot of time with survey data and much of this data revolves around gauging people’s sentiments and tendencies using either a Likert Scale or a Net Promoter Score (NPS) type of thing.

Examples

Here’s an example of gauging sentiment using a 5-point Likert scale.

Indicate how satisfied you are with the following:

00_Grid1

Here’s an example of measuring tendencies, using a 4-point Likert scale.

How often do you use the following learning modalities?

00_Grid2

So, what’s a good way to visualize responses to these types of questions?

Over the past ten years I’ve spent thousands of hours working on the best ways to show how opinion and tendencies skew one way or another.  I have found that in most cases a divergent stacked bar chart helps me (and more importantly, my clients) best see what’s going on with the survey responses.

In this blog posts we’ll

  • See an example of a divergent stacked bar chart (also called a staggered stacked bar chart)
  • Work through a data visualization improvement process
  • Show how to visualize different scales (e.g., NPS, Top 3/Bottom 3, 5-point Likert, etc.)
  • Show sentiment and tendencies over time
  • Present a dashboard that will allow you to experiment with different visualization approaches

Note: for step-by-step instructions on how to build a Likert-scale divergent stacked bar chart in Tableau, click here.

Divergent Stacked Bar vs. 100% Stacked Bar

Readers of my newsletter and folks visiting the web site may have seen my redesign of a New York Times infographic that showed the tendencies of politicians to lie or tell the truth.  Here’s the 100% Stacked Bar chart that appeared in the New York Times.

Figure 1 -- 100% stacked bar chart.

Figure 1 — 100% stacked bar chart.

Here’s the redesign using a divergent stacked bar chart.

Figure 2 -- Divergent stacked bar chart.

Figure 2 — Divergent stacked bar chart.

With both the 100% stacked bar chart and the divergent stacked bar charts the overall length of the bars is the same, but with the divergent approach the bars are shifted left or right to show which way a candidate leans. I, and others I’ve polled, find that shifting the bars makes the chart easier to understand.

How We Got Here — Likert Scale Improvement Process

Consider the table below that shows the results from a fictitious poll on the use of various learning modalities.

Figure 3 -- Table with survey results.

Figure 3 — Survey results in a table.

I can’t glean anything meaningful from this.

What about a bar chart?

Figure 4 -- Likert scale questions using a bar chart. Yikes.

Figure 4 — Likert scale questions using a bar chart. Yikes.

Wow, that’s really bad.

What about a 100% stacked bar chart?

Figure 5 -- 100% stacked bar chart using default colors.

Figure 5 — 100% stacked bar chart using default colors.

Okay, that’s better, but it’s still pretty bad as Tableau’s default colors do nothing to help us see tendencies that are adjacent. That is, “Often” and “Sometimes” should have similar colors, as should “Rarely” and “Never.”

So, let’s try using better colors…

(…and don’t even think about using red and green.)

Figure 6 -- 100% stacked bar chart using a more appropriate color scheme.

Figure 6 — 100% stacked bar chart using a more appropriate color scheme.

This is certainly an improvement, but the modalities are listed alphabetically and not by how often they’re used. Let’s see what happens when we sort the bars.

Figure 7 -- Sorted 100% stacked bar chart with good colors.

Figure 7 — Sorted 100% stacked bar chart with good colors.

It’s taken us several tries, but it’s now easier to see which modalities are more popular.

But we can do better.

Here’s the same data rendered as a divergent stacked bar chart.

Figure 8 -- Sorted divergent stacked bar chart with good colors.

Figure 8 — Sorted divergent stacked bar chart with good colors.

Of course, we can also look take a coarser view and just compare Sometimes/Often with Rarely/Never, as shown here.

Figure 9 – Divergent stacked bar chart with only two levels of sentiment.

Figure 9 – Divergent stacked bar chart with only two levels of sentiment.

I find that the divergent approach “speaks” to me and it resonates with my colleagues and clients.

Experiments using Different Scales

A while back Helen Lindsey was kind enough to send me some data that contained responses to some Net Promoter Score questions.  Specifically, folks were asked to rate companies/products on a 0 to 10 or 1 to 10 scale.

Figure 10 -- The classic Net Promoter Score (NPS) question

Figure 10 — The classic Net Promoter Score (NPS) question

We compute NPS by subtracting the percentage of folks that are promoters (i.e., people who responded with a 9 or a 10), subtracting the percentage of folks that are detractors (i.e., people who responded with a 0 through 6) and multiplying by 100.

But sometimes my clients have questions that are on a 10 or 11-point scale but instead want to compute the percentage of folks that responded with one of the top three boxes minus the percentage of folks that responded with the bottom three boxes.

I realized that the Lindsey data set could provide a type of “sandbox” where we could experiment with different sentiment scales including NPS, Top 3 minus Bottom 3, 5-point Likert, 3-point Likert, and 2-point Likert.

Let’s look at the results of some of these experiments.

NPS

Here are two ways we can visualize NPS data.  The first shows the percentages of people that fall into the three categories.

Figure 11 -- NPS showing percentages

Figure 11 — NPS showing percentages

Here’s the same view, but with the NPS score superimposed over the divergent stacked bars.

Figure 12 -- NPS with score superimposed

Figure 12 — NPS with score superimposed

NPS over Time

It turns out that divergent stacked bars are great at showing NPS trends over time.  Here’s a view using percentages.

Figure 13 -- Divergent stacked bar showing NPS over time with percentages

Figure 13 — Divergent stacked bar showing NPS over time with percentages

Here’s the same view but with the score superimposed.

Figure 14 -- Divergent stacked bar showing NPS over time with scores

Figure 14 — Divergent stacked bar showing NPS over time with scores

Note – for some other interesting treatments of showing sentiment over time, see Joe Mako’s visualization on banker honesty.

Net = Top 3 minus Bottom 3

Let’s take the same data but divide it into the following buckets:

  • Positive = Top 3 Boxes
  • Neutral = Middle 4 Boxes
  • Negative = Bottom 3 Boxes

Here are the associated visualizations.

Figure 15 -- Top 3 / Bottom 3 showing with percentages

Figure 15 — Top 3/Bottom 3 showing with percentages

Figure 16 -- Top 3 / Bottom 3 with scores

Figure 16 — Top 3/Bottom 3 with scores

Five, Three, and Two-Point Likert Scale Renderings

Let’s suppose that instead of asking a questions on a 1 through 10 scale we instead asked folks to select one of the following five responses:

  • Strongly disagree
  • Disagree
  • Neutral
  • Agree
  • Strongly agree

Here’s the same NPS data but rendered using a five-point Likert scale.

Figure 17 -- Divergent stacked bar chart showing all responses

Figure 17 — Divergent stacked bar chart showing all responses

And here’s the same data, but divided into positive, neutral, and negative sentiments (3-point Likert).

Figure 18 -- Divergent stacked bar showing positive, neutral, and negative

Figure 18 — Divergent stacked bar showing positive, neutral, and negative

Finally, here’s the same data, but only showing positive and negative sentiments (2-point Likert).

Figure 19 -- Divergent stacked bar showing just positive and negative

Figure 19 — Divergent stacked bar showing just positive and negative

Try it yourself

Below you will find a dashboard that allows you to explore different combinations of the 1 to 10 scale.

I strongly recommend you do NOT give your audience all these scaling options;  these are here for you to experiment and see how the visualizations and ranking change based on what scales you use.  The only option I would present to your audience is the ability to toggle back and forth between percentages and scores.

Jun 042015
 

Showing Differences between Periods and Statistical Significance in Tableau

Overview

Addressing this scenario has been the most popular request I’ve received over the past year. Here’s a summary what my clients and students have asked:

  • How do I show the change in Sales, Percentage of Promoters, Number of Visits, etc., between this month / quarter / year, and the previous month / quarter / year?
  • How do I make it easy to see which areas of the organization had an increase this period and which had a decrease?
  • How do I make it easy to see how much greater / less this period’s numbers are than the previous period?
  • How do I determine and show if this change is statistically significant? That is, how do I apply the stat test we like to use in our organization?
  • If the change is statistically significant, is it a one-time thing or should I start hyperventilating?

This is a LOT to take on and we won’t be able to fit all of it into a single visualization.

But we can fit it into a compact dashboard.

Important Ground Rules

In the example that follows I look at the percentage of people that responded with a “9” or “10” to a survey question. That is, I am only looking at the percentage of people that selected one of the top two boxes.  I am NOT trying to see if there is statistical significance or calculate the margin of error in the change in Net Promoter Score over time.

The concepts I explore are not just for survey data; I just happen to have some good longitudinal survey data that is well-suited for seeing how to build a stat test formula in Tableau.

I hope you will indulge me and accept that “the company stat guru” has a fine reason for applying a particular statistical test to the data we’ll be analyzing. That said, you should push back on “business-as-usual” assumptions to determine if what you are visualizing and testing really is important (this is the focus of the work Stacy Barr is doing with her Measure Up blog and is the foundation for Stephen Few’s most recent book Signal.)

So, with the assumption that the particular stat test we want to apply – or any stat test, for that matter – is warranted, how do you show it and how do you build it?

Let’s first explore the working dashboard then see how to build it with Tableau.

Note: A very heartfelt thanks to Kelly Martin,, Joe Mako, Vicki Reinhard, Susan Ferarri, and Tiffany Spaulding who helped vet the dashboard.  I went through many different approaches before settling on the one shown below.

A very special thanks to Jeffrey Shaffer who reviewed the blog post and asked some very good questions, and also to Helen Lindsay who provided sample data.

The data and what we want to show

The data below contains the first few rows of Net Promoter Score survey data with fields for date and role.

Figure 1 -- Net Promoter Score survey data with dates and roles

Figure 1 — Net Promoter Score survey data with dates and roles

For the dashboard I built I only focused on the percentage of people that were Promoters; that is, people who responded with a 9 or 10 when asked if they would recommend a product or service.

I decided to look at the data broken down by quarters as this particular data set didn’t lend itself to month over month comparison.  Note that the techniques we’ll see will work for any time period.

Here’s the top portion of the interactive dashboard.

1_SSDashboardTop

Figure 2 — Top portion of dashboard.  Notice that you can change the selected period, the confidence percentage, and filter by company.

Understanding the chart

Figure 3 -- The key features of the chart

Figure 3 — The key features of the chart

Let’s review what we can glean from the chart.  We can see

  • The percentage of promoters for a particular period and sort them by role, using a bar chart.
  • Which roles have a percentage of promoters that is greater than the previous period and which have less, using color to distinguish (blue for greater, brown for less).
  • Just how much more or less the percentage for this period is compared to the previous using a reference line (the bar is the current period; the vertical line is the previous period).
  • Which roles showed a significantly significant increase or decrease (the red dot).

Note that that the chart uses “Cotgreavian” tooltips that allow you to glean more detail for a particular role when you hover over a bar:

Figure 4 -- Hover over a bar for in-depth information about the role for the current period and the previous period

Figure 4 — Hover over a bar for in-depth information about the role for the current period and the previous period

So, we can see from the red dot that something is up with Lawyers, Doctors and Nurses; that is, the percent increase from the previous period for Doctors and Lawyers is statistically significant and the percent decrease for Nurses is also significantly significant.  Is this a one-time thing or a trend?

Looking at changes over time

Clicking a role or roles will display trends for that role / roles.  For example, if we select Nurse in the top chart a second chart showing percentage of promoters over time will appear, as shown here.

Figure 5 -- Percentage of nurses that are promoters, over time.

Figure 5 — Percentage of nurses that are promoters, over time.

The big takeaway for me is that up until the first quarter of 2013 there were very few responses and after that there was both a consistent number of responses along with a consist decline in the percentage of nurses that were promoters.

Should you be hyperventilating because of the four-month downward trend?  That discussion is beyond this blog post but I again encourage you to check out the work Stacy Barr is doing at her Measure Up blog as well as Stephen Few’s most recent book Signal.

How the This Quarter vs. That Quarter Chart is Built

Let’s dig into how to build this in Tableau, starting with the top viz in the dashboard.

Figure 6 -- What's under the hood.

Figure 6 — What’s under the hood.

  1. Promoters – Current Quarter. This is the measure that drives the bars.  It’s also driving what appears on the labels.
  2. Promoters – Previous Quarter. This measure is on the Level of Detail and drives the reference lines.
  3. Greater / Less. This is a discrete measure that determines the color of the bar.

Promoters – Current Quarter

What we want is the percentage of people that were promoters for the selected quarter, the “selected” quarter being determined by a parameter that the user can control.

Specially, we want to add up everybody that responded with a 9 or 10 for the selected quarter and divide by the total number of people that responded.  Here’s the calculation that handles this.

SUM(

IF [Value]>=9 and DATETRUNC(‘quarter’, [Select Period])==DATETRUNC(‘quarter’,[Date])
then 1 else 0
END)

/

SUM(

IF DATETRUNC(‘quarter’, [Select Period])==DATETRUNC(‘quarter’,[Date])
then 1 else 0
END)

The translation into English is

Take the sum of

If the value from a respondent is greater than or equal to 9 and the date value, truncated to the nearest quarter from the parameter drop down [Select Period] is the same as the date value, truncated to the nearest quarter for [Date], then 1, else 0.

Divide this by the sum of

If the date value, truncated to the nearest quarter for the selected period is the same as the date value, truncated for the nearest quarter for [Date], then 1, else 0.

Not sure about the [DATETRUNC] function vs. the [DATEPART] function?  Have a look at Joshua Milligan’s excellent post explaining date values vs. date parts.

Promoters – Previous Quarter

This calculation is very similar to the calculation for the Current Quarter, except we want to find results for the quarter that occurred just prior to the selected quarter.  Here’s the calculation.

SUM(

IF [Value]>=9 and DATETRUNC(‘quarter’, [Select Period])=DATETRUNC(‘quarter’,DATEADD(‘quarter’,1,[Date]))
then 1 else 0
END)

/

SUM(

IF DATETRUNC(‘quarter’, [Select Period])==DATETRUNC(‘quarter’,DATEADD(‘quarter’,1,[Date]))
then 1 else 0
END)

The formula is the same except we use the DATEADD function to add an additional quarter; that is, we’re saying that we only want to find results where, when we add an additional quarter, we get a value equal to the current quarter; i.e., the previous quarter, plus one quarter, gives us the current quarter.

Greater / Less

The color of the bars is determined by this discrete measure:

IF [Promoters — Current Quarter] > [Promoters — Previous Quarter] then “Greater than previous”
else “Less than previous”
END

Yes, I suppose we should have a contingency for when the percentage of promoters for the current period is the same as the previous period; I leave it as an exercise for the reader to add this functionality.

So, we’ve explained everything except … The Red Dot.

The Red Dot – Computing Statistical Significance on the Fly

Most of my clients and students are surprised to find out that you can fashion a test for statistical significance inside Tableau and it can test for statistical significance “on the fly”; e.g., you can apply filters and Tableau will recalculate based on the filter settings.

The first step is determining just how the client wants to test for statistical significance. This usually entails sending an inquiry to “the stats person” who responds with something that looks like this:

Figure 7 -- Z-test formula for statistical significance

Figure 7 — Z-test formula for statistical significance

I hope your eyes aren’t glassing over as this really isn’t very complicated; it just might look complicated if you’re not used to seeing stat formulas with square root symbols.  Here are the critical things you need to know:

p1            Percentage of promoters for the current period

p2            Percentage of promoters for the previous period

n1            Number of respondents for the current period

n2            Number of respondents for the previous period

If z1 is greater than or equal to 1.96 then there is a 95% degree of confidence that the difference between the two periods is statistically significant.

So, how do we build this formula?

Slowly, and in easy-to-digest pieces.

The Dot Itself

Figure 8 -- The discrete measure Z-Test Significance Dot is responsible for displaying the dot

Figure 8 — The discrete measure Z-Test Significance Dot is responsible for displaying the dot

The calculation that produces the dot is called Z-Test Significance Dot and it is defined as follows.

IF ABS([Promoters — Z-Score Quarter])>=[Confidence] THEN “•”
ELSE “”
END

This translates as

If the absolute value of [Promoters – Z-Score Quarter] is greater than or equal to the confidence parameter (currently set to 1.96, or 95%) then display a dot; otherwise, display a null string.

And just how is [Promoters – Z-Score Quarter] defined?  Let’s explore the next layer of the onion.

Promoters – Z-Score Quarter

This is defined as follows:

[Promoters — Z-Score Quarter Numerator] /

SQRT(

([Promoters — Z-Score Quarter Denom – Current] +
[Promoters — Z-Score Quarter Denom – Previous])
)

Here’s how it maps to the stat formula we saw earlier:

Figure 9 -- Mapping the components of the formula to different calculated field

Figure 9 — Mapping the components of the formula to different calculated field

So now we just need to understand the three different pieces that go into the stat function.

Promoters – Z-Score Quarter Numerator

This is very simple and refers to calculations we’ve already used.

[Promoters — Current Quarter] –
[Promoters — Previous Quarter]

Promoters — Z-Score Quarter Denom – Current

This is fairly straightforward given what we’ve already explored.

([Promoters — Current Quarter]*(1-[Promoters — Current Quarter]))
/SUM([Promoters — Current Quarter Count])

Where [Promoters – Current Quarter Count] is defined as follows.

IF DATETRUNC(‘quarter’, [Select Period])==DATETRUNC(‘quarter’,[Date])
THEN 1 END

So SUM(Promoters — Current Quarter Count]) is just adding up all the people that responded during the selected quarter.

Promoters — Z-Score Quarter Denom – Previous

([Promoters — Previous Quarter]*(1-[Promoters — Previous Quarter]))/
SUM([Promoters — Previous Quarter Count])

This uses the same logic as [Promoters – Z-Score Quarter Denom – Current] but instead aggregates results from the previous quarter.

Putting it all together

In addition to building the components in a piecemeal fashion I will often build a crosstab of all these components to see if they are working as I would expect.  Consider the crosstab shown here.

Figure 10 -- Crosstab showing all the pieces that contribute to the red dot

Figure 10 — Crosstab showing all the pieces that contribute to the red dot

The cross tab allows us to examine all the intermediate calculations to see how the contribute to the determining calculation in the last column.

What about the secondary chart?

So we’ve now seen how to build the top chart that shows current and previous quarters broken down by role.  How does the secondary chart – the chart that appears when you click a role or roles in the first chart – work?

Figure 11 -- Percentage of promoters for Nurses over time

Figure 11 — Percentage of promoters for Nurses over time

Here we have a dual axis chart so that we can have both a line (gray) and a circle (colored based on whether the change for the previous period is statistically significant).

In this case we have to construct all of the pieces using a table calculation, but the process of putting together the different components is identical to what we saw earlier.  For example, the calculation that determined the color of the circle, [LONG_Z-Test Significance], is defined as follows.

IF ABS([LONG_Z-Score])>=[Confidence] then “Significant”
else “Not significant”
end

And [LONG_Z-Score] is defined this way:

[LONG_Z-ScoreNumerator] /

SQRT(

([LONG_Z-Score Denom Current] +
[LONG_Z-Score Denom Previous])

)

I also built a crosstab to see how all the pieces fit together, as shown below.

Figure 12 -- Crosstab to help put together a z-test calculation for values shown over time

Figure 12 — Crosstab to help put together a z-test calculation for values shown over time

Conclusion

The dashboard in this blog post shows the percentage of promoters, sorted by role, for a particular quarter, compared with the percentage of promoters for the previous quarter.  Roles where the percentage difference is statistically significant are marked with a red dot. You can drill down on a particular role (or role) and see how scores have changed over time.

While the critical visual component was showing bars and reference lines, most of the “heavy lifting” went into determining if a change was statistically significant.  The key here was to not be intimidated by a statistical formula and to build the calculations in small pieces, using crosstabs to check the work.

 

May 112015
 

Much thanks to Susan Ferrari for exposing me to the concept of Net Promoter Score, Susan Baier for encouraging me to blog about it, and Helen Lindsey for providing anonymized NPS data.

Overview

My wife and I recently went out to a restaurant to celebrate our anniversary.  Accompanying the check was a survey card with three questions, one of which looked like this.

Figure 1 -- The classic Net Promoter Score question

Figure 1 — The classic Net Promoter Score question

We both agreed that the restaurant was very good, if not excellent, and that we would indeed recommend it to friends.  My wife suggested we circle the “8”.

I told her that if we were enthusiastic about recommending the restaurant we should give it a “9” as a 7 or 8 would be tabulated as a “neutral” or “passive” response.

She looked at me quizzically and asked why an “8” would be considered neutral.

I then explained how the Net Promoter Score works.

Understanding the Score

Respondents are presented with the question “Using a scale from 0 to 10, would you recommend this product / service to a friend or colleague?”

  • Anyone that responds with a 0 through 6 is considered a Detractor.
  • Anyone that responds with a 7 or 8 is considered a Passive (or Neutral).
  • Anyone that responds with a 9 or 10 is considered a Promoter.

The Net Promoter Score (NPS) is computed by taking the percentage of people that are Promoters, subtracting the percentage of people that at Detractors, and multiplying that number by 100.

How to compute NPS, courtesy B2B International.

Figure 2 — How to compute NPS, courtesy B2B International.

If you are like me (and my wife) you’re probably thinking that a “6” is a pretty good score and that it shouldn’t be bunched among the detractors.

I’m not going to get into a debate about NPS methodology and its usefulness, but I do want to show you some good ways to visualize NPS data.

The Problem with the Traditional Presentation

Consider this snippet of NPS survey data with responses about different companies from people in different roles.

Figure 3 -- Raw NPS data about different companies from people with different occupations.

Figure 3 — Raw NPS data about different companies from people with different occupations.

If we just focus on the NPS and not the components that comprise the NPS we can produce an easy-to-sort bar chart like the one shown here.

Figure 4 -- Traditional way to show NPS

Figure 4 — Traditional way to show NPS

Yes, it’s easy to see the company D has a much higher NPS than company H, but by not showing the individual components – and in particular the Neutrals / Passives –  we’re missing an important part of the story as the Neutrals / Passives are right on the cusp of becoming promoters.

For example, a Net Promoter Score of 40 can come from

  • 70% Promoters and 30% Detractors
  • 45% Promoters, 50% Passives, 5% Detractors

Same score, big difference in makeup.

An Alternative Approach to Displaying NPS Results

Consider the dashboard below which presents the data as a divergent stacked bar chart.

Figure 5 -- NPS dashboard with toggle to show percentages and score.

Figure 5 — NPS dashboard with toggle to show percentages and score.

The chart is easy to sort and you can also see that Company B and Company F have a relatively large group of Neutrals.

That said, being able to see the NPS score is very useful so the dashboard (see working version at the end of this post) has a toggle that switches between percentages and the score, as shown below.

Figure 6 -- Divergent stacked bar chart with NPS overlay.

Figure 6 — Divergent stacked bar chart with NPS overlay.

Note that the NPS divergent stacked bar chart is just a variation on a Likert scale divergent stacked bar chart.  You can find an explanation of how to build this type of visualization here.

What’s Next?

We now have what I think is a more insightful way to visualize Net Promoter Score data.

But clients and readers of my blog have asked me to address some of these questions as well:

  • How do you show the difference in NPS, or just the difference in percentage of promoters, between this quarter and the previous quarter?
  • If there is a difference, is the difference statistically significant?
  • What’s a good way to visualize and analyze NPS over time?

I will be addressing these issues in an upcoming post.  Stay tuned.