Month: March 2015

How to assign same axis values to a group of spark-lines in Excel?

Standard

Spark-line is a very handy data visualization technique! It’s great when you are space constrained to show trends among multiple data points.

Here’s an example:

Spark Line Trend Excel Data Visualization

But there’s an issue with above chart! Axis values for these group of spark-lines do not seem match – it could throw someone off if they didn’t pay close attention. So a good practice – when you know users are going to compare segments based on the spark-lines – is to assign them same axis values so it’s easier to compare. Here’s the modified version:

Excel Sparkline data visualization same axis

And…here are the steps:

1. Make sure that spark-lines are grouped.

Select the spark-lines > go to toolbar > Sparkline Tools > Design > Group

Excel Sparkline Group

2. On the “group” section, you’ll also find the “Axis” option – select that and make sure that “same for all axis” is selected for Vertical axis minimum and maximum values:

Excel Spark Line Data Viz same min max value

 

That’s about it. Just a quick formatting option that makes your spark-lines much more effective!

Author: Paras Doshi

Business Metric #6 of N: Net Promoter Score (NPS)

Standard

In this post, you’ll see the definition, benefits and basic calculation tutorial for using Net promoter score (NPS)

What is it? 

Net Promoter Score is a nice indicator to measure customer loyalty and satisfaction. The way you do that is by measuring how users likelihood to recommend your products/services. You can do this by asking a simple question: In a scale of 0-10, How likely are you to recommend to a friend?

Here’s how you calculate it: 

1) After you get responses, you need to classify the range in three categories “Promoters”, “passive”, “Detractors”. It could something like:

0-5 -> Detractors

5-8-> Passive

9-10 -> Promoters.

2) Now, here’s the formula

(Total promoters – Total detractors)/(Total survey users)

How to interpret it? 

So, what’s a good NPS?

Let’s take an example.

1) Promoters = 100, passive = 100, detractors = 100 THEN NPS = 0

2) Promoters = 50, passive = 100, detractors =  10 THEN NPS = 0.25 (or 25%)

3) Promoters = 10, passive = 100, detractors = 50 THEN NPS = -25%

As a basic rule of thumb, higher the number then better it is for you! You don’t want this to be negative because as you can see from example below it indicates that you have more detractors then promoters.

Other than general rule of thumb, you might want to keep an eye on the trend of NPS on a monthly/quarterly basis to make sure it’s moving in right direction. You might also want to benchmark this against your Industry standard – because NPS tends to be different for different industries.

Conclusion:

In this post, you learned about Net Promoter score and how to use it to measure customer loyalty and satisfaction.

Cohort Analysis: What is it and why use it?

Standard

In this post, you’ll learn definition and benefits of Cohort Analysis. Let’s get started!

Cohort Analysis: What is it?

Cohort analysis is a data analysis technique used to compare similar groups over time.

Cohort Analysis: Why use it?

Here’s the basic idea: Businesses are dynamic and thus are continuously evolving. A customer who joined previous year might get a different experience compared to customer who joined this year. This is especially true if it’s a startup or tech company where the business models change (or Pivot!) often. You might miss crucial insights if you ignore the dynamic nature of businesses in your data analysis. To see if the business models are evolving in right direction, you need to to use cohort analysis to analyze similar groups over time – Let’s see an example to make it a little bit more clear for you.

You decide to analyze “Average Revenue per Customer” by Fiscal Year and came up with following report:

Simple Data Analysis Averages Hide Interesting Trends

It seems that your “Average revenue per customer” is dropping and you worry that your investors might freak out and you won’t secure new investments. That’s sad! But hold on – Let’s put some cohort analysis technique to use and look at the same data-set from a different angle.

In this case, you decide to create cohorts of customers based on their joining year and then plot “Average Revenue Per Customer” by their year from joining date. Same data-set but it might give you different view. See here:

Cohort Analysis Customer Revenue and Year Joined

It seems you’re doing a good job! your latest cohort is performing better than previous cohorts since it has a higher average revenue per customer. This is a great sign – and you don’t need to worry about your investors pulling out either and well, start preparations to attract new investors – all because of cohort analysis! 🙂 WIN-WIN!

Conclusion:

As you saw, cohort analysis is a very powerful technique which can help you uncover trends that you wouldn’t otherwise find by traditional data analysis techniques.

You might also like: Top 2 techniques to analyze data

Author: Paras Doshi