Small Business and Startup Tips: Analyze This (a Data Primer) Mike | May 21st, 2012
Our awesome Customer Service Master, Bianca, came to me last week with a simple request: “Let me have more responsibility.” Well it’s not everyday that a member of the team asks for the opportunity to do more, and it’s not every person in the world who expresses interest in learning new skills. We had a short conversation about ways she could contribute more and about skills she’d like to develop and one of the areas we discussed was, hold on to your hats, data analysis. That’s right – it turns out that Bianca likes numbers and that she’d like to help us better plan our customer support based on a deeper appreciation of the underlying data. Yikes, a geek.
I have written several posts in the last couple of years about small business and the importance of numbers and of gaining a deeper knowledge of and ability to work with data – from financial modeling to basic analysis it is critical that you be able to use a spreadsheet program such as Excel, Numbers, or Google Spreadsheets, and be able to build a simple analytical model. I thought that if I can create a one-page lesson plan for Bianca to use as she moves up the learning curve, it might turn out to be helpful to others as well.
To start the lesson we’ll need a modest set of data to work with – nothing too overwhelming for the first time out the gate, just a couple of hundred data points to use for the exercise. Bianca and I had discussed an opportunity for her to start practicing: last month we switched our phone support software to a new provider and we now have about 5 week’s worth of data to look at. By the way, our new phone software is really cool: IfByPhone is a SaaS solution that allows us to set up phone support schedules, automatically route calls to multiple phone agents, receive and record voicemails, forward calls as needed, and give callers a variety of outgoing messages dependent on the specific number they dialed as well as day, time, etc. IfByPhone integrates with Zendesk, our help desk software, and works with Skype, allowing our folks to provide high quality phone support from anywhere, as long as they have laptop, a headset and an internet connection. Nice.
But back to the lesson: I have created a file of this data that you can use to work through the tutorial with Bianca and you can download it here. The data set is a pretty simple one, but for Bianca’s purposes it should serve well; 6 columns of data are included: DATE, ACTIVITY (or what the system did when a call came in), CALLER ID (we’ve changed these to randomly generated phone numbers to protect our user’s privacy), CALL DURATION, SYSTEM MINUTES (this is the time a caller spends on hold or listening to an outbound message), and TALK MINUTES (the amount of time the caller actually spends on the phone with an agent). The 7 steps below are by no means the final word on spreadsheet analysis, but this is a good start to get you working with your own data sets and start you down the road to more meaningful analytical exercises.
For the purpose of this lesson, I will assume that everyone is using Excel and will use terms and features specific to that software. But, many of these features are available in other spreadsheet software and you should be able to figure it out if you are using a different program.
1. Get your ducks lined up.
OK, Bianca, first things first: open a new, completely blank spreadsheet and save it with a title that works for you. Next, download the data, open it in a text file, and copy/paste it into a tab in your spreadsheet. The file we are using is a .csv file and you can also open it directly in Excel or import it into the sheet – just click on the ‘File’ menu, choose ‘Import’ and follow the simple steps. In the first window of the Text Import Wizard choose CSV file then select your file from the folder where you downloaded it. Excel will then guide you through a series of simple steps to import the raw data into rows and columns in your sheet.
2. Organize your work.
You’ll see that our data has a header row with labels for each column of data (the 6 I listed above) ad well as 325 rows of the actual raw data. Each row represents one call received by our system between April 10th and May 17th. At this point we want to make sure the data is in the proper formats and easy to work with. We start by telling Excel exactly what type of data is included in each of the columns; start by clicking the letter ‘A’ at the top of the sheet to select the entire first column. Use the key-combination Command-1 to open the formatting menu, then select date from the list of categories on the left. You will see a list of choices to format the dates – select the one that displays as “3/14/01 13:30″ and click OK. Repeat the process with columns D, E, and F but this time select “Number” in the Format/Category list. Set decimal places to 1, check the box labeled “Use 1000 Separator (,)” and click OK. We don’t need to bother with columns B and C as these are already formatted correctly for this exercise. Save your work and off we go.
3. Lay it out.
To make it easier to work with our spreadsheet its a good idea to take a couple of simple formatting steps. First let’s deal with the header row which are the labels for our 6 columns. Highlight row 1 in the sheet, and in the formatting menu (command-1, remember?) select “Font,” and set the font to Bold; then select “Alignment” and under the pulldown labelled “Horizontal” choose Center. Click OK and your header row looks nice and stand out, right? But now we want to “freeze” the header row so that it stays visible even when you scroll down through the sheet. Place you cursor in Cell A2 and from the top menus pull down the ‘Window’ and choose ‘Freeze panes.’ You will see a thin gray line appear below Row 1. Try scrolling down through the rows and you will see that the header stays locked and visible at the top of the sheet.
4. Ask your questions.
The most important part of business analysis is figuring out exactly what you want the raw data to tell you. I’ve created a short list questions that we answer using our data and you can come up with others on your own. For this lesson we’re going to ask the following three questions and I will show you how to find the answers using Excel’s tools, features, and formulas.
- How many calls did each agent receive and how many went to voicemail?
- Which were the busiest days of the week for incoming calls?
- Which customer made the most calls to us in the 5 weeks for which we have data?
5. Determine what you don’t have.
Based on the questions above there may be some information not contained in the data that we’ll need to add to the spreadsheet. For instance, question 2 asks what is the busiest day of the week for incoming calls; to answer the question we need to create a new column which tells us the day of the week for each row of data. We will set up column G by adding a label at the top (cell G1), “Weekday.” Now we need to add a formula to the cells below, to display the day for each row of data. The Excel formula for displaying the day is pretty simple; first you want to tell the system that you wish to display the day as text and then you simply need to tell it how to format that text. in our case, the formula we will use (first in cell G2) is “=text(weekday(A2),”dddd”).” When you paste this into the cell and hit return you will see that our first row of data was created on a Tuesday! Now copy the formula down the rest of the rows in column G and you will see a day appear for each data point. You can create other specific date-related information with a number of Excel formulas, such as to display the month, year, or even the hour of a 24-hour day for use in answering other questions you may come up with.
6. Sort it out.
We can start the simplest analysis by just sorting the columns. Let’s try first to sort using the information in column B, “Activity.” Click the ‘Data’ menu and select ‘Sort’ at the very top of the list; the Sort window will open and all you have to do is tell Excel which data you want to sort the sheet with; click “Sort by” on the left and choose ACTIVITY, click OK and your data will now be sorted alphabetically based on the type of activity in the system. You can repeat this process using any of the columns or you can add additional columns and sort using those.
7. Ready? Set? Pivot.
Bianca, Excel contains a very powerful feature, without which it would be impossible for us to do meaningful (and fast) data analysis: the “PivotTable” report. A PivotTable allows you to select a range or sheet of data and look at that information in different ways with just a few mouse clicks. Creating your first PivotTable can be a little intimidating, but hang with me and I’ll show you how to do it. There is invariably some trial and error in the process, but it is really cool once you have one set up and can start getting the results. As a matter of fact we are going to use one PivotTable to answer all three of our questions from above.
A PivotTable is it’s a self-contained chart divided into columns and rows that you define and that you can easily change. To start, let’s select the entire sheet we’ve created by clicking and dragging starting with column A and going all the way to column G; you should have highlighted all 7 columns of data. Next click the Data menu and select PivotTable from the list; a window will appear asking you to define the data for your PivotTable – you’ll see that “Use a table or a range in the workbook” is already selected and columns A:G are shown in the “Location” field; click OK and continue to the next step.
A cool-looking window will appear called the “PivotTable Builder” (see the screenshot at top of this page); this is the tool where customize the information you want included in your PivotTable. You’ll also see that an empty table has appeared on a new sheet of your workbook with sections titled Column Area, Row Area, and Values Area; this is your shiny new and un-populated PivotTable and this is where you will see your answers start to magically appear as we go through the following steps.
Let’s use our first question to populate the table, “How many calls did each agent receive and how many went to voicemail?” You’ll see in the Builder a list of “Field names” – these are the column-heading titles for each of our seven columns; start by checking the box next to “ACTIVITY” in the list and you will see that it now appears in the section below called “Row Labels.” Next we will define the “Values” for each Activity, i.e. the number of times each Activity was listed in our sheet. This time, click and hold the Label “ACTIVITY” from the Field name list and drag it down into the Builder section labelled “Values.” You will see it appears there with the title “Count of ACTIVITY” and you’ll see a number appear in the Values Area of your new table which is the actual count for each of the listed Activities. Look at the table and you can see that it is sorted alphabetically by Activity; you can sort it by the Total column using the sort tool. If we choose to sort by total, we can see that in the 5-week period, Audree was assigned 96 calls, Bianca received 92 calls, 51 went to phone-hours voicemail, etc. right down to the 2 calls that were transferred to me!
Question 2 was “Which were the busiest days of the week for incoming calls?” To answer this we just need to delete ACTIVITY by dragging it out of the Row Labels and Values in the Builder and replacing it with “Day” in the Row labels and Values sections. You’ll see that your PivotTable has magically changed and now the rows are the days of the week and the number of incoming calls received for each day is displayed in the Total column. Looks like Wednesday is the busiest day of the week for calls thus far, with Monday and Tuesday not far behind.
Finally for Question 3, “Which customer made the most calls to us in the 5 weeks for which we have data?” we will replace Day with CALLER ID and then sort the data to see from which number we received the most calls. Quite a few Caller IDs show up 3 times during the period, but (fortunately) we do not appear to have any phone stalkers. Yet.
So, that’s it for the first lesson; you can use your PivotTable to answer other questions by selecting other fields from the list: for instance which caller kept us on the phone for longest? Which agent has the shortest average time on a call? Or, how long was the average hold time for a caller? Take some time to play around with the Builder to understand all of it’s features including how to display different totals, by clicking the little i in the Values area and changing Count to, for instance, Average to see how the display changes. Have fun, Bianca!