How do I find real trends on Google numbers?

Using Google Analytics and Google Sheets, you can set up a dashboard to see search terms that people are looking for more than usual. By filtering out searches that are popular all the time, you can focus on whats new or noteworthy.

We use this to keep an eye on topical and seasonal user needs on GOV.UK, so that we can react quickly to rising searches and make sure people can find relevant information.

Ill show you how I made this dashboard, step by step, so that you can build your own version or adapt it to your needs.

Heres one I made earlier

To save time, open this ready-made trending searches spreadsheet and go to File > Make a copy to save an editable version. Then you can change the Google Analytics view ID and customise the configuration.

Im looking at internal site search terms here, but you could use the same method for external search engine keywords that brought people to the site (taking into account that up to 90 per cent of keywords are not provided), or for trending content pageviews.

Install the Google Analytics add-on

If you dont already have the Google Analytics spreadsheet add-on installed, go to Add-ons > Get add-ons to add it to your spreadsheet.

Set up daily search reports

If youre starting from scratch, create a new spreadsheet in Google Sheets, then go to Add-ons > Google Analytics > Create new report. In the Create a new report sidebar, name your first report Today, and set your Google Analytics account and view (profile). Select the Total Unique Searches metric and the Search Term dimension.

Click Create Report and the Report Configuration tab will appear, with those details filled in. Set the Start Date and End Date to today and remove 7 from the Last N Days row. Set the Sort option to -ga:searchUniques to sort by the number of searches (descending), rather than alphabetically by search term. You can also add filters or segments if you want.

To test your first report, go to Add-ons > Google Analytics > Run reports. If its successful, a tab called Today will appear, with todays top 1,000 search terms so far. (You can change this number with the Max Results configuration option.)

If your site doesnt get many searches, you might prefer to ignore today and start with yesterday for a full days data, or compare weeks instead of days.

Now you can add more reports, for each day of the past week, and for the previous month as a comparison. Copy the Today settings from column B into columns C to L, but set the start and end dates to yesterday, 2daysAgo, 3daysAgo and so on up to 8daysAgo, and give each column a corresponding Report Name.

In columns K and L Ive used 28daysAgo and 56daysAgo, so that we can compare the current searches with what was happening 4 weeks and 8 weeks ago. You can customise these to whatever works best for your content, or leave them out completely.

Once youve set up all the columns, run the reports from the Add-ons menu.

Combine the daily data

The next step is to mergedata from all these reports into one sheet, on a new tab (which Ive called Combined). Leave column A empty, as well be using it later on.

Column B collates the search terms from today and yesterday. Theyre case sensitive by default in Google Analytics, so well make them all lower case and remove any duplicates, using this formula in cell B2:

Then look up the number of searches made each day for each of those search terms. Starting with todays searches in cell C2:

For each row, this formula looks through column A of the Today report, finds the search term from column B of the Combined tab, and returns the number of searches from column B of the Today report. It adds together any variations with different capitalisation (for example, contact, Contact and CONTACT).

Make a column for each of the reports you created earlier. So in cell D2, put the same formula with Yesterday instead of Today, and so on from E2 onwards. Youll need to put single quotes around any report names containing spaces, for example: '2 days ago'!$A$16:$A.

Put corresponding column headers in the top row:either 'Today' and so on, or you canshow the actual report datesusing =Today!$B2 in column C, then =Today!$B2-1 to calculate the day beforein column D, and so on (and then customise how the dates are displayed atFormat > Number).

Optional extra step

For more accurate results you could calculate the percentage share of searches, by dividing the number of searches for each term by the total searches that day (taken from cell B12 in each daily report), as in the formula below. This evens out any big changes in traffic on different days of the week. Ive left out this step in the example spreadsheet though, to keep things simple.

If you do use the share of searches, youll still need two columns with the actual number of searches for today and yesterday as well, to include in the dashboard later.

Filter the trending searches

Now comes the clever part, using the QUERY function to select just the search terms that have increased recently.

On a new tab (which Ive called Filtered), in cell B1 well pull in all the rows from the 'Combined' tab where the today column has more searches than yesterday (C > D), or today or yesterday have more than the corresponding day last week (C > J or D > K), or more than 4 or 8 weeks ago (L or M):

Then we can sort these results by the number of searches today and then yesterday (order by C desc, D desc), or by both added together (order by C+D desc). And we only need up to 100 results (limit 100), or however many you want to show on your dashboard.

Well also set a maximum number of searches for 4 and 8 weeks ago (for example, L <= 100 and M <= 100), to remove search terms that were already popular. You can find a suitable threshold by trial and error, looking at the number of searches for ever-popular terms that you want to filter out. (If youve used the share of searches instead, youll need numbers like L <= 0.001, which means 0.1 per cent of searches.)

Similarly, you could set a minimum number of searches made today (for example, C >= 10), to leave out insignificant data, particularly when youre looking at the dashboard early in the day while the numbers are still low.

So the complete formula in cell B1 looks something like this:

Add sparkline charts

Sparklines are miniature charts contained in a single cell. For each search term you can add a timeline showing the daily searches over the past week, to see at a glance whether searches have increased steadily or suddenly, or have already peaked.

This formula takes the values from columns C to J (from today to 8 days ago), but reverses them right to left (rtl) so that the oldest date is on the left of the chart. We can also change the line colour and make the line a bit thicker. Wrapping it with IFERROR means that if theres no data in that row, the sparkline cell will be blank instead of showing an error message.

Youll need to copy the formula down to the rest of column A.

If youd prefer bar charts, set the chart type to column. You can also highlight the highest value in a different colour or shade using the highcolor option.

Bear in mind that weve only taken the top 1,000 search terms each day, so if some days show no searches it doesnt necessarily mean zero, it just means the search term wasnt in the top 1,000 on those days.

Design the dashboard

The finishing touch is to display the filtered results more neatly on another tab, without all the daily figures.

First, use a simple =ARRAYFORMULA(Filtered!B2:D101) formula to copy the trending search terms and their number of searches today and yesterday from the previous tab, and then do the same for the sparkline column.

To make the dashboard look nicer, you can increase the font size, turn off the grid (untick View > Gridlines), and shade alternate rows using conditional formatting with a custom formula: =ISODD(ROW()) or =ISEVEN(ROW()).

To add the last updated time at the top, you can take the Last Run On time from cell B2 in one of the reports, then simplify the date and time format using the TEXT function:

You can also show the overall top searches list alongside the trending searches. Here were taking the top 100 search terms from the combined list of today and yesterday, sorted by the number of searches, and removing (other) if any data has been rolled up:

You could publish the dashboard to the web to share it more easily with other people or display it on a screen, but unfortunately the sparklines are blank in the web version.

Schedule regular updates

You can set the analytics reports to run automatically at Add-ons > Google Analytics > Schedule reports. If youve included todays data, youll want an hourly running total; otherwise daily reports will be enough.

The first report will run randomly during the time slot youve set, and after that the reports will run at the same time every hour or day.

To refresh the data manually, go to Add-ons > Google Analytics > Run reports.

Feedback

If you have any questions or suggestions, or youd like to share your experiences of customising this dashboard, then please get in touch in the comments section on this page.