Ready-made query examples for the virtual assistant exchanges feed, showing the browser URL and the equivalent Excel Power Query code for each. For an explanation of how query options work, see How to query OData v2 feeds. For field names and types, see the Virtual assistant exchanges feed reference.
Replace yourtenant with your tenant name and YOUR_API_KEY with your API key in every example.
The Question field holds text learners have written themselves and can contain personal or sensitive information, including safeguarding disclosures. Use $select to leave it out of any extract that does not need it, and treat exports that include it accordingly.
Quick reference
To use URL (browser) queries you will need to log into your Aptem tenant first as this acts as your authenticator to access your OData v2 feed.
Each query is appended to your feed URL: https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges followed by the query string.
For Excel Power Queries replace yourtenant with your tenant name and YOUR_API_KEY with your API key in every example.
I want to... |
Query to append to your feed URL |
|---|---|
See all exchanges, most recent first |
|
See exchanges for a specific learner |
|
See exchanges for a specific programme |
|
See exchanges on one topic |
|
Exclude general conversation |
|
See one full conversation in order |
|
Count exchanges in a period |
|
Return only fields for topic analysis |
|
You cannot search the text of learners' questions. Question cannot be used in $filter or $orderby, so there is no equivalent of a keyword search. To narrow results by subject, filter on Topic instead, then search the returned text in Excel or your reporting tool.
Full worked examples
Each example below shows the complete browser URL and the equivalent Excel Power Query code, so you can copy whichever you need.
All exchanges in the last 30 days
Returns recent virtual assistant activity, newest first. Adjust the date to match your reporting period.
Browser URL
https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Date ge 2026-08-02T00:00:00Z&$orderby=Date desc&$select=LearnerId,ProgrammeId,Question,Topic,Date
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Date ge 2026-08-02T00:00:00Z&$orderby=Date desc&$select=LearnerId,ProgrammeId,Question,Topic,Date", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
All exchanges for a specific learner
Returns one learner's full virtual assistant history, newest first. Replace 48213 with the learner ID.
Browser URL:
https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=LearnerId eq 48213&$orderby=Date desc
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=LearnerId eq 48213&$orderby=Date desc", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
Exchanges on a specific programme this month
Returns activity for a single programme within a date range. Replace 312 with the programme ID and adjust the dates.
Browser URL:
https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=ProgrammeId eq 312 and Date ge 2026-08-01T00:00:00Z and Date lt 2026-09-01T00:00:00Z&$orderby=Date desc
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=ProgrammeId eq 312 and Date ge 2026-08-01T00:00:00Z and Date lt 2026-09-01T00:00:00Z&$orderby=Date desc", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Topic eq 'safeguarding'&$orderby=Date desc&$select=LearnerId,ProgrammeId,Question,Date", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
Topic breakdown without question text
Returns activity by topic with no free text in the extract, for counting exchanges in a pivot table.
Browser URL:
https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Date ge 2026-08-01T00:00:00Z&$select=LearnerId,ProgrammeId,Topic,Date&$orderby=Date desc
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Date ge 2026-08-01T00:00:00Z&$select=LearnerId,ProgrammeId,Topic,Date&$orderby=Date desc", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
Exchanges the virtual assistant did not route to a capability
Returns everything that fell back to general conversation. Useful for spotting questions the virtual assistant has no specific capability for.
Browser URL:
https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Topic eq 'general'&$orderby=Date desc
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Topic eq 'general'&$orderby=Date desc", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
A single conversation, in order
Reconstructs one session end to end. Replace the conversation ID with the one you want.
Browser URL:
https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=ConversationId eq 'conv_5e1b8d40-72af-4c19-8b6e-3d9a0f1c4b27'&$orderby=Date
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=ConversationId eq 'conv_5e1b8d40-72af-4c19-8b6e-3d9a0f1c4b27'&$orderby=Date", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
Exchanges with no topic recorded
Returns historical rows from before the topic was captured. Filter these out if they distort your topic analysis.
Browser URL:
https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Topic eq null&$orderby=Date desc
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Topic eq null&$orderby=Date desc", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
Count of exchanges in the last 30 days
Returns the total number of exchanges without returning the rows themselves. Useful for tracking usage volume over time.
Browser URL:
https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Date ge 2026-08-02T00:00:00Z&$count=true&$top=0
Excel Power Query:
let Source = OData.Feed( "https://yourtenant.aptem.co.uk/odata/2.0/VirtualAssistantExchanges?$filter=Date ge 2026-08-02T00:00:00Z&$count=true&$top=0", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"] ) in Source
Next steps
For the full field reference, the list of topic values, and join information, see the Virtual assistant exchanges feed reference.
Getting help
If you have questions about building queries:
Contact your Implementation Consultant if you are still in implementation
Contact your Customer Success Manager if you are a live customer
Raise a support ticket