MS Excel has a fabulous, easy and fast tool for planning: it’s called PivotTable. I discovered it a couple of years ago and hasn’t stopped using ever since. But now I decided to turn PivotTable into a standard procedure for planning. I posted an article a while ago about the necessity of using data from different sources and a 3-D comparison. Pivot tables are great for concentrating large chunks of data segmented into different fields.
An example.
I wanted to map and understand purchase behavior of 30,000 customers. The client didn’t know anything about it neither used it properly. They had all the data, but didn’t plan anything over it. Some of the fields in the 27 MB-file were DOB, Postal Code, order amount, customer number, gender etc.
To find the pivot table control, go to the Data tab and select PivotTable and PivotChart. There, Excel will ask you a few details of your selection data. I often used the first option Microsoft Excel list or database, but you might have other options. In the end, create a PivotTable.
After your command, the computer will create a list with all the fields your data contains. From there you can control the most different comparisons. It allows you to sum or count quantities, among other features. This is perfect for any kind of data, from sales (you can have a total breakdown) or people (total amount of customers for instance).
I crossed and compared everything I could. My own data feast. In my case, 10 minutes after starting, results showed me male customers spend more than women and that some regions are more profitable than others. The table also told me how old my customers were and how much each age band used to spend. Guess what I’m going to do with all these conclusions?
Pivot tables are all about what computers were built for: concentrate on strategy and let the machine does the “dirty” work for you. Maybe I’m becoming a marketing geek, but looking at all that privileged information had a terrific impact on my planning beliefs.
A little of philosophy: results will never be new because they’ve already happened. However, the way you discover and how you will use them is what transforms regular marketers into masters of strategy. Dig deeper to find the treasure.
An example.
I wanted to map and understand purchase behavior of 30,000 customers. The client didn’t know anything about it neither used it properly. They had all the data, but didn’t plan anything over it. Some of the fields in the 27 MB-file were DOB, Postal Code, order amount, customer number, gender etc.
To find the pivot table control, go to the Data tab and select PivotTable and PivotChart. There, Excel will ask you a few details of your selection data. I often used the first option Microsoft Excel list or database, but you might have other options. In the end, create a PivotTable.
After your command, the computer will create a list with all the fields your data contains. From there you can control the most different comparisons. It allows you to sum or count quantities, among other features. This is perfect for any kind of data, from sales (you can have a total breakdown) or people (total amount of customers for instance).
I crossed and compared everything I could. My own data feast. In my case, 10 minutes after starting, results showed me male customers spend more than women and that some regions are more profitable than others. The table also told me how old my customers were and how much each age band used to spend. Guess what I’m going to do with all these conclusions?
Pivot tables are all about what computers were built for: concentrate on strategy and let the machine does the “dirty” work for you. Maybe I’m becoming a marketing geek, but looking at all that privileged information had a terrific impact on my planning beliefs.
A little of philosophy: results will never be new because they’ve already happened. However, the way you discover and how you will use them is what transforms regular marketers into masters of strategy. Dig deeper to find the treasure.
No comments:
Post a Comment