Using Zoho CRM SKILL.md to generate complex COQL queries using natural language

Using Zoho CRM SKILL.md to generate complex COQL queries using natural language



Hello everyone,
In the previous Kaizen posts, we introduced the zoho-crm SKILL.md and discussed how it can help you when you are working with  Zoho CRM APIs and Client Script.
In this Kaizen, we will focus on using the Zoho CRM `SKILL.md` with GitHub Copilot to solve complex COQL requirements, particularly nested subqueries and JOINs inside subqueries.

Use case 1: Retrieve contacts associated with high-value customers

Requirement

Find contacts associated with Retail accounts that have at least one Closed Won deal worth more than $50,000. Return each contact’s name and email.

Prompt

"
Find all contacts associated with accounts in the Retail industry that have at least one Closed Won deal worth more than $50,000.
For each matching contact, return the contact's full name and email address. Use the appropriate Zoho CRM data-retrieval mechanism and provide the query. Explain how the relationships between Contacts, Accounts, and Deals are handled, including any relevant limitations.
"


Instead of generating a query immediately, the Zoho CRM skill first analyzed the lookup relationships between Contacts, Accounts, and Deals. It identified that Contacts and Deals are both associated with Accounts, but that Contacts do not have a direct lookup relationship to Deals.
Based on this analysis, the skill proposed two methods:
  1. A two-step approach: Retrieve the qualifying Account IDs first, then use those IDs to retrieve the associated Contacts.
    1. Retrieve Account IDs for Closed Won Retail Deals over $50,000
      {
        "select_query": "select Account_Name from Deals where (Account_Name.Industry = 'Retail' and (Stage = 'Closed Won' and Amount > 50000)) limit 0, 200"
      }

    2. Fetch Contacts for those Accounts
      {
       "select_query": "select First_Name, Last_Name, Email from Contacts where Account_Name in ('5843104000002543041','5843104000000425263') limit 0, 200"
      }
  2. A subquery-based approach: 
    Retrieve the Contacts in a single query, subject to the limitations of subquery result handling. The skill also highlighted considerations such as pagination, the maximum number of records supported by a subquery, duplicate Account IDs, and the need to verify field API names against the target CRM organization.
    {

    "select_query": "select First_Name, Last_Name, Email from Contacts where (Account_Name.Industry = 'Retail' and Account_Name in (select Account_Name from Deals where (Stage = 'Closed Won' and Amount > 50000))) limit 0, 200"

    }



           
This illustrates how the skill can assist not only with COQL syntax, but also with relationship analysis, query design, and identifying potential data-volume limitations.

Use case 2: Deals linked to active campaigns and high-revenue accounts

Requirement

Find deals associated with active campaigns and accounts with annual revenue greater than $10 million. Return the deal name, amount, account name, campaign name, and contact email.

Prompt
"
/zoho-crm I need to analyze deals associated with active marketing campaigns and high-revenue accounts. Find deals where the associated campaign has an Active status and the associated account has annual revenue greater than $10 million. For each matching deal, return the deal name, deal amount, account name, campaign name, and contact email. Use the appropriate Zoho CRM data-retrieval mechanism. Explain which relationships are used to retrieve related fields and which conditions require further filtering. Identify any limitations or assumptions in the generated solution.
"



{
  "select_query": "select Deal_Name, Amount, Account_Name.Account_Name, Campaign_Source.Campaign_Name, Contact_Name.Email from Deals where (Campaign_Source.Status = 'Active' and Account_Name.Annual_Revenue > 10000000) limit 0, 200"
}

How the query works

  1. Deals is the base module.
  2. Account_Name.Account_Name retrieves the associated account name.
  3. Campaign_Source.Campaign_Name retrieves the campaign name.
  4. Contact_Name.Email retrieves the email address from the associated contact.
  5. Campaign_Source.Status = 'Active' filters deals based on the campaign's status.
  6. Account_Name.Annual_Revenue > 10000000 filters deals based on the associated account's annual revenue.

Use case 3: Analyze Expected Revenue across deal stages

Requirement

A sales manager wants to understand the total Expected Revenue generated at each deal stage. The analysis should include the Stage and the corresponding total Expected Revenue.

Prompt

"
A sales manager wants to understand the total Expected Revenue at each Stage. Find all deals, analyze them based on their stage, and calculate the total Expected Revenue for each stage. Return the Stage and total Expected Revenue, and explain how the generated solution works, including any limitations or assumptions.
"



{
  "select_query": "select Stage, SUM(Expected_Revenue) as Total_Expected_Revenue from Deals where (Stage is not null) group by Stage order by Stage asc limit 0, 200"
}



Validation is as important as generation

  1. A syntactically valid query may still fail to meet the business requirement. Always verify:
  2. The correct fields and lookup relationships are used.
  3. Subqueries do not exceed record limits.
  4. Owner and picklist values are valid.
  5. Pagination and duplicate records are handled.
  6. The query returns the intended records, not merely related records.

Conclusion

By using the Zoho CRM `SKILL.md` with GitHub Copilot, developers can explore how an AI coding agent handles these requirements, from identifying the relevant modules to constructing and validating the query.
The most valuable outcome is not simply a generated COQL statement. It is understanding how the SKILL.md translates a business requirement into a technically valid and appropriately scoped CRM data-retrieval solution.
Try the examples with GitHub Copilot, compare the generated solutions with the documented COQL patterns, and examine how the SKILL.md  handles limitations and ambiguities.
Happy querying!