Autopilot

Knowi connects to Autopilot (now Ortto) to pull contact data, lists, smart segments, and custom fields into a unified analytics platform.

Combine your marketing automation contact data with CRM, support, and product analytics data for a complete customer view.

Overview

Connect to Autopilot using your API key. Knowi supports 7 collections covering contacts, lists, segments, and custom field definitions.

Connecting

  1. Log in to Knowi and select "Queries" from the left sidebar.

  2. Click on the "New Datasource +" button and select AutoPilot from the list of datasources.

  3. Configure the following:

    a. Datasource Name: Enter a name (e.g., "Marketing Autopilot")

    b. API Key: Enter your Autopilot API key. Generate one in Autopilot under Settings > Autopilot API.

  4. Click "Save" to finish setup.

Collections

Contacts

Retrieves all contacts with full pagination support (up to 2000 pages). Includes standard and custom fields.

Default Cloud9QL:

select expand(contacts);
select expand(custom_fields);
select transpose(kind, value, email);

The transpose function pivots custom field key-value pairs into columns, with email as the row identifier.

Contact Search

Looks up a single contact by email address.

Required Parameters: * Email - The email address to search for.

Lists

Retrieves all contact lists defined in your Autopilot account.

Contacts From List

Retrieves contacts belonging to a specific list.

Optional Parameters: * List ID - The list ID to retrieve contacts from. Use the Lists collection to find list IDs.

Smart Segments

Retrieves all smart segment definitions.

Smart Segment Contacts

Retrieves contacts belonging to a specific smart segment.

Optional Parameters: * Smart Segment ID - The segment ID. Use the Smart Segments collection to find segment IDs.

Custom Fields

Retrieves custom field definitions configured in your Autopilot account.

Common Use Cases

Contact Growth Analysis

select expand(contacts);
select datetrunc('month', created_at) as month, count(*) as new_contacts
group by month
order by month;

List Membership Breakdown

Query the Lists collection first to get list IDs, then use Contacts From List to analyze each list:

select expand(contacts);
select expand(custom_fields);
select transpose(kind, value, email);
select count(*) as total_contacts;

Scheduling

Schedule queries to run automatically. The Contacts collection supports pagination with a 2000-page limit, covering very large contact databases.

Notes