Jun 10 2012
Cheaper by the Dozen by Frank Bunker Gilbreth, Jr. and Ernestine Gilbreth Carey ( A | BN | K | S ) 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. 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 (they really carried me last week). Finally, I try to eliminate as many steps in my every day tasks as possible.
Doing buy links is a repetitive and not fun 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, BN, Goodreads, Kobo, Sony links for every post. Sometimes, depending on the book, I will generate a AllRomance eBooks link and a Harlequin link.
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: http://www.kobobooks.com/search/search.html?q=random+title+author+name
Sony has a similar URL search address: http://ebookstore.sony.com/search?keyword=random+title+author+name
What is the similarity here? They both have append the search url with the title and the author. For Kobo it is “http://www.kobobooks.com/search/search.html?q=” and for Sony it is “http://ebookstore.sony.com/search?keyword=”.
Amazon is just as easy: http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords=random+title+author+name
Barnes and Noble is a little more difficult: http://www.barnesandnoble.com/s/random-title-author-name?keyword=random+title+author+name&store=allproducts
BN uses random title and author name twice and in two different ways but all retailers use the search links repetitively.
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: http://www.kobobooks.com/search/search.html?q=
- Sony: http://ebookstore.sony.com/search?keyword=
- Amazon: http://www.amazon.com/s/ref=nb_sb_noss?url=search-alias%3Daps&field-keywords=
- BN: http://www.barnesandnoble.com/s/
- Goodreads: http://www.goodreads.com/search?q=
- BN: ?keyword=
- Amazon: See next step
Step 4. Get your affiliate code.
I’m only going to cover Amazon here because it is really the only affiliate program that earns me any money at all. I’ve been an affiliate for Kobo and BN for at least a couple of years and have made zero from either of them. I was told that a huge number of nook users purchase direct from the device and not through any links. This is perfectly fine with me and I believe in providing the links regardless, but that is the reason why I’m only going to cover Amazon here.
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.
DA’s affiliate code is dearauthorcom-20. To make the buy links work with the affiliate code, I add “&index=books&linkCode=qs&tag=dearauthorcom-20″ at the end. The index=books makes sure my search takes place for books and the tag is my affiliate code.
Step 5. Creating your Buy Link Generator Sheet
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 “Author, Title, Price” and the parts of the URL. I’ve tried to label the sheet clearly.
As you can see I enter every part of my html code because I don’t want to do anything in the future but change the variables: the Author, the Title, the Price (if I use the Price).
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. Also, you are free to copy my worksheet. I just protected each sheet so that if people were looking at the code, they wouldn’t inadvertently mess it up.
My columns are as follows:
- Title (variable): Title of Book
- Author (variable): Author Name
- Price (variable): 0.99
- Beginning Link: <a href=”
- End Links: ” target=”_blank”> (the “target blank” code makes the link open a new window)
- Begin BN Link: http://www.barnesandnoble.com/s/
- Middle BN Link: ?keyword=
- End BN Link: &store=allproducts
- Begin Sony: http://ebookstore.sony.com/search?keyword=
- Begin Kobo: http://kobobooks.com/search/search.html?q=
- Begin Amazon: http://www.amazon.com/gp/search?keywords=
- Amazon end: &index=books&linkCode=qs&tag=dearauthorcom-20″ (the “dearauthorcom-20″ is my affiliate code. replace it with your own)
- Goodreads: http://www.goodreads.com/search?q=
- Harlequin Begin: http://www.harlequin.com/catalogsearch.html?keyword=
- Harlequin End: &tab=items&vcname=Catalog_Search
- All Romance Link: (must hand code)
- ARE Affiliate Code: ?referrer=da357781 (My affiliate code. This is an invitation only program.)
- ARE, Search by Title:http://www.allromanceebooks.com/storeSearch.html?searchBy=title&qString=
You may have different columns than me which is perfectly okay. You may set up buy links for different retailers and never use them. The “button” stuff is the short code I use to make the pretty buy links like these for Easy by Tammara Webber (note TW’s books are not in Sony, Kobo, Harlequin, and ARE stores. This is just an example):AMZ BN Kobo Sony ARE HQN
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 “+”:
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 “manipulating your links” sheet, I put in all the various html code that I need from “(” to the “<p style=”align:center”>” or even “<li>”. This is because I want to reduce my repetitive tasks.
Here is my Amazon link:
=’Entry sheet’!D2&’Entry sheet’!K2&’Entry sheet’!A2&” “&’Entry sheet’!B2&’Entry sheet’!E2
- Start with an equals sign. This signals that the spreadsheet should look for a function.
- Entry sheet D2: <a href=”
- Entry sheet K2: http://www.amazon.com/gp/search?keywords=
- Entry sheet A2: Title
- ” “: that is a space between the title and author
- Entry sheet B2: Author
- Entry sheet E2: </a>
I hope this helps a blogger make one task a little bit easier. If you use my spreadsheet (and you are absolutely welcome to), please just give a link back to DearAuthor.com. If you have some facility with scripting and want to create a form for others to use, please do so. I’d love to hear your #bloggertips as well.
UPDATE: I’ve had so much spam on this thread that I had to close the comments. If you have a question, shoot me an email. Jane at DearAuthor.Com