The Ad Relevance Excel Tool - FREE Download

Having relevant ads is essential in getting customers to your site. Due to the sheer number of unique search queries, it is very likely that many of your search queries do not appear in your ads at all. The potential customer whose search query does not appear in the ad is less likely to find your ad relevant and therefore less likely to click on your ad.


Our Ad Relevance Excel function reviews your keywords or search queries and compares them to the text in your ads and calculates how relevant the ad is to your keyword or search query.


For example, the search query "organic dog food" compared to the ad text "Best Quality Dog Food" would have an Ad Relevance score of 66% since only two of the three words show up in the ad text.  This will allow you to determine where in your account you should focus writing new ads to help boost click through rate.


Here is the syntax for the AdRelevance function:

=AdRelevance("keyword or search query","ad text","stop word array")


queryminer AdRelevance Excel Function syntax


As you can see from the image above, you can score a keyword across the three columns that make up the Headline, Description Line 1, & Description Line 2.  You also have the option to have the AdRelevance function ignore stop words.


You can also use the AdRelevance function to score your ad groups using a "weighted AdRelevance score".  This technique allows you to roll up all of your keyword or search query scores to the ad group level weighted by impressions using a pivot table.  This is very similar to how you calculate a weighted average position when analyzing your PPC data.


In order to score your ad groups you will need to add a column to your keyword or search query report (Impressions*Ad Relevance Score).  This new metric can be used in a pivot table calculated metric to create a Weighted Ad Relevance = ((Impressions*Ad Relevance Score)/sum(Impressions)).


weighted ad relevance score


The example above shows the ad group "Blue Dog Food" having a Weighted Ad Relevance Score of 53.22% while the average is 47.78%.  This is because the keyword "blue dog food" has a higher score and a large share of the impressions.  This type of insight will help you determine which ad groups you need to make a priority.


Click here to download our new AdRelevance Tool for free.  We hope you find the tool useful and look forward to your comments.  Thanks!