Jun 25, 2022
In General Discussions
Once you have all of your keyword data populated in Keylime Toolbox, you will want to download 12 months of all your keywords and data into a CSV. I like to copy and paste Banner Design the data into Google Sheets so I can easily share it with my team, but you can also use Microsoft Excel. The most important data for the purpose of this exercise is click-through rate (CTR) and position (average ranking position). Download all Keylime Toolbox keyword data Next, we will set up Banner Design a sheet that contains the average CTR based on the average position using the “AVERAGEIFS” formula to find the position and average the CTR across all keywords. Advertising Continue reading below It should look something like this: CTR of keyword data based on average Banner Design position formula When targeting a specific set of terms (e.g. brand terms), I like to break down the average CTR by position for those terms. Using the "AVERAGEIFS" formula, we can focus on the average CTR for the specific Banner Design position for any term, including "vashon" or "things to do". CTR data based on average position and terms This is especially useful because branded CTRs tend to be higher than non-branded searches for most organizations. Advertising Continue reading below 4. Identify Keyword Gaps and Opportunities Now that we have our identified keyword opportunities from our keyword analysis, we need to see: What keywords we are currently ranking for. Where we rank. How many impressions we see. Banner Design What is CTR. And finally how many clicks. Using the same Google Sheet set up with current keyword data (exported from Keylime Toolbox), CTR data we calculated (in separate tabs). How to calculate ROI for SEO Banner Design when targeting a set of keywords We will use the tab that contains our keyword analysis (keywords and volume) in a new tab (I called it “Keywords to target” in my example here). Advertising Continue reading below In the columns next to “Volume” I added Current Clicks, Impressions, CTR, and Average Position. Using the SUMIF and AVERAGEIF formulas, I set up a search for each term to get a view of Banner Design how each term is currently performing. Keyword Gap Analysis Note that all of this should be an average number per month of 12 months of data. So your impression totals in the top positions should be rounded up or down to match the “volume” numbers.