Understand Raw Data with Pivot Tables: AIMCLEAR‘s Deep in the Sheets (Episode 10)

Posted in Analytics & Reporting, Paid Search

Posted on January 21st, 2015

In this week’s episode of Deep in the Sheets, we discuss Part 2 in our series about taking a document from raw data to sharing-ready through pivot tables. In particular, we walk the PPC pro through how to:

  • Formulate months as rows
  • Integrate lead quality
  • Determine quantity of leads
  • Use filters to better understand data

Enjoy the video, and keep an eye out for our next episode of Deep in the Sheets, which will cover Calculated Fields.

Read on for the full transcript:
Hi everybody! Dion here. This is part two in a series about taking a document from raw data to sharing ready. This week, we’ll be talking about “Pivot Tables 101.” [MUSIC PLAYS] You’ll recognize this as our data-set from last time. So, we’re gonna throw this into a pivot table. Since it’s in a table right now, we can just go ahead and go to “Insert,” and “Pivot Table.” And it should have all our data ready to go; Table 2. The first thing we’re gonna do is go over these different sections, down here, and kind of show how they interact. So the first thing we’re gonna try and do is get our month as rows. So you can see here we have our months counted up to the end of the year. The second thing we’re going to do is bring in our lead quality, which is isolated to the three different types. So we’ll go ahead and show that underneath each one. Now, you’ll see here we have “(blank)” because quite a few of ours don’t have a lead status. We’re gonna go ahead and remove that by going into this which is the filter, and remove blanks. So that gives us “January,” “Inquiry,” “Junk” and “Qualified,” and we wanna go ahead and see how many leads come from each one. So “ID” exists on each and every section. So we’re gonna go ahead and go and put that under “Values.” Instead of sum, because it’s just a random number, we’re gonna make this one a count, so it will count each one for each section. So you can see here, in January we had 221 Inquiry, 53 Junk, 89 Qualified. Now, say we only want to use this for certain campaigns we have. If we wanna do that, we can go ahead and use this “Filters” group. We would toss “Campaign” into the filter, And now we have the option to only show certain ones for each. So say we only wanna look at Campaign 3 over time. So, Campaign 3 only existed in June and July. That’s a quick way to see how those interact. Now, with these rows we have some other options we can do. Say we want to look at Lead Quality as the top group. So we would see Inquiry, Junk and Qualified as they existed in the months moving forward. So, those are the basics of a pivot table, next week we’ll go into calculated fields and see how this data stand out for us.

Subscribe Today

We'll keep you updated on the latest Aimclear musings & appearances