Running a pivot table on your data is a great way to see everything at a high level. Here is an example report that I pulled from my PPC account. The one column that had incorrect data was the column called “Count of Conversion Rate”. The reason why this data was off was because the pivot table could not get the true conversion rate for what I was looking for. To fix this error in data, I will need to create a calculated item in excel to show the true conversion rate percentage. Let’s start this now!
Step 1: Select the Data
Click anywhere in the pivot table. By doing this, you will have the ability to click on the analyze section. From there, you want to head over to the fields, items, and sets tool section. From there, you wan to click on the section that says “Insert Calculated Field”. See the picture below to see where we are right now.
As a side note, you will see in column G what I was talking about with the incorrect data reading.
Step 2: Create the Formula
For this example, I want to focus on the conversions against the total amount of clicks for my PPC campaign. Since I am looking for that, I am going to set up my formula to look like this. This is where you can create any formula that you want, so feel free to do whatever you would like. For me, I am just tryin to pull the conversion rate for the data that I have, so I am going to put this in.
Step 3: Deploy
With the formula set, I am going to go live with it. Again, I want this to be a percentage, but the default makes this column into a whole number. I have to go into the settings on this column to change the default type to a percentage. See the picture below for what I am talking about.
Step 4: Done
At this point, we just changed the format to a percentage, so we should be seeing this now.
This was just the tip of the iceberg on what we can do with the data. Since we are in a pivot table, we can manipulate this data to show any sort of trends. I could break out the pivot table to show by keyword, ad group, device, CTR, and so much more, but I will just leave it at that.