Getting Started: Build a Customer Health AI Agent with Microsoft Copilot Studio and CData Connect AI


Microsoft Copilot Studio is a powerful no-code/low-code platform for creating AI agents that can automate tasks, answer questions, and assist with various business processes. When combined with CData Connect AI, you can build intelligent agents that analyze customer health metrics from live data sources like Google Sheets, providing real-time insights and actionable recommendations.

In this article, we'll guide you through creating a customer health AI agent that analyzes account information, sales opportunities, support tickets, and product usage data to provide a comprehensive view of customer health and suggest courses of action.

Prerequisites

You need the following to follow along with this guide:

Overview

Here's a quick overview of the steps:

  1. Copy the sample Google Sheet with customer health data
  2. Sign up for CData Connect AI and configure Google Sheets connection
  3. Create and configure a Microsoft Copilot Studio agent
  4. Test the customer health AI agent

Step 1: Copy the Sample Google Sheet

We'll start by copying a sample Google Sheet that contains customer health data including accounts, opportunities, support tickets, and product usage information.

  1. Navigate to the sample customer health spreadsheet in your browser.
  2. Click File > Make a copy to save a copy to your Google Drive.

    NOTE: Remember the name you give to the copy (e.g., "demo_organization"). You'll need this name when configuring the connection in Connect AI.

The spreadsheet contains four sheets with related customer data:

  • account: Company information including name, industry, revenue, and employee count
  • opportunity: Sales opportunities with stage, amount, probability, and close dates
  • tickets: Customer support tickets with priority, status, and descriptions
  • usage: Product usage metrics including job runs, records processed, and revenue data

Step 2: Configure Google Sheets Connectivity in Connect AI

Now we'll set up the connection to your Google Sheet in CData Connect AI.

Sign up or Log in to Connect AI

  1. Navigate to https://www.cdata.com/ai/signup/ to create a new account, or https://cloud.cdata.com/ to log in to your existing account.
  2. Complete the sign-up process or log in with your credentials.

Add a Google Sheets Connection

  1. Once logged in to Connect AI, click Sources in the left navigation menu and click Add Connection.
  2. Select Google Sheets from the Add Connection panel.
  3. In the connection configuration:
    • Set the Spreadsheet property to the name of your copied Google Sheet (e.g., "demo_organization")
    • Click Sign in to authenticate with Google using OAuth
  4. After successful authentication, navigate to the Permissions tab and configure user-based permissions as needed.

Create a Personal Access Token

A Personal Access Token (PAT) is required to authenticate the connection from Microsoft Copilot Studio to Connect AI.

  1. Click the gear icon () at the top right of Connect AI to open Settings.
  2. Go to the Access Tokens section and click Create PAT.
  3. Give the PAT a descriptive name (e.g., "Microsoft Copilot Studio") and click Create.
  4. Copy the PAT value immediately and store it securely—it will only be visible once.

    NOTE: You'll need this PAT in the next step when configuring Microsoft Copilot Studio.

Step 3: Create and Configure the Microsoft Copilot Studio Agent

With Connect AI configured, we'll now create an AI agent in Microsoft Copilot Studio that can analyze customer health.

Create a New Agent

  1. Navigate to Microsoft Copilot Studio and sign in.
  2. In the navigation menu, click Agents, then click + New agent.
  3. Toggle the Configure button and enter:
    • Name: Customer Health Advisor
    • Description: An AI agent that analyzes customer health metrics and provides actionable recommendations
    • Instructions: You are a customer success expert who analyzes customer data to assess health and recommend actions. You have access to account information, sales opportunities, support tickets, and product usage data.
  4. Click Create in the upper right to create the agent.

Add the Connect AI MCP Tool

  1. Under Tools, click Add tool, then click + New Tool.
  2. In the Add Tool window, click Model Context Protocol.
  3. In the Add a Model Context Protocol server window, enter:
    • Server Name: CData Connect AI MCP Server
    • Server Description: Provides access to customer health data from Google Sheets including accounts, opportunities, support tickets, and product usage metrics
    • Server URL: https://mcp.cloud.cdata.com/mcp
    • Authentication Type: API Key
    • Header Name: Authorization
    Click Create.
  4. When prompted to choose a connection, click Create new connection.
  5. Set the connection value to: 
    Basic YOUR_EMAIL:YOUR_PAT

    Replace 

    YOUR_EMAIL
    with your Connect AI email address and 
    YOUR_PAT
    with the Personal Access Token you created earlier.

    For example: 

    Basic john.doe@company.com:Uu90pt5vEO...

  6. Click Add to agent to add the MCP tool to your agent.

Enhance the Agent with Detailed Instructions

To improve the agent's effectiveness, you can add more detailed instructions about its role and the available tools:

  1. In your agent settings, navigate to the Instructions section.
  2. Add the following enhanced instructions: 
    You are a customer success expert who analyzes customer health data and provides actionable recommendations. You have access to the CData Connect AI MCP Server which provides the following tools:

- getCatalogs: Retrieve available data connections
- getSchemas: Get database schemas for a specific catalog
- getTables: List available tables (accounts, opportunities, tickets, usage data)
- getColumns: View table structure and column details
- queryData: Execute SQL queries to analyze customer data

When analyzing customer health, consider:
1. Account information (industry, revenue, size)
2. Sales pipeline (opportunities, stages, amounts)
3. Support ticket volume and severity
4. Product usage metrics (adoption, engagement)

Always use fully qualified table names in queries: catalog.schema.table
Provide specific, actionable recommendations based on the data.

Step 4: Test the Customer Health AI Agent

Now it's time to test your customer health AI agent with real queries.

  1. Open the chat window in your Microsoft Copilot Studio agent.
  2. Try asking questions like:
    • "Show me a picture of the health for Aurora Healthcare Systems"
    • "Which customers have high support ticket volume but low product usage?"
    • "Analyze the top 5 accounts by revenue and identify any health risks"
    • "What accounts have open opportunities but also critical support tickets?"
  3. The agent will use the MCP tools to query your Google Sheets data, analyze the customer health metrics, and provide recommendations.

Example: Customer Health Analysis

When you ask the agent to "create a picture of customer health for Aurora Healthcare Systems", it will:

  1. Query the account table for company information
  2. Analyze opportunities to understand the sales pipeline
  3. Review support tickets for any issues or trends
  4. Examine product usage metrics to gauge adoption
  5. Synthesize all data into a comprehensive health assessment
  6. Suggest specific actions such as:
    • Schedule a check-in call if support tickets are increasing
    • Offer additional training if usage metrics are declining
    • Identify upsell opportunities based on successful usage patterns
    • Flag at-risk accounts with multiple warning signs

Next Steps

Now that you have a working customer health AI agent, you can:

  • Connect additional data sources beyond Google Sheets (Salesforce, Zendesk, Snowflake, etc.)
  • Create more sophisticated queries that join data across multiple tables
  • Build custom workflows and automation based on health scores
  • Export insights to other systems using Connect AI's integration capabilities
  • Refine the agent's instructions to align with your specific business processes

Get Started with CData Connect AI

To get live data access to 300+ SaaS, Big Data, and NoSQL sources directly from your AI applications, try CData Connect AI today!

In this article

Related articles