Export conversations to BigQuery

CCAI Insights allows you to 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 do not have a target table, use the sample below 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.

CCAI Insights export currently only supports exporting data to the BigQuery multi-regional location in the US. For instructions on moving data between locations, including non-US locations, see the BigQuery documentation.

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 functionality, 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>'

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.

REST & CMD LINE

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

  • PROJECT_ID: your GCP project ID.
  • LOCATION_ID: the location you chose for your Cloud Storage bucket. The only location currently available is us-central1.
  • 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.

HTTP method and URL:

POST https://contactcenterinsights.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION_ID/insightsdata:export

Request JSON body:

{
  "big_query_destination": {
      "project_id": PROJECT_ID
      "dataset": DATASET
      "table": TABLE
      },
  "filter": FILTER_QUERY
  },
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

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

Python

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:
    # 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")

Node.js

/**
 * 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:service-PROJECT_NUMBER@gcp-sa-contactcenterinsights.iam.gserviceaccount.com \
    --role=roles/bigquery.admin

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 the command below 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"