Data-Driven-Marketing-unlock audience behavior pivot table excel

You’ve heard it before, and you’ll hear it again: data is a marketer’s most powerful tool.

Whether you’re using an advanced CRM to store your lead and customer data, or you’re working with advertising platforms and their results dashboards, or you’re collecting form submission data through your marketing software, chances are, you have a whopping amount of data at your fingertips.

But, what do you do with it all? Where do you begin, and how do you handle such large amounts of data in a manageable way?

The good news is: you don’t have to be a data analyst or know how to write SQL queries to be able to pull insights from a data set. Let’s take a look at a very simple approach to start getting more value out of your data.

How to Use Pivot Tables for Quick & Simple Data Analysis

In Excel, there’s a feature called Pivot Tables, which allows you to group data in various ways.

To begin, export whatever data you’d like to work with as an Excel file.

For our example, let’s say I own a business that sells a variety of paintings. My data about my existing customers might look something like this:

Now, let’s say I want to look for some patterns that might help me run better targeted and more effective marketing campaigns. I can use the Pivot Tables tool to manipulate my data to make it easier to see trends.

First, highlight the entire set of data that you want to work with. Then, navigate to the Data tab in Excel, click on PivotTable, and Create Manual PivotTable. Hit OK.


You should now see the Pivot Table Builder tool pop up.

Let’s say I want to know how many customers have purchased each of my paintings. Instead of manually counting each one in a massive data set (trust me, no one wants that task), we can simply build a pivot table.

First, I would drag “Painting” into the Row Labels box. This tells the Pivot Table Builder to group whatever data we give it by the values in “Painting.”

Next, I’ll look for a value that is unique to each customer, so that I can count how many there are for each painting. In this case I’ll use “Name.” You might choose to use email address, user ID, or any other unique value.

I drag “Name” into the Values box, which defaults to a “Count of” setting, meaning the values shown in the Pivot Table are a count of the “Name” values.

Our resulting Pivot Table looks like this:

This gives us a quick & easy way to view the sum totals for the purchases of each painting.

You also have the option of changing the Values column from “Count of” to something else, like Average, Max, Min, etc. Simply click on the “i” and select from the options.

Now, let’s say I also want to know if the Brooklyn Bridge painting is more popular in NY or in other states. I can add a secondary value to the Row Labels box. By placing “State” under “Painting,” I’m telling the Pivot Table to group first by “Painting” and then by “State.”

Now I can view the total number of purchases by both painting and state. Discovering that the Brooklyn Bridge painting sells best in NY might lead me to decide to run a dedicated marketing campaign for that painting, targeted to a segment of prospective buyers in NY.

Of course, in this example we used a very small data set, but imagine how powerful this tool can be for very large data sets!

Play around with the values that you include in your Pivot Tables and test out different ways of manipulating your data to see what sort of patterns you can uncover.

Once you have these insights from your data, put them to good use in improving your audience targeting and structuring your marketing campaigns in more effective ways.