Make ad analysis easier with pivot tables

Here’s the scenario: you’ve got six different creatives with four copy variations running across ads on three different platforms. You want to run the best combination of creative and copy and shut down the bad combos. What do you do?

If all you care about is the lowest cost-per-click and average click-through rate, you can check your application dashboard and stop reading here. That said, as a digital marketer, I’d advise you to go deeper with your conversion goals and stop settling for surface-level tracking. Some companies we work with have their conversions integrated right into the ad platforms (think Google Analytics goals brought into Google Ads). If this sounds like you and you’re tracking conversions on the platform level, you can also stop reading and know that I’m very impressed.

If you’re not that fancy and you’re pulling stats from LinkedIn, HubSpot, and Google Analytics to get a look at the full picture, have I got the pivot table for you. Keep reading to learn how you can organize your data in Google Sheets and Excel to improve your data analysis and save time on your next report.

Pull in your raw data

In one sheet you’ll want to have your individual ad stats and break them down to the individual ad level. This will allow you to segment your tests and analyze them across different variables.

Here are the columns most people like to include:

• Date range
• Source (ad platform/channel)
• Ad copy version
• Ad creative version
• Number of impressions
• Number of clicks
• Cost or spend
• Number of conversions

You might be wondering where CTR and CPC went. I like where your head is at, but stick with me, we’re not there yet.

Each row will document a different ad you have running during your defined time period (I suggest weekly tracking at the start of your campaign for faster feedback loops). Hopefully, you can export data from your ad platform to make this go nice and fast!

Set up your pivot table

Select your data columns and start building the pivot table. In Excel, you can go to Insert > PivotTable and in Google Sheets it’s under Data > Pivot Table. Next, we set up our rows and columns. First, add the fields we inputted, then it’s onto CTR, CPC, conversion rate, and conversion cost.

To create these fields, you’ll go to (in Excel) PivotTable > Analyze > Fields, Items, & Sets > Calculated Field or (in Google Sheets) Pivot Table Editor > Values > Add > Calculated Field.

In this dialogue, you’ll get asked what the formula is for the calculated field. You know this! Use the names of the fields to set them up.

• CTR = Clicks/Impressions
• CPC = Spend/Clicks
• Conversion Rate = Conversions/Clicks
• Cost per Conversion = Spend/Conversions

You’ll want to summarize these as sums and change the field type to percentage or currency to display the numbers nicely.

Slice and dice it how you want

How you arrange the data fields in your pivot table will depend on what kind of view you want to have. Here are two options that go along with this example:

View performance by platform: Set the platform as your rows and the rest of the data as your columns.

View performance by creative: Set the creative as your rows with the rest as your columns. I also like to filter here by platform, since some creative may be better on one platform but not another due to a different audience.

Sure, I could have lead with this, but then you wouldn’t have learned anything. Grab our kick-ass pivot table template so you can stay organized as your conversion goals get more and more complex.

If you find something else works better for your goals or campaign type, hit us up on social and let us know what you do differently!