Downloadable PPC Calculator (Excel File)

May 5, 2008: We have created a basic PPC calculator that is available for download (click on the “PPC Calculator” link to download the Excel file).

There are two worksheets in the file – one for B2B Lead Generation and one for E-Commerce.

Each calculator requires a series of inputs from you in order to calculate the primary metric – Target Cost Per Acquisition (in each case, B2B & B2C).

ppc_calculator_b2b.png ppc_calculator_b2c.png

You can use the calculators to focus on an entire program, a specific campaign, a specific ad group, or specific keyword.

Here are some general notes for input fields (notes specific to B2B and B2C are below):

  • Impressions – Estimate the number of impressions based on either keyword research or your past history with Pay Per Click
  • Click Through Rate – Use this field to make estimates for the CTR that you expect, or use your past PPC history. Note, whenever someone asks us “what is a good click through rate”, our response is typically “it does not matter. ROI is what matters”. Obviously you do want to improve CTR, but only when the economics of your program are in line (i.e. work on improving CTR once you know your program is profitable)
  • Avg. Cost/Click – Use this input field as a method for gauging how much you should spend on a per-click basis.
  • Avg. Sale – In both B2B and B2C you may want to incorporate the Lifetime Value of a customer into this value (e.g. repeat purchases, service contracts, etc.)
  • Gross Margin % – If your company does not focus on Gross Margin (e.g. consulting, online subscriptions, etc.) you can simply leave this field blank.
  • Operating Overhead % – I like to incorporate a value in PPC profitability calculations that takes us as close as possible to “true” profit. In many cases I have seen Pay Per Click models that do not attempt to take the analysis to this level. If you choose to go this route, you can leave the field blank and simply look at Gross Profit Contribution.

Here are some notes for input fields for the B2B calculator:

  • Conversion Rate – Clicks to Leads (All) – The use of “leads” in this level of the calculator is based on all of the conversions you generate with users filling out forms (e.g. Contact Us, White Paper, Free Analysis, etc.).
  • % Qualified Leads – Use your offline, Customer Relationship Management (CRM) data to estimate what percentage of the raw “leads” are qualified once they enter the pipeline.
  • % Conversion from Qualified Lead to Sale – Use your offline, Customer Relationship Management (CRM) data to estimate what percentage of Qualified Leads are closed.

Here are some notes for input fields for the B2C calculator:

  • Conversion Rate – In e-commerce, a “conversion” is typically a sale. If you have additional conversion metrics such as e-newsletter subscriptions, you may want to create an additional calculation model that analyzes the conversion rate to subscriptions and then takes e-mail metrics to analyze revenue streams from e-mail.

If you have any feedback on the calculators, please feel free to comment below (and let me know if you find any errors!).

Related Posts:

  • http://canoe.ca Protheus

    in B2C cell B29 you should use the following:
    =IF(B20=0,”-“,B26/B20)

    To eliminate those DIV/0 errors …

    Nice sheet thanks

  • http://www.komarketingassociates.com/ Andy Komack

    @Protheus – thank you! Very helpful. I will be uploading a revised version of the file that uses this expression to eliminate the various DIV/0 error messages.

  • Pingback: Blog News Roundup – 5/7/2008 | The Adventures of PPC Hero()

  • http://www.komarketingassociates.com/ Andy Komack

    Note – there was an error I discovered in the calculation for Target Cost Per Acquisition. I have fixed this and re-uploaded the file.

    I had to make sure that the current Cost Per Acquisition was added to the profit/deficit per action.

Contact KoMarketing Associates

If you have questions or would like to talk with us about our online marketing services, call us at 1-877-322-2736 or use our contact form to get in touch with our leadership team.

katie-meurin

"With little digging, you can see that KoMarketing’s team is frequently educating the SEO industry on best practices through articles, blogs and presentations. If you’re considering hiring an agency you want talented people collaborating to bring you the best search results possible – that’s what you’ll get with KoMarketing." – Katie Meurin, Director of Marketing at Zco Corporation

Subscribe to our free quarterly newsletter to get the latest recommendations for B2B SEO, PPC and social media marketing: