About ChatGPT:
ChatGPT’s natural language processing capabilities enables users to have human-like conversations to complete various tasks. When combines with data from Verodat, it’s a powerful solution that enables users to ‘talk to their data’ and identify insights in a user friendly, near-real time experience. Learn more here.
Functionality:
ChatGPT enables business users to get immediate answers to every day questions. When combined with access to data from Verodat, it provides real time insights and powerful summary analysis of the most important data points for your business, within a user friendly interface. ChatGPT is a powerful solution to:
Quickly perform analysis at a macro and micro level within the same interface
Answer once-off questions from executives and teams
Answer follow up questions to support understanding
Summarise information into user friendly written reports
Deliver audio summaries of key information
It delivers headline summaries of key information for Executives and Managers users, and helps business users to get instant answers to day to day questions within a user-friendly interface.
Key Users:
Executives and Business Teams: Delivers real time answers to daily questions, and summarises key information in easy to read reports.
Data Analytics Teams: An efficient, fast solution to create data queries, and verify their results. Can be trained to deliver macro and micro level reporting using a range of languages and techniques, including ML etc as a reliable reporting interface.
Verodat benefits:
Verodat formats data so it’s optimised for use by AI tools like ChatGPT:
Enables the connection of your data to ChatGPT using secure API
Accelerates the training of GPT solutions, by providing the following context out of the box:
Data Dictionary (Column Names, Format, Descriptions)
Entity Relationships
Primary & Foreign Keys
Reporting Needs
Simplifies the workflow to ‘govern’ the AI solution using pre-configured queries
Verifies results back to source using Query Attribution and Supply Chain Audit ID
How to steps:
Step 1: Create a Custom GPT
In the top right menu in your ChatGPT account, select ‘MyGPTs’, and click ‘Create a GPT’
Name and Describe your GPT, and copy & paste the following below AI Instructions to the 'Instructions' section.
AI Instructions
AI Instructions
You are an AI assistant designed to help users analyze complex datasets using the Verodat platform. Your primary function is to interpret natural language queries, construct appropriate SQL queries, and return meaningful results to the user. Follow these instructions carefully:
1. Before responding to any query, retrieve the API context by using the getAIContext action. This will provide you with dataset descriptions, target field descriptions, foreign key relationships, and column names in the SQL database.
2. When you receive a user query, interpret it and construct an appropriate SQL query based on the context you retrieved. The user query will be provided in the following format:
<user_query>
</user_query>
3. Execute the SQL query using the runAIQuery action. Do not print the SQL query to the chat unless the user specifically asks to see it.
4. When returning results:
a. If the query returns results, present them in a clear and organized manner.
b. If the query returns no results, immediately run a second query that uses the SQL JAROWINKLER_SIMILARITY(string1, string2) function to return suggestions of similar records for the column used in the WHERE clause. Only return the top 5 similar records.
5. After returning the results, check if the SQL query used is very similar to one of the approved queries from the getAIContext result. If so, include the following message:
":white_tick: This query is verified by Verodat (hyperlink to https://www.verodat.com). That mean this answer is correct, fully human verified, transparent and traceable. Data is valid up to 2022. :white_tick:"
6. If the user's question is NOT an exact match to the prompts shared in the getAIContext API result under workspace.ai-context, include this comment after returning the results:
"⚠️ This query has not been verified by Verodat (hyperlink to https://www.verodat.com). Let us know what you have asked here and we will verify it for you. If your query returned no results, ask it to suggest the most similar records. ⚠️ "
7. When determining foreign key relationships between tables, use the LOOKUP_EXISTS validation rules. These act as constraints on the child dataset. For example, LOOKUP_EXISTS("Products","unique_id",row["Product Id"]) means that the column "Product Id" is in a child dataset and is referencing the Primary Key of the parent dataset called "Products", where the primary key in this table is called "Unique Id". The primary key of the parent table can be found from the "isKeyComponent": true. Note that the primary key can be a compound key (indicated by "isKeyComponent": true, "isCompound": true).
8. If the user asks "What does this GPT do?", provide the following response:
"This GPT helps you analyze large and complex datasets easily and securely by using Verodat, a platform that collects, prepares, and adds context to your data. We've connected to Xero and can analyse all journals in Verodat historically.
With the Verodat API, this GPT can turn your natural language questions into precise SQL queries, execute them on the Verodat database, and return the results for you to review. Your raw data stays secure in your database, while you get the insights you need. The Verodat API ensures that only the data you want ChatGPT to query is available to it. If you find a query helpful, you can add it to your AI Context in Verodat, allowing the GPT to learn and reuse it for future queries. This makes data analysis intuitive and efficient, leveraging Verodat's powerful data supply chain platform. To learn more about Verodat and request a demo visit www.verodat.com."
9. If the user asks "Tell me my data context.", execute the AI context action and return a summary of the datasets available, the AI context, and how the datasets relate to each other.
10. Always ensure that your responses are clear, concise, and directly address the user's query. If you need clarification on any aspect of the user's request, ask for it before proceeding with the query construction and execution.
11. If you encounter any errors during the process of retrieving context, constructing queries, or executing them, provide a clear explanation of the error and suggest possible solutions or alternative approaches.
Remember to maintain a helpful and informative tone throughout your interactions, and always prioritize the accuracy and relevance of the information you provide.
Select capabilities (we initially recommend you de-select Web Browsing, Image Generation & Code Interpreter to constrain your GPT to follow your instructions as closely as possible.
Step 2: Set up an API connection between Verodat & ChatGPT
Click ‘Create new Action’ to configure the API connection, and enter the attached YAML file.
Replace {{account_id}} with your specific Verodat account id
How to find your Verodat Account ID
How to find your Verodat Account ID
To find your account ID:
Click the cog icon next to your Account Name in the account settings section at the top right corner of your screen
You will see the account ID in the top right corner of the Account Settings box
Replace {{workspace_id}} with your specific Verodat workspace id
How to find your Verodat Workspace ID
How to find your Verodat Workspace ID
To find your Workspace ID:
Click the Workspace Settings button in the bottom left corner of your screen
You will see the Workspace ID in the top right corner of the Workspace Settings box
Click authentication.
Choose API Key
Paste your API key from Verodat
How to find your Verodat API Key
How to find your Verodat API Key
To find your Verodat API Key:
Click the Workspace Settings button in the bottom left corner of your screen
Navigate to the API Keys Tab
Generate your API Key
Select Custom & enter the following header name: ocp-apim-subscription-key
Click 'Save'and Test/Debug the GPT actions
Save your settings, and your GPT
Use GPT to construct an analytics query
The Action File in your GPT allows it to execute a query against a connected database. On initial setup, your GPT will have no preconfigured queries, and will therefore attempt to create its own queries based on information provided on the data dictionary as part of the Verodat API.
In many cases, the data dictionary information provided will be sufficient and the GPT will return a correct result. However, to improve the reliability of results within your GPT, we recommend that users set up ‘Approved Queries’ within the Verodat interface.
Below is the recommended process to build Approved Queries.
Instruct the GPT: Clearly instruct the GPT on what you’re looking to understand. There are two approaches to this:
Write as a question e.g. ‘What are the top 5 best performing products by units sold between April - June 2024?’
Write as an instruction e.g. ‘Please create a bullet point list of the top 5 products that have sold the most units between April - June 2024.’
Grant GPT access to DB: The GPT will then consult the context file to understand what columns of data to use in its query, and construct a query to answer your question. It will ask you to connect to the DB to run the query it has constructed. Click 'Allow' or 'Always Allow'. Next, your GPT will answer the question.
Check your answer: If you are familiar with SQL or Python, we recommend clicking on the action text (the sentence saying 'Talked to...') to see the full query that the GPT has executed, and replicating this query directly on your database to check the result.
It can be helpful here to ask the GPT to tell you more about the steps it followed. If you’re confident on Excel, we also recommend you check your results by utilising the Verodat Excel Add In to export the data to excel and create a comparison table returning the same answer.
Not getting the right result? Below are three common causes of errors:
Incorrect date ranges: If your dates include timestamps, you may need to adjust your date ranges to include until midnight on the last day of the month.
Incorrect column headings: GPTs can assume that there is a corresponding field for “products” when actually the column name is “product_id”. If this is the case, ask it to check its context information.
Incorrect match to query: GPT may search for an exact term in your query, which does not exactly match the information in your data rows, or you may have used some industry jargon which the GPT does not understand.
If this is the case, you can:
Amend your field descriptions in Verodat to include additional context on relevant jargon terms etc.Contact the Verodat support team for assistance with Fuzzy Matching
Refine your answer: You may have received the correct answer, but want to amend the way it is provided. At this stage, it pays to be as specific as possible. Consider adding extra information to the GPT, like whether you’d like the answer presented as a table, or in bullet points. Also consider how many results you’d like to return - the top or the top 10? Ask it to provide the answer again taking into account your format request.
Building an approved query in Verodat
Lock in your query in Verodat: If you’re happy with the answer your GPT has provided, you can click on the action text and copy the SQL or Python Query that the GPT used for your answer.
Navigate to the ‘Workspace Details’ screen.
Open the AI context setting screen.
Using the following format, write in your query. This will ensure the ChatGPT uses this query structure the next time it is asked the same question.
At this stage, you should also include the question text that this query relates to.
Approved Queries:
Name: Top categories by revenue.
Description: A list of the best performing product categories, measured by sum of total revenue.
Question: What are the top product categories by revenue?
Query: "SELECT c.category_name, SUM(od.unit_price * od.quantity) AS total_revenue FROM order_details od JOIN products p ON od.product_id = p.product_id JOIN categories c ON p.category_id = c.category_id GROUP BY c.category_name ORDER BY total_revenue DESC;"
---
Name: Sales revenue by state.
Description: Returns the state location which generated the most sales revenue.
Question: What us state has the most sales revenue?
Query: "SELECT us.state_name, SUM(od.unit_price * od.quantity) AS total_revenue FROM order_details od JOIN orders o ON od.order_id = o.order_id JOIN customers c ON o.customer_id = c.customer_id JOIN us_states us ON c.region = us.state_abbr GROUP BY us.state_name ORDER BY total_revenue DESC LIMIT 1;"
Test your result: When you’ve saved your results in Verodat, try writing the same question into your GPT a second time. If it returns your approved query, the GPT will append the text below to your answer, to assure your users the result is correct.
✅ This is an approved Verodat query ✅
You can repeat the above steps for any number of queries that you wish to create.
Top tip:
To improve usability, we recommend adding popular queries to the ‘Conversation Starters’ section of your GPT. This will set them up as helpful buttons on the welcome page for your users.
Video example of GPT result: