How data analysis works

how to make data analysis report and how to data analysis in excel 2007 and what is data analysis and types
HalfoedGibbs Profile Pic
HalfoedGibbs,United Kingdom,Professional
Published Date:02-08-2017
Your Website URL(Optional)
Comment
1 introduction to data analysis Break it down I detect ginger, garlic, paprika, and perhaps a hint of fish sauce… Data is everywhere. Nowadays, everyone has to deal with mounds of data, whether they call themselves “data analysts” or not. But people who possess a toolbox of data analysis skills have a massive edge on everyone else, because they understand what to do with all that stuff. They know how to translate raw numbers into intelligence that drives real-world action. They know how to break down and structure complex problems and data sets to get right to the heart of the problems in their business. this is a new chapter 1Do you see a pattern in Acme’s expenses? How do their gross sales figures compare to their target sales figures? sales are off Acme Cosmetics needs your help It’s your first day on the job as a data analyst, and you were just sent this sales data from the CEO to review. The data describes sales of Acme’s flagship moisturizer, MoisturePlus. September October November December January February Gross sales 5,280,000 5,501,000 5,469,000 5,480,000 5,533,000 5,554,000 Target sales 5,280,000 5,500,000 5,729,000 5,968,000 6,217,000 6,476,000 Ad costs 1,056,000 950,400 739,200 528,000 316,800 316,800 Social network costs 0 105,600 316,800 528,000 739,200 739,200 Unit prices (per oz.) 2.00 2.00 2.00 1.90 1.90 1.90 What do you think is going on with these unit prices? Why are they going down? Take a look at the data. It’s fine not to know everything—just slow down and take a look. What do you see? How much does the table tell you about Acme’s business? About Acme’s MoisturePlus moisturizer? Good data analysts always want to see the data. 2 Chapter 1 What has been happening during the last six months with sales?Now what could he mean by this? introduction to data analysis The CEO wants data analysis to help increase sales He wants you to “give him an analysis.” It’s kind of a vague request, isn’t it? It sounds simple, but will your job be that straightforward? Sure, he wants more sales. Sure, he thinks something in the data will help accomplish that goal. But what, and how? Welcome to the team. Take a look at our data and give me an analysis to help us figure out how to increase sales. Looking forward to your conclusions. Think about what, fundamentally, the CEO is looking for from you with this question. When you analyze data, what are you doing? you are here 4 3 Here’s the CEO.You might bet that she knows Excel, but that’s about it Define your problem. Finally, you put it all back together and make (or recommend) a decision. the steps of analysis Data analysis is careful thinking about evidence The expression “data analysis” covers a lot of different activities and a lot of different skills. If someone tells you that she’s a data analyst, you still won’t know much about what specifically she knows or does. But all good analysts, regardless of their skills or goals, go through this same basic process during the course of their work, always using empirical evidence to think carefully about problems. Define Disassemble Evaluate Decide Data analysis is all about breaking problems and data into smaller pieces In every chapter of this book, you’ll go through these steps over and over again, and they’ll become second nature really quickly. Ultimately, all data analysis is designed to lead to better decisions, and you’re about to learn how to make better decisions by gleaning insights from a sea of data. 4 Chapter 1 Here’s the meat of the analysis, where you draw your conclusions about what you’ve learned in the first two steps. Knowing your problem is the very first step.Mission accomplished See the similarity? introduction to data analysis Define the problem Doing data analysis without explicitly defining your problem or goal is like heading out on a road trip without having decided on a destination. Sure, you might come across some interesting Map Map sights, and sometimes you might want to wander around in the hopes you’ll stumble on something cool, but who’s to say you’ll find anything? Road trip with a Road trip without a destination. destination. Ever seen an “analytical report” that’s a million pages long, with tons and tons of charts and diagrams? Every once in a while, an analyst really does need a ream of paper or an hour- long slide show to make a point. But in this sort of case, the analyst often hasn’t focused enough on his problem and is pelting you with information as a way of ducking his obligation to solve a problem and recommend a decision. Sometimes, the situation is even worse: the problem isn’t defined at all and the analyst doesn’t want you to realize that he’s just wandering around in the data. How do you define your problem? you are here 4 5 Here’s a gigantic analytical report. Who knows where you’ll end up?The better you understand your client, the more likely your analysis will be able to help. what are you looking for? Your client will help you define your problem There’s a bonus in it for you if you can figure He is the person your analysis is meant to out how to increase serve. Your client might be your boss, your MoisturePlus sales. company’s CEO, or even yourself. Your client is the person who will make decisions on the basis of your analysis. You need to get as much information as you can from him to define your problem. The CEO here wants more sales. But that’s only the beginning of an answer. You need to understand more specifically what he means in order to craft an analysis that solves the problem. CEO of Acme Cosmetics Your client might be:ƒ well or badly informed about his business ƒ well or badly informed about his dataƒ focused or indecisive ƒ well or badly informed ƒ clear or vague about his problems or ƒ intuitive or analytic goals Define Disassemble Evaluate Decide 6 Chapter 1 This is your client, the guy you’re working for. Keep an eye at the bottom of the page during this chapter for these cues, which show you where you are. It’s a really good idea to know your client as well as you can.introduction to data analysis Q: I always like wandering around in data. Do you mean that A: Of course I need to have some specific goal in mind before I even look at my data? Q: Sounds to me like that kind of person needs professional help. A: You don’t need to have a problem in mind just to look at data. But keep in mind that looking by itself is not yet data analysis. Data A: Actually, good data analysts help their clients think through their analysis is all about identifying problems and then solving them. problem; they don’t just wait around for their clients to tell them what to do. Your clients will really appreciate it if you can show them that Q: I’ve heard about “exploratory data analysis,” where you they have problems they didn’t even know about. explore the data for ideas you might want to evaluate further. There’s no problem definition in that sort of data analysis Q: That sounds silly. Who wants more problems? A: Sure there is. Your problem in exploratory data analysis is to A: People who hire data analysts recognize that people with find hypotheses worth testing. That’s totally a concrete problem to analytical skills have the ability to improve their businesses. Some solve. people see problems as opportunities, and data analysts who show their clients how to exploit opportunity give them a competitive Q: Fine. Tell me more about these clients who aren’t well advantage. informed about their problems. Does that kind of person even need a data analyst? The general problem is that we need to increase sales. What questions would you ask the CEO to understand better what he means specifically? List five. 1 2 3 4 5 you are here 4 7Your questions might be different. Always ask “how much.” Make your goals and beliefs quantitative. Anticipate what your client thinks about. He’s definitely going to be concerned with competitors. response from the client Acme’s CEO has some feedback for you This email just came through in response to your questions. Lots of intelligence here… Define Disassemble Evaluate Decide 8 Chapter 1 From: CEO, Acme Cosmetics Here are some sample questions To: Head First to get the CEO to define Subject: Re: Define the problem your analytical goals. By how much do you want to increase sales? I need to get it back in line with our target sales, which you can see on the table. All our budgeting is built around those targets, and we’ll be in trouble if we miss them. How do you think we’ll do it? Well, that’s your job to figure out. But the strategy is going to involve getting people to buy more, and by “people” I mean tween girls (age 11–15). You’re going to get sales up with marketing of some sort or another. You’re the data person. Figure it out How much of a sales increase do you think is feasible? Are the target sales figures reasonable? These tween girls have deep pockets. Babysitting money, parents, and so on. I don’t think there’s any limit to what we can make off of selling them MoisturePlus. How are our competitors’ sales? I don’t have any hard numbers, but my impression is that they are going to leave us in the dust. I’d say they’re 50–100 percent ahead of us in terms of gross moisturizer revenue. What’s the deal with the ads and the social networking marketing budget? We’re trying something new. The total budget is 20 percent of our first month’s revenue. All of that used to go to ads, but we’re shifting it over to social networking. I shudder to think what’d be happening if we’d kept ads at the same level. See something curious in the numbers? Ask about itThese might be the chunks you need to watch. introduction to data analysis Break the problem and data into smaller pieces The next step in data analysis is to take what you’ve learned about your problem from your client, along with your data, and break that information down into the level of granularity that will best serve your analysis. Define Disassemble Evaluate Decide Divide the problem into smaller problems You need to divide your problem into manageable, solvable chunks. Often, your problem will be vague, like this: “What do our best customers want from us?” “How do we increase sales?” “What promotions are most likely to work?” “How is our advertising doing?” You can’t answer the big problem directly. But by answering the smaller problems, which you’ve analyzed out of the big problem, you can get your answer to the big one. September October November December January February Gross sales 5,280,000 5,501,000 5,469,000 5,480,000 5,533,000 5,554,000 Divide the data into smaller chunks Target sales 5,280,000 5,500,000 5,729,000 5,968,000 6,217,000 6,476,000 Ad costs 1,056,000 950,400 739,200 528,000 316,800 316,800 Same deal with the data. People aren’t going to Social network costs 0 105,600 316,800 528,000 739,200 739,200 present you the precise quantitative answers you Unit prices (per oz.) 2.00 2.00 2.00 1.90 1.90 1.90 need; you’ll need to extract important elements on your own. December Target Sales 5,968,000 If the data you receive is a summary, like what you’ve received from Acme, you’ll want to know November Unit Prices 2.00 which elements are most important to you. If your data comes in a raw form, you’ll want to Let’s give disassembling summarize the elements to make that data more useful. a shot… you are here 4 9 Answer the smaller problems to solve the bigger one. More on these buzzwords in a momentHow do January’s gross sales compare to February’s? How are ad and social network costs changing relative to each other over time? revisit the data Now take another look at what you know Break down your summary Let’s start with the data. Here you have a data by searching for summary of Acme’s sales data, and the best way to start trying to isolate the most interesting comparisons. important elements of it is to find strong comparisons. September October November December January February Gross sales 5,280,000 5,501,000 5,469,000 5,480,000 5,533,000 5,554,000 Target sales 5,280,000 5,500,000 5,729,000 5,968,000 6,217,000 6,476,000 Ad costs 1,056,000 950,400 739,200 528,000 316,800 316,800 Social network costs 0 105,600 316,800 528,000 739,200 739,200 Unit prices (per oz.) 2.00 2.00 2.00 1.90 1.90 1.90 Making good comparisons is at the core of data analysis, and you’ll be doing it throughout this book. In this case, you want to build a conception in your mind of how Acme’s MoisturePlus business works by comparing their summary statistics. Define Disassemble Evaluate Decide 10 Chapter 1 How do the gross and target sales figures compare to each other for October? Does the decrease in unit prices coincide with any change in gross sales?This commentary is itself a kind of data. Which parts of it are most important? introduction to data analysis You’ve defined the problem: figure out how to increase sales. But that problem tells you very little about how you’re expected to do it, so you elicited a lot of useful commentary from the CEO. This commentary provides an important baseline set of assumptions about how the cosmetics business works. Hopefully, the CEO is right about those assumptions, because they will be the backbone of your analysis What are the most important points that the CEO makes? Summarize what your client believes and your thoughts on the data you’ve received to do the analysis. Analyze the above email and your data into smaller pieces that describe your situation. Your client’s beliefs. Your thoughts on the data. 1 1 2 2 3 3 4 4 you are here 4 11 Here’s the “how” question. From: CEO, Acme Cosmetics To: Head First Subject: Re: Define the problem By how much do you want to increase sales? I need to get it back in line with our target sales, which you can see on the table. All our budgeting is built around those targets, and we’ll be in trouble if we miss them. How do you think we’ll do it? Well, that’s your job to figure out. But the strategy is going to involve getting people to buy more, and by “people” I mean tween girls (age 11–15). You’re going to get sales up with marketing of some sort or another. You’re the data person. Figure it out How much of a sales increase do you think is feasible? Are the target sales figures reasonable? These tween girls have deep pockets. Babysitting money, parents, and so on. I don’t think there’s any limit to what we can make off of selling them MoisturePlus. How are our competitors’ sales? I don’t have any hard numbers, but my impression is that they are going to leave us in the dust. I’d say they’re 50–100 percent ahead of us in terms of gross moisturizer revenue. What’s the deal with the ads and the social networking marketing budget? We’re trying something new. The total budget is 20 percent of our first month’s revenue. All of that used to go to ads, but we’re shifting it over to social networking. I shudder to think what’d be happening if we’d kept ads at the same level. What’s most useful?This could be worth remembering. Now it’s time to evaluate those pieces in greater detail… Good… this is the sort of thing one does nowadays. analyze the client You just took an inventory of your and your client’s beliefs. What w did you find? Your client’s beliefs. MoisturePlus customers are tween girls (where tweens are people aged 11–15). They’re basically the 1 only customer group. Acme is trying out reallocating expenses from advertisements to social networking, but so far, the 2 success of the initiative is unknown. We see no limit to potential sales growth among tween girls. 3 Acme’s competitors are extremely dangerous. 4 Your thoughts on the data. Sales are slightly up in February compared to September, but kind of flat. 1 Sales are way off their targets and began diverging in November. 2 Cutting ad expenses may have hurt Acme’s ability to keep pace with sales targets. 3 Cutting the prices does not seem to have helped sales keep pace with targets. 4 Define Disassemble Evaluate Decide 12 Chapter 1 What should they do next? Your own answers might be slightly different. Big problem You’ve successfully broken your problem into smaller, more manageable pieces.What do you see? introduction to data analysis Evaluate the pieces Here comes the fun part. You know what you need to figure out, and you know what chunks of data will enable you to do it. Now, take a close, focused look at the pieces and form your own judgements about them. Define Disassemble Evaluate Decide Just as it was with disassembly, the key to evaluating the pieces you have isolated is comparison. What do you see when you compare these elements to each other? Observations about the problem Observations about the data Sales are slightly up in February MoisturePlus customers are tween girls (where compared to September, but kind of flat. tweens are people aged 11–15). They’re basically the only customer group. Sales are way off their targets. Acme is trying out reallocating expenses from advertisements to social networking, but so far, Cutting ad expenses may have hurt Acme’s the success of the initiative is unknown. ability to keep pace with sales targets. We see no limit to potential sales Cutting the prices does not seem to have growth among tween girls. helped sales keep pace with targets. Acme’s competitors are extremely dangerous. You have almost all the right pieces, but one important piece is missing… you are here 4 13 Use your imagination Pick any two elements and read them next to each other.Yikes You don’t want to run into these problems. you are your analysis Analysis begins when you insert yourself Inserting yourself into your analysis means making your own assumptions explicit and betting your credibility on your conclusions. Whether you’re building complex models or making simple decisions, data analysis is all about you: your beliefs, your judgement, your credibility. Insert yourself Good for you Good for your clients You'll know what to look for in the data. Your client will respect your judgments more. You'll avoid overreaching in your conclusions. Your client will understand the limitations of your conclusions. You'll be responsible for the success of your work. Don’t insert yourself Bad for you Bad for your client You’ll lose track of how your baseline Your client won’t trust your analysis, assumptions affect your conclusions. because he won’t know your motives and incentives. You’ll be a wimp who avoids responsibility Your client might get a false sense of “objectivity” or detached rationality. As you craft your final report, be sure to refer to yourself, so that your client knows where your conclusions are coming from. Define Disassemble Evaluate Decide 14 Chapter 1 Your prospects for success are much better if you are an explicit part of your analysis.introduction to data analysis Make a recommendation As a data analyst, your job is to empower yourself and your client to make better decisions, using insights gleaned from carefully studying your evaluation of the data. Define Disassemble Evaluate Decide Making that happen means you have to package your ideas and judgments together into a format that can be digested by your client. That means making your work as simple as it can be, but not simpler It’s your job to make sure your voice is heard and that people make good decisions on the basis of what you have to say. The report you present to your client needs to be focused on making yourself understood and encouraging intelligent, data-based decision making. Look at the information you’ve collected on the previous pages. What do you recommend that Acme does to increase sales? Why? you are here 4 15 An analysis is useless unless it’s assembled into a form that facilitates decisions. It’s a good idea to state your and your clients’ assumptions in your report. A simple graphic to illustrate your conclusion. Here’s the meat of your analysis. present your findings Your report is ready Acme Cosmetics Analytical Report Context MoisturePlus customers are tween girls (where tweens are people aged 11–15). They’re basically the only customer group. Acme is trying out reallocating expenses from advertisements to social networking, but so far, the success of the initiative is unknown. We see no limit to potential sales growth among tween girls. Acme’s competitors are extremely dangerous. Interpretation of data Sales are slightly up in February compared to September, but kind of flat. Sales are way off their Ad vs. targets. Cutting ad expenses social may have hurt Acme’s ability to keep pace with sales targets. Cutting the prices does not seem to S O N D J F have helped sales keep pace with targets. Recommendation Your conclusion might be It might be that the decline in sales relative to the target is different. linked to the decline in advertising relative to past advertising expenses. We have no good evidence to believe that social networking has been as successful as we had hoped. I will return advertising to September levels to see if the tween girls respond. Advertising to tween girls is the way to get gross sales back in line with targets. What will the CEO think? Define Disassemble Evaluate Decide 16 Chapter 1 This is the stuff we got from the CEO at the beginning. Actual Targetintroduction to data analysis The CEO likes your work Excellent work. I’m totally persuaded. I’ll execute the order for more ads at once. I can’t wait to see what happens Your report is concise, professional, and direct. It speaks to the CEO’s needs in a way that’s even clearer than his own way of describing them. You looked at the data, got greater clarity from the CEO, compared his beliefs to your own interpretation of his data, and recommended a decision. Nice work How will your recommendation affect Acme’s business? Will Acme’s sales increase? you are here 4 17Seems like a nice article, on the face of it. Dataville Business Daily The Acme CEO was space, Competition MoisturePlus surprised when our Cosmetics, responded to achieves complete reporter told him of our reporter's inquiry saying, our findings. "We are "We have basically given up market saturation commited to providing on marketing to tween girls. among tween girls our tween customers the The customers that we most luxurious cosmetic recruit for viral marketing Our very own cosmetics experience possible at just- are made fun of by their industry analysts report accessible prices," he said. friends for allegedly using that the tween girl "I'm delighted to hear that a cheap, inferior product. moisturizer market is MoisturePlus has achieved The MoisturePlus brand is completely dominated by so much success with them. so powerful that it's a waste Acme Cosmetics's flagship Hopefully, our analytical of our marketing dollars product, MoisturePlus. department will be able to compete. With any luck, According to the DBD's to deliver this information the MoisturePlus brand survey, 95 percent of tween to me in the future, rather will take a hit if something girls report "Very Frequent" than the press." happens like their celebrity usage of MoisturePlus, endorsement getting caught typically twice a day or Acme's only viable on video having… more. competitor in this market surprise information An article just came across the wire On the face of it, this sounds good for Acme. But if the market’s saturated, more ads to tween girls probably won’t do much good. 18 Chapter 1 What does this mean for your analysis?introduction to data analysis You’re lucky I got this call. I canceled the tween girl ad campaign. Now come back to me with a plan that works. It’s hard to imagine the tween girl campaign would have worked. If the overwhelming majority of them are using MoisturePlus two or more times a day, what opportunity is there for increasing sales? You’ll need to find other opportunities for sales growth. But first, you need to get a handle on what just happened to your analysis. Somewhere along the way, you picked up some bad or incomplete information that left you blind to these facts about tween girls. What was that information? you are here 4 19analysis with bad models You let the CEO’s beliefs take you down the wrong path Here’s what the CEO said about how MoisturePlus sales works: The CEO’s beliefs about MoisturePlus MoisturePlus customers are tween girls (where tweens are people aged 11–15). They’re basically the only customer group. Acme is trying out reallocating expenses from advertisements to social networking, but so far, the success of the initiative is unknown. We see no limit to potential sales growth among tween girls. Acme’s competitors are extremely dangerous. Take a look at how these beliefs fit with the data. Do the two agree or conflict? Do they describe different things? September October November December January February Gross sales 5,280,000 5,501,000 5,469,000 5,480,000 5,533,000 5,554,000 Target sales 5,280,000 5,500,000 5,729,000 5,968,000 6,217,000 6,476,000 Ad costs 1,056,000 950,400 739,200 528,000 316,800 316,800 Social network costs 0 105,600 316,800 528,000 739,200 739,200 Unit prices (per oz.) 2.00 2.00 2.00 1.90 1.90 1.90 The data doesn’t say anything about tween girls. He assumes that tween girls are the only buyers and that tween girls have the ability to purchase more MoisturePlus. Define Disassemble Evaluate Decide 20 Chapter 1 This is a mental model… We’re back to the beginning In light of the news article, you might want to reassess these beliefs.