Continuing our pivot table series, this week’s episode of Deep in the Sheets shares how to efficiently organize data in pivot tables. Pivot charts arrange data in a visually appealing format and convey statistical changes over time.
In this episode, we’ll also dive into:
- Return on Ad Spend (ROAS)
- Cost Over Time
- Distribution of Quality of Leads
Tune in next time!
Read on for the full transcript:
Hi everybody, Dion here. Today is our next part in a series about pivot tables. Today we’ll be looking at pivot table charts. [MUSIC PLAYS] So, we have here our chart from the last episode. What we’re gonna do is look at return on ad-spend and cost over time, as well as the distribution on quality of leads over time. So to do this,w e’re gonna make it visual with a pivot chart. So to start this out, we’re gonna clean this up. We’re only gonna look at Campaign 1. So I’m gonna go ahead and throw my filter in, and have us just looking at Campaign 1. Get that started. Next thing we’re gonna do is clean up these value items and only have return on ad-spend and cost. Go ahead, go down here, grab cost, throw that in there. So, right now this is not too useful, we wanna see this over time. So I’m gonna go up here, grab Month and put that into my row. Cost: I’m gonna make this a dollar value; here I’m gonna change that to currency. Simplify it a bit. So now we have our cost and return on ad-spend over time. It’s not too easy to draw conclusions out of this in this format, so we’re gonna do a pivot chart to make it, ya know, visually accessible. So we’re gonna go over to pivot chart up here We’re gonna go to combo, we’re gonna put return on ad-spend as a line, but we want it on a secondary axis. Once we do that we are good to go. Now, we’re gonna clean this up a bit, I don’t like the way it looks at default. So I’m gonna go ahead and remove all field buttons. Then I’m gonna change my design to… I like this one over here. Excel has a lot of good options. Then I’m gonna change this to this one. So I think in this format it’s pretty quick to see things really easily over time. We can see our costs, how they’ve changed. July was a big month in terms of cost, but we saw our return on ad-spend drop down, uh, so that might be a month of concern, and overall, the trend here is not so great. We can see a downward return on ad-spend and a bit of a curve here for cost. So that’s a quick way to do cost. Now, we’re gonna switch up this same chart using these same aesthetic features and look at lead-quality over time. What we’re gonna do for that is remove these, we’re gonna drop Lead Quality, which we had setup in an earlier video, we’re gonna drop that into the values, and we’re gonna take Lead Quality again and put it as the legend of the series. So, if you look at this, now you can see the number of leads. I’m actually gonna change this From a percentage to just a number. Gonna grab all those, switch those to a number. So, now if we look at this, we can see Inquiry, ya know, was very strong towards the middle of the year, and then all of a sudden it just dropped off the map. So, we might have made a change in our system at that time and as a response, we saw junk grow. After that, junk dropped a little an qualified came back up. So this is just a quick way to move data around, and with this you can really start to see how your stats are changing over time, and as you saw a pivot table lets you move things around quickly and the chart will continue to adapt. That’s it for this week, and next week we’ll look at making this data very presentable and ready to send to a client or a higher-up.
