[GCP] Google Analytics BigQuery Clickstream Data → Data Warehouse

Google Analytics BigQuery Clickstream Data를 Data Warehouse로 내보내는 방법

많은 IT인이 Google BigQuery에 익숙하지 않으며 이를 사용하는 방법에 익숙 하지 않습니다. 또한 Google 애널리틱스 (GA) 프리미엄 데이터가 BigQuery에 저장되는 구조가 언뜻보기에는 분명하지 않을 수 있습니다.

아래의 지침을 따르면 지식을 빠르게 습득하고 귀중한 스트림 데이터를 여러분의 데이터웨어 하우스로 가져올 수 있는 ETL 프로세스에 보다 신속하게 접근할 수 있습니다

Google Analytics BigQuery 클릭 스트림 데이터를 내보내는 이유

한 마디로 말하면 풍부한 데이터 입니다 하지만 여기에 세 가지 구체적인 포인트가 있습니다

1) 비즈니스 로직
채널 (소스, 매체, 캠페인 등의 조합)을 결정하는 사용자 정의 규칙이 있고이 새로운 필드로 클릭 스트림 데이터를 업데이트 해야한다고 가정 해 보겠습니다. Google BigQuery는 현재 UPDATE 또는 DELETE 작업을 지원하지 않습니다. 일부 사용 사례의 경우 문제가 될 수 있습니다.

2)Google 애널리틱스 분석 분석 데이터가 여러 테이블에 분산되어 있고 중첩 된 구조이기 때문에 BigQuery 데이터는 약간 성가신 일입니다. 중첩은 계층 적 데이터 (중복 감소)를 표현하는 데 유용하지만 일반 SQL 사용자에게 고유 한 문제를 제기 할 수 있습니다.

3) 데이터 통합이미 자체 데이터웨어 하우스가있을 수 있으며 자체 인프라를 사용하여 클릭 스트림 데이터를 분석하려고합니다. 또한이 데이터를 다른 데이터 소스 (CRM, 전자 메일, 사회 등)와 결합하여 비즈니스 성과 향상을위한 상황을 향상시킬 수 있습니다.

아래에서는성에 대한 열쇠를 제공하여 BigQuery에서 클릭 스트림 데이터를 내보내고 자신의 데이터웨어 하우스로 가져 오기위한 여정을 시작합니다.

BigQuery에서 Google 애널리틱스 클릭 스트림 데이터를 내보내는 방법

Google 애널리틱스 프리미엄 은 clickstream 데이터를 Google BigQuery에 매일 내보내고 중복을 피하고 공간을 절약하기 위해 중첩 된 JSON 형식을 사용하여 저장합니다. Google에서 사용 하는 중첩 스키마 를 살펴보십시오  .

내보내려면 첫 번째 단계는 중첩하지 않고 지정된 날짜의 모든 조회를 선택하는 SQL 쿼리를 만드는 것입니다. 한 가지주의 할 점은 Google 웹 로그 분석 클릭 스트림 데이터에 히트 타임 스탬프가있는 열이 없기 때문에 초로 변환 된 적중 시간 (hits.time)을 추가하고 visitStartTime (visitStartTime + hits.time / 1000)을 추가하여 생성해야합니다. ). 이 새로운 열의 이름은 hit_timestamp입니다.

아래는 SQL 쿼리의 스 니펫입니다. BlastAM GitHub Repository 에서 전체 SQL 쿼리를 찾을 수 있습니다 .

단계별 지침

1. Google 애널리틱스 BigQuery 프로젝트로 이동하여 새 데이터 집합을 만듭니다 (이 예에서는이 ETL을 호출합니다 ). 이 데이터 세트는 클릭 스트림 데이터 내보내기를 보유합니다.
2. GitHub 에서 SQL 쿼리를 가져옵니다 .
3. Google 애널리틱스 구현을 맞춤화하기 위해 SQL 검색어 를 수정하십시오 (측정 기준, 향상된 전자 상거래 등의 입력란 사용자 정의). 현재 구현에서 사용중인 측정 기준 / 측정 항목보다 많은 측정 항목 / 측정 항목을 추가하지 마십시오.
4. 쿼리 창에 쿼리 를 붙여 넣습니다 .
5. 'Show Option'을 선택하고 대상 테이블을 구성하십시오 (이 예제에서는 sessions_20150101 ). '1 단계'에서 작성한 데이터 세트를 선택하고 테이블의 이름을 적절하게 지정하십시오. 테스트하는 동안 SQL 쿼리에 'LIMIT'을 추가하여 반환되는 행 수를 제한 할 수 있습니다.
6. 쿼리가 끝나면 새 데이터 세트의 새 테이블에서 병합 된 클릭 스트림 데이터를 갖게됩니다. 분석을 위해 다른 테이블을이 테이블에 추가하거나 다른 플랫폼의 저장 또는 분석을 위해 CSV로 내보낼 수 있습니다.

Google 애널리틱스 클릭 스트림 데이터를 자체 데이터웨어 하우스로 내보내기

1. 같은 프로젝트에서 Google Storage에 버킷을 만듭니다.
2. 표를 Google Storage로 내보내 분석 용으로 다운로드하거나 데이터웨어 하우스에로드하십시오. 파일 패턴은 파일을 배수로 나누고 gzip을 사용하여 압축합니다. 이것은 Hive (Hadoop)로 분석 할 때 유용합니다. 형식 자체는 CSV입니다.

내 보낸 데이터가 이제 Google 저장 용량에 있습니다.

이제 데이터웨어 하우스 데이터를 최신 상태로 유지하기 위해 위에 설명 된 프로세스를 자동화하는 ETL 작업을 작성할 수 있습니다.