Examples to help you differentiate between Business Intelligence and Data Science problems:

In this post, I’ll list few examples from various industries to help you differentiate between business intelligence and data science problems.

Sometime back, I blogged about “Business Analytics Continuum” and in the post we saw that Every Organization has DATA but they use their business data at different levels because of their maturity level. Excel (or other transactional reporting tools) is usually the starting point for any organization – it helps them see WHAT happened. They advance to the next stage, where they get capabilities to slice and dice their data – To find out WHY – and usually this capability is delivered using Business Intelligence tools & techniques. Once the data culture spreads – Thanks to a successful Business Intelligence project – then they soon start to outgrow their business intelligence capabilities by asking problems that need predictive capabilities. This is advanced analytics and Data Science stage. To that end, here are 5 examples to help you differentiate between business intelligence and data science problems:

Business Intelligence.(WHAT & WHY) Data Science & advanced analytics.
Bike Rentals
  1. How many bikes did we rent in Q3 2014? How does that compare to Q3 2013?
  2. What is the trend of total bike rentals at week level? Can you break it down by geography?
Can you predict bike rentals on an hourly basis?
Credit Risk
  1. How many customers have a credit risk of ‘C’?
  2. Can you rank customers by their payments due amount that have a credit risk ‘C’?
Can you predict the credit risk of the customer during contract negotiations stage?
Customer relationship management
  1. How many account cancellations occurred this year (broken down by month and customer segmentation)?
  2. How does percentage of account cancellations this year compare to that previous year?
 Can you predict customer churn?
Flight Delays
  1. What is the trend of % of flight delayed this year?
  2. Can you break down flight delays this year by their reasons?
Can you predict whether a scheduled flight will be delayed by more than 15 minutes?
Customer feedback
  1. What is the customer satisfaction % trend this year?
  2. What is the customer satisfaction % broken down by customer segments and product segments?
Can you classify a customer feedback comment into “positive”, “negative” or “neutral”?

I hope this helps!

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

PASS September 2014 Outstanding Volunteer

Paras Doshi:

Dan English and I got the “PASS Outstanding Award” for our work with Business Analytics Virtual Chapter. Thanks & Congrats Dan, It’s great to have you on the virtual chapter’s leadership team :)

Originally posted on Dan English's BI Blog:

Just a couple of weeks ago I received an email notifying me that I had been nominated as one of the PASS Outstanding Volunteers for September 2014! The official email stated that Paras Doshi, the PASS Business Analytics Virtual Chapter co-lead were selected for our excellent work with the Business Analytics Virtual Chapter.image

image

The official list for the PASS Outstanding Volunteer nominations is listed here –> Outstanding Volunteers

Dan English and Paras Doshi – Dan and Paras are model Virtual Chapter leaders. They have taken the BA VC to a new level in terms of activity and audience and have done wonderful, innovative work when it comes to posting session recordings on YouTube. Through their efforts they promote growth for PASS in terms of audience and the range of content offered – not just SQL server, but the holistic data platform. They have gone from being completely new VC…

View original 83 more words

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.

Business Metric #4 of N: “Leads” (marketing)

Summary:

In this post, we will discuss about a common metric used by Sales & Marketing teams called “leads”.

Description:

In simple terms,

Leads = number of individuals (or companies) that have expressed an interest in your goods or services.

why do we want to measure this?

For a business to grow, it’s important that the sales & marketing department work to make sure that there is a growing interest in company’s goods or services. It’s important to track this metric to make sure that it’s a positive upward trend!

Word of caution: It’s important to also note that this metric on its own can be misleading. It might be a good idea to also track “conversion ratios” (converting leads or potential customers into actual customers) to make sure that high-quality leads are being generated.

where can you get this data?

Depending on the channel that you use to capture potential customer’s information & the technology maturity of the company, it varies. I’ve seen CRM systems used to report “leads” data and I’ve also seen manual excel files that are used to generate leads report.

Are there any sub-categories?

Yes, it’s usually subdivided into 1) Marketing Qualified Leads and 2) Sales Marketing Leads.

usually, Marketing Qualified lead (MQL) is someone who has shown interest in your product or service but you don’t know if they fulfill your qualifications to buy your products or services. out of all MQL’s, those leads that qualify your criteria and are identified are someone who is ready to buy your products or services becomes your Sales Qualified Lead (SQL) and sales department get’s ready to engage with these leads to make them an actual customer.

Marketing Funnel Sales Qualified Lead

Conclusion:

In this post, we saw a high level overview of a business metric used in marketing and sales called “leads”.  As mentioned earlier, don’t report on just “leads” – it can be misleading for marketing & sales executives since upward trend in number of leads doesn’t necessarily result in increased sales unless the quality of new leads is same or better. Marketing and sales executives would really appreciate any context  (example: conversions) that you can provide to their “leads” report. I hope that helps!

Time Intelligence in MDX: last N days

it’s a common requirement to create a report that shows last N days of a business metric – so I thought I’ll post a template here for SQL server analysis server’s MDX query:


WITH
  MEMBER [Measures].[Sales_last_15_days] AS
    Sum
    (
      {
          [Calendar].[Date YYYYMMDD].CurrentMember.Lag(14)
        :
          [Calendar].[Date YYYYMMDD].CurrentMember
      }
     ,[Measures].[Sales]
    )

   MEMBER [Measures].[CurrDate] as
      "[Calendar].[Date YYYYMMDD].[" + Cstr(Year(Now())*10000+month(now())*100+day(now()))  +"]"

SELECT
  {
     [Measures].[Sales_last_15_days]
  } ON COLUMNS
FROM 
[CubeName]
WHERE
STRTOMEMBER([Measures].[CurrDate])

Here are things that you’ll need to adjust to make it work for your scenario:

1. Date Dimension Attribute & it’s format. The example shows yyyymmdd but you could have different format of the date.

2. Measure name. Instead of [Measures].[Sales] you’ll have to replace it with your business metric. Also, make sure you are using the right aggregate function, in the example above I have used SUM but you’ll have to change this based on your requirement.

3. Create a parameter and use it in index for the Lag function.

4. change [cubename] to your cube name.

I hope this gives you a good starting point to create last N days for your business metric.

SQL Server Reporting Services Tip: How to capitalize just the first letter of text?

Attention to detail is a key in creating SSRS reports/dashboards that look like a work of a professional; To that end, here’s a tip: How to capitalize the first letter in your string? In other words, how to Camel Case the Text?

Here’s the function that you can use in your SSRS Expressions:


StrConv("hello world",3)

OR

StrConv("hello world",vbProperCase)
Input Function Output
hello world StrConv(“hello world”,3) Hello World

I hope that helps!

Back to basics: Design your Business Intelligence system to have lowest level data even if it’s not asked!

Here’s a scenario:

A Business Intelligence (BI) system for Sales is being developed at a company. Here are the events that occur:

1) Based on the requirements, It is documented that the Business needs to analyze Sales numbers by product, month, customer & employee

2) While designing the system IT learns that the data is stored at each Invoice Level but since the requirements document doesn’t say anything about having details down to invoice level, they decide to aggregate data before bringing in their system.

3) They develop the BI system within the time frame and sends it to business for data validation.

4) Business Analysts starts looking at the BI system and finds some numbers that don’t look right for a few products and need to see Invoices for those products to make sure that the data is right so they ask IT to give them invoice level data.

5) IT realizes that even though business had not requested Invoice Level data explicitly but they do NEED the lowest level data! They realize it’s crucial to pass data validation. Also, they talk with their business analysts and found out that they may  sometimes need to drill down to lowest level data to find insights that may be hidden at the aggregate level.

6) so IT decides to re-work on their solution. This increases the timeline & budget set for the project. Not only that they have lost the opportunity to gain the confidence of business by missing the budget and timeline.

7) They learn to “Design BI system to have the lowest level data even if it’s not asked!” and decides to never make this mistake again in the future!

This concludes the post and it’s important to include lowest level data in your BI system even if it’s not explicitly requested – this will save you time & build your credibility as a Business Intelligence developer/architect.

Business Intelligence system for Inventory management for a manufacturing organization:

Business Intelligence system for Inventory management for a manufacturing organization:

Business Goal:

Need to answer following questions: 1) How much is the Inventory? 2) What products makes up the Inventory? 3) How old is the Inventory? 4) Where is Inventory?

Summary:

- Extract data from five data source systems. (Before the system, analysts had to manually extract data from five systems to get a full picture of inventory & It took them 2+ hours to develop reports using tools like excel. Due to this, executives & Sr. Managers didn’t have a (near) real-time view of the inventory and there was a huge opportunity to help them monitor inventory levels & trends to help them make better operational decisions)

- Used Microsoft BI stack to create cubes & dashboard.

Mockup:

BI system allows the analysts & operational specialists to drill down to the lowest data available but here’s a dashboard for executives & Sr. managers:

Inventory Management Business Intelligence Manufacturing