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:

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}