Querying ZoomRank using ShufflePoint

Summary

ZoomRank is a service for organic and local rank monitoring.

Setup

Visit "Manage Datasources" and enter your ZoomRank API key, which you can get from ZoomRank.

Introduction

It is a common need in web analytics reporting to have included in reports information about the search ranking of selected keywords. The ranking metric is an indicator of the success (or failure) of SEO efforts. Several services exist to automatically check the page rank of specified keywords. ShufflePoint evaluated several and choose ZoomRank (ZR) for our first SEO service integration. We believe they have a quality service coupled with a quality API. You can get all the details at their web site at www.zoomrank.com.

ZoomRank supports bulk uploading of keywords to be tracked. A quick way to get started would be to write AQL queries to pull keywords from Google Analytics organic search or campaigns (or query AdWords directly, which we now support). See the end of this help doc for sample queries.

ShufflePoint has added to our Analytics Query Language (AQL) about two dozen new metrics and dimensions to support querying ZR. A ZR query should look familiar to AQL users. The namespace prefix for ZR is "zr:". The dimensions and metrics are now described.

Available Dimensions, Metrics and Timeframes

Dimensions

User dimensions

Category dimensions

SERP dimensions

Timeframe dimensions

Metrics

User metrics

SERP metrics

Timeframes

The AQL TIMEFRAME clause specifies the timeframe over which data is obtained. Timeframe handling with ZR is not as flexible as it is with GA (GA is an OLAP cube; ZR is table based). The following table lists the supported timeframes and describes how they are processed. Queries which do not contain SERP metrics do not require a timeframe. This is true of user table and category table queries. For example, this user table query requires no timeframe.

SELECT

METRICS zr:allowedProfiles, zr:availableProfiles ON COLUMNS

DIMENSIONS zr:userName, zr:company, zr:email ON ROWS

Timeframes with special handling

All other named timeframes and timeframes with a start and end date are only valid if a zr:date, zr:week, or zr:month dimension is present.

Usage Notes

Special ranks

These rank values have special meaning with ZoomRank

These special ranks will not be considered in queries which perform averages.

Sample queries

All user table info

SELECT

METRICS zr:allowedProfiles, zr:availableProfiles ON COLUMNS

DIMENSIONS zr:userName, zr:company, zr:email, zr:firstName, zr:lastName, zr:userId ON ROWS

All category table info

SELECT

METRICS xx:identity ON COLUMNS

DIMENSIONS zr:category, zr:categoryId ON ROWS

Note the xx:identity placeholder metric. It returns no data but is present merely to satisfy the current need for AQL queries to have at least one metric.

Most recent organic rank with all available dimensions

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME yesterday

Most recent weekly average organic rank with all available dimensions

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME thisCalendarWeek

Weekly average organic rank with all available dimensions for two weeks ago

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME lastCalendarWeek2

Most recent monthly average organic rank with all available dimensions

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME thisMonth

Same for last month

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME lastMonth

Monthly average rank by month for last three full months

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:month, zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME fullMonths:3

Weeklyly average rank by week for last eight weeks

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:week, zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME weeks:8

Daily rank for last ten days

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:date, zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME days:10

Daily rank for a specific date

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:date, zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME 2011-04-01:2011-04-01

Daily rank for a specific date range

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:date, zr:engine, zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME 2011-03-01:2011-04-30

Pivot engine ranks into distinct metrics

SELECT

METRICS zr:googleRank, zr:yahooRank, zr:bingRank ON COLUMNS

DIMENSIONS zr:category, zr:url, zr:keyword ON ROWS

FROM "user name"

WHERE

TIMEFRAME yesterday

Trends engine ranks over ten days for a specific url and keyword

SELECT

METRICS zr:googleRank, zr:yahooRank, zr:bingRank ON COLUMNS

DIMENSIONS zr:date ON ROWS

FROM "user name"

WHERE

2011-04-01:2011-04-10

FILTER

zr:url == "www.mydomain.com" AND zr:keyword == "category 1"

Average ranks by category

SELECT

METRICS zr:rank ON COLUMNS

DIMENSIONS zr:engine, zr:url, zr:category ON ROWS

FROM "user name"

WHERE

TIMEFRAME yesterday

Average engine ranks by category for a specific url

SELECT

METRICS zr:googleRank, zr:yahooRank, zr:bingRank ON COLUMNS

DIMENSIONS zr:category ON ROWS

FROM "user name"

WHERE

TIMEFRAME yesterday

FILTER

zr:url == "www.mydomain.com"

Rank count by rank 5-bucket score, aggregated to category

SELECT

METRICS zr:count ON COLUMNS

DIMENSIONS zr:score5, zr:engine, zr:category ON ROWS

FROM "user name"

WHERE

TIMEFRAME yesterday

Engine monthly average rank count by rank 3-bucket score, aggregated across all keywords

SELECT

METRICS zr:googleCount, zr:yahooCount, zr:bingCount ON COLUMNS

DIMENSIONS zr:score3 ON ROWS

FROM "user name"

WHERE

TIMEFRAME lastMonth

Future plans

1. More aggregation support

2. Support queries which distinguish organic and local

3. Improved filtering capability