구글 빅쿼리와 GA360을활용한 온오프데이터 연동

분석사례

September. 2017

IndexHow business can utilize GA 360 + Google BigQuery Integration

• Brief Introduction To Google Analytics 360 Suite

• GA 360 & BigQuery Integrations benefits

• User Case

-온오프연동분석

-데이터내재화

- 퀵리포팅및시각화

• BigQuery Basic Concept

• Datalicious Optima Hub Platform on Google Cloud Platform

3

Google Analytics 360 Suite when launched

Data Studio 360 Data Analysis and Visualisation

Tag Manager 360 Data Collection

Analytics 360

Customer

Insights

Attribution 360

Marketing

Measurement

Optimise 360

Site Testing and

Personalisation

Audience Center 360

Audience

Management

4

Go from insight to impact, faster

Analytics 360을통해발견된유의미한고객세그먼트를 Audience Center 360으로전송, 다양한통합마케팅캠페인의타겟팅조건으로삼고, 통합마케팅을통해유입된고객에게 Optimize 360을통해개인화된온사이트/인앱메시지를전달,Attribution 360 을통해마케팅활동의직접적성과뿐만아닌간접적 성과까지그기여도를분석하고, Data Studio 360 을통해이러한인사이트들이신속하고폭넓게조직내공유되는프로세스를지향합니다.

도출 된인사이트가 보다 신속히 비즈니스 성과로 귀결될 수있도록!!

GA 360 & BigQueryIntegrations Benefits

- Beauty of Raw Data Access

CRM

Call Center

Ads Data

Offline Marketing Cost Data

3rd Party Data

Leads Data

Vendor Data

2nd Party / Partner Data

POS data

CRM

Call Center

Ads Data

Offline Marketing Cost Data

3rd Party Data

Leads Data

Vendor Data

2nd Party / Partner Data

POS data

8

Premium Google stack integrations

Execution LayerExecution Layer

Offline data

Monetisation

All Major BMP’s

The “rest”: Customer surveys, ERP, bookkeeping, R&D

DM, CRM, call center & POS

Data Export

Campaign ManagerBid ManagerSearch

Ad ExchangeDFP

9

Using BigQuery & Integrating Data with Other System

Corporate data3rd party data Co-Workers

App EngineApp

Google SpreadsheetsMicrosoft Excel

Other BI Tools

Scalable Storage

Analyse InteractivelyMash it up

Securely Share /distribute the results

Share all your datain the cloud

10

Predictive analytics with GA360 & R

CRM

Online Subscriptions

11

Transactions plus behaviours

CRM Profile Site Behaviour

one-off collection of demographical data

age, gender, address, etc

customer lifecycle metrics and key dates

profitability, expiration, etc

predictive models based on data mining

propensity to buy, churn, etc

historical data from previous transactions

average order value, points, etc

tracking of purchase funnel stage

browsing, checkout, etc

tracking of content preferences

products, brands, features, etc

tracking of external campaign responses

search terms, referrers, etc

tracking of internal promotion responses

emails, internal search, etc

Updated Occasionally Updated Continuously

12

Using BigQuery with GA360

● Can export session and hit data from Google Analytics 360 account to BigQuery● Can associate only one view per property with a single bigquery project.● When integration is complete the GAP view ID becomes the dataset name in BigQuery.

Click

Query[EX] : select date, visitStartTime from

[77413617.ga_sessions_20140915] limit 100

13

Using BigQuery with DCM

● DCM Data transfer files○ NetworkAcitivity○ NetworkImpressions○ NetworkClicks

● Exported to google cloud storage bucket daily from DCM platform

● Can setup automatic data import to BigQuery

14

Firebase + BigQuery

Androidapp

Androidapp

with playstore

iOS app

Firebase analyticsdashboard

Google Bigquery앱당매시간 마다업로드또는 Conversion 이벤트 발생시

모든앱의로그를모아서 매시간마다업로드또는 Conversion이벤트발생시앱당매시간 마다업로드또는앱이백그라운드로전환되면 업로드

거의즉시

최대 24시간

● No Sampling, Raw Data Access

BigQuery Use-cases-온오프연동분석

16

Use Case – On-Offline Analytics

Combined online product browsing data with offline transactional data using BigQuery,

we can figure out the ratio of webrooming and specific behavior pattern and sales cycle between online browsing and

offline purchase, etcc

DataCollector

1st VisitPDP View

DataCollector

2nd VisitPDP View

Log-in

DataCollectorStore Visit

Online

Offline $$$

17

활용구문

온라인, 오프라인데이터

웹루밍데이터

Data Cleansing

BigQuery Use-cases-데이터내재화

19

Use Case – GA 360 데이터실시간스트리밍구현

Google Analytics360

Real time data collecting

20

Use Case – GA 360 data 내부 BO system 으로연동하는자바모듈개발

- 쿼리링크

- 18, 29라인의날짜를수정순서대로 TIMESTAMP(‘시작일’),TIMESTAMP(‘마지막날’)으로입력

21

Use Case – BigQuery Data Integration

Output report with one of the keys

Google AnalyticsAdobe AnalyticsSiteCore

Google BigQueryAdobe WarehouseBackend Analytics

Member IDAdobe Visitor IDSiteCore Cookie ID

Google Client ID

Adobe Visitor ID

SiteCore Cookie ID

BigQuery Use-cases-퀵리포팅및시각화

23

Use Case – Ad-hoc query support

Automated specific event performance monitoring using BigQuery

: Every event promoted different set of products, so to report the event performance, the marketers needed to create a

event page view and product purchase behavior sequence segment for each products. 20 products for 1 event means 20

segment creation for 1 report

24

Use Case – BigQuery Visualization

Datalicious can also use Bigquery to visualise your data in other platforms such as Datalicious OptimaHub.

Custom reporting and highly tailored reporting so you can get the most out of your GA360 Data.

Analysis of your custom marketing tools Understand how users interact with your site via Events

BigQuery Basic

Concept

26

GA360 + BigQuery 연결

Google Analytics > 관리 > 속성설정 > 모든제품 > BigQuery연결클릭

GA 360 고객은월 500달러상당의BigQuery용 Google Analytics 360 크레딧을이용가능

27

BigQuery User Interface

GA View ID

Test

GA 360 연동 시GA View ID 별데이터 생성됨

GA data table

28

BigQuery 쿼리실행

쿼리문 입력

쿼리문 결과

29

BigQuery Structure

Project

Manages billing, users and user

privileges

Contains one or more datasets, jobs,

and access control list

Dataset

Contains tables and views

Access Control applied to all the

tables and views in the dataset

z

Project - (billing, top-level Container)

Dataset (organization, access control)

Table (data with schema)

Job (query, import, export, copy)

30

BigQuery Structure

Table

Collection of columns and rows

Have schema

Views are supported

Tables can be external or federated

Job

Used to start all potentially long

running process (import, export),

Can be cancelled

z

Project - (billing, top-level Container)

Dataset (organization, access control)

Table (data with schema)

Job (query, import, export, copy)

31

Working With Bigquery

Data Import

● Can import csv,

json and avro file

● Schema is

mandatory

● csv doesn’t

support multi-

valued or nested

fields

● For large file

better to upload to

GCS first and

import from there

● Limit - 4GB

compressed

upload, 5TB

uncompressed

32

Working With Bigquery

Data Export

33

Working With Bigquery

Querying Table

34

Useful Links

활용예문

1. BigQuery Query Reference: https://cloud.google.com/bigquery/query-

reference#select

2.BigQuery Concepts: https://cloud.google.com/bigquery/docs/concepts

3.BigQuery Export Schema:

https://support.google.com/analytics/answer/3437719?hl=en

4.BigQuery Cookbook:

https://support.google.com/analytics/answer/4419694?hl=en&ref_topic

=3416089

Datalicious Optima Hub Platform on GCP

36

What do we Do?

People based, custom

media attribution with

OptimaHub

Re-sale, implementation

and ongoing support

within the GA360 Suite

of products

Enterprise level tag

management solution

with multi-platform

support

Data Strategy and

vendor agnostic support

& consultancy

CRM & SCV

Understanding

customers and

personalised experience

37

Strategic Partnerships

Exclusive* access to Facebook ID data -

Enabling people basedanalytics

Exclusive* access to Ads Data Hub - Enabling

enhanced performanceanalytics at the most

granular level

38

Cloud data warehouse

Media spend optimisation

D A T A L I C I O U S O P T I M A H U B P L A T F O R M

Socialmedia

Reporting & BI Tools

Websites, web applications

POS apps, beacons

Call tracking, live chat

Ad Servers Campaign Tools

Mobile applications

Data collection, standardisation,

enrichment, cross-device ID stitching

Purchase path: Media Attribution

modelling

Single customer view

Personalisation & campaigns

Campaign data (eDM, DM, display)

39

Inside Data Collector

Website

Google

Facebook

Email

Data Collector

Go HTTP Collectoron App Engine

Memcache on App Engine

Python Workers on App Engine

Raw data stored on Google Cloud Storage

Stream Processing

Ba

tch P

roce

ssing

Data Producer

Sun Young Kim Country Manager - Korea

sykim@datalicious.com+82 10 8425 1188