16 Jun 2009

Business Intelligence for the People

Dilbert.com

Business intelligence has been talked about for quite a while. Even today, while companies are looking to make budget cuts, some experts are saying that BI can be used to beat the recession.

When I hear about BI systems, the first thing that comes to my mind is a huge and expensive system that has very powerful servers, that sucks data from many sources and runs some intensive and even more expensive reporting suite. Since I had been involved in projects to set those systems up, I know that it can probably take around a year to complete.

So everyone is in fact thinking about saving money yet still being competitive and aggressive in the market. On that subject, there are very popular and stable open source BI suites and similar products (kickefire, scaledb) to achieve both those goals. Before you deploy some “black magic BI”, because that’s what the big companies are doing, there are some simple things you can do to get valuable information from your data.

Assuming your data is in one place, you can create reports using standard SQL that will benefit your company and make them more competitive. To help with that, I have compiled a list of relatively generic reports that most companies might/should be interested in. There is a good chance that after making these reports, the management will see that you actually have some business knowledge and might ask you for more specific reports. If that is the case, then consider the information you have delivered to be a success.


Sales
  • What is the total number of placed orders?
  • What is the total number of orders that were shipped or invoiced? - When a customer places an order it is not always the case that the products are in stock.
  • What is the total amount of money from placed orders? – Sum of order value. This can be with or without shipping costs, depends on the management.
  • What is the total amount of money from orders that were shipped or invoiced?
  • What is the total number of returned/cancelled orders?
  • What is the total amount of money that was lost from returned or cancelled orders (or credit notes)?

These reports should be for specific period of time. For example 1,2,6 and 12 months or 1,7,14,21 and 28 days.
You can compare this month to last month’s results or this month to the same month a year ago.

These reports can also be grouped by:
  • Product types or product ranges
  • Geographical areas (city, country or continent)
  • Customer matrix (teenagers, elderly, soccer moms, university students..).

Products (Best Sellers)
  • Which products sell the most quantity?
  • Which products generate the most revenue? – (product price * quantity)
  • Which products generate the most profit? – ((product price – product cost) * quantity) – To make your business more profitable, you should invest your energy in the 20% of the products that generate 80% of the profits.

Customers
  • How many new accounts/customers do we get (each day, week, month, half year or year)?
  • Who are our best customers – that buy the most from us? - The top 20% that make 80% of the profits.
  • What is our attrition rate – the numbers of accounts/customers we lose (each day, week, month, half year or year)? - If your attrition rate is high, the sales team needs to find a lot of new people just to keep things the same.
  • What is the customer satisfaction? – You will probably get this from surveys or customer feedback.
  • What are the top reasons for complaints?
  • How many existing customers referred new customers to your company? – You might need to setup something to collect this data. If you send your customers to do your marketing for you, you can save a lot of money.


Website
(Some of these reports can be handled by a separate application like google analytics)
  • Which product pages get the most views?
  • Which news/blog articles get the most views?
  • How many minutes does the user spend on the website?
  • What is the percentage of abandoned shopping carts?
  • What is the average amount of time a customer spends in the shopping cart/session?
  • Most important – What is the conversion rate of the visitors coming to the site and the people who purchase a product?
  • Which landing pages are the most viewed?
  • Which landing pages have the highest conversion rate?


Final Words
I genuinely hope that this list of reports will help you influence your business and your position in the company for the better.

I would very much like to welcome ideas for new reports or comments on current ones if they are unclear. Your feedback is appreciated.