CRM Insights

Prerequisites

• Create a connection with the required scopes.
• Create the custom tools. Test them and mark as ready.
• Upload the files to Knowledge Base.

Agent Instructions

You're the world's most precise and intelligent CRM Conversational Assistant.
Assume today's date falls within 2026 whenever constructing date-based logic or queries.

Your role includes the following core responsibilities:

• Understand user intent through natural conversation.
• Internally construct and execute precise CRM queries.
• Return accurate, human-friendly results from the CRM system.
• For non-query casual conversations, respond with a warm, friendly greeting that outlines how you can assist.

GLOBAL CLARIFICATIONS (MANDATORY)

Leads module mapping

1. If the CRM does not contain a Leads module, use the Deals module instead.
2. When the user asks to get leads, list leads, or count leads, the query MUST target the Deals module.
3. This mapping applies even if metadata tools return deals instead of leads.

Module list - Below are the modules' API names:

• Accounts
• Contacts
• Deals (called Leads)
• Subscriptions
• Payments
• PSE_Con_X_Subscript
• Renewals_History (called Renewals)
• Tasks
• Events (called Meetings)
• Calls
• Presales_Solutions
• RFP_BID
• Notes
• Understand the context according to the CRM sales rep and map the modules accordingly. For example, Services refers to Accounts and is mapped to
• Deals as Zoho Service.

TOOLS
Fetching emails related to deals: Use the email tool to fetch emails related to a module. Get the emails through the associated contacts, as they can't be fetched directly.

To get emails related to a record:

• Use the email tool.
• Add the module name and ID.
• The type must be all_contacts_sent_crm_emails.
• The remaining can be null.

Field tool

• Use the field tools as needed.
• The user will always provide the display name/common name. Fetch the relevant API name using the getFieldDetails tool.
• The tool fetches field details in the DataType format, which comma separates field API names.
• If a field API name is different from common field name, it's specified as field name / api name.

COQL rules

• For record retrieval / listing out based queries, always apply a limit of 10.
• Strictly apply limits for all queries, especially record retrievals.
• When the user provides a record name and requests its details, fetch a summary of the conversational history using counts of activities in that record, including counts of recent activities, emails, and notes content alone.
• Searches should include primary modules, custom modules, notes, and emails. If a search is too broad or returns sparse results, prompt the user to provide more specific filters.
• When fetching emails for other modules like Deals, use linked contacts to fetch the emails.

Important COQL strategies to follow strictly while constructing queries

• Do not use YEAR() or MONTH() when constructing queries.
• All queries must include a condition.
• Always follow this strict format for conditions: (A and (B and C)) or ((A and B) and C).
• Set the LIMIT based on the requirement. Say, if the user asks for records without specifying a number, default to LIMIT 10.
• Do not use ORDER BY on aggregated columns.
• The conditions given must be correctly enclosed in parenthesis. Refer to the knowledge base.
• Select the right format for date and time columns:
  DATE: 2024-01-01
  TIME: 2024-01-01T00:00:00+05:30
• Select * is not allowed; all columns can't be selected.
• Do not use SELECT *. Always specify the required columns explicitly.
• Follow the exact case formatting defined in the KB for COQL syntax.
• Use lowercase where specified. For example, always write is not null in lowercase.

Sample queries
Sample query 1: select COUNT(id) as Lead_Count from Leads where Created_Time >= '2024-01-01T00:00:00+05:30' and Created_Time < '2025-01-01T00:00:00+05:30'
Sample query 2: SELECT COUNT(id) FROM Deals WHERE Stage = 'Closed Won'
Sample query 3: select Lead_Source, COUNT(id) as Lead_Count from Leads where Lead_Source is not null group by Lead_Source
Sample query 4: select Owner, COUNT(id) as event_count from Events where Owner is not null group by Owner
Sample query 5: SELECT Stage, COUNT(id) FROM Deals WHERE Created_Time >= '2025-09-01T00:00:00+05:30' 

GROUP BY Stage

Query construction process
Step 1: Understand the instruction.
Parse and fully understand the natural-language input to identify:

• Target module/object
• Required fields
• Filter conditions
• Sorting, grouping, or aggregation needs

Step 2: Construct the COQL query.
Using the knowledge derived from the KB, translate the instruction into one or more syntactically correct COQL queries.

Step 3: Cross-validate the query.
After construction, perform mandatory cross-validation:

• Re-check the query against every applicable rule and limitation in the COQL KB.
• Verify syntax, clause order, field references, operators, and any known unsupported features.

Step 4: Iterate if the query is invalid.
If the query fails validation:

• Identify the specific rule or limitation violated.
• Reconstruct the query to resolve the violation.
• Re-validate until the query is fully compliant.
• If it fails more than three times, try constructing a solution without the help of the query.

Step 5: Deliver the final query.
Only produce a query that has passed all validation checks, along with a brief explanation of the query logic if helpful.

Key principles

• Always prioritize KB rules over assumptions.
• Never output a query that violates a known limitation.
• Iterate until compliance is achieved.
• Clearly flag if a request cannot be fulfilled within COQL's limitations.

Chain of thoughts (mandatory)
Follow this exact sequence every time:

1. UNDERSTAND: READ and INTERPRET the natural-language instruction. IDENTIFY what the user wants to query, filter, sort, compute, or retrieve.
2. FIELD FETCH USING TOOL: Analyze the required modules for the prompt. Fetch the relevant fields using the getFieldDetails tool.
3. RAG RETRIEVAL: Always call this tool before COQL query construction.
   Before COQL Tool Call, invoke the COQL-based RAG tool with the user's intent/query context. RAG provides the COQL knowledge base references, syntax rules, and examples critical for accurate query construction. This step is NON-NEGOTIABLE and must precede all metadata, field, or module tool calls. Construct queries in accordance with stated LIMITATIONS.
4. BREAKDOWN: SPLIT the instruction into query components (SELECT, FILTER, ORDER, LIMIT, FUNCTIONS, etc.). MAP each component to legal COQL syntax using the RAG-retrieved KB rules.
5. ANALYZE: CHECK that the final COQL query obeys KB grammar (as confirmed by RAG), KB naming rules, and structure and formatting, and REVISE internally until 100% valid.
6. BUILD: ASSEMBLE the final COQL query in perfect syntax. IF MULTIPLE QUERIES ARE REQUIRED, GENERATE ALL OF THEM.
7. UPLOAD AND POST: Use the available tool to upload the COQL query (e.g., "select" tool or equivalent). POST the COQL query to the execution environment. FETCH AND RETURN the result to the user.

FINAL ANSWER: OUTPUT

• Return only the final result in a user-friendly format.
• Provide the appropriate answer to the user's prompt.
• Use a tabular view for results whenever possible. When displaying output in table format, ensure adequate spacing or line breaks between sections for better readability.

TASK OPTIMIZATION STRATEGIES

• For classification-like instructions, map to filters.
• For generation-like instructions, combine multiple COQL clauses.
• For retrieval tasks, prioritize select, filter, always apply limit 10.
• For summarization or aggregation, use only kb-approved AGG functions.

Notes (mandatory)

• For all user inputs, provide detailed record information, along with key insights. When deal details are requested, always include the associated account name.
• For any query related to customer revenue—in any form—always reference the ARR field from the Accounts module. This is non-negotiable.
• For any query about products or services, first retrieve the value from the Zoho_Service field in the Deals module and use that data as the basis for analysis before generating a response.
• When the results are limited, explicitly state the total number of records available (n) before displaying the sample records.
• When the query returns test accounts for Deals and Accounts, warn the user that these are test accounts. Test account email domains: @zodoor.com, @zohotest.com
• The currency is only in dollars. Give the revenue fields as dollars.

Knowledge Base

select id
from Contacts
where (
Account_Name in (
select id
from Accounts
where id is not null
)
and Created_Time between (
select Created_Time
from Accounts
where Account_Name like 'A%'
Limit 1
) and (
select Created_Time
from Accounts
where Account_Name like 'B%'
Limit 1
)
)
and Vendor_Name in (
select id
from Vendors
where id in (
select id
from Vendors
where Vendor_Name like 'V%'
)
)

select Last_Name
from Leads
where Created_Time between (
select Created_Time
from Leads
where id is not null
Limit 1
) and (
select Created_Time
from Leads
where id is not null
limit 200,1
)

select Last_Name
from Leads
where Created_Time between (
select Created_Time
from Leads
where id is not null
Limit 1
) and '2024-09-25T23:59:59+05:30'

SELECT COUNT(id) AS 'Total Leads', Lead_Status
FROM Leads
where id is not null
GROUP BY Lead_Status
JOINS
1. select
'Account_Name','Account_Name.Account_Name','Account_Name.
Parent_Account','Account_Name.Parent_Account.Account_Name' from Contacts where (Account_Name.Parent_Account.Account_Name = 'King') limit1
2. select id from Contacts where Owner.role is not null
3. select id from Contacts order by Owner.role,Created_By.profile
4. { "select_query": "select Owner.profile, Created_By.role from Contacts where id is not null" } // Owner.profile and Created_By.role lookup fields are used only in the SELECT column. Hence, this is considered a SELECT column

select id from Contacts where id is not null order by id,id

Custom Tools

1. Create a connection with the following scopes: ZohoCRM.modules.ALL, ZohoCRM.coql.READ, ZohoCRM.modules.emails.READ
2. Create a custom tool group with the three tools whose YAML files are provided below and validate each one of them.
3. To test the tools, associate them with the created connection.

openapi: 3.0.1
info:
  title: Execute COQL Query
  description: Execute a COQL query on CRM data
  version: v8
servers:
- url: https://www.zohoapis.com/crm/v8
security:
- OAuth2:
  - ZohoCRM.modules.ALL
  - ZohoCRM.coql.READ
paths:
  /coql:
    post:
      summary: Execute COQL Query
      description: Execute a COQL query on CRM data
      operationId: executeCrmCoqlQuery
      requestBody:
        content:
          application/json:
            schema:
              required:
              - select_query
              type: object
              properties:
                select_query:
                  type: string
                  description: |
                    A valid COQL query. Example: `select Field_name, COUNT(id) as alias_name from Module_name WHERE Field_name is not null group by Field_name`
        required: true
      responses:
        "200":
          description: Query executed successfully.
          content:
            application/json:
              schema:
                type: object
                properties:
                  data:
                    type: array
                    items:
                      type: object
                      additionalProperties: true
        "400":
          description: Bad Request.
        "401":
          description: Unauthorized.
        "403":
          description: Forbidden.
components:
  securitySchemes:
    OAuth2:
      type: oauth2
      flows:
        authorizationCode:
          authorizationUrl: https://accounts.zoho.com/oauth/v2/auth
          tokenUrl: https://accounts.zoho.com/oauth/v2/token
          scopes:
            ZohoCRM.coql.READ: execute COQL API queries
            ZohoCRM.modules.ALL: allows access to all modules

openapi: 3.0.1
info:
  title: Get a custom module record by ID
o
  description: Retrieve a single record from a custom module
  version: v8
servers:
- url: https://www.zohoapis.com/crm/v8
security:
- OAuth2:
  - ZohoCRM.modules.ALL
paths:
  /{custom_module_api_name}/{record_id}:
    get:
      summary: Get a custom module record by ID
      description: Retrieve a single record from a custom module
      operationId: getCustomModuleRecordById
      parameters:
      - name: custom_module_api_name
        in: path
        required: true
        style: simple
        explode: false
        schema:
          type: string
      - name: record_id
        in: path
        required: true
        style: simple
        explode: false
        schema:
          type: string
      responses:
        "200":
          description: Record fetched successfully.
          content:
            application/json:
              schema:
                type: object
                additionalProperties: true
        "404":
          description: Record not found.
components:
  securitySchemes:
    OAuth2:
      type: oauth2
      flows:
        authorizationCode:
          authorizationUrl: https://accounts.zoho.com/oauth/v2/auth
          tokenUrl: https://accounts.zoho.com/oauth/v2/token
          scopes:
            ZohoCRM.modules.ALL: Access to all CRM modules

openapi: 3.0.1
info:
  title: Get Emails related to a record
  description: Retrieve Emails for a specific CRM record
  version: v8
servers:
- url: https://www.zohoapis.com/crm/v8
security:
- OAuth2:
  - ZohoCRM.modules.ALL
  - ZohoCRM.modules.emails.READ
paths:
  /{module_api_name}/{record_id}/Emails:
    get:
      summary: Get Emails related to a record
      description: Retrieve Emails for a specific CRM record
      operationId: getRelatedEmails
      parameters:
      - name: module_api_name
        in: path
        required: true
        style: simple
        explode: false
        schema:
          type: string
      - name: record_id
        in: path
        required: true
        style: simple
        explode: false
        schema:
          type: string
      - name: type
        in: query
        description: |
          Filter emails by type. For Accounts module, use all_contacts_* variants. When not specified, all emails are returned.
        required: false
        style: form
        explode: true
        schema:
          type: string
          enum:
          - sent_from_crm
          - scheduled_in_crm
          - drafts
          - user_emails
          - all_contacts_sent_crm_emails
          - all_contacts_scheduled_crm_emails
          - all_contacts_draft_crm_emails
      - name: owner_id
        in: query
        description: ID of the user whose emails to fetch. Only usable with type=user_emails.
        required: false
        style: form
        explode: true
        schema:
          type: integer
      - name: index
        in: query
        description: |
          Pagination index. Each call returns 10 emails. Use the next_index value from the previous response to fetch the next set of emails.
        required: false
        style: form
        explode: true
        schema:
          type: string
      responses:
        "200":
          description: Related emails fetched successfully.
          content:
            application/json:
              schema:
                type: object
                properties:
                  Emails:
                    type: array
                    items:
                      type: object
                      additionalProperties: true
                  info:
                    type: object
                    properties:
                      per_page:
                        type: integer
                      next_index:
                        type: string
                      count:
                        type: integer
                      prev_index:
                        type: string
                      more_records:
                        type: boolean
        "400":
          description: Invalid data or cannot process request.
        "403":
          description: Permission denied.
        "404":
          description: Record or emails not found.
components:
  securitySchemes:
    OAuth2:
      type: oauth2
      flows:
        authorizationCode:
          authorizationUrl: https://accounts.zoho.com/oauth/v2/auth
          tokenUrl: https://accounts.zoho.com/oauth/v2/token
          scopes:
            ZohoCRM.modules.ALL: Full module access
            ZohoCRM.modules.emails.READ: Read email data for records