마케팅 분석 데이터의 ELT 파이프라인 빌드

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

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

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

시작하기 전에

계정에 로그인합니다. Google Cloud 를 처음 사용하는 경우 Google Cloud, 계정을 만들고 Google 제품의 실제 성능을 평가해 보세요. 신규 고객에게는 워크로드를 실행, 테스트, 배포하는 데 사용할 수 있는 $300의 무료 크레딧이 제공됩니다.

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 role (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 role (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을 입력합니다.
일정 옵션 섹션에서 다음을 수행합니다.
- 반복 빈도에서 일을 선택합니다.
- 시간에 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 저장소를 만듭니다.

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

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

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

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

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

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

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

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

이제 marketing-tutorial-workspace 개발 작업공간이 definitions 디렉터리의 *first_view.sqlx 및 *second_view.sqlx라는 두 가지 예시 파일과 함께 개발 작업공간 탭의 marketing-tutorial-repository 저장소에 표시됩니다.

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

Google Ads 테이블을 테이블 소스로 선언

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

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

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

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

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 작업공간에서 기본 브랜치로 푸시 를 클릭합니다.

변경사항이 저장소에 푸시되면 작업공간이 최신 상태임 메시지가 표시됩니다.

데이터 변환 예약

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

프로덕션 출시 만들기

Dataform의 프로덕션 출시를 사용하면 데이터 변환 결과로 환경이 지속적으로 업데이트됩니다. 다음 단계에서는 데이터 변환을 저장할 marketing-tutorial-repository 저장소의 main 브랜치를 지정하는 방법을 보여줍니다.

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

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

워크플로 구성 만들기

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

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

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 개발 작업공간을 만드는 데 비용은 발생하지 않지만 개발 작업공간을 삭제하려면 다음 단계를 수행하면 됩니다.

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

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

Dataform 저장소 삭제

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

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

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