Using AI with data analysis

Concept Information

AICOMMAND command

You can use the Analytics AI command and an external AI service to incorporate AI in your data analysis. This no-code or low-code option allows you to enter a natural-language user prompt in the Analytics interface that describes the analysis you want the AI service to perform, and the type of results you want returned.

Note

The external AI service is not provided by Diligent. It must be provided by your organization.

Only connect to an external AI service that has been approved for use by your organization. Make sure to follow all security protocols if you intend to transmit sensitive data from Analytics to the AI service.

AI results can contain inaccuracies or errors. Use an alternative method to verify results if absolute accuracy is required.

Intended use of the AI command

The AI command is intended to extend or augment the existing data analysis capabilities in Analytics, not replace them. AI can be particularly useful for performing qualitative analysis of text-based data, including unstructured data in free-text fields. Using inference, AI can process text and draw out meaning, intent, and context, or identify patterns. This inference-based, qualitative analysis is difficult or impossible to perform using the existing Analytics command set, which is quantitative and rules-based.

You can also use AI to quickly survey publicly available information on the internet and then incorporate the findings in data analysis that you are constructing in Analytics. For example, you could ask AI to assess the reputation of individual vendors in a vendors table, or the reasonableness of expense claims.

Using the AI command, you can add this sort of analysis to your work in Analytics:

  • Classification Classification of risk survey responses into categories such as operational, strategic, financial, and systemic

  • Sentiment analysis Sentiment analysis and categorization of customer reviews, employee feedback, open-ended survey questions, or interview transcripts

  • Data cleansing Cleansing of Personally Identifiable Information (PII) from transaction descriptions

  • Assessment Assessment and evaluation of vendors based on vendor name and address

  • Comparison Comparison of expense descriptions and amounts to check for reasonableness and surface anomalies

  • Correlation Measurement of the correlation between free-text data and quantitative fields like numerical ratings or scores

AI command examples

The examples below illustrate three useful applications of the AI command. Many other useful applications exist. To perform any of these tasks manually would be exceedingly time-consuming.

  • Vendor assessment using AI Draws upon publicly available information to quickly assess the basic validity of each vendor in a vendor table.

  • Expense review using AI Considers expense description, unit cost, and publicly available information about goods and services to check the reasonableness of expense claims.

  • Sentiment analysis using AI Processes unstructured text in a customer comments field to infer customers' feelings or sentiment, and categorizes comments based on the results.

Vendor assessment using AI

You want to perform a basic check of a vendor table to surface any possible fictitious vendors. You input the vendor name and address fields to the AI service and ask it to categorize the vendors as Common, Rare, or Unknown.

User prompt Is this a commonly known vendor? The output value should be COMMON if common, RARE if known but uncommon, UNKNOWN otherwise.
AI response

Industrial Business Equip, 6 E Monroe St Ste 1004, RARE

Officemax 896, 2300 E Highland Dr, COMMON

Staples 500, 2910 E Harding Ave, COMMON

Ingras Garden Images, 1203 N 4th St, UNKNOWN
How AI helps The AI process draws upon a large body of publicly available information regarding vendors and quickly makes an assessment of the validity of each vendor in the vendor table.

You can shape the user prompt in any way that suits you. Instead of a three-category breakdown in the output, you might want something more fine-grained such as:

  • Blue chip established

  • Established vendor

  • Some information available

  • Unknown

Expense review using AI

You want to review P-card transactions and flag any expenses where a discrepancy exists between the item or service and the purchase price. You input the expense description and the cost fields to the AI service and ask it to assess whether the expense is reasonable.

User prompt Given the combination of Description and Unit Cost, indicate whether the cost is typical or unusual.
AI response

5-Shelf Steel Shelving Unit, 112.50, Typical price for basic metal shelving

60" Urban Bookcase, Cherry, 276.20, Typical price for a quality wooden bookcase

Stainless Steel Water Bottle, 312.90, Unusually high for a water bottle

Deluxe High-Back Computer Operator Chair, 532.00, Typical for ergonomic chair

File Folder Red Lgl, 13.10, Slightly high for file folders

Color Paper Grey Ltr Cascade, 22.50, Unusually high for colored paper
How AI helps The AI process draws upon a large body of publicly available information regarding the price of goods and services and quickly makes an assessment of the reasonableness of each expense in the P-card transactions table.

Sentiment analysis using AI

You want to perform a sentiment analysis and categorization of the free-text comments field in a table containing customer hotel reviews. You ask the AI service to categorize the comments by the six basic human emotions.

Brief user prompt

User prompt Perform a sentiment analysis on the reviews_text field and categorize the reviews by the following basic human emotions: Happiness, Anger, Fear, Sadness, Disgust, Surprise.

Guest comment #1

Bad: A little bit of noise from the 'L' down below, but then again that is part of the character of the city. Good: Overall cleanliness, new feel, friendly efficient staff, great happy hour! Excellent location.
AI response #1

Happiness - Generally positive despite minor noise concern

Guest comment #2

Bad: The room rate was terrible we had a Booking.com rate that was not honored it soured the stay and was a big difference. We to date have no explanation! Good: Location

AI response #2

Anger - Frustrated about rate discrepancy despite good location
How AI helps

The AI service processes the unstructured text in the customer comments field and can infer:

  • The customer's feelings or sentiment

  • The intensity of the sentiment

  • Conflicting sentiment

The AI inference is based on the customer's choice of specific words, intensifiers and modifiers ("very", "extremely", "somewhat"), the presence of sarcasm or irony, and various other linguistic and contextual aspects of the comment text.

Based on all aspects of the inference, the AI process assigns each comment to one of the six basic human emotions.

Longer, very specific user prompt

Tip

You can ask an online AI chatbot such as ChatGPT to create a prompt for you, which you can then refine as required. For example, you could instruct the chatbot: "Give me a good prompt to use when performing a sentiment analysis on the customer comment field in a table of hotel reviews."

User prompt

You are an expert data analyst specializing in customer feedback analysis for the hospitality industry.

Your task is to evaluate the sentiment of each customer comment about a hotel stay.

For each input comment, return a response with:

sentiment — one of: Positive, Neutral, or Negative.

sentiment_score — a numeric value from -1.0 (very negative) to +1.0 (very positive).

key_reasons — a short list (1–3 phrases) explaining what drove the sentiment (e.g., friendly staff, dirty room, great location).

themes — the main aspects mentioned, chosen from: staff, cleanliness, food, amenities, location, value, comfort, service, booking, other.

Important:

Consider tone, context, and modifiers (e.g., not bad = mildly positive).

If the sentiment is mixed, base the rating on the overall impression.

Be concise and consistent across reviews.

Input Example: The room was clean and the staff were helpful, but the Wi-Fi didn't work.

Expected Output Example:

sentiment: Positive

sentiment_score: 0.4

key_reasons: clean room, helpful staff, poor Wi-Fi

themes: cleanliness, staff, amenities

Guest comment #1

Brand new hotel in brand new retail area on the water. Easy to access off the beltway. Friendly staff, clean rooms, comfortable beds. Only complaint is that the full offering of the Hampton Inn breakfast was not available at the two times we came down (at 7am and 9am on a Saturday). The food at 9am had been in the bin too long. Still more choices than the continental breakfast at other places, including make-you-own waffles and yummy egg burritos. The fee for parking in the community garage was free with room key and even included a convenient room key drop upon final exit. Pool but no hot tub.
AI response #1

sentiment: Positive

sentiment_score: 0.6

key_reasons: clean rooms, friendly staff, breakfast issues

themes: cleanliness, staff, food

Guest comment #2

Bad: Timing seemed to be off. Had to wait a very long time for breakfast, longer than people who had come in after us. Had to wait a very long time for the valet to bring our car, seemed to be some kind of communication issue. They were nice about it, and so were we, but it seemed to be a theme, which was a bit of a disappointment. Good: Nicely appointed room, great location.

AI response #2

sentiment: Neutral

sentiment_score: 0.3

key_reasons: nice room, slow service times

themes: service, amenities

How AI helps

In addition to the inference and basic classification described in the previous example, this more thorough AI analysis provides multiple useful components in the output.

  • The fine-grained sentiment score, derived from a customer's actual words, may provide a more reliable quantitative metric than the typical customer-assigned 1-5 star rating.

  • The grouping of comments by theme could help hotel management decide where to focus their attention and resources.

Working effectively with an AI service

An AI service typically limits the amount of data it will process with each operation. Records in an Analytics table are sent to an AI service in batches, to improve the likelihood of remaining within the data limits. Batching helps, but you should also follow these guidelines to optimize the performance and response times of the AI command:

  • Send only those fields that are strictly necessary for your analysis goal Do not send an entire source table. If you include a unique identifier field in the data that you send, you can join or relate the table returned from the AI service with the original source table. For more information, see Create a unique identifier field.

  • Limit the number of records that you send Distill data sets by performing data preparation and curation steps before sending the data to an AI service.

  • Use the AI service for qualitative analysis Do not use an AI service for large-scale numerical or quantitative analysis. This type of analysis is more quickly and efficiently performed locally, using existing Analytics operations such as sorting, classifying, summarizing, or stratifying.

Before you start

These prerequisites must be in place before you can use the AI command.

  • An installation of Python

    You must install and configure a compatible version of Python on the computer where the command will run. You must also install two Python libraries: Pandas and OpenAI Python API. For more information, see Configuring Python for use with Analytics.

  • Connection settings for an AI service

    In the Options dialog box, you must specify values for the settings required to connect Analytics with an external AI service. For more information, see Connection settings for an external AI service.

Analyze data using an external AI service

Use the AI command to send data to an external AI service for analysis.

  1. Open the table containing the data.

  2. From the Analytics main menu, select Analyze > AI Command.

    If a message appears asking for an API key for an AI service, you need to specify an API key in the Options dialog box. For more information, see Connection settings for an external AI service.

  3. Select Select Fields and specify one or more fields to send to the AI service.

    Note

    Limit the total amount of data sent to the AI service by specifying only those fields that are strictly necessary for your analysis goal.

    Include a unique identifier field if you want to join or relate the table returned from the AI service with the original source table. For more information, see Create a unique identifier field.

  4. Optional. To filter the records that are sent to the AI service, click If and use the Expression Builder dialog box to create a conditional expression to use as the filter.

    At least one record in the table must be included by the filter. If the filter excludes all records in the table, Analytics displays an error and the AI command does not run.

    For more information about creating expressions using the Expression Builder, see Creating expressions using the Expression Builder.

  5. In the To text box, specify a name for the table to contain the data returned from the AI service.

    You can click To and use the file explorer to navigate to the folder you want to use to store the returned data table.

    Note

    Analytics table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

  6. Select Use Output Table if you want the table to open automatically upon completion of the operation.

  7. Do one of the following:

    • Leave the Python Script field blank to use the Analytics default Python script to interact with the AI service.

    • Use your own Python script to interact with the AI service.

      In the Python Script field, specify the file path and name of the Python script that you want to use.

      For example: C:\Users\<account_name>\Documents\ACL Data\<analytics_project_name>\Python_scripts\your_python_script.py

      You can specify a relative path to the Python script, or just the script file name if the script is in the Analytics project folder.

      You can also click Browse and navigate to the Python script on your computer.

      For more information, see Using your own Python script.

  8. In the User Prompt text field, specify the prompt that you want to input to the AI service.

    The prompt tells the AI service how to process the input data that you send and what output you want the service to send back. For some examples of prompts, see AI command examples.

    To increase your working space, select Edit Prompt to open the User Prompt Builder. You can double-click field names in the Available Fields list to quickly add them to the User Prompt text field.

    Note

    Do not include any double quotation marks ( " ) in the User Prompt field. Double quotation marks cause the command to fail. You can use single quotation marks ( ' ) in the field.

  9. Ensure that the AI Model field contains correct AI model information.

    If required, you can update the AI model information.

    The AI Model field is prefilled using a value specified in the Options dialog box (Tools > Options > System). For more information, see Connection settings for an external AI service.

  10. Ensure that the AI Base URL field contains the full URL for the AI service API.

    If required, you can update the URL for the AI service API. If you do update the URL, you will very likely need to also update the AI API Key in the Options dialog box, which is the password or license key value required for authentication with the AI service.

    The AI Base URL field is prefilled using a value specified in the Options dialog box (Tools > Options > System). For more information, see Connection settings for an external AI service.

  11. Optional. In the Batch Size field, specify the number of records to include in each batch sent to the AI service.

    If you leave the field blank, the default batch size of 100 records is used. Specifying a number less than 100 reduces the overall amount of data sent to the AI service with each batch. Reducing the amount of data per batch improves the likelihood of remaining within the AI service data limits.

  12. Optional. On the More tab of the dialog box, specify any scope options that you want to use.

    For more information, see More tab.

  13. Click OK to run the command.

    In the output table returned from the AI service, the single ai_response field contains the results of the AI analysis.

    The command may take a few minutes to run depending on the amount of data that you send to the AI service. Consider sending a small test set of 100 records initially so that you can quickly get an idea of the type of results that are returned.

    If you get an error message and the AI command fails to run, try reducing the batch size to 50 or 25 records. For more information, see Working effectively with an AI service.

    Note

    If the ai_response field contains multiple components or elements, you can isolate the components in separate fields by using standard Analytics techniques. For more information, see Separate multiple components in the ai_response field.

AI command dialog box options

More tab

Option Description
All Processes all records in the view (default selection).
First Processes from the first record in the table and includes only the specified number of records.
Next

Processes from the currently selected record in the table and includes only the specified number of records.

Note

The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering.

If a view is quick sorted, Next behaves like First.
While

Uses a WHILE statement to limit the processing of records in the primary table based on criteria.

Records in the view are processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered. For more information, see Creating expressions using the Expression Builder.

Create a unique identifier field

If you want to join the table returned from an AI service with the original source table, but lack a unique identifier field, you can use the RECNO( ) function to create one before you use the AI command.

  1. In the source table, create a computed field named record_number that uses the following expression:

    RECNO( )

    For each record in the table, the RECNO( ) function adds the unique record number in the record_number field.

    For more information, see Basic computed field.

  2. Include record_number in the fields that you specify in the AI command and send to an external AI service.

  3. Use the record_number field as the common key field to join the table returned from the AI service with the source table.

    For more information, see Join tables.