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.

Advertisements

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

Every Data Analyst Needs to check out this FREE excel add-in: Power Query!

Standard

Power Query is amazing! It takes the data analysis capabilities of Excel to whole new level! In this post, I am going to share three reasons:

1. it enables repeatable mash-up of data!

Have you every had to do your data analysis tasks repeatedly on the data with same structure? Do you get “new” data every other week and need to go through the same data transformation workflow to get to the data that you need?

What’s the solution? Well, you can look at MACRO’s! Or you can request your IT department to create a Business Intelligence platform. However, what if you need to modify your data mashup workflow then these solutions don’t look great, do they now?

Don’t worry! Power Query is here!

It enables repeatable mashup of data like you might have never seen before! You need to try it to believe.

It’s very easy to input new data to Power Query and it enables you to retrieve final output based on new data using a “refresh” feature.

Each data-mashup is recorded as steps which you can go back and edit if you need to.

Power Query Refresh

2. It’s super-flexible!

Any data mashup performed using Power Query is expressed using its formula language called “M”. You can edit the code if you need to and as you can imagine such a platform enables much-needed flexibility for the analyst’s.

3. It has awesome advance features!

Do you want to Merge data? How about Join? Are you tired with VLOOKUP’s! Don’t worry! it’s super easy with Power Query! Here’s a post: Join Excel Tables in Power Query

How about Pivot or Unpivot? Done! Check this out: Unpivot excel data using Power Query

How about searching for online & open data sets? Done!

How about connecting to data sources that “Data” section of Excel doesn’t support yet? (Example: Facebook) – DONE! Power Query makes that happen for you.

And That’s not a complete list!

Plus you can unlock the “Power” (pun intended) of Power Query by using it with other tools in Power BI Stack. (Power Pivot, Power View, etc…) OR you can use the your final output from Power Query with other tools too! After all it’s an excel file.

Action-Item!

If you haven’t already then check out Power Query! it’s free and works with Excel 2010 and above.

Author: Paras Doshi

Top two key techniques to analyze data:

Standard

There are many techniques to analyze data. In this post, we’re going to talk about two techniques that are critical for good data analysis! They are called “Benchmarking” and “Segmentation” techniques – Let’s talk a bit more about them:

1. Benchmarking

It means that when you analyze your numbers, you compare it against some point of reference. This would help you quickly add context to your analysis and help you assess if the number if good or bad. This is super important! it adds meaning to you data!

Let’s look at an example. CEO wants to see Revenue numbers for 2014 and an analyst is tasked to create this report. If you were the analyst, which report would you think resonated more w/ the CEO? Left or Right?

Benchmarking data providing context in analysis

I hope the above example helped you understand the importance of providing context w/ your data.

Now, let’s briefly talk about where do you get the data for benchmark?

There are two main sources: 1) Internal & 2) External

The example that you saw above was using an Internal source as a benchmark.

An example of an external benchmark could be subscribing to Industry news/data so that you understand how your business is running compared to similar other businesses. If your business sees a huge spike in sales, you need to know if it’s just your business or if it’s an Industry wide phenomenon. For instance, in Q4 most e-commerce sites would see spike in their sales – they would be able to understand what’s driving it only if they analyze by looking at Industry data and realizing that it’s shopping season!

Now, let’s shift gears and talk about technique #2: Segmentation.

2. Segmentation

Segmentation means that you break your data into categories (a.k.a segments) for analysis. So why do want to do that? Looking at the data at aggregated level is certainly helpful and helps you figure out the direction for your analysis. The real magic & powerful insights are usually derived by analyzing the segments (or sub sets of data)

Let’s a look at an example.

Let’s say CEO of a company looks at profitability numbers. He sees $6.5M and it’s $1M greater than last years – so that’s great news, right? But does that mean everything is fine and there’s no scope of optimization? Well – that could only be found out if you segment your data. So he asks his analyst to look at the data for him. So analyst goes back and after some experimentation & interviews w/ business leaders, he find an interesting insight by segmenting data by customers & sales channel! He finds that even though the company is profitable – there is a huge opportunity to optimize profitability for customer segment #1 across all sales channel (especially channel #1 where there’s a $2M+ loss!) Here’s a visual:

segmentation data Improve profitability low margin service offerings customers

I hope that helps to show that segmentation is a very important technique in data analysis!

Conclusion:

In this post, we saw segmentation & benchmark techniques that you can apply in your daily data analysis tasks!

Back to basics: continuous Vs. Discrete variables and their importance in Data Visualization.

Standard

Take a look at the following chart, do you see any issues with it?

month trend chart line chart string to date

Notice that the month values are shown as “distinct” values instead of shown as a “continuous” values and it misleads the person looking at the chart.  Agree? Great! You already know based on your instincts what continuous and discrete values are, it’s just that we will need to label what you already know.

In the example used above, the “Date & Time” shown as a “Sales Date” is a continuous value since you can’t never say the “Exact” time that the event occurred…1/1/2008 22 hours, 15 minutes, 7 seconds, 5 milliseconds…and it goes on…it’s continuous.

But let’s say you wanted to see Number of Units Sold Vs Product Name. now that’s countable, isn’t it? You can say that we sold 150 units of Product X and 250 units of product Y. In this case, Units sold becomes discrete value.

The chart shown above was treating Sales Date as discrete values and hence causing confusion…let’s fix it since now you the difference between continuous and discrete variables:

Statistics Discrete Continuos Variable Data Visualization

Conclusion:

To develop effective data visualizations, it’s important to understand the data types of your data. In this post, you saw the difference between continuous and discrete variables and their importance in data visualization.

SQL Server Analysis services – How to set the order by attribute sort key?

Standard

Problem:

How to sort the dimension attribute by something other than the key and name column? How do you set the “OrderBy” property?

Example: You have created an Inventory age buckets 1-50,51-100,101-150 and so if a business user uses this dimension attribute then the sorting won’t be logical. It would be 1-50, 101-150,51-100 – so how to show the buckets in the logical order?

Solution:

1. make sure that the table/view that you are bringing in has the sort key.

Example:

1 SSAS Attribute order by sort key2. Now, switch to SSAS and open your dimension. I am assuming that you’ve already configured your data source views and you are already bringing in these columns in the dimension:

Dim Inventory SSAS SSIS VIEW Data source VIEW

3. Let’s start with hiding Aging Bucket Sort key so that it’s not visible to user. Change the AttributeHierarchyVisible to False

4. Now, switch to Attribute Relationships – Right Click on Aging Bucket and click on New Attribute Relationship. And set the attribute relanship between Aging bucket and Aging Bucket Sort Key

Attribute Relationships SSAS

And you should see something like this in your attribute relationship section:

SSAS Attribute Relationship Sort Key

5. Now, one more thing to configure. Go back to dimension structure section. Open the properties section for the Aging Bucket Attribute and change the OrderBy property to AttributeKey. Also, change the orderByAttribute property to Aging Bucket Sort Key (in your case, choose the sort key that you have)

SSAS Order Sort by attribute property

That’s it, after you process the model then you should see the attribute being sorted based on the sort key that you had.

Conclusion:

In this post, you saw how to configure sort/order property of a dimension attribute.

How to train your users to create their own Business Intelligence reports? #3 of 5: User Experience, Trainer, Content

Standard

In part #1, I wrote about why is it important to enable business users to create their own BI reports.

In part #2, I wrote about three pre-training preparations – 1. Data 2. Tool 3. Understanding Culture.

In this post, part #3, I am going to write about three more important topics before you schedule trainings. Here they are:

1. user experience

2. Training Content

3. Trainer

Let’s talk about them in more detail:

1. User Experience:

How many clicks does it take for a user to get to the data? Measure this! From desktop, It’s Ideal to have 3 clicks or less to get to the data. If you more steps that users need to follow to get to the data, the chances of them getting lost somewhere else increases. If you have a great user experience, it’s easier for users to not have to remember how to get to the system. Here’s one example of an ideal User Experience:

Click #1: Click on web browser & type the name of the BI site (or pull it from favorites)

Click #2: On a BI site, they will have a “team site” (and that would show up automatically based on windows authentication) and they will see a reports categorized by subject areas. They will click on their subject area.

click #3: Click on a template and it will download the excel based template to user’s computer. (The template needs to be pre configured to connect to the data source)

In summary, easy-to-navigate BI sites are a huge plus!

On point #2, I had mentioned a BI site. You need some place for users to collaborate with their team & share reports. If you can’t setup SharePoint BI sites, then consider some shared network folders or have it on a common web site, some place that users can use to collaborate.

On point #3, I had mentioned “templates”. They may be excel files or blank power view reports configured to connect to the data source. Don’t ask your users to enter data source credentials – who would remember hxajfafhjfdakj\instance2143452 anyways?! Have templates that are ready to consume for end-users.

2. Trainer

Who needs to train user? of course, the trainer to have decent public speaking and communication skills along with being an expert at the end-user tool. He/she will also have to understand the business value of the data that the users are being trained on.

Now depending on the demand for training,  a trainer could be hired full-time/part-time to train users.

If there’s not enough budget or training demand, IT managers can consider requesting Business Intelligence Developers/Consultants/Architects or IT analysts to train the users.

If possible, IT managers can also request an analyst from the business group to do the training.

It would be great to record the trainings in video/document format for users to review them later.

3. Training Content & Format

3a Content

There are various methods to design training content:

– Look at Frequently asked questions from user community & design training content around them

– Invite smaller group of users for “beta” testing your training content. see if they like it! And keep improving your training content iteratively as you have more training sessions.

– Look at resources available online or books, user groups, etc for best practices & samples

– build upon the work of your colleagues, your past work, ask for feedback!

– And most importantly, remember to communicate business value in your training content.

– consider including Hands on (practice sessions) content in your training.

3b. Format

There are various training format & depending on your needs you will have to decide on the format of delivery mechanisms and training schedules:

Delivering mechanisms: In person or virtual.

Time: One hour-long/2 hour-long/one-hour for three days/ etc

I have had virtual trainings with users from Asia at 9 PM Easter Time & I’ve had 6 AM Eastern time meetings for users from Europe. You’ll need to decide the format that works best for you.

Conclusion:

In this post, I wrote about three topics for training business intelligence users 1. user experience 2. Trainer 3. Training Content.

How to train your users to create their own Business Intelligence reports. #2 of 5: Pre Training Prep

Standard

In part 1, I wrote about why is it important to enable business users to create their own BI reports. In this post, part 2, I am going to share the pre-training preparations before you start training. I’ve classified into three categories: 1) Data 2) Tools 3) Culture. Let’s go through them:

1. Data

Data assets needs to be published before you start training. It should be a business friendly analytic layer on top of your data sources. It could something as simple as a Power Pivot Model to a SQL server analysis cube. As long as you have an analytics layer – you’re good! Do NOT grant access to transactional systems. I’ve seen a business analyst who was considered the go-to-business-expert of a system having issues trying to create reports using the system’s relational data source – He had challenge trying to get his head around multiple tables, keys, unfriendly field names. He got something up & running but it was hard for him! What’s the lesson here? Try to make it as easy as possible for business users to use data – create an analytic layer over your data sources.

Apart from this, Data Integrity is very important! If the users don’t trust data, they are not going to use it. Invite selected set of business users to test the integrity of the data before you publish the data assets.

Also, the analytic layer that you developed should perform well. if it takes a minute to return fairly simple result, then you will have challenge driving adoption.

2. Tools

tools Business Intelligence reporting dashboarding

What tools would you use to teach business users reporting? Of course, Excel is a top choice since many of the users are already familiar with using excel. Also, Show them a Power View using YOUR data – that may get them excited enough to learn Power View.

How about SSRS report builder & performance point dashboard designer? This is mainly targeted for IT developers so it won’t be great idea to train business users using this tools.

What about Power Pivot/ Power Query? from #1, business users face challenge trying to analyze relational data sources, so test your audience to see Power Pivot/ Power Query is a fit or not. It might work well if they want to combine from couple of spreadsheets with IT’s data assets, that would work! But don’t expect business users to spend time trying to learn Power Pivot & Power Query to analyze data. Again, test it with your audience, see if they pick it up, some of your users may pick it up, great! But usually, you’ll have to create data assets (cubes/power pivot models/tabular models) to reach the masses!

3. Culture

Data Driven Culture Business Intelligence

Image Source: Economist & Tableau

The more time that you spend understanding the culture, the more successful you are going to get in training users. It’s because the things you’ll learn while trying to understanding an organization’s culture will be useful in content creation, delivery mechanisms, target audience selection & communicating business value of data driven decisions.

let’s step back. what is culture? It is a characteristics of a group of people. What characteristics are you trying to find before you start training? Try answering following questions:

a. Where are the “analysts”?

– Are they part of IT teams? Who requests reports from “IT analysts”. (they are your target audience!)

– Are they part of business units? (Great! Make them efficient by removing manual data copy-pasting from their to-do list)

– Do they report to CxO’s/presidents/VP’s? (Great! request examples of data driven decisions)

b. Are there examples of value generated using data driven decisions?

– understanding how business uses data to generate value is very important. you will create content using these examples!

c. How comfortable are users learning new technology?

– Have they shown resistance in learning new systems?

– Are they used to receiving ready-to-consume reports! (don’t expect them to change their behavior. But figure out the person creating reports for them. Train them! Make them better)

In summary, understand the culture of the organization, it would help you prepare before you start the training.

Conclusion:

In this post, we saw three pre training preparations (1. Data 2. Tool Selection 3. Culture) before you start training users.

Back to basics: What is the difference between Data Analysis and Data Mining?

Standard

What is the difference between Data Analaysis and Data Mining:

1) One view is that: Data Mining is one particular form of Data Analysis.

difference between data mining and data analysis

One of the reason I researched about the difference between Data Analysis and Data Mining because I find that the terms are used Interchangeably and now I know why. It’s because Data Mining is considered as a particular form of Data Analysis.

2) I found other view that says:

Data Analysis is meant to support decision-making, support conclusions & Highlight note-worthy information. So when “Analyzing data” – we know what we want; we want answers to support our hypothesis; we want data in summarized form to highlight useful information.

While

Data Mining is meant for “Knowledge discovery” and “predictions”. So when “Mining data” – we look for undefined insights; We want the data to tell us something we didn’t knew before; We want to find patterns in the data that we had not anticipated.

Sources:

http://www-stat.stanford.edu/~jhf/ftp/dm-stat.pdf

http://stats.stackexchange.com/questions/5026/what-is-the-difference-between-data-mining-statistics-machine-learning-and-ai

http://stats.stackexchange.com/questions/1521/data-mining-and-statistical-analysis

http://en.wikipedia.org/wiki/Data_analysis