Developing a Data Analysis Strategy

Your organization should already be collecting and storing the data that you need to make good business decisions. If your organization does not have a data collection and storage plan, it is at a serious business disadvantage. You should regularly assess your organization’s data collection and storage procedures or make recommendations to others in your organization who are responsible for establishing data collection and storage procedures. If you do not collect or store the right data, you might make less valuable business decisions.

Once collection and storage procedures are in place, an effective data analysis strategy can help an organization unite under a common vision, mission, or financial goal. For example, your organization might want to increase profits by 5 percent next quarter, add 750 new customers next month, or sell 1 million product units next year. This effectiveness can be achieved when representatives from across the organization come together to decide questions such as the following:

• Which data facts should we record about our business?

• Which data facts do we record now and are they sufficient?

• How often should we record the data?

• How should we record, collect, and store the data?

• How will we present the data?

• How will we make future business decisions based on the data?

Depending on the answers your organization comes up with, you may decide that you are collecting the wrong data, not collecting enough data, collecting data at the wrong times, and so on.

To help answer these questions, you should assemble your organization’s key business decision makers, which might simply be you, or might involve your business managers or department heads, or even corporate officers (CEOs, CFOs, COOs, and the like). Without an understanding of business goals and how performance against these goals will be measured, those who implement data capture and analysis strategies within an organization could actually be working counter to the organization’s overall goals.

You should also understand information technology (IT) requirements. What are the budgetary constraints in terms of computer hardware and software? If you have an IT department, knowing whether it has spent its budget for the rest of the fiscal year or whether a large purchase is right around the corner will affect your data gathering and analysis plans. Can you afford any computer upgrades for data storage and other hardware, software, and support?

For certain organizations, you could also assemble the following individuals:

• In a retail sales organization or customer service organization, you could assemble product and service developers, product purchasers, marketing specialists, sales and service managers, and sales and service trainers.

• In a manufacturing organization you could gather operations managers, materials buyers, resource planners, plant managers, safety supervisors, supply managers, warehouse managers, and shipping managers.

• In an insurance organization, you could meet with your actuarial managers, policy line developers, insurance agent managers, and insurance agent trainers.

Last but not least, don’t forget to include your customers’ points of view through focus groups, interviews, or informal surveys. Do they perceive your current data collection efforts as too intrusive? Does the data collection experience detract from your customers’ purchasing behaviors? For example, if you visit a local retail store and the cashiers ask you for your complete address and phone number at every sales transaction, would you return there?

In answer to the question about which data facts to record, let’s say your organization’s representatives decide to start by recording facts only about your customers’ purchasing behaviors and demographics. After an intense planning meeting, here are the areas the group decides to focus on:

• What products and services do our customers purchase most often?

• In what quantities do our customers purchase our products and services?

• At what times of the year, month, or day do our customers make purchases?

• Which combinations of products and services do our customers purchase together?

• Which of our discount promotions result in the most customer visits and purchases?

• Where do our customers live?

• What are our customers’ ages?

• Are our customers married?

• Do our customers have children, and if so, how many and of what ages?

• What are our customers’ professional and personal interests?

• Are our customers retired?

• What products and services do our customers purchase from other organizations?

In order to record data about your customers’ purchasing behaviors, you determine that your sales receipt system needs to be modified slightly. To gather demographic data about your customers, you decide to offer a one-time 5 percent discount off a future purchase if a customer fills out a brief survey and turns the completed survey in at the point of purchase. You decide also to combine this survey data with personal information collected from your customers’ credit applications.

Note

If you collect your customers’ demographic or personal data without their consent, you could expose your organization to unwanted legal actions. Be certain that you understand any legal issues that affect customers’ rights to privacy before you begin collecting their demographic or personal data. If you do collect customers’ demographic or personal data, a good practice is to allow customers to opt in (choose to give you their data). This approach is preferable to making customers opt out (tell you to stop collecting their data).

Recording Data

The way that your organization records data facts is just as important as the data itself. Some approaches you can take to record data facts include

• Automated data capture devices such as turnstile counters, infrared beam interruptions, or other automated machinery.

• Observational data capture by means of people taking physical counts with tally sheets or hand clickers or manually reading scales or measures and recording the results.

• Transactional data capture at cash registers, bank teller windows, or other online transaction processing (OLTP) systems.

• Voluntary data submission through questionnaires, surveys, or application forms.

At a small retail store, for instance, you could take just a few simple actions to collect data to improve sales:

• Install an infrared beam at the store’s entrance. Whenever the beam is interrupted, a counter is triggered. This measures customer traffic, which you can then compare to the number of sales transactions on a specific day. You can determine how many of your customers are just browsing versus how many are purchasing your products.

• Place a simple tally sheet at each telephone. Divide the tally sheet into one-hour time blocks, and divide each hourly block into subject blocks to record whether a caller inquired about store hours, directions to the store, whether a particular product was in stock, whether an order was still on layaway for that customer, and so on. Every time a customer calls on the telephone, the employee answering marks the tally sheet in the correct subject block and time block. This information will measure how many customers called, during which hours of the day they called, and for what reasons. Analyzing this information, you could determine whether you should add answers to frequently asked questions to your organization’s automated telephone greeting (your hours and location, for example), whether you need to hire more salesclerks for certain days of the week or times of the day to handle phone calls, and so on.

• Make sure your cash registers record not only product names, quantities, and sales prices for each transaction, but also the date and time of the sale, payment method, the salesclerk’s ID, any promotion codes, and any available customer information such as ZIP code, phone number, address, and so on. If you can’t modify your current sales receipt system, consider purchasing or upgrading to a new system.

Troubleshooting Data Compatibility Issues

Perhaps your organization has data stored in a format that’s not compatible with a particular Microsoft data analysis software application. Or maybe your data is stored in a format created with a Microsoft application that was not designed primarily for data analysis, such as PowerPoint. The following table provides some possible solutions to foster data analysis in these situations.

Problem	Solutions
The data is stored in a non-Microsoft electronic data file or database, such as on a mainframe computer.	To analyze these types of data with the techniques described in this book, you should export the data into text files, Excel spreadsheets, Access databases, or SQL Server databases. Consult the documentation for your specific software application or database to see whether exporting your data to one of these formats is supported.
The data is “ragged” (one or more of the records are missing values in one or more of their fields), which may lead to unexpected data analysis results.	Depending on the values allowed in the data file or database, use values such as 0 (zero), NULL, EMPTY, NONE, or N/A in each field with a missing value.
Unrelated data records are stored on the same electronic spreadsheet, which makes the data hard to manage.	Consolidate groups of related data records in separate spreadsheets. You can store multiple Excel spreadsheets (also called worksheets) in a single file, called a workbook, for organizational purposes.
You want to use the features of Microsoft Data Analyzer to analyze non-OLAP data.	Use the tools included with Excel to convert the non-OLAP data into an offline cube file, or use Microsoft SQL Server Data Transformation Services to import the data into SQL Server database format. Then use the tools included with Microsoft SQL Server 2000 Analysis Services to convert the data into OLAP cubes. See the specific product’s documentation for instructions on how to do this.