---
title: SQL expressions reference for metrics
slug: mcge0079
canonical_url: https://docs.coveo.com/en/mcge0079/
collection: coveo-analytics
source_format: adoc
---
# SQL expressions reference for metrics
This article is a reference of SQL expressions relating to [Coveo Analytics metrics](https://docs.coveo.com/en/263/) that can be added to queries which can be run against [Coveo Analytics data](https://docs.coveo.com/en/259/) to build reports.
The query syntax is Snowflake-specific and can be used in the [Snowflake reader account](https://docs.coveo.com/en/l9e90297/) or [Snowflake data share](https://docs.coveo.com/en/m1rf0146/).

> **Note**
>
> SQL expressions must be entered within a `SELECT` statement.
> For example:
> 
> ```sql
SELECT
	"origin_country" AS "Search Country",
	COUNT(distinct "search_id") AS "Search Event Count"
FROM
	"COVEO"."ua"."searches_shared"
WHERE
	CONVERT_TIMEZONE('UTC', 'America/New_York', "datetime") BETWEEN '2022-12-13 00:00:00.000' :: TIMESTAMP_TZ
	AND '2023-01-12 23:59:59.999' :: TIMESTAMP_TZ
GROUP BY
	"Search Country"
ORDER BY
	"Search Event Count" DESC
```

## Search Count

```sql
COUNT(distinct "searches_shared"."search_id")
COUNT(distinct "all_events_shared"."search_id")
```

## Click Count

```sql
COUNT(distinct "clicks_shared"."click_id")
COUNT(distinct "all_events_shared"."click_id")
```

## Custom Event Count

```sql
COUNT(distinct "custom_events_shared"."custom_event_id")
COUNT(distinct "all_events_shared"."custom_event_id")
```

## DocumentView Count

```sql
COUNT(distinct "click_id") AS "Click Event Count"
FROM "COVEO"."ua"."clicks_shared"
```

> **Note**
>
> When using this expression, add a `WHERE` clause to filter the data and avoid a long query execution time.

## Unique Visit Count

```sql
COUNT(distinct "searches_shared"."visit_id")
COUNT(distinct "clicks_shared"."visit_id")
COUNT(distinct "custom_events_shared"."visit_id")
COUNT(distinct "all_events_shared"."visit_id")
```

## Click Rank

```sql
ZEROIFNULL(avg("clicks_shared"."click_rank"))
ZEROIFNULL(avg("all_events_shared"."click_rank"))
```

## Click Through

```sql
DIV0NULL(
    COUNT(distinct "clicks_shared"."search_id"),
    COUNT(distinct "searches_shared"."search_id")
)
```

## Visit Click Through

```sql
DIV0NULL(
    COUNT(distinct "clicks_shared"."visit_id"),
    COUNT(distinct
        COALESCE(
            "searches_shared"."visit_id",
            "clicks_shared"."visit_id",
            "custom_events_shared"."visit_id"
        )
    )
)
```

## Manual Search Event Count

```sql
COUNT(
	distinct case
		when "searches_shared"."cause2" in (
			'didyoumeanAutomatic',
			'didyoumeanClick',
			'searchboxSubmit',
			'searchboxAsYouType',
			'omniboxSearch',
			'omniboxAnalytics',
			'omniboxFromLink',
			'omniboxField',
			'searchFromLink'
		) then "search_id"
		else null
	end
)
```

## Refined Search Event Count

```sql
COUNT(
	distinct case
		when "searches_shared"."cause2" in (
			'contextRemove',
			'interfaceChange',
			'breadcrumbFacet',
			'breadcrumbResetAll',
			'documentField',
			'documentTag',
			'facetClearAll',
			'facetRangeSlider',
			'facetRangeGraph',
			'facetSelect',
			'facetDeselect',
			'facetExclude',
			'facetUnexclude',
			'facetSelectAll',
			'omniboxFacet'
		) then "search_id"
		else null
	end
)
```

## Unique User ID

```sql
COUNT(distinct "searches_shared"."user_id")
COUNT(distinct "clicks_shared"."user_id")
COUNT(distinct "custom_events_shared"."user_id")
COUNT(distinct "all_events_shared"."user_id")
```

## Unique User Name

```sql
COUNT(distinct "searches_shared"."user_name")
COUNT(distinct "clicks_shared"."user_name")
COUNT(distinct "custom_events_shared"."user_name")
COUNT(distinct "all_events_shared"."user_name")
```

## Unique User IP

```sql
COUNT(distinct "searches_shared"."user_ip")
COUNT(distinct "clicks_shared"."user_ip")
COUNT(distinct "custom_events_shared"."user_ip")
COUNT(distinct "all_events_shared"."user_ip")
```

## Unique Visitor ID

```sql
COUNT(
	DISTINCT COALESCE(
		"searches_shared"."client_id",
		"searches_shared"."visitor_id"
	)
)
COUNT(
	DISTINCT COALESCE(
		"clicks_shared"."client_id",
		"clicks_shared"."visitor_id"
	)
)
COUNT(
	DISTINCT COALESCE(
		"custom_events_shared"."client_id",
		"custom_events_shared"."visitor_id"
	)
)
COUNT(
	DISTINCT COALESCE(
		"all_events_shared"."client_id",
		"all_events_shared"."visitor_id"
	)
)
```

## Attach to Case Count

```sql
COUNT(
	DISTINCT IF(
		"custom_events_shared"."event_value" = 'caseAttach',
		"custom_events_shared"."custom_event_id",
		NULL
	)
)
```

## Search Event With Clicks

```sql
COUNT(distinct "clicks_shared"."search_id")
```

## Average Response Time

```sql
NVL(
	cast(
		floor(avg("searches_shared"."response_time_ms")) as integer
	),
	0
)
```

## Average Search Query Word Count

```sql
NVL(
	cast(
		floor(avg(regexp_count("searches_shared"."query_expression", '[\w-]+'))) as integer
	),
	0
)
```

## Maximum Response Time

```sql
MAX("searches_shared"."response_time_ms")
```

## Last Search Date

```sql
MAX("searches_shared"."datetime")
```

## Last Click Date

```sql
MAX("clicks_shared"."datetime")
```

## Last Custom Event Date

```sql
MAX("custom_events_shared"."datetime")
```

## Visits With Click

```sql
COUNT(distinct "clicks_shared"."visit_id")
```

## Visit With Manual Search Events

```sql
COUNT(
	distinct case
		when "searches_shared"."cause2" in (
			'didyoumeanAutomatic',
			'didyoumeanClick',
			'searchboxSubmit',
			'searchboxAsYouType',
			'omniboxSearch',
			'omniboxAnalytics',
			'omniboxFromLink',
			'omniboxField',
			'searchFromLink'
		) then "visit_id"
		else null
	end
)
```

## Dimension Count

```sql
COUNT(distinct <DIMENSION>)
```