Windsor.ai API Query Documentation

Overview

Many of our users have been asking about visualising data in Data Studio, PowerBI or Tableau. Some also asked to be able to pull data into R or Python. We are therefore publishing an API to support connectivity into any tool.

Later we will also support not only pulling data via the API but also sending us data. Eg. from CRM’s or other tools to more easily optimise on offline conversions or CLV’s.

This API gives flexibility for visualising data in any tool. It also provides joining, aggregation and filtering possibilities to minimise the data-transfers and keep response times fast.

To use the API you will need an API key that you will be provided when signing up.

---

Getting Started

Owners of the accounts with access to the configuration portal can retrieve the API user name and API key in the section on the top right after logging in

 

---

Authentication

The Windsor.ai API requires an API key to be included in all requests

https://api.windsor.ai/pied_piper_llc/pied_piper_llc_attribution/public?api_key=<YOUR API KEY>

 

---

Filter data

Filter (WHERE)

GET /DATABASE/SCHEMA/TABLE?FIELD=$eq.VALUE

Query Operators

Name	Description
$eq	Matches values that are equal to a specified value.
$gt	Matches values that are greater than a specified value.
$gte	Matches values that are greater than or equal to a specified value.
$lt	Matches values that are less than a specified value.
$lte	Matches values that are less than or equal to a specified value.
$ne	Matches all values that are not equal to a specified value.
$in	Matches any of the values specified in an array.
$nin	Matches none of the values specified in an array.
$null	Matches if field is null.
$notnull	Matches if field is not null.
$true	Matches if field is true.
$nottrue	Matches if field is not true.
$false	Matches if field is false.
$notfalse	Matches if field is not false.
$like	Matches always cover the entire string.
$ilike	Matches case-insensitive always cover the entire string.

Filter (WHERE) with JSONb field

https://api.windsor.ai/DATABASE/SCHEMA/TABLE?FIELD->>JSONFIELD:jsonb=VALUE (filter)

---

Get data

Select – GET

https://api.windsor.ai/tables?_renderer=xml (JSON by default)
https://api.windsor.ai/DATABASE/SCHEMA (show all tables, find by schema)
https://api.windsor.ai/DATABASE/SCHEMA?_renderer=xml (JSON by default)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE (show all rows, find by database and table)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE?_select=column (select statement by columns)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE?_select=column[array id] (select statement by array colum)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE?_select=* (select all from TABLE)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE?_count=* (use count function)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE?_count=column (use count function)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE?_page=2&_page_size=10 (pagination, page_size 10 by default)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE?FIELD=VALUE (filter)
https://api.windsor.ai/DATABASE/SCHEMA/TABLE?_renderer=xml (JSON by default)

Renaming aggregated columns:

---

Submit, modify and delete data

Insert – POST

https://api.windsor.ai/DATABASE/SCHEMA/TABLE

JSON DATA:

{
    "FIELD1": "string value",
    "FIELD2": 1234567890
}

Batch Insert – POST

https://api.windsor.ai/batch/DATABASE/SCHEMA/TABLE

JSON DATA:

[
    {"FIELD1": "string value", "FIELD2": 1234567890},
    {"FIELD1": "other string value", "FIELD2":1234567891},
]

 

Update – PATCH/PUT

Using query string to make filter (WHERE), example:

https://api.windsor.ai/DATABASE/SCHEMA/TABLE?FIELD1=xyz

JSON DATA:

{
    "FIELD1": "string value",
    "FIELD2": 1234567890,
    "ARRAYFIELD": ["value 1","value 2"]
}

Delete – DELETE

Using query string to make filter (WHERE), example:

https://api.windsor.ai/DATABASE/SCHEMA/TABLE?FIELD1=xyz

---

Join tables

JOIN

/DATABASE/SCHEMA/Table?_join=Type:Table2:Table.field:Operator:Table2.field

Parameters:

1. 1. Type (INNER, LEFT, RIGHT, OUTER)
   2. Table2
   3. Table.field
   4. Operator ($eq, $lt, $gt, $lte, $gte)
   5. Table2.field

Using query string to JOIN tables, example:

/DATABASE/SCHEMA/friends?_join=inner:users:friends.userid:$eq:users.id

Query Operators

Name	Description
$eq	Matches values that are equal to a specified value.
$gt	Matches values that are greater than a specified value.
$gte	Matches values that are greater than or equal to a specified value.
$lt	Matches values that are less than a specified value.
$lte	Matches values that are less than or equal to a specified value.
$ne	Matches all values that are not equal to a specified value.
$in	Matches any of the values specified in an array.
$nin	Matches none of the values specified in an array.

 

---

Group and order data

DISTINCT

To use DISTINCT clause with SELECT, follow this syntax _distinct=true.

Examples:

GET /DATABASE/SCHEMA/TABLE/?_distinct=true

---

 

ORDER BY

Using ORDER BY in queries you must pass in GET request the attribute _order with fieldname(s) as value. For DESC order, use the prefix -. For multiple orders, the fields are separated by comma.

Examples:

ASC

GET /DATABASE/SCHEMA/TABLE/?_order=fieldname

DESC

GET /DATABASE/SCHEMA/TABLE/?_order=-fieldname

Multiple Orders

GET /DATABASE/SCHEMA/TABLE/?_order=fieldname01,-fieldname02,fieldname03

---

 

GROUP BY

We support these Group Functions:

Name	Use in request
SUM	sum:field
AVG	avg:field
MAX	max:field
MIN	max:field
MEDIAN	median:field
STDDEV	stddev:field
VARIANCE	variance:field

 

Examples:

GET /DATABASE/SCHEMA/TABLE/?_select=fieldname00,fieldname01&_groupby=fieldname01

Using Group Functions

GET /DATABASE/SCHEMA/TABLE/?_select=fieldname00,sum:fieldname01&_groupby=fieldname01

Having support

To use Having clause with Group By, follow this syntax:

GET /DATABASE/SCHEMA/TABLE/?_groupby=fieldname->>having:GROUPFUNC:FIELDNAME:CONDITION:VALUE-CONDITION

Example:

GET /DATABASE/SCHEMA/TABLE/?_select=fieldname00,sum:fieldname01&_groupby=fieldname01->>having:sum:fieldname01:$gt:500

---

Frequently used queries

Data retrieval (GET)

Note: All dates should be in the DD-MM-YYYY (e.g. 2020-06-30) format

Retrieval of list of available tables

https://api.windsor.ai/<YOUR USERNAME>/<YOUR USERNAME>_attribution/public?api_key=<YOUR USERNAME>

Retrieval of media costs across all channels for a specific date range

https://api.windsor.ai/<YOUR USERNAME>/<YOUR USERNAME>_attribution/public/costs?api_key=<YOUR API KEY>&_select=source,medium,campaign,sum:totalcost:spend&_groupby=source,medium,campaign&date=$gte.<YOUR START DATE>&date=$lte.<YOUR END DATE>&totalcost=$gte.0&campaign=$notnull&source=$notnull&medium=$notnull

Retrieval of conversions paths (source, medium and campaign level) for e-commerce transactions

https://api.windsor.ai/<YOUR USERNAME>/<YOUR USERNAME>_attribution/public/conversions?api_key=<YOUR API KEY>&_select=sourcepath,campaignpath,transactionid,mediumpath,keywordpath&_groupby=sourcepath,campaignpath,transactionid,mediumpath,keywordpath&date=$gte.<YOUR START DATE>&date=$lte.<YOUR END DATE>

Data submission (POST/PUT/DELETE)

Upload or update of CRM data for net revenue attribution

https://api.windsor.ai/<YOUR USERNAME>/<YOUR USERNAME>_attribution/public/crm_conversions?api_key=<YOUR API KEY>

Body:

{
'order_date': <TRANSACTION DATE (OPTIONAL)>,
'crm_type': <CONVERSION TYPE>,
'transaction_id': <Transaction ID>,
'net_revenue': <REVENUE>
}

Upload of media data (costs, clicks, impressions)

https://api.windsor.ai/${bundle.authData.user_name}/${bundle.authData.user_name}_attribution/public/manual_costs?api_key=<YOUR API KEY>

Body:

{
'source': <SOURCE>,
'medium': <MEDIUM>,
'date': <DATE>,
'totalcost': <MEDIA COST>,
'campaign': <CAMPAIGN NAME>,
'adcontent': <ADCONTENT NAME (OPTIONAL)>,
'impressions': <IMPRESSIONS (OPTIONAL)>,
'clicks': <CLICKS (OPTIONAL)>,
'keyword': <KEYWORD(OPTIONAL)>,
'type': <CONVERSION TYPE (OPTIONAL)>
}

Upload of CRM touchpoints

https://api.windsor.ai/${bundle.authData.user_name}/${bundle.authData.user_name}_attribution/public/crm_touchpoints??api_key=<YOUR API KEY>

Body:

{
'datehourminute': <DATE TIME>,
'source': <SOURCE NAME>,
'clientid': <CLIENT ID>,
'medium': <MEDIUM NAME>,
'campaign': <CAMPAIGN NAME>,
'keyword': <KEYWORD (OPTOINAL)>
}

Questions?

If you have any questions, we would be happy to help you. Just send us an email.