Skip to content

Week 1 Day 1: Prerequisites

Required Information Gathering

Create configuration file: ~/gcp-wxcc-deployment/config.yaml

# GCP Configuration

gcp:
  organization_id: "123456789012"
  billing_account_id: "01234A-567890-BCDEF1"
  project_id: "abhavtech-wxcc-prod"
  region: "us-east4"
  zone: "us-east4-a"

## Network Information

network:
  on_premise_cidr: "10.252.0.0/16"
  mumbai_nat_ip: "203.0.113.50"
  london_nat_ip: "203.0.113.100"
  nj_nat_ip: "203.0.113.75"

## WxCC Information

wxcc:
  region: "us-east1"
  webhook_url: "https://webhook.wxcc-us1.cisco.com"
  api_endpoint: "https://api.wxcc-us1.cisco.com/v1"
  ip_ranges:
    - "52.0.0.0/8"
    - "44.224.0.0/11"
  agent_count: 175
  daily_call_volume: 5000

## Splunk Integration

splunk:
  hec_url: "https://splunk.abhavtech.com:8088"
  index: "wxcc_analytics"

Development Environment Setup

## Create project directory

mkdir -p ~/gcp-wxcc-deployment/{terraform,scripts,configs,docs}
cd ~/gcp-wxcc-deployment

## Install Terraform

curl -fsSL https://apt.releases.hashicorp.com/gpg | sudo apt-key add -
sudo apt-add-repository "deb [arch=amd64] https://apt.releases.hashicorp.com $(lsb_release -cs) main"
sudo apt-get update && sudo apt-get install terraform

## Install gcloud CLI

curl https://sdk.cloud.google.com | bash
exec -l $SHELL

## Authenticate

gcloud auth login
gcloud auth application-default login

## Install Python dependencies

pip3 install --upgrade \
  google-cloud-aiplatform==1.38.0 \
  google-cloud-bigquery==3.13.0 \
  google-cloud-storage==2.14.0 \
  google-cloud-dlp==3.12.0 \
  google-cloud-speech==2.21.0 \
  google-cloud-language==2.11.0 \
  google-cloud-pubsub==2.18.0 \
  google-cloud-functions==1.13.0

## Verify installations

terraform version  # Should be >= 1.5.0
gcloud version     # Should be >= 400.0.0
python3 --version  # Should be >= 3.9.0

## Set environment variables

cat >> ~/.bashrc << 'EOF'
export GCP_PROJECT_ID="abhavtech-wxcc-prod"
export GCP_REGION="us-east4"
export GOOGLE_APPLICATION_CREDENTIALS="$HOME/.gcp/wxcc-admin-key.json"
EOF

source ~/.bashrc

Week 1 Day 2: Project Creation

Terraform Module: Project Foundation

Directory Structure:

terraform/
├── 01-project/
│   ├── main.tf
│   ├── variables.tf
│   ├── terraform.tfvars
│   └── outputs.tf
├── 02-iam/
├── 03-bigquery/
├── 04-storage/
├── 05-pubsub/
└── 06-vpc-sc/

File: terraform/01-project/main.tf

## GCP Project Creation and API Enablement


terraform {
  required_version = ">= 1.5.0"

  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 5.0"
    }
  }

  backend "local" {
    path = "terraform.tfstate"
  }
}

provider "google" {
  billing_project = var.billing_account_id
}

## Create GCP Project

resource "google_project" "wxcc_prod" {
  name            = "Abhavtech WxCC Production"
  project_id      = var.project_id
  org_id          = var.organization_id
  billing_account = var.billing_account_id

  labels = {
    environment = "production"
    application = "wxcc-analytics"
    managed_by  = "terraform"
    cost_center = "contact-center"
  }
}

## Enable Required APIs

resource "google_project_service" "required_apis" {
  for_each = toset([
## Vertex AI and ML

    "aiplatform.googleapis.com",
    "notebooks.googleapis.com",
    "ml.googleapis.com",

## Data Services

    "bigquery.googleapis.com",
    "bigquerystorage.googleapis.com",
    "storage.googleapis.com",
    "storage-api.googleapis.com",

## Security

    "dlp.googleapis.com",
    "secretmanager.googleapis.com",
    "cloudkms.googleapis.com",

## AI Services

    "speech.googleapis.com",
    "language.googleapis.com",
    "dialogflow.googleapis.com",
    "contactcenteraiplatform.googleapis.com",

## Event Processing

    "pubsub.googleapis.com",
    "cloudfunctions.googleapis.com",
    "cloudscheduler.googleapis.com",
    "eventarc.googleapis.com",

## Monitoring and Logging

    "logging.googleapis.com",
    "monitoring.googleapis.com",
    "cloudtrace.googleapis.com",
    "cloudprofiler.googleapis.com",

## Networking and Security

    "compute.googleapis.com",
    "dns.googleapis.com",
    "servicenetworking.googleapis.com",
    "vpcaccess.googleapis.com",

## IAM and Resource Management

    "iam.googleapis.com",
    "iamcredentials.googleapis.com",
    "cloudresourcemanager.googleapis.com",
    "accesscontextmanager.googleapis.com",

## Build and Deploy

    "cloudbuild.googleapis.com",
    "artifactregistry.googleapis.com"
  ])

  project            = google_project.wxcc_prod.project_id
  service            = each.value
  disable_on_destroy = false

  depends_on = [google_project.wxcc_prod]
}

## Wait for APIs to be fully enabled

resource "time_sleep" "wait_for_apis" {
  depends_on      = [google_project_service.required_apis]
  create_duration = "60s"
}

## Project IAM Bindings for Core Services

resource "google_project_iam_member" "default_compute_sa" {
  project = google_project.wxcc_prod.project_id
  role    = "roles/editor"
  member  = "serviceAccount:${google_project.wxcc_prod.number}-compute@developer.gserviceaccount.com"

  depends_on = [time_sleep.wait_for_apis]
}

File: terraform/01-project/variables.tf

variable "organization_id" {
  description = "GCP Organization ID"
  type        = string
}

variable "billing_account_id" {
  description = "GCP Billing Account ID"
  type        = string
}

variable "project_id" {
  description = "Project ID (globally unique, 6-30 chars, lowercase)"
  type        = string

  validation {
    condition     = can(regex("^[a-z][a-z0-9-]{4,28}[a-z0-9]$", var.project_id))
    error_message = "Project ID must be 6-30 characters, start with letter, lowercase, hyphens allowed."
  }
}

variable "region" {
  description = "Default GCP region"
  type        = string
  default     = "us-east4"
}

File: terraform/01-project/terraform.tfvars

## Fill in your actual values

organization_id    = "123456789012"
billing_account_id = "01234A-567890-BCDEF1"
project_id         = "abhavtech-wxcc-prod"
region             = "us-east4"

File: terraform/01-project/outputs.tf

output "project_id" {
  description = "The project ID"
  value       = google_project.wxcc_prod.project_id
}

output "project_number" {
  description = "The project number"
  value       = google_project.wxcc_prod.number
}

output "project_name" {
  description = "The project name"
  value       = google_project.wxcc_prod.name
}

Deployment

cd ~/gcp-wxcc-deployment/terraform/01-project

## Initialize Terraform

terraform init

## Validate configuration

terraform validate

## Plan deployment

terraform plan -out=tfplan

## Review plan carefully

terraform show tfplan

## Apply configuration

terraform apply tfplan

## Capture outputs

export PROJECT_ID=$(terraform output -raw project_id)
export PROJECT_NUMBER=$(terraform output -raw project_number)

## Save to environment

cat >> ~/.bashrc << EOF
export GCP_PROJECT_ID="$PROJECT_ID"
export GCP_PROJECT_NUMBER="$PROJECT_NUMBER"
EOF

source ~/.bashrc

## Verify project created

gcloud projects describe $PROJECT_ID

## Verify APIs enabled

gcloud services list --project=$PROJECT_ID --enabled

Week 1 Day 3: Service Accounts

Terraform Module: IAM Configuration

File: terraform/02-iam/main.tf

## Service Accounts and IAM Configuration


terraform {
  required_version = ">= 1.5.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 5.0"
    }
  }
}

provider "google" {
  project = var.project_id
}

## Service Account: Vertex AI Workload

resource "google_service_account" "vertex_ai" {
  account_id   = "vertex-ai-workload"
  display_name = "Vertex AI Workload Service Account"
  description  = "Used by Vertex AI for training and prediction jobs"
  project      = var.project_id
}

## Service Account: BigQuery Operations

resource "google_service_account" "bigquery_ops" {
  account_id   = "bigquery-operations"
  display_name = "BigQuery Operations Service Account"
  description  = "Used for BigQuery data operations and ML"
  project      = var.project_id
}

## Service Account: Cloud Functions

resource "google_service_account" "cloud_functions" {
  account_id   = "wxcc-data-pipeline"
  display_name = "WxCC Data Pipeline Service Account"
  description  = "Used by Cloud Functions for data processing"
  project      = var.project_id
}

## Service Account: WxCC Integration

resource "google_service_account" "wxcc_integration" {
  account_id   = "wxcc-integration"
  display_name = "WxCC Integration Service Account"
  description  = "Used by WxCC to push data to GCP"
  project      = var.project_id
}

## Service Account: Cloud Scheduler

resource "google_service_account" "cloud_scheduler" {
  account_id   = "cloud-scheduler"
  display_name = "Cloud Scheduler Service Account"
  description  = "Used by Cloud Scheduler for triggering jobs"
  project      = var.project_id
}

## IAM Roles: Vertex AI Service Account

resource "google_project_iam_member" "vertex_ai_roles" {
  for_each = toset([
    "roles/aiplatform.user",
    "roles/aiplatform.serviceAgent",
    "roles/bigquery.dataEditor",
    "roles/bigquery.jobUser",
    "roles/storage.objectViewer",
    "roles/dlp.user",
    "roles/logging.logWriter",
    "roles/monitoring.metricWriter"
  ])

  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.vertex_ai.email}"
}

## IAM Roles: BigQuery Operations Service Account

resource "google_project_iam_member" "bigquery_ops_roles" {
  for_each = toset([
    "roles/bigquery.admin",
    "roles/bigquery.dataEditor",
    "roles/bigquery.jobUser",
    "roles/storage.objectViewer"
  ])

  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.bigquery_ops.email}"
}

## IAM Roles: Cloud Functions Service Account

resource "google_project_iam_member" "cloud_functions_roles" {
  for_each = toset([
    "roles/aiplatform.user",
    "roles/bigquery.dataEditor",
    "roles/bigquery.jobUser",
    "roles/storage.objectAdmin",
    "roles/dlp.user",
    "roles/pubsub.subscriber",
    "roles/pubsub.publisher",
    "roles/logging.logWriter",
    "roles/monitoring.metricWriter",
    "roles/secretmanager.secretAccessor"
  ])

  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.cloud_functions.email}"
}

## IAM Roles: WxCC Integration Service Account

resource "google_project_iam_member" "wxcc_integration_roles" {
  for_each = toset([
    "roles/bigquery.dataEditor",
    "roles/storage.objectCreator",
    "roles/pubsub.publisher",
    "roles/logging.logWriter"
  ])

  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.wxcc_integration.email}"
}

## IAM Roles: Cloud Scheduler Service Account

resource "google_project_iam_member" "cloud_scheduler_roles" {
  for_each = toset([
    "roles/cloudscheduler.jobRunner",
    "roles/pubsub.publisher"
  ])

  project = var.project_id
  role    = each.value
  member  = "serviceAccount:${google_service_account.cloud_scheduler.email}"
}

## Generate Service Account Key for WxCC Integration

resource "google_service_account_key" "wxcc_integration_key" {
  service_account_id = google_service_account.wxcc_integration.name
  public_key_type    = "TYPE_X509_PEM_FILE"
}

## Store WxCC Integration Key in Secret Manager

resource "google_secret_manager_secret" "wxcc_sa_key" {
  secret_id = "wxcc-integration-sa-key"

  replication {
    auto {}
  }

  labels = {
    purpose = "wxcc-integration"
  }
}

resource "google_secret_manager_secret_version" "wxcc_sa_key_version" {
  secret      = google_secret_manager_secret.wxcc_sa_key.id
  secret_data = base64decode(google_service_account_key.wxcc_integration_key.private_key)
}

## Grant Cloud Functions access to secrets

resource "google_secret_manager_secret_iam_member" "cloud_functions_secret_access" {
  secret_id = google_secret_manager_secret.wxcc_sa_key.id
  role      = "roles/secretmanager.secretAccessor"
  member    = "serviceAccount:${google_service_account.cloud_functions.email}"
}

File: terraform/02-iam/variables.tf

variable "project_id" {
  description = "GCP Project ID"
  type        = string
}

File: terraform/02-iam/terraform.tfvars

project_id = "abhavtech-wxcc-prod"

File: terraform/02-iam/outputs.tf

output "vertex_ai_sa_email" {
  value = google_service_account.vertex_ai.email
}

output "bigquery_ops_sa_email" {
  value = google_service_account.bigquery_ops.email
}

output "cloud_functions_sa_email" {
  value = google_service_account.cloud_functions.email
}

output "wxcc_integration_sa_email" {
  value = google_service_account.wxcc_integration.email
}

output "wxcc_integration_key_secret" {
  value = google_secret_manager_secret.wxcc_sa_key.id
}

Deployment

cd ~/gcp-wxcc-deployment/terraform/02-iam

terraform init
terraform plan -out=tfplan
terraform apply tfplan

## Capture service account emails

export VERTEX_AI_SA=$(terraform output -raw vertex_ai_sa_email)
export WXCC_SA=$(terraform output -raw wxcc_integration_sa_email)
export CF_SA=$(terraform output -raw cloud_functions_sa_email)

## Save to environment

cat >> ~/.bashrc << EOF
export VERTEX_AI_SA="$VERTEX_AI_SA"
export WXCC_SA="$WXCC_SA"
export CF_SA="$CF_SA"
EOF

## Verify service accounts

gcloud iam service-accounts list --project=$PROJECT_ID

## Download WxCC integration key (for WxCC configuration later)

gcloud secrets versions access latest \
  --secret="wxcc-integration-sa-key" \
  --project=$PROJECT_ID > ~/.gcp/wxcc-sa-key.json

chmod 600 ~/.gcp/wxcc-sa-key.json

Week 1 Day 4: BigQuery Setup

Terraform Module: BigQuery Dataset

File: terraform/03-bigquery/main.tf

## BigQuery Dataset and Tables for WxCC Analytics


terraform {
  required_version = ">= 1.5.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 5.0"
    }
  }
}

provider "google" {
  project = var.project_id
}

## KMS Key Ring for BigQuery Encryption

resource "google_kms_key_ring" "bigquery_keyring" {
  name     = "bigquery-keyring"
  location = "us"
}

## KMS Crypto Key for BigQuery

resource "google_kms_crypto_key" "bigquery_key" {
  name            = "bigquery-encryption-key"
  key_ring        = google_kms_key_ring.bigquery_keyring.id
  rotation_period = "7776000s"  # 90 days

  purpose = "ENCRYPT_DECRYPT"

  lifecycle {
    prevent_destroy = true
  }
}

## Grant BigQuery service account access to KMS key

data "google_project" "project" {
  project_id = var.project_id
}

resource "google_kms_crypto_key_iam_member" "bigquery_key_access" {
  crypto_key_id = google_kms_crypto_key.bigquery_key.id
  role          = "roles/cloudkms.cryptoKeyEncrypterDecrypter"
  member        = "serviceAccount:bq-${data.google_project.project.number}@bigquery-encryption.iam.gserviceaccount.com"
}

## BigQuery Dataset

resource "google_bigquery_dataset" "wxcc_analytics" {
  dataset_id    = "wxcc_analytics"
  friendly_name = "WxCC Analytics Dataset"
  description   = "Contact center analytics data from Webex Contact Center"
  location      = "US"

  default_table_expiration_ms = 31536000000  # 365 days

  labels = {
    environment = "production"
    data_source = "wxcc"
    sensitivity = "high"
  }

  default_encryption_configuration {
    kms_key_name = google_kms_crypto_key.bigquery_key.id
  }

  access {
    role          = "OWNER"
    special_group = "projectOwners"
  }

  access {
    role          = "WRITER"
    user_by_email = var.wxcc_sa_email
  }

  access {
    role          = "READER"
    user_by_email = var.vertex_ai_sa_email
  }

  access {
    role          = "READER"
    user_by_email = var.cloud_functions_sa_email
  }

  depends_on = [google_kms_crypto_key_iam_member.bigquery_key_access]
}

## Table: Call Detail Records (CDR)

resource "google_bigquery_table" "call_detail_records" {
  dataset_id = google_bigquery_dataset.wxcc_analytics.dataset_id
  table_id   = "call_detail_records"

  description = "Call Detail Records from WxCC with partitioning and clustering"

  time_partitioning {
    type  = "DAY"
    field = "call_start_time"
    expiration_ms = 31536000000  # 365 days
  }

  clustering = ["queue_id", "agent_id", "call_direction"]

  schema = jsonencode([
    {
      name        = "call_id"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Unique call identifier (UUID)"
    },
    {
      name        = "call_start_time"
      type        = "TIMESTAMP"
      mode        = "REQUIRED"
      description = "Call start timestamp (UTC)"
    },
    {
      name        = "call_end_time"
      type        = "TIMESTAMP"
      mode        = "NULLABLE"
      description = "Call end timestamp (UTC)"
    },
    {
      name        = "call_duration_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Total call duration in seconds"
    },
    {
      name        = "queue_id"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Queue identifier"
    },
    {
      name        = "queue_name"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Queue display name"
    },
    {
      name        = "agent_id"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Agent identifier (null for abandoned calls)"
    },
    {
      name        = "agent_email"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Agent email address"
    },
    {
      name        = "agent_name"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Agent display name"
    },
    {
      name        = "customer_ani"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Customer phone number (hashed SHA-256)"
    },
    {
      name        = "customer_dnis"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Dialed number (company number called)"
    },
    {
      name        = "disposition_code"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Call disposition code (COMPLETED, TRANSFERRED, etc.)"
    },
    {
      name        = "call_direction"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "INBOUND or OUTBOUND"
    },
    {
      name        = "call_result"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "ANSWERED, ABANDONED, REJECTED, ERROR"
    },
    {
      name        = "queue_wait_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Time spent in queue before agent pickup"
    },
    {
      name        = "ring_duration_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Time spent ringing before agent answered"
    },
    {
      name        = "talk_duration_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Active conversation time with agent"
    },
    {
      name        = "hold_duration_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Total time customer was on hold"
    },
    {
      name        = "wrap_duration_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "After-call work time by agent"
    },
    {
      name        = "transfer_count"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Number of times call was transferred"
    },
    {
      name        = "conference_count"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Number of times call was conferenced"
    },
    {
      name        = "recording_uri"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "GCS URI of call recording (gs://bucket/path)"
    },
    {
      name        = "recording_duration_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Duration of recording"
    },
    {
      name        = "ivr_path"
      type        = "STRING"
      mode        = "REPEATED"
      description = "IVR menu path taken (array of menu IDs)"
    },
    {
      name        = "custom_attributes"
      type        = "JSON"
      mode        = "NULLABLE"
      description = "Custom call attributes from WxCC (JSON)"
    },
    {
      name        = "created_at"
      type        = "TIMESTAMP"
      mode        = "REQUIRED"
      description = "Record creation timestamp in BigQuery"
    },
    {
      name        = "updated_at"
      type        = "TIMESTAMP"
      mode        = "NULLABLE"
      description = "Record last update timestamp"
    }
  ])

  depends_on = [google_bigquery_dataset.wxcc_analytics]
}

## Table: Call Transcripts

resource "google_bigquery_table" "call_transcripts" {
  dataset_id = google_bigquery_dataset.wxcc_analytics.dataset_id
  table_id   = "call_transcripts"

  description = "Call transcripts with PII redacted by Cloud DLP"

  time_partitioning {
    type  = "DAY"
    field = "call_start_time"
    expiration_ms = 31536000000
  }

  clustering = ["call_id"]

  schema = jsonencode([
    {
      name        = "transcript_id"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Unique transcript identifier (UUID)"
    },
    {
      name        = "call_id"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Foreign key to call_detail_records"
    },
    {
      name        = "call_start_time"
      type        = "TIMESTAMP"
      mode        = "REQUIRED"
      description = "Call start timestamp (for partitioning)"
    },
    {
      name        = "transcript_redacted"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Full transcript with PII redacted by DLP"
    },
    {
      name        = "transcript_raw_gcs_uri"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "GCS URI of raw transcript (restricted access)"
    },
    {
      name        = "language_code"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Detected language (e.g., en-US, hi-IN)"
    },
    {
      name        = "confidence_score"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Overall transcription confidence (0-1)"
    },
    {
      name        = "word_count"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Total word count in transcript"
    },
    {
      name        = "speaker_labels"
      type        = "BOOLEAN"
      mode        = "NULLABLE"
      description = "Whether speaker diarization was performed"
    },
    {
      name        = "segments"
      type        = "RECORD"
      mode        = "REPEATED"
      description = "Transcript segments with timestamps"
      fields = [
        {
          name        = "start_time_offset"
          type        = "FLOAT"
          mode        = "REQUIRED"
          description = "Segment start time in seconds"
        },
        {
          name        = "end_time_offset"
          type        = "FLOAT"
          mode        = "REQUIRED"
          description = "Segment end time in seconds"
        },
        {
          name        = "speaker_tag"
          type        = "INTEGER"
          mode        = "NULLABLE"
          description = "Speaker identifier (1=agent, 2=customer)"
        },
        {
          name        = "text"
          type        = "STRING"
          mode        = "REQUIRED"
          description = "Segment text (redacted)"
        },
        {
          name        = "confidence"
          type        = "FLOAT"
          mode        = "NULLABLE"
          description = "Segment confidence score"
        }
      ]
    },
    {
      name        = "pii_found"
      type        = "BOOLEAN"
      mode        = "REQUIRED"
      description = "Whether PII was detected and redacted"
    },
    {
      name        = "pii_types"
      type        = "STRING"
      mode        = "REPEATED"
      description = "Types of PII found (CREDIT_CARD, SSN, NAME, etc.)"
    },
    {
      name        = "pii_count"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Total number of PII instances redacted"
    },
    {
      name        = "processing_time_ms"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Time taken to process transcript (milliseconds)"
    },
    {
      name        = "created_at"
      type        = "TIMESTAMP"
      mode        = "REQUIRED"
      description = "Record creation timestamp"
    }
  ])

  depends_on = [google_bigquery_dataset.wxcc_analytics]
}

## Table: Sentiment Analysis Results

resource "google_bigquery_table" "sentiment_analysis" {
  dataset_id = google_bigquery_dataset.wxcc_analytics.dataset_id
  table_id   = "sentiment_analysis"

  description = "Sentiment analysis results from CCAI Insights"

  time_partitioning {
    type  = "DAY"
    field = "call_start_time"
    expiration_ms = 31536000000
  }

  clustering = ["call_id", "overall_sentiment"]

  schema = jsonencode([
    {
      name        = "analysis_id"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Unique analysis identifier"
    },
    {
      name        = "call_id"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Foreign key to call_detail_records"
    },
    {
      name        = "transcript_id"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Foreign key to call_transcripts"
    },
    {
      name        = "call_start_time"
      type        = "TIMESTAMP"
      mode        = "REQUIRED"
      description = "Call start timestamp (for partitioning)"
    },
    {
      name        = "overall_sentiment"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "POSITIVE, NEGATIVE, NEUTRAL, MIXED"
    },
    {
      name        = "sentiment_score"
      type        = "FLOAT"
      mode        = "REQUIRED"
      description = "Overall sentiment score (-1 to +1)"
    },
    {
      name        = "sentiment_magnitude"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Sentiment strength (0 to infinity)"
    },
    {
      name        = "customer_sentiment"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Customer-specific sentiment (if diarization available)"
    },
    {
      name        = "customer_sentiment_score"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Customer sentiment score (-1 to +1)"
    },
    {
      name        = "agent_sentiment"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Agent-specific sentiment"
    },
    {
      name        = "agent_sentiment_score"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Agent sentiment score (-1 to +1)"
    },
    {
      name        = "sentiment_timeline"
      type        = "RECORD"
      mode        = "REPEATED"
      description = "Sentiment evolution over call duration"
      fields = [
        {
          name        = "timestamp_offset_seconds"
          type        = "INTEGER"
          mode        = "REQUIRED"
          description = "Time offset from call start"
        },
        {
          name        = "sentiment"
          type        = "STRING"
          mode        = "REQUIRED"
          description = "Sentiment at this time"
        },
        {
          name        = "score"
          type        = "FLOAT"
          mode        = "REQUIRED"
          description = "Sentiment score at this time"
        },
        {
          name        = "trigger_phrase"
          type        = "STRING"
          mode        = "NULLABLE"
          description = "Phrase that triggered sentiment change"
        }
      ]
    },
    {
      name        = "key_phrases"
      type        = "STRING"
      mode        = "REPEATED"
      description = "Important phrases detected in conversation"
    },
    {
      name        = "entities_detected"
      type        = "RECORD"
      mode        = "REPEATED"
      description = "Entities extracted from conversation"
      fields = [
        {
          name        = "entity_type"
          type        = "STRING"
          mode        = "REQUIRED"
          description = "Type (PERSON, LOCATION, PRODUCT, etc.)"
        },
        {
          name        = "entity_value"
          type        = "STRING"
          mode        = "REQUIRED"
          description = "Entity value"
        },
        {
          name        = "salience"
          type        = "FLOAT"
          mode        = "NULLABLE"
          description = "Importance score (0-1)"
        }
      ]
    },
    {
      name        = "created_at"
      type        = "TIMESTAMP"
      mode        = "REQUIRED"
      description = "Analysis timestamp"
    }
  ])

  depends_on = [google_bigquery_dataset.wxcc_analytics]
}

## Table: Agent Performance Metrics

resource "google_bigquery_table" "agent_metrics" {
  dataset_id = google_bigquery_dataset.wxcc_analytics.dataset_id
  table_id   = "agent_metrics"

  description = "Daily aggregated agent performance metrics"

  time_partitioning {
    type  = "DAY"
    field = "metric_date"
    expiration_ms = 94608000000  # 3 years
  }

  clustering = ["agent_id", "team_id"]

  schema = jsonencode([
    {
      name        = "metric_id"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Unique metric record identifier"
    },
    {
      name        = "agent_id"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Agent identifier"
    },
    {
      name        = "agent_email"
      type        = "STRING"
      mode        = "REQUIRED"
      description = "Agent email address"
    },
    {
      name        = "agent_name"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Agent display name"
    },
    {
      name        = "team_id"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Team identifier"
    },
    {
      name        = "team_name"
      type        = "STRING"
      mode        = "NULLABLE"
      description = "Team display name"
    },
    {
      name        = "metric_date"
      type        = "DATE"
      mode        = "REQUIRED"
      description = "Date for which metrics are calculated"
    },
    {
      name        = "total_calls_handled"
      type        = "INTEGER"
      mode        = "REQUIRED"
      description = "Total calls handled on this date"
    },
    {
      name        = "calls_answered"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Calls answered (not transferred immediately)"
    },
    {
      name        = "calls_transferred"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Calls transferred to another agent"
    },
    {
      name        = "avg_handle_time_seconds"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Average handle time (talk + hold + wrap)"
    },
    {
      name        = "avg_talk_time_seconds"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Average talk time with customer"
    },
    {
      name        = "avg_hold_time_seconds"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Average hold time per call"
    },
    {
      name        = "avg_wrap_time_seconds"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Average after-call work time"
    },
    {
      name        = "total_login_time_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Total time agent was logged in"
    },
    {
      name        = "total_available_time_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Total time agent was in Available state"
    },
    {
      name        = "occupancy_rate"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Occupancy rate (handle time / login time)"
    },
    {
      name        = "utilization_rate"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Utilization rate (talk time / available time)"
    },
    {
      name        = "avg_sentiment_score"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Average sentiment score for calls handled"
    },
    {
      name        = "positive_sentiment_percentage"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Percentage of calls with positive sentiment"
    },
    {
      name        = "negative_sentiment_percentage"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Percentage of calls with negative sentiment"
    },
    {
      name        = "transfer_rate"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Percentage of calls transferred"
    },
    {
      name        = "avg_customer_satisfaction"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Average CSAT score (from post-call surveys)"
    },
    {
      name        = "first_call_resolution_rate"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "FCR rate (percentage, estimated from no follow-up)"
    },
    {
      name        = "agent_assist_accepted_count"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Number of Agent Assist suggestions accepted"
    },
    {
      name        = "agent_assist_shown_count"
      type        = "INTEGER"
      mode        = "NULLABLE"
      description = "Number of Agent Assist suggestions shown"
    },
    {
      name        = "agent_assist_acceptance_rate"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "Agent Assist acceptance rate"
    },
    {
      name        = "created_at"
      type        = "TIMESTAMP"
      mode        = "REQUIRED"
      description = "Record creation timestamp"
    },
    {
      name        = "updated_at"
      type        = "TIMESTAMP"
      mode        = "NULLABLE"
      description = "Record last update timestamp"
    }
  ])

  depends_on = [google_bigquery_dataset.wxcc_analytics]
}

## Table: Queue Performance Metrics

resource "google_bigquery_table" "queue_metrics" {
  dataset_id = google_bigquery_dataset.wxcc_analytics.dataset_id
  table_id   = "queue_metrics"

  description = "Queue-level performance metrics"

  time_partitioning {
    type  = "DAY"
    field = "metric_date"
  }

  clustering = ["queue_id"]

  schema = jsonencode([
    {
      name        = "metric_id"
      type        = "STRING"
      mode        = "REQUIRED"
    },
    {
      name        = "queue_id"
      type        = "STRING"
      mode        = "REQUIRED"
    },
    {
      name        = "queue_name"
      type        = "STRING"
      mode        = "REQUIRED"
    },
    {
      name        = "metric_date"
      type        = "DATE"
      mode        = "REQUIRED"
    },
    {
      name        = "total_calls_offered"
      type        = "INTEGER"
      mode        = "REQUIRED"
    },
    {
      name        = "calls_answered"
      type        = "INTEGER"
      mode        = "NULLABLE"
    },
    {
      name        = "calls_abandoned"
      type        = "INTEGER"
      mode        = "NULLABLE"
    },
    {
      name        = "abandonment_rate"
      type        = "FLOAT"
      mode        = "NULLABLE"
    },
    {
      name        = "avg_wait_time_seconds"
      type        = "FLOAT"
      mode        = "NULLABLE"
    },
    {
      name        = "max_wait_time_seconds"
      type        = "INTEGER"
      mode        = "NULLABLE"
    },
    {
      name        = "service_level_15s"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "% of calls answered within 15 seconds"
    },
    {
      name        = "service_level_30s"
      type        = "FLOAT"
      mode        = "NULLABLE"
      description = "% of calls answered within 30 seconds"
    },
    {
      name        = "avg_handle_time_seconds"
      type        = "FLOAT"
      mode        = "NULLABLE"
    },
    {
      name        = "created_at"
      type        = "TIMESTAMP"
      mode        = "REQUIRED"
    }
  ])

  depends_on = [google_bigquery_dataset.wxcc_analytics]
}

[CONTINUED... This is getting very long. Would you like me to: 1. Continue with the complete 4-week guide in this document (will be ~15,000+ lines) 2. Create the guide in multiple manageable parts (Part 1: Weeks 1-2, Part 2: Weeks 3-4) 3. Focus on just the most critical sections and provide the rest as appendices

Please advise how you'd like me to proceed with the remaining content covering: - Cloud Storage setup - Pub/Sub configuration - VPC Service Controls - Cloud DLP - Week 3: CCAI Platform, Dialogflow CX, Custom Models - Week 4: Cloud Functions, WxCC Integration, Testing - Complete validation procedures - Troubleshooting guide] File: terraform/03-bigquery/variables.tf

variable "project_id" {
  type = string
}

variable "wxcc_sa_email" {
  type = string
}

variable "vertex_ai_sa_email" {
  type = string
}

variable "cloud_functions_sa_email" {
  type = string
}

File: terraform/03-bigquery/outputs.tf

output "dataset_id" {
  value = google_bigquery_dataset.wxcc_analytics.dataset_id
}

output "dataset_location" {
  value = google_bigquery_dataset.wxcc_analytics.location
}

output "cdr_table_id" {
  value = google_bigquery_table.call_detail_records.table_id
}

output "transcripts_table_id" {
  value = google_bigquery_table.call_transcripts.table_id
}

Deployment

cd ~/gcp-wxcc-deployment/terraform/03-bigquery

## Get service account emails from previous module

export WXCC_SA=$(cd ../02-iam && terraform output -raw wxcc_integration_sa_email)
export VERTEX_AI_SA=$(cd ../02-iam && terraform output -raw vertex_ai_sa_email)
export CF_SA=$(cd ../02-iam && terraform output -raw cloud_functions_sa_email)

## Create terraform.tfvars

cat > terraform.tfvars << EOF
project_id = "$PROJECT_ID"
wxcc_sa_email = "$WXCC_SA"
vertex_ai_sa_email = "$VERTEX_AI_SA"
cloud_functions_sa_email = "$CF_SA"
EOF

terraform init
terraform plan -out=tfplan
terraform apply tfplan

## Verify dataset and tables

bq ls --project_id=$PROJECT_ID
bq ls --project_id=$PROJECT_ID wxcc_analytics

## View table schemas

bq show --schema --format=prettyjson \
  $PROJECT_ID:wxcc_analytics.call_detail_records | head -50

Week 1 Day 5: Cloud Storage

Terraform Module: Storage Buckets

File: terraform/04-storage/main.tf

terraform {
  required_version = ">= 1.5.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 5.0"
    }
  }
}

provider "google" {
  project = var.project_id
}

## KMS Key Ring for Storage

resource "google_kms_key_ring" "storage_keyring" {
  name     = "storage-keyring"
  location = "us"
}

## KMS Crypto Key for Storage

resource "google_kms_crypto_key" "storage_key" {
  name            = "storage-encryption-key"
  key_ring        = google_kms_key_ring.storage_keyring.id
  rotation_period = "7776000s"  # 90 days

  purpose = "ENCRYPT_DECRYPT"

  lifecycle {
    prevent_destroy = true
  }
}

## Grant Cloud Storage service account access to KMS key

data "google_storage_project_service_account" "gcs_account" {
  project = var.project_id
}

resource "google_kms_crypto_key_iam_member" "storage_key_access" {
  crypto_key_id = google_kms_crypto_key.storage_key.id
  role          = "roles/cloudkms.cryptoKeyEncrypterDecrypter"
  member        = "serviceAccount:${data.google_storage_project_service_account.gcs_account.email_address}"
}

## Bucket: Call Recordings (Restricted Access)

resource "google_storage_bucket" "call_recordings" {
  name          = "${var.project_id}-call-recordings"
  location      = "US"
  storage_class = "STANDARD"

  uniform_bucket_level_access = true

  versioning {
    enabled = true
  }

  encryption {
    default_kms_key_name = google_kms_crypto_key.storage_key.id
  }

  lifecycle_rule {
    condition {
      age = 90
    }
    action {
      type = "Delete"
    }
  }

  lifecycle_rule {
    condition {
      age = 30
    }
    action {
      type          = "SetStorageClass"
      storage_class = "NEARLINE"
    }
  }

  labels = {
    environment = "production"
    data_type   = "recordings"
    sensitivity = "high"
  }

  depends_on = [google_kms_crypto_key_iam_member.storage_key_access]
}

## Bucket: Processed Data

resource "google_storage_bucket" "processed_data" {
  name          = "${var.project_id}-processed-data"
  location      = "US"
  storage_class = "STANDARD"

  uniform_bucket_level_access = true

  encryption {
    default_kms_key_name = google_kms_crypto_key.storage_key.id
  }

  lifecycle_rule {
    condition {
      age = 180
    }
    action {
      type = "Delete"
    }
  }

  labels = {
    environment = "production"
    data_type   = "processed"
  }

  depends_on = [google_kms_crypto_key_iam_member.storage_key_access]
}

## Bucket: Vertex AI Models

resource "google_storage_bucket" "vertex_models" {
  name          = "${var.project_id}-vertex-models"
  location      = "US"
  storage_class = "STANDARD"

  uniform_bucket_level_access = true

  versioning {
    enabled = true
  }

  encryption {
    default_kms_key_name = google_kms_crypto_key.storage_key.id
  }

  labels = {
    environment = "production"
    data_type   = "models"
  }

  depends_on = [google_kms_crypto_key_iam_member.storage_key_access]
}

## IAM Bindings

resource "google_storage_bucket_iam_member" "recordings_wxcc_write" {
  bucket = google_storage_bucket.call_recordings.name
  role   = "roles/storage.objectCreator"
  member = "serviceAccount:${var.wxcc_sa_email}"
}

resource "google_storage_bucket_iam_member" "recordings_function_read" {
  bucket = google_storage_bucket.call_recordings.name
  role   = "roles/storage.objectViewer"
  member = "serviceAccount:${var.cloud_functions_sa_email}"
}

resource "google_storage_bucket_iam_member" "processed_function_write" {
  bucket = google_storage_bucket.processed_data.name
  role   = "roles/storage.objectAdmin"
  member = "serviceAccount:${var.cloud_functions_sa_email}"
}

resource "google_storage_bucket_iam_member" "processed_vertex_read" {
  bucket = google_storage_bucket.processed_data.name
  role   = "roles/storage.objectViewer"
  member = "serviceAccount:${var.vertex_ai_sa_email}"
}

resource "google_storage_bucket_iam_member" "models_vertex_admin" {
  bucket = google_storage_bucket.vertex_models.name
  role   = "roles/storage.objectAdmin"
  member = "serviceAccount:${var.vertex_ai_sa_email}"
}

Deployment

cd ~/gcp-wxcc-deployment/terraform/04-storage
terraform init
terraform plan -out=tfplan
terraform apply tfplan

## Verify buckets

gsutil ls -p $PROJECT_ID

## Test upload

echo "test" > /tmp/test.txt
gsutil cp /tmp/test.txt gs://$PROJECT_ID-processed-data/
gsutil rm gs://$PROJECT_ID-processed-data/test.txt

Week 1 Day 6: Pub/Sub Configuration

File: terraform/05-pubsub/main.tf

terraform {
  required_version = ">= 1.5.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 5.0"
    }
  }
}

provider "google" {
  project = var.project_id
}

## Topic: Real-time Call Events

resource "google_pubsub_topic" "call_events" {
  name = "wxcc-call-events"

  message_retention_duration = "86400s"  # 24 hours

  labels = {
    environment = "production"
    data_source = "wxcc"
  }
}

## Subscription: Call Events Processor

resource "google_pubsub_subscription" "call_events_processor" {
  name  = "call-events-processor"
  topic = google_pubsub_topic.call_events.name

  ack_deadline_seconds       = 600  # 10 minutes
  message_retention_duration = "86400s"
  retain_acked_messages      = false

  expiration_policy {
    ttl = ""  # Never expire
  }

  retry_policy {
    minimum_backoff = "10s"
    maximum_backoff = "600s"
  }

  dead_letter_policy {
    dead_letter_topic     = google_pubsub_topic.call_events_dlq.id
    max_delivery_attempts = 5
  }
}

## Dead Letter Queue

resource "google_pubsub_topic" "call_events_dlq" {
  name = "wxcc-call-events-dlq"

  message_retention_duration = "604800s"  # 7 days
}

resource "google_pubsub_subscription" "call_events_dlq_sub" {
  name                       = "call-events-dlq-subscription"
  topic                      = google_pubsub_topic.call_events_dlq.name
  ack_deadline_seconds       = 60
  message_retention_duration = "604800s"
}

## IAM Bindings

resource "google_pubsub_topic_iam_member" "wxcc_publisher" {
  topic  = google_pubsub_topic.call_events.name
  role   = "roles/pubsub.publisher"
  member = "serviceAccount:${var.wxcc_sa_email}"
}

resource "google_pubsub_subscription_iam_member" "function_subscriber" {
  subscription = google_pubsub_subscription.call_events_processor.name
  role         = "roles/pubsub.subscriber"
  member       = "serviceAccount:${var.cloud_functions_sa_email}"
}

Deployment

cd ~/gcp-wxcc-deployment/terraform/05-pubsub
terraform init
terraform plan -out=tfplan
terraform apply tfplan

## Verify topics and subscriptions

gcloud pubsub topics list --project=$PROJECT_ID
gcloud pubsub subscriptions list --project=$PROJECT_ID

## Test publish/subscribe

gcloud pubsub topics publish wxcc-call-events \
  --message='{"test": "message"}' \
  --project=$PROJECT_ID

gcloud pubsub subscriptions pull call-events-processor \
  --auto-ack --limit=1 --project=$PROJECT_ID

Week 2: Security Layer Deployment