SERPs.com Google Sheets Add-on

The SERPs.com add-on for Google Sheets lets you import your ranking data automatically into a Google Sheets spreadsheet.

This plugin is still in beta and there may be some bugs, so we invite you to give us feedback in the feedback form below.

Read below for instructions on how to install and use the add-on.

Install the Add On

Get the add on from the Chrome Web Store.

Screen Shot 2016-05-17 at 1.36.12 PM

A new spreadsheet will be created and you will be prompted to grant permissions to the plugin.

Screen Shot 2016-05-17 at 1.38.15 PM

Then go to “Update Credentials” under the SERPs.com menu and enter your email and password. Note: you have to be an admin user on the SERPs account to use this add-on.

Screen Shot 2016-05-17 at 2.13.18 PM

Hit “Save Credentials”, and you’re ready to start creating reports.

 

Create Reports

If you’ve ever used the Google Analytics Sheets Add On, you’ll be familiar with how we create and run reports. Reports can be configured manually, but it is far easier to use the “Create Report” form which is found at “Add-ons” > “SERPS.com” > “Create Report”.

Each new report is added as a column in the “SEO Report Configuration” sheet which will be automatically created the first time you run the add-on.

This setup allows you to create multiple reports, either from different sites on your SERPs account, or with different report settings.

 

The “Create Report” form needs to load a list of your sites via the API, so make sure you have already entered your credentials (otherwise there will be an error).

If you have a lot of sites, it might take 20-30 seconds for the form to finish loading the first time.

 

A note for very large accounts:

Accounts that track over 3000 keywords can still use the Sheets plugin, but may run into some of the limitations that Google Sheets imposes. The first is that Google Sheets spreadsheets are limited in their maximum number of cells in a single sheet, so some reports (especially the basic keyword report) may return more data than the spreadsheet can hold. In this case, you should try adding in more filters or using an aggregate report, which reduces the amount of data inserted into the report sheet.

Secondly, reports set to update automatically may run into the 6-minute execution time limit that Google places on add-ons. For large accounts, try limiting the number of reports setup on a single spreadsheet. We may try to add functionality to get around this limitation in the future.

Report Settings

Screen Shot 2016-06-21 at 11.21.59 AM

 

Report Name: this will be the name of the sheet in which the results of your report will be placed. This should be unique and descriptive. If two reports share the same name, the second will override the first’s output sheet.

Select Site: choose the site from your account that you wish to pull ranking data from.

Select Locale: select which country’s results you wish to pull. Remember this will need to line up with how the site is configured in your SERPs.com account.

Search Engine: Google or Yahoo

Device: filter between mobile, desktop or tablet rankings, or leave set to “all” to pull all rankings.

Date Fields: You can get up to 4 different ranking data points per keyword:

Latest: the latest ranking for that keyword – which may be today or the day before depending on when the add-on is run

Yesterday: the day before the current day

Last week: 7 days ago

Last month: 30 days ago

Remove Unranked: Not all keywords in your account may currently have ranking data. While this won’t be an issue for smaller accounts, larger accounts may wish to filter out keywords for which the site doesn’t rank in order to limit the amount of data returned.

If the site has ranking data for any of the date fields, it will still be included, even if it has subsequently fallen out of the rankings.

Report Types

The add-on can create two types of reports from your ranking data:

Keyword Report

The keyword report returns all keywords that fit your report criteria.

Aggregate Report

An aggregate report groups ranking data by either tags or URL. This can be particularly useful for larger accounts. The report will contain average rank columns for each of the selected date fields. However averages can be very deceiving in ranking data depending on the number of keywords tracked and the size of the spread of rankings.

This is why we use “rank groups” to count the number of keywords that are ranking between two positions and then add them to the report for each date field as both the absolute count of keywords in that range and the percentage of all the keywords in that tag or URL that fall within the specified range.

Screen Shot 2016-06-21 at 11.27.49 AMFilters

 

Filters allow you to filter your report data based on the values of specific field. Text and numeric fields have corresponding conditions (e.g. RegEx or greater-than/less-than) that can be applied, while the tags field lets you enter a list of tag terms from which to filter.

 

Output Settings

Format Tags

By default tags will be in a comma-separated list in each row of the keyword report. However if you wish to use a ranking report in a pivot table, you can use the “pivotable” option which will create a new row for each tag associated with a keyword.

Report Output

All reports include a date column that show when the report was run. When a report is run, you have the option to either replace the same sheet with new data, or create a new sheet which will have the report name followed by the date. This is useful if you schedule the report to run periodically and don’t want to overwrite data.

 

 

Running Reports

To run your reports, go to the Add-ons menu and select “SERPs.com” > “Run Reports”.

A dialog will appear that will show each of your reports running.

Screen Shot 2016-05-17 at 1.23.40 PM

When a report is finished, it’s title will turn blue. If there’s an error, you will see it in this dialog.

Screen Shot 2016-05-17 at 2.48.42 PM

Schedule Report Automatic Updates

You can set your reports to automatically update periodically.

Screen Shot 2016-05-17 at 2.52.53 PM

You can set it to automatically run daily, weekly (select day of week) or monthly (select day of month). You also need to select an hour of the day when the report will run. Note: run the report later in the day (afternoon/evening) to make it more likely that you can retrieve the most up to date rankings for that day. Reports running earlier in the day should use the “yesterday” ranking field.

Feedback

This plugin is still in development and we’d appreciate your feedback. If you notice any bugs or have any suggestions, please let us know!