See Unique Variables where CTR = 0 | Community
Skip to main content
C
cgarzi11
Level 2
July 6, 2018
Solved

See Unique Variables where CTR = 0

  • July 6, 2018
  • 12 replies
  • 7120 views

I have a variable dimension that shows all the search terms used on my intranet. I also have a calculated metric that shows clicks on search results divided by searches for that term (CTR).

I'd like to be able to see all search terms that have an absolute CTR of 0 in my workspace. Whenever I use the If/Then or Equals calculated metrics, though, it shows me any query that got 0 clicks at least once. So for example, if there are 1,000 instances of searches for Google, but in only 999 of those instances did the user click on search results, it will still show me the search term of Google in my workspace because of the one time there wasn't a click for that instance of the query. Meanwhile, I'm trying to see all search terms that resulted in absolutely no clicks (as an example, 1,000 instances of searches for ABC and in 0 of those instances were there any clicks).

Does anyone have any ideas? Appreciate the help!

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Gigazelle

    The only thing I can think of would be to just pull CTR by search term, sort it ascending, then count the number of terms that have a zero value. You might even be able to automate it using a combination of Report Builder and Excel formulas.

    Bring in the top few thousand keywords (enough that you know you have all of the CTR 0 values), then make a formula in excel that takes the CTR column and counts the number of cells with a value of 0. Then, whenever you need that number, open Excel and refresh the workbook.

    I know it's pretty hacky, but hey, it should get you your data

    12 replies

    Show previous replies
    Gigazelle
    Adobe Employee
    GigazelleAdobe EmployeeAccepted solution
    Adobe Employee
    July 13, 2018

    The only thing I can think of would be to just pull CTR by search term, sort it ascending, then count the number of terms that have a zero value. You might even be able to automate it using a combination of Report Builder and Excel formulas.

    Bring in the top few thousand keywords (enough that you know you have all of the CTR 0 values), then make a formula in excel that takes the CTR column and counts the number of cells with a value of 0. Then, whenever you need that number, open Excel and refresh the workbook.

    I know it's pretty hacky, but hey, it should get you your data

      C
      cgarzi11Author
      Level 2
      July 13, 2018

      Thank you-- I will try report builder/excel, because I have already tried to count the keywords. There are so many that don't have a CTR of 0, and so many that do have a CTR of 0, that even showing 400 keywords at a time and going to page 500, I then need to manually go forward one page at a time and it takes forever. Plus, I want to be able to sort keywords that have a 0 CTR by number of searches for that term, that way I can focus my efforts on resolving high-volume zero click searches.

      Terms & ConditionsAccessibility statement

      Loading footer...