Data Mining – Friend or Foe to My Customer Lifetime Value
December 30, 2009
We just had a new Wal-Mart Super Center open about 3 miles from our house in Cincinnati, Ohio. They built it on land that used to have a “Big 3” automobile factory on it years ago. Vacant for at least a decade, it was torn down and then up sprang an Arkansas just-in-time building, the Wal-Mart Super Center. I normally don’t like shopping at Wal-Mart; I do value low prices although not as much as fast entry/exit and knowledgeable clerical sales help. As an IT Professional, I do admire Wal-Mart for their storied data mining and sales analytics platform. You know…the stories of just-in-time shipping of more beer to Florida when a hurricane starts to threaten the Sunshine State. They’ve been written up in almost every IT journal over the last 10 years as well as every national business publication.
So, I decided to venture into the Super Center 3 days before Christmas to specifically buy a popular board game, Risk, for my three boys. The store greeter told me “left and down 5 aisles or so.” I came upon the shelves where Candy Land, Trouble, Life, Monopoly and others were located. No Risk. There was a stocker, 45 years old, unshaven…reminding me of an auto mechanic…he seemed out of place and I wasn’t sure whether he’d even talk to me. He was just walking away from the game shelves. I ran after him stuttering with uncertainty to see if he knew whether they carried Risk and if I was in the right section? He stopped and said, “well, yes and no. We don’t sell much so it isn’t stocked very much.” I then asked about Candy Land, Monopoly, and Life and their stocking levels. “We sell tons of them.” So, that’s it.
My question is…does Wal-Mart really do analytics of all of their customers and prospects? If it’s the normal retail geography of “1-mile, 2-mile, 5-mile, 10-mile” I should be very near their sweet spot of 3 miles away! With their new grocery store there, should I be classified as the 40-something guy that wants to buy micro-beers, brie and gorgonzola cheeses, organic crackers and Risk?
Every media company in the country is publishing articles trending the growth of data volumes doubling and tripling! That includes Wal-Marts data as well. Business Intelligence and Customer Analytics are going to be key tools for retaining customers, finding new customers, and overall market share strategies. Leveraging them wisely through intelligence business questions and common sense will still be integral to the overall platform. Just because you use Business Intelligence doesn’t mean that you are going to be successful…setting specific goals up-front in the process is integral to success.
The Wall Street Journal article this week indicated that Wal-Mart’s web ordering is gaining market share rapidly. Perhaps they should have offered a web order entry for me with a quick ship program for Risk? Perhaps next year….for now, I’m going to venture out again and buy the Risk game at another retailer.
The VLOOKUP Hookup
December 22, 2009
Companies invest large amounts of money, time, and other resources acquiring and implementing reporting and analysis software. I’ve seen organizations invest hundreds of thousands of dollars in projects and fail to realize a decent return on their investments. The point of this series of posts is to educate you about the reporting and analysis capabilities of a tool your organization probably already owns: Microsoft Excel.
In this series of posts, I will discuss a number of these capabilities and will give some concrete examples of how to utilize them.
I will be using Excel 2007 for these examples. Much of this functionality is also available in Excel 2005, it’s just not as easy to use and does not have some of the more advanced features.
Getting the Data
The first step in any effort is to get some data into Excel. We’ll start out using a simple static list. You probably already use lists like this regularly. If you don’t utilize Excel in this way today, think of the reports that you work with from the various systems that you run your organization with. In most cases, you could probably either copy andhttp://thefuturevalueofbusiness.com/wp-admin/post.php?action=edit&post=745&message=6 paste or import these reports into Excel to get some data to work with.
In future posts, we’ll cover a much more powerful method of acquiring data by connecting to external databases from within Excel. For now though, we’ll stick with this simple example.
I’ll be working with the sample database that comes with Microsoft’s SQL Server database software. This sample database contains information about a fictitious company called Adventure Works. Below, you can see that I have an extract of order information that I’ve pasted into Excel.
This is the most common manner in which people utilize Excel for reporting purposes: simple lists of data pasted or imported from other sources. In most situations, this data comes from existing reports or queries. My example above is a very simple query…you can see that we don’t even have names or descriptions for most of the data. For example, Column F is showing us the Product ID instead of the Product Name.
The best way to solve this problem is to have the author of the report or query modify it to include the Product Name in addition to the Product ID. Let’s imagine that this is not a realistic option though; there is a way that we can solve this problem using an extremely powerful Excel formula called VLOOKUP.
Using VLOOKUP
To expand on our situation above, let’s imagine that I have a second worksheet in my Excel workbook. I have an image of this second sheet below.
The Product ID in column A corresponds to the Product ID in column F on the Orders List. We are going to use VLOOKUP to take the Product ID in the orders list and lookup the Product Name in the product list.
To make the formulas a little more understandable, I am going to rename the Sheet with the order list “Orders” and I am going to rename the Sheet with the products list “Products”.
On the “Orders” sheet, let’s insert a column immediately to the right of the Product ID. We’ll label it “Product Desc” in Row 1. In Row 2, we’ll enter the VLOOKUP formula:
| =VLOOKUP(F2,Products!A:B,2,FALSE) |
The parameters (the information between the parentheses) tell Excel how to lookup the value we want:
- The first parameter, “F2”, tells Excel what value we are performing the lookup for. In this case, we are looking up the Product ID.
- The second parameter, “Products!A:B”, tells Excel where to go to do the lookup. Here I selected the first 2 entire columns on the “Products” sheet.
- The third parameter, “2”, tells Excel to bring back the data in column 2 from the lookup list when it finds a match for the value from cell F2. I know that’s a confusing sentence at best, but it will make sense in a moment.
- The last parameter, “FALSE”, tells Excel that we want it to return only an exact match for the value we are looking for. If Excel cannot find an exact match for the Product ID, it will return an error indicator.
The Results
Now, let’s take a look at the results of our formula. The screenshot below shows what I have now.
This screenshot shows a few rows from the “Products” sheet:
Hopefully you can see how VLOOKUP works now. Excel took the value in F2 in the “Orders” sheet, 776. It went to the first column of the range we gave it; that range was Columns A and B of the “Products” sheet. It scanned through that range until it found a match for 776. It then took the value in the 2nd column of the range, column B, in the same row and returned the value in that cell (“Mountain-100 Black, 42”).
One thing I didn’t make clear before that I want to point out now. VLOOKUP is always going to look in the first column of the lookup range for the matching value. In our example, the lookup range was columns A and B of the “Products” sheet, so Excel looked in column A for the matching value. There is no way to tell the formula to look anywhere other than the first column; so you either need to cut and paste the columns to get the right one first, or just change the reference so the lookup column is first.
To complete our list, we can just fill down the VLOOKUP formula in column G to the bottom of our orders list. Now we can analyze our order data with actual Product Names instead of just Product IDs.
Summary
VLOOKUP is useful in many other situations…you can probably imagine a few other uses for it yourself. It is very handy to use it as we did in this example though. Even though we could have accomplished the same goal by having someone in IT modify the query or report, now you can be a little more self sufficient with your reporting needs.
In my next post, I’ll cover a few more features like filtering and date manipulation. Ultimately, we’ll move on to Pivot Tables and External Queries which provide very powerful mechanisms for analyzing data and can compete with some features offered in expensive reporting software.
Sprechen sie…IT?
December 17, 2009
Tonight I was at a local grocer (that’s headquartered in Michigan). I was at the “No Limit Self-checkout” at around 9:45 PM. (I have a BUNCH of kids so I shop after bedtime…) Anyway…while I was there, they began to shut down ALL of the checkout lines except for the 12 items or less lanes. They explained to those in line that it was time for “change over” and that the lanes would be open just as soon as they were finished. Um…huh?? It’s 9:45PM. I want to go home. I don’t care why my line just got shut down…what are my other options? “Change Over” means nothing to me. I just want to check out.
The whole experience reminded me of what it’s like to be in the operational side of the business requesting services from IT. Think about the SNL sketch with Nick Burns the Help Desk guy. He speaks a language that his customers don’t understand and then treats them like they are stupid for not getting it. The customers eyes glaze over thinking, “so…um…ok… how will you fix my problem?”
Is your IT department speaking your language? If not, then how will they ever help you to solve your business problems? Chances are they don’t understand you either. Bridging the gap takes a skilled interpreter who can understand both. Your best BI architects do this and don’t try to explain ETL, Dimensional Models or ODS to you…cuz really…do you care how it works if it answers your questions and helps to make sense of your data.
I Should Have Called LÛCRUM – Episode 2
December 16, 2009
Well episode 1 was so much fun that I decided to create another episode of “I Should Have Called LÛCRUM” In this episode we explore the problems that manually created spread sheets can create for organizations through the magic of animation. Of course if you want to read more about the topic, you can read this post by LÛCRUM managing partner Jody Detzel. I hope you enjoy the video, and that it tells you a little more about what we do at LÛCRUM.
If your organization is using manually created spreadsheets to manage important company data or if you are having trouble getting accurate, actionable, complete and timely information, you should contact LÛCRUM. We specialize in helping our Clients to maximize the value of their data, and we would be delighted to help you.
I Should Have Called LÛCRUM
December 16, 2009
Last night my brother in law introduced me to a really cool site called xtranormal.com where you can very easily make animated videos – for free. I wanted to test it out, and thought “why not have fun with some of the problems we solve at LÛCRUM?” So I stayed up past my bedtime and played. The end result – Episode 1 of “I should have called LÛCRUM.” Not sure if there will be an episode 2.
Does this seem like a familiar scenario? Do you ever have difficulty getting the important information you need in time to meet your needs? Do you find yourself having to request and schedule reports, which take days or even weeks to actually arrive? Do you wish that you could immediately generate the information you need from your data without the “help” of others? If so, you should think about contacting LÛCRUM. In the time it takes to get those reports the current way, we could build you a data mart with dashboards, alerts, reports, and more – enabling you to make fact based decisions about the future of your business. At LÛCRUM, we help customers to maximize the value of their data – and sometimes we even make silly cartoons to show people how we do it.
Planning… blech
December 15, 2009
It’s December. If your Company hasn’t finished it’s planning process for 2010, surely you are nearly finished. What blows me away is the number of people that try to start planning before understanding what happened in the past.
“Those who don’t know history are destined to repeat it.” Edmund Burke (1729-1797)
If you have been asked to participate in the planning process, were you provided with the stats from last year? How many new customers did you get? How did the change to your business process impact your financial performance? How did your customer demographics change in 2009 and what was the resulting impact on sales? Did lower priced products perform as well as expected in this down economy?
How well is your Business Intelligence system allowing you to answer these types of questions? Does it provide these types of answers quickly, easily and can you do-it-yourself? (Or do you have to call someone in IT?) If your 2010 planning was complicated by the lack of 2009 performance data, perhaps you need to add BI to your 2010 plan.
We Do BI Faster… Part 2
December 8, 2009
It’s been my experience that BI projects fail when the technical team fails to understand the urgency that exists with their customers. Failure also comes in the form of not delivering what was needed to answer the business questions or delivering in a format that doesn’t fit into the day-to-day way that the Customer does his/her job.
My son’s day-to-day life is impacted by data points. One of my sons has Type 1 Diabetes. It’s a pain to manage. There are numbers EVERY day!!! Right now he is tied to a Continuous Glucose Sensor and an insulin pump. (When I say tied…I’m not kidding…it’s really attached…by a 23” tube…24×7.) We receive data points every 5 minutes. On his pump, I can see a display that tells me what his BG is right now. It allows me to make decisions right now. What it does not tell me is how past decisions have impacted the current BG. It doesn’t tell me how actions I take now will impact future BG. I use my “gut” to determine what to do when his BG is high and requires insulin or low and requires some sugar. There are some pre-programmed “rules” that help me to make my decisions (e.g. it takes 1 unit of insulin to drop his BG 90 “points”). Displaying these data points on my son’s pump is great if he’s the only one making decisions. However, his dad, me, our doctor, my son’s teachers…all of us need to see the data to make the right decisions. More importantly, we need to see the historical data to understand the impact of past decisions. A tool does exist from his pump company that allows me to upload the data and view pretty graphs in PDF reports. But the data keeps changing…every 5 minutes…Isn’t the same true in business?
At a grocer…a shopper decides to buy Crest toothpaste and not Colgate (no offense to Colgate…but I’m in a P&G town). The shopper does this despite being sent oodles of coupons for Colgate. What does that data point tell me? Should I stop sending Colgate coupons to that shopper? If I start sending Crest coupons can I guarantee that the shopper will come back the next time they need Crest? Where will that decision get made? Where should the BI tool be?
At a utility company…right now, I get my bill at the end of the month and I pay it. I’ve set my thermostat back to 67 in the Winter and 74 in the Summer. I’ve seen a decrease in my bill over the last 12 months since I started doing this. But last night was COLD!!! I was so cold that I couldn’t sleep. What’s the impact of turning up my thermostat to 72 while I sleep? If the utility company decides to build me a BI app that sits on my PC and prints for me really pretty graphs, it does me no good when I’m lying in bed and want to make a decision on if it’s worth it to turn up the heat.
For a sales guy/gal…I enter all of my contact info in Salesforce.com. I have to send out my forecast to my boss, typically in Excel. Should the IT team give me Business Objects for me to see my past customer sales? What about Cognos?
When I design BI solutions, I try to make sure that the solution is delivered in a tool that I need and that makes sense to me. I don’t want my customers to require training just to look at their data. The tool should be easy for me, cuz analyzing the data is HARD!!! What are you giving your customers for data analysis? Is it a tool that’s easy for you to build? Or easy for them to see the data in the format that they need?
We do BI…faster
December 4, 2009
When asked, I say that I’ve been working on BI projects both accidentally and on purpose since 1993. The first Business Intelligence books were published in 1991 (Inmon) and 1996 (Kimball). TDWI, The Data Warehouse Institute, wasn’t started until 1995. So really, the Business Intelligence / Data Warehousing practices didn’t really start to become players until the mid-90’s. I consider all work that I did prior to 1998 to be “accidental BI”. Looking back, if I had understood dimensional modeling in 1994 I would have actually finished my Activity Based Costing project…but I digress.
There is a “new player” in the field of DW and his name is Dan Lindstedt. Dan’s view of DW is called The Data Vault. Endorsed by Bill Inmon (which speaks volumes), his method is a hybrid approach to the ODS and is “a hybrid approach encompassing the best of breed between 3rd normal form (3NF) and star schema. The design is flexible, scalable, consistent and adaptable to the needs of the enterprise. It is a data model that is architected specifically to meet the needs of today’s enterprise data warehouses.” Today I sat down with my good friend Jon Shirey. Jon and I first worked together in 1993. He is the true Computer Science scholar that takes a disciplined and well thought out approach to data warehousing. He is by no means a cowboy and is always the #1 guy that I go to when I run into a tough question that needs an answer. Jon turned me on to The Data Vault a few years ago when he first attended training.
I was truly impressed with the architecture of The Data Vault. To me, it seems to allow for Real Time Warehousing and improves the auditability of data. When coupled with tools like Balanced Insight Consensus® rapid BI really becomes a reality. My wheels are spinning…I need to keep processing…I’m so excited… stay tuned for updates….
One Person’s Trash…
December 4, 2009
Tis’ the holiday season, and at LUCRUM that means time for the annual holiday lunch, complete with the annual white elephant gift exchange. This year’s exchange was full of white elephant treasures, a few hidden gems, one really big bottle of beer, lots of junk, and even more laughs. The biggest loser this year, as gauged by both quality of gift and reaction of the recipient was Steve McWhorter, who was blessed with the gift of scented drawer liners. Who doesn’t love a fresh smelling silverware drawer right? Congrats Steve.
The lunch was a lot of fun, as members of the LUCRUM team spent a little time enjoying food and conversation. Every time we have these events I am reminded of the great collection of people I get to work with. Thanks to all who put the lunch together and all who shared in the fun of giving and receiving junk and talking a little trash.
Social Media Analytics
December 4, 2009
A few weeks back I had the pleasure of emceeing & presenting at SummitUp – a social media conference in Dayton, Ohio. I presented to a breakout session on the topic of social media and data analytics. The presentation focused primarily on establishing the paradigm that social media transcends individual conversations and represents rich data with can be both aggregated and analyzed to uncover deeper truths relative to areas of thought ranging from computation, innovation, emotion, and process. Much of the discussion of social media to date is centered in trust and transparency at the individual conversational level. These important discussions are rooted in understanding the mechanics of this new conversational landscape. Make no mistake, these are critical concepts to understand and embrace. Recently it seems new conversations are emerging around the concept of using data analytics to better understand the meaning of these conversations and those having them. While still in its infancy, I expect to see this discussion mature rapidly, taking center stage in the minds of many businesses in 2010.
One of the leading voices in this conversation is Avinash Kaushik, who among many other things, is the author of the newly released book Web Analytics 2.0. The book is great. It is easy to understand, even for a non-technical marketing nerd like me, and offers up some very useful information on how to better understand and measure the activity on the web to get real results. Accompanying the book, is Avinash’s blog, entitled Occam’s Razor. One of his recent posts examined the concept of social media analytics, specifically with regard to Twitter, the popular online microblogging platform.
In the post Avinash highlights four useful tools for gathering quantitative and qualitative information out of Twitter – Klout, GraphEdge, TweetPsych, and Twitter Stream Graphs. Each of these tools has different useful attributes, and hint at the emerging potential of social media analytics. I particularly like Klout, as it offers up some pretty amazing insights.
Moving forward I am interested in seeing how social data analytics will grow more robust. I imagine each of these tools having the ability to calculate multiple users simultaneously. Another interesting idea would be the ability to run these tools on followers of your brand and then sub-segment the results on numerous variables. Further, as online identities continue to aggregate with tools like Google Friend Connect and Facebook Connect, it will be easier to track the conversations of individuals and groups of people across multiple platforms – providing businesses with rich internal and external insights on both a micro and macro level.
Where it really gets interesting to me (i.e. makes my head start to spin a little) is the idea of then combining this external data with internal company data. For example integrating facebook, linkedin, twitter, blogs, and other sources of external with individual customer records in a crm system. Then taking that data and running it against something like gps data, or transactional and financial data to create entirely new sources of information. The potential is amazing, and as things like mobile technology, social platforms, online transactions continue to grow, the possibilities expand with them.
My thanks to Avinash for writing both a great blog post and a great book. Both are helping me to think in new and exciting ways. I hope you will take the time to check them out as well.
What about you? What do you think about social media and data analytics? What ideas excite you? Where do you see the opportunities of the future?