WQL API

Introduction

All Woopra logs are stored in a relational database system. Woopra data-tables are logical data structures made up of a variable number of columns and rows. Unlike traditional databases, Woopra tables are schema-less; they are designed to grow vertically and horizontally to store visitor-specific variables.

There are three tables for each tracking instance: visitors, visits and actions. The ‘visitors’ table stores visitor specific data. The ‘actions’ table stores all actions (pageviews and custom events) committed by a visitor.

The data-tables have a fixed number pre-defined columns* such as browser, country, IP-address, etc; and can be extended to store custom visitor and action data.

visitors table

visits table

actions table

Inserting Data

Inserting data to your tables is done through javascript requests. For example:

var event = {
    'name': 'Play',
    'title': 'Aint no sunshine',
    'Artist': 'Billy Withers'
};
woopraTracker.addVisitorProperty(‘email’, ’johnsmith@mail.com’);
woopraTracker.pushEvent(event);​

More details about using Javascript to insert data.

The above Javascript snippet will insert the following data

‘visitors’ table

‘visits’ table

‘actions’ table

Pulling Data

Introduction

Woopra Query Language (WQL): is used to pull data from the tables. WQL implements an SQL-like syntax to retrieve data through a friendly and powerful interface. A valid WQL statement must define at least one column and a ‘GROUP BY’ variable (check Appendix for all variables).

HTTP Request

A WQL request must be submitted to api.woopra.com/rest/analytics/get.jsp via http POST or GET methods. The http response body will contain the formatted response.

-->Endpoint: http://api.woopra.com/rest/analytics/get.jsp

All HTTP requests must have the following parameters at all times:

Example GET request

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format = dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT <strong>visits.count</strong> FROM cloud GROUP BY <strong>'info.browser’</strong>

WQL syntax

Basic Syntax

The basic building blocks of WQL are:

1. SELECT modifier
2. Column definitions ( visits, visitors, actions )
3. Datasource: ‘FROM cloud’
4. GROUP BY variable(check Appendix for a list of all variables)

GET HTTP Request

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = <strong>SELECT visits.count FROM cloud GROUP BY 'info.browser'</strong>

Boundaries

WQL defines a LIMIT and OFFSET modifiers to control the number of elements returned. To prevent slow queries, a hard limit is enforced on the maximum number of records that can be returned.

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT visits.count FROM cloud GROUP BY 'info.browser' <strong>LIMIT 10 OFFSET 0</strong>

Multiple Columns

WQL automatically generates column names. To override default names, columns can be labeled using the ‘AS’ modifier. Labeling the column will return a friendly response.

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT <strong>visits.count AS 'My Visits', actions.count AS 'My Actions'</strong> FROM cloud GROUP
BY 'info.browser'

Renaming Columns

WQL automatically generates column names. To override default names, columns can be renamed using the ‘AS’ modifier. Labeling the column will return a friendly response.

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT visits.count <strong>AS 'My Visits'</strong>, actions.count <strong>AS 'My Actions'</strong> FROM cloud GROUP BY 'info.browser'

Text Columns

In addition to numeric columns, WQL allows text columns. Text columns are visit or action propreties that can be appended to the results.

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT <strong>visitor.email</strong> FROM cloud GROUP BY visitor.name limit 1000

Ordering Results

WQL allows ordering results by a specific column, in case there are more than one column.

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT visits.count AS 'My Visits',actions.count AS 'My Actions' FROM cloud GROUP BY 'info.browser' <strong>ORDER BY 'My Actions'</strong>

Filtering / Segmentation

One powerful feature of WQL is segmentation. The ‘WHERE’ switch will run the query on the visitors segment exclusively. This allows programmers to drill deep down the data, and to analyze the traffic that matters the most. More details about filters syntax can be found here.

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT visits.count AS 'My Visits', actions.count AS 'My Actions' FROM cloud <strong>WHERE " info.browser=firef* "</strong> GROUP BY 'info.browser'

System (pre-defined) Variables

Custom Visitor and Event Data

WQL Examples

Example 1: Get Top Elements

To aggregate visit elements in one report, the ‘GROUP BY’ element should point to a visit variable. (Check Appendix for all variable. s)To pull top browsers:
GROUP BY is set to ‘info.browser’, LIMIT is set 100, OFFSET is set to 0

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT visits.count FROM cloud WHERE true GROUP BY 'info.browser'

Example 2: Get Top Elements

In a previous section, we showed how custom visitor data is inserted into tables:
woopraTracker.addVisitorProperty<strong>(‘email’,’johnsmith@mail.com’);</strong>
woopraTracker.pushEvent(e);

Resulting row in visitors table:

To pull the visitors emails, the GROUP BY element has to be ‘visitor.email’ (case sensitive)

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT visits.count FROM cloud <strong>GROUP BY 'vistor.email'</strong> LIMIT 100 OFFSET 0

Example 3: Get Custom Actions

In a previous section, we showed how custom visitor data is inserted into tables:
var e = {<strong>'name': 'Play', 'title': 'Aint no sunshine', 'Artist': 'Billy Withers'</strong>};
woopraTracker.pushEvent(e);

Resulting row in visitors table:

To pull the top songs, the GROUP BY element has to be ‘action.title’ (case sensitive)

http://api.woopra.com/rest/analytics/get.jsp?website = mywebsite.com &<br>
api_key = ABCDEF0000 &<br>
format = xml &<br>
date_format= dd.MM.yyyy &<br>
start_day = 01.07.2011 &<br>
end_day = 27.07.2011 &<br>
query = SELECT visits.count FROM cloud <strong>GROUP BY 'action.title'</strong> LIMIT 100 OFFSET 0