![]() Starting with today’s searches in cell C2: =ARRAYFORMULA(SUMIF(Today!$A$16:$A, $B2:$B, Today!$B$16:$B))įor 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. Then look up the number of searches made each day for each of those search terms. They’re case sensitive by default in Google Analytics, so we’ll make them all lower case and remove any duplicates, using this formula in cell B2: =UNIQUE(ARRAYFORMULA(LOWER())) Leave column A empty, as we’ll be using it later on.Ĭolumn B collates the search terms from today and yesterday. The next step is to merge data from all these reports into one sheet, on a new tab (which I’ve called ‘Combined’). The report configuration options (click the image for a bigger version) Combine the daily data Once you’ve set up all the columns, run the reports from the Add-ons menu. ![]() You can customise these to whatever works best for your content, or leave them out completely. In columns K and L I’ve used ‘28daysAgo’ and ‘56daysAgo’, so that we can compare the current searches with what was happening 4 weeks and 8 weeks ago. 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. Now you can add more reports, for each day of the past week, and for the previous month as a comparison. If your site doesn’t get many searches, you might prefer to ignore today and start with yesterday for a full day’s data, or compare weeks instead of days. (You can change this number with the Max Results configuration option.) If it’s successful, a tab called Today will appear, with today’s top 1,000 search terms so far. To test your first report, go to Add-ons > Google Analytics > Run reports. You can also add filters or segments if you want. Set the Sort option to ‘-ga:searchUniques’ to sort by the number of searches (descending), rather than alphabetically by search term. Set the Start Date and End Date to ‘today’ and remove ‘7’ from the Last N Days row. Creating a new reportĬlick ‘Create Report’ and the Report Configuration tab will appear, with those details filled in. Select the Total Unique Searches metric and the Search Term dimension. In the ‘Create a new report’ sidebar, name your first report ‘Today’, and set your Google Analytics account and view (profile). If you’re starting from scratch, create a new spreadsheet in Google Sheets, then go to Add-ons > Google Analytics > Create new report. ![]() If you don’t already have the Google Analytics spreadsheet add-on installed, go to Add-ons > Get add-ons… to add it to your spreadsheet. I’m 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. ![]() Then you can change the Google Analytics view ID and customise the configuration. To save time, open this ready-made trending searches spreadsheet and go to File > Make a copy… to save an editable version. The trending searches dashboard Here’s one I made earlier I’ll show you how I made this dashboard, step by step, so that you can build your own version or adapt it to your needs. 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. By filtering out searches that are popular all the time, you can focus on what’s new or noteworthy. Using Google Analytics and Google Sheets, you can set up a dashboard to see search terms that people are looking for more than usual.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |