Google Analytics와 Shopify 결합: BigQuery에서 전자상거래 데이터 웨어하우스 생성

게시 됨: 2023-02-15

Google Analytics는 귀하의 웹사이트를 방문하는 사람들의 사용자 행동을 이해할 수 있는 훌륭한 도구입니다. 반면 Shopify는 전자 상거래 관점에서 상세한 성능 범위를 제공하는 데 환상적입니다. 둘 다 자신이 하는 일에 훌륭하지만 Shopify와 Google Analytics(GA)의 데이터만 단독으로는 원하는 더 깊은 통찰력을 제공하지 못하는 지점이 있습니다. 각각 강력한 정보를 제공할 수 있는 능력이 있지만 데이터를 결합하여 얻을 수 있는 훨씬 더 높은 수준의 통찰력이 있습니다.

이 특별한 예에서 우리는 자연스럽고 건강한 개 사료를 생산하는 영국 회사인 Pooch & Mutt에서 친구들을 위해 어떻게 이것을 달성했는지 다룰 것입니다. Google Analytics에서 수집한 데이터를 Shopify의 전자상거래 데이터 및 Recharge의 구독 데이터와 함께 사용하고 이를 Bold의 기존 구독 데이터와 결합하여 통찰력을 향상시키고 싶었습니다. 이 다양한 데이터 소스를 사용하여 더 풍부하고 완전한 그림을 만들 수 있었습니다.

이 블로그에서는 이 데이터를 집계하는 방법에 대한 개요를 제공하고 데이터 기반 통찰력을 추출하고 시각화하기 위해 데이터를 결합하는 사고 프로세스를 간략하게 설명합니다. 구체적으로 다음 세 가지 질문에 어떻게 답했는지 설명하겠습니다.

어떤 채널이 구독 가입을 유도합니까?
GA 이상의 바스켓 조합; 가입자와 비구독자에게 가장 인기 있는 바스켓은 무엇이며 가장 수익성이 높은 바스켓은 무엇입니까?
사용자가 구독자가 되기까지 평균적으로 얼마나 많은 시간과 구매가 필요합니까?

방법론

이러한 질문에 답하기 전에 먼저 각 플랫폼의 모든 데이터를 중앙 위치로 수집하고 이 데이터를 수집하는 데 사용할 수 있는 테이블을 구축한 방법을 살펴보겠습니다.

데이터 구조

우리는 BigQuery(BQ)를 사용하여 데이터 집합 조합을 처리할 수 있는 다재다능함과 기능 때문에 데이터를 중앙 집중화하기로 했습니다. 아래의 순서도는 데이터와 이것이 달성된 방식 사이의 일반적인 연결을 강조 표시합니다.

땀

Stitch는 사전 구축된 데이터 웨어하우스에 데이터를 추출하고 로드할 수 있는 저렴한 도구입니다.

Shopify에 쉽게 연결하고 스티치를 통해 충전하여 원하는 테이블을 선택하기만 하면 됩니다. 이 경우 가장 중요한 테이블은 Recharge의 '고객' 및 '구독'과 Shopify의 '주문'입니다.

그런 다음 이 정보는 Stitch의 자동 스케줄링 프로세스를 통해 6시간마다 BQ와 동기화됩니다.

용감한

이전 구독자 데이터의 경우 최종 내보내기를 수행하여 Google 스프레드시트를 통해 BQ에 업로드했습니다.

구글 애널리틱스

여기에 Stitch를 사용할 수도 있지만 RStudio를 사용하여 이 데이터를 BQ로 가져오기로 했습니다. 이를 수행하는 방법에 대해 자세히 알아보려면 R에서 GA API를 사용하는 방법에 대한 자체 R Studio 전문가 Danny Smith의 블로그를 읽을 수 있습니다.

마스터 테이블 구축

BQ에 필요한 모든 데이터가 있으면 결합을 시작할 수 있습니다. 이를 위해 수행한 단계는 다음과 같습니다.

우리는 Bold의 레거시 구독자 데이터를 Recharge의 현재 구독자 데이터와 결합하고 정렬했습니다.
그런 다음 소스, 매체 및 채널과 함께 주문 ID(예: SPFYXXXXX)를 포함하는 GA의 전자상거래 데이터를 가져왔습니다.
Shopify 주문 데이터에 결합하기 전에 GA 테이블과 결합된 구독자 데이터를 기반으로 새 열을 만들었습니다. 이 경우 이러한 추가 열 중 일부가 포함됩니다.
1. '활성' 구독자 식별자
2. 원래 구독 날짜
3. 소스, 매체 및 주문 채널
4. 첫 번째 또는 반복 고객 주문
다음으로 이 새 데이터를 결합하려는 Shopify 주문 데이터에서 관련 필드를 선택했습니다. 몇 가지 주요 예는 다음과 같습니다.
1. 고객 ID
2. 주문 아이디
3. 주문 날짜
4. SKU
5. 항목 수량
6. 품목 가격
7. 총 주문 가격
8. 할인 코드
9. 할인 량
10. 태그 (구독자와 비구독자를 구분하기 위한 추가 수단으로 사용)
여기서 마지막 단계는 같은 순서로 다른 항목에 대한 중복 행을 더 쉽게 처리하기 위해 몇 개의 추가 열을 만드는 것이었습니다.
1. 아브지템프라이스
2. 평균 할인
3. 구독자 상태(태그 + Bold & Recharge 표에 있는 경우)

그런 다음 이 쿼리를 하루에 한 번 실행하도록 예약하고 결과를 추가 쿼리의 마스터 테이블로 사용했습니다. 이렇게 하면 Looker Studio 대시보드를 통해 요청할 때마다 업데이트되는 것이 아니라 예약된 경우에만 업데이트되는 정적 테이블이므로 전체 쿼리를 여러 번 실행할 필요가 없습니다.

인사이트

방법론을 다루고 필요한 모든 데이터를 얻기 위해 취한 단계를 설명했으므로 이제 이 데이터를 사용하여 질문에 답하는 방법에 대해 논의할 수 있습니다.

어떤 채널이 구독 가입을 유도합니까?

이를 계산하기 위해 첫 주문 날짜를 기준으로 '처음' 또는 '반복'을 반환하는 마스터 테이블에서 생성한 필드를 사용하여 고객의 첫 번째 주문을 살펴봤습니다.
마스터 테이블의 채널 그룹화 및 구독자 상태 추가 열을 사용하여 첫 구매 또는 반복 구매에서 구독자가 된 사람들의 첫 번째 주문과 GA에서 이들이 기여한 채널을 확인할 수 있습니다. .
구독자만 보는 단계를 제거하면 첫 구매를 주로 유도하는 채널도 확인할 수 있습니다.

GA 이상의 바스켓 조합; 가입자와 비구독자에게 가장 인기 있는 바스켓은 무엇이며 가장 수익성이 높은 바스켓은 무엇입니까?

STRING_AGG 함수를 사용하여 단일 주문의 다양한 제품을 ' | '. 이것은 본질적으로 각 주문에 대한 바구니를 생성합니다.
이 정보와 함께 각 항목의 수익을 '장바구니 총계'로 합산하여 모든 관련 정보가 포함된 주문당 행을 갖도록 했습니다.
물론 제품(맛, 크기 등) 간에는 많은 차이가 있으므로 기본 제품 바구니 조합을 보기 위해 이 차이를 제거한 열을 추가했습니다.
그런 다음 'ROW_NUMBER() OVER (PARTITION BY Transaction_ID) AS 식별자'를 사용하여 각 주문에 순차적인 값을 할당했습니다. 그런 다음 대시보드에서 여전히 모든 정보가 포함된 단일 행을 분리하는 필터로 사용되었습니다.

사용자가 구독자가 되기까지 평균적으로 얼마나 많은 시간과 구매가 필요합니까?

마스터 테이블을 사용하여 구독자였던 것으로 알고 있는 모든 고객 ID를 가져와 첫 구독 날짜를 잡았습니다.
그런 다음 최소 주문 날짜를 취하고 DATE_DIFF 함수를 사용하여 이 날짜 간의 차이를 '일'로 계산했습니다.
마지막 단계는 채널과 구독하기 전의 평균 일수를 가져와 고객이 일반적으로 구독자가 되기까지의 평균 일수를 보여주는 표를 만들었습니다.
고객이 구독자가 되기 전에 주문한 평균 수를 계산하기 위해 고유 주문 ID 수를 '구독자'이고 주문 날짜가 최소 구독 날짜보다 이전인 고유 고객 ID 수로 나누었습니다.

이는 이러한 보고서를 사용하여 생성한 보기의 일부 표면 수준 예일 뿐입니다. 데이터에는 무한한 가능성이 있습니다. 이 프로젝트의 다른 예로는 다양한 관점에서 고객 평생 가치를 살펴보고 할인 코드의 사용과 고객의 평생 행동에 미치는 영향을 평가하는 것이 있습니다. 이러한 통찰력을 통해 비즈니스 의사 결정을 보완하는 데 사용할 수 있습니다. 예를 들어 구독자가 되기 전에 평균 3번의 주문이 필요하다는 것을 알면 고객이 2번 구매한 후 구독자가 되도록 장려하는 제안 및 인센티브를 공유할 수 있습니다. 이 결과는 나중에 측정할 수 있습니다.

요약

이 사례 연구는 GA에서 Shopify에 이르기까지 여러 데이터 소스의 조합을 통해 얻을 수 있는 매우 가치 있는 데이터 기반 통찰력의 잠재력을 보여줍니다. 이 인프라가 생성되면 동적으로 유지 관리할 수 있으며 비즈니스의 다양한 측면에 대해 보고하는 데 사용할 수 있습니다.

이 블로그에서 답변한 질문이 귀하의 비즈니스에 관심이 있을 수 있는 질문과 유사한 경우 문의 양식을 작성하여 연락할 수 있습니다.