.
Ellie Mirman noted in an earlier post that one of the 5 biggest misconceptions about marketers is that we don’t know how to do math. She couldn’t be more correct! Many of us love numbers. And if you’re using a closed loop marketing system, you’ve probably got a lot of numbers to play with. Even if you don't love numbers, it's increasingly important for marketers to use quantitative metrics for decion making.
Most of us use Excel to slice and dice our data. Excel is one of those products with some very powerful tools that are easy to use, but you might not know about. It turns out that by learning handful of these tools, you can save yourself hours of time and quickly whip up some revealing analysis.
I'll start this Excel series off with an introduction to the almighty pivot table. The pivot table is well-loved among data geeks for its ability to quickly summarize large datasets. Suppose you wanted to know the average sales cycle for your customers according to the type of marketing campaign they participated in and their location. For each customer, you have one row of data with campaign type, location, and the number of days the lead took to become a customer.
A pivot table will allow you to quickly crunch thousands of rows of data into a neat little table of averages. The screenshots below illustrate this. In this example, customers that signed up for a demo, who were located domestically took on average 21 days to purchase, while international businesses took 42 days.
You can download the above sample data and pivot in this example file. I created this pivot by doing the following:
- Highlight the data and select the “Insert” tab > “Pivot Table” > “Ok” (leave the default options).
- In the “Pivot Table Field List” (see screenshot below), drag & drop “Campaign Type” into the “Row Labels” box; then drag & drop “Location” into the “Column Labels” box.
- Lastly, drag & drop “Sales Cycle (Days)” into “Values”. The default setting will set this to “Sum of Sales Cycle”. Since we want the average number of days, click on “Sum of Sales Cycle” > select “Value field settings” > select “Average”.
And that's all there is to it!
If you want to learn more about pivot tables and other advanced features of Excel, please stay turned to this series.
How do you use pivot tables?
Free Download: 101 Marketing Charts and Graphs
Improve your decision making and long-term performance with marketing data for best practices.
Download these 101 charts today!