Blog

The idea for this tutorial came while I was taking Bryan Cohen’s Author Ad Challenge (again). I’m a fan of this advertising course. It’s a low-investment way to learn about advertising your book(s) using Amazon Ads. The five-day challenge dispenses tons of information using videos, which means you can stop and rewatch parts that go by too fast. (A technique I use a lot!)

The difficulty for course providers is instructing an audience with vastly different technical skills. Often, participants asked questions on how to get the tools to work or expressed frustration when they couldn’t replicate the results seen in the video.

Since the technical stuff comes easy for me, I thought I’d create a “How To” article on using these tools. (Good news! You can use these tips and tricks even if you didn’t take Bryan’s course.)

But first, let’s review some terminology.

Terminology and Tools

Here are some technical terms that describe what we’ll be doing (in case I forget to explain below):

• data scraping or crawling – This is where you use an application to pull specific data from a website.
• extract – Another word for taking data from somewhere.
• transform – This refers to “massaging” the data or cleaning it up so it’s in a format we can use.
• duplicates – In the data world, this refers to repeats of the same data we’ve already collected.
• Incognito Window – This refers to a separate Chrome window opened in “incognito” mode, meaning any searches you make won’t use your search history, which can affect the end results.
• Also Bought or Also Viewed – These are sections on an Amazon product page (near the bottom) that show you similar books that customers purchased or looked at. The best source is the Also Bought table, but you can use Also Viewed as well.
• keywords – This refers to the search phrases, comparative book titles, and author names you will use as Keyword targets in the ads you create.
• ASIN – The identifying number assigned to a book by Amazon. Each book format has its own ASIN, so your ebook will have a different ASIN from your paperback.
• browser extension – Extensions (also called plugins) are applications you add to your web browser that provide additional functionality.

Here are some tools we’ll work with during this tutorial:

• BkLNK.com has a handy tool (CATFINDER) for identifying Amazon categories for a specific ASIN number.
• Instant Data Scraper extension – A Chrome extension that crawls through tables on a website, collecting everything into a file you can load into a spreadsheet.
• Also Bought Downloader by Kindletrends – Another Chrome extension built specifically to crawl Amazon pages.

A few last things before we begin:

• It’s also important to mention that I work on a Mac and am using Google Chrome and MS Excel for this tutorial.
• The browser extensions mentioned above and referred to throughout the tutorial only work on Chrome.
• If you use Windows, a spreadsheet other than Excel, or Mozilla Firefox, you may need to adjust the directions slightly to fit your environment.

Preparing Your Browser

Before we start, let’s download the extensions and get them set up properly.

Also Boughts Downloader

First, install the Also Bought Downloader by Kindletrends extension to Chrome.

1) In your web browser, navigate to Kindletrends Also Boughts Downloader.

2) Follow the instructions to add to Chrome.

3) At the top of your browser, on the right, click on the Extensions menu. (It looks like a puzzle piece.)

4) Find the Also Bought Downloader and click the pushpin icon to pin it to the top.

5) At the bottom of the Extensions menu, click Manage Extensions.

6) Find the Also Bought Downloader extension and click Details.

7) Scroll down until you see the setting to “Allow in Incognito” and click the switch to allow.

Once you’ve updated the settings, you can close the Manage Extensions tab.

Instant Data Scraper extension

The Instant Data Scraper extension is another data scraper. It’s a little clunky, so you may not want this one unless you’re going to scrape data from sites other than Amazon. 

I’ve used it on Goodreads, Kobo, and a few other sites. (Note: It doesn’t always work right, and each site is hit or miss, so you may not use this one at all, but I’ve included it here as a resource for non-Amazon sites.)

You can find this extension at Instant Data Scraper.

Follow the same instructions as you did for the Also Boughts extensions above, remembering to turn on the Allow in Incognito setting.

Finding Data (aka Keywords)

The goal of advertising is to find your target audience and let them know you have a book they might like. A basic way to do this is to identify the genre of your book, then look for comparative book titles in your genre that are like your book. These are called “comp titles” in the publishing industry.

Go Incognito 

Before we do anything, let’s open up an incognito browser window. This lets us do our searches with no influence from our search history or things we’ve looked at today.

Right-click on the Chrome icon and look for the option to open the sneaky window. You can also do this from Chrome by navigating to File > New Incognito Window using the menu at the top.

A new browser window will open that looks different from your regular window. Double-check that the extension icons are showing at the top right. If they aren’t, you need to go back to your regular window and change the extension settings to allow them in the incognito mode as described above.

Now that we’re in sneaky mode, let’s find our data.

One quick and dirty way to start is to find the category your book is in. You can look on your book’s product page and scroll down to the Product Details section or you can use a tool to get your more information that what’s shown there.

Find the Category

Using BkLNK

1) In your web browser, navigate to BkLNK.com.

2) From the main menu, click on Author Tools > CATFINDER – Category Finder.

3) Scroll down until you see the box where you enter the Book’s ASIN or ISBN-10 number (and the country you want).

4) Enter your book’s ASIN number and click Go Find!.

5) Copy the text results at the bottom and store them somewhere for use later.

Scraping the Comp Data

Now that you know your category, you can find other books in the same category.

Finding Books in Your Category

Another neat feature BkLNK.com provides is the ability to go directly to the Amazon Top 100 page for the categories returned when you search.

1. From the results, click on the Top 100 icon next to the first category you want to explore. This takes you directly to the Amazon Best Sellers page for that category.

Note: You can also get to this page by clicking the category from your product page or by searching for “Amazon best sellers for [your category].”

Using the Extensions to Grab the Data

With your Also Boughts extension, you will notice an icon that looks like an inbox at the top of the Amazon Best Sellers page.

Note: If you don’t see this box, go back to your regular browser and check the extension settings to make sure allow in incognito windows has been turned on.

1) Click the inbox icon and wait for the extension to finish scraping the data. It may prompt you to allow Chrome to download files.

2) When the scraper is complete, at least one file will show downloaded in the bottom bar of the browser.

Note: Ignore the .PNG file completely. We’re not going to open the .TXT file from here, but if you did, it would look like this:

Bring the Data into a Spreadsheet

From this point on, we’ll be playing in a spreadsheet. The instructions below are specifically for Excel, but these actions and formulas are possible in other spreadsheet applications as well. (I’m not familiar with anything else, so can’t provide detailed instructions on those.)

If you don’t have Excel, check out fellow Ads Challenge participant Robert Graver’s 4-minute video to show how to get the data into Google Sheets using the Instant Data Scraper plugin. He covers how to use the text-to-columns feature in Sheets similar to how I use it in Excel.

Shared with permission by Robert Graver

3) From Excel, open the .TXT file. You can find the file wherever your browser usually saves downloads. 

For me, it’s my Downloads folder. Steps to open:

• Open Excel and go to File > Open.
• Navigate to the Downloads folder.
• Sort by date so the latest file shows up top. 
• Select the file and open.

4) When you open the file in Excel, the Text Import Wizard will pop open. Do the following:

• Select Delimited (default) at the top and click Next.

• Verify only Tab is clicked and nothing else, then click Next, then Finish.

• The data from the Best Sellers page appears in columns in your spreadsheet.

5) Save the file to your preferred folder, changing the type from a Comma delimited (.CSV) file to an Excel Workbook (.XLSX) file.

Massaging the Book Titles

The next thing we need to do is clean up the book titles. In the title column, the full title shows, including the subtitle and any references to series (often in parentheses). We don’t want all that extra information in our keywords. We want the title only.

Here’s my procedure to clean these up.

1) In the column to the right of the last column of data, enter a character and copy it down. This delineates our raw data from the information we’ll be working on. I like to use two pipe symbols (||) and highlight the column so I can easily see it.

In my example, it’s Column J. I change the background to gold, make it smaller, and center the contents. 

2) Copy the Title column to the right of the gold column (Column K, in the example). Then rename the original title column to “Original Title.” (I do this so my original data stays pure and I can make changes without issues in the new column.) 

3) Highlight the new Title column. From the Excel menu, select Data > Text to Columns.

Note: This process will overwrite any data in the column to the right and sometimes multiple columns if the Original Title has more than one instance of the chosen delimiter. Make sure your columns to the right are empty before proceeding. (And if you get a message saying there is already data there, just cancel and check out what’s happening. It will warn you before overwriting something.)

4) Follow these steps in the Convert Text To Columns Wizard:

• Select the Delimited option and click Next.
• Uncheck Tab and select Other. Enter an open parenthesis (() in the box by Other. 

• Click Next, then Finish.

Note: Don’t worry if something goes wrong. You can always use the Undo function. (Cmd+Z on the Mac or Ctrl+Z in Windows)

5) Highlight columns L through N to the right of the new Title column and clear (delete) the contents. (That information is still in the Original Title column (Col C) if you need to refer to it again.)

6) Highlight the new Title column (Col K) again and repeat the process for Text to Columns, this time using the colon (:) as the delimiter.

7) Highlight columns L through N and clear (delete) the contents again.

Now your comparable book titles are nice and clean, with the book titles only in column K.

Be sure to save your file after all that work you just did!

Filtering the Results

There may be titles on your list that aren’t relevant to your book. For example, in the Contemporary Romance category, you often get a mix of sweet, spicy, and LGBQ+ romances. Sometimes a sneaky Historical Romance will sneak into the list. (Not often, but sometimes.)

Here’s how you set up Excel so you can filter out those non-relevant titles:

1) Highlight the headings in row 1, then click Data > Filter from the Excel ribbon bar. This sets a filter dropdown on the right side of the heading cells.

2) Widen the Original Title column (Col C) so you can see the information.

3) Click on the dropdown in the Original Title column to open the filter dialog box. You can deselect the titles you don’t want to include.

If your list is really long, you can use the search parameters in the filter box to find groups of items to exclude. I often search for titles that have the word “Duke” or “Regency” in them.

The example below shows how I searched for items that Contain the word Mafia and the results from that filter. I’ll often use my gold column (Col J) to write “No” for those books I want to exclude, then set up a filter on Column J to exclude any “No” items from the overall list.

We are searching on the Original Title column because it has all the extra information which may have clues about the type of book it is. If there are extra columns for Series (as above), you can search there too.

Once you have your list of titles narrowed down to comparable books, you now have relevant titles and authors to use as keywords in your Amazon ads.

Caution When Using Filters

One last word about using Filters in Excel… When you need to copy and paste entire columns and rows, it’s best to make sure you have no filters in play. You can have the Filter function turned on, just clear any applied filters so that all rows are showing.

Otherwise, the copy and paste functionality goes wonkers. (That’s a technical term!)

You can tell if you have filters activated by looking at the row numbers on the left. If they are in blue and numbers are missing, you have a filter on.

Look at the top of each column for the filter symbol. Columns with no filter will simply have the dropdown symbol.

Advanced Filtering and Tracking

Okay, that’s a lot. You can stop here and you’ll be fine. If you want to see what I do for tracking, keep reading.

Since I create ads every week, I like to track what titles I’ve used. To do this, I keep lists of titles and authors that grow each week. Then I compare my new keywords to what I’ve already used.

Random heads up: If you use the Instant Data Scraper (mentioned above, but not detailed in the tutorial), you may wind up with more columns from your downloaded data, because it will pick up ALL fields from the table on the page you’re crawling.

Extra! I’ve created a basic spreadsheet for you so you don’t have to type in these formulas and can just copy them. See the end of the article for the link.

Set up Used Targets Sheet

This part is a one time set up. You only need to set up the Used Targets lists once, then continue to add to them each week.

1) Insert a new sheet/tab to your spreadsheet to start your lists containing used titles and authors. Name it Used Targets.

2) Set up two columns labeled TITLES and AUTHORS. (I also freeze the panes so those headings stay at the top. To do this, move cursor to cell A2 and click View > Freeze Panes.)

Modify Existing Sheet of Targets

1) Rename the tab with the downloaded targets to something that shows what category/group these keywords relate to. I like to include the source, the category, and the date so I know how fresh the information is.

2) Next, highlight the original Authors column and Copy-Paste to the right of the new Title column. Again, just keeping our working data together visually.

3) Highlight row 1 and insert a new row above it.

4) In the first row of the gold column (cell J1), enter the following formula:

=subtotal(3,k:k)-1

This formula counts the number of filtered items in the Title column, not including the heading.

5) In cell M2, enter the heading “Duplicate Title?“. In cell N2, enter the heading “Duplicate Author?“. For better visibility, format these cells so the text wraps.

6) In cell M3, enter the following formula where K3 should point to the new Title column (adjust the formula as needed):

=IF(ISERROR(VLOOKUP(K3,'Used Targets'!$A:$A,1,FALSE)),"--","Yes")

7) In cell N3, enter the following formula where L3 should point to the new Author column (adjust the formula as needed):

=IF(ISERROR(VLOOKUP(L3,'Used Targets'!$C:$C,1,FALSE)),"--","Yes")

Quick Tip: You can copy the first formula into the cell in column N, but you’ll need to adjust it to point to column range $C:$C on the Used Targets sheet.

8) Copy these formulas from row 3 to the last row of data. Any titles and/or authors already listed on the Used Targets tab would show “Yes” in the respective columns. This lets you know these titles/authors are duplicates you’ve used before.

9) Click Data > Filter to undo the existing filter. Then move your cursor to the heading row (Row 2) and click Data > Filter to reset it again. This now includes the two new columns in the filtered data.

Now you can filter out any duplicate titles from your list by clicking the filter button at the top of the Duplicate Titles? column and unchecking the “Yes” item on the list.

Update Used Targets

AFTER you’ve created your ad using these title and authors, update your Used Targets lists with the information.

1) On the downloaded targets sheet, highlight the filtered titles from the new Titles column and copy them.

2) Move to the bottom of the TITLES column on the Used Targets sheet and Paste-Value these titles. (The paste-value function will ignore items that were filtered out along with any formatting you may have done.)

3) Do the same for the Authors. Make sure you have cleared the filter on the Duplicate Titles? column before you apply the filter on the Duplicate Authors? column.

Note: After you Paste-Value these items into their appropriate lists, all the rows on the downloaded target page will show “Yes” in the “Duplicate?” columns. It’s okay. We’re done with these keywords.

Clean up the Used Targets

You don’t have to do this, but I really like my data neat, so I do a few extra cleanup steps afterward.

1) Remove duplicates and sort the TITLES column.

• On the Used Targets sheet, highlight everything in the TITLES column, including the heading.
• Click Data > Remove Duplicates to get rid of any duplicate titles we didn’t catch. (It happens.) 
• Click OK in the confirmation box to proceed. You should get a message telling you how many were removed.
• While the data is still highlighted, click Data > A-Z (sort). (This button has an A on top of a Z with a down arrow beside them.)

2) Follow the same process for the Authors column.

These two steps keep your data nice and tidy.

Bonus: A Cool Tip for Spotting Duplicates on a List

Regarding duplicates… It’s easy to see why you would wind up with duplicate Author names since authors often write multiple books. 

But why do you get duplicates in the Titles column when you’ve allegedly filtered for them? Reasons I’ve seen include:

• Different authors use the same titles as other authors.
• Some authors will begin the name of their book with the series and we removed everything behind the colon or parentheses when we cleansed the data. (An imaginary example: The Mason Brothers: Glen, The Mason Brothers: Joseph, etc.)
  • Sometimes you catch those when you’re reviewing your keyword data; sometimes you don’t.

On to the cool tip: There’s a way to quickly highlight duplicates in a list.

Let’s walk through an example.

1) On the downloaded targets sheet, highlight the entire column with the Authors (column L).

2) Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values….

3) Click OK on the window that pops up.

Now any duplicated values in that column are highlighted in red.

You can use this trick for LOTS of things in Excel.

Wrap Up

Whew! That was a long one. 

You may not use all these tips and tricks, but hopefully some of them will come in handy.

If you have any questions, drop them in the comments below and I’ll respond (and/or update the article for clarification.)

Or please share if you have other tricks that you use.

Happy advertising!

Bonus Spreadsheet Link

While writing this tutorial, I created an Excel spreadsheet that I’m sharing with you. (Here’s the link.)

I’ve saved it to my Google drive for you to download. I tested it to see if it would open in Google Sheets and it opened, but said there were compatibility issues. Consider yourself forewarned!

(This is the first time I’ve shared a document this way, so let me know if there are problems with downloading it!)

NOTE: We ask for your email address in the comment section to prevent spammers. Your email will not be displayed or shared, nor are you subscribing to any list. Thank you!