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:

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).

Clicks:

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.

Leads:

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:

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 🙂