Compute metrics using Agent Assist conversation data

WITH calculation AS
(
  SELECT
    COUNTIF(
      (
        SELECT COUNT(*)
        FROM UNNEST (sen.annotations) AS annotation
        WHERE annotation.type = 'ANNOTATION_TYPE'
      ) > 0
    ) AS numSentencesAtLeastOneAnnotation,
    COUNT(*) AS numSentencesTotal
  FROM BIGQUERY_TABLE,
  UNNEST (sentences) AS sen
)
SELECT
  numSentencesAtLeastOneAnnotation,
  numSentencesTotal,
  numSentencesAtLeastOneAnnotation / numSentencesTotal AS triggerRate
FROM calculation;

WITH calculation AS
(
  SELECT
    COUNTIF(
      (
        SELECT COUNT(*)
        FROM
          UNNEST (sentences) AS sen,
          UNNEST (sen.annotations) AS annotation
        WHERE annotation.type = 'ANNOTATION_TYPE'
      ) > 0
    ) AS numConversationsAtLeastOneAnnotation,
    COUNT(*) AS numConversationsTotal
  FROM BIGQUERY_TABLE
) SELECT
  numConversationsAtLeastOneAnnotation,
  numConversationsTotal,
  numConversationsAtLeastOneAnnotation / numConversationsTotal AS triggerRate
FROM calculation;

WITH calculation AS
(
  SELECT
    COUNTIF(
      (
        SELECT COUNT(*)
        FROM UNNEST (sen.annotations) AS annotation
        WHERE
          annotation.type = 'ANNOTATION_TYPE' AND
          annotation.displayed = TRUE AND
          annotation.clicked = TRUE
      ) > 0
    ) AS numSentencesAtLeastOneClickedAndDisplayedAnnotation,
    COUNTIF(
      (
        SELECT COUNT(*)
        FROM UNNEST (sen.annotations) AS annotation
        WHERE
          annotation.type = 'ANNOTATION_TYPE' AND
          annotation.displayed = TRUE
      ) > 0
    ) AS numSentencesAtLeastOneDisplayedAnnotation,
  FROM BIGQUERY_TABLE,
  UNNEST (sentences) AS sen
)
SELECT
  numSentencesAtLeastOneClickedAndDisplayedAnnotation,
  numSentencesAtLeastOneDisplayedAnnotation,
  numSentencesAtLeastOneClickedAndDisplayedAnnotation / numSentencesAtLeastOneDisplayedAnnotation AS clickThroughRate
FROM calculation;

WITH calculation AS (
  SELECT
    sen.sentence AS agentResponse,
    (
      # Get a list of effective smart replies that map to each agent response.
      SELECT ARRAY_AGG(JSON_QUERY(PARSE_JSON(JSON_VALUE(annotation.annotationRecord)), "$.reply"))
      FROM
        UNNEST (sentences) AS sen2,
        UNNEST (sen2.annotations) AS annotation
      WHERE
        annotation.type='SMART_REPLY' AND
        annotation.clicked=TRUE AND
        annotation.annotationRecord IS NOT NULL AND
        annotation.createTimeNanos >= (
          # Get the timestamp of the agent response that immediately precedes
          # the current agent response. The timestamp of the effective smart
          # reply must be greater than this timestamp.
          SELECT MAX(sen3.createTimeNanos)
          FROM UNNEST(sentences) AS sen3
          WHERE
            sen3.participantRole = 'HUMAN_AGENT' AND
            sen3.createTimeNanos < sen.createTimeNanos
        ) AND
        annotation.createTimeNanos <= sen.createTimeNanos
    ) AS effectiveSmartRepliesPerAgentResponse
  FROM BIGQUERY_TABLE,
  UNNEST (sentences) AS sen
  WHERE sen.participantRole = 'HUMAN_AGENT'
)
SELECT COUNTIF(ARRAY_LENGTH(effectiveSmartRepliesPerAgentResponse) > 0) / COUNT(agentResponse) AS effectiveCTR
FROM calculation;

WITH calculation AS
(
  SELECT
    JSON_VALUE(PARSE_JSON(JSON_VALUE(annotation.annotationRecord), wide_number_mode=>'round'), "$.reply") AS smartReply,
    (
      # Get the agent response that immediately follows the smart reply.
      SELECT sen2.sentence
      FROM UNNEST (sentences) AS sen2
      WHERE sen2.createTimeNanos = (
        SELECT MIN(sen3.createTimeNanos)
        FROM UNNEST (sentences) AS sen3
        WHERE
          sen3.createTimeNanos > sen.createTimeNanos AND
          sen3.participantRole = 'HUMAN_AGENT'
      )
    ) AS agentResponseThatFollows,
  FROM BIGQUERY_TABLE,
  UNNEST (sentences) AS sen,
  UNNEST (sen.annotations) AS annotation
  WHERE
    annotation.type='SMART_REPLY' AND
    annotation.clicked=TRUE AND
    annotation.annotationRecord IS NOT NULL
)
SELECT COUNTIF(REGEXP_CONTAINS(agentResponseThatFollows, smartReply)) / COUNT(smartReply) AS editRate
FROM calculation;

SELECT
IF
  (JSON_VALUE(annotation.annotationRecord) != '', 
   JSON_QUERY(PARSE_JSON(JSON_VALUE(annotation.annotationRecord)), "$.generatorSuggestion.summarySuggestion"),
   NULL) AS generator_summary,
IF
  (JSON_VALUE(annotation.detailedFeedback) != '',
   JSON_QUERY(PARSE_JSON(JSON_VALUE(annotation.detailedFeedback)), "$.summarizationFeedback.summaryText"),
   NULL) AS feedback,
FROM
  BIGQUERY_TABLE AS c,
  UNNEST(c.sentences) AS sentences,
  UNNEST(sentences.annotations) AS annotation
WHERE
  annotation.type = 'GENERATOR_SUGGESTION_RESULT'
  AND JSON_QUERY(PARSE_JSON(JSON_VALUE(annotation.annotationRecord)), "$.generatorSuggestion.summarySuggestion") IS NOT NULL
LIMIT
  10 ;

SELECT
  JSON_VALUE(PARSE_JSON(JSON_VALUE(annotation.annotationRecord)), "$.text") AS suggestedSummary,
IF
  (JSON_VALUE(annotation.detailedFeedback) != '', JSON_QUERY(PARSE_JSON(JSON_VALUE(annotation.detailedFeedback)), "$.summarizationFeedback.summaryText"), NULL) AS feedback,
FROM
  BIGQUERY_TABLE,
  UNNEST (sentences) AS sen,
  UNNEST (sen.annotations) as annotation
WHERE
  type='CONVERSATION_SUMMARIZATION_SUGGESTION';

WITH
  summaryAndEdits AS (
  SELECT
    JSON_VALUE(PARSE_JSON(JSON_VALUE(annotation.annotationRecord)), "$.text") AS suggestedSummary,
  IF
    (JSON_VALUE(annotation.detailedFeedback) != '', JSON_QUERY(PARSE_JSON(JSON_VALUE(annotation.detailedFeedback)), "$.summarizationFeedback.summaryText"), NULL) AS editedSummary,
  FROM
    BIGQUERY_TABLE,
    UNNEST (sentences) AS sen,
    UNNEST (sen.annotations) AS annotation
  WHERE
    type='CONVERSATION_SUMMARIZATION_SUGGESTION' )
SELECT
  countif (suggestedSummary IS NOT NULL) AS totalSummaries,
  countif (editedSummary IS NOT NULL) AS editedSummaries,
  countif (editedSummary IS NOT NULL) / countif (suggestedSummary IS NOT NULL) AS editRate
FROM
  summaryAndEdits;