OData API
WhosOn OData API
Introduction
The WhosOn OData API enables developers to access reporting and analytics data from the WhosOn SQL Server database using a strongly typed API.
This can be imported directly into BI tools such as Microsoft PowerBI, or used in your own custom dashboards.
OData
Our OData services use OData v4. You can find the documentation for OData v4 here https://www.odata.org/documentation/, which includes some helpful guides for how to use the different components of an OData query.
Authentication
To authenticate with the WhosOn OData service you need your OData API Key. You can get your data API key from the settings portal by viewing your user through the profile section, then going to the advanced area. One API key is generated per user, and this key gives the holder of that key access to any data that your user can see.
You need to append key={your api key} on to the query string for your OData URL.
Data Model
You can view the datamodel by accessing the OData metadata UI, available at
The following are the available data types and what they contain:
Chats
All the information about a chat, including properties related to Transcript Lines, Events and Survey information.
DaySummary
Summary data for each day and each site. Use this for quick access to numbers for missed chats, visits and revenue.
Sites
The sites data lets you obtain basic name information about a site such as domain name.
VisitDetails
The visit details returns information about how each visitor got to the website, and what they did there.
Surveys
The survey data allows you to directly query for certain survey results to link to chat identifiers.
EventLogs
The event logs data shows what actions happened on each chat session.
Skills
The skills data returns the list of skills that your user has access to. Use this for populating skill list filters or linking skill identifiers to skill names.
Users
The users data gives a list of the users and the access rights for those users. Can be linked to sites and skills by the IDs.
UserLogs
The data for each user when they are logging in and out of the client.
The OData data is not linked so would require manual linking against users if you want to use this. A REST service SiteUserLogs is provided for ease of access.
Supported OData Keywords
We support the following keywords:
$filter
-- this filters the data returned from the database
$expand
-- this loads the data from a child property of an entity, for instance the Chat Survey data when selecting a chat. Expanded results can also be filtered.
$to
-- this limits the number of records returned
$skip
-- this skips a certain number of records
$select
-- this reduces the number of columns returned from the query
$orderby
-- this changes the order of the records returned -- most useful with top
Paging
Paging support is built into the OData service. If a query you run results in more than 10 records being returned, then the data will automatically be paged. A new @odata.nextpage
property will be added at the bottom of the dataset that can be followed.
You can implement client-side paging if you want to by using the $top
and $skip
keywords together.
Paging on all data sets defaults to being set to 10 records. This applies to the main query (for example "chats") and any expanded child queries (such as "surveys"). If you need more data, make sure you add `$top` to your queries, for example:
https://{{server}}.whoson.com/data/odata/chats?key={{key}}&$filter=Dated ge 2024-04-10 and Dated le 2024-04-10&$expand=Surveys($top=50)&$top=20
This would get the top 50 survey fields for each of the top 20 chats.
GeoLocation information
Location information derived from the IP address location is included in the GeoLocation column in the Chat and Visit models. This information is returned for each record. You cannot write filter queries based on this data.
Default filters
If you access any of the endpoints with no filter, then a default date filter is applied to prevent too much data being queried during testing.
The default length is 1 year for summary data and 1 month for chat and visit data.
Examples
All chats since the start of 2020, including wrap up information
/odata/chats/?$filter=Dated ge 2020-01-01&$expand=Surveys($filter=VarKey eq 'Value')
Expanding the Surveys and applying the filter will give us the wrapup data in the Survey.VarKey field.
All chats this month taken by user named steve.johnson
/odata/chats/?$filter=Dated ge 2020-03-01 and Events/Any(ev:ev/Username eq 'steve.johnson' and ev/Event eq '[OP]')
Filtering using the /Any against the events table will join the two tables together to allow the correct data to be returned.
Busiest 10 days for chats in 2020, only returning the date and the numbers for chats
/odata/DaySummary?$filter=Dated ge 2020-01-01 and Sitekey eq 31&$select=Dated,Chats,ChatsMissed&$OrderBy=Chats desc$top=10
The $select
clause changes the amount of columns returned. $orderby
instructs the ordering to be by the most number of chats. The filter query is against Sitekey.
Chats this year with transcripts containing the word complain
/odata/Chats?$filter=Dated ge 2020-01-01 and Lines/any(line:contains(line/LineText, 'complain'))&$expand=lines
Using Power BI with the Data API
What is Power BI
Power BI is a tool and service from Microsoft that allows you to create your own dashboards and reports from external data services.
You can use it with the WhosOn data services to create custom dashboards that show whatever data you need. The Power BI data can be refreshed on whatever interval you need.
If you want to publish the dashboards to the web, then you will need a Power BI pro account, but the free version can be downloaded and run on your desktop computer.
What you need
Power BI installed on your computer. Get the latest version from https://powerbi.microsoft.com/en-us/desktop/
Your WhosOn Data API Key. Get this from your user profile inside the settings portal.
Your WhosOn Data API Url. Get this from your profile inside the settings portal, or from your server administrator. This is usually in the form https://{server}/data/odata/
Getting Started
Launch Power BI Desktop on your computer
Click "Get data". This will create a new Power BI document.
In the text box that pops up, enter Blank, then select "Blank Query, and click Connect.
In the box after the = sign, enter: OData.Feed("{YOUR_FEED_URL}", null, [Implementation="2.0", ApiKeyName="key"])
then press return.
Below the URL, you will see a box that says "Edit Credentials" click this.
Click "Web API" on the left, then enter your WhosOn Data API Key in the box, then click Connect
After connecting, you will see a box like this:
Click the word "Table" in the "Data" column of the "DaySummary" row.
You will now see the data view correctly:
Rename the query on the left by right clicking Query1 and selecting "Rename"
Click "Close & Apply" in the top left. The data will load into the model on the right hand side.
Click the chart type you'd like to display -- for example "Area Chart"
Under "Visualizations" you can drag the fields in to the correct areas:
a. First, the Dated field into "Axis"
b. Next, "Chats"values box.
c. Then "Chats Missed" also into the values box.
At the moment, this is showing date heirarchy, rather than a date only graph, click the down arrow next to the Dated field under Axis, and change the selection to "Dated"
You will get a chart showing like this. You can apply styles to this to make it look how you need.
Applying a site selector
Click "Edit Queries" on the top bar
Right Click "WhosOnDaySummary" query and select "Duplicate"
Right Click your new "WhosOnDaySummary (2)" table and rename it to Sites
Edit the bar next to the fx = and change: Source{[Name="DaySummary",Signature="table"]}[Data]
To: Source{[Name="Sites",Signature="table"]}[Data]
Then press return.
Click "Close and Apply" again
Click the relationships button on the left hand side, and check that the relationships are correct. Power BI will have detected the relationship for you and should show the link. You can mouse over the link to see the fields that are in use:
Go back to our chart view, and make sure your chart is de-selected by clicking on the white area
From Visualizations, select
Drag from the "Sites" table the column "Domain"
You can now click the site in the Domain list, and it will filter any data on that page.
Using an OData query
If you want to get more specific data, go back into your edit queries view. When you are entering your OData feed URL, OData.Feed("{YOUR_FEED_URL}", null, [Implementation="2.0", ApiKeyName="key"])
you can enter any OData filter query after the Feed URL.
If you want to access expanded data, its usually more efficient to pre-expand the data using the $expand
keyword. You can then convert the data into columns.
Secondary Services
These services are convenience API endpoints stored within the data services. You must still use your API Key to authenticate your access to the data services.
Chat View API
Retrieves a full chat including text, events and surveys.
/api/ChatView/?chatuid={chatuid}
Reports
This allows a standard report or custom report to be executed. The report details should be obtained using /odata/UserCharts/
/api/Reports/{report-name}?params={parameters}
Parameters is a string in the format {field1}|{value1},{field2}|{value2}
For standard WhosOn reporting, allowed parameter values are:
Field name | Field Description | Purpose |
---|---|---|
SiteKey | Site Key | Site key / id from site record |
FromDate | From Date | Date that query should start from |
ToDate | To Date | Date that query should run until |
ForDate | For Date | Date that query should run against (single date) |
AlertNo | Alert Number | Alert number for queries that use a specific alert |
Site User Logs
Site user logs are an easier way to query what users were connected during a specific window.
/api/SiteUserLogs/{sitekey}/?fromdate={date}&todate={date}&skill={skill}
Parameter | Required | Type | Decription |
---|---|---|---|
Sitekey | Yes | Integer | The sitekey of site being queried |
FromDate | No | Date/Time | The date and time to start the query window from |
ToDate | No | Date/Time | The date and time to end the query window from |
Skill | No | Integer | The skill ID (see /odata/skills ) being checked |