Romance, Historical, Contemporary, Paranormal, Young Adult, Book reviews, industry news, and commentary from a reader's point of view

Auto Generate Amazon, Barnes & Noble, and more Buy Links for...

Note: This piece has been rewritten and updated to reflect changes in my practices as well as changes in the retailers.

Introduction

Cheaper by the Dozen by Frank Bunker Gilbreth, Jr. and Ernestine Gilbreth Carey ( A | iBooksBN | K | ) features parents who are efficiency experts. They study tasks and reduce the number of steps to complete the tasks. To me, that is one of the coolest things ever.

Many people ask me how I do everything: the blogging, the lawyering, the parenting, the writing. First, I have a loving and supportive husband. Second, I set time aside every day to spend with my child. Third, I have a great team of reviewers here.  Finally, I try to eliminate as many steps in my every day tasks as possible.

Doing buy links or new releases is a repetitive and laborious task so I set about trying to find the easiest way to generate these. With the use of a google spreadsheet (or any spreadsheet program) and some rudimentary knowledge of the “substitute” and “concatenate” functions I developed the worksheet you find here.

Don’t be afraid by these terms. I barely know what they mean. I promise you only need to know how to cut, paste, and use the “&” symbol.

The first sheet in the document is the “Entry Sheet”. This is where I enter the author, title, and sometimes price. This is the only information that will change once you complete the set up for your Buy Link Generator sheet.

Step 1. Decide what you want to generate.

Decide what links you want to generate. I generate Amazon, iBooks, BN, Kobo, Google links for every post. This list changes from time to time as I used to have Sony and Harlequin links but have since dropped them.

Step 2.  Study the links

Study the links and look for patterns. Links have an anatomy and by looking at them, you can see a consistent pattern. The way to do this is simply go to the online bookstore and enter a few search terms. For example, go to http://www.kobobooks.com/ and enter “Random Title Author Name”.

Click for larger image

After hitting enter, the URL changes. It is now: https://store.kobobooks.com/search?Query=author+title

Click for larger image

For most retail sites, they are appending the search terms (in this case author + title) at the end of a string. BN, Google work this way. For Amazon and iBooks, because I use affiliate links, I will generate them a little differently.

 

Step 3. Deconstruct the links.

Once you have studied the links, you can start to deconstruct them.  As we can see from Step 2, the links all use some kind of url address with the search term at the end. To make sure I’m directing the readers to the right book, I use title and author. You could just use title or author if you like.

The beginning of the links:

  • Kobo:  https://store.kobobooks.com/search?Query
  • Sony: http://www.barnesandnoble.com/s/
  • Google: https://play.google.com/store/search?q=

Step 4. Get your affiliate code.

I’m only going to cover Amazon and iBooks here because while I’ve held affiliate accounts at Kobo and BN, they have never generated any income for me. I was told that a huge number of nook users purchase direct from the device and not through any links and that might be true for Kobo readers as well.  This is perfectly fine with me and I believe in providing the links regardless,.

Amazon’s affiliate program is here. You don’t have to show any stats to prove you belong to this program so sign up.  You will get an Amazon affiliate code.  You simply need to append the affiliate code on any link  you generate for Amazon to count this toward your account. You get a percentage of any sale you make, from 6% up to 15%, depending on how much you sell.

The iBooks affiliate signup is here. You get 7% of any iTunes sale, from movies to apps to books.

Step 5. Creating your Link Generator Spreadsheet

We’ve now broken down the urls into pieces.  Open your favorite spreadsheet. I use Google Spreadsheets because I can then access the spreadsheet from every computer, no matter where I am.  Here is the link to the spreadsheet again.

The first sheet is the “Entry Sheet”.  This is where you will put in your variables. For this spreadsheet, I’m collecting information to create a new release page but you can use the same principles to create buy links. My variables on this page include:

  • Author
  • Title
  • Price
  • ASIN
  • iBooks link
  • Category

Screenshot 2015-05-23 19.14.52

The IMG column is actually auto generated from the ASIN. More about that later.

Every sheet after the Entry sheet is simply manipulating the data from the entry sheet in different ways.  Remember, once you enter your URL parts, you will never need to change those again. Never.  Please feel free to copy my sheet.

Step 6.  Getting your Title and Author ready for use

As you can see by the above urls, the author and title need to be separated by a “+” or “-” often times. Not so for Amazon, Kobo or Sony.  Those etailers will automatically create the right sort of link but Barnes and Noble will not. Thus, you need to use the “Substitute” function.

The Substitute function looks for characters and replaces the character with another character. In our case, we want the program to look for a space between letters and replace the space with a “+” or “-“.  You can look at the Substitute sheet on the worksheet to see how I’ve used the Substitute function to replace the “space” with several different characters or sets of characters.

SUBSTITUTE(A1,” “,”-“)

Here, we are saying look at the content in the cell A1 (which is title and author) and replace the ” ” with “-“.  In the second column, I am replacing the ” ” with “+”:

SUBSTITUTE(A1,” “,”+”)

You can see the substitution in action on Sheet named “Book Titles.”

Screenshot 2015-05-23 19.09.29

Step 7.  Manipulating your code.

Now the real magic takes place. Most spreadsheets recognize the concatenate function.  Basically this means that you are joining two “strings” together.  Each column contains a string and we are just putting it all together by using the “&” sign..

To add Author and Title together, you would have A1&A2.  Which is TitleAuthor.  The problem here is that we need a space between the Title and Author.  Any text you want to add needs to be between quotations marks.  Therefore, to have Author space Title you would use this code: A1&” “&A2.

With the concatenate function, you simply add together the data entered in the entry form in various formats to complete the output that you want.  As you can see by the “New Releases” sheet, I put in all the various html code that I need from “|” (these are called pipes) to the column codes.  This is because I want to reduce my repetitive tasks.I can simply copy and paste from the New Releases sheet into a WordPress blog entry form.

=CONCATENATE(C1,‘Entry sheet’!E2,‘Entry sheet’!A2,” by “, ‘Entry sheet’!B2,“: “,“$ “,‘Entry sheet’!F2,AMZN!A1,” | “,iBooks!A1,” | “,BN!A1, ” | “, Kobo!A1,” | “, ‘Google Play’!A1,‘Entry sheet’!H2,D1,)

This function says to Google Spreadsheets to take C1 and add that to theE2  cell from Entry Sheet and so forth. The end result is this:

<a href="http://www.amazon.com/gp/product/B00OXLEKBW/ref=as_li_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=B00OXLEKBW&linkCode=as2&tag=dearauthorcom-20"><img border="0" src="http://ws-na.amazon-adsystem.com/widgets/q?_encoding=UTF8&ASIN=B00OXLEKBW&Format=_SL250_&ID=AsinImage&MarketPlace=US&ServiceVersion=20070822&WS=1&tag=dearauthorcom-20" ></a><img src="http://ir-na.amazon-adsystem.com/e/ir?t=dearauthorcom-20&l=as2&o=1&a=" width="1" height="1" border="0" alt="" style="border:none !important; margin:0px !important;" />Hot Alphas by Lora Leigh: $ 4.99<a href="http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords= Hot+Alphas+Lora+Leigh&linkCode=ur2&tag=dearauthorcom-20">AMZN</a> | <a href="https://itunes.apple.com/us/book/hot-alphas/id934032812?mt=11">iBooks</a> | <a href="http://www.barnesandnoble.com/s/ Hot-Alphas-Lora-Leigh">BN</a> | <a href="https://store.kobobooks.com/en-US/search?Query=Hot+Alphas+Lora+Leigh">Kobo</a> | <a href="https://play.google.com/store/search?q=Hot%20Alphas%20Lora%20Leigh">Google Play</a>Anthology

Step 8. Amazon and the ASIN.

Amazon has images and data that you can pull from its site using API. I don’t know how to use API but I do know that Amazon uses its ASIN code to pull images and direct links. You can use these to display different sized images on your site and direct readers to a specific page on Amazon’s retail site.

Basically all you have to do is change the ASIN number which is the TEN digit/letter combo that follows ASIN in the book link. The ASIN is found in the product detail section on the book page.

Screenshot 2015-05-23 19.21.20

I use a slightly different code in my worksheet because I like a larger image.

http://ws-na.amazon-adsystem.com/widgets/q?_encoding=UTF8&ASIN=B00OXLEKBW&Format=_SL250_&ID=AsinImage&MarketPlace=US&ServiceVersion=20070822&WS=1&tag=dearauthorcom-20

It looks more complicated but I’m only changing the ASIN again. Thus with the ASIN, you can generate a link to the direct product page as well as to the cover of the book.

You would put

A1: http://ws-na.amazon-adsystem.com/widgets/q?_encoding=UTF8&ASIN=

A2: ASIN

A3: &Format=_SL250_&ID=AsinImage&MarketPlace=US&ServiceVersion=20070822&WS=1&tag=dearauthorcom-20

Then using the concatenate function, you would add the three together to get your image link.

  • =CONCATENATE(A1,A2,A3)

Step 9. Apple and the Linkmaker

If you want to use iBooks affiliate, the easiest way is to insert a special code in the footer of your site. The javascript code is here.

With one snippet of code added to your website, all iTunes, App Store, iBookstore and Mac App Store links will be converted to affiliate links. Add the javascript to the footer of your website to get started.

Once you have your Javascript code in your footer, the easiest way to obtain links is to go to the iBooks store and right click. Copy Link will appear and you select that option.

Screenshot 2015-05-23 19.05.17

I had the worst tine using the dropdown box by the price and was told by the iBooks representative that this is the easiest way to obtain a link. You can also search/find a link using the iTunes Link Maker.

Jane Litte is the founder of Dear Author, a lawyer, and a lover of pencil skirts. She self publishes NA and contemporaries (and publishes with Berkley and Montlake) and spends her downtime reading romances and writing about them. Her TBR pile is much larger than the one shown in the picture and not as pretty. You can reach Jane by email at jane @ dearauthor dot com

16 Comments

  1. tellulahdarling
    Jun 10, 2012 @ 12:36:48

    Great info. Plus Cheaper By The Dozen is a fabulous book. The movie (the original, not the Steve Martin remake) holds up really well too. I just showed it to my ten year old and she loved it.

  2. Tessa Dare
    Jun 10, 2012 @ 13:23:12

    This is … wow. Thanks for writing that all out.

    For anyone who might be overwhelmed by spreadsheets, there’s also the a link generator at Courtney Milan’s site here. Just plug in the isbn, your affiliate code if you have one, and it generates html code with links. However, the script still includes a Borders link, so you have to edit that out. Sigh, I miss Borders.

  3. Darlynne
    Jun 10, 2012 @ 13:49:02

    I was a VBA and Perfect Script programmer, but HTML doesn’t speak to me at all. Between this and your “Coming Soon” magic, Jane, consider me many shades of impressed.

  4. Putting Buy Links On Your Blog « Mary Sue Digital, LLC
    Jun 10, 2012 @ 21:36:47

    […] tool for book bloggers, who can include buy links with their reviews.  Here’s the article: http://dearauthor.com/misc/bloggertips/auto-generate-amazon-barnes-noble-and-more-buy-links-for-your… Share this:TwitterFacebookLike this:LikeBe the first to like this […]

  5. Courtney Milan
    Jun 11, 2012 @ 12:50:32

    This feels like something that could be implemented with PHP to make it even easier to use than an Excel spreadsheet. That could hypothetically then be modified into a WordPress plugin.

    I did something like this for print books, but it’s less useful now, and the lack of easily searchable ISBNs confounds my original script.

    Jane, would you mind if I tried to work something up that would do this all on the web?

  6. Jane
    Jun 11, 2012 @ 12:54:37

    @Courtney Milan: I think that would be fantastic. Take everything. Make it yours.

  7. Estara
    Jun 11, 2012 @ 16:05:34

    Author Shawntelle Madison also did an ISBN link generator php on her site, inspired by Courtney Milan’s but with many more options (a whole slew of bookshops you can check, format for sidebar, a pre-order link label, etc.):
    http://www.shawntellemadison.com/bookstore-isbn-link-generator/

  8. janicu
    Jun 11, 2012 @ 21:10:05

    I like it. It’s one of the more annoying things with blogging -those links! I’m always going to the site, plunking in the search query, then copying the link. This would also be useful tip for generating the html I use to display the book covers for my reviews.

  9. Shawntelle Madison
    Jun 11, 2012 @ 21:59:51

    Great post, Jane!

    Like Estara mentioned I created a bookstore link generator based on Courtney’s work. I can see there are some retailers I need to add. I also need to add an output type for custom code like custom link classes, unordered lists, etc.

  10. lil wayne
    Jun 21, 2012 @ 14:31:59

    An impressive share! I’ve just forwarded this onto a friend who has been conducting a little research on this. And he actually ordered me dinner because I found it for him… lol. So let me reword this…. Thanks for the meal!! But yeah, thanks for spending some time to discuss this subject here on your website.

  11. Abigail
    Jul 15, 2012 @ 20:50:24

    This is going to be a HUGE help. I’ve never really played around with spreedsheets like this before, so I apologize if this is an obvious question. On the ‘Manipulating Your Links’ sheet, once you have everything the way you want it, is there an easy way to combine everything in the row so you can paste the whole code in a post?

  12. Jane
    Jul 15, 2012 @ 21:07:24

    @Abigail: Yes. If you are looking at the manipulating your links sheet, you can see that I have three different ways I am presenting the links with the html code included. Simply copy and paste the row that represents the code that you want in your blog post.

    Test it out and you’ll see what I mean.

  13. Abigail
    Jul 16, 2012 @ 16:01:01

    I was having trouble getting it to copy a range of cells (and not the formula). I didn’t realize ctrl+click doesn’t work in Google spreadsheets. i did a quick search and someone suggested Shift+click and that worked. Can’t wait to try it out :)

  14. Hot off the Press: 89 Paranormal Titles Releasing in August | All Things Urban Fantasy - Where Para is Normal
    Jul 31, 2012 @ 04:00:34

    […] to get them all done–she did the lion’s share–but a huge shout out has to go to DearAuthor.com and their amazing time saving automated BuyLink Generator.  We’re grateful they shared their […]

  15. I ♥ Bookie Nookie
    Apr 28, 2013 @ 09:38:32

    Hi Jane!

    Thank you so much for putting this together – very helpful!

    Does this code work for blogger as well? I am having a problem getting the button to show up.

  16. Jane
    Apr 28, 2013 @ 10:03:10

    The button code is CSS dependent, meaning you need to have CSS styling to make your links look like buttons.

%d bloggers like this: