Note: This piece has been rewritten and updated to reflect changes in my practices as well as changes in the retailers.
Cheaper by the Dozen by Frank Bunker Gilbreth, Jr. and Ernestine Gilbreth Carey ( A | iBooks | BN | 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”.
After hitting enter, the URL changes. It is now: https://store.kobobooks.com/search?Query=author+title
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:
- iBooks link
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.
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 “+”:
You can see the substitution in action on Sheet named “Book Titles.”
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.
- Book Link: http://www.amazon.com/exec/obidos/ASIN/B00OXLEKBW/dearauthorcom-20
- Small Image Location: http://images.amazon.com/images/P/B00OXLEKBW.01.THUMBZZZ.jpg
- Medium Image Location: http://images.amazon.com/images/P/B00OXLEKBW.01.MZZZZZZZ.jpg
- Large Image Location: http://images.amazon.com/images/P/B00OXLEKBW.01.LZZZZZZZ.jpg
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.
I use a slightly different code in my worksheet because I like a larger image.
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
Then using the concatenate function, you would add the three together to get your image link.
Step 9. Apple and the Linkmaker
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.