Sample Queries
The following examples illustrate BigQuery queries for common analytics, assuming logs have been copied to a table called glean_customer_event_table.
User Activity & Engagement
Output: Count of unique users per day who performed qualifying actions
Grain: Daily aggregation
Use Case: Track overall platform engagement and user activity trends
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY);
DECLARE end_date DATE DEFAULT CURRENT_DATE();
WITH
glean_customer_event AS (
SELECT
resource,
DATE(DATE_TRUNC(timestamp, DAY)) AS datepartition,
jsonPayload,
timestamp,
FROM
-- replace <glean_customer_event_table> with your company's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from start_date - 28 days to end_date (to support MAU calculation)
DATE(`timestamp`) >= DATE_SUB(start_date, INTERVAL 28 DAY) AND DATE(`timestamp`) <= end_date
)
,
id_to_alias AS (
SELECT DISTINCT
datepartition,
project_id,
aliasid,
userid AS canonicalid
FROM (
SELECT
datepartition,
resource.labels.project_id,
jsonPayload.productsnapshot.user.id AS userid,
jsonPayload.productsnapshot.user.aliasids
FROM
glean_customer_event
WHERE
jsonPayload.type = 'PRODUCT_SNAPSHOT'
AND jsonPayload.productsnapshot.type = 'USER'
), UNNEST(aliasids) AS aliasid
),
chat_usage as (
SELECT
COALESCE(wf.datepartition, chat.datepartition) AS datepartition,
COALESCE(wf.project_id, chat.project_id) AS project_id,
COALESCE(wf.userid, chat.userid) AS userid,
COUNT(DISTINCT wf.run_id) + COUNT(DISTINCT CASE WHEN wf.run_id IS NULL THEN chat.qtt END) AS num_chat_queries
FROM (
SELECT
jsonPayload.workflow.runid AS run_id,
jsonPayload.user.userid,
resource.labels.project_id,
MIN(datepartition) AS datepartition
FROM glean_customer_event
WHERE jsonPayload.workflow.workflowid IN (
'DOC_CONTEXT_READER',
'ORIGINAL_MESSAGE_SEARCH',
'DIRECT_LLM_RESPONSE',
'REACT_TOOLS_2_HOPS',
'DEFAULT_CHAT',
'WORLD_MODE_V2',
'DEEP_RESEARCH_PYAGENT',
'DEEP_RESEARCH_PREVIEW'
)
AND jsonPayload.workflow.initiator = 'USER'
GROUP BY 1, 2, 3
UNION ALL
SELECT
DISTINCT jsonPayload.workflowrun.runid AS run_id,
jsonPayload.user.userid,
resource.labels.project_id,
datepartition
FROM glean_customer_event, UNNEST(jsonPayload.workflowrun.workflowexecutions) AS workflow_execution
WHERE jsonPayload.type = 'WORKFLOW_RUN'
AND jsonPayload.workflowrun.initiator = 'USER'
AND workflow_execution.workflowid IN (
'DOC_CONTEXT_READER',
'ORIGINAL_MESSAGE_SEARCH',
'DIRECT_LLM_RESPONSE',
'REACT_TOOLS_2_HOPS',
'DEFAULT_CHAT',
'WORLD_MODE_V2',
'DEEP_RESEARCH_PYAGENT',
'DEEP_RESEARCH_PREVIEW'
)
) wf
FULL OUTER JOIN (
SELECT
datepartition,
resource.labels.project_id,
jsonPayload.user.userid,
jsonPayload.chat.qtt AS qtt,
jsonPayload.chat.workflowrunid AS workflowrunid
FROM glean_customer_event
WHERE jsonPayload.type = 'CHAT'
AND jsonPayload.chat.initiator = 'USER'
) chat
ON wf.run_id = chat.workflowrunid
GROUP BY 1, 2, 3
),
other_feature_usage AS (
SELECT
gce.datepartition,
gce.resource.labels.project_id,
gce.jsonPayload.user.userid,
COUNT(
DISTINCT CASE WHEN (
gce.jsonPayload.type = 'SEARCH'
AND (
gce.jsonPayload.search.initiator IN ('USER', 'PAGE_LOAD', 'ONBOARDING')
AND LOWER(gce.jsonPayload.Search.modality) NOT IN
('gleanbot', 'slackbot_command', 'slackbot_leaderboard', 'slackbot_retry', '')
)
) THEN gce.jsonPayload.search.trackingtoken END
) + COUNT(
CASE WHEN (
gce.jsonPayload.type = 'SEARCH'
AND gce.jsonPayload.search.isrestclientapi
) THEN 1 END
) AS num_searches,
COUNT(
DISTINCT CASE WHEN (
-- Depending on category, opening links/documents counts towards DAU
gce.jsonPayload.clientevent.event IN ('OpenDocument', 'OpenLink')
AND gce.jsonPayload.clientevent.category IN (
-- Autocomplete and Search interactions count towards DAU
'Search Result'
, 'Autocomplete'
-- New tab page(NTP) and Homepage interactions count towards DAU
, 'Feed'
, 'Calendar'
, 'New Tab Page'
-- Directory tab interactions count towards DAU
, 'Org Chart'
, 'Person Card'
, 'Teams'
, 'Profile'
, 'People Celebrations'
, 'Person Attribution'
-- User Generated Content(UGC) interactions count towards DAU
, 'Announcements'
, 'Answers'
, 'Collections'
, 'Featured Question and Answer'
, 'Generated Question and Answer'
, 'Pins'
-- Golinks interactions counts towards DAU
, 'Shortcuts'
-- Admin and Setup page interactions count towards DAU
, 'Verification'
, 'Datasource Auth'
, 'Insights'
-- Feature related interactions count towards DAU
, 'Chat'
, 'Result Preview'
, 'App Card'
, 'Customer Card'
, 'Search'
-- Other tangible user interactions that count towards DAU
, 'Feedback'
)
)
OR (
-- Depending on category, certain feature related clicks count towards DAU
gce.jsonPayload.clientevent.event IN ('Click')
AND gce.jsonPayload.clientevent.category IN (
-- Autocomplete and Search interactions count towards DAU
'Autocomplete'
, 'Search Result'
, 'Datasource Filter'
, 'Facets'
-- New tab page(NTP) and Homepage interactions count towards DAU
, 'Feed'
, 'Calendar'
-- Directory tab interactions count towards DAU
, 'Org Chart'
, 'Person Card'
, 'Teams'
, 'Profile'
, 'People Celebrations'
, 'Person Attribution'
-- Sidebar interactions count towards DAU
, 'Sidebar Tabs'
-- User Generated Content(UGC) interactions count towards DAU
, 'Announcements'
, 'Answers'
, 'Collections'
-- Golinks interactions counts towards DAU
, 'Shortcuts'
-- Admin and Setup page interactions count towards DAU
, 'Datasource Auth'
, 'User Menu'
, 'Admin Console'
-- Other tangible user interactions that count towards DAU
, 'Feedback'
)
)
-- CRUD operations on User Generated Content(UGC) always count always count towards DAU
OR (
gce.jsonPayload.clientevent.event IN ('Add', 'Create', 'Delete')
AND gce.jsonPayload.clientevent.category IN ('Announcements', 'Answers', 'Collections')
)
OR (
gce.jsonPayload.clientevent.event IN ('View')
AND gce.jsonPayload.clientevent.category IN (
-- User Generated Content(UGC) interactions count towards DAU
'Announcements'
,'Answers'
,'Collections'
-- Directory tab interactions count towards DAU
,'Person Card'
,'Team Card'
,'Org Chart'
)
AND gce.jsonPayload.clientevent.pagepath NOT IN ('/', '/ntp', '/search')
)
/* Summarization expansion */
OR (gce.jsonPayload.clientevent.event = 'Expand' AND gce.jsonPayload.clientevent.category = 'Summary' AND jsonPayload.clientevent.uielement = 'summarize-card')
/* Counts Start events (user chat message in Chat tab) and Expand events (prompt expansion in Discover tab) from Sidebar V2 */
OR (gce.jsonPayload.clientevent.event in ('Start', 'Expand') AND gce.jsonPayload.clientevent.category = 'Sidebar V2')
/* Counts Start events (user query or preset click) from Inline Menu */
OR (gce.jsonPayload.clientevent.event = 'Start' AND gce.jsonPayload.clientevent.category = 'Inline Menu')
/* Counts visits to past chats via GleanChat Conversation History */
OR (gce.jsonPayload.clientevent.event = 'Click' AND gce.jsonPayload.clientevent.category = 'Chat' AND jsonPayload.clientevent.uielement = 'chats-menu')
THEN gce.jsonPayload.clientevent.SessionTrackingToken END
) AS num_client_active_sessions,
COUNT(
CASE WHEN (
gce.jsonPayload.Type IN ('SEARCH_CLICK','CHAT_FEEDBACK','SEARCH_FEEDBACK')
OR gce.jsonPayload.Type IN ('SHORTCUT')
AND gce.jsonPayload.shortcut.event IN ('REDIRECT', 'CREATE', 'DELETE', 'UPDATE')
) THEN 1 END
) >= 1 AS is_active_other
FROM
glean_customer_event gce
WHERE
gce.jsonPayload.type NOT IN ('CHAT','WORKFLOW')
GROUP BY
1, 2, 3
),
slackbot_usage as (
WITH event_traces AS (
SELECT
datepartition,
resource.labels.project_id,
jsonPayload.user.userid,
jsonPayload.gleanbotactivity.eventtrackingtoken,
jsonPayload.gleanbotactivity.responseevents,
jsonPayload.gleanbotactivity.eventtype,
jsonPayload.gleanbotactivity.workflowexecutionpoints,
jsonPayload.gleanbotactivity.stt
FROM
glean_customer_event
WHERE
jsonPayload.type = 'GLEAN_BOT_ACTIVITY'
AND LOWER(jsonPayload.gleanbotactivity.source) = 'slack'
),
slack_reactive_bot_activity AS (
SELECT
datepartition,
project_id,
userid,
COUNT(DISTINCT CASE
WHEN response_downvoted THEN NULL
WHEN bot_responded THEN eventtrackingtoken
WHEN response_shared THEN eventtrackingtoken
ELSE NULL END) AS num_useful_responses,
COUNT(DISTINCT CASE
WHEN voted_not_helpful THEN eventtrackingtoken
ELSE NULL END) AS num_slack_reactive_bot_downvotes,
COUNT(DISTINCT CASE
WHEN voted_helpful THEN eventtrackingtoken
ELSE NULL END) AS num_slack_reactive_bot_upvotes,
COUNT(DISTINCT CASE
WHEN bot_responded THEN eventtrackingtoken
ELSE NULL END) AS num_slack_reactive_bot_responses
FROM (
SELECT
message_data.datepartition,
message_data.project_id,
message_data.userid,
message_data.eventtrackingtoken,
'REACTIVE_ACT_MESSAGE' IN UNNEST(message_data.responseevents) -- Messages that got a successful response from the bot should be counted
OR 'REACTIVE_GENERATION_RESPONSE_MODAL' IN UNNEST(message_data.responseevents) -- Gleanbot added value to users other than the question asker by generating responses for them
OR 'COMMAND_SEARCH_RESULTS_MESSAGE' IN UNNEST(message_data.responseevents) -- Messages that were triggered through a /glean command and got results as response
OR 'COMMAND_SEARCH_UPDATE_MODAL_WITH_RESULTS' IN UNNEST(message_data.responseevents) -- Messages that were triggered through a /glean command and got results as response on retry
OR 'COMMAND_WORKFLOW_SUCCESS' IN UNNEST(message_data.responseevents) -- Response was successfully generated after user queried through glean console
OR 'DISCUSSION_SUMMARY_SENT' IN UNNEST(message_data.responseevents) -- [Slack Compete] Response to summarize a thread discussion was successfully sent through glean DM
OR 'SINGLE_CHANNEL_SUMMARY_SENT' IN UNNEST(message_data.responseevents) -- [Slack Compete] Response to summarize a channel discussion was successfully sent through glean DM
OR 'DOC_URL_SUMMARY_SENT' IN UNNEST(message_data.responseevents) -- [Slack Compete] Response to summarize a uploaded doc discussion was successfully sent through glean DM
AS bot_responded,
interaction_data.eventtype = 'SHARE_CLICK' AS response_shared, -- Response was shared by the user
'SUBMIT_FEEDBACK_WORKFLOW_DOWNVOTE' IN UNNEST(interaction_data.workflowexecutionpoints) AS response_downvoted, -- Response was dismissed by the user
'SHOW_SOURCES_CLICK_SUCCESS' IN UNNEST(interaction_data.workflowexecutionpoints) AS citations_shown, -- Response was checked for its citation source
'SHARE_HELPFUL' IN UNNEST(interaction_data.responseevents) AS voted_helpful, -- Response was voted as helpful
'SHARE_NOT_HELPFUL' IN UNNEST(interaction_data.responseevents) AS voted_not_helpful -- Response was voted as not helpful
FROM (
SELECT
*
FROM
event_traces
WHERE
-- Identify the set of valid slack reactive bot workflow starting points that could potentitally contribute to active users definition
(
'REACTIVE_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where reactive workflow started successfully
OR 'REACTIVE_CHAT_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where reactive workflow for assistant type started
OR 'REACTIVE_SEARCH_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where reactive workflow for search type started
OR 'COMMAND_SEARCH_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where /glean command was used to initiate a reactive search
OR 'COMMAND_DISCUSSION_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where glean console was used to summarize a discussion/query
OR 'COMMAND_CHANNEL_SUMMARIZE_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where glean console was used to summarize a channel
OR 'COMMAND_DOC_SUMMARIZE_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where glean console was used to summarize an attached/shared doc
OR eventtype IN (
'TAGGED_MESSAGE' -- Identify messages where @glean was mentioned
, 'GENERATE_ANSWER' -- Identify messages where someone clicked on generate answer button
, 'COMMAND' -- Identify messages where /glean was mentioned
, 'DM_TO_GLEANBOT_MESSAGE' -- [Slack Compete] Identify messages where user sent DM to glean bot with a query
)
)
AND NOT 'REACTIVE_DISABLED_FAILURE' IN UNNEST(workflowexecutionpoints) -- Remove the events where reactive workflow cannont be triggered. Eg: Invalid Channel
AND NOT 'REACTIVE_DISABLED_FAILURE_FOR_USER' IN UNNEST(workflowexecutionpoints) -- Remove the events where reactive workflow cannont be triggered for the user Eg: Insufficient permissions
AND NOT 'COMMAND_BAD_REQUEST' IN UNNEST(workflowexecutionpoints) -- Remove the event where /glean command was triggered in an invalid channel/by invalid user
AND NOT 'COMMAND_SEARCH_RESULTS_MODAL_UPDATE_FAILURE' IN UNNEST(workflowexecutionpoints) -- Remove cases where /glean command failed to load results
AND NOT 'COMMAND_SEARCH_NO_RESULTS_MODAL_UPDATE_FAILURE' IN UNNEST(workflowexecutionpoints) -- Remove cases where /glean command failed to update and dispaly results
) AS message_data
LEFT JOIN (
SELECT
*
FROM
event_traces
WHERE eventtype IN (
'SHOW_SOURCES' -- All events where a user clicked on show sources modal
, 'SHARE_HELPFULNESS' -- All events for which helpful/not helpful buttons click was initiated
, 'SHARE_CLICK' -- All events for which sharing workflow was started
, 'SUBMIT_FEEDBACK' -- All events for which manual feedback workflow was started
)
) AS interaction_data
ON
message_data.project_id = interaction_data.project_id
AND message_data.eventtrackingtoken = interaction_data.stt
)
GROUP BY
datepartition,
project_id,
userid
),
slack_proactive_bot_activity AS (
SELECT
datepartition,
project_id,
userid,
COUNT(DISTINCT CASE
WHEN response_downvoted THEN NULL
WHEN voted_helpful THEN eventtrackingtoken
WHEN citations_shown THEN eventtrackingtoken
WHEN response_shared THEN eventtrackingtoken
WHEN bot_responded_on_users_request AND NOT voted_not_helpful THEN eventtrackingtoken
ELSE NULL END) AS num_useful_responses,
COUNT(DISTINCT CASE
WHEN response_downvoted THEN NULL
WHEN voted_helpful THEN eventtrackingtoken
WHEN citations_shown THEN eventtrackingtoken
WHEN response_shared THEN eventtrackingtoken
WHEN bot_responded_on_users_request AND NOT voted_not_helpful AND NOT digest_consumed THEN eventtrackingtoken
ELSE NULL END) AS num_useful_responses_without_digest,
COUNT(DISTINCT CASE
WHEN voted_not_helpful THEN eventtrackingtoken
ELSE NULL END) AS num_slack_proactive_bot_downvotes,
COUNT(DISTINCT CASE
WHEN voted_helpful THEN eventtrackingtoken
ELSE NULL END) AS num_slack_proactive_bot_upvotes,
COUNT(DISTINCT CASE
WHEN bot_responded THEN eventtrackingtoken
ELSE NULL END) AS num_slack_proactive_bot_responses
FROM (
SELECT
message_data.datepartition,
message_data.project_id,
message_data.userid,
message_data.eventtrackingtoken,
message_data.eventtype IN ('NON_TAGGED_MESSAGE', 'PROACTIVE_DISCUSSION_SUMMARIZER') AS workflow_started,
'DISCUSSION_SUMMARY_THREAD_SUMMARY_SENT' IN UNNEST(message_data.responseevents)
OR 'DISCUSSION_SUMMARY_DM_RESPONSE_SENT' IN UNNEST(message_data.responseevents)
OR 'PROACTIVE_MESSAGE' IN UNNEST(message_data.responseevents)
AS bot_responded,
'DISCUSSION_SUMMARY_THREAD_SUMMARY_SENT' IN UNNEST(message_data.responseevents)
OR 'DISCUSSION_SUMMARY_DM_RESPONSE_SENT' IN UNNEST(message_data.responseevents)
OR 'VIEW_DIGEST' IN UNNEST(message_data.responseevents)
AS bot_responded_on_users_request,
interaction_data.eventtype = 'SHARE_CLICK' AS response_shared, -- Response was shared by the user
'SUBMIT_FEEDBACK_WORKFLOW_DOWNVOTE' IN UNNEST(interaction_data.workflowexecutionpoints)
OR interaction_data.eventtype = 'DISMISS_SUGGESTION'
AS response_downvoted, -- Response was dismissed by the user
interaction_data.eventtype = 'SHOW_SOURCES' AS citations_shown, -- Response was checked for its citation source
'SHARE_HELPFUL' IN UNNEST(interaction_data.responseevents) AS voted_helpful, -- Response was voted as helpful
'SHARE_NOT_HELPFUL' IN UNNEST(interaction_data.responseevents) AS voted_not_helpful, -- Response was voted as not helpful
'DAILY_DIGEST_REMINDER_SENT' IN UNNEST(message_data.responseevents) AS digest_sent, -- Bot sent user daily digest over DM
'VIEW_DIGEST' IN UNNEST(message_data.responseevents) AS digest_consumed, -- Digest was opened and viewed by the user
'DISCUSSION_SUMMARY_THREAD_SUMMARY_SENT' IN UNNEST(message_data.responseevents)
OR 'DISCUSSION_SUMMARY_DM_RESPONSE_SENT' IN UNNEST(message_data.responseevents)
AS proactive_summary_sent
FROM (
SELECT
*
FROM
event_traces
WHERE
-- Identify the set of valid slack proactive bot workflow starting points that could potentitally contribute to active users definition
(
'PROACTIVE_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where proactive workflow started
OR 'PROACTIVE_CHAT_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where proactive workflow for assistant type started
OR 'PROACTIVE_SEARCH_WORKFLOW_START' IN UNNEST(workflowexecutionpoints) -- Identify messages where proactive workflow for search type started
OR eventtype IN (
'NON_TAGGED_MESSAGE' -- All messages that were not DMs and did not mention @Glean or \glean fall in this category
, 'PROACTIVE_DISCUSSION_SUMMARIZER' -- All events for which proactive thread summarizer workflow is initiated
, 'VIEW_DIGEST_CLICK' -- All events where proactive digest was clicked
, 'DAILY_DIGEST_REMINDER' -- All events where digest was subscribed to and thus proactively sent over DM
)
)
AND NOT 'PROACTIVE_BOT_DISABLED_FAILURE' IN UNNEST(workflowexecutionpoints) -- Remove the events where proactive workflow cannont be triggered. Eg: Invalid Channel
AND NOT 'DROP_BOT_MESSAGE' IN UNNEST(workflowexecutionpoints) -- Remove the events which represent messages sent by other bots, so we dont converse with slack automations
AND NOT 'DROP_EXTERNAL_CHANNEL_MESSAGE' IN UNNEST(workflowexecutionpoints) -- Remove the events that correspond to messages sent on external channels.
) AS message_data
LEFT JOIN (
SELECT
*
FROM
event_traces
WHERE
eventtype IN (
'SHOW_SOURCES' -- All events where a user clicked on show sources modal
, 'SHARE_HELPFULNESS' -- All events for which helpful/not helpful buttons click was initiated
, 'SHARE_CLICK' -- All events for which sharing workflow was started
, 'SUBMIT_FEEDBACK' -- All events for which manual feedback workflow was started
, 'DISMISS_SUGGESTION' -- All events for which dismiss suggestion workflow was initiated
)
) AS interaction_data
ON
message_data.project_id = interaction_data.project_id
AND message_data.eventtrackingtoken = interaction_data.stt
)
GROUP BY
datepartition, project_id, userid
)
SELECT
COALESCE(p.datepartition, r.datepartition) AS datepartition,
COALESCE(p.project_id, r.project_id) AS project_id,
COALESCE(p.userid, r.userid) AS userid,
COALESCE(r.num_useful_responses, 0) AS reactive_num_useful_responses,
COALESCE(p.num_useful_responses, 0) AS proactive_num_useful_responses,
COALESCE(p.num_useful_responses_without_digest, 0) AS proactive_num_useful_responses_without_digest,
COALESCE(r.num_useful_responses, 0) + COALESCE(p.num_useful_responses, 0) AS total_num_useful_responses,
COALESCE(r.num_slack_reactive_bot_downvotes, 0)+ COALESCE(p.num_slack_proactive_bot_downvotes, 0) AS total_num_slackbot_downvotes,
COALESCE(r.num_slack_reactive_bot_upvotes, 0)+ COALESCE(p.num_slack_proactive_bot_upvotes, 0) AS total_num_slackbot_upvotes,
COALESCE(r.num_slack_reactive_bot_responses, 0)+ COALESCE(p.num_slack_proactive_bot_responses, 0) AS total_num_slackbot_responses
FROM slack_proactive_bot_activity p
FULL OUTER JOIN slack_reactive_bot_activity r
ON p.datepartition = r.datepartition
AND p.project_id = r.project_id
AND p.userid = r.userid
),
ai_summarizations AS (
SELECT
summ_runs.datepartition,
summ_runs.project_id,
summ_runs.userid,
COUNT(DISTINCT summ_runs.run_id) AS num_summarizations
FROM (
SELECT
jsonPayload.workflow.runid AS run_id,
resource.labels.project_id,
jsonPayload.user.userid,
MIN(datepartition) AS datepartition
FROM
glean_customer_event
WHERE
jsonPayload.workflow.workflowid IN (
'SUMMARIZE',
'MEETING_SUMMARIZER',
'DISCUSSION_SUMMARIZER',
'MULTI_QUERY_SUMMARIZER'
)
AND jsonPayload.workflow.initiator IN ('USER', 'SUMMARIZE')
GROUP BY
1, 2, 3
UNION ALL
SELECT DISTINCT
jsonPayload.workflowrun.runid AS run_id,
resource.labels.project_id,
jsonPayload.user.userid,
datepartition
FROM
glean_customer_event,
UNNEST(jsonPayload.workflowrun.stepexecutions) AS stepexecutions
WHERE
jsonPayload.type = 'WORKFLOW_RUN'
AND jsonPayload.workflowrun.initiator IN ('USER', 'SUMMARIZE')
AND stepexecutions.workflowid IN (
'SUMMARIZE',
'MEETING_SUMMARIZER',
'DISCUSSION_SUMMARIZER',
'MULTI_QUERY_SUMMARIZER'
)
) summ_runs
GROUP BY
1, 2, 3
),
ai_answers AS (
SELECT
COALESCE(waa.datepartition, aa.datepartition) AS datepartition,
COALESCE(waa.project_id, aa.project_id) AS project_id,
COALESCE(waa.userid, aa.userid) AS userid,
COUNT(DISTINCT waa.runid) + COUNT(DISTINCT CASE WHEN waa.runid IS NULL THEN aa.trackingtoken END) AS num_ai_answers
FROM (
SELECT
datepartition,
resource.labels.project_id,
jsonPayload.user.userid,
jsonPayload.workflowrun.runid,
jsonPayload.workflowrun.sourcetrackingtoken
FROM
glean_customer_event
WHERE
jsonPayload.type = 'WORKFLOW_RUN'
AND jsonPayload.workflowrun.feature = 'AI_ANSWER'
AND jsonPayload.workflowrun.platform = 'WEB'
AND jsonPayload.workflowrun.initiator = 'GLEAN'
) waa
FULL OUTER JOIN (
SELECT
datepartition,
resource.labels.project_id,
jsonPayload.user.userid,
jsonPayload.aianswer.trackingtoken
FROM
glean_customer_event
WHERE
jsonPayload.type = 'AI_ANSWER'
AND COALESCE(jsonPayload.aianswer.trackingtoken, '') != ''
) aa
ON
waa.project_id = aa.project_id
AND waa.userid = aa.userid
AND waa.sourcetrackingtoken = aa.trackingtoken
AND waa.datepartition = aa.datepartition
GROUP BY
1, 2, 3
),
chat_feedback AS (
SELECT
datepartition,
project_id,
userid,
SUM(CASE WHEN event = 'UPVOTE' THEN 1 ELSE 0 END) AS num_chat_upvotes,
SUM(CASE WHEN event = 'DOWNVOTE' THEN 1 ELSE 0 END) AS num_chat_downvotes
FROM (
SELECT
datepartition,
resource.labels.project_id,
jsonPayload.user.userid,
COALESCE(
jsonPayload.chatfeedback.runid,
jsonPayload.chatfeedback.messageid,
jsonPayload.aianswervote.trackingtoken,
CONCAT(
jsonPayload.aisummaryvote.trackingtoken,
'-',
COALESCE(ARRAY_TO_STRING(jsonPayload.aisummaryvote.docids,'-'), '')
)
) AS requestid,
-- take latest vote. disregard manual feedback
MAX_BY(
COALESCE(
jsonPayload.chatfeedback.event,
jsonPayload.aianswervote.vote,
jsonPayload.aisummaryvote.vote
),
timestamp
) AS event
FROM
glean_customer_event
WHERE
(
jsonPayload.type = 'CHAT_FEEDBACK'
AND jsonPayload.chatfeedback.event IN ('UPVOTE', 'DOWNVOTE')
-- coalesce for backward compatibility with workflowid and agentid
AND COALESCE(jsonPayload.chatfeedback.agentid, jsonPayload.chatfeedback.workflowid) is NULL -- ignore feedback from agent workflows
) OR (
jsonPayload.type = 'AI_ANSWER_VOTE'
AND jsonPayload.aianswervote.vote IN ('UPVOTE', 'DOWNVOTE')
) OR (
jsonPayload.type = 'AI_SUMMARY_VOTE'
AND jsonPayload.aisummaryvote.vote IN ('UPVOTE', 'DOWNVOTE')
)
GROUP BY
1, 2, 3, 4
)
GROUP BY
1, 2, 3
),
feature_usage AS (
WITH join_0 AS (
SELECT
COALESCE(other_feature_usage.datepartition, chat_usage.datepartition) AS datepartition,
COALESCE(other_feature_usage.project_id, chat_usage.project_id) AS project_id,
COALESCE(other_feature_usage.userid, chat_usage.userid) AS userid,
COALESCE(other_feature_usage.num_searches, 0) AS _num_searches,
COALESCE(other_feature_usage.num_client_active_sessions, 0) AS _num_client_active_sessions,
COALESCE(other_feature_usage.is_active_other, FALSE) AS _is_active_other,
COALESCE(chat_usage.num_chat_queries, 0) AS _num_chats
FROM
other_feature_usage
FULL OUTER JOIN
chat_usage
ON
other_feature_usage.datepartition = chat_usage.datepartition
AND other_feature_usage.project_id = chat_usage.project_id
AND other_feature_usage.userid = chat_usage.userid
),
join_1 AS (
SELECT
COALESCE(join_0.datepartition, slackbot_usage.datepartition) AS datepartition,
COALESCE(join_0.project_id, slackbot_usage.project_id) AS project_id,
COALESCE(join_0.userid, slackbot_usage.userid) AS userid,
COALESCE(join_0._num_searches, 0) AS _num_searches,
COALESCE(join_0._num_client_active_sessions, 0) AS _num_client_active_sessions,
COALESCE(join_0._is_active_other, FALSE) AS _is_active_other,
COALESCE(join_0._num_chats, 0) AS _num_chats,
COALESCE(slackbot_usage.total_num_useful_responses, 0) AS _num_slackbot_useful_responses,
COALESCE(slackbot_usage.total_num_slackbot_downvotes, 0) AS _num_slackbot_downvotes,
COALESCE(slackbot_usage.total_num_slackbot_upvotes, 0) AS _num_slackbot_upvotes,
COALESCE(slackbot_usage.total_num_slackbot_responses, 0) AS _num_slackbot_responses
FROM
join_0
FULL OUTER JOIN
slackbot_usage
ON
join_0.datepartition = slackbot_usage.datepartition
AND join_0.project_id = slackbot_usage.project_id
AND join_0.userid = slackbot_usage.userid
),
join_2 AS (
SELECT
COALESCE(join_1.datepartition, ai_summarizations.datepartition) AS datepartition,
COALESCE(join_1.project_id, ai_summarizations.project_id) AS project_id,
COALESCE(join_1.userid, ai_summarizations.userid) AS userid,
COALESCE(join_1._num_searches, 0) AS _num_searches,
COALESCE(join_1._num_client_active_sessions, 0) AS _num_client_active_sessions,
COALESCE(join_1._is_active_other, FALSE) AS _is_active_other,
COALESCE(join_1._num_chats, 0) AS _num_chats,
COALESCE(join_1._num_slackbot_useful_responses, 0) AS _num_slackbot_useful_responses,
COALESCE(join_1._num_slackbot_downvotes, 0) AS _num_slackbot_downvotes,
COALESCE(join_1._num_slackbot_upvotes, 0) AS _num_slackbot_upvotes,
COALESCE(join_1._num_slackbot_responses, 0) AS _num_slackbot_responses,
COALESCE(ai_summarizations.num_summarizations, 0) AS _num_summarizations
FROM
join_1
FULL OUTER JOIN
ai_summarizations
ON
join_1.datepartition = ai_summarizations.datepartition
AND join_1.project_id = ai_summarizations.project_id
AND join_1.userid = ai_summarizations.userid
),
join_3 AS (
SELECT
COALESCE(join_2.datepartition, ai_answers.datepartition) AS datepartition,
COALESCE(join_2.project_id, ai_answers.project_id) AS project_id,
COALESCE(join_2.userid, ai_answers.userid) AS userid,
COALESCE(join_2._num_searches, 0) AS _num_searches,
COALESCE(join_2._num_client_active_sessions, 0) AS _num_client_active_sessions,
COALESCE(join_2._is_active_other, FALSE) AS _is_active_other,
COALESCE(join_2._num_chats, 0) AS _num_chats,
COALESCE(join_2._num_slackbot_useful_responses, 0) AS _num_slackbot_useful_responses,
COALESCE(join_2._num_slackbot_downvotes, 0) AS _num_slackbot_downvotes,
COALESCE(join_2._num_slackbot_upvotes, 0) AS _num_slackbot_upvotes,
COALESCE(join_2._num_slackbot_responses, 0) AS _num_slackbot_responses,
COALESCE(join_2._num_summarizations, 0) AS _num_summarizations,
COALESCE(ai_answers.num_ai_answers, 0) AS _num_ai_answers
FROM
join_2
FULL OUTER JOIN
ai_answers
ON
join_2.datepartition = ai_answers.datepartition
AND join_2.project_id = ai_answers.project_id
AND join_2.userid = ai_answers.userid
)
SELECT
COALESCE(join_3.datepartition, chat_feedback.datepartition) AS datepartition,
COALESCE(join_3.project_id, chat_feedback.project_id) AS project_id,
COALESCE(join_3.userid, chat_feedback.userid) AS userid,
COALESCE(join_3._num_searches, 0) AS _num_searches,
COALESCE(join_3._num_ai_answers, 0) AS _num_ai_answers,
COALESCE(join_3._num_client_active_sessions, 0) AS _num_client_active_sessions,
COALESCE(join_3._is_active_other, FALSE) AS _is_active_other,
COALESCE(join_3._num_chats, 0) AS _num_chats,
COALESCE(join_3._num_slackbot_useful_responses, 0) AS _num_slackbot_useful_responses,
COALESCE(join_3._num_slackbot_downvotes, 0) AS _num_slackbot_downvotes,
COALESCE(join_3._num_slackbot_upvotes, 0) AS _num_slackbot_upvotes,
COALESCE(join_3._num_slackbot_responses, 0) AS _num_slackbot_responses,
COALESCE(join_3._num_summarizations, 0) AS _num_summarizations,
COALESCE(chat_feedback.num_chat_upvotes, 0) AS _num_chat_upvotes,
COALESCE(chat_feedback.num_chat_downvotes, 0) AS _num_chat_downvotes
FROM
join_3
FULL OUTER JOIN
chat_feedback
ON
join_3.datepartition = chat_feedback.datepartition
AND join_3.project_id = chat_feedback.project_id
AND join_3.userid = chat_feedback.userid
),
canonicalized AS (
SELECT
feature_usage.datepartition,
feature_usage.project_id,
COALESCE(latest_orgchart_data.userid, id_to_alias.canonicalid, feature_usage.userid) AS userid,
COALESCE(SUM(_num_searches), 0) AS num_searches,
COALESCE(SUM(_num_chats), 0) AS num_chats,
COALESCE(SUM(_num_ai_answers), 0) AS num_ai_answers,
COALESCE(SUM(_num_summarizations), 0) AS num_summarizations,
COALESCE(SUM(_num_chat_upvotes), 0) AS num_chat_upvotes,
COALESCE(SUM(_num_chat_downvotes), 0) AS num_chat_downvotes,
COALESCE(SUM(_num_client_active_sessions), 0) AS num_client_active_sessions,
COALESCE(SUM(_num_slackbot_useful_responses), 0) AS num_slackbot_useful_responses,
COALESCE(SUM(_num_slackbot_downvotes), 0) AS num_slackbot_downvotes,
COALESCE(SUM(_num_slackbot_upvotes), 0) AS num_slackbot_upvotes,
COALESCE(SUM(_num_slackbot_responses), 0) AS num_slackbot_responses,
COALESCE(LOGICAL_OR(_is_active_other), FALSE) AS is_active_other
FROM
feature_usage
LEFT JOIN
id_to_alias
ON
feature_usage.project_id = id_to_alias.project_id
AND feature_usage.userid = id_to_alias.aliasid
AND feature_usage.datepartition = id_to_alias.datepartition
LEFT JOIN
glean_customer_event.latest_orgchart_data AS latest_orgchart_data
ON
COALESCE(id_to_alias.canonicalid, feature_usage.userid) = latest_orgchart_data.loggingid
WHERE
COALESCE(id_to_alias.canonicalid, feature_usage.userid) IS NOT NULL
GROUP BY
1, 2, 3
HAVING
COALESCE(SUM(_num_searches), 0) > 0
OR COALESCE(SUM(_num_chats), 0) > 0
OR COALESCE(SUM(_num_ai_answers), 0) > 0
OR COALESCE(SUM(_num_summarizations), 0) > 0
OR COALESCE(SUM(_num_client_active_sessions), 0) > 0
OR COALESCE(SUM(_num_slackbot_useful_responses), 0) > 0
OR COALESCE(SUM(_num_chat_upvotes), 0) > 0
OR COALESCE(SUM(_num_chat_downvotes), 0) > 0
OR COALESCE(SUM(_num_slackbot_downvotes), 0) > 0
OR COALESCE(SUM(_num_slackbot_upvotes), 0) > 0
OR COALESCE(SUM(_num_slackbot_responses), 0) > 0
OR COALESCE(LOGICAL_OR(_is_active_other), FALSE)
)
, combined_usage AS (
SELECT
c.datepartition AS datepartition,
c.userid AS userid,
((c.num_searches + c.num_chats + c.num_client_active_sessions + c.num_slackbot_useful_responses + c.num_ai_answers + c.num_summarizations) > 0 OR c.is_active_other) AS is_active_overall,
c.num_searches AS num_searches,
COALESCE(c.num_chats, 0) + COALESCE(c.num_ai_answers, 0) + COALESCE(c.num_summarizations, 0) + COALESCE(c.num_slackbot_useful_responses, 0) AS num_assistant_interactions
FROM
canonicalized c
)
SELECT
MetricDate,
COUNT(DISTINCT CASE WHEN c.is_active_overall AND DATE(MetricDate) = c.datepartition THEN c.userid END) AS DailyActiveUsers,
COUNT(DISTINCT CASE WHEN c.is_active_overall AND c.datepartition BETWEEN DATE(MetricDate - INTERVAL '6' DAY) AND DATE(MetricDate) THEN c.userid END) AS WeeklyActiveUsers,
COUNT(DISTINCT CASE WHEN c.is_active_overall THEN c.userid END) AS MonthlyActiveUsers,
COUNT(DISTINCT CASE WHEN c.num_searches > 0 AND DATE(MetricDate) = c.datepartition THEN c.userid END) AS SearchDailyActiveUsers,
COUNT(DISTINCT CASE WHEN c.num_searches > 0 AND c.datepartition BETWEEN DATE(MetricDate - INTERVAL '6' DAY) AND DATE(MetricDate) THEN c.userid END) AS SearchWeeklyActiveUsers,
COUNT(DISTINCT CASE WHEN c.num_searches > 0 THEN c.userid END) AS SearchMonthlyActiveUsers,
SUM(CASE WHEN DATE(MetricDate) = c.datepartition THEN num_searches END) AS DailySearchCount,
COUNT(DISTINCT CASE WHEN c.num_assistant_interactions > 0 AND DATE(MetricDate) = c.datepartition THEN c.userid END) AS AssistantDailyActiveUsers,
COUNT(DISTINCT CASE WHEN c.num_assistant_interactions > 0 AND c.datepartition BETWEEN DATE(MetricDate - INTERVAL '6' DAY) AND DATE(MetricDate) THEN c.userid END) AS AssistantWeeklyActiveUsers,
COUNT(DISTINCT CASE WHEN c.num_assistant_interactions > 0 THEN c.userid END) AS AssistantMonthlyActiveUsers,
SUM(CASE WHEN DATE(MetricDate) = c.datepartition THEN num_assistant_interactions END) AS DailyAssistantInteractionsCount
FROM
UNNEST(GENERATE_TIMESTAMP_ARRAY(TIMESTAMP(start_date), TIMESTAMP(end_date), INTERVAL '1' DAY)) AS MetricDate
LEFT JOIN
combined_usage c
ON
c.datepartition BETWEEN DATE(MetricDate - INTERVAL '27' DAY) AND DATE(MetricDate)
-- Do not force Active Users to be present in the Org Chart; include qualifying activity even if not in org chart
LEFT JOIN
glean_customer_event.latest_orgchart_data o
ON
c.userid = o.userid
WHERE
1=1
--Adjust department filter if needed
--and o.department=''
GROUP BY
1
ORDER BY
1 DESC
Output: Daily counts of searches and unique users performing webapp searches
Grain: Daily aggregation
Use Case: Monitor webapp search adoption and usage patterns
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- Optional project filtering - only filter if project_id is specified
(project_id IS NULL OR resource.labels.project_id = project_id)
)
SELECT
TIMESTAMP_TRUNC(`timestamp`, DAY) AS utc_day
, COUNT(DISTINCT jsonPayload.User.UserId) AS num_users
, COUNT(DISTINCT jsonPayload.Search.TrackingToken) AS num_searches
FROM glean_customer_event
WHERE jsonPayload.Type = 'SEARCH'
AND jsonPayload.Search.Initiator = 'USER'
AND jsonPayload.Search.Modality = 'FULLPAGE'
GROUP BY 1
ORDER BY 1
Output: Daily counts of embedded searches and users by host domain
Grain: Daily aggregation by embedding domain
Use Case: Track embedded search component adoption across different platforms and domains
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- Optional project filtering - only filter if project_id is specified
(project_id IS NULL OR resource.labels.project_id = project_id)
)
SELECT
TIMESTAMP_TRUNC(`timestamp`, DAY) AS utc_day
, jsonPayload.Search.HostDomain
, COUNT(DISTINCT jsonPayload.User.UserId) AS num_users
, COUNT(DISTINCT jsonPayload.Search.TrackingToken) AS num_searches
FROM glean_customer_event
WHERE jsonPayload.Type = 'SEARCH'
AND jsonPayload.Search.Initiator = 'USER'
AND jsonPayload.Search.Modality = 'EMBEDDED_SEARCH'
GROUP BY 1, 2
ORDER BY 1, 2
Search Analytics
Output: Daily search metrics including queries, sessions, queries per session, and unique users
Grain: Daily aggregation over 7-day period
Use Case: Analyze search engagement depth and session patterns
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from the last 7 days (adjust as needed)
`timestamp` >= TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL -7 DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
SELECT
TIMESTAMP_TRUNC(`timestamp`, DAY) AS utc_day
,COUNT(DISTINCT jsonPayload.User.UserId) AS num_users
,COUNT(DISTINCT jsonPayload.Search.TrackingToken) AS num_queries
,COUNT(DISTINCT jsonPayload.Search.SessionTrackingToken) AS num_sessions
,COUNT(DISTINCT jsonPayload.Search.TrackingToken)/COUNT(DISTINCT jsonPayload.Search.SessionTrackingToken) AS Queries_per_session
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH'
-- filter for user queries (exclude Gleanbot requests)
AND LOWER(jsonPayload.Search.Initiator) IN ('user')
AND LOWER(jsonPayload.Search.Modality) NOT IN ('gleanbot', '')
GROUP BY 1
ORDER BY 1;
Output: Daily counts of search result clicks and queries that received clicks
Grain: Daily aggregation over 7-day period
Use Case: Measure search result relevance and click-through rates
DECLARE lookback_period INT64 DEFAULT 7;
DECLARE lookback_unit STRING DEFAULT 'DAY';
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL -- Set to NULL to include all projects, or specify a project ID
WITH
-- Base CTE to centralize table reference with parameterized lookback period and optional project filtering
glean_customer_event AS (
SELECT
DATE(timestamp) AS datepartition
, resource.labels.project_id AS project_id
, jsonPayload.User.UserId AS userid
, jsonPayload
, timestamp
FROM
-- replace <glean_customer_event_table> with your firm's table name
<glean_customer_event_table>
WHERE
timestamp >= CASE
WHEN lookback_unit = 'DAY' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY))
WHEN lookback_unit = 'WEEK' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period WEEK))
WHEN lookback_unit = 'MONTH' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period MONTH))
WHEN lookback_unit = 'HOUR' THEN TIMESTAMP(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL lookback_period HOUR))
ELSE TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY)) -- Default to DAY
END
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
SELECT
TIMESTAMP_TRUNC(`timestamp`, DAY) AS utc_day
,count(jsonPayload.SearchClick.TrackingToken) AS num_clicks
,count(DISTINCT jsonPayload.SearchClick.TrackingToken) AS num_queries_with_clicks
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH_CLICK'
GROUP BY 1
ORDER BY 1
Output: Daily clicks, queries with clicks, and average position by datasource
Grain: Daily aggregation by datasource over 7-day period
Use Case: Evaluate datasource performance and result positioning effectiveness
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from the last 7 days (adjust as needed)
`timestamp` >= TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL -7 DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
SELECT
TIMESTAMP_TRUNC(`timestamp`, DAY) AS utc_day
,jsonPayload.searchclick.datasource
,count(jsonPayload.SearchClick.TrackingToken) AS num_clicks
,count(DISTINCT jsonPayload.SearchClick.TrackingToken) AS num_queries_with_clicks
,avg(jsonPayload.searchclick.position)+1 as average_position
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH_CLICK'
GROUP BY 1,2
ORDER BY 1,3 desc;
Output: List of search queries ranked by frequency and user count
Grain: Query-level aggregation over 14-day period
Use Case: Identify popular search topics and content gaps
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from the last 14 days (adjust as needed)
`timestamp` >= TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL -14 DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, queries_data AS (
SELECT
DISTINCT `timestamp`
, jsonPayload.Search.SessionTrackingToken AS stt
, jsonPayload.Search.TrackingToken AS qtt --Query Tracking Token
, jsonPayload.Search.Query AS search_query
, jsonPayload.user.userid
, jsonPayload.user.useremail
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH'
-- filter for user queries (exclude Gleanbot requests)
AND LOWER(jsonPayload.Search.Initiator) IN ('user')
AND LOWER(jsonPayload.Search.Modality) NOT IN ('gleanbot', '')
)
SELECT
search_query
, COUNT(DISTINCT qtt) AS frequency
, COUNT(DISTINCT userid) AS user_count
FROM
queries_data
GROUP BY
1
ORDER BY
2 DESC
Output: Individual search terms ranked by frequency and user count
Grain: Search term-level aggregation over 14-day period
Use Case: Understand user vocabulary and optimize search suggestions
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from the last 14 days (adjust as needed)
`timestamp` >= TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL -14 DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, queries_data AS (
SELECT
DISTINCT `timestamp`
, jsonPayload.Search.SessionTrackingToken AS stt
, jsonPayload.Search.TrackingToken AS qtt --Query Tracking Token
, jsonPayload.Search.Query AS search_query
, jsonPayload.user.userid
, jsonPayload.user.useremail
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH'
-- filter for user queries (exclude Gleanbot requests)
AND LOWER(jsonPayload.Search.Initiator) IN ('user')
AND LOWER(jsonPayload.Search.Modality) NOT IN ('gleanbot', '')
)
, query_terms_list AS (
SELECT
*
, SPLIT(search_query, ' ') AS query_terms
FROM
queries_data
)
, query_terms_flattened AS (
SELECT
timestamp
, stt
, qtt
, userid
, useremail
, query_term
FROM
query_terms_list
, UNNEST(query_terms) AS query_term
)
SELECT
query_term
, COUNT(*) AS frequency
, COUNT(DISTINCT userid) AS user_count
FROM
query_terms_flattened
GROUP BY
1
ORDER BY
2 DESC
Search Quality & Performance
Output: Daily ratio of satisfied sessions (sessions with clicks) to total sessions
Grain: Daily aggregation over 14-day period
Use Case: Measure search quality and user satisfaction trends
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from the last 14 days (adjust as needed)
`timestamp` >= TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL -14 DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, queries_data AS (
SELECT
DISTINCT TIMESTAMP_TRUNC(`timestamp`, DAY) AS utc_day
, jsonPayload.Search.SessionTrackingToken AS stt
, jsonPayload.Search.TrackingToken AS qtt --Query Tracking Token
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH'
-- filter for user queries (exclude Gleanbot requests)
AND LOWER(jsonPayload.Search.Initiator) IN ('user')
AND LOWER(jsonPayload.Search.Modality) NOT IN ('gleanbot', '')
)
, clicks_data AS (
SELECT
DISTINCT jsonPayload.SearchClick.TrackingToken AS qtt
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH_CLICK'
)
SELECT
q.utc_day --metric date
, COUNT(DISTINCT IF(c.qtt IS NOT NULL, q.stt, NULL)) / GREATEST(COUNT(DISTINCT q.stt), 1) AS session_satisfaction --Ratio of satisfied sessions to all sessions
FROM
queries_data q
LEFT JOIN
clicks_data c
ON
q.qtt = c.qtt
GROUP BY 1
ORDER BY 1;
Output: Daily mean average precision score based on click positions
Grain: Daily aggregation over 14-day period
Use Case: Evaluate search result ranking quality and relevance
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from the last 14 days (adjust as needed)
`timestamp` >= TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL -14 DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, clicks_data AS (
SELECT
jsonPayload.SearchClick.TrackingToken AS qtt
,jsonPayload.searchclick.position AS position
,MAX(TIMESTAMP_TRUNC(`timestamp`, DAY)) AS utc_day
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH_CLICK'
GROUP BY
1,2
)
, precision_data AS (
SELECT
utc_day
, qtt
, position
, COUNT(*) OVER (PARTITION BY qtt ORDER BY position) / (1 + position) AS precision
FROM
clicks_data
)
, average_precision_data AS (
SELECT
utc_day
, qtt
, AVG(precision) AS average_precision
FROM
precision_data
GROUP BY
1, 2
)
SELECT
utc_day --metric_date
, AVG(average_precision) AS mean_average_precision
FROM
average_precision_data
GROUP BY
1
ORDER BY
1;
Output: Daily median time in milliseconds from first query to last click in a session
Grain: Daily aggregation over 14-day period
Use Case: Measure search session efficiency and time-to-information
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from the last 14 days (adjust as needed)
`timestamp` >= TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL -14 DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, queries_data AS (
SELECT
DISTINCT TIMESTAMP_TRUNC(`timestamp`, DAY) AS utc_day
, jsonPayload.Search.SessionTrackingToken AS stt
, jsonPayload.Search.TrackingToken AS qtt
, timestamp
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH'
-- filter for user queries (exclude background and slackbot requests)
AND LOWER(jsonPayload.Search.Initiator) IN ('user')
AND LOWER(jsonPayload.Search.Modality) NOT IN ('gleanbot', '')
)
, clicks_data AS (
SELECT
jsonPayload.SearchClick.TrackingToken AS qtt
, MAX(timestamp) as timestamp
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH_CLICK'
GROUP BY 1
)
, sessions_w_clicks AS (
SELECT
utc_day --metric_date
, stt
, first_query_ts
, last_click_ts
, timestamp_diff(last_click_ts, first_query_ts, millisecond) AS time_from_first_query_to_last_click
FROM (
SELECT
stt
, MIN(utc_day) AS utc_day
, MIN(q.timestamp) AS first_query_ts
, MIN(c.timestamp) AS first_click_ts
, MAX(c.timestamp) AS last_click_ts
, COUNTIF(c.qtt IS NOT NULL) AS num_clicks
FROM
queries_data q
LEFT JOIN
clicks_data c
ON
q.qtt = c.qtt
GROUP BY
1
)
WHERE
num_clicks > 0
)
SELECT
utc_day --metric_date
-- , avg(time_from_first_query_to_last_click) as mean_time_from_first_query_to_last_click -- surface average if desired
, approx_quantiles(time_from_first_query_to_last_click, 100)[offset(50)] AS p50_time_first_query_to_last_click_ms
FROM
sessions_w_clicks
GROUP BY
1
ORDER BY
1
Output: A list of all search queries annotated with whether an AI-generated answer was present for each query.
Grain: Individual query-answer pairs with configurable time periods
Use Case: Understand AI answer usage patterns, track which searches trigger AI responses, and analyze AI answer effectiveness
-- Parameterized lookback period using variables
DECLARE lookback_period INT64 DEFAULT 1;
DECLARE lookback_unit STRING DEFAULT 'DAY';
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH
-- Base CTE to centralize table reference with parameterized lookback period and optional project filtering
glean_customer_event AS (
SELECT
DATE(timestamp) AS datepartition
, resource.labels.project_id AS project_id
, jsonPayload.User.UserId AS userid
, jsonPayload
, timestamp
FROM
-- replace <glean_customer_event_table> with your firm's table name
<glean_customer_event_table>
WHERE
timestamp >= CASE
WHEN lookback_unit = 'DAY' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY))
WHEN lookback_unit = 'WEEK' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period WEEK))
WHEN lookback_unit = 'MONTH' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period MONTH))
WHEN lookback_unit = 'HOUR' THEN TIMESTAMP(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL lookback_period HOUR))
ELSE TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY)) -- Default to DAY
END
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, search_events AS (
SELECT
datepartition
, project_id
, userid
, jsonPayload.Search.Query AS query
, jsonPayload.Search.Modality AS modality
, jsonPayload.Search.Initiator AS initiator
, jsonPayload.Search.TrackingToken AS trackingtoken
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH'
AND LOWER(jsonPayload.Search.Initiator) IN ('user')
)
, -- Direct AI Answer events
direct_ai_answers AS (
SELECT
datepartition
, project_id
, userid
, jsonPayload.AIAnswer.TrackingToken AS trackingtoken
, jsonPayload.AIAnswer.Response AS response
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'AI_ANSWER'
AND jsonPayload.AIAnswer.TrackingToken != ''
)
, workflow_conversation AS (
SELECT
jsonPayload.workflowconversation.workflowrunid as runid
, MAX(CASE WHEN message.author='USER' THEN text END) as user_query
, MAX(CASE WHEN message.author='GLEAN' THEN text END) as response
FROM
glean_customer_event
, UNNEST(jsonPayload.workflowconversation.messages) message
WHERE
jsonPayload.type='WORKFLOW_CONVERSATION'
GROUP BY
runid
)
, -- Workflow-based AI Answer events
workflow_ai_answers AS (
SELECT
datepartition
, project_id
, userid
, jsonPayload.WorkflowRun.SourceTrackingToken AS trackingtoken
, wc.response
FROM
glean_customer_event ai_answer
LEFT JOIN
workflow_conversation wc
ON
ai_answer.jsonPayload.workflowrun.runid = wc.runid
WHERE
ai_answer.jsonPayload.Type = 'WORKFLOW_RUN'
AND ai_answer.jsonPayload.WorkflowRun.Feature = 'AI_ANSWER'
AND ai_answer.jsonPayload.WorkflowRun.Initiator = 'GLEAN'
AND ai_answer.jsonPayload.WorkflowRun.Platform = 'WEB'
)
, -- Combine both AI answer types
all_ai_answers AS (
SELECT * FROM direct_ai_answers
UNION ALL
SELECT * FROM workflow_ai_answers
)
SELECT
-- Search Information (from original queries)
s.datepartition
, s.trackingtoken
, s.query
, s.modality
, s.userid
-- AI Answer Information
, aa.response
, CASE WHEN aa.trackingtoken IS NOT NULL THEN TRUE ELSE FALSE END AS is_ai_answer_present
FROM
search_events s
LEFT JOIN
all_ai_answers aa
ON
aa.datepartition = s.datepartition
AND aa.project_id = s.project_id
AND aa.userid = s.userid
AND aa.trackingtoken = s.trackingtoken
ORDER BY
aa.datepartition DESC
, aa.project_id
, aa.userid
Output: Search queries linked to AI-generated answers that have an upvote or downvote associated with them, with user and project context
Grain: Individual query-answer pairs with configurable time periods
Use Case: Understand distribution of AI answer feedback (upvote/downvote), and analyze AI answer feedback rate
-- Parameterized lookback period using variables
DECLARE lookback_period INT64 DEFAULT 1;
DECLARE lookback_unit STRING DEFAULT 'DAY';
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL -- Set to NULL to include all projects, or specify a project ID
BEGIN
WITH
-- Base CTE to centralize table reference with parameterized lookback period and optional project filtering
glean_customer_event AS (
SELECT
DATE(timestamp) AS datepartition
, resource.labels.project_id AS project_id
, jsonPayload.User.UserId AS userid
, jsonPayload
, timestamp
FROM
-- replace <glean_customer_event_table> with your firm's table name
<glean_customer_event_table>
WHERE
timestamp >= CASE
WHEN lookback_unit = 'DAY' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY))
WHEN lookback_unit = 'WEEK' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period WEEK))
WHEN lookback_unit = 'MONTH' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period MONTH))
WHEN lookback_unit = 'HOUR' THEN TIMESTAMP(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL lookback_period HOUR))
ELSE TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY)) -- Default to DAY
END
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, search_events AS (
SELECT
datepartition
, project_id
, userid
, jsonPayload.Search.Query AS query
, jsonPayload.Search.Modality AS modality
, jsonPayload.Search.Initiator AS initiator
, jsonPayload.Search.TrackingToken AS trackingtoken
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'SEARCH'
)
, -- Direct AI Answer events
direct_ai_answers AS (
SELECT
datepartition
, project_id
, userid
, jsonPayload.AIAnswer.TrackingToken AS trackingtoken
FROM
glean_customer_event
WHERE
jsonPayload.Type = 'AI_ANSWER'
AND jsonPayload.AIAnswer.TrackingToken != ''
)
, -- Workflow-based AI Answer events
workflow_ai_answers AS (
SELECT
datepartition
, project_id
, userid
, jsonPayload.WorkflowRun.SourceTrackingToken AS trackingtoken
FROM
glean_customer_event ai_answer
WHERE
ai_answer.jsonPayload.Type = 'WORKFLOW_RUN'
AND ai_answer.jsonPayload.WorkflowRun.Feature = 'AI_ANSWER'
AND ai_answer.jsonPayload.WorkflowRun.Initiator = 'GLEAN'
AND ai_answer.jsonPayload.WorkflowRun.Platform = 'WEB'
)
, -- Combine both AI answer types
all_ai_answers AS (
SELECT * FROM direct_ai_answers
UNION ALL
SELECT * FROM workflow_ai_answers
)
, -- Get all AI answers that have an upvote (thumbs up) or downvote (thumbs down)
ai_answer_votes AS (
SELECT
jsonpayload.aianswervote.trackingtoken
,jsonpayload.aianswervote.vote
FROM glean_customer_event
WHERE
jsonpayload.type = 'AI_ANSWER_VOTE'
)
SELECT
-- Dimensional Context
aa.datepartition
, aa.project_id
, aa.userid
-- Search Information (from original queries)
, s.query AS search_query
, s.modality AS search_modality
, s.initiator AS search_initiator
-- AI Answer Vote Information
, aav.vote AS ai_answer_vote
FROM
all_ai_answers aa
LEFT JOIN
search_events s
ON
aa.datepartition = s.datepartition
AND aa.project_id = s.project_id
AND aa.userid = s.userid
AND aa.trackingtoken = s.trackingtoken
LEFT JOIN
ai_answer_votes aav
ON
aa.trackingtoken = aav.trackingtoken
WHERE
-- Filter out AI answers without corresponding search events or missing initiators
s.initiator != ''
-- Filter out search queries without an AI answer.
-- Note: If you want to get the % of searches that have an AI Answer, commenet this field, so you'll get the entire list of searches along with their AI Answer Vote status
AND aav.vote != ''
ORDER BY
aa.datepartition DESC
, aa.project_id
, aa.userid;
END;
Chat & AI Analytics
Metrics: Active Chat Users, # of Chats
Grain: Per Day, Per User
This query identifies users who had at least one chat interaction on a given date; one row per user per day.
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
DECLARE workflow_ids ARRAY<STRING> DEFAULT [
'DOC_CONTEXT_READER',
'ORIGINAL_MESSAGE_SEARCH',
'DIRECT_LLM_RESPONSE',
'REACT_TOOLS_2_HOPS',
'DEFAULT_CHAT',
'WORLD_MODE_V2',
'DEEP_RESEARCH_PYAGENT',
'DEEP_RESEARCH_PREVIEW'
];
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- Optional project filtering - only filter if project_id is specified
(project_id IS NULL OR resource.labels.project_id = project_id)
)
, -- Extract the user level data from the most recent date to get most recent user attributes
-- Concatenate aliasids at a userid level
product_snapshot_agg AS (
SELECT
jsonPayload.productsnapshot.user.id AS userid,
MAX(jsonPayload.productsnapshot.user.signuptime) AS signuptime,
ARRAY_CONCAT_AGG(jsonPayload.productsnapshot.user.aliasids) AS aliasids,
MAX(jsonPayload.productsnapshot.user.department) AS department
FROM glean_customer_event
WHERE
-- Adjust as needed
DATE(timestamp) = CURRENT_DATE - INTERVAL 1 DAY
AND jsonPayload.type = 'PRODUCT_SNAPSHOT'
GROUP BY 1
)
, -- [Optional]: For users belonging to departments with less than 5 members, assign them a single bucket
latest_orgchart_data AS (
SELECT
userid,
signuptime,
aliasids,
CASE WHEN COUNT(userid) OVER (PARTITION BY department) < 5
THEN "Departments less than 5 Users"
ELSE department
END AS department
FROM product_snapshot_agg
)
, -- Map each alias to the canonical UserID
id_to_alias AS (
SELECT
DISTINCT aliasid,
userid AS canonicalid
FROM
latest_orgchart_data, UNNEST(aliasids) AS aliasid
)
, -- [IMP]: Calculate chat usage metrics.
/*
This CTE merges data across three event types: CHAT, WORKFLOW, and WORKFLOW_RUN.
The WORKFLOW event type was active from mid-February to mid-June 2025, after which it was replaced by WORKFLOW_RUN.
Before that period, all chat-related activity was logged under the CHAT event type.
To calculate accurate Chat usage metrics, we first UNION WORKFLOW and WORKFLOW_RUN. Since only one is ever populated
at a given time, this is a safe operation. We then perform a FULL OUTER JOIN with CHAT to deduplicate any overlapping
runs that appear in both CHAT and WORKFLOW/WORKFLOW_RUN logs.
*/
chat_usage as (
-- Workflow data + Workflow run data
SELECT
COALESCE(wf.datepartition, chat.datepartition) AS datepartition,
COALESCE(wf.userid, chat.userid) AS userid,
COUNT(DISTINCT wf.run_id) + COUNT(DISTINCT CASE WHEN wf.run_id IS NULL THEN chat.qtt END) AS num_chat_queries
FROM (
SELECT
jsonPayload.workflow.runid AS run_id,
jsonPayload.user.userid,
MIN(DATE(timestamp)) AS datepartition
FROM glean_customer_event
WHERE
jsonPayload.type = 'WORKFLOW'
-- These workflowIDs correspond to Chats
AND jsonPayload.workflow.workflowid IN UNNEST(workflow_ids)
AND jsonPayload.workflow.initiator = 'USER'
GROUP BY 1,2
UNION ALL
SELECT
DISTINCT jsonPayload.workflowrun.runid AS run_id,
jsonPayload.user.userid,
DATE(timestamp) AS datepartition
FROM glean_customer_event,
UNNEST(jsonPayload.workflowrun.workflowexecutions) AS wfe
WHERE jsonPayload.type = 'WORKFLOW_RUN'
AND jsonPayload.workflowrun.initiator = 'USER'
AND wfe.workflowid IN UNNEST(workflow_ids)
) wf
-- Join the unioned workflow data with chat data
FULL OUTER JOIN (
SELECT
DATE(timestamp) AS datepartition,
jsonPayload.user.userid,
jsonPayload.chat.qtt AS qtt,
jsonPayload.chat.workflowrunid AS workflowrunid
FROM glean_customer_event
WHERE jsonPayload.type = 'CHAT'
AND jsonPayload.chat.initiator = 'USER'
) chat
ON wf.run_id = chat.workflowrunid
GROUP BY 1, 2
)
, feature_usage AS (
SELECT
datepartition,
userid,
COALESCE(chat_usage.num_chat_queries, 0) AS _num_chats
FROM chat_usage
)
, canonicalized AS (
SELECT
datepartition,
COALESCE(id_to_alias.canonicalid, feature_usage.userid) AS userid,
COALESCE(SUM(_num_chats), 0) AS num_chats
FROM feature_usage
LEFT JOIN id_to_alias
ON feature_usage.userid = id_to_alias.aliasid
WHERE
COALESCE(id_to_alias.canonicalid, feature_usage.userid) IS NOT NULL
GROUP BY 1, 2
HAVING COALESCE(SUM(_num_chats), 0) > 0
)
SELECT * FROM canonicalized ORDER BY datepartition DESC, userid
Output: Daily feedback metrics (upvotes, downvotes, manual feedback) by AI application
Grain: Daily aggregation by AI application
Use Case: Monitor custom AI app performance and user satisfaction
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
DECLARE lookback_period INT64 DEFAULT 28;
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- filter for data from the last 28 days (adjust as needed)
timestamp >= TIMESTAMP(DATE_ADD(CURRENT_DATE, INTERVAL -lookback_period DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, feedback_events AS (
SELECT
DISTINCT TIMESTAMP_TRUNC(m.`timestamp`, DAY) AS utc_day
, m.jsonPayload.Chat.Qtt AS tracking_token --Query Tracking Token
, COALESCE(m.jsonPayload.Chat.ApplicationId, 'Default Gleanchat') AS application_id
, f.jsonPayload.ChatFeedback.Event AS event
FROM
glean_customer_event m
LEFT JOIN glean_customer_event f
ON m.jsonPayload.Chat.ResponseMessageId = f.jsonPayload.ChatFeedback.MessageId
WHERE
m.jsonPayload.Type = 'CHAT'
AND f.jsonPayload.Type = 'CHAT_FEEDBACK'
UNION ALL
SELECT
DISTINCT TIMESTAMP_TRUNC(m.`timestamp`, DAY) AS utc_day
, m.jsonPayload.WorkflowRun.RunId AS tracking_token
, COALESCE(m.jsonPayload.WorkflowRun.ApplicationId, 'Default Gleanchat') AS application_id
, f.jsonPayload.ChatFeedback.Event AS event
FROM
glean_customer_event m
LEFT JOIN glean_customer_event f
ON m.jsonPayload.WorkflowRun.RunId = f.jsonPayload.ChatFeedback.RunId
WHERE
m.jsonPayload.Type = 'WORKFLOW_RUN'
AND f.jsonPayload.Type = 'CHAT_FEEDBACK'
)
SELECT
application_id
, utc_day --dateparititon
, COUNT(DISTINCT tracking_token) AS num_interactions
, COUNT(DISTINCT CASE WHEN event = 'UPVOTE' THEN tracking_token END) AS upvotes
, COUNT(DISTINCT CASE WHEN event = 'DOWNVOTE' THEN tracking_token END) AS downvotes
, COUNT(DISTINCT CASE WHEN event = 'MANUAL_FEEDBACK' THEN tracking_token END) AS shared_feedback
FROM
feedback_events
WHERE
COALESCE(NULLIF(application_id,''), 'Default Gleanchat') != 'Default Gleanchat'
GROUP BY
1, 2
Output: Chat messages with feedback ratings and comments
Grain: Individual chat message level showing ratings and comments if exists
Use Case: Analyze chat response quality and gather detailed user feedback for improvements
Note: Requires raw logs.
-- Parameterized lookback period using variables
DECLARE lookback_period INT64 DEFAULT 14;
DECLARE lookback_unit STRING DEFAULT 'DAY';
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
BEGIN
WITH
-- Base CTE to centralize table reference with parameterized lookback period and optional project filtering
glean_customer_event AS (
SELECT
DATE(timestamp) AS datepartition
, resource.labels.project_id AS project_id
, jsonPayload.User.UserId AS userid
, jsonPayload
, timestamp
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
timestamp >= CASE
WHEN lookback_unit = 'DAY' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY))
WHEN lookback_unit = 'WEEK' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period WEEK))
WHEN lookback_unit = 'MONTH' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period MONTH))
WHEN lookback_unit = 'HOUR' THEN TIMESTAMP(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL lookback_period HOUR))
ELSE TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY)) -- Default to DAY
END
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
,chat_events AS (
SELECT DISTINCT
m.`timestamp` AS message_time
, m.jsonPayload.User.Userid
, f.`timestamp` AS feedback_time
, f.jsonPayload.ChatFeedback.Event AS rating
, fc.jsonPayload.ChatFeedback.comments AS comments
, m.jsonPayload.Chat.Qtt AS run_id
FROM
glean_customer_event m
LEFT JOIN
glean_customer_event f
ON m.jsonPayload.Chat.ResponseMessageId = f.jsonPayload.ChatFeedback.MessageId
LEFT JOIN
-- For the same chat message we might receive both upvote/downvote and manual feedback. This is to collect comments from the user.
glean_customer_event fc
ON m.jsonPayload.Chat.ResponseMessageId = fc.jsonPayload.ChatFeedback.MessageId
WHERE
m.jsonPayload.Type = 'CHAT'
AND f.jsonPayload.Type = 'CHAT_FEEDBACK'
AND f.jsonPayload.ChatFeedback.Event IN ('UPVOTE', 'DOWNVOTE')
AND fc.jsonPayload.Type = 'CHAT_FEEDBACK'
AND fc.jsonPayload.ChatFeedback.Event = ('MANUAL_FEEDBACK')
)
,workflow_events AS (
SELECT DISTINCT
m.`timestamp` AS message_time
, m.jsonPayload.User.Userid
, f.`timestamp` AS feedback_time
, f.jsonPayload.ChatFeedback.Event AS rating
, fc.jsonPayload.ChatFeedback.comments AS comments
, m.jsonPayload.workflow.RunID AS run_id
FROM
glean_customer_event m
LEFT JOIN
glean_customer_event f
ON m.jsonPayload.Workflow.RunId = f.jsonPayload.ChatFeedback.RunID
LEFT JOIN
-- For the same chat message we might receive both upvote/downvote and manual feedback. This is to collect comments from the user.
glean_customer_event fc
ON m.jsonPayload.Workflow.RunId = fc.jsonPayload.ChatFeedback.RunID
WHERE
m.jsonPayload.Type = 'WORKFLOW'
AND f.jsonPayload.Type = 'CHAT_FEEDBACK'
AND f.jsonPayload.ChatFeedback.Event IN ('UPVOTE', 'DOWNVOTE')
AND fc.jsonPayload.Type = 'CHAT_FEEDBACK'
AND fc.jsonPayload.ChatFeedback.Event = ('MANUAL_FEEDBACK')
)
,workflowrun_events AS (
SELECT DISTINCT
m.`timestamp` AS message_time
, m.jsonPayload.User.Userid
, f.`timestamp` AS feedback_time
, f.jsonPayload.ChatFeedback.Event AS rating
, fc.jsonPayload.ChatFeedback.comments AS comments
, m.jsonPayload.workflowRun.RunID AS run_id
FROM
glean_customer_event m
LEFT JOIN
glean_customer_event f
ON m.jsonPayload.WorkflowRun.RunId = f.jsonPayload.ChatFeedback.RunID
LEFT JOIN
-- For the same chat message we might receive both upvote/downvote and manual feedback. This is to collect comments from the user.
glean_customer_event fc
ON m.jsonPayload.WorkflowRun.RunId = fc.jsonPayload.ChatFeedback.RunID
WHERE
m.jsonPayload.Type = 'WORKFLOW_RUN'
AND f.jsonPayload.Type = 'CHAT_FEEDBACK'
AND f.jsonPayload.ChatFeedback.Event IN ('UPVOTE', 'DOWNVOTE')
AND fc.jsonPayload.Type = 'CHAT_FEEDBACK'
AND fc.jsonPayload.ChatFeedback.Event = ('MANUAL_FEEDBACK')
)
SELECT *
FROM chat_events
UNION ALL
SELECT *
FROM workflow_events
UNION ALL
SELECT *
FROM workflowrun_events;
END
Output: All chat citation records from both CHAT_CITATIONS events and WORKFLOW_RUN citations
Grain: Individual citation level over configurable time period
Use Case: Extract all chat citations for analysis, including source documents, files, and people cited in chat responses across both legacy and modern workflow implementations
DECLARE lookback_period INT64 DEFAULT 7;
DECLARE lookback_unit STRING DEFAULT 'DAY';
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL -- Set to NULL to include all projects, or specify a project ID
DECLARE workflow_ids ARRAY<STRING> DEFAULT [
'DOC_CONTEXT_READER',
'ORIGINAL_MESSAGE_SEARCH',
'DIRECT_LLM_RESPONSE',
'REACT_TOOLS_2_HOPS',
'DEFAULT_CHAT',
'WORLD_MODE_V2',
'DEEP_RESEARCH_PYAGENT',
'DEEP_RESEARCH_PREVIEW'
];
WITH
-- Base CTE to centralize table reference with parameterized lookback period and optional project filtering
glean_customer_event AS (
SELECT
DATE(timestamp) AS datepartition
, resource.labels.project_id AS project_id
, jsonPayload.User.UserId AS userid
, jsonPayload
, timestamp
FROM
-- replace <glean_customer_event_table> with your firm's table name
<glean_customer_event_table>
WHERE
timestamp >= CASE
WHEN lookback_unit = 'DAY' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY))
WHEN lookback_unit = 'WEEK' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period WEEK))
WHEN lookback_unit = 'MONTH' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period MONTH))
WHEN lookback_unit = 'HOUR' THEN TIMESTAMP(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL lookback_period HOUR))
ELSE TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY)) -- Default to DAY
END
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
-- Legacy CHAT_CITATIONS events
, chat_citations_events AS (
SELECT
DATE(timestamp) as datepartition,
jsonPayload.user.userid,
jsonPayload.user.useremail,
jsonPayload.chatcitations.chatsessionid,
jsonPayload.chatcitations.workflowrunid,
citation.trackingtoken,
citation.sourcedocument.datasource,
citation.sourcedocument.doctype,
citation.sourcedocument.id AS source_document_id,
citation.sourcedocument.title AS source_document_title,
citation.sourcedocument.url AS source_document_url,
citation.sourcefile.id AS source_file_id,
citation.sourcefile.name AS source_file_name,
citation.sourceperson.id AS source_person_id,
citation.sourceperson.name AS source_person_name,
'CHAT_CITATIONS' AS event_source
FROM glean_customer_event,
UNNEST(jsonPayload.chatcitations.citations) AS citation
WHERE jsonPayload.Type = 'CHAT_CITATIONS'
)
-- WORKFLOW_RUN citations for chat workflows
, workflow_run_citations AS (
SELECT
DATE(timestamp) as datepartition,
jsonPayload.user.userid,
jsonPayload.user.useremail,
jsonPayload.workflowrun.chatsessionid,
jsonPayload.workflowrun.runid AS workflowrunid,
citation.trackingtoken,
citation.sourcedocument.datasource,
citation.sourcedocument.doctype,
citation.sourcedocument.id AS source_document_id,
citation.sourcedocument.title AS source_document_title,
citation.sourcedocument.url AS source_document_url,
citation.sourcefile.id AS source_file_id,
citation.sourcefile.name AS source_file_name,
citation.sourceperson.id AS source_person_id,
citation.sourceperson.name AS source_person_name,
'WORKFLOW_RUN' AS event_source
FROM glean_customer_event,
UNNEST(jsonPayload.workflowrun.workflowexecutions) AS workflow_execution,
UNNEST(jsonPayload.workflowrun.stepexecutions) AS step_execution,
UNNEST(step_execution.citations) AS citation
WHERE jsonPayload.Type = 'WORKFLOW_RUN'
AND jsonPayload.workflowrun.initiator = 'USER'
-- Filter for chat-related workflows
AND workflow_execution.workflowid IN UNNEST(workflow_ids)
)
-- Combine all chat citations
SELECT * FROM chat_citations_events
UNION DISTINCT
SELECT * FROM workflow_run_citations
ORDER BY datepartition DESC
Output: Complete chat conversations with user queries, Glean responses, and user metadata
Grain: Individual chat run level over configurable time period
Use Case: Analyze chat conversation patterns, response quality, and user engagement across departments and user segments
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY);
DECLARE end_date DATE DEFAULT CURRENT_DATE();
DECLARE chat_workflow_ids ARRAY<STRING> DEFAULT [
'DOC_CONTEXT_READER',
'ORIGINAL_MESSAGE_SEARCH',
'DIRECT_LLM_RESPONSE',
'REACT_TOOLS_2_HOPS',
'DEFAULT_CHAT',
'WORLD_MODE_V2',
'DEEP_RESEARCH_PYAGENT',
'DEEP_RESEARCH_PREVIEW'
];
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
<glean_customer_event_table>
WHERE
-- Optional project filtering - only filter if project_id is specified
(project_id IS NULL OR resource.labels.project_id = project_id)
AND timestamp BETWEEN TIMESTAMP(start_date) and TIMESTAMP(end_date)
)
, chat_usage as (
-- Workflow data + Workflow run data
SELECT
COALESCE(wf.datepartition, chat.datepartition) AS date_partition
, COALESCE(wf.run_id, chat.workflow_run_id) as run_id
, COALESCE(wf.useremail, chat.useremail) as user_email
, COALESCE(wf.department, chat.department) as department
FROM (
SELECT
DISTINCT jsonPayload.workflow.runid AS run_id
, jsonPayload.user.*
, DATE(timestamp) AS datepartition
FROM glean_customer_event
WHERE
jsonPayload.workflow.workflowid IN UNNEST(chat_workflow_ids)
AND jsonPayload.workflow.initiator = 'USER'
UNION ALL
SELECT
DISTINCT jsonPayload.workflowrun.runid AS run_id
, jsonPayload.user.*
, DATE(timestamp) AS datepartition
FROM glean_customer_event, UNNEST(jsonPayload.workflowrun.workflowexecutions) AS wfe
WHERE jsonPayload.type = 'WORKFLOW_RUN'
AND jsonPayload.workflowrun.initiator = 'USER'
AND wfe.workflowid IN UNNEST(chat_workflow_ids)
) wf
-- Join the unioned workflow data with chat data
FULL OUTER JOIN (
SELECT
DATE(timestamp) AS datepartition
, jsonPayload.user.*
, jsonPayload.chat.workflowrunid AS workflow_run_id
FROM glean_customer_event
WHERE jsonPayload.type = 'CHAT'
AND jsonPayload.chat.initiator = 'USER'
) chat
ON wf.run_id = chat.workflow_run_id
)
, workflow_conversation AS(
SELECT
jsonPayload.workflowconversation.workflowrunid as run_id
, MAX(CASE WHEN message.author='USER' THEN text END) as user_query
, MAX(CASE WHEN message.author='GLEAN' THEN text END) as response
FROM
glean_customer_event, UNNEST(jsonPayload.workflowconversation.messages) as message
WHERE
COALESCE(jsonPayload.workflowconversation.workflowrunid, '') != ''
GROUP BY 1
)
SELECT
chat_usage.* except(run_id)
, user_query
, response
, chat_usage.run_id
FROM chat_usage
LEFT JOIN workflow_conversation
USING(run_id)
ORDER by date_partition DESC
Output: Complete chat citation clicks log with user, datasource, and doc information
Grain: Individual chat run level over configurable time period
Use Case: Analyze chat citation click data
DECLARE lookback_period INT64 DEFAULT 25;
DECLARE lookback_unit STRING DEFAULT 'DAY';
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL -- Set to NULL to include all projects, or specify a project ID
WITH
-- Base CTE to centralize table reference with parameterized lookback period and optional project filtering
glean_customer_event AS (
SELECT
DATE(timestamp) AS datepartition
, resource.labels.project_id AS project_id
, jsonPayload.User.UserId AS userid
, jsonPayload
, timestamp
FROM
-- replace <glean_customer_event_table> with your firm's table name
<glean_customer_event_table>
WHERE
timestamp >= CASE
WHEN lookback_unit = 'DAY' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY))
WHEN lookback_unit = 'WEEK' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period WEEK))
WHEN lookback_unit = 'MONTH' THEN TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period MONTH))
WHEN lookback_unit = 'HOUR' THEN TIMESTAMP(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL lookback_period HOUR))
ELSE TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL lookback_period DAY)) -- Default to DAY
END
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
,ccc AS (
SELECT
project_id,
jsonPayload.user.userid AS user_id,
jsonPayload.chatcitationclick.datasource AS citation_datasource,
jsonPayload.chatcitationclick.trackingtoken AS citation_click_tracking_token,
timestamp
FROM glean_customer_event
WHERE jsonPayload.Type = 'CHAT_CITATION_CLICK'
),
wr AS (
SELECT
project_id,
jsonPayload.user.userid AS user_id,
jsonPayload.workflowrun.chatsessionid AS chat_session_id,
jsonPayload.workflowrun.runid AS run_id,
search_tracking_token
FROM glean_customer_event, UNNEST(jsonPayload.workflowrun.actionexecutions) AS ae,
UNNEST(ae.searchtrackingtokens) AS search_tracking_token
WHERE jsonPayload.Type = 'WORKFLOW_RUN'
),
cc AS (
SELECT
jsonPayload.chatcitations.workflowrunid AS run_id,
jsonPayload.chatcitations.chatsessionid AS chat_session_id,
c.trackingtoken AS citation_tracking_token,
c.sourcedocument.datasource AS cited_datasource,
c.sourcedocument.id AS doc_id,
c.sourcedocument.title AS doc_title, -- NULL in scrubbed logs
c.sourcedocument.url AS doc_url -- NULL in scrubbed logs
FROM glean_customer_event,
UNNEST(jsonPayload.chatcitations.citations) AS c
WHERE jsonPayload.Type = 'CHAT_CITATIONS'
)
SELECT
ccc.timestamp,
ccc.user_id,
ccc.citation_datasource,
wr.chat_session_id,
wr.run_id,
cc.doc_id,
cc.doc_title,
cc.doc_url
FROM ccc
LEFT JOIN wr
ON ccc.project_id = wr.project_id
AND ccc.user_id = wr.user_id
AND ccc.citation_click_tracking_token = wr.search_tracking_token
LEFT JOIN cc
ON wr.run_id = cc.run_id
AND wr.chat_session_id = cc.chat_session_id
AND ccc.citation_click_tracking_token = cc.citation_tracking_token
Agent Metrics
Agent Types Explained:
When viewing query results in this section, you'll see agents categorized by type:
-
AGENT: Standard conversational agents created and managed by users. These are the primary agents that end-users interact with directly. -
SUBAGENT: Internal helper agents that are called by other agents to perform specialized tasks. These run behind the scenes and are not directly invoked by users. Common examples include search agents, data retrieval agents, and tool execution agents. Note: In production environments, you may see a high volume of subagent runs as they are triggered by parent agents during workflow execution. -
AGENT_TEMPLATE: Pre-built agent configurations provided by Glean that users can clone, customize, and deploy as their own agents. These serve as starting points for common use cases.
Output: Agent-level metrics including total runs, first and last usage dates, and agent type
Grain: Per agent aggregation over configurable time period (default: 185 days)
Use Case: Track agent lifecycle from creation through usage, analyze agent popularity and engagement trends, identify stale agents
Agent Types Explained:
When viewing query results, you'll see agents categorized by type:
-
AGENT: Standard conversational agents created and managed by users. These are the primary agents that end-users interact with directly. -
SUBAGENT: Internal helper agents that are called by other agents to perform specialized tasks. These run behind the scenes and are not directly invoked by users. Common examples include search agents, data retrieval agents, and tool execution agents. Note: In production environments, you may see a high volume of subagent runs as they are triggered by parent agents during workflow execution. -
AGENT_TEMPLATE: Pre-built agent configurations provided by Glean that users can clone, customize, and deploy as their own agents. These serve as starting points for common use cases.
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
DECLARE lookback_days INT64 DEFAULT 185;
WITH glean_customer_event AS (
SELECT
resource,
DATE(DATE_TRUNC(timestamp, DAY)) AS datepartition,
jsonPayload,
timestamp,
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
timestamp >= TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL lookback_days DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
-- Get agent metadata from latest snapshots
, agent_info AS (
SELECT
resource.labels.project_id,
jsonPayload.productsnapshot.workflow.workflowid AS workflow_id,
ARRAY_AGG(jsonPayload.productsnapshot.workflow.name ORDER BY timestamp DESC LIMIT 1)[OFFSET(0)] AS name,
ARRAY_AGG(jsonPayload.productsnapshot.workflow.namespaceenum ORDER BY timestamp DESC LIMIT 1)[OFFSET(0)] AS workflow_type,
ARRAY_AGG(
PARSE_DATE('%Y-%m-%d', SUBSTR(jsonPayload.productsnapshot.workflow.createdat, 1, 10))
ORDER BY timestamp DESC LIMIT 1
)[OFFSET(0)] AS created_date,
ARRAY_AGG(jsonPayload.productsnapshot.workflow.createdby ORDER BY timestamp DESC LIMIT 1)[OFFSET(0)] AS createdby
FROM glean_customer_event
WHERE
jsonPayload.productsnapshot.workflow.namespaceenum IN ('AGENT', 'SUBAGENT', 'AGENT_TEMPLATE')
-- Filter out draft-only agents that were never published/deployed for actual use
AND jsonPayload.productsnapshot.workflow.isdraftonly = FALSE
GROUP BY 1, 2
)
-- Agent runs from WORKFLOW_RUN (must be in agent_info)
, agent_runs AS (
SELECT
DATE(DATE_TRUNC(timestamp, DAY)) AS datepartition,
resource.labels.project_id,
workflow_execution.workflowid AS workflow_id,
jsonpayload.workflowrun.runid AS run_id
FROM glean_customer_event, UNNEST(jsonpayload.workflowrun.workflowexecutions) AS workflow_execution
WHERE
jsonPayload.type = 'WORKFLOW_RUN'
AND workflow_execution.status = 'SUCCESS'
)
-- Filter agent runs to only those in agent_info
, agent_runs_filtered AS (
SELECT ar.*
FROM agent_runs ar
INNER JOIN agent_info ai ON ar.workflow_id = ai.workflow_id
)
-- App runs from CHAT (don't require agent_info)
, app_runs_from_chat AS (
SELECT
DATE(DATE_TRUNC(timestamp, DAY)) AS datepartition,
resource.labels.project_id,
jsonpayload.chat.applicationid AS workflow_id,
IF(
jsonpayload.chat.workflowrunid IS NOT NULL AND jsonpayload.chat.workflowrunid != '',
jsonpayload.chat.workflowrunid,
jsonpayload.chat.qtt
) AS run_id
FROM glean_customer_event
WHERE
jsonPayload.type = 'CHAT'
-- Filter for valid agent/app IDs: Glean assigns 16-character IDs to custom apps/agents
AND LENGTH(jsonpayload.chat.applicationid) = 16
)
-- App runs from WORKFLOW_RUN.applicationid (don't require agent_info)
, app_runs_from_workflow AS (
SELECT
DATE(DATE_TRUNC(timestamp, DAY)) AS datepartition,
resource.labels.project_id,
jsonPayload.workflowrun.applicationid AS workflow_id,
jsonpayload.workflowrun.runid AS run_id
FROM glean_customer_event, UNNEST(jsonpayload.workflowrun.workflowexecutions) AS workflow_execution
WHERE
jsonPayload.type = 'WORKFLOW_RUN'
AND jsonPayload.workflowrun.applicationid IS NOT NULL
AND jsonPayload.workflowrun.applicationid != ''
AND workflow_execution.status = 'SUCCESS'
GROUP BY 1, 2, 3, 4
)
-- Combine all runs (both agent and app)
, all_runs AS (
SELECT * FROM agent_runs_filtered
UNION ALL
SELECT * FROM app_runs_from_chat
UNION ALL
SELECT * FROM app_runs_from_workflow
)
-- Add creation dates as activity (only for agents with metadata)
, all_activity AS (
SELECT datepartition, project_id, workflow_id, run_id
FROM all_runs
UNION ALL
SELECT
created_date AS datepartition,
project_id,
workflow_id,
CONCAT('creation_', workflow_id) AS run_id
FROM agent_info
WHERE
created_date >= CURRENT_DATE - INTERVAL lookback_days DAY
AND created_date <= CURRENT_DATE - INTERVAL '1' DAY
AND created_date != DATE('0001-01-01')
AND createdby IS NOT NULL
)
-- Final aggregation with LEFT JOIN to include apps without metadata
SELECT
aa.project_id,
aa.workflow_id AS agent_id,
COALESCE(ai.name, aa.workflow_id) AS agent_name,
ai.workflow_type AS agent_type,
COUNT(DISTINCT CASE WHEN aa.run_id NOT LIKE 'creation_%' THEN aa.run_id END) AS num_agent_runs,
MAX(aa.datepartition) AS last_usage_date,
MIN(aa.datepartition) AS first_usage_date
FROM all_activity aa
LEFT JOIN agent_info ai ON aa.workflow_id = ai.workflow_id
GROUP BY 1, 2, 3, 4
ORDER BY 5 DESC;
Output: Detailed agent creation records with creator, permissions, and metadata
Grain: Individual agent level for agents created within the lookback period (default: 185 days)
Use Case: Track who is creating agents, analyze permission settings, identify agent creation trends, and monitor agent lifecycle from creation
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
DECLARE lookback_days INT64 DEFAULT 185;
WITH glean_customer_event AS (
SELECT
resource,
DATE(DATE_TRUNC(timestamp, DAY)) AS datepartition,
jsonPayload,
timestamp,
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
timestamp >= TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL lookback_days DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
-- Get latest workflow snapshot info (filter to agents only)
, workflows_last_snapshot_date AS (
SELECT
resource.labels.project_id,
jsonPayload.productsnapshot.workflow.workflowid AS agent_id,
MAX(DATE(DATE_TRUNC(timestamp, DAY))) AS latest_snapshot_date
FROM glean_customer_event
WHERE
jsonPayload.type = 'PRODUCT_SNAPSHOT'
AND jsonPayload.productsnapshot.type = 'WORKFLOW'
AND jsonPayload.productsnapshot.workflow.namespaceenum IN ('AGENT', 'SUBAGENT', 'AGENT_TEMPLATE')
-- Filter out draft-only agents that were never published/deployed for actual use
AND jsonPayload.productsnapshot.workflow.isdraftonly = FALSE
GROUP BY 1, 2
)
-- Extract all workflow snapshot data with parsed dates (agents only)
, all_workflow_snapshots AS (
SELECT
resource.labels.project_id,
jsonPayload.productsnapshot.workflow.workflowid AS agent_id,
DATE(DATE_TRUNC(timestamp, DAY)) AS snapshot_date,
jsonPayload.productsnapshot.workflow.name AS name,
-- Parse dates once to reuse
PARSE_DATE('%Y-%m-%d', SUBSTR(jsonPayload.productsnapshot.workflow.createdat, 1, 10)) AS created_date,
jsonPayload.productsnapshot.workflow.createdby AS createdby,
jsonPayload.productsnapshot.workflow.trigger.type AS trigger_type,
jsonPayload.productsnapshot.workflow.namespaceenum AS workflow_type,
permissions.role AS role,
permissions.id AS permission_id,
permissions.type AS permission_type
FROM glean_customer_event, UNNEST(jsonPayload.productsnapshot.workflow.roles) AS permissions
WHERE
jsonPayload.type = 'PRODUCT_SNAPSHOT'
AND jsonPayload.productsnapshot.type = 'WORKFLOW'
AND jsonPayload.productsnapshot.workflow.namespaceenum IN ('AGENT', 'SUBAGENT', 'AGENT_TEMPLATE')
)
-- Get most recent workflow metadata
, all_workflows_info AS (
SELECT
ws.project_id,
ws.agent_id,
ws.name,
ws.created_date,
ws.createdby,
ws.trigger_type,
ws.workflow_type,
ws.role,
ws.permission_id,
ws.permission_type,
-- Mark as deleted if snapshot is older than yesterday
ws.snapshot_date < CURRENT_DATE - INTERVAL '1' DAY AS is_deleted
FROM all_workflow_snapshots ws
INNER JOIN workflows_last_snapshot_date lsd
ON ws.agent_id = lsd.agent_id
AND ws.snapshot_date = lsd.latest_snapshot_date
)
-- Aggregate workflow metadata with permission levels
, all_workflows AS (
SELECT DISTINCT
created_date,
project_id,
-- Handle system-created workflows (date = 0001-01-01)
CASE
WHEN created_date = DATE('0001-01-01') THEN NULL
ELSE createdby
END AS creator_id,
agent_id,
name,
trigger_type,
workflow_type,
created_date = DATE('0001-01-01') AS is_system_created_workflow,
is_deleted,
-- Determine viewer audience based on permissions
MAX(CASE
WHEN role IN ('VIEWER', 'EDITOR') AND permission_type = 'ALL' THEN '4) All users'
WHEN role IN ('VIEWER', 'EDITOR') AND permission_type = 'DEPARTMENT' THEN '3) Users in select departments'
WHEN role IN ('VIEWER', 'EDITOR') AND permission_type = 'USER' THEN '2) Select users'
ELSE '1) Owner only'
END) AS viewer_audience,
-- Determine editor audience
MAX(CASE
WHEN role = 'EDITOR' AND permission_type = 'ALL' THEN '4) All users'
WHEN role = 'EDITOR' AND permission_type = 'DEPARTMENT' THEN '3) Users in select departments'
WHEN role = 'EDITOR' AND permission_type = 'USER' THEN '2) Select users'
ELSE '1) Owner only'
END) AS editor_audience
FROM all_workflows_info
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
)
-- Agent creation metrics
SELECT
created_date,
creator_id,
project_id,
agent_id,
name,
workflow_type AS agent_type,
trigger_type,
viewer_audience,
editor_audience,
is_deleted
FROM all_workflows
WHERE
workflow_type IN ('AGENT', 'SUBAGENT', 'AGENT_TEMPLATE')
-- Exclude system-created workflows
AND is_system_created_workflow = FALSE
AND creator_id IS NOT NULL
-- Only include creations within our time window
AND created_date >= CURRENT_DATE - INTERVAL lookback_days DAY
AND created_date <= CURRENT_DATE - INTERVAL '1' DAY
ORDER BY
created_date DESC,
creator_id;
Output: Agent feedback data including upvotes, downvotes, and sentiment analysis per user and agent
Grain: Per user per agent aggregation over configurable time period (default: 28 days)
Use Case: Track agent response quality through user feedback, identify poorly performing agents, measure user satisfaction trends
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
DECLARE lookback_days INT64 DEFAULT 28; -- Number of days to look back (default: 28 days)
WITH glean_customer_event AS (
SELECT
resource,
DATE(DATE_TRUNC(timestamp, DAY)) AS datepartition,
jsonPayload,
timestamp,
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
timestamp >= TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL lookback_days DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
SELECT
resource,
DATE(DATE_TRUNC(timestamp, DAY)) AS datepartition,
jsonPayload,
timestamp,
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
timestamp >= TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL lookback_days DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
-- Get latest workflow snapshot info (filter to agents only)
, workflows_last_snapshot_date AS (
SELECT
resource.labels.project_id,
jsonPayload.productsnapshot.workflow.workflowid AS agent_id,
MAX(DATE(DATE_TRUNC(timestamp, DAY))) AS latest_snapshot_date
FROM glean_customer_event
WHERE
jsonPayload.productsnapshot.workflow.namespaceenum IN ('AGENT', 'SUBAGENT', 'AGENT_TEMPLATE')
-- Filter out draft-only agents that were never published/deployed for actual use
AND jsonPayload.productsnapshot.workflow.isdraftonly = FALSE
GROUP BY 1, 2
)
-- Extract workflow snapshot data (agents only)
, all_workflow_snapshots AS (
SELECT
resource.labels.project_id,
jsonPayload.productsnapshot.workflow.workflowid AS agent_id,
DATE(DATE_TRUNC(timestamp, DAY)) AS snapshot_date,
jsonPayload.productsnapshot.workflow.name AS name,
jsonPayload.productsnapshot.workflow.namespaceenum AS workflow_type
FROM glean_customer_event
WHERE
jsonPayload.productsnapshot.workflow.namespaceenum IN ('AGENT', 'SUBAGENT', 'AGENT_TEMPLATE')
-- Filter out draft-only agents that were never published/deployed for actual use
AND jsonPayload.productsnapshot.workflow.isdraftonly = FALSE
)
-- Get most recent agent metadata
, agent_info AS (
SELECT
ws.project_id,
ws.agent_id,
ws.name,
ws.workflow_type
FROM all_workflow_snapshots ws
INNER JOIN workflows_last_snapshot_date lsd
ON ws.agent_id = lsd.agent_id
AND ws.snapshot_date = lsd.latest_snapshot_date
)
-- Agent feedback from CHAT_FEEDBACK events
, agent_feedback_raw AS (
SELECT
gce.jsonPayload.chatfeedback.runid AS run_id,
gce.jsonPayload.chatfeedback.agentid AS agent_id,
gce.jsonPayload.user.userid AS user_id,
gce.resource.labels.project_id AS project_id,
MAX(ai.name) AS agent_name,
MAX(ai.workflow_type) AS agent_type,
-- Take latest vote, disregard manual feedback
MAX_BY(gce.jsonPayload.chatfeedback.event, gce.timestamp) AS vote
FROM agent_info ai
INNER JOIN glean_customer_event gce
ON gce.jsonPayload.chatfeedback.agentid = ai.agent_id
WHERE
gce.jsonPayload.type = 'CHAT_FEEDBACK'
AND gce.jsonPayload.chatfeedback.event IN ('UPVOTE', 'DOWNVOTE')
GROUP BY 1, 2, 3, 4
)
-- Aggregate feedback by date, agent, and user
, agent_feedback AS (
SELECT
agent_id,
user_id,
project_id,
agent_name,
agent_type,
SUM(CASE WHEN vote = 'UPVOTE' THEN 1 ELSE 0 END) AS num_upvotes,
SUM(CASE WHEN vote = 'DOWNVOTE' THEN 1 ELSE 0 END) AS num_downvotes,
COUNT(DISTINCT run_id) AS num_feedback_instances
FROM agent_feedback_raw
GROUP BY 1, 2, 3, 4, 5
)
-- Final output with feedback metrics
SELECT
project_id,
agent_id,
agent_name,
agent_type,
user_id,
num_upvotes,
num_downvotes,
num_feedback_instances,
-- Calculate net sentiment
num_upvotes - num_downvotes AS net_sentiment,
-- Calculate upvote rate
CASE
WHEN (num_upvotes + num_downvotes) > 0
THEN CAST(num_upvotes AS FLOAT64) / (num_upvotes + num_downvotes)
ELSE NULL
END AS upvote_rate
FROM agent_feedback
WHERE
-- Only include agents with actual feedback
(num_upvotes + num_downvotes) > 0
ORDER BY
num_feedback_instances DESC;
Action Metric
Output: Workflow runs that executed web search actions with action details
Grain: Individual workflow run level with associated action information
Use Case: Track usage of web search actions across agent and assistant workflows, analyze which workflows leverage external web search capabilities
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
DECLARE lookback_days INT64 DEFAULT 30;
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
-- change date ranges as needed
timestamp >= TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL lookback_days DAY))
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, web_search_actions AS(
SELECT
jsonPayload.productsnapshot.actioninstance.*
FROM glean_customer_event
WHERE
jsonPayload.productsnapshot.actioninstance.actiontype LIKE '%WEB_SEARCH'
OR LOWER(jsonPayload.productsnapshot.actioninstance.actionpackname) = 'microsoftbing'
)
-- For actions in assistant workflows it is not required to enable the action therefore
-- actionid is used as actioninstanceid
-- For agent workflows the action needs to be enabled therefore we use action_instance_id
, web_search_action_ids AS(
SELECT
id, actionname
FROM web_search_actions
UNION DISTINCT
SELECT
actionid as id, actionname
FROM web_search_actions
)
SELECT
DISTINCT
jsonPayload.workflowrun.runid
, wsa.actionname
FROM
glean_customer_event, UNNEST(jsonPayload.workflowrun.actionexecutions) ae
JOIN
web_search_action_ids wsa
ON
ae.actioninstanceid = wsa.id
GleanBot User Level Activity
Output: Proactive GleanBot messages with user interaction details and engagement metrics
Grain: Individual message level with user interaction data
Use Case: Analyze bot effectiveness, user engagement patterns, and proactive messaging success rates
DECLARE project_id STRING DEFAULT NULL; -- Replace with your project ID or leave as NULL
WITH glean_customer_event AS (
SELECT *
FROM
-- replace <glean_customer_event_table> with your firm's table name
`<glean_customer_event_table>`
WHERE
timestamp > TIMESTAMP(CURRENT_DATE() - INTERVAL 1 DAY)
-- Optional project filtering - only filter if project_id is specified
AND (project_id IS NULL OR resource.labels.project_id = project_id)
)
, gleanbot_activity AS (
SELECT
jsonPayload.user.userid AS user_id
,jsonPayload.gleanbotactivity.eventtrackingtoken AS event_tracking_token
,jsonPayload.gleanbotactivity.stt AS stt
,DATE(timestamp) AS datepartition
,jsonPayload.gleanbotactivity.latenciesmillismap.proactive_message AS latency_proactive_message
,jsonPayload.gleanbotactivity.eventtype AS event_type_verbose
,jsonPayload.gleanbotactivity.responseevents AS response_events_verbose
,jsonPayload.gleanbotactivity.workflowexecutionpoints AS workflow_execution_points_verbose
,(jsonPayload.gleanbotactivity.applicationid is NOT NULL AND LENGTH(jsonPayload.gleanbotactivity.applicationid) != 0) AS is_app_session
FROM
glean_customer_event
WHERE
jsonPayload.type = 'GLEAN_BOT_ACTIVITY'
)
, message_data AS (
SELECT *
FROM gleanbot_activity
WHERE
(
-- Identify messages where proactive workflow started
'PROACTIVE_WORKFLOW_START' IN UNNEST(workflow_execution_points_verbose)
-- Identify messages where proactive workflow for assistant type started
OR 'PROACTIVE_CHAT_WORKFLOW_START' IN UNNEST(workflow_execution_points_verbose)
-- Identify messages where proactive workflow for search type started
OR 'PROACTIVE_SEARCH_WORKFLOW_START' IN UNNEST(workflow_execution_points_verbose)
-- All messages that were not DMs and did not mention @Glean or \glean fall in this category
OR event_type_verbose IN (
'NON_TAGGED_MESSAGE'
-- All events for which proactive thread summarizer workflow is initiated
, 'PROACTIVE_DISCUSSION_SUMMARIZER'
-- All events where proactive digest was clicked
'VIEW_DIGEST_CLICK'
-- All events where digest was subscribed to and thus proactively sent over DM
, 'DAILY_DIGEST_REMINDER'
)
)
-- Remove the events where proactive workflow cannot be triggered. Eg: Invalid Channel
AND 'PROACTIVE_BOT_DISABLED_FAILURE' NOT IN UNNEST(workflow_execution_points_verbose)
-- Remove the events which represent messages sent by other bots, so we don't converse with slack automations
AND 'DROP_BOT_MESSAGE' NOT IN UNNEST(workflow_execution_points_verbose)
AND 'DROP_EXTERNAL_CHANNEL_MESSAGE' NOT IN UNNEST(workflow_execution_points_verbose)
)
, interaction_data AS (
SELECT *
FROM gleanbot_activity
WHERE
event_type_verbose IN (
-- All events where a user clicked on show sources modal
'SHOW_SOURCES',
-- All events for which helpful/not helpful buttons click was initiated
'SHARE_HELPFULNESS',
-- All events for which sharing workflow was started
'SHARE_CLICK',
-- All events for which manual feedback workflow was started
'SUBMIT_FEEDBACK',
-- All events for which dismiss suggestion workflow was initiated
'DISMISS_SUGGESTION'
)
)
SELECT
message_data.datepartition
-- ID of the user who received the proactive message
,message_data.user_id
-- Unique identifier for the proactive message
,message_data.event_tracking_token
-- Whether the user was in an active app session.
,message_data.is_app_session
-- Latency in milliseconds for the proactive message delivery
,message_data.latency_proactive_message
-- Tracking token of the user's interaction with the proactive message
,interaction_data.event_tracking_token AS interaction_event_tracking_token
-- ID of the user who interacted with the proactive message
,interaction_data.user_id AS interacting_user_id
-- Whether the workflow was triggered (via non-tagged or summarizer event)
,(CASE
WHEN message_data.event_type_verbose = 'NON_TAGGED_MESSAGE' THEN TRUE
WHEN message_data.event_type_verbose = 'PROACTIVE_DISCUSSION_SUMMARIZER' THEN TRUE
ELSE FALSE
END) AS workflow_started
-- Whether the message was classified as a question. Commented out for now
-- ,IF(question_detection_data.isquestion = TRUE, TRUE, FALSE) AS question_detected
-- Bot responded: includes proactive message, or summarization thread/DM
,(CASE
WHEN 'DISCUSSION_SUMMARY_THREAD_SUMMARY_SENT' IN UNNEST(message_data.response_events_verbose) THEN TRUE
WHEN 'DISCUSSION_SUMMARY_DM_RESPONSE_SENT' IN UNNEST(message_data.response_events_verbose) THEN TRUE
WHEN 'PROACTIVE_MESSAGE' IN UNNEST(message_data.response_events_verbose) THEN TRUE
ELSE FALSE
END) AS bot_responded
-- Bot response was explicitly triggered by user (e.g., digest opened or summary clicked)
,(CASE
WHEN 'DISCUSSION_SUMMARY_THREAD_SUMMARY_SENT' IN UNNEST(message_data.response_events_verbose) THEN TRUE
WHEN 'DISCUSSION_SUMMARY_DM_RESPONSE_SENT' IN UNNEST(message_data.response_events_verbose) THEN TRUE
WHEN 'VIEW_DIGEST' IN UNNEST(message_data.response_events_verbose) THEN TRUE
ELSE FALSE
END) AS bot_responded_on_users_request
-- Response was shared by the user
,IF(interaction_data.event_type_verbose = 'SHARE_CLICK', TRUE, FALSE) AS response_shared
-- Response was dismissed or downvoted
,(CASE
WHEN 'SUBMIT_FEEDBACK_WORKFLOW_DOWNVOTE' IN UNNEST(interaction_data.workflow_execution_points_verbose) THEN TRUE
WHEN interaction_data.event_type_verbose = 'DISMISS_SUGGESTION' THEN TRUE
ELSE FALSE
END) AS response_downvoted
-- Citations shown to the user
,IF(interaction_data.event_type_verbose = 'SHOW_SOURCES', TRUE, FALSE) AS citations_shown
-- Response was marked helpful
,IF('SHARE_HELPFUL' IN UNNEST(interaction_data.response_events_verbose), TRUE, FALSE) AS voted_helpful
-- Response was marked not helpful
,IF('SHARE_NOT_HELPFUL' IN UNNEST(interaction_data.response_events_verbose), TRUE, FALSE) AS voted_not_helpful
-- Daily digest proactively sent to the user
,IF('DAILY_DIGEST_REMINDER_SENT' IN UNNEST(message_data.response_events_verbose), TRUE, FALSE) AS digest_sent
-- User consumed/viewed the digest
,IF('VIEW_DIGEST' IN UNNEST(message_data.response_events_verbose), TRUE, FALSE) AS digest_consumed
-- Bot sent a proactive thread or DM summary
,(CASE
WHEN 'DISCUSSION_SUMMARY_THREAD_SUMMARY_SENT' IN UNNEST(message_data.response_events_verbose) THEN TRUE
WHEN 'DISCUSSION_SUMMARY_DM_RESPONSE_SENT' IN UNNEST(message_data.response_events_verbose) THEN TRUE
ELSE FALSE
END) AS proactive_summary_sent
FROM message_data
LEFT JOIN interaction_data
ON message_data.event_tracking_token = interaction_data.stt