이 페이지는 Cloud Translation API를 통해 번역되었습니다.

마케팅 분석 데이터를 위한 ELT 파이프라인 빌드

이 튜토리얼에서는 BigQuery에서 마케팅 분석 데이터를 추출, 로드, 변환하는 ELT 워크플로를 설정하는 방법을 보여줍니다.

일반적인 ELT 워크플로는 데이터 소스에서 주기적으로 새 고객 데이터를 추출하여 BigQuery에 로드합니다. 그런 다음 비정형 데이터가 의미 있는 측정항목으로 처리됩니다. 이 튜토리얼에서는 BigQuery Data Transfer Service를 사용하여 마케팅 분석 데이터 전송을 설정하여 ELT 워크플로를 만듭니다. 그런 다음 데이터에 대한 주기적인 변환을 실행하도록 Dataform을 예약합니다.

이 튜토리얼에서는 Google Ads를 데이터 소스로 사용하지만 BigQuery Data Transfer Service에서 지원하는 데이터 소스를 사용할 수도 있습니다.

시작하기 전에

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

필요한 역할

튜토리얼을 완료하는 데 필요한 권한을 얻으려면 관리자에게 프로젝트에 대한 다음 IAM 역할을 부여해 달라고 요청하세요.

BigQuery 관리자(roles/bigquery.admin)
Dataform 관리자 (roles/dataform.admin)

역할 부여에 대한 자세한 내용은 프로젝트, 폴더, 조직에 대한 액세스 관리를 참조하세요.

커스텀 역할이나 다른 사전 정의된 역할을 통해 필요한 권한을 얻을 수도 있습니다.

반복 데이터 전송 예약

BigQuery를 데이터 소스의 최신 마케팅 데이터로 업데이트하려면 BigQuery Data Transfer Service를 사용하여 반복되는 데이터 전송을 설정하여 일정에 따라 데이터를 추출하고 로드하세요.

이 튜토리얼에서는 Google Ads를 예시 데이터 소스로 사용합니다. BigQuery Data Transfer Service에서 지원하는 데이터 소스의 전체 목록은 지원되는 데이터 소스를 참고하세요.

Google Cloud 콘솔에서 데이터 전송 페이지로 이동합니다.

데이터 전송으로 이동
전송 만들기를 클릭합니다.
소스 유형 섹션의 소스에서 Google Ads를 선택합니다.
데이터 소스 세부정보 섹션에서 다음을 수행합니다.
1. 고객 ID에 Google Ads 고객 ID를 입력합니다.
2. 보고서 유형에서 표준을 선택합니다. 표준 보고서에는 Google Ads 보고서 변환에 설명된 표준 보고서 및 필드 세트가 포함됩니다.
  - 새로고침 범위에 5를 입력합니다.
대상 설정 섹션의 데이터 세트에서 데이터를 저장하기 위해 만든 데이터 세트를 선택합니다.
전송 구성 이름 섹션의 표시 이름에 Marketing tutorial를 입력합니다.
일정 옵션 섹션에서 다음을 수행합니다.
- 반복 빈도에서 일을 선택합니다.
- At에 08:00를 입력합니다.
저장을 클릭합니다.

구성을 저장하면 BigQuery Data Transfer Service가 데이터 전송을 시작합니다. 전송 구성의 설정에 따라 데이터 전송은 매일 오전 8시(UTC)에 한 번 실행되며 지난 5일간의 Google Ads에서 데이터를 추출합니다.

진행 중인 전송 작업을 모니터링하여 각 데이터 전송의 상태를 확인할 수 있습니다.

테이블 데이터 쿼리

BigQuery로 전송되는 데이터는 내부 데이터화 시간별로 파티션을 나눈 테이블에 기록됩니다. 자세한 내용은 파티션을 나눈 테이블 소개를 참조하세요.

자동 생성된 뷰를 사용하지 않고 직접 테이블을 쿼리하는 경우에는 쿼리에서 _PARTITIONTIME 유사 열을 사용해야 합니다. 자세한 내용은 파티션을 나눈 테이블 쿼리를 참조하세요.

다음 섹션에서는 전송된 데이터를 검사하는 데 사용할 수 있는 샘플 쿼리를 보여줍니다.

캠페인 실적

다음 샘플 쿼리는 지난 30일 동안의 Google Ads 캠페인 실적을 분석합니다.

콘솔

SELECT
  c.customer_id,
  c.campaign_name,
  c.campaign_status,
  SUM(cs.metrics_impressions) AS Impressions,
  SUM(cs.metrics_interactions) AS Interactions,
  (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
FROM
  `DATASET.ads_Campaign_CUSTOMER_ID` c
LEFT JOIN
  `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
ON
  (c.campaign_id = cs.campaign_id
  AND cs._DATA_DATE BETWEEN
  DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  1, 2, 3
ORDER BY
  Impressions DESC

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.customer_id,
    c.campaign_name,
    c.campaign_status,
    SUM(cs.metrics_impressions) AS Impressions,
    SUM(cs.metrics_interactions) AS Interactions,
    (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
  FROM
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  LEFT JOIN
    `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
  ON
    (c.campaign_id = cs.campaign_id
    AND cs._DATA_DATE BETWEEN
    DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  WHERE
    c._DATA_DATE = c._LATEST_DATE
  GROUP BY
    1, 2, 3
  ORDER BY
    Impressions DESC'

다음을 바꿉니다.

DATASET: 전송된 테이블을 저장하기 위해 생성한 데이터 세트의 이름
CUSTOMER_ID: Google Ads 고객 ID입니다.

키워드 수

다음 샘플 쿼리는 캠페인, 광고그룹, 키워드 상태별로 키워드를 분석합니다. 이 쿼리는 KeywordMatchType 함수를 사용합니다. 키워드 검색 유형을 사용하면 어떤 검색어로 광고를 게재할지 지정할 수 있습니다. 키워드 일치 옵션에 대한 자세한 내용은 키워드 일치 옵션 정보를 참조하세요.

콘솔

  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
    JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
  JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4'

다음을 바꿉니다.

DATASET: 전송된 테이블을 저장하기 위해 생성한 데이터 세트의 이름
CUSTOMER_ID: Google Ads 고객 ID입니다.

Dataform 저장소 만들기

Google Ads에서 최신 데이터를 전송하는 데이터 전송 구성을 만든 후에는 마케팅 분석 데이터를 정기적으로 변환하도록 Dataform을 설정합니다. Dataform을 사용하면 정기적인 데이터 변환을 예약할 수 있으며, 다른 데이터 분석가와 공동작업하면서 SQL로 이러한 변환을 정의할 수 있습니다.

변환 코드를 구성하는 SQLX 쿼리를 저장할 Dataform 저장소를 만듭니다.

Google Cloud 콘솔에서 Dataform 페이지로 이동합니다.

Dataform으로 이동
저장소 만들기를 클릭합니다.
저장소 만들기 페이지에서 다음을 수행합니다.
1. 저장소 ID 필드에 marketing-tutorial-repository을 입력합니다.
2. 리전 목록에서 리전을 선택합니다.
3. 만들기를 클릭합니다.

이제 marketing-tutorial-repository 저장소가 Dataform 저장소 목록에 표시됩니다.

Dataform 저장소에 대한 자세한 내용은 Dataform 저장소 정보를 참고하세요.

Dataform 개발 작업공간 만들기 및 초기화

변경사항을 커밋하고 저장소에 푸시하기 전에 저장소 내에서 변환 코드를 작업할 수 있도록 Dataform 개발 작업공간을 만듭니다.

Google Cloud 콘솔에서 Dataform 페이지로 이동합니다.

Dataform으로 이동
marketing-tutorial-repository를 클릭합니다.
개발 작업공간 만들기를 클릭합니다.
개발 작업공간 만들기 창에서 다음을 수행합니다.
1. 작업공간 ID 필드에 marketing-tutorial-workspace를 입력합니다.
2. 만들기를 클릭합니다.
개발 작업공간 페이지가 표시됩니다.
작업공간 초기화를 클릭합니다.

이제 marketing-tutorial-workspace 개발 작업공간이 marketing-tutorial-repository 저장소의 개발 작업공간 탭에 표시되며, definitions 디렉터리에는 *first_view.sqlx 및 *second_view.sqlx라는 두 개의 예시 파일이 있습니다.

Dataform 개발 작업공간에 대한 자세한 내용은 개발 작업공간 개요를 참고하세요.

Google Ads 표를 표 소스로 선언

다음 단계를 따라 새로 이전된 Google Ads 테이블을 데이터 소스로 선언하여 Dataform에 연결합니다.

데이터 소스 선언을 위한 SQLX 파일 만들기

Dataform에서는 definitions/ 디렉터리에 SQLX 파일을 만들어 데이터 소스 대상을 선언합니다.

Google Cloud 콘솔에서 Dataform 페이지로 이동합니다.

Dataform 페이지로 이동
marketing-tutorial-repository을 선택합니다.
marketing-tutorial-workspace을 선택합니다.
파일 창에서 definitions/ 옆에 있는 더보기 메뉴를 클릭합니다.
파일 만들기를 클릭합니다.
새 파일 만들기 창에서 다음을 수행합니다.
1. 파일 경로 추가 필드에 definitions/ 다음에 definitions/googleads-declaration.sqlx 이름을 입력합니다.
2. 파일 만들기를 클릭합니다.

데이터 소스 선언

전송된 Google Ads 테이블을 데이터 소스로 선언하도록 definitions/googleads-declaration.sqlx를 수정합니다. 이 예에서는 ads_Campaign 테이블을 데이터 소스로 선언합니다.

개발 작업공간의 파일 창에서 데이터 소스 선언을 위한 SQLX 파일을 클릭합니다.

파일에 다음 코드 스니펫을 입력합니다.

    config {
        type: "declaration",
        database: "PROJECT_ID",
        schema: "DATASET",
        name: "ads_Campaign_CUSTOMER_ID",
    }

변환 정의

definitions/ 디렉터리에 SQLX 파일을 만들어 데이터 변환을 정의합니다. 이 튜토리얼에서는 daily_performance.sqlx이라는 파일을 사용하여 클릭수, 노출수, 비용, 전환수와 같은 측정항목을 집계하는 일일 변환을 만듭니다.

변환 SQLX 파일 만들기

파일 창에서 definitions/ 옆에 있는 더보기 메뉴를 클릭한 다음 파일 만들기를 선택합니다.
파일 경로 추가 필드에 definitions/daily_performance.sqlx를 입력합니다.
파일 만들기를 클릭합니다.

변환 SQLX 파일 정의

파일 창에서 definitions/ 디렉터리를 펼칩니다.

daily_performance.sqlx를 선택한 다음 다음 쿼리를 입력합니다.

    config {
        type: "table",
        schema: "reporting",
        tags: ["daily", "google_ads"]
    }
    SELECT
        date,
        campaign_id,
        campaign_name,
    SUM(clicks) AS total_clicks
    FROM
        `ads_Campaign_CUSTOMER_ID`
    GROUP BY
        date,
        campaign_id,
        campaign_name
        ORDER BY
        date DESC

변경사항 커밋 및 푸시

개발 작업공간에서 변경사항을 적용한 후 다음 단계에 따라 이러한 변경사항을 저장소에 커밋하고 푸시할 수 있습니다.

marketing-tutorial-workspace 작업공간에서 변경사항 1개 커밋을 클릭합니다.
새 커밋 창의 커밋 메시지 추가 필드에 커밋 설명을 입력합니다.
모든 변경사항 커밋을 클릭합니다.
marketing-tutorial-workspace 작업공간에서 기본 브랜치로 푸시를 클릭합니다.

변경사항이 저장소에 성공적으로 푸시되면 Workspace is up to date 메시지가 표시됩니다.

데이터 변환 예약

데이터 변환 파일을 정의한 후 데이터 변환을 예약합니다.

프로덕션 출시 만들기

Dataform의 프로덕션 버전은 데이터 변환 결과로 환경이 일관되게 업데이트되도록 합니다. 다음 단계에서는 데이터 변환을 저장할 marketing-tutorial-repository 저장소의 main 브랜치를 지정하는 방법을 보여줍니다.

Google Cloud 콘솔에서 Dataform 페이지로 이동합니다.

Dataform 페이지로 이동
marketing-tutorial-repository을 선택합니다.
버전 및 예약 탭을 클릭합니다.
프로덕션 버전 만들기를 클릭합니다.
출시 구성 만들기 창에서 다음 설정을 구성합니다.
1. 출시 ID 필드에 transformations을 입력합니다.
2. Git commitish 필드에 기본값 main을 그대로 둡니다.
3. 일정 빈도 섹션에서 주문형을 선택합니다.
만들기를 클릭합니다.

워크플로 구성 만들기

프로덕션 버전을 만든 후 저장소에서 지정된 일정에 따라 데이터 변환을 실행하는 워크플로 구성을 만들 수 있습니다. 다음 단계는 transformations 파일에서 일일 변환을 예약하는 방법을 보여줍니다.

Google Cloud 콘솔에서 Dataform 페이지로 이동합니다.

Dataform 페이지로 이동
marketing-tutorial-repository을 선택합니다.
버전 및 예약 탭을 클릭합니다.
워크플로 구성 섹션에서 만들기를 클릭합니다.
워크플로 구성 만들기 창의 구성 ID 필드에 transformations을 입력합니다.
출시 구성 메뉴에서 transformations을 선택합니다.
인증에서 사용자 인증 정보로 실행을 선택합니다.

일정 빈도 섹션에서 다음을 수행합니다.

1. Select **Repeat**.
1. For **Repeats**, select `Daily`.
1. For **At time**, enter `10:00 AM`.
1. For **Timezone**, select `Coordinated Universal Time (UTC)`.

태그 선택을 클릭합니다.
실행할 태그 선택 필드에서 일별을 선택합니다.
만들기를 클릭합니다.

생성한 워크플로 구성은 transformations 출시 구성에서 만든 최신 컴파일 결과 전체를 실행합니다.

삭제

이 페이지에서 사용한 리소스 비용이 Google Cloud 계정에 청구되지 않도록 하려면 다음 단계를 수행합니다.

BigQuery에서 만든 데이터 세트 삭제

BigQuery 애셋에 대한 요금이 청구되지 않도록 dataform이라는 데이터 세트를 삭제합니다.

Google Cloud 콘솔에서 BigQuery 페이지로 이동합니다.

BigQuery로 이동
탐색기 패널에서 프로젝트를 확장하고 dataform을 선택합니다.
작업 메뉴를 클릭한 후 삭제를 클릭합니다.
데이터 세트 삭제 대화상자의 필드에 delete를 입력한 후 삭제를 클릭합니다.

Dataform 개발 작업공간 및 구성 삭제

Dataform 개발 작업공간을 만드는 데 비용은 발생하지 않지만 개발 작업공간을 삭제하려면 다음 단계를 수행하면 됩니다.

Google Cloud 콘솔에서 Dataform 페이지로 이동합니다.

Dataform으로 이동
quickstart-repository을 클릭합니다.
출시 및 예약 탭을 클릭합니다.
출시 구성 섹션에서 production 구성 옆에 있는 더보기 메뉴를 클릭한 다음 삭제를 클릭합니다.
워크플로 구성 섹션에서 transformations 구성 옆에 있는 더보기 메뉴를 클릭한 다음 삭제를 클릭합니다.
개발 작업공간 탭에서 quickstart-workspace 옆에 있는 더보기 메뉴를 클릭한 후 삭제를 선택합니다.
삭제를 클릭하여 확인합니다.

Dataform 저장소 삭제

Dataform 저장소를 만드는 데 비용은 발생하지 않지만 저장소를 삭제하려면 다음 단계를 수행하면 됩니다.

Google Cloud 콘솔에서 Dataform 페이지로 이동합니다.

Dataform으로 이동
quickstart-repository까지 더보기 메뉴를 클릭한 다음 삭제를 선택합니다.
저장소 삭제 창에서 저장소 이름을 입력하여 삭제를 확인합니다.
삭제를 클릭하여 확인합니다.