Downloadable PPC Calculator (Excel File)
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).
 |
 |
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: