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:
https://api.windsor.ai/<client_name>/DATABASE/SCHEMA/TABLE?_select=source,sum:totalcost:total_cost,sum:clicks:clicks_sum&_groupby=source&date=$gt.2020-05-01&api_key=<api_key>
[{"source":"facebook","total_cost":42.490017,"clicks_sum":47},
{"source":"google","total_cost":393.74585,"clicks_sum":260}]
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:
- Type (
INNER
,LEFT
,RIGHT
,OUTER
) - Table2
- Table.field
- Operator (
$eq
,$lt
,$gt
,$lte
,$gte
) - Table2.field
- Type (
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.