I got one task to ‘systemize’ the following process: upon conducting customer satisfaction surveys on periodic basis, survey data is being downloaded into Excel format and analyzed. Results are copied to separate PowerPoint files and dispatched to the relevant department heads.
Usually, when Information Workers (IWs) sense that something can be done on the process or data to make it faster, consistent, or eliminate redundant manual work, they turn to IT to help in ‘systemizing the process’.
So how could we help in such requests?
First, it is important to understand the business unit (BU) main pains in the way they do prioritize them. For example, from IT perspective first concern that would hit our heads is: do you have centralized repository for data, i.e. are we keeping our data in a centralized database? Whereas the business priority may be to have the data ready for end users (department heads) in the minimal time and least manipulation required from IW’s end. Moreover, end users need means to interact with data:: to query, filter, and drill throw it, so that they could reach sound conclusions. This is the concept of business intelligence.
As IT professionals, we need to curb our desire to throw away what we think is a simple tool and bring in a more complex full-fledged tool. Excel is the most important data tool ever, because it is simply the most intuitive one for dealing with data. So, let us keep what we have and try to utilize the set of tools and skills we have.
With that understanding I took the following steps to take the input of one Excel.
- Transform the data from semi-structured format into a structured format. We need to set our Excel file into a format that supports the following criteria: If you applied different filters on different columns can you easily slice-and-dice data so that you get straight forward answers to questions about your desired key metrics through the different dimensions (segmentation of data)? For example, can you get how many respondents rated 5 on one specific criteria (this is the metric) related to a specific business department, in a specific month/year, for a specific product (those are the dimensions)? If your answer is yes, then your do have a structured dataset. Otherwise, you need to work on your data structure.
- Automate the computation of results. The result of step 1 above is that no more multiple sheets for each dimension (one sheet for each month, or year, or product). We rather have all this in one table in one sheet. But how do we then generate correct analysis results specific to desired dimensions? One simple way is to consider the first Excel workbook as the data source and have another one with sufficient formulas to read from the data source file and compute result. I formatted the latter file as follows:
Year // Month // .. (other dimentsions) // Rating Criteria // Rating 1// Rating 2//….
For the ratings formula I made use of the Indirect, and CountIf formulas to read from the source file and generate corresponding calculations.
- BI Dashboard. The butter! While there are different alternatives to go about this step, such as Excel charts that can be shared on SharePoint based on the corresponding target audience sites. However, at that time we got one specialized tool for this in my company: Tableau Software for fast data analytics. It is extremely easy to link to Excel data sources (among many other data source types), drag dimensions and produce visually appealing charts that makes it easy to dice and slice through dimensions to analyze measures, and sort this all out in one neat and nice dashboard that can be published to Tableau Server with customized group access roles.
Time to sit back and enjoy amusement signs on users’ faces!