Microsoft Office Tutorials and References
In Depth Information
Chapter 27: Data Mining Using Pivot Tables
Data Mining Using Pivot Tables
This chapter is about more advanced PivotTable techniques. After you master the
basics of the previous chapter, you can explore many more of the PivotTable
features in this chapter.
On the Example sheet of the Chapter 27 Excel file, you will see a partial
database of 1,000 customers
purchases. Using this sales report, you can try and
detect patterns in the data
s PivotTable report.
In doing so, you will master many of the possibilities offered by the PivotTable.
Figure 27.1 shows a small section of the database. (The sample data has 1,000 sales
As an analyst, you might want to explore relationships between the different
variables; for example, between the customers
mining as it is called
attributes and purchases. It may help
your marketing and salespeople to identify the type of customers they want to target
to increase sales
particularly if you have limited resources, and marketing wants to
reach only part of the customer base.
The goal is to try to detect patterns. You can also check the relationship between
the number of children and purchases. Try something else: the married/single attri-
bute and purchases. Should marketing target customers with more children? Should
they target married or single customers? You can try and find out these relationships
using a PivotTable.
Let me start by finding out whether we can find a relationship between the
number of children and the amount purchased. Let
s verify whether the average
purchase value increases or decreases when customers have more children.
Create a PivotTable using the Children field in the Row Labels area. Move (drag
and drop) the Purchases field to the Data area. See Figure 27.2. This table is showing
the default sum of purchases
which is telling us that, for example, the total amount
spent by all customers with 0 children is $818, 606.
These are not really numbers that can tell any story about the relationships we
are interested in: Is there anything that indicates that an increase in the number of
children has an impact on purchases? What you want to know is the average amount
spent by a customer with 0 children and the average amount spent by a customer
with 1 child, and so on. This average amount makes more sense than the total
amount spent when you try to target the right customers.
You have to change the default Sum in the PivotTable into an Average.
A reminder on the procedure (as was done in the previous chapter): Double-click on the
word Sum and change it to Average in the menu. See the results in Figure 27.3. Now
you can see the average amount spent by customers with 0, 1, 2, 3, and 4 children.