Trying to create a summary of Top Search terms by term by date

[lkm64]

Hi,

I am pulling data down from Google Analytics which include site search terms and the date of the search. The data is in multiple dimensions/metrics and i want to add the 'Top Search Terms by Date' to my summary page.

For example, for January 22nd I want my summary to include the usual metrics then show the top 10 search terms for each day in descending order.. My company is in the travel business and the search terms will be cities or resort names.

I have attached a sample workbook. On the detail page is where i dump the GA data in columns A-G which is followed by a series of formula keys created to help with other tabs pulling the info.

I setup the key in column P to be a combination of the search term + day and column Q is the total times that search term has been used that day.

On the summary tab i want to show the top 10 search terms for each day of the report.

Any help is sure appreciated.

Thank you very much!

 

[Nebu]

Hi:

Hope this is what you are looking for I had to use an helper column and pivot to get the result.

Thanks

 

[lkm64]

Hi:

Hope this is what you are looking for I had to use an helper column and pivot to get the result.

Thanks

Hello Nebu,

That is awesome! I would love to hear/read an explanation of the logic to fully 'get it'. I am good with advanced "basic" formulas (an oxymoron to be sure)...but when it comes to this i am a true novice but want to learn it.

This is definitely something i can use....and will reverse engineer it for other uses.

I really thank you!

Loren

 

[Nebu]

Hi:

Glad that it helped you , the trick is to multiple the count with a very small value so that it will give you the top 10 searches even if the count of some searches are same you can see the formula I have given in the helper column to comprehend the logic better.

Thanks