Getting started in Business Intelligence (BI) on a budget

This is a simple demonstration as to how you and your firm can get started turning the data that you already have into the information you desperately need using tools you already own. The task of turning data into information for decision-making is the essence of business intelligence (BI).

So, here we go.

Everybody has data

Everybody has data. Many companies are wallowing in data. What they are lacking is “information.”

Read my posts here and here for more about the differences between data, information and knowledge.

Quick! Take five or ten minutes to peruse the following table of data and write down everything that you see in these data to help make decisions about the firm’s future.

I will give you one hint: the column identified as ‘ARPAC’ is “Average Revenues per Active Customer.”

---

Okay. Times up.

Hold on to your list.

Turning data into information—simply, easily, cheaply

In order to produce what follows, I used only Microsoft® Excel™ and its native ability to access databases to fetch and refresh data.

Here’s the first graph I produced:

This is nothing more than a simple bar graph of column “SOSales” (Sales Order Sales, as opposed to Invoiced Sales, for example) shown in the data above. I used Microsoft’s native capabilities to add a “trend line.”

By looking at this simple graph, several questions might come to mind that would bear further investigation:

1. Why have our monthly sales dropped from just over $8 million a month to an average of about $6 million per month over these 29 months?
2. Why or how were able to produce about $11 million in sales in July of 2008? What did we do differently? How can we build on what we learned in that experience?
3. Is my drop in sales related to lost customers?

The next graph that I produced looked like this:

This graph answered my question number three above—at least partially. Month-to-month our firm has stayed pretty steady in terms of the number of active customers served. The firm is hovering right in the 250-customers-per-month range.

On the one hand, that is good. It means the firm is steady in this regard, but it does provoke other questions that would need to be answered through further digging:

1. We are serving about 250 customer per month, but is the same 250 customers, or do I have high turnover rates for customers?
2. Are we constantly having to spend precious marketing resources to capture new customers, or do we have a high volume of repeat business?

But wait! If we are not loosing customers (at least in numbers), but our sales are falling off (in aggregate), what is that telling us?

The third graph I produced was “Average Sales per Active Customer” (month-to-month). This graph clearly shows that between January 2008 and May 2010, the firm’s average sale per active customer fell from about $32,000 per customer to under $25,000 per customer.

Here again, this graph immediately provides clues worthy of further, more detailed, investigation:

1. Are these different customers buying less product? Or, are we serving pretty much the same customers, but they are just buying less from us?
2. Either way, we should figure out why: Are they buying similar quantities, but our prices (and, perhaps, margins) have shrunk over this period? Or, are they buying smaller quantities of merchandise or services from us?
3. Either way, we should find out why: If they are buying smaller quantities, is some of that business going to our competitors?

---

Next steps

As you can see, turning the data into information allows our mind to quickly digest it and move toward decision-making. In some cases—perhaps many cases, when you first start—the process will lead to further information gathering.

On the other hand, you will sometimes discover that tribal knowledge already present in your organization will help you take immediate steps to begin making more money tomorrow than you are making today. Frequently, those steps involve no investment at all. Sometimes all it take is understanding better what is happening. Other times, a simple policy change permits significant increases in Throughput and profits.

After all, isn’t that really what you want to do—not spending six-figures on a new business intelligence “solution”?

---

Read more here about unlocking “tribal knowledge.”

---

How I did it step-by-step

1. Identify the data
2. Build a SQL Server view or query
3. Connect Microsoft Excel to the data
4. Build the graphs

Total time: about 2 to 2.5 hours

Business Intelligence and “Tribal Knowledge” – Part 2

In Part 1 of this series, we were talking about a firm that had identified that the thing that was keeping them from increasing Throughput – from making more money tomorrow than they were making today – was understanding their customers and the other participants in the decision-making process better. I also pointed out that this firm already had some data available to them that could be used to begin the process of understanding their customers better. They had, of course, their historical sales data. But this firm also had available to them an independent database that contained some additional demographic data about their customers and prospects that could be correlated with their own sales data.

I closed Part 1 by suggesting that they could employ these available data to begin exploring relationships such as:

• Sales by salesperson
  
• Sales by salesperson by geography (e.g., city, state, region)
  
• Sales by salesperson by demography (e.g., size of school or school district)
  
• Sales by product line by geography
  
• Sales by product line by demography
  
• Sales by salesperson by product line
  
• Sales by salesperson by product line by geography
  
• Sales by salesperson by product line by demography
  

There are other data elements (dimensions) available to virtually every firm that we are not including here. For example, if you introduce the additional "time" dimension, it may be easy to spot trends over time – e.g., salespersons, regions, or demographic groups where sales are growing or decreasing over time.

This is an example of what business intelligence practitioners call "cubing the data." The data is summarized by various "dimension." In the example above, the sales data is being summarized and the "dimensions" are:

1. Salesperson
   
2. Geography
   1. City
      
   2. State
      
   3. Region
      
3. Demography
   1. Size of school (number of students)
      
   2. Size of school district (number of students)
      
   3. Teacher/student ratio
      

As I said, all of this can be done using low-cost tools available to almost every small-to-mid-sized business and already on the desktop of almost every computer. Microsoft Excel, especially Office 2007 and later versions, is capable of digesting a large set of data within its own operating context. However, if you or your firm has a Standard Query Language (SQL) server and these data reside in a relational database (such as Microsoft SQL Server, especially SQL Server 2005 and later), you have even more relatively low-cost tools to manipulate and digest even larger data sets. SQL Server 2005 and later is even capable of calculating and summarizing data cubes on the fly. These pre-digested data may then be presented to Excel as a presentation tool and user-interface.

Introducing "tribal knowledge"

So what is keeping companies from leveraging the data that they already have in order to use the insights discovered through such analyses? Generally, in small-to-mid-sized businesses I find the following factors are holding them back:

1. Uncertainties regarding the value – I have to put this one at the very top of the list for one simple reason: If executives and managers in the firms were convinced that discovering new factors about their marketplace – market segmentation – would help them make more money tomorrow than they are making today, they would find a way to get it done.
   
2. Uncertainties regarding the costs – Sadly, the business intelligence community itself has much to do with making small businesses wary of the costs moving into the realm of business intelligence. Many who make their money by selling and implementing business intelligence tools want you to believe that is not possible to make real gains and reap significant business benefits without investing in expensive business intelligence software and spending lots of time, energy and money to build expensive data warehouses and, perhaps, hundreds or even thousands of "cubes." This is simply not the case, but it is frequently the belief.
   
3. Uncertainties about how to get started – Again, in part to the pseudo-mystique surrounding the world of "business intelligence," many executives and managers do not feel that they "have what it takes" to get started benefiting from understanding their customers and marketplace better by leveraging the data they have been collecting in their ERP systems for years. There are simple ways to get started and one can always make the leap to more sophisticated business intelligence applications when conditions warrant.
   

But, wait!

So far in our discussions I have intentionally left a tacit implication on the table. That implication is the one that drives far too many executives and managers in companies of all sizes, and it is this: What is valuable and can be leveraged in "business intelligence" is found in our data systems and the data stored or collected.

This is very far from true!

Some of the most important contributions to making computer-based "business intelligence" valuable do not come from the data, nor from the software. These valuable contributions come from the people that have worked in your enterprise year after year. Your people know things about your customers, your prospects, your products, your industry and your marketplace. I call this kind of knowledge held within a business enterprise "tribal knowledge."

Now, tribal knowledge in every organization extends well beyond the examples I will suggest in this series, but I think you will begin to see just how adding tribal knowledge into the blend with the data you have available to you extends the power of business intelligence and may lead to truly valuable breakthrough thinking.

Suppose that in analyzing sales data currently available, they looked at the data summarized in a certain way and the graph looked like the following figure:

The questions that ought to be asked when looking at such a data summarization should be along these lines:

• Why are sales in category 'A' five times better than sales in category 'E'?
  
• What can we learn from what we do to get the results in category 'A' in order to apply it to the other categories?
  

Now, let me bring this down to more practical examples:

• Categories are product lines: What factors make Product Line A perform so well? Do we sell it differently than Product Line E? Do we promote it differently? Do we sell it to different kinds of customers? If so, what are the differences between the kinds of customers? How can we apply what we know about how we sell Product Line A to improve results for Product Line E?
  
• Categories are salespersons: What does 'A' do to get results that 'E' does not? Are these results simply differences by sales territory? Are there demographic differences in 'A's customer list from the customer lists of the other salespeople?
  

Naturally, this of questioning can go on and on, limited only by the management team's ability to think of the "right" questions to ask. Some of the questions can be answered using the data and re-summarizing it in a different way. For example, to answer the question, "Are these differences [between salesperson results] simply differences by sales territory?" it may be necessary to re-summarize the data by sales territory. However, if salespersons and sales territories are synchronous and exclusive, then one might need to compare similar but broader territorial results to see if a pattern exists. (For example, if the salesperson assigned to Washington State is Category A, then one might compare results for other West Coast states to see if they are similarly high even though different salespersons are assigned to these territories.)

The basic point, however, is that the people involved in your organization are carrying about with them "tribal knowledge" that can help you and your management team discover new ways to segment your market and increase Throughput.

[To be continued]

©2010 Richard D. Cushing