All Things Data

Foundation for Sucessful Data Analysis

In these challenging economic times there is a greater need for data analysis to understand what is going on and to determine what to do about it.

There are a plethora of data mining and business intelligence tools on the market today.  Even Microsoft’s SQL Server has business intelligence multi-dimensional data cubing and data mining algorithms built-in as part of its capabilities.

Still, while there are lots of great analysis tools available, it is a fundamental step that you need to first have your data properly organized to gain the benefit of these tools.

For example, if you are analyzing transactional data for multi-entity organizations, the first step is to identify and link all of the separate, but related entities and individuals that are part of the same organization in order to get the complete analysis of an organization.  Also, the data needs to be organized in an efficient manner, so that the analysis tools can work in an efficient manner as well.

Further, a continuously updated data framework is going to enable analysis on an on-going basis and not just a moment in time snapshot.  Without first organizing and coordinating the data, you either will be unable to perform the necessary analysis or your analysis will be incomplete and inaccurate regardless of the analysis tool.

Master Data Management and Rightsizing the Business

In these difficult economic times, it’s important to cut expenses where ever possible.  But how do you know what and how much to cut?

The answers are in your data.

Your data can tell you many things such as:

1. How many customers and prospects that you have?
2. How much revenue by customer, sales territory, and market segment?
3. Where your customers and prospects are located?
4. How effective are certain marketing campaigns?

The answers to these questions and many others are the keys to making decisions regarding the allocation of resources.  Here are some examples:

• Knowing the proper number of customers and prospects and current and potential revenue by markets or territories lets you know how many sales reps, service personnel, service locations, and other expenses that can be consolidated to manage profitability.

• Knowing that someone you sent a direct mail piece has responded either by mail, by phone, through your website, or in-person is critical in understanding whether the campaign should be continued and how much budget should be allocated to future campaigns.

However, there are many challenges to utilizing your data to get the answers to these types of questions.  Here are some of the challenges:

1. Multiple records exist for the same or related customer or prospect entity or contact due to multiple contacts, multiple locations, lack of data uniformity for organization and contact names, and data entry errors for the same entity or contact in your data files.  This makes it difficult to properly report counts and eliminate duplications.  Over counting may result in a missed opportunity to reduce expenses.

2. Information is contained in separate data files such as the initial mailing campaign file, and the related responses by the mail, the web, by phone, and in-person.  The respondents may use different representations of their contact and organization names for the different response vehicles.  This makes it a difficult task to track the initial mailing with the actual results.

Customer Data Integration or Master Data Management software can help properly count the multiple representations of the same or related entity or contacts and correlate records that are contained in separate data silos through matching algorithms.  With the matching and linking via the software’s numbering schema, proper counts can be generated and information from separate data sources can be queried.  This information can help determine the maximum expense consolidation to manage profitability.

Excel Pivot Tables Revisited

Many of our clients have sophisticated tools and expertise in-house in order to analyze and report on data.  Therefore, these clients engage us so that we can help integrate their data from separate data sources in order to develop a single customer view.  Once they have a customer data integration application in place, they can analyze and report on their separate data sources using their single view of the customer.

However, we are also seeing more and more clients who are not only asking to implement a customer data integration system and develop a single customer view, but are also looking to us to help analyze and report on their data integration.  Sometimes, these clients do not have the internal personnel and/or tools to analyze and report on data.  Other times, they may have the tools, but lack the expertise on how to use those tools.  Further, there are times when the client does have the internal expertise and the tools, but those resources are not available.  Therefore, I thought it would be a good time to revisit the subject of Excel Pivot Tables.  We have been using Excel Pivot Tables to assist clients with their analysis and reporting when they do not have internal expertise available.

I spoke with Steve LoCastro from BDS to get a sense of what he likes about Excel Pivot Tables and what are some of the limitations.

Steve stated that Excel Pivot Tables are basically a front-end interface that enables you to build a query in a similar fashion as Access in order to develop a report.

The things that Steve likes about Excel Pivot Tables are that they are: 

1. Simple to use
2. Easy to set-up
3. Easy to change

However, Steve also pointed out some of the limitations of Excel Pivots Tables.

First, if you want to report on any information that needs to be derived, this needs to be developed outside of the Pivot Tables.  For example, if you want to report on how old someone is and your data provides the date of birth, but not the actual age, this would have to be derived outside of the Pivot Tables.  Therefore, we typically need to write an application in order to build a data framework in order to derive certain pieces of data that need to be analyzed before using the Excel Pivot Tables.

Another major limitation is that the Excel Pivot Tables are best suited for looking at one query or question at a time.  If you want to know how much revenue that you have, that may be one query and one table.  Then, if you want to look at how many sales by representative, by region, by product line, or other variables, the Excel Pivot Tables are best suited to look at each one of these variables separately.  Other more sophisticated tools may give your end-user the option to take a look at the general question regarding your revenue and then keep drilling down further to look at different slices of revenue.  Of course, these other sophisticated tools are more expensive, require more expertise to set-up, and require more time to develop before the end-user can start drilling down on these variables.

Campaign Lists vs. Building Databases

Many companies seek out the advice of consultants, brokers, and data list companies to provide them with data for marketing campaigns.  However, both the providers of the lists and those that are seeking prospect lists are missing out on the strategic value that external marketing data can provide as part of a customer data integration application to build a single customer view. 

External marketing campaign lists can be purchased with company, contact, and demographic information that can be used for analysis and database building purposes.  When you are speaking about b-to-b prospect lists, examples of demographic information include employee size, company revenue, and industry.  All kinds of general information and information that is specific to an industry, such as when a Worker’s Comp file is up for renewal, are also available.  All of this information can add value to your single view of the customer.

Here are 4 ways that you can benefit from utilizing customer data integration and external marketing data in order to profile your current customer and prospect base as part of a single customer view:

1.  Save money and be more effective by developing more targeted prospects lists for your next campaign. 

2.  Up-sell, cross-sell, and service customers with external information on your customers and prospects

3.  Identify potential business for on-going marketing and not just one-time campaigns

4.  Allocate company resources better by understanding the combinations of your customers’ demographics, products and services purchased, revenues produced, and other variables

Business Intelligence, Customer Data Integration, and Excel Pivot Tables

In my last post regarding customer data integration and analysis, I mentioned that there are a lot of great business intelligence tools.  Also, these business intelligence applications can vary greatly in terms of cost, learning curve, and functionality.  Today, I would like to further elaborate on one of the most practical options, Excel Pivot Tables.

Excel Pivot Tables are an excellent option for anyone who is looking for a powerful, yet affordable reporting capability.  According to Microsoft, PivotTables allow you to create multidimensional data views by dragging and dropping column headings to move data around.  The Microsoft link also provides tips on how to use Pivot Tables.

We like Excel Pivot Tables as an entry level business intelligence tool because everyone already has Excel.  Therefore, an organization can roll-out their business intelligence application without purchasing any additional software.  Additionally, since most users already are familiar with Excel, the learning curve to understand how to develop and use the Pivot Tables is low compared to other alternatives.  Also, since Excel is universal, external books, courses, and professionals are available to help you learn and utilize Pivot Tables.

Even though Pivot Tables are comparably affordable, that does not mean they are not powerful.  As you can see from the above Microsoft link, not only can you create spreadsheets, but with the Pivot Chart capability, you can also create graphics.  Further, like other business intelligence tools, you can create reports that contain certain variables and then you can drill down further to view the details behind the grand totals.

Now, many of our clients are using the more sophisticated, higher-end applications.  Although I am not an expert on business intelligence tools, I am sure these higher-end systems provide much more capability than the Excel Pivot Tables.  However, if you are just starting out, we would suggest that the Pivot Tables are a great place to start.  Once you start using the Pivot Tables and are gaining value, you may at some point find the Pivot Tables limiting and want to take your reporting capabilities to another level.  At this point, you will be an educated buyer who will understand what features are important to your goals when evaluating your business intelligence options.

Regardless of the reporting tool that you are using, customer data integration plays a key role in matching and linking data from disparate data systems in order to develop a single customer view.  The Common IDs that a CDI application can apply to same or related organization and contact records are critical in enabling data analysis across multiple data silos.  Common IDs are what enable business intelligence reports to accurately count the number of organizations and contacts for the criteria that you are analyzing.  Whether you are using Microsoft Excel Pivot Tables or the most sophisticated and expensive business intelligence application, Common IDs are necessary to provide accurate and meaningful reports.

Customer Data Integration and Analysis

There are lots of great applications of different levels of sophistication such as Cognos, Crystal Reports, Excel Pivot Tables, Qliktech, and many others that help analyze internal business intelligence.  However, one of the challenges in data mining this business intelligence is properly reporting on the multiple representations of the same company or contact that are in an organization’s disparate database systems.  There could be duplicate records, records that are part of the same company, but have different contacts, different locations for the same organization, and other combinations.  If these multiple representations of the same company or contact are counted as being more than “one”, counts will be erroneous due to over stating the number of locations and contacts rendering the business intelligence reports inaccurate. 

One of the very first benefits of any customer data integration project is the ability to analyze data from disparate data systems.  Typically, a company will want to perform analysis based on information in their CRM system, which contains customers and prospects, and their financial systems.  For example, a common analysis question is how much revenue is received from a certain organization.  This seemingly simple question can become quite complicated due to multiple records in your database systems with the same organization, but different locations, as well as subsidiaries and divisions that have different names.  Customer data integration can solve this issue by developing a numbering system that places Common IDs on all the location and contact records that are part of the same organization across multiple databases.  Once you have a Common ID, reports can be run that accurately count the multiple representations of the same organization and contact as “one”.  Once data is matched and linked with a numbering system of Common IDs, an organization can perform analysis immediately even if there has not been any data clean-up.