Business Intelligence system – Customer Complaints – B2B company:

Business Intelligence system – Customer Complaints – B2B company:

Analyzing customer complaints in crucial for customer service & sales teams. It helps them increase customer loyalty and fix quality issues. To that end, here’s a mockup:

Note: Drill down reports are not shown, details are hidden to maintain confidentiality and numbers are made up.

Customer complaint dashboard quality feedback

How to get descriptive statistics in Excel?

Problem:

you are analyzing a dataset and before modeling/analyzing you need to generate descriptive statistics on a field. you have the data loaded in Excel and wondered if there’s a way to do that in Excel.

Solution:

There’s an out of the box solution that will support your needs to generate descriptive statistics on a field. Here are the steps:

Note: for the purpose of this blog post, I am using Excel 2013 but data analysis toolpak is available in Excel 2007+.

1. Active “Data Analysis” toolpak.

Follow this steps:  File > Options > Add-ins > Manage: Excel Addins > “GO”

excel data analysis toolpak

2. make sure to check the “analysis toolpak” checkbox.

3. Now you should see a “data analysis” option under the “Data” pane:

Excel Data Analysis Descriptive Statistics

4. Now click on “Data Analysis” and select one of the following options:

Anova, Correlation, Covariance, Descriptive Statistics, Exponential Smoothing, F-Test Two-Sample for Variances, Fourier Analysis, Histogram, Moving Average, Random Number Generation, Rank and Percentile, Regression, Sampling, t-Test, z-Test.

in this case, let’s go with descriptive statistics but you can see that you can perform other tasks as well.

5. Once you click on the descriptive statistics, a dialog box will show up and you will have to enter some data like your input range to generate descriptive statistics. Once you have filled the data needed, click on OK and it should generate descriptive statistics for you in EXCEL!

I hope that helps!

Conclusion:

In this post, we saw how to generate descriptive statistics in Microsoft Excel.

Author: Paras Doshi

Cost Driver’s Dashboard for a Supply Chain Executive:

Cost Driver’s Dashboard for a Supply Chain Executive:

Summary:

Profitability equals revenue minus costs – To that end, A supply chain executive is mostly focused on optimizing cost elements to drive profitability. Here’s a mock up of a dashboard created for an executive to help him keep an eye on the overall health while making sure he gets alerted for key cost categories.

The Dashboard was created using profitability data-set & also had drill down capabilities to analyze numbers for cost buckets like Raw materials, manufacturing & logistics.

Mockup:

Supply Chain Cost Drivers Profitability Dashboard

Business Metric #5 of N: “Conversion rate” (online marketing)

Summary:

In this post, we will see an important metric in online marketing called “conversion rate”.

Description:

so, what is conversion rate?

Conversion rate = (Number of Goals Achieved)/(Total Visitors)

why is this important to track?

In my previous blog on leads (marketing), I mentioned it’s important to track number of people interested in your products/services but along with that it’s important to provide context while reporting on Leads – this is where conversion rate comes into picture and provides the necessary context. Conversion rate can tell us the Quality of the leads & visitors that you get from your online marketing efforts.

Let’s take an example of an e-commerce site:

An e-commerce site decides to increase their monthly online marketing budget and they see a spike in the number of visitors – so that’s great, right? They should continue to increase their marketing budget, right? Well – that might not be true. While the number of visitors may have increased how do we know that increased number of visitors results in increased revenue? It all depends on the quality of the visitors that’s being generated – so how do you quantify the quality of the visitors? That’s right – conversion rate with the goal: number of visitors clicking “buy” button. So you want to make sure that with increased online marketing budget 1) Conversion rate is good or better 2) Number of visitors/leads have grown.

This was a basic scenario helping you appreciate the power of tracking the conversion rate for your online marketing efforts.

Now, If your marketing funnel is more complex then you might also create multiple conversion rate metrics to track conversions at each stage of a marketing funnel. This is VERY powerful. Here’s an Example:

Conversion Rate #1: (Number of Leads)/(Number of Total Visitors)

Note: your marketing team would define a “lead” based on their criteria(s) like downloads a newsletter, submits a contact us form, favorites a product, etc.

Conversion Rate #2: (Number of Actual Customers)/(Number of Leads)

Note: Conversion Rate #1 is great to evaluate effectiveness of marketing campaigns and conversion rate #2 is great to evaluate sales effectiveness.

How can you capture this data?

A good web analytics tool (like Google analytics) should help you track your conversion rates.

Conclusion:

In this post, we saw that tracking conversion rate is very important metric to track your online marketing efforts.

How does Internet of Things (#IoT) impact data professionals?

Internet enabled computers to be connected with each other.

Internet enabled Mobile Devices to be connected with each other.

Now, Internet will be used to enable physical things to be connected with each other. This is what is called “Internet of things” (IoT).

So what happens?

since more devices are connected with internet – we will able to generate more data! This is usually good if there’s a business vision around how to make sense of data to increase efficiency of all these things.

Here’s a nice case study from Microsoft (focus on the business case – the things in this case is “elevator” to drive reliability)

 

This is all good news for data professionals! There will be increased demand for professionals who can help businesses make sense of data generated via IoT.

Also beware of the “hype” around this technology. It’s important to take incremental steps to achieve the vision – Instead of trying to analyze data from ALL devices in your organization, start with one physical thing that matter the most for your organization or start with data that you have and take incremental steps to spread data culture in your organization!

Now that Big Data has become a mainstream word in IT and business, we have a new buzzword to learn/talk about IoT – but remember it’s all about making sense of data and your skills would be more valuable than ever!

Achievement Unlocked: Tableau Desktop 8 Qualified Associate!

To test my Tableau knowledge, I attempted the Tableau product certification and got the “Tableau Desktop 8 Qualified Associate” certificate.

Tableau 8 Qualified associate Certificate paras doshi

 

Business Metrics #3 of N: Inventory Turnover

Summary:

In this post, we will a common metric used in inventory management called Inventory Turnover

Description:

In simple terms,

Inventory Turnover = Sales / Inventory

why do we want to measure this?

A business manager can analyze this metric to figure out the efficiency of sales and efficiency of buying.

A high over turnover equals strong/efficient sales OR inefficient buying process. It can also show loss in business due to lack of goods supply.

A Low turnover equals inefficient sales or marketing efforts and excess inventory.

How do you benchmark inventory turnover?

usually, it’s bench-marked against Industry average. You don’t want to benchmark a company selling Auto Spare Rates versus a company selling dairy products because company selling dairy products (perishable goods) would have a high turnover ratio since they move inventory fast.

Conclusion:

This was a high level discussion of a business metric “Inventory Turnover” commonly analyzed by business managers to keep an eye on their sales and buying efficiencies. of course, the use of the formula would involve interviewing business managers to understand how they measure inventory turnover but whatever the formula may be it should ideally be consistent across the organizations.

Here are some links if you want to research further:

http://www.investopedia.com/terms/i/inventoryturnover.asp

http://www.accounting-basics-for-students.com/cost-of-goods-sold.html

http://accountingexplained.com/financial/ratios/inventory-turnover

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

Exploring, filtering and shaping web-based public data using Data Explorer Excel add-in:

Data Explorer let’s you “Explore” (search) for web-based public data. This is a great way to combine data that you may have in your data-sources with public data sources for data analysis purposes. Sometimes your data might not tell you the reason behind the observed trends, so when that happens – you can try to see if a public data-set might give you the much-needed context. Let me give you an Example before we start hands-on w/ data explorer so that you have better understanding of importance of public datasets. Here’s a sample that I found here. So, Here’s a demo:

An auto company is seeing sales trends of Hybrid cars and SUV’s from the sales data-sources. But what is the reason behind that? company data does not show that. Someone hypothesizes that it might be because of gas prices. So they test out the hypothesis by combining gas prices information available via public data. And turns out gas prices might be the driving force of sales trends! SEE:

if the gas prices increase, then the sale of SUV go down and the sale of Hybrids go up:

data analysis combine data with public datasets

You know that public data can be helpful! So how can you search for public data-sets? Well, You can manually search online, ask someone, browse through public data repositories like azure data market (and other data markets), there’s also a public data search engine! OR you can directly search for them via Data Explorer.

Here are the steps:

1) Excel 2010/2013 > Data Explorer Tab > Online Search > type “Tallest Buildings”

excel public data search data explorer2) I selected one of the data-sets that said “Tallest completed building…. “

excel data from internet

3) Now let’s do some filtering and shaping. Here are the requirements:

- Hide columns: Image, notes & key

- clean columns that has heights data

- Show only city name in location

OK, let’s get to this one by one!

4) Hiding Columns:

Click on Filter & Shape button from the Query Settings:

excel data shaping cleaning

Select Image Column > Right Click > Hide:

excel hide remove columns

Repeat the steps for notes & key column.

Click on DONE

5) clean column that has heights data.

Click on Filter & Shape to open the query editor

A) let’s rename it. Select column > Right Click > Rename to Height > press ENTER

B) let’s remove the values in brackets. Select Column > right click > split column > By delimiter > At each occurrence of the delimiter > Custom and enter “(” > OK

excel split a columnThis should transform the data like this:

excel data explorer split a column

Hide height.2 and rename the height.1 to height

Click on DONE

6) Let’s just have city names in the location column

click on Filter & shape to load query editor:

A) select location > right click > split column > by delimiter > Custom – Enter: ° in the text box like this:

an excel split by delimiter dataclick on OK

Hide Location.2, Location.3, Location.4 & Location.5

Select Location.1 > Right Click > Split Column > by Number of characters > Number of characters: 2 > Once, as far right as possible > OK

cleaning data in excel shaping filtering

Hide Location.1.2 and rename Location.1.1 to Location

One last thing! making sure that the data type of height is numbers.

Select height > change type > number

Also,

Select floors > change type > number

click on DONE. Here’s our filtered and shaped data!

filter data excel shape clean

7) LET”S VISUALIZE IT!

For the purpose of visualization I copied first 20 rows to a separate excel sheet and created a chart:

z excel data visualization

That’s about it for this post. Here are some related Posts on Data Explorer:
Unpivoting data using the data explorer preview for Excel 2010/2013
Merging/Joining datasets in Excel using Data Explorer add-in
Remove Duplicates in Excel Tables using Data Explorer Add-in
Web Scraping Tables using Excel add-in Data Explorer preview:

Your comments are very welcome!

Getting Started: Implementing Dynamic Security with row filters in Tabular Models

In this blog post. I’ll help you get started w/ implementing dynamic security with row filters in Tabular Models.

Scenario:

We’ve users that connect to a Tabular Model via Excel for Data Analysis purposes. One of the analysis that they do is Countries VS. Total Margin:

tabular models countries total margin profit

What we want to do is restrict someone from Europe to see data only about France, Germany and United Kingdom

Solution:

1) Open Tabular Model in SSDT (SQL Server Data Tools)

2) Toolbar > Model > Roles

tabular models BISM roles

3)  Role Manager > NEW > change Name to Europe and Permissions to Read

4) Under the Row Filters, for the Geography Table, enter the following code:

=[Country Region Name]=”France” ||  [Country Region Name]=”Germany” || [Country Region Name]=”United Kingdom”

How to edit code for your scenario? change the [country region name] to your column(s) and also change the values

role tabular dax filter ssdt code

5) Click OK

6) Now let’s test it!

7) Toolbar > Model > Analyze in Excel

8) Select the role Europe

dynamic row filter in tabular models9) Click ok.

10) From Pivot Table, Select Margin & Countries:

DAX tabular models dynamic row filters based on location

11) As you can see, since the role Europe was selected for testing purpose in step 8 –  ONLY France, Germany and UK data is shown in our test! This means that our row filters are working perfectly!

I hope this tutorial helps you get started on implementing dynamic security in Tabular models.

Resource:

WhitePaper: Securing the Tabular BI Semantic Model

 

Data Analysis and In Memory Technologies, let’s connect the dots:

SPEED is one of the important aspect of Data Analysis. Wouldn’t it be great if you query a data source, you get your answers as soon as possible? Yes? Right! Of course, it depends on factors like the size of the data you are trying to query but wouldn’t it be great if it’s at “SPEED OF THOUGHT“?

So Here’s the Problem:

Databases are mostly disk based and so the bottleneck here is the speed at which can get access to data off the disks.

So what can you do?

Let’s put data in a RAM (memory) because data-access via memory is faster.

If it’s sounds so easy, why didn’t people do it earlier? And why are we talking about “In Memory” NOW?

1) BIGGER Data Size/sets and so today with more data, it takes more time to query data from databases. And so researchers have looked at other approaches. One of the effective approach they found is: In-memory

(And I am not ignoring the advances in Database Technologies like Parallel databases, But for the purpose of understanding “Why In-memory”, it’s important to realize the growing size of data sets and a viable alternative we have to tackle the problem: In memory. And also I am not saying that it’s the ONLY way to go. I am just trying to understand the significance of in-memory technologies. We, as data professionals, have lot’s of choices! And only after evaluating project requirements, we can talk about tools and techniques)

2)  PRICE of Memory: Was the price of RAM/memory higher than what it is today? So even though it was a great idea to put data in memory, it was cost-prohibitive.

So Let’s connect the dots: Data Analysis + In Memory Technologies:

What’s common between Microsoft’s PowerPivot, SAP HANA, Tableau and Qlikview?

1) Tools for Data-Analysis/Business-Intelligence 2) Their Back End data architecture is “In Memory”

So since Data Analysis needs SPEED and In-Memory Technologies solves this need – Data Analysis and Business Intelligence Tools adopted “In-memory” as their back-end data architecture. And next time, when you hear a vendor saying “in-memory”, you don’t have to get confused about what they’re trying to say. They’re just saying that we got you covered by giving you ability to query your data at “speed of thought” via our In-memory technologies so that you can go back to your (data) analysis.

That’s about it for this post. Here’s a related post: What’s the benefit of columnar databases?

your comments are very welcome!