Table of Contents

• Survey data analysis by pivot table in Excel method step by step

I have discussed the relationship between statistics  and marketing. On this occasion, I will explain step by step about tool to analyze customer survey with a pivot table and pivot chart. There are many phases are carried out on marketing activities, ranging from making a business plan to the execution stage.  In this article, I will discuss the phase of marketing activity after you released your product advertising in various media.

Typically, after your ad has been spread in the mass media, you want to know how the effectiveness of advertising and its influence to sales. If you want to know whether the ad is good and effective in order to educate the public or how much influence on people's desire to buy your product, you must create a questionnaire or survey to the people.

From the results of the survey, you can analyze further and can make the decision whether the ad that you distribute to the community it worth continuing or not. Now I will give you an example a straightforward analysis of the simple data. These data are only examples.  And tools to analyze and make a summary of the survey is a pivot table and chart contained in Microsoft Excel.

I made an assumption that you have obtained the data, and data that you've put it in Microsoft Excel. An example is in the picture below:

Survey data analysis by pivot table in Excel method step by step

1. The first step, you must convert the data to the form of pivot tables. It's easy (I assume you are using Microsoft access 2007), just click the insert tab, and select the pivot table. Then you have to select the data range and click OK. For the sake of easiness to understand, you should see a picture shown in the image below.

2. Put the answer field in the area of ​​column labels and question  field in row labels. Put answer field in the values area also ​​, change the custom name into percentage of answer rating, and summarize value field by Count. Then you have to change the show values ​​as % of row. Observe the example as shown in the image below.

 3. After the above steps are completed, you will see the results of the percent people who answered "yes", how many percentage people answered "no", and so on. You can see the images below for more details.

4. Or you can also see the number calculations. An example is shown below.

5. Now is the time you make a report in chart form. Decision-makers usually prefer to see the report in chart form so that they can infer a decision correctly and easily. You can choose the type of chart you want which is tailored to the information requested by decision makers. In this case, I chose the bar chart. You can see an example below.

Recapitulation of survey data based on chart report

In the chart above, we will draw conclusion that from a number of respondents who had been asked, as many as 60% are familiar with your product. What does it mean? It means that:

1. Your advertising program run successfully because so many people have known your new product. 2. As many as 60% of people already know the features of your product. 

3. It indicates that the message which is delivered in your ad has been well received by potential customers. 

4. You also have succeed to educate your new products to prospective customers. 

Meanwhile, when asked, "Do you want to buy this product now?",  50% answered  "yes". It could be:

1. This value is smaller than the previous question, so you can draw the conclusion that you have performed a good product advertising, but a powerful sales force is required to improve conversions. 

2. You must implement innovative ways to increase sales. 

With these simple statistics, you can conclude that you should focus on sales and strengthen your sales force to increase sales.

Conclusions, to be able to make decisions to implement an effective marketing activities, you can analyze the data with the help of statistical science, ranging from basic statistics such as mean, average, sum, count, up to advanced statistics.