When my keyword analysis brings up a long list of keywords to target, Banner Design I like to set up a “Summary” tab using the “SUMIF” and “AVERAGEIF” formulas to get an overview of the opportunities. Using SUMIF to sum cells with specific text Notice that there are no impressions, clicks, and other data for keywords around “Seattle”? Advertising Continue reading below These are the terms of opportunity that I would like to target for a new project. Now that I see we don't have Banner Design current data, I need to calculate what our potential clicks might be. This is where the average click-through rate data we collected will come in handy. 6. Estimate clicks from volume data We know what the estimated impressions will be (from our keyword analysis using 'volume' data), so we'll use the 'SUMIF' formula to calculate the 'estimated impressions'. Using SUMIF to Find Keyword Impressions
The rest of the data here is where we need to Banner Design reverse our calculations. Advertising Continue reading below Basically, using a proposed average position to estimate CTR data and then calculating what our estimated clicks would be. However, we do not know Banner Design what the average position is currently. This is where we have to use our best judgment to determine where we think we can rank for these terms. In this case, the terms I want to target don't have much competition, and the domain authority of the site I'm working on is pretty strong for local searches, so I think I can realistically get third position for these terms. Estimate average position when calculating Banner Design ROI for SEO In our average CTR data, the 3rd position typically sees a CTR of 6.7%. Average CTR for SEO in 3rd position Using the formula “
AVERAGEIF”, we will find the average click-through rate for Banner Design the 3rd position.using the AVERAGEIF formula to find the CTR for the average position. By using the formula here (instead of just typing it in or using a VLOOKUP), we make it easy to edit this sheet later for future estimates. Advertising Continue reading below If I want to see what the estimated clicks Banner Design would be for a 2nd position or maybe a lower position like 5th then I just change the '3' to '2' or '5' and the numbers will automatically update. Now we just need to multiply our impressions by our estimated CTR to get the estimated clicks for our particular project.