WP Search Insights is made to provide you a great amount of information and important insights in search behavior on your WordPress website. While our dashboard can be very conveniënt to keep an eye out for search behavior on a daily basis, it can be convenient to share an Excel export with third parties or to do some deeper analyzing using Excel. Below we will provide you with five tips on using Excel to analyze your WPSI export.
How to export your WP Search Insights data to .csv
Let’s start with the beginning, exporting the data from WP Search Insights. You can do so by clicking “settings” and go to “Data”. Here you can download the specific date range export in CSV.
View and edit your CSV file in Microsoft Excel
If you open your downloaded .csv file you will see the raw data from WP Search Insights in a comma-separated format. You can convert this data to a regular Excel view by folowing these steps:
- Select column A (which contains the data)
- Navigate to Data -> Text to columns
- In the pop-up, select ‘Delimited’ and click ‘Next’
- Select ‘Comma’ and click ‘Finish’
You will now see your data is a classic Excel view. Follow these steps to further enhance your possibilities with the Excel data:
- Select the entire sheet using the
button on the top left of the screen - Doubleclick on the border between two columns (eg. A and B). Excel will automatically adjust column with to your data
- Next, navigate to the ‘Data’ tab and click on ‘Filter’. This will add filters to the first row of each column. These will allow you to show all data for specific criteria and sort data as desired.
Tip 1: Monitor searches from specific pages
If you have followed the above-mentioned steps, you will be able to select a specific page. In our example page, we are curious where visitors on our checkout page are searching for. Obviously this is very interesting, as this might indicate why some visitors don’t complete their checkout.
To apply the filter, click on the ‘From’ header -> deselect all -> select ‘/checkout’
As you can see in the screenshot below, on our example website adding a contact section or customer service chat widget would be worth considering.
Tip 2: Adjust your spam filters
Using the built-in spam filter from WP Search Insights is key to gathering valuable information about search behavior on your website. Using the CSV/Excel export can be a fast and easy way to see if there are ways to optimize your search spam filter.
Short searches
WP Search Insights enables you to exclude searches with a number of characters below a specific value. To discover if you need to adjust this number, follow these steps:
- we will use the first empty column (probably column E) to determine the number of characters per search. Name this column ‘Characters’
- In the second row of this column, type the following formula: =LEN(A2) This cell will now display the number of characters from the search term in cell A2.
- Hover to the bottom-right of the cell containing your formula (in most cases E2) and double-click when your mouse turns into a plus. Column E will now be filled with data about the number of characters of all recorded searches.
- Select the column (E in this example), right click + Copy, right click + ‘Paste Special..’
- In the pop-up, select ‘Values’ and click ‘OK’. This removes the formula from the cells, which is more convenient for further sorting and filtering.
- Now select the header cell of this column (probably E1), navigate to the ‘Data’ tab and click ‘filter’.
- Sort this column ascending using the newly added filter.
As you can see in our example below, it could be wise to adjust our spam filter to ignore searches with less than 3 characters.
