From keyword research to link building, an SEO will spend the majority of their working day with data. Manipulating and interpreting data is fundamental to SEO data analysis. The process of analysing data can be lengthy but can be sped up with just a few simple data handling hacks.
I’ve put together a list of some of the Google Sheets tips that I find most useful. Saving potentially anything from milliseconds to hours at a time, I hope the following tips help improve the way you manage your data.
Quickly backdating data from Google Analytics
Want to quickly gather data for individual landing pages as shown here? Follow the steps below to speed this up and save hours browsing between different dates for different pages.
For this example, I want to grab the sessions data for Men’s T Shirts on the Google Merchandising Store over the past 12 months. To do that, head to GA – Account – View – Behaviour and then Landing Pages. I then searched for “shirts” and selected the top performing Men’s T Shirt pages from the past year.
From this stage above, click “Plot Rows” to import the relevant data into the graph. Your Analytics should look like the following image. From this point, you’re ready to export your data.
This image shows a typical landing page export. The first block of digits is a breakdown of the statistics for all of the landing pages in the filter (very useful) – however, we want the individual session figures for each page which are displayed below and marked out in the following image.
With every export, the cumulative sessions for all landing pages are displayed in this first block of figures. The next boxes, marked 1, 2 & 3, show the top three landing pages, in the same order as selected in Analytics.
To show how we at Distinctly might import this into a data set, I’ve created an import template for anybody to use here.
Simply copy and paste the data as follows into your spreadsheet, or into the cell Q6 in our template. From here, the data automatically transposes into our table, thanks to a =transpose formula in C6. However, this can also be achieved by copying the data range again and hitting: Right Click – Paste Special – Paste Transposed.
Note: this trick does not work across spreadsheets at the moment on Google Sheets, so copy and paste the data from within the same spreadsheet to transpose.
Once you’ve got your data, there are a number of useful tricks to quickly manipulate it. Here are some of my favourites.
Google Sheets Tips
Navigating Quickly: Ctrl + Arrow
The Ctrl button activates the majority of the most useful and common Google Sheets keyboard shortcuts. This first tip simply helps quickly navigate the selected cell. By holding Ctrl and navigating with the arrow keys, you quickly jump blocks of cells which have been filled to the next empty cell. By holding Shift while at the top of a data range, the combination highlights this block of filled cells.
Upgrading Copy + Paste
One of the first things you’ll ever have learned on a computer is how to copy and paste to your clipboard. However, with a few amendments, you can save time needlessly re-formatting data.
Paste Values Only: Ctrl + Shift + V
Instead of choosing whether to paste formats or values, by clicking the box in the bottom right corner, simply use this shortcut to paste as a value only.
Paste Formatting Only: Ctrl + Alt + V
You may already know of the paint format button, but this saves further time and doesn’t overwrite your current values or formulas.
Copy Formulas Down/Right: Ctrl + D / Ctrl + R
Select the range of cells you wish to replicate in another cell at the top of the range and hit the keys above. Depending on whether the cell has a formula or a value, this exact format is what is copied across the range.
Clear Formatting: Ctrl + \
To quickly remove any colour, or text style amends, use this to revert back to Sheets’ default settings.
Inserting the Correct Range into a Chart
Before editing a data range in a chart, get your data range together to simply copy and paste it in. The best way to do this is to head to a different sheet to that of your target range and open a formula wherever convenient by hitting “=”.
From this point, use your mouse to click onto the correct sheet and highlight the data range. Then hit “Enter” on your keyboard so that the formula reads something along the lines of “=Sheet1!B25:B26”. Where Sheet 1 is the sheet name, ! denotes the end of the sheet name and B25:B26 denotes the data range.
If you found this informative, you can also check out my bank of Google Sheet tips here.