Analyze Smart Snippets Metrics

This is for:

In this article

How Does Smart Snippets Affect Analytics?
Step 1: Verify Prerequisites
Step 2: Access the Reader Account
Step 3: Initialize Data Analysis
Step 4: Run Analysis Queries
Step 5: Review Results
Reference

Smart Snippets is a Coveo Machine Learning (Coveo ML) feature that allows users to quickly obtain relevant answers to their queries. When a query is sent, a snippet of the most relevant result is displayed directly on the results page and therefore, the user avoids having to open links on the page. Furthermore, this feature provides additional questions and topics that users can click to find further information related to their query without ever leaving the results page.

How Does Smart Snippets Affect Analytics?

Smart Snippets increases the speed and efficiency in which users obtain query results and consequently affects metrics such as Click Event Count, Visit Clickthrough (%), since the clickthrough rate may decrease if users found what they were looking for directly on the search result page.

Smart Snippets uses server-side search impressions and client-side custom events, and both are required for an accurate rendering of UA data.

Search impressions logged server side by the Search API provide data related to the volume of snippets displayed on a search page as well as the affected click metrics. You must use the Snowflake reader account by following the steps documented below to access this data.

Custom events are logged client side (out of the box in the Coveo JavaScript Search Framework or manually via the Usage Analytics Write API) and provide data related to interactions with a snippet. For example, when a user clicks Show More, a click event is logged by Coveo UA.

To obtain data on custom events, you must create a dashboard on the Reports (platform-ca | platform-eu | platform-au) page of the Coveo Administration Console using the Smart Snippets Report template.

Step 1: Verify Prerequisites

To get started, ensure that you have the following:

Access to the Snowflake reader account.
JSUI version v2.10081 (or higher).
Two custom dimensions created for feedback reasons and details in the Coveo Administration Console. For example:
Required privileges for creating reports in the Coveo Administration Console.

Step 2: Access the Reader Account

Access the reader account from the Snowflake Reader Account tab of the Raw Data (platform-ca | platform-eu | platform-au) page in the Coveo Administration Console. Once you’ve logged in, you’ll be able to run the applicable SQL codes directly in the reader account’s SQL editor.

Step 3: Initialize Data Analysis

To get started, you must run the following two SQL codes which initialize the data analysis:

The first initialization code must be run once at the beginning of each session prior to running further data analysis SQL queries featured in Step 4.
The second initialization code contains a set of variables that determine the type of snippet that should be included in the calculations when running further data analysis queries in Step 4. Depending on the snippet type you want to include, you must uncomment the applicable variables.

First Initialization Code

-- 1. RUN THIS SETUP FIRST
-- Configure Variables (date must be in format YYYY-MM-DD)
SET OrgID = '<ORG_ID>';
SET PipelineID = '<PIPELINE_ID>';
SET Start_Date = '<START_DATE>';
SET End_Date = '<END_DATE>';

SET searches_table = '"COVEO"."ua"."searches_shared"';
SET all_events_table = '"COVEO"."ua"."all_events_shared"';
SET search_impressions_table = '"COVEO"."ua"."search_impressions_shared"';
SET click_table = '"COVEO"."ua"."clicks_shared"';

Where you replace:

<ORG_ID> with the applicable organization ID.
<PIPELINE_ID> with either a query pipeline or a search hub name associated with a Smart Snippet model.
<START_DATE> with the starting value for the date range, formatted as YYYY/MM/DD.
<END_DATE> with the ending value for the date range, formatted as YYYY/MM/DD.

Second Initialization Code

-- RUN THESE VARIABLES (used in later calculations)
SET total_visits = (
select count (distinct "visit_id")
from table($all_events_table)
where "account_id" = $OrgID
and "query_pipeline" = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")> 0);

SET total_search_events = (
select count (distinct "search_id")
from table($all_events_table)
where "event_type" = 'Search'
and "account_id" = $OrgID
and "query_pipeline" = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")> 0
);

SET count_of_search_events_with_snippet = (
select count (distinct si."search_id")
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date

-- SELECT THE SNIPPET TYPE YOU WANT TO INCLUDE IN THE CALCULATION BY UNCOMMENTING THE APPLICABLE OPTION

-- A. Main Snippet OR People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)

-- B. Main Snippet AND People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") > 2)

-- C. Main Snippet only
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") <= 2)

-- D. People Also Ask only
--and (len(si."search_impression":"result":"answers"[0].question) <= 2 and len(si."search_impression":"result":"related_questions") > 2)

and len("query_expression")> 0);

SET count_of_visits_with_snippet = (
select count (distinct s."visit_id")
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date

-- SELECT THE SNIPPET TYPE TO MATCH THE PREVIOUS SELECTION

-- A. Main Snippet OR People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)

-- B. Main Snippet AND People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") > 2)

-- C. Main Snippet only
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") <= 2)

-- D. People Also Ask only
--and (len(si."search_impression":"result":"answers"[0].question) <= 2 and len(si."search_impression":"result":"related_questions") > 2)

and len("query_expression")> 0);
SET count_of_search_events_without_snippet = (
select $total_search_events - $count_of_search_events_with_snippet)

where you uncomment option A, B, C, or D, here:

-- SELECT THE SNIPPET TYPE YOU WANT TO INCLUDE IN THE CALCULATION BY UNCOMMENTING THE APPLICABLE OPTION

-- A. Main Snippet OR People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)

-- B. Main Snippet AND People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") > 2)

-- C. Main Snippet only
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") <= 2)

-- D. People Also Ask only
--and (len(si."search_impression":"result":"answers"[0].question) <= 2 and len(si."search_impression":"result":"related_questions") > 2)

and also uncomment the corresponding option here:

-- SELECT THE SNIPPET TYPE TO MATCH THE PREVIOUS SELECTION

-- A. Main Snippet OR People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)

-- B. Main Snippet AND People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") > 2)

-- C. Main Snippet only
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") <= 2)

-- D. People Also Ask only
--and (len(si."search_impression":"result":"answers"[0].question) <= 2 and len(si."search_impression":"result":"related_questions") > 2)

Notes

You’ll need to rerun the second initialization code prior to running certain analysis queries where you want to change the snippet type.
To add both initilization codes and the analysis queries to the reader account in one shot, see the reference section at the end of the article.

Step 4: Run Analysis Queries

Once you’ve completed the initialization, you can now run the following SQL codes to obtain data which will help you understand when and how Smart Snippets impact your search metrics. Remember to change the variables (i.e., A, B, C, D) and rerun the second initialization code. After each variable change, rerun the queries to obtain the breakdown per snippet type.

Leading practice

As you run each query, we recommend that you use this companion spreadsheet to enter the results and create your own report. For example:

Search Events

Search Volume in Search Events

Run the following query to obtain the total number of search events in the pipeline.

-- SEARCH VOLUME
-- Total Search Events and Total Unique Visits in the pipeline
select $total_search_events as total_search_events

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Search Events with Snippets

Run the following query to obtain the total number of search events that generated snippets.

-- SNIPPET VOLUME
-- Search Events with Snippets
select $count_of_search_events_with_snippet as total_with_snippet,
$total_search_events as total_search_events,
($count_of_search_events_with_snippet / $total_search_events) * 100 as percentage
-- Change the variables (B, C, D) in the setup and rerun this query to get the breakdown per snippet type

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Snippets Engagement in Search Events

Run the following query to obtain the total number of snippet interactions in search events.

-- SNIPPETS ENGAGEMENT
-- Snippet engagement
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('expandSmartSnippet', 'expandSmartSnippetSuggestion','likeSmartSnippet', 'dislikeSmartSnippet') or "cause2" in ('openSmartSnippetSource', 'openSmartSnippetSuggestionSource'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Note

The Engagement column in the results table of the Snowflake console appears for all interaction types, including expansions, clicks, thumbs-up, and thumbs-down.

Main Snippets Expansions in Search Events

Run the following query to obtain the total of expansions on the primary snippet in search events.

-- MAIN SNIPPET EXPANSIONS
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where  "event_type" IN ('smartSnippet', 'Click')
and ("event_value" in ('expandSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

FrequentSnippetsExpansionsinSearchEvents

Main Snippet Clicks in Search Events

Run the following query to obtain the total of clicks on the secondary snippets in search events.

-- MAIN SNIPPET CLICKS
select count(distinct "click_id") as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where  "event_type" = 'Click'
and "cause2" = 'openSmartSnippetSource'
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Total "Was this useful?"

Run the following query to obtain the total number of "Was this useful?" feedback.

-- TOTAL "Was this useful?"
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as Total_Feedback,
$count_of_search_events_with_snippet as search_events_with_snippet, (Total_Feedback / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('likeSmartSnippet', 'dislikeSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Total Thumbs-Up

Run the following query to obtain the total number of thumbs-up received.

-- TOTAL 👍
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('likeSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Total Thumbs-Down

Run the following query to obtain the total number of thumbs-down received.

-- TOTAL 👎
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('dislikeSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Compare Click Metrics Performances

Run the following query to view click metrics such as click volume, clickthrough rate (CTR), and click rank, and also to obtain a comparison of CTR and click ranks for when a snippet is displayed as well as when it isn’t displayed.

-- CLICK METRICS
-- Compare click metrics performances
select 'with_snippet' as category, avg("click_rank") as Avg_click_rank, count(distinct "click_id") as clicks, $count_of_search_events_with_snippet as search_events,
(count(distinct "search_id") / search_events) * 100 as CTR
from table($click_table)
where "cause" in ('openSmartSnippetSource', 'openSmartSnippetSuggestionSource', 'documentOpen')
and "account_id" = $OrgID
and "query_pipeline" = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and "search_id" in (
select distinct si."search_id"
from table($search_impressions_table) si
inner join
(select "account_id","search_id","query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")> 0) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date
and (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2))
UNION ALL
select 'without_snippet' as category, avg("click_rank") as Avg_click_rank, count(distinct "click_id") as clicks, $count_of_search_events_without_snippet as search_events,
(count(distinct "search_id") / search_events) * 100 as CTR
from table($click_table)
where "account_id" = $OrgID
and "query_pipeline" = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and "search_id" in (select distinct si."search_id" from table($search_impressions_table) si
inner join
(select "account_id","search_id","query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")> 0) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date
and (len(ifnull(si."search_impression":"result":"answers"[0].question, 0)) <= 2 and len(ifnull(si."search_impression":"result":"related_questions", 0)) <= 2))
UNION ALL
select 'overall' as category, avg("click_rank") as Avg_click_rank, count(distinct "click_id") as clicks, $total_search_events as search_events,
(count(distinct "search_id") / search_events) * 100 as CTR
from table($click_table)
where "account_id" = $OrgID
and trim("query_pipeline", '"') = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Snippets Displayed in User Queries

Run the following query to view which queries displayed snippets and to view the snippets themselves.

-- QUALITATIVE METRICS
-- Which queries displayed a snippet and what is the snippet?
with base_table_cte as
(select distinct
si."search_id" as search_id,
trim(s."query_expression", '"') as user_query,
case when (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)
then True else False end as search_with_snippet,
si."search_impression":"result":"answers"[0].question as snippet,
si."search_impression":"result":"answers"[0].permanent_id as permanent_id
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date
and len("query_expression")> 0)
select user_query, count(user_query) as count_of_user_query, mode(snippet) as snippet_title, mode(permanent_id) as permanent_id
from base_table_cte
where search_with_snippet = True and len(snippet)>0
group by 1
order by 2 desc

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Total Snippets in User Queries

Run the following query to view which queries displayed snippet and to view the list of all the displayed snippets.

-- Which queries displayed a snippet and what is the list of all snippets displayed?
with base_table_cte as
(select distinct
si."search_id" as search_id,
trim(s."query_expression", '"') as user_query,
case when (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)
then True else False end as search_with_snippet,
si."search_impression":"result":"answers"[0].question as snippet_title,
si."search_impression":"result":"answers"[0].permanent_id as permanent_id
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date
and len("query_expression")> 0)
select user_query, snippet_title, permanent_id, count(snippet_title) as count_of_snippet
from base_table_cte
where search_with_snippet = True and len(snippet_title)>0
group by 1,2, 3
order by 1,3 desc

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

User Queries Without Snippets

Run the following query to view which common queries don’t display snippets.

-- Which popular queries don’t have a snippet? (so that I can work on my content)
with base_table_cte as
(select distinct
si."search_id" as search_id,
trim(s."query_expression", '"') as user_query,
case when (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)
then True else False end as search_with_snippet
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date)
select user_query, count(user_query) as count_of_user_query, sum(case when search_with_snippet = True then 1 else 0 end) as count_with_snippet
from base_table_cte
where len(user_query)>0
group by 1
having count_with_snippet = 0
order by 2 desc
limit 200

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Unique Visits

Leading practice

The calculations in the following queries are the same from the queries listed in the previous section, with the one difference being that they use unique visits instead of search events. Run these queries if you wish to obtain the numbers for unique visits.

Search Volume in Unique Visits

Run the following query to obtain the total number of unique visits in the pipeline.

-- SEARCH VOLUME
-- Total Unique Visits in the pipeline
select $total_visits as total_unique_visits

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Unique Visits With Snippets

Run the following query to obtain the total number of unique visits where snippets were generated.

-- SNIPPET VOLUME
-- Unique visits with snippets
select $count_of_visits_with_snippet as visits_with_snippet,
$total_visits as total_visits,
($count_of_visits_with_snippet / $total_visits) * 100 as percentage

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Snippets Engagement in Unique Visits

Run the following query to obtain the total number of snippet engagement in unique visits.

-- SNIPPETS ENGAGEMENT
-- Snippet engagement (all)
select count(distinct "visit_id") as engagement,
$count_of_visits_with_snippet as visits_with_snippet, (engagement / $count_of_visits_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('expandSmartSnippet', 'expandSmartSnippetSuggestion','likeSmartSnippet', 'dislikeSmartSnippet') or "cause2" in ('openSmartSnippetSource', 'openSmartSnippetSuggestionSource'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Main Snippet Expansions in Unique Visits

Run the following query to obtain the total of expansions on the primary snippet in in unique visits.

-- MAIN SNIPPET EXPANSIONS
select count(distinct "visit_id") as engagement, $count_of_visits_with_snippet as visits_with_snippet,
(engagement / $count_of_visits_with_snippet) * 100 as percentage
from table($all_events_table)
where  "event_type" IN ('smartSnippet', 'Click')
and ("event_value" in ('expandSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Main Snippet Clicks in Unique Visits

Run the following query to obtain the total number of clicks on the secondary snippets in unique visits.

-- MAIN SNIPPET CLICKS
select count(distinct "visit_id") as engagement, $count_of_visits_with_snippet as visits_with_snippet,
(engagement / $count_of_visits_with_snippet) * 100 as percentage
from table($all_events_table)
where  "event_type" = 'Click'
and "cause2" = 'openSmartSnippetSource'
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)

After running the query, here’s an example of what the SQL results table in the Snowflake console should look like:

Step 5: Review Results

Once you’ve added the results of the SQL queries to the companion spreadsheet, you now have a complete picture and can assess your findings to determine how Smart Snippets affect your search metrics.

Reference

You can alternatively paste the complete SQL code to the Snowflake reader account, and then select the applicable sections to be queried with each step.

-- 1. RUN THIS SETUP FIRST
-- Configure Variables (date must be in format YYYY-MM-DD)
SET OrgID = '<ORG_ID>';
SET PipelineID = '<PIPELINE_ID>';
SET Start_Date = '<START_DATE>';
SET End_Date = '<END_DATE>';
SET searches_table = '"COVEO"."ua"."searches_shared"';
SET all_events_table = '"COVEO"."ua"."all_events_shared"';
SET search_impressions_table = '"COVEO"."ua"."search_impressions_shared"';
SET click_table = '"COVEO"."ua"."clicks_shared"';
//
-- 2. RUN THESE VARIABLES (used in later calculations)
SET total_visits = (
select count (distinct "visit_id")
from table($all_events_table)
where "account_id" = $OrgID
and "query_pipeline" = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")> 0);
SET total_search_events = (
select count (distinct "search_id")
from table($all_events_table)
where "event_type" = 'Search'
and "account_id" = $OrgID
and "query_pipeline" = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")> 0
);
SET count_of_search_events_with_snippet = (
select count (distinct si."search_id")
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date

-- SELECT THE SNIPPET TYPE YOU WANT TO INCLUDE IN THE CALCULATION BY UNCOMMENTING THE APPLICABLE OPTION
-- A. Main Snippet OR People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)
-- B. Main Snippet AND People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") > 2)
-- C. Main Snippet only
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") <= 2)
-- D. People Also Ask only
--and (len(si."search_impression":"result":"answers"[0].question) <= 2 and len(si."search_impression":"result":"related_questions") > 2)
and len("query_expression")> 0);
SET count_of_visits_with_snippet = (
select count (distinct s."visit_id")
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date

-- SELECT THE SNIPPET TYPE TO MATCH THE PREVIOUS SELECTION
-- A. Main Snippet OR People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)
-- B. Main Snippet AND People Also Ask
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") > 2)
-- C. Main Snippet only
--and (len(si."search_impression":"result":"answers"[0].question) > 2 and len(si."search_impression":"result":"related_questions") <= 2)
-- D. People Also Ask only
--and (len(si."search_impression":"result":"answers"[0].question) <= 2 and len(si."search_impression":"result":"related_questions") > 2)
and len("query_expression")> 0);
SET count_of_search_events_without_snippet = (
select $total_search_events - $count_of_search_events_with_snippet)
//
-- SEARCH VOLUME
-- Total Search Events and Total Unique Visits in the pipeline
select $total_search_events as total_search_events
//
-- SNIPPET VOLUME
-- Search Events with Snippets
select $count_of_search_events_with_snippet as total_with_snippet,
$total_search_events as total_search_events,
($count_of_search_events_with_snippet / $total_search_events) * 100 as percentage
-- Change the variables (B, C, D) in the setup and rerun this query to get the breakdown per snippet type
//
-- SNIPPETS ENGAGEMENT
-- Snippet engagement
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('expandSmartSnippet', 'expandSmartSnippetSuggestion','likeSmartSnippet', 'dislikeSmartSnippet') or "cause2" in ('openSmartSnippetSource', 'openSmartSnippetSuggestionSource'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- MAIN SNIPPET EXPANSIONS
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where  "event_type" IN ('smartSnippet', 'Click')
and ("event_value" in ('expandSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- MAIN SNIPPET CLICKS
select count(distinct "click_id") as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where  "event_type" = 'Click'
and "cause2" = 'openSmartSnippetSource'
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- PEOPLE ALSO ASKED (PAA) EXPANSIONS
select count(distinct "custom_event_id") as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where "event_value" = 'expandSmartSnippetSuggestion'
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- PEOPLE ALSO ASKED (PAA) CLICKS
select count(distinct "click_id") as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where "event_type" = 'Click'
and "cause2" = 'openSmartSnippetSuggestionSource'
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- TOTAL "Was this useful?"
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as Total_Feedback,
$count_of_search_events_with_snippet as search_events_with_snippet, (Total_Feedback / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('likeSmartSnippet', 'dislikeSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- TOTAL 👍
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('likeSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- TOTAL 👎
select count(distinct case when "event_type" = 'Click' then "click_id" else "custom_event_id" end) as engagement,
$count_of_search_events_with_snippet as search_events_with_snippet, (engagement / $count_of_search_events_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('dislikeSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- CLICK METRICS
-- Compare click metrics performances
select 'with_snippet' as category, avg("click_rank") as Avg_click_rank, count(distinct "click_id") as clicks, $count_of_search_events_with_snippet as search_events,
(count(distinct "search_id") / search_events) * 100 as CTR
from table($click_table)
where "cause" in ('openSmartSnippetSource', 'openSmartSnippetSuggestionSource', 'documentOpen')
and "account_id" = $OrgID
and "query_pipeline" = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and "search_id" in (
select distinct si."search_id"
from table($search_impressions_table) si
inner join
(select "account_id","search_id","query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")> 0) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date
and (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2))
UNION ALL
select 'without_snippet' as category, avg("click_rank") as Avg_click_rank, count(distinct "click_id") as clicks, $count_of_search_events_without_snippet as search_events,
(count(distinct "search_id") / search_events) * 100 as CTR
from table($click_table)
where "account_id" = $OrgID
and "query_pipeline" = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and "search_id" in (select distinct si."search_id" from table($search_impressions_table) si
inner join
(select "account_id","search_id","query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")> 0) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date
and (len(ifnull(si."search_impression":"result":"answers"[0].question, 0)) <= 2 and len(ifnull(si."search_impression":"result":"related_questions", 0)) <= 2))
UNION ALL
select 'overall' as category, avg("click_rank") as Avg_click_rank, count(distinct "click_id") as clicks, $total_search_events as search_events,
(count(distinct "search_id") / search_events) * 100 as CTR
from table($click_table)
where "account_id" = $OrgID
and trim("query_pipeline", '"') = $PipelineID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- QUALITATIVE METRICS
-- Which queries displayed a snippet and what is the snippet?
with base_table_cte as
(select distinct
si."search_id" as search_id,
trim(s."query_expression", '"') as user_query,
case when (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)
then True else False end as search_with_snippet,
si."search_impression":"result":"answers"[0].question as snippet,
si."search_impression":"result":"answers"[0].permanent_id as permanent_id
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date
and len("query_expression")> 0)
select user_query, count(user_query) as count_of_user_query, mode(snippet) as snippet_title, mode(permanent_id) as permanent_id
from base_table_cte
where search_with_snippet = True and len(snippet)>0
group by 1
order by 2 desc
//
-- Which queries displayed a snippet and what is the list of all snippets displayed?
with base_table_cte as
(select distinct
si."search_id" as search_id,
trim(s."query_expression", '"') as user_query,
case when (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)
then True else False end as search_with_snippet,
si."search_impression":"result":"answers"[0].question as snippet_title,
si."search_impression":"result":"answers"[0].permanent_id as permanent_id
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date
and len("query_expression")> 0)
select user_query, snippet_title, permanent_id, count(snippet_title) as count_of_snippet
from base_table_cte
where search_with_snippet = True and len(snippet_title)>0
group by 1,2, 3
order by 1,3 desc
//
-- Which popular queries don’t have a snippet? (so that I can work on my content)
with base_table_cte as
(select distinct
si."search_id" as search_id,
trim(s."query_expression", '"') as user_query,
case when (len(si."search_impression":"result":"answers"[0].question) > 2 or len(si."search_impression":"result":"related_questions") > 2)
then True else False end as search_with_snippet
from table($search_impressions_table) si
left join
(select "account_id","search_id","visit_id", "query_expression" from table($searches_table) where date("datetime") >= $Start_Date and date("datetime") <= $End_Date) s
on s."search_id" = si."search_id" and s."account_id" = si."account_id"
where si."account_id" = $OrgID
and trim(si."search_impression":"query":"pipeline", '"') = $PipelineID
and date(si."datetime") >= $Start_Date and date(si."datetime") <= $End_Date)
select user_query, count(user_query) as count_of_user_query, sum(case when search_with_snippet = True then 1 else 0 end) as count_with_snippet
from base_table_cte
where len(user_query)>0
group by 1
having count_with_snippet = 0
order by 2 desc
limit 200
//
-- SEARCH VOLUME
-- Total Unique Visits in the pipeline
select $total_visits as total_unique_visits
//
-- SNIPPET VOLUME
-- Unique visits with snippets
select $count_of_visits_with_snippet as visits_with_snippet,
$total_visits as total_visits,
($count_of_visits_with_snippet / $total_visits) * 100 as percentage
//
-- SNIPPETS ENGAGEMENT
-- Snippet engagement (all)
select count(distinct "visit_id") as engagement,
$count_of_visits_with_snippet as visits_with_snippet, (engagement / $count_of_visits_with_snippet) * 100 as percentage
from table($all_events_table)
where ("event_value" in ('expandSmartSnippet', 'expandSmartSnippetSuggestion','likeSmartSnippet', 'dislikeSmartSnippet') or "cause2" in ('openSmartSnippetSource', 'openSmartSnippetSuggestionSource'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- MAIN SNIPPET EXPANSIONS
select count(distinct "visit_id") as engagement, $count_of_visits_with_snippet as visits_with_snippet,
(engagement / $count_of_visits_with_snippet) * 100 as percentage
from table($all_events_table)
where  "event_type" IN ('smartSnippet', 'Click')
and ("event_value" in ('expandSmartSnippet'))
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- MAIN SNIPPET CLICKS
select count(distinct "visit_id") as engagement, $count_of_visits_with_snippet as visits_with_snippet,
(engagement / $count_of_visits_with_snippet) * 100 as percentage
from table($all_events_table)
where  "event_type" = 'Click'
and "cause2" = 'openSmartSnippetSource'
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- PEOPLE ALSO ASKED (PAA) EXPANSIONS
select count(distinct "visit_id") as engagement, $count_of_visits_with_snippet as visits_with_snippet,
(engagement / $count_of_visits_with_snippet) * 100 as percentage
from table($all_events_table)
where "event_value" = 'expandSmartSnippetSuggestion'
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)
//
-- PEOPLE ALSO ASKED (PAA) CLICKS
select count(distinct "visit_id") as engagement, $count_of_visits_with_snippet as visits_with_snippet,
(engagement / $count_of_visits_with_snippet) * 100 as percentage
from table($all_events_table)
where "event_type" = 'Click'
and "cause2" = 'openSmartSnippetSuggestionSource'
and "account_id" = $OrgID
and "search_id" in (select distinct "search_id" from table($all_events_table)
where trim("query_pipeline", '"') = $PipelineID
and "account_id" = $OrgID
and date("datetime") >= $Start_Date and date("datetime") <= $End_Date
and len("query_expression")>0)