We've updated our documentation with the latest features and updates introduced by Ads Data Hub's two new experiences. Advertisers, agencies, and publishers, refer to the Marketers site. Vendors and partners, refer to the Measurement Partners site.

Allowed SQL functions

For privacy reasons, we restrict which SQL functions you can use in ADH.

Scalar functions

All scalar functions except the following are allowed:

ERROR
ST_AREA
ST_ASBINARY
ST_ASGEOJSON
ST_ASTEXT
ST_BOUNDARY
ST_CENTROID
ST_CENTROID_AGG
ST_CLOSESTPOINT
ST_CLUSTERDBSCAN
ST_CONTAINS
ST_CONVEXHULL
ST_COVEREDBY
ST_COVERS
ST_DIFFERENCE
ST_DIMENSION
ST_DISJOINT
ST_DISTANCE
ST_DUMP
ST_DWITHIN
ST_EQUALS
ST_GEOGFROMGEOJSON
ST_GEOGFROMTEXT
ST_GEOGFROMWKB
ST_GEOGPOINT
ST_GEOGPOINTFROMGEOHASH
ST_GEOHASH
ST_INTERSECTION
ST_INTERSECTS
ST_INTERSECTSBOX
ST_ISCOLLECTION
ST_ISEMPTY
ST_LENGTH
ST_MAKELINE
ST_MAKEPOLYGON
ST_MAKEPOLYGONORIENTED
ST_MAXDISTANCE
ST_NPOINTS
ST_NUMPOINTS
ST_PERIMETER
ST_SIMPLIFY
ST_SNAPTOGRID
ST_TOUCHES
ST_UNION
ST_UNION_AGG
ST_WITHIN
ST_X
ST_Y

Analysis queries

For aggregated analysis queries, we restrict functions that combine data from multiple users. All aggregate and analytic functions are allowed when processing a single user's data. See User-level aggregations below for details.

Aggregate functions

Function	Allowed?
`ANY_VALUE`	Yes
`APPROX_QUANTILES`	No
`APPROX_TOP_COUNT`	No
`APPROX_TOP_SUM`	No
`ARRAY_AGG`	No
`ARRAY_CONCAT_AGG`	No
`AVG`	Yes
`BIT_AND`	No
`BIT_OR`	No
`BIT_XOR`	No
`COUNT`	Yes
`COUNTIF`	Yes
`LOGICAL_AND`	Yes
`LOGICAL_OR`	Yes
`MAX`	Yes
`MIN`	Yes
`SUM`	Yes
`CORR`	Yes
`COVAR_POP`	Yes
`COVAR_SAMP`	Yes
`STDDEV_POP`	Yes
`STDDEV_SAMP`	Yes
`STDDEV`	Yes
`STRING_AGG`	No
`VAR_POP`	Yes
`VAR_SAMP`	Yes
`VARIANCE`	Yes
`APPROX_COUNT_DISTINCT`	Yes

Analytic functions

All analytic functions are restricted.

HyperLogLog++ functions

Ads Data Hub supports HyperLogLog++ (HLL++) functions. However, they use specialized data types with more limitations.

Supported types

Type	Description
`ADH.USER_HLL`	A sketch aggregated from values of type `ADH.USER_ID`
`ADH.BYTE_HLL`	A sketch aggregated from values of any non-ID type

These types support merging and extracting to cardinality estimates, and don't support direct conversion to any other type. They cannot be directly exported from Ads Data Hub, so they must use HLL_COUNT.EXTRACT or HLL_COUNT.MERGE before reaching the query output.

HLL sketches enable cardinality estimation across multiple layers of aggregation, but note that in Ads Data Hub they don't give a substantial performance improvement over COUNT(DISTINCT) in a single layer.

Supported functions

Function name	Supported types	Return type
`HLL_COUNT.EXTRACT`	`ADH.USER_HLL`, `ADH.BYTE_HLL`	`INT64`
`HLL_COUNT.INIT`	All standard supported types, `ADH.USER_ID`	`ADH.USER_HLL` for `ADH.USER_ID` input, otherwise `ADH.BYTE_HLL`
`HLL_COUNT.INITNT.MERGE`	`ADH.USER_HLL`, `ADH.BYTE_HLL`	`INT64`
`HLL_COUNT.MERGE_PARTIAL`	`ADH.USER_HLL`, `ADH.BYTE_HLL`	Same as input type

Limitations

Noise injection: HLL++ functions are not supported when running a query using noise injection.

User-level aggregations

We allow all aggregate and analytic functions in SELECT statements that group or partition by user_id. For this to work, any preceding joins must make it clear that the data is kept at the user level by joining on user_id, like in this example:

CREATE TABLE paths AS (
SELECT ARRAY_AGG(campaign_id ORDER BY query_id.time_usec) AS path
FROM
  adh.google_ads_impressions
  LEFT JOIN adh.google_ads_clicks
    USING(query_id, user_id)
GROUP BY user_id)

User list queries

In user list queries, aggregate functions are allowed, and analytic functions aren't supported.