If you own a small company and use or are considering using Google pay-per-click (AdWords) for your goods and services, this article is meant for you.
I’m including two practical Excel techniques in this article:
- How to write Google PPC ads using Excel.
- How to estimate profit/loss for a Google PPC campaign.
Fun Stuff – Writing Google PPC Ads with Excel
First the fun stuff. Seven years ago when I ran my one and only Google PPC campaign (a profitable one), I really didn’t enjoy writing Google pay-per-click ads . I hated counting the letters in the ads to meet Google’s criteria.
- Ad line #1: 25 characters.
- Ad line #2: 35 characters.
- Ad line #3: 35 characters.
I wrote the ads on scraps of paper when I had creative flashes. Naturally I lost some of the slips of paper and started using Microsoft Word. That’s how I wrote Google PPC  ads seven years ago.
Too late I realized I could have used Excel to write my Google PPC ads.
Whenever I type a new ad line or revise one, Excel calculates the number of characters in the ad line and tells me if it’s too long. If the length of the ad is OK, there’s no warning.
In column B I used an =LEN formula and in column C I used an =IF formula. Some people love Excel, some hate it, and some tolerate it. If you love Excel you can build your own spreadsheet like the one shown above. If you’d rather use my spreadsheet, you can visit my Excel site to download my Google AdWords campaign spreadsheet , which includes the PPC ad writing tool.
Hard Stuff – Estimating Profit/Loss for Google PPC
Starting a Google AdWords campaign without estimating the profit/loss of a test campaign is foolish. You absolutely need an Excel spreadsheet to make estimates.
Seven years ago I sold an ebook in 40 countries on how to use Excel. Years ago, my average CPC was 8 cents. Google’s Traffic Estimator tool now estimates that a click will cost me 75 cents. But absolutely no one is advertising for my chosen keywords so I’m hoping Google’s estimate is wrong.
Shown below, the colored cells are the most important to me.
- Baseprice – I’m selling a product at $20 that I hope people will renew on monthly. That’s why cell B7 is $20 and C7 is $60.
- Cost per Click – Even though Google estimates my CPC at 75 cents, I hope they’re wrong. If the cost per click  is 25 cents, my test campaign has a chance.
- Daily Budget – I’m willing to wager $20 a day for a few days to test the campaign. If it can sell profitably as a test campaign, I’ll advertise as much as I can up to $100 per day.
- Conversion rate – Years ago I converted at between 1 and 1.25%. It was an ugly little website that had no stickiness. All it had was a sales page that was done by me. Now that I know WordPress I’ve built a website that has value where there’s a greater chance a visitor will stick around and join my email opt-in list. Hope so. If it can just sell profitably, I’ll get a professional copywriter for my landing page(s).
- Daily net – Green is for money. Currently I estimate the test campaign will break even. If it breaks even, I have a chance to alter it and make a profit.
Will it run at a profit? Column B is a breakeven analysis. Column C is the blue sky analysis where my wife is shocked with income magically appearing in our checking account via PayPal.
If my new ebook and Google AdWords marketing is just 1/3 as successful as my Column C, you’ll hear shouts of joy from Chicago, IL, USA.
If you know Excel, reverse engineering my spreadsheet above won’t be hard. If you’d rather use mine, you can download my Google AdWords campaign spreadsheet here .
My Google AdWords PPC spreadsheet is for selling an ebook. My only cost was Google AdWords. If you’re selling new garages your estimates will be different. As a business owner with a team of garage builders sitting idly by, you have different costs to add to your spreadsheet. If you’re thinking of outsourcing a Google AdWords campaign to someone, then you need to add that cost structure to your spreadsheet. Google AdWords experts don’t work for free.
Google AdWords and Excel Are Like Peas and Carrots
Isn’t that the line from Forrest Gump? He and his girlfriend were like “peas and carrots.” If someone is advertising profitably with Google AdWords, somewhere there’s an Excel spreadsheet keeping score.
I’d like to thank Elisa Gabbert and the nice people at WordStream for helping me with this blog post. If you need copies of my Google PPC spreadsheets, just visit Your Excel Coach.
This is a guest post by Richard Kraneis, a frequent commenter and occasional contributor at the WordStream blog. Richard is a training consultant and online marketer. He’s offering his Microsoft Excel services at Your Excel Coach  and his just for fun blog is What is a Film Camera ?
(Read More: A Tutorial on How to Create Excel Bubble Charts )