When you have a large keyword list, you may find yourself facing the daunting task of creating a tracking link for each of those keywords. After all, you need to know which keywords are profitable and which are sucking up your PPC funds needlessly. Depending on the format of the tracking link, there is no need to do this by hand. You can quickly create your tracking links using a spreadsheet program, such as Microsoft Excel.
1. Copy and paste your keyword list into a new spreadsheet, in Column A. This could be anything from a handful of keywords to five thousand keywords or more.
2. You may need to clean up your keyword list depending on what characters the tracking links support. If you cannot use spaces, for example, you will need to remove the spaces, or insert a dash, or insert whatever character or text you personally prefer. To remove spaces, highlight the column and do a “find and replace”; put a space in the “find” field and leave the “replace with” field blank, and select “replace all”. To replace the spaces with a dash, put a space in the “find” field and put a dash in the “replace with” field. Your entire keyword list will now either have no spaces in each keyword or a dash where the spaces were.
You may have other odd characters in your keywords that are not supported by the tracking links, such as / or @ or # etc. Simply “find” the particular character and leave “replace with” blank.
For example, my keyword list in the example now has dashes instead of spaces:
3. Next, determine the tracking link format for the particular affiliate program you are sending traffic to (or the format of whatever type of tracking link you are using). This will have a landing page and your affiliate ID code, as well as space to insert your keyword, if the particular affiliate program supports keywords. Unfortunately, there are examples of tracking links that will not work with this method, depending on which characters the link contains, if they are similar to characters that the spreadsheet program uses in its formulas. You will be able to see right away if it works or not.
For example, if my website ScienceLives.com had an affiliate program and my affiliate ID was 8888, the tracking link might look like:
4. Now it is time to add the first tracking link. In my example, in cell B2 I would enter the formula:
When I hit enter, the cell now contains the tracking link for the keyword in cell A2. In my example, it is:
If the tracking link has other characters after the keyword field, for example:
Then the formula should look like:
The keyword will appear in the appropriate place between the affiliate code “8888” and the modifier “blue”, for example:
5. To fill in the rest of the tracking links, click on cell B2 and then double-click on the square in the lower right corner of the cell. You should now have tracking links extending down the entire length of your keyword list.
6. The next step is to clean up your list so you have actual tracking links and not the formulas. If you submit spreadsheets with formulas into the search engines, you may have undesired results. Copy the list of tracking keywords and paste them into the appropriate column of your original keyword list (or create a new spreadsheet with your original keywords copied in). Make sure you select “paste special” and “values” to remove any formulas.
7. In the future, you may want to send traffic to a different landing page. For example:
Simply highlight the tracking links column and click on “find and replace”. Put the original landing page, sciencelives.com, in the “find” field and put the new landing page, sciencelives.com/biology, in the “replace with” field, and select “replace all”. All your tracking links should now have the new landing page along with their existing keywords.
Now whenever you create a new search engine campaign or switch landing pages, you can save yourself time that you might have spent typing your tracking links in by hand. Spend your saved time tweaking your bids and making money instead.
About the author: Suzanne Currie has been a webmaster since 1995 and has worked in online marketing since 2001. She currently lives near Toronto, Canada.
The Affiliate’s Dictionary – Guide to the affiliate and online marketing terminology you need to know.