[amazon Ads] AMC - Write queries within AMC

2innnnn0·2025년 1월 14일
AMCadsamazonAds

https://advertising.amazon.com/academy/academy-assets/resource_target/dd093638-cbd5-4e24-b7a4-1ca103d577a0/index.html#/page/63e6ab7969682d0838cf1ef9?element=63e6ab7969682d0838cf1f36

쿼리 개요 (Query Overview)

AMC에서 쿼리 작성에 익숙해지기 위해 단일 테이블을 사용하여 답할 수 있는 비즈니스 질문을 시작점으로 삼을 수 있습니다. 아래는 간단한 단일 테이블 쿼리와 사용 방법에 대한 예제입니다.

쿼리 1: 캠페인별 노출 수 조회
• 사용 테이블: dsp_impressions
• 사용 SQL 함수: CONCAT, SUM
• 비즈니스 통찰:
• 캠페인별 노출 수를 확인
• AMC 캠페인의 노출 수를 Amazon DSP Reporting과 비교하여 분석 및 검증 가능

쿼리 2: 캠페인별 총 비용 조회 (달러 단위)
• 사용 테이블: dsp_impressions
• 사용 SQL 함수: CONCAT, SUM, 계산된 필드
• 비즈니스 통찰:
• 캠페인별 총 비용 및 평균 CPM(비용 대비 노출)을 확인

쿼리 2.A: 디바이스 차원별 노출 수 및 비용 메트릭 조회
• 사용 테이블: dsp_impressions
• 사용 SQL 함수: CONCAT, SUM, 계산된 필드
• 비즈니스 통찰:
• 다양한 차원 범주를 기반으로 비용 메트릭을 확인하여 비즈니스 질문에 답변

쿼리 3: Sponsored Products 및 Sponsored Brands 캠페인에 대한 노출, 클릭, 비용 메트릭 조회
• 사용 테이블: sponsored_ads_traffic
• 사용 SQL 함수: SUM, 계산된 필드, WHERE 필터
• 비즈니스 통찰:
• 타겟팅 차원별 노출, 클릭, 비용 메트릭을 집계하여 키워드 타겟팅 최적화

쿼리 4: 탐색적 쿼리를 통한 메타데이터 가져오기
• 사용 테이블: dsp_impressions
• 사용 SQL 함수: CONCAT, SUM, WHERE, SIMILAR TO, CAST
• 비즈니스 통찰:
• 더 복잡한 쿼리 필터링에 사용할 메타데이터를 가져오기

Beginner queries

1: Retrieve impressions by campaign

-쿼리 설명
- dsp_impressions 표에는 Amazon DSP 캠페인에 대한 인상 이벤트가 포함되어 있습니다. 이 표를 사용하여 캠페인, 라인 아이템, 크리에이티브와 같은 Amazon DSP 객체부터 공급, 디바이스 및 지리적 카테고리의 설명적 차원까지 다양한 차원에서 인상 전달 지표를 집계할 수 있습니다. 아래 예시는 캠페인별 인상을 집계한 것입니다.

  • 예제 쿼리
SELECT
      campaign,
      CONCAT('cid_', campaign_id_string) AS campaign_id_string,
      SUM(impressions) AS impressions
FROM 

      dsp_impressions
GROUP BY 

      1,2
  • 쿼리 작성 노트
    : Excel은 .csv 출력을 열 때 필드 유형을 자동으로 해석합니다. Excel은 부동 소수점 숫자를 저장하고 계산하는 방법에 대한 IEEE 754 사양을 따르며, 따라서 15자리 숫자를 숫자에 저장하고 15자리 숫자 뒤의 숫자를 0으로 변경합니다. campaign_id_string과 같은 필드는 숫자 값으로 해석되어 이러한 필드를 반올림하게 됩니다.

모범 사례: ConCAT를 사용하여 ID 필드에 문자열을 추가하여 Excel 내에서 정확한 ID를 읽는 데 문제가 발생하지 않도록 합니다.

참고: 보고서의 ID를 다른 쿼리 내의 입력으로 사용하는 경우, AMC는 해당 접두사가 부착된 ID를 인식하지 않으므로 접두사를 포함하지 않도록 주의하세요. 예를 들어, AMC는 'cid_543219876543212345'를 인식할 수 없지만, 543219876543212345라는 접두사가 없으면 캠페인 ID를 인식할 수 있습니다.

  • 분석

  • 이 쿼리를 사용하여 전체 캠페인의 인상을 비교하여 인상량 기준으로 가장 큰 캠페인이 무엇인지 파악할 수 있습니다. 또한 Amazon DSP 보고서와 함께 AMC 결과를 사용하여 지표가 어떻게 비교되는지 파악할 수 있습니다. AMC와 Amazon DSP 간의 유사한 지표를 비교할 때, 이와 같은 쿼리의 경우 5~10%의 불일치가 발생하는 것이 일반적입니다. 그러나 AMC 메트릭은 쿼리의 기본 비즈니스 논리(예: 어떤 레코드가 포함되어 있는지, 어떤 속성 논리가 적용되는지 등)의 산물이므로 더 복잡한 AMC 쿼리는 다른 Amazon Ads 보고와 더 큰 차이를 보일 수 있습니다.

  • 명심하세요: 시간대, 사용된 테이블, 쿼리 로직 및 기타 차이와 같은 요인으로 인해 AMC와 다른 보고 표면(예: Amazon DSP) 간의 불일치가 발생할 수 있습니다.
    보고에 예상보다 큰 차이가 발생하면 시도해 보세요:
    a. 좋아요를 비교하고 있는지 확인하기(같은 기본 캠페인, 동일한 보고 시간대, 공식의 정확성, 쿼리의 논리를 통해 레코드를 제외하거나 복제하고 있는지 평가하기).
    b. 추가 문제 해결을 위해 AMC-support@amazon.com 에 문의하기

2: Get total costs by campaign in dollars

  • 이전 예제를 기반으로 이제 총 비용 및 평균 CPM과 같은 지출 지표를 통합해 보겠습니다. 이 작업은 dsp_impressions 테이블만 사용하여 수행할 수도 있습니다. dsp_impressions 테이블은 이벤트 수준이기 때문에(즉, 개별 노출 이벤트 당 개별 노출 이벤트당 하나의 기록)이기 때문에 비용 지표는 달러/현지 통화로 보고되지 않고 필드에 따라 마이크로센트 또는 밀리센트 단위로 보고됩니다.

  • 마이크로센트: 1 USD = 100,000,000 마이크로센트(1센트 = 1,000,000 마이크로센트)
    예시 필드: 캠페인예산금액, 라인아이템예산금액, 지출, 노출비용

  • 밀리센트: 1 USD = 100,000 밀리센트(1센트 = 1000 밀리센트) 예시 필드: 총비용, 공급비용, 오디언스비용, 타사비용

이에 대한 자세한 설명은 캠페인 비용 IQL 계산 방법 항목을 참조하세요.

모범 사례:

  • 데이터를 더 쉽게 해석할 수 있도록 지출 지표를 달러(또는 현지 통화)로 변환하고, 스키마 탐색기 도구 설명(아래 스크린샷)을 사용하여 어떤 필드가 어떤 단위인지 확인할 수 있습니다.

  • 여러 시장/지역의 기록으로 작업하는 경우(즉, 여러 통화가 사용되는 경우, 여러 통화가 사용되는 경우), 보고서에 currency_iso_code 필드를 포함하면 독자가 비용 지표가 어떤 통화로 보고되는지 명확하게
    파악할 수 있습니다. currency_iso_code 값은 연결된 Amazon DSP 또는 스폰서 광고 엔티티에 설정된 통화를 기준으로 합니다.

  • 예시 쿼리

SELECT
      campaign,
      CONCAT('cid_', campaign_id_string) AS campaign_id_string,

      SUM(total_cost)/100000 AS total_cost_dollars,
      ((SUM(total_cost)/100000)/SUM(impressions))*1000 AS avg_cpm,
      SUM(impressions) AS impressions
FROM 

      dsp_impressions
GROUP BY 

      1,2

  • 쿼리 작성 노트
    이전 예시와 마찬가지로 ID를 보존하기 위해 항상 CONCAT을 사용하는 것을 잊지 마세요.
    비용 지표를 달러/현지 통화로 변환하려면:
    총 비용, 밀리센트 단위로 보고된 필드를 100,000으로 나누어 비용을 달러/현지 통화로 변환합니다.
    평균 CPM(평균 CPM)은 다음 공식을 통해 계산됩니다: ((총 비용)/100000)/SUM(인상)*1000
    비슷한 공식은 마일당 비용을 받는 것이 합리적인 다른 분야에서도 사용할 수 있습니다(예: 입찰 가격)

  • 분석

  • 캠페인 비용과 인상을 요약함으로써, 캠페인 비용 효율성을 추적하고 평가하며 여러 캠페인 유형을 비교할 수 있습니다. 동일한 쿼리를 재사용하되 공급 차원(예: supply_source 또는 site)을 포함하면 공급 수준에서도 효율성을 평가할 수 있으므로 가장 효율적인 공급 유형에 우선순위를 둘 수 있습니다.

2.A: Retrieve impressions and spend metrics by device dimensions

SELECT
      device_type,
      operating_system,
      SUM(total_cost)/100000 AS total_cost_dollars,
      ((SUM(total_cost)/100000)/SUM(impressions))*1000 AS avg_cpm,
      SUM(impressions) AS impressions
FROM 

      dsp_impressions
GROUP BY 

      1,2

분석

  • 아래 예시 출력에서 총 비용(달러 단위), 평균 CPM, 그리고 디바이스_타입 및 운영 체제 조합별 총 인상량을 확인할 수 있습니다. CPM 관점에서 어떤 조합이 다른 조합보다 더 효율적인지 알 수 있습니다.

도달 범위를 우선시하는 경우 가장 효율적인 CPM과 최고의 규모(녹색으로 표시된 레코드)를 갖춘 디바이스 유형 및 운영 체제에 대한 지출을 최적화하는 것이 가치가 있을 수 있습니다. 이 정보를 사용하여 특정 장치 속성 조합에 대한 전용 라인 항목을 구분하여 이전에 달성한 것보다 더 효율적인 CPM으로 확장하려고 시도할 수도 있습니다(오렌지색 레코드).

CPM은 형식에 따라 자연스럽게 다를 수 있습니다(예: TV CPM은 휴대폰, 태블릿 또는 PC보다 높을 수 있습니다).

3: Retrieve impressions, clicks, and spend metrics for Sponsored Products and Sponsored Brands campaigns

SELECT
      ad_product_type,
      targeting,
      customer_search_term,
      match_type,
      SUM(spend)/100000000 AS total_cost_dollars,
      ((SUM(spend)/100000000)/SUM(impressions))*1000 AS avg_cpm,
      SUM(impressions) AS impressions,
      SUM(clicks) AS clicks,
      (SUM(clicks)/SUM(impressions)) AS ctr
FROM 

      sponsored_ads_traffic
WHERE match_type IN('PHRASE', 'BROAD', 'EXACT')
GROUP BY 

      1,2,3,4

4: Pulling metadata via exploratory queries

  • 쿼리 설명
    IQL을 사용할 때 특정 IQ 항목의 지침에 따라 비즈니스 질문과 관련된 캠페인을 찾기 위해 사용하는 "탐색 쿼리"를 실행하는 경우가 많습니다. 이러한 캠페인이나 관련 객체의 이름이나 ID는 IQ 항목의 주요 쿼리 템플릿 입력으로 사용됩니다. 탐색적 쿼리는 Amazon 광고 계정 내에서 무엇이 있는지 빠르게 정리하거나 이해하고 싶은 경우에도 유용합니다. 예를 들어, 당신이 실행 중인 모든 여성 전용 캠페인이나 특정 날짜 이후에 시작된 캠페인 목록, 또는 특정 날짜 범위 내에서 실행된 모든 창의적인 캠페인 목록을 가져오고 싶을 수도 있습니다.

아래 쿼리는 원하는 날짜 범위에서 실시간으로 제공된 내용(예: 인상을 전달한 내용)을 신속하게 평가하기 위해 메타데이터를 끌어오는 예시입니다.

SELECT
      advertiser,
      campaign,
      CONCAT('cid_', campaign_id_string) AS campaign_id_string,
      campaign_start_date,
      campaign_end_date,
      campaign_budget_amount,
      line_item,
      CONCAT('lid_', line_item_id) AS line_item_id,
      line_item_start_date,
      line_item_end_date,
      line_item_status,
      line_item_budget_amount,
      creative,
      CONCAT('crid_', creative_id) AS creative_id,
      creative_type,
      creative_size,
      SUM(impressions) AS impressions
FROM 

      dsp_impressions
GROUP BY 

      1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

참고: 관련된 인상 데이터보다 메타데이터에 더 관심이 있을 수 있지만, 메트릭 필드에 적용되는 집계 함수를 포함하지 않고는 차원 열을 풀링할 수 없다는 점을 기억하세요.

  • AMC에 대한 메타데이터 요청을 보다 구체적으로 설명하고 싶다면, 예를 들어 문자열 로직을 하나 이상의 필드에 적용하는 WHERE 필터를 활용할 수 있습니다. SILAR TO 함수와 함께 사용되는 경우 정규 표현식을 사용하여 결과를 특정 문자열 값으로 필터링합니다. 예를 들어, 아래 쿼리는 값의 어딘가에 '여성' 또는 '여성'이 포함된 캠페인으로 결과를 제한합니다.
SELECT
      advertiser,
      campaign,
      CONCAT('cid_', campaign_id_string) AS campaign_id_string,
      campaign_start_date,
      campaign_end_date,
      campaign_budget_amount,
      line_item,
      CONCAT('lid_', line_item_id) AS line_item_id,
      line_item_start_date,
      line_item_end_date,
      line_item_status,
      line_item_budget_amount,
      creative,
      CONCAT('crid_', creative_id) AS creative_id,
      creative_type,
      creative_size,
      SUM(impressions) AS impressions
FROM 

      dsp_impressions
WHERE 

      campaign SIMILAR TO 'women'
      OR campaign SIMILAR TO '(?i)women'
      OR campaign SIMILAR TO '^women'
      OR campaign SIMILAR TO 'women''s'
GROUP BY 

      1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

  1. 결과를 캠페인 가치 어딘가에 '여성'이 포함된 캠페인으로 필터링합니다. 대소문자 구분이 필요합니다.
  2. 결과를 캠페인 가치 어딘가에 '여성'이 포함된 캠페인으로 필터링합니다. 대소문자 구분이 없습니다.
    3.'여성'으로 시작하는 캠페인에 결과를 필터링하고 대소문자 구분을 합니다.
  3. 결과를 캠페인 값 어딘가에 '여성'이 포함된 캠페인으로 필터링합니다. 문자열에 아포스트로피가 나타나면 표시된 것처럼 해당 아포스트로피를 두개로 작성해야 합니다(즉, women's becomes women''s).

또는 LIKE(소문자 구분)를 사용할 수도 있습니다. 가치 어딘가에 '여성'이 포함된 캠페인을 필터링하려면 WHERE 문구를 '%여성'과 같은 캠페인으로 조정할 수 있습니다. (LIKE is not currently supported in AMC).

WHERE는 논리 연산자와 함께 날짜와 같은 다른 것들을 필터링하는 데에도 사용할 수 있습니다. 예를 들어, 아래 쿼리는 결과를 캠페인_start_date 값이 2023년 1월 01일 이후인 캠페인으로 제한합니다. 이 예제에서는 CAST를 사용하여 campaign_start_date와 2023-01-01 날짜를 동일한 데이터 유형으로 변환하므로 논리 연산자를 사용하여 값을 표준화하여 비교할 수 있습니다.

SELECT
      advertiser,
      campaign,
      CONCAT('cid_', campaign_id_string) AS campaign_id_string,
      campaign_start_date,
      campaign_end_date,
      campaign_budget_amount,
      line_item,
      CONCAT('lid_', line_item_id) AS line_item_id,
      line_item_start_date,
      line_item_end_date,
      line_item_status,
      line_item_budget_amount,
      creative,
      CONCAT('crid_', creative_id) AS creative_id,
      creative_type,
      creative_size,
      SUM(impressions) AS impressions
FROM 

      dsp_impressions
WHERE 

      CAST(campaign_start_date AS DATE) >= CAST('2023-01-01' AS DATE)
GROUP BY 

      1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
  • 기타 필터링 사례
    이러한 유형의 WHERE 논리는 탐색적 쿼리뿐만 아니라 모든 쿼리에 적용할 수 있다는 점을 기억하세요. 필터링이 적절한 다른 예들도 있습니다:

특정 명명 규칙을 사용하여 크리에이티브의 제공 및 성과를 검토합니다.
결과를 특정 문자열이 포함된 사이트 또는 거래 ID에서 실행되는 인상으로 제한합니다.
결과를 특정 지리적 지역에서 실행되는 인상으로 제한합니다(예: iso_state_province_code).
특정 광고 제품의 변환 이벤트로 결과 제한
결과를 특정 캠페인 인상 기간 내에 발생한 전환 이벤트로 제한

요약 Summary

  1. CPM을 dsp_impression 테이블로 구한다면?
  • ((SUM(total_cost)/100000)/SUM(impressions))*1000
  1. dsp_impressions 테이블에 대한 탐색적 쿼리를 사용하여 쿼리 기간 내에 해당 캠페인이 실시간으로 진행되었는지 여부에 관계없이 모든 Amazon DSP 캠페인에 대한 메타데이터를 가져올 수 있습니다.
  • 거짓. dsp_impressions 테이블에는 인상 이벤트가 포함되어 있으므로 탐색 쿼리는 쿼리 기간 내에 인상이 있는 객체에 대해서만 결과를 반환합니다.
  1. 라인 아이템 아이디에 컨캣으로 문자열을 붙이기.

CONCAT('lineItemId: ', lineitem_id) AS line_item_id,
CONCAT('lid', line_item_id) AS line_item_id,


  1. SUM(total_cost)/100000000


  2. WHERE campaign SIMILAR TO '(?i)electronics'

profile
2innnnn0
성장한 데이터분석가
이전 포스트

[amazon Ads] 아마존 광고 용어 101

다음 포스트

[amazon Ads] AMC - 중간복잡도 쿼리 실행 (CTE,UNION, JOIN)

0개의 댓글