쿼리 가이드라인 및 샘플 쿼리

대량 데이터 내보내기 쿼리에 관한 권장사항

집계 함수를 항상 사용합니다

테이블의 데이터는 날짜, URL, 사이트 또는 기타 키 조합을 기준으로 통합되지 않을 수 있습니다. 따라서 항상 필드(SUM, COUNT 등)를 집계하여 행 값이 통합되도록 해야 합니다. 테이블에 '뚱뚱한 고양이'라는 검색어가 포함된 행이 여러 개 있다고 가정해 보겠습니다.

검색어     날짜       검색_유형   노출수 ...
뚱뚱한 고양이 2022-10-10      웹          1000
뚱뚱한 고양이 2022-10-10      웹           200
뚱뚱한 고양이 2022-10-10    이미지          5000

'뚱뚱한 고양이'라는 검색어만 검색하면 특정 날짜 및 검색 유형에 대해서만도 여러 행이 반환될 수 있습니다. 따라서 다음과 같이 집계하여 노출수의 합계를 확인해야 합니다.

/* 가장 인기 있는 검색어를 확인하는 잘못된 방법: 검색어당 행 1개만 가져옴 */
SELECT query, impressions FROM searchconsole.searchdata_site_impression
ORDER BY 2 DESC LIMIT 1;

Result:
뚱뚱한 고양이   5000

/* 가장 인기 있는 검색어를 확인하는 올바른 방법 */
SELECT query, SUM(impressions) FROM searchconsole.searchdata_url_impression
WHERE query != ''  /* 필터링으로 익명처리된 검색어 제외 */
GROUP BY query ORDER BY 2 DESC LIMIT 1;

Result:
뚱뚱한 고양이   6200

날짜별로 쿼리를 제한하여 처리 비용 절감합니다

BigQuery에서 쿼리를 실행하면 요금이 청구되고 테이블이 상당히 커질 수 있습니다. 내보낸 테이블은 날짜를 기준으로 파티션이 나뉘어 있으므로 입력 검사를 관심 있는 날짜로만 효율적으로 제한할 수 있습니다. 따라서 대규모 사이트의 경우 쿼리 비용을 크게 절감할 수 있습니다. 쿼리 비용을 최소화하기 위해서는 WHERE 절을 사용하여 날짜를 기준으로 파티션을 나눈 테이블의 기간을 제한하는 방법이 가장 좋습니다.

필터링으로 null 쿼리 문자열을 제외합니다

익명처리된 검색어는 사이트에서 일반적으로 가장 많이 사용되는 단일 검색어 중 하나입니다. 익명처리된 검색어는 테이블에서 길이가 0인 문자열로 보고됩니다. 특정 기준에 따라 가장 인기 있는 검색어를 보려면 SQL 쿼리에서 길이가 0인 검색어 값을 필터링으로 제외하는 것이 좋습니다.

샘플 쿼리

다음은 데이터를 준비하고 실행하는 데 도움이 되는 샘플 쿼리입니다.

사이트별 결과

일별 모든 웹 통계(최근 2주)

SELECT
  data_date AS date,
  sum(impressions) AS impressions,
  sum(clicks) as clicks,
  sum(clicks) / sum(impressions) AS ctr,
  /* 게재위치가 0부터 시작하므로 아래에 하나 추가 */
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY data_date

ORDER BY date desc
  LIMIT 1000

클릭수 기준 미국 모바일 웹 인기 검색어(최근 2주)

SELECT
  query,
  device,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_top_position) / sum(impressions)) + 1.0) AS avg_position

FROM searchconsole.searchdata_site_impression
WHERE search_type = 'WEB'
    AND country = 'usa'
    AND device = 'MOBILE'
    AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY clicks
LIMIT 1000

URL별 검색 결과

클릭수별 인기 디스커버 URL(특정 날짜)

SELECT
  url,
  sum(impressions),
  sum(clicks)
  FROM searchconsole.searchdata_url_impression
WHERE search_type = 'DISCOVER'
  AND data_date = DATE(2022, 02, 01)
GROUP BY url
ORDER BY 3
LIMIT 1000

FAQ 리치 결과의 총 검색어 + URL 통계(최근 2주)

SELECT
  url,
  query,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks,
  sum(clicks) / sum(impressions) AS ctr,
  ((sum(sum_position) / sum(impressions)) + 1.0) AS avg_position
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND is_tpf_faq = true
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2 
ORDER BY clicks
LIMIT 1000

URL별 모든 트래픽(최근 2주)

SELECT
  url,
  search_type,
  sum(impressions) as impressions,
  sum(clicks) as clicks
FROM searchconsole.searchdata_url_impression
WHERE data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1,2
ORDER BY 3 desc
LIMIT 1000

'내_브랜드'가 포함된 검색어의 노출수(최근 2주)

SELECT
  regexp_contains(query, '내_브랜드') AS contains_brand,
  sum(impressions) AS impressions,
  sum(clicks) AS clicks
FROM searchconsole.searchdata_url_impression
WHERE search_type = 'WEB'
  AND data_date between DATE_SUB(CURRENT_DATE(), INTERVAL 14 day) and CURRENT_DATE()
GROUP BY 1
ORDER BY 2 desc
LIMIT 1000

도움이 되었나요?

어떻게 하면 개선할 수 있을까요?