Export conversations to BigQuery

CCAI Insights lets you export your CCAI Insights conversation and analysis data to BigQuery so that you can perform your own raw queries. The export process writes a schema that is similar to the Speech Analysis Framework. This guide details the export process.

Create a new BigQuery table and dataset

The CCAI Insights Exporter requires a BigQuery table in order for the operation to be successful. If you don't have a target table, use this sample to create a new table and dataset using the bq command-line tool. See the BigQuery schema documentation for the output schema and column definitions.

BigQuery has some restrictions on the location of its data source. See Location considerations. Restrictions that apply to Cloud Storage buckets also apply to CCAI Insights. For example, if your BigQuery dataset is in the EU multi-region location, you can only export CCAI Insights data from one of the europe-* locations.

Warning: CCAI Insights export will truncate any data in the provided table before writing.

bq mk --dataset --location=LOCATION PROJECT:DATASET

bq mk --table PROJECT:DATASET.TABLE

Export conversation data to BigQuery

The export tool supports both filtering and writing data to customer-managed encryption key (CMEK)-protected tables. If you don't want to enable this feature, you can skip ahead and export your data to BigQuery.

Add filtering to the request (Optional)

Export to BigQuery is compatible with all combinations of filters that can be applied to conversation queries. For example, the following sample will export all conversations with 10 or more turns handled by agent_id "007" between January 1st 2021 and January 2nd 2021 PST:

FILTER='create_time>"2021-01-01T00:00:00-08:00" create_time<"2021-01-02T00:00:00-08:00" agent_id="007" turn_count>="10"'

Export data to a CMEK-protected table (Optional)

Provide your CCAI Insights service account with the Cloud KMS CryptoKey Encrypter/Decrypter role. See the known issues documentation about service account format. Once you have provided the correct role to your service account, add the fully-qualified name of the KMS key protecting the table to the export request:

KMS_KEY='projects/<project>/locations/<location>/keyRings/<key_ring>/cryptoKeys/<key_name>'

Specify write disposition option in the request (Optional)

CCAI Insights export supports the following write disposition options from BigQuery:

• WRITE_TRUNCATE: If the table already exists, BigQuery overwrites the table data and uses the schema from the query result. This is the default option.
• WRITE_APPEND: If the table already exists, BigQuery appends the data to the table.

For example, the following sample will append the exported data to an existing destination table:

WRITE_DISPOSITION='WRITE_APPEND'

Export your data to BigQuery

The following code sample demonstrates how to export your data. See the export reference documentation for complete details.

The export creates a long-running Operation object. You can poll the operation to check its status.

Permissions required for this task

To perform this task, you must have the following permissions:

• contactcenterinsights.conversations.list on the parent resource

REST

Before using any of the request data, make the following replacements:

• PROJECT_ID: your Google Cloud project ID.
• DATASET: the name of the BigQuery dataset that the data should be exported to.
• TABLE: the BigQuery table name that your CCAI Insights data should be written to.
• FILTER_QUERY: a query that CCAI Insights uses to export only conversations that have specific properties. For example, entering the value "agent_id=\"007\"" will result in only conversations associated with agent 007 being exported.

Note: All the examples are using the global Contact Center AI Insights instance. Refer to Regionalization for instructions on using regional instances.

HTTP method and URL:

POST https://contactcenterinsights.googleapis.com/v1/projects/PROJECT_ID/locations/us-central1/insightsdata:export

Request JSON body:

{
  "bigQueryDestination": {
    "projectId": "PROJECT_ID",
    "dataset": "DATASET",
    "table": "TABLE",
  },
  "filter": "FILTER_QUERY"
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell, which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list.

Save the request body in a file named request.json, and execute the following command:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://contactcenterinsights.googleapis.com/v1/projects/PROJECT_ID/locations/us-central1/insightsdata:export"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list.

Save the request body in a file named request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://contactcenterinsights.googleapis.com/v1/projects/PROJECT_ID/locations/us-central1/insightsdata:export" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "name": "projects/PROJECT_ID/locations/us-central1/operations/OPERATION_ID"
}

Python

To authenticate to CCAI Insights, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

from google.cloud import contact_center_insights_v1


def export_to_bigquery(
    project_id: str,
    bigquery_project_id: str,
    bigquery_dataset_id: str,
    bigquery_table_id: str,
) -> None:
    """Exports data to BigQuery.

    Args:
        project_id:
            The project identifier that owns the data source to be exported.
            For example, 'my-project'.
        bigquery_project_id:
            The project identifier that owns the BigQuery sink to export data to.
            For example, 'my-project'.
        bigquery_dataset_id:
            The BigQuery dataset identifier. For example, 'my-dataset'.
        bigquery_table_id:
            The BigQuery table identifier. For example, 'my-table'.

    Returns:
        None.
    """
    # Construct an export request.
    request = contact_center_insights_v1.ExportInsightsDataRequest()
    request.parent = (
        contact_center_insights_v1.ContactCenterInsightsClient.common_location_path(
            project_id, "us-central1"
        )
    )
    request.big_query_destination.project_id = bigquery_project_id
    request.big_query_destination.dataset = bigquery_dataset_id
    request.big_query_destination.table = bigquery_table_id
    request.filter = 'agent_id="007"'

    # Call the Insights client to export data to BigQuery.
    insights_client = contact_center_insights_v1.ContactCenterInsightsClient()
    export_operation = insights_client.export_insights_data(request=request)
    export_operation.result(timeout=600000)
    print("Exported data to BigQuery")

Java

To authenticate to CCAI Insights, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

import com.google.api.gax.longrunning.OperationTimedPollAlgorithm;
import com.google.api.gax.retrying.RetrySettings;
import com.google.cloud.contactcenterinsights.v1.ContactCenterInsightsClient;
import com.google.cloud.contactcenterinsights.v1.ContactCenterInsightsSettings;
import com.google.cloud.contactcenterinsights.v1.ExportInsightsDataRequest;
import com.google.cloud.contactcenterinsights.v1.ExportInsightsDataResponse;
import com.google.cloud.contactcenterinsights.v1.LocationName;
import java.io.IOException;
import org.threeten.bp.Duration;

public class ExportToBigquery {

  public static void main(String[] args) throws Exception, IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "my_project_id";
    String bigqueryProjectId = "my_bigquery_project_id";
    String bigqueryDataset = "my_bigquery_dataset";
    String bigqueryTable = "my_bigquery_table";

    exportToBigquery(projectId, bigqueryProjectId, bigqueryDataset, bigqueryTable);
  }

  public static void exportToBigquery(
      String projectId, String bigqueryProjectId, String bigqueryDataset, String bigqueryTable)
      throws Exception, IOException {
    // Set the operation total polling timeout to 24 hours instead of the 5-minute default.
    // Other values are copied from the default values of {@link ContactCenterInsightsStubSettings}.
    ContactCenterInsightsSettings.Builder clientSettings =
        ContactCenterInsightsSettings.newBuilder();
    clientSettings
        .exportInsightsDataOperationSettings()
        .setPollingAlgorithm(
            OperationTimedPollAlgorithm.create(
                RetrySettings.newBuilder()
                    .setInitialRetryDelay(Duration.ofMillis(5000L))
                    .setRetryDelayMultiplier(1.5)
                    .setMaxRetryDelay(Duration.ofMillis(45000L))
                    .setInitialRpcTimeout(Duration.ZERO)
                    .setRpcTimeoutMultiplier(1.0)
                    .setMaxRpcTimeout(Duration.ZERO)
                    .setTotalTimeout(Duration.ofHours(24L))
                    .build()));

    // Initialize client that will be used to send requests. This client only needs to be created
    // once, and can be reused for multiple requests. After completing all of your requests, call
    // the "close" method on the client to safely clean up any remaining background resources.
    try (ContactCenterInsightsClient client =
        ContactCenterInsightsClient.create(clientSettings.build())) {
      // Construct an export request.
      LocationName parent = LocationName.of(projectId, "us-central1");
      ExportInsightsDataRequest request =
          ExportInsightsDataRequest.newBuilder()
              .setParent(parent.toString())
              .setBigQueryDestination(
                  ExportInsightsDataRequest.BigQueryDestination.newBuilder()
                      .setProjectId(bigqueryProjectId)
                      .setDataset(bigqueryDataset)
                      .setTable(bigqueryTable)
                      .build())
              .setFilter("agent_id=\"007\"")
              .build();

      // Call the Insights client to export data to BigQuery.
      ExportInsightsDataResponse response = client.exportInsightsDataAsync(request).get();
      System.out.printf("Exported data to BigQuery");
    }
  }
}

Node.js

To authenticate to CCAI Insights, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.

/**
 * TODO(developer): Uncomment these variables before running the sample.
 */
// const projectId = 'my_project_id';
// const bigqueryProjectId = 'my_bigquery_project_id';
// const bigqueryDataset = 'my_bigquery_dataset';
// const bigqueryTable = 'my_bigquery_table';

// Imports the Contact Center Insights client.
const {
  ContactCenterInsightsClient,
} = require('@google-cloud/contact-center-insights');

// Instantiates a client.
const client = new ContactCenterInsightsClient();

async function exportToBigquery() {
  const [operation] = await client.exportInsightsData({
    parent: client.locationPath(projectId, 'us-central1'),
    bigQueryDestination: {
      projectId: bigqueryProjectId,
      dataset: bigqueryDataset,
      table: bigqueryTable,
    },
    filter: 'agent_id="007"',
  });

  // Wait for the operation to complete.
  await operation.promise();
  console.info('Exported data to BigQuery');
}
exportToBigquery();

Export data to another project (Optional)

By default, CCAI Insights BigQuery export writes data to the same project that owns the CCAI Insights data. However, you can also export to BigQuery in another project.

Ensure that your CCAI Insights service account has BigQuery access to the recipient project using either the IAM console or with gcloud:

gcloud projects add-iam-policy-binding RECEIVER_PROJECT \
    --member=serviceAccount:[e-mail] \
    --role=roles/bigquery.admin

Note: If you want to restrict access further, you can also grant the service account access to a specific BigQuery dataset.

To export your data to a specific project, input the recipient project's ID number in the project_id field in the BigQueryDestination object.

Query the data in BigQuery

Run this command to query the data in BigQuery. See the BigQuery Quickstart documentation for more query options:

gcloud config set project PROJECT
bq show DATASET.TABLE

Querying exported conversations:

bq query --use_legacy_sql=false \
   "SELECT conversationName FROM DATASET.TABLE"