Keyword Conversion: Figure Out Which Works


So my excel video did pretty good that I got emails to help with excel and keyword manipulation. I think I should get a job with the infomercial guy “The Video Professor!” So there were some emails on creating excel formulas on keywords and how to track and how to see conversion and all that stuff. Well Google Adwords gives you all that information, impressions, clicks, CTR, etc.

But I decided there were a lot of noobs out there that want to learn how PPC keywords work. There were emails where someone asked about how to determine CTR and CPC, I simply redirected them to the Google Adwords section or Wikipedia. What is the point of asking when you do not know the basics? I learned by reading and understanding how these functions worked, thus creating excel tools for my benefits.

Well since there are newbies out there that want to learn I decided to go ahead and create a simple Google Docs file for everyone to view and learn from. Everyone needs examples and actual tools to look at. Some people learn by looking while other learn by reading. I learn by taking what I read and just playing around and testing.

View Google Doc Sheets

So the above image is a snapshot of false data for each keyword that were used for a campaign. It shows the mathematical version of CTR, conversions, potential revenue, EPC, CPC, your profits, and percentage gains or difference. The only things you will need to fill out are the columns in yellow: Impressions, Clicks, Leads, Cost. Each of this information can be pulled from your Google Adwords campaigns.

From visually viewing this sheet you will be able to see the formula and see how it works. Sometimes Google jargon is hard to understand, so by giving you a live example with working formulas can show you what Google jargon is all about.

Another part of this sheet is I may also be on the sheet itself and we can group collaborate to help you better understand. This sheet will definitely bring users together and give each other a better understanding. Since this is public I will be able to see who is logged into their Google account and I will be able to chat will you via Google Docs and vice versa, you will also be able to interact with users as well that are viewing this specific sheet.

Video of some explanation of the spreadsheet use:



Impressions are what is implied, how many time was your ad viewable to the targeted audience. This number is important to determine CTR (click through rate).


From the impressions, how many of those were clicked on. This determine the CTR percentage, the higher the number the better. It shows your ads are well written and attractive to the end user.


I made this viewable on my end and decided maybe I should add this in as well. This part is not found within your Google Adwords account, it is base on your own research and tools. Some of my affiliate links are only connected to certain keywords with specific tags, so I can research these keywords.

So leads are simply the converted clicks, from those clicks how many of them bought or converted. Did those click sign up from your affiliate links? Did they subscribe? Did they purchase something?

This is just an extra feature I added in this spreadsheet, since I track specific keywords and maybe it would be beneficial for users that are just starting to learn PPC.


Cost is how much are you spending on a keyword. In this spreadsheet, I made it real general since you may be tracking it on a day to day basis, weekly monthly, etc. Cost is also listed within your Google Adwords account.

Another nice feature about this spreadsheet is the profit column changes colors between green if you are even or earned and red if you lost money. So it gives you a nice visual of what is going on with your money. This spreadsheet will also give you a total of your stats at the first row of the spreadsheet. The only downside I made on this is the file will only handle 40 keywords. If you are good at excel you can expand if you wish. But you should not be tracking all your keywords, just keywords you think that are competitive and if they convert or not.

Also, this spreadsheet is hosted on Google Docs and is open to the public for educational use; it is for your learning experience. If I find that this document is being abused in any way I will remove it, no questions. You will need a Google account to view this document as well. I hope this helps some readers out there to get a better understanding of PPC.

PS – I love Google Docs 🙂

Follow and Join Me: Facebook & Instagram

  • you can generate unique urls for every single long tail search for every single click!!!!!

    Takes tracking to a whole new level.

    on your landing page:

    1. use php to grab the search query
    2. add it to a DB with a unique id
    3. use php to create dynamic affiliate links on your landing page with that unique id

    Bamn, if you get a sale your report will show the exact database id to lookup and that will show you the exact LONG TAIL phrase that was used.

    for instance if you bid on “make money online” it will show you ANY variation that was used when the click was paid for and you track at that level. (such as “make money online in new york” or “make money online new years”)

    actually, even if you don’t get a sale you can still see the exact long tail keywords that you are buying and it helps to identify possible negative keywords.

    I’ve got the script already made but it’s real sloppy. If you are interested I can email the nuts and bolts.

  • Still waiting for your respons on this thread 😀

  • Great tips!!

  • I’m still learning the whole keyword thing this is some very good info! BTW..I am a new subscriber;)

  • Good tips!

  • Very well written post for newbies.

    I’ve stumbled the post 🙂

  • Hi Ian,

    Great post. The link to the Google Docs spreadsheet doesn’t work. I would like to show it to some newbies.


  • Ian,

    The link to the spreadsheet doesn’t appear to be working. I would like to direct some newbies to check it out.


  • Ian

    > Jamie
    the link should be working now! thanks for the heads up

  • Thank you for the sensible critique. Me & my neighbor were preparing to do some research about that. We got a good book on that matter from our local library and most books where not as influential as your information. I am very glad to see such information which I was searching for a long time.This made very glad