On this week’s episode of Deep in the Sheets, we tackle a XY Quadrant Scatter Plot. Fancy term for a simple PPC plot that will make performance comparison visually interesting for the client and meaningful for the marketer.
Using geographic location, click-through rate (CTR) and conversion rate (CVR), we plot performance based on the set average. Adding shapes to act as barriers for performance and labels with the best Excel label add-in, simple scatter plots transform into a tool that will wow the client AND provide meaningful data. Enjoy!
Read on for the full transcript:
Hi, and welcome to another episode of Deep in the Sheets. I’m Rachel, and on this episode we explore XY quadrant charts. [MUSIC PLAYS] On today’s episode with XY quadrant charts, we will evaluate performance based on geo-location. So, with raw data we have geos broken out by their service types, with impressions, cost, clicks and conversion. You’ll take your raw data, and insert a pivot table. Within your pivot table, you’ll want your row labels to be your geo-location. You’ll want your values to calculate click-through rate and conversion-rate. To create a calculated field, go to formulas, and select calculated field. We’ll want click-through rate, so that is calculated by clicks divided by impressions. Next, we’ll want conversion-rate. That is calculated by conversions divided by clicks. We’ll change these numbers to look like percentages. And for these kind of charts, I always copy the data and paste it into a new worksheet, so that you can make a scatter-plot without having to use a pivot table graph. With these types of quadrant performance graphs you’re gonna want an average to base your quadrants off of. So you will take this cell and the average of the click-through rate for all of the geos, and the average of the conversion-rate for all of the geos. Then we’ll take just this information and insert a graph. Now that we have our graph and it’s plotted on XY, we’ll wanna add our quadrants so that we can really evaluate the performance. So, first we’ll add our labels. And once we find our average, we can add our quadrants. Go into “illustrations” and select shapes, and the square. Find the center of your average point, and draw outward within your graph. You’ll wanna format this and reduce the transparency so you can see the data underneath. To copy them, hold down CTRL, and drag to a quadrant below. And then copy those two, and drag to the quadrants to the left. Now you have your four quadrants plotted on the one graph, and you can evaluate performance based on the average and how the geos performed. Geos located in this have a high click-through rate, and a high conversion rate, meaning they’re high performing. Geos in this quadrant have a lower than average click-through rate and a lower than average conversion-rate, meaning something needs to be done to improve performance. If you wanna spiff them up even more, you can change the colors to show that this quadrant is good and high-performing, that this one is poor and low performing, and these two quadrants need a little work. And there you have your XY quadrant graph!
