How to Analyze Amazon Ads Targeting in Excel (for Authors)

Loading

computer screen showing metrics for data analysis
Photo by Stephen Dawson on Unsplash

This article is a long, detailed tutorial involving exporting data from Amazon Ads, creating pivot tables in Excel, and working with formulas. You’ve been warned. 🙂

When you’re learning how to use ads to promote your books, you’ll hear the experts talking about analyzing and testing ads, then pausing those that don’t work. But what criteria show an ad isn’t working? And how long do you let it go before you’ve collected enough data to determine it’s not working?

Various metrics/advice I’ve read include:

  • Pause targets with no Orders, over 1,000 Impressions, and a CTR (click-thru-rate) of less than 0.10%.
  • Lower the bid on targets with no Orders, over 1,000 Impressions, and a CTR of less than 0.20%.
  • When targets aren’t generating impressions, increase the bid by 10-20%, let run for 5-7 days, then reassess.
  • When targets are generating orders at a loss, reduce the bid on the target until it converts at a sustainable level.
  • Any ASIN targets with no Orders and 10-15 Clicks or a very low CTR should be added as Negative Products.
  • Any Keyword targets with no Orders and 10-15 Clicks or a very low CTR rate should be added as Negative Keywords with a Negative Exact match type.
  • Pause ads with no Orders and 7 Clicks (or more) within a short time period.

These suggestions all sound great. Until you realize they come from people who are spending hundreds of dollars per day and have definitive knowledge of what works for them because they have data to support their findings. What about those of us who are just starting out and don’t have tons of data? Or those who have some data, but aren’t sure what to do with it?

I’ve had some success selling my contemporary romance / women’s fiction novel in the Australian market, but I’m regularly spending more on ads than I’m bringing in. I’ve been trying to tweak my ad spend to reduce the amount I’m spending without reducing my visibility or orders, and it’s been slow going and somewhat frustrating.

BUT, I’ve finally put together a spreadsheet that’s letting me see exactly which targets are working and which ones aren’t. And I think it might be useful to others, so I’m sharing it. Whether you can use it depends on how much data you have, if you’ve had a significant amount of sales/orders, and if you’re willing to take the spreadsheet plunge with me. (I know. It’s intimidating.)

My goal is to identify which targets are costing me more than they are earning and which ones are producing more revenue than they cost.

My Scenario

The scenario I’m working with is:

  • I’m focusing on the Australian market and working with AUD dollar units.
  • I sell wide and am NOT in the KDP Select (Kindle Unlimited) program.
  • I use Microsoft Excel, so if you use Google Sheets or another spreadsheet program, you’ll have to translate what I’m doing here for that application.
  • I’m on a Mac, so my Excel may look different from yours.

Things you should know to follow along:

  • Be familiar with the Amazon Ads dashboard.
  • If you don’t use Excel, know how to translate my Excel instructions for your spreadsheet of choice.
  • What is your royalty amount per order? (Not your sales amount, but what Amazon actually pays you per book sale in the local currency. If we’re working in the Australian market, we need to know the AUD amount.)
  • What is your average royalty percentage at Amazon from the marketplace you’re exporting from? (Note: This should be calculated on what you’re actually paid, not what box you ticked. If you have a mix of ebooks and paperbacks, your average royalty percentage will be something between the ebook and paperback percentages.)

Caveats Before We Start

I need to point out some things before you spend any time on this. There are some scenarios where this process and spreadsheet will not help you. It’s okay to bail on it, I promise!

  • If you are in KDP Select (Kindle Unlimited), this export process only works for your actual book sales. The KENP Royalties column is not included in the export from the All Campaigns dashboard. 
  • If your royalty percentage varies within this market, this analysis will not be 100% accurate. We’re applying a weighted average to all sales within the exported market.
  • If you are afraid of spreadsheets and/or exporting and importing data, this tutorial may not be for you because we’re doing a deep dive with formulas and pivot tables.

If none of those scenarios apply to you, then let’s get started.

Exporting from the Amazon Ads Dashboard

The Amazon Ads dashboard is pretty fantastic. You can view data from a macro level (all campaigns) or analyze a specific campaign. For either of these scenarios, you can filter out data based on time periods and/or other criteria represented by the columns available. And you can go further by drilling down to the actual targets (ASIN or keywords) used. Once you’ve got your filters set, you can export the data to a spreadsheet.

For our purposes, we’re going to pull lifetime data from all enabled targets.

1) Open your Amazon Ads Dashboard for the marketplace you wish to focus on.

The link for the Australia marketplace where I’ll be working is advertising.amazon.com.au. The link for the U.S. marketplace is similar: advertising.amazon.com.

Another way to access Amazon Ads is from your KDP Dashboard:

  1. Log into kdpreports.amazon.com.
  2. From the top menu, click Marketing.
  3. Scroll down to the Amazon Ads box and click the dropdown under Choose a marketplace.
  4. Click the yellow Go to Ads Console button.

2) Click on Targeting on the left side menu.

screen shot of amazon ads dashboard
Screenshot by Lancy McCall

When you select Targeting, the pane at the bottom changes to show the individual targets (either ASINs or keywords) used throughout all of your Campaigns.

3) Change filters to Enabled targets and Lifetime date range.

We want to see the full history of the active targets, excluding any paused or archived targets. (Note: If you wish to see all history, including paused and archived targets, don’t select any filters.)

Screenshot by Lancy McCall
  1. Filter to see Enabled Targets only.
    • Click the Filter dropdown. 
    • Select Target Active Status > Enabled.
    • Click Apply.
  2. Change Date Range to Lifetime.
    • Click the Date Range dropdown arrow.
    • Select Lifetime on the left.

We are choosing to view the Lifetime of the targets in order to get all the history, but you may want something different.

4) Export the data.

Important! Scroll down so you can see the totals row at the bottom of the Pane. In the Targets column, you’ll see the total number of enabled targets. But if you look over to the right, below the Targets pane, you’ll notice you’re only being shown 300 results per page and you have to click an arrow to go to the next page of results.

Screenshot by Lancy McCall

This means you’ll need to export MULTIPLE times to get all of your targets.

If you have a ton of targets and don’t want to export all of them, you can use the other filter options to reduce the total number of targets. You can filter by specific campaigns or targeting types. Or by the number of orders or clicks. You need to decide what’s important to see. For this tutorial, we’ll leave it set to all enabled targets and export multiple times.

When you click the Export button (to the right of the Date Range), the dashboard will produce a comma-delimited (.csv) file to download. Save the file to a location you’ll remember. (These are temporary, so I usually just leave it in my Downloads folder to be cleaned up later.)

Screenshot by Lancy McCall

Here’s the process:

  1. Click the Export button next to the Date Range.
  2. Click the right arrow button at the very bottom (>) to advance to the next page of targets.
  3. Click the Export button again.
  4. Repeat until there are no more pages of targets.

Importing into the Spreadsheet

Now that we’ve exported all the target data, we need to import it into a spreadsheet where we can work with it.

1) Locate the downloaded files.

Using Finder (or Windows Explorer), locate the folder where you downloaded the .csv files.

Screenshot by Lancy McCall

2) Copy the data into Excel (or other spreadsheet).

If you have Excel on your computer, double-clicking the file will open it in Excel. Do NOT let Excel convert your data! You want it to come in exactly as it is in the file. 

Detailed instructions:

  1. Open the first file in Excel.
  2. If prompted to convert your data, select Don’t Convert.
  3. Click File > Save As… and change the File Format type (at the bottom) to Excel Workbook (.xlsx). Give it a name and click Save. (Save to a location you will remember!)
  4. Highlight the heading row and change the style so you can see it better. I usually give my headings a darker background color, bold the text, and change the font so it wraps around.
  5. Click in the cell beneath the headings row (A2) and freeze the panes to keep the headings at the top. (View > Freeze Panes).
  6. Move to the first cell beneath the last row of data (should be cell A302 if you had more than one export).
  7. Open the next .csv file and copy the next batch of data to your Excel spreadsheet (excluding the headings). Double-check the data looks right and lines up properly.
  8. Repeat until you have all the data copied over. Your last row of data should be one more than your total number of records from the Amazon Dashboard. (1,003 total targets equals data down to row 1004.)
  9. Save your spreadsheet file!

Setting Up the Spreadsheet 

Visual Cleanup

Here are some things I like to do to help me visually see the data. (These are optional.)

  • Format the headings, so they stand out. I usually change the background color, center and wrap the text, and add borders to the cells.
  • Add a filter to the column headings. In Excel, select a heading cell, then click Data > Filter.
  • Show duplicates in my target column. Highlight the Target column and turn on Conditional Formatting. In Excel, highlight the column and click Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values, then click OK.
  • Add sums and counts for important columns. Insert a row above the column headings to add formulas to sum or count the items in the columns. Use the SUBTOTAL= formula to return values while filtered. I usually count the Targets column and sum the Impressions, Clicks, and Orders columns.
    • Example 1: Count 500 rows in column B where the heading is in row 2: =subtotal(3,B3:B503) 
    • Example 1: Sum 500 rows in column B where the heading is in row 2: =subtotal(9,B3:B503) 
  • Freeze the panes so the Target column is always visible on the left. Place your cursor in cell C3 (the column immediately right of the Target and the row just under the headings row). Click View > Unfreeze Panes to clear the previous settings, then View > Freeze Panes to reset.
  • Save frequently! My rule is to save at least after every major change, but I save more often than that. CMD + S (CTRL + S in Windows) is your friend!

At this point, my spreadsheet looks like this:

Screenshot by Lancy McCall

You can see I have some duplicates listed, so I’ll need to examine those to make sure they are legitimate.

Setting up the Pivot Table

Okay, you’ve hung with me this far. Don’t lose steam now! We’re going to create a Pivot Table. I’ll walk you through it. These instructions are for Excel, so make changes as needed to use in your spreadsheet application.

  1. In your data sheet where you’ve imported all your target information, highlight all of your data, including the column headers. (Highlighting the columns won’t work; the column headings must be the top row selected.)
  2. Click Insert > Pivot Table, then click OK to put it in a new sheet.
  3. On the right in the Pivot Table Fields pane, drag the following fields (column headings) from the top list down into the following areas:
    • Filters area:
      • Active field
    • Rows area:
      • Campaign field
      • Ad Group field
      • Target field
    • Values area:
      • Impressions field
      • Clicks field
      • Orders field
  4. Rename the Excel tab. Double-click the sheet tab and give it a name to indicate it’s your analysis/summary report.

Adding Calculated Field: Net Profit

We’re going to create a calculated field called Net Profit to show how much we make per order. This is where you need to know what your average royalty percentage is for each book sale you make. 

Tip: If the PivotTable Fields pane on the right disappears, click anywhere in the Pivot Table and it will reappear. Another way to find it is to navigate to PivotTable Analyze > Field List.

This formula will calculate your estimated Amazon royalties (not gross sales) less your ad spending for each target on the list.

1) Calculate the weighted average royalty rate for this marketplace.

The weighted average royalty rate is based on what you’re paid from Amazon on the books you sell in this marketplace. If you sell a mix of ebooks, paperbacks, etc., this rate will not come out to the standard rate you checked when uploading the book (i.e., 70%, 35%, or 60%). 

Formula: (eb royalty paid * eb weight + pb royalty paid * pb weight) / (eb price * eb weight + pb price * pb weight)

Example 1: Australia market where I only sell ebooks:

  • 100% ebooks sold (eb weight = 100)
  • Ebook sales price: 6.30 AUD
  • Ebook royalty paid: 4.40 AUD 

Formula:  (4.40 * 100) / (6.30 * 100) = 70%

Example 2: UK market with a mix of ebooks and paperbacks:

  • Ratio ebooks to paperback is 2 to 1 (eb weight = 2, pb weight = 1)
  • Ebook sales price: 3.99 GBP
  • Ebook royalty paid: 2.76 GBP
  • Paperback sales price: 12.99 GBP
  • Paperback royalty paid: 3.94 GBP

Formula: (2.76 * 2 + 3.94 * 1) / (3.99 * 2 + 12.99 * 1) = 45%

2) Set up the calculated field in Excel

Here’s how to set up the calculated field to estimate your Net Profit:

1) In Excel, go to PivotTable Analyze on the Ribbon Bar and click on the dropdown for Fields, Items, & Sets.

2) Click Calculated Field…

Screenshot by Lancy McCall

3) Name the field Net Profit.

4) In the Formula field, enter the formula:

  • Replace the 0 with the average royalty percentage in decimals (i.e., 70% = .70) that Amazon pays you in this marketplace.
  • Type an asterisk (*) behind that number.
  • From the Fields list, scroll down and find the Sales field. Double-click it to move it up to our formula.
  • Type in the minus sign (-) behind that field.
  • From the Fields list, find the Spend field. Double-click it to move it up to the formula.

5) Check that your formula looks like this: 0.xx * Sales - Spend.

Screenshot by Lancy McCall

6) Click Add in the top panel just below the formula field.

7) Click OK.

Visual Cleanup

Because I’m a nerdy bean counter, I like to make it pretty and easy to read by formatting my numbers and headings.

Format Numbers:

  • On the PivotTable Fields pane, find the Values window, then right-click on the Sum of Impressions field and select Fields Settings.
  • Click the Number… button on the lower left.
  • Select the Number category for Impressions and click OK. Here are the settings I use for non-currency amounts: use 1000s separators, 0 decimal places, and show negative numbers in red.
Screenshot by Lancy McCall
  • Click OK again to close the PivotTable Field box.
  • Repeat for the Clicks and Orders fields.
  • For currency fields like Net Profit, I use the Currency category with red for negative amounts to make them stand out:
Screenshot by Lancy McCall

Format Headings

For headings, I format the row and columns rather than the fields inside the Pivot Table. (I find it just works better.)

  • Highlight the four columns the PivotTable numbers are in (should be B through E).
  • Make them skinnier. You can do this by dragging the column divider at the top or by right-clicking in any highlighted column, select Column Width, and enter 10.
  • Highlight the row the headings are on and format the text to Wrap. 
  • From here, you can tighten up or widen the columns as you prefer.

Now let’s set it so the headings don’t move.

  • Place your cursor to the right of the Row Labels column (our targets) and just under the headings row. This should be cell B4.
  • Click View > Freeze Panes.
  • Now when you scroll, the headings remain at the top.

Analysis

Let’s dive into how to use the Pivot Table to analyze your data. First, let’s see how to hide and view details in a Pivot Table.

Hide/Show Details

Right now, our Pivot Table is showing all targets. But we can collapse the table to show only subtotals at each level we have in the Rows window.

Example: Campaigns

  • Hover over any Campaign in column A until you see a thick, black down arrow, then left-click. 
  • This highlights all Campaigns in the table.
  • Now right-click a highlighted Campaign.
  • Select Expand/Collapse > Collapse to “Campaign”.
  • The table collapses so you can see at a glance which Campaigns are making a profit and which ones aren’t.

You can collapse and expand this way for all fields in the Rows window. And if you see a subtotal that you want to dive into, you can click the little plus (+) sign to the left of its name to expand just that one field.

Here you can see that my “Also Boughts” Campaign is making me a small profit overall, but when I expand it to see the Ad Groups, my ASIN Ad Group is showing a loss! I’ll need to drill down into the targets to see what’s going on there.

Screenshot by Lancy McCall

Change Filters

When we set this table up, we included the Active field in the Filter window. But that only makes sense if our data has targets with multiple statuses. 

Active Targets

Since our export was for enabled targets only, we can remove the Active field from the filter. (If you included all targets in your export, instead of removing the field from the filter, use the dropdown to filter only Enabled targets.)

To remove the field, you have two options using the PivotTable Fields pane on the right: 

  1. In the Field Name window, uncheck the Active field OR
  2. In the Filters window, click Active field then drag and drop it back to the Field Name window.

Note: Once you change Filters, you may need to Unfreeze, then Freeze the column headings again to keep the headings at the top of the worksheet.

Orders

Let’s go back to the loser ASIN Ad Group in my Also Boughts Campaign. When I drill down into the Targets, there are many items. We’ve changed the negative numbers to red, so those stand out, but how can we narrow down what we’re looking at even further?

Screenshot by Lancy McCall

Consider this: Any target with zero clicks won’t show a loss because we haven’t spent any money. We could filter out targets with zero clicks to hide unneeded details.

  • In the PivotTable Fields pane, scroll through the Field Name window and find Clicks. It will have a checkmark by it because we’re using it in our Values window.
  • Click and drag it into the Filters window.
  • At the top of the Pivot Table, select the dropdown beside the Clicks field and uncheck the zero (0).
Screenshot by Lancy McCall

Now we’ve cleared out some data and can see a list of which targets are negative and which ones are positive. Based on what we see here, we may even want to filter out targets with only 1 click so we can focus on targets getting multiple clicks, but no orders. 

And we can also easily see where we’re making money, which means we might want to review our bidding for those targets.

Screenshot by Lancy McCall

Sorting

Another way to make data jump out at you is to sort in a way that is meaningful. Right now, our data is sorted according to how it’s listed in our data set on the other sheet. But what if we put the biggest losers at the top? That would really make them stand out.

  1. To sort from least profitable to most profitable, click on any detailed target in the Sum of Net Profit column.
  2. Right-click and select Sort > Sort Smallest to Largest.

That’s it! Now, no matter what filter we’re using or how much detail we’re showing, our detail data within the pivot table will list in order of the biggest loser to best winner.

Screenshot by Lancy McCall

Now, I can easily see which ASIN targets need my immediate attention. That target with 3,197 impressions, two clicks, and zero orders may not be a good fit for my book. On the other hand, that target with 496 impressions, two clicks, and one order might need a higher bid!

Note: You can sort at the Ad Group level and the Campaign level as well. Just click the amount in the Sum of Net Profit column on a Campaign or Ad Group summary row.

Reorganizing

Currently, I’ve got this grouped by Campaign, then Ad Group, then Target. You can change this at any time. 

Examples:
  • If you don’t care about the Ad Group or Campaign and just want to see Targets, you can remove the Campaign and Ad Group fields from the Rows window (using the drag-and-drop method we used on the Filter).
  • If you want to filter by Campaign and see Ad Groups and/or Targets, you can move the Campaign field up to the Filters window.

That’s the magic of Pivot Tables… you can change the view and move things around to see what you need to see.

Don’t forget to save frequently!

Wrap Up

Okay, THAT felt like a ton of work. But it’s just because we walked through it for the first time. Now that you know the process and how to create and work with pivot tables, you can analyze your data to your heart’s content.

And the good news is that since you have set this up, you can copy this spreadsheet and reuse it for different data sets. The key there is to make sure you’re downloading the same columns/headings. (Note: If your number of rows increases, you’ll need to change the range of your pivot table (PivotTable Analyze > Change Data Source).)

Warning and Heads Up

This tutorial used the All Campaigns Targeting data set on the Amazon Dashboard. If you export the targeting for a specific Campaign on your dashboard (Campaign > Ad Group > Targeting), the columns are different. You can get KENP Sales this way, but it’s painful. 

Also, when working within one Campaign, the exported columns for Product and Category Ad Groups differ from the column headers exported for Keyword Ad Groups. If you want these all in the same spreadsheet, you’ll have to do some fancy footwork to make those match up when you copy them over.

It’s probably better to use Amazon’s Targeting Reports (under Measurements and Reporting on the left side menu). The caveat there is that the data only goes back for the last sixty-five days, so if you haven’t been collecting it from the beginning, you won’t have the history.

Your Turn

Thoughts? Was this too technical? Or is it the missing piece you’ve been looking for? Did I explain it well or do you still have questions?

Let me know in the comments! I hope you find it useful, so I truly want to know if it helps. (Or if it needs some work!)

Thanks!


Happy Analyzing!


NOTE: Your email is not required to comment in the comment section. Feel free to leave it blank. If you do enter your email, it will not be displayed or shared, nor are you subscribing to any list. Thank you!

Leave a Reply