google analytics automation with big...

Google Analytics Automation With

Big Query

Simon Chapman

02 July 2015

Acronyms

• GA = Google Analytics • BQ = Big Query • API = Application Programming Interface • SQL = Structured Query Language

Automating GA

• About me

Automating GA

• About me • About Thomas Cook Group

Automating GA

• About me • About Thomas Cook Group • About this session

Automating GA

• About me • About Thomas Cook Group • About this session

• What is possible (exciting stuff)

Automating GA

• About me • About Thomas Cook Group • About this session

• What is possible (exciting stuff) • The BQ schema (not exciting stuff)

Automating GA

• About me • About Thomas Cook Group • About this session

• What is possible (exciting stuff) • The BQ schema (not exciting stuff) • The results (very exciting stuff)

What is possible

2010: customer IDs were a grey area

What is possible

2010: customer IDs were a grey area 2011: sampled data everywhere

What is possible

2010: customer IDs were a grey area 2011: sampled data everywhere 2012: unsampled reporting

What is possible

2010: customer IDs were a grey area 2011: sampled data everywhere 2012: unsampled reporting 2013: Big Query

What is possible

2010: customer IDs were a grey area 2011: sampled data everywhere 2012: unsampled reporting 2013: Big Query 2014: automating BQ reporting

What is possible

2010: customer IDs were a grey area 2011: sampled data everywhere 2012: unsampled reporting 2013: Big Query 2014: automating BQ reporting 2015: outbound targeted emails

What is possible

What is possible

What is possible

Data

Reporting Customisation BQ

What is possible

BQ

Table1 Table2 Table3 Table4

Dataset

What is possible

BQ

API

Query Load Export Copy

Job

What is possible

API Read file

SELECT fullVisitorId

, visitNumber

, totals.visits

, totals.hits

, trafficSource.source

, trafficSource.medium

, device.deviceCategory

, customDimensions.index

, customDimensions.value

FROM [dataset.table]

LIMIT 10

Query

Job

Parameters:

• Source data

• Query

• Results destination

What is possible

Read file

Query 01 filename

Query 02 filename

Query 03 filename

Query 04 filename

Query 05 filename

Query 06 filename

Query 07 filename

Query 08 filename

Query 09 filename

Query 10 filename

Query 01 Job 01

Query 02 Job 02

Query 03 Job 03

API

What is possible

• Reporting • Visualisation • Analysis • Integration

What is possible

In summary…

GA BQ Schema

Non-repeated

Repeated Session level Hit level

totals trafficSource device

session

customDimensions

GA BQ Schema

fullVisitorId

visitNumber

totals.visits trafficSource.source device.deviceCategory customDimensions.index

totals.pageviews trafficSource.medium customDimensions.value

totals trafficSource device

session

customDimensions

GA BQ Schema

fullVisitorId

visitNumber

totals.visits trafficSource.source device.deviceCategory customDimensions.index

totals.pageviews trafficSource.medium customDimensions.value

totals trafficSource device

session

customDimensions

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

GA BQ Schema

SELECT fullVisitorId

, visitNumber

, totals.visits

, totals.hits

, trafficSource.source

, trafficSource.medium

, device.deviceCategory

, customDimensions.index

, customDimensions.value

FROM [dataset.table]

LIMIT 10

GA BQ Schema

Custom Dimensions:

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

GA BQ Schema

Custom Dimensions: 6 = Server (session scope)

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

GA BQ Schema

Row fullVisitorId visitNumber totals.visits totals.hits trafficSource.source trafficSource.medium device.deviceCategory customDimensions.index customDimensions.value

1 8335169515827510000 1 1 5 google organic tablet 6 empty

2 8335186669923520000 18 1 6 google organic desktop 6 app110.tcw.catn.com

3 8335186669923520000 19 1 28 google organic desktop 6 app110.tcw.catn.com

4 8335186669923520000 20 1 18 google organic desktop 6 app110.tcw.catn.com

5 8335186669923520000 21 1 1 google organic desktop 6 app110.tcw.catn.com

6 8335225844323600000 1 1 6 google cpc mobile 6 empty

7 8335260715163060000 1 1 13 yahoo organic mobile 6 607997-srv202.eceit.net

8 8335311765144230000 2 1 12 google organic desktop 6 app110.tcw.catn.com

9 8335311765144230000 3 1 1 google organic desktop 6 app110.tcw.catn.com

10 8335311765144230000 4 1 5 google organic desktop 6 app110.tcw.catn.com

Custom Dimensions: 6 = Server (session scope)

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

GA BQ Schema

Row fullVisitorId visitNumber totals.visits totals.hits trafficSource.source trafficSource.medium device.deviceCategory customDimensions.index customDimensions.value

1 8335169515827510000 1 1 5 google organic tablet 6 empty

2 8335186669923520000 18 1 6 google organic desktop 6 app110.tcw.catn.com

3 8335186669923520000 19 1 28 google organic desktop 6 app110.tcw.catn.com

4 8335186669923520000 20 1 18 google organic desktop 6 app110.tcw.catn.com

5 8335186669923520000 21 1 1 google organic desktop 6 app110.tcw.catn.com

6 8335225844323600000 1 1 6 google cpc mobile 6 empty

7 8335260715163060000 1 1 13 yahoo organic mobile 6 607997-srv202.eceit.net

8 8335311765144230000 2 1 12 google organic desktop 6 app110.tcw.catn.com

9 8335311765144230000 3 1 1 google organic desktop 6 app110.tcw.catn.com

10 8335311765144230000 4 1 5 google organic desktop 6 app110.tcw.catn.com

Custom Dimensions: 6 = Server (session scope)

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

GA BQ Schema

Custom Dimensions: 6 = Server (session scope)

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

GA BQ Schema

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

GA BQ Schema

Row fullVisitorId visitNumber totals.visits totals.hits trafficSource.source trafficSource.medium device.deviceCategory customDimensions.index customDimensions.value

1 6130261119992881099 1 1 1 (direct) (none) tablet 14 f47a7633-32ef-e197-f5a4-422097508d32

2 6130277539652806858 1 1 2 google organic mobile 6 app110.tcw.catn.com

3 6130291760289571604 1 1 55 google organic desktop 6 app110.tcw.catn.com

4 6130323856580164952 1 1 4 aol organic desktop 6 app110.tcw.catn.com

5 6130326622539110857 1 1 13 google organic tablet 6 empty

6 6130326622539110857 1 1 13 google organic tablet 14 7d40ee2f-4b6b-9bdc-2e12-3c0b79a9723d

7 6130337196748591896 1 1 14 yahoo organic desktop 6 empty

8 6130337196748591896 1 1 14 yahoo organic desktop 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

9 6130337196748591896 2 1 7 yahoo organic desktop 6 empty

10 6130337196748591896 2 1 7 yahoo organic desktop 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

11 6130339241153012821 1 1 4 (direct) (none) mobile 6 app110.tcw.catn.com

12 6130339241153012821 1 1 4 (direct) (none) mobile 14 9bd5a75c-51f1-d82a-0e9f-a9fc89306bd5

13 6130339241153012821 2 1 2 (direct) (none) mobile 6 app110.tcw.catn.com

14 6130387520880384798 1 1 2 (direct) (none) tablet 6 app110.tcw.catn.com

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

GA BQ Schema

Row fullVisitorId visitNumber totals.visits totals.hits trafficSource.source trafficSource.medium device.deviceCategory customDimensions.index customDimensions.value

1 6130261119992881099 1 1 1 (direct) (none) tablet 14 f47a7633-32ef-e197-f5a4-422097508d32

2 6130277539652806858 1 1 2 google organic mobile 6 app110.tcw.catn.com

3 6130291760289571604 1 1 55 google organic desktop 6 app110.tcw.catn.com

4 6130323856580164952 1 1 4 aol organic desktop 6 app110.tcw.catn.com

5 6130326622539110857 1 1 13 google organic tablet 6 empty

6 6130326622539110857 1 1 13 google organic tablet 14 7d40ee2f-4b6b-9bdc-2e12-3c0b79a9723d

7 6130337196748591896 1 1 14 yahoo organic desktop 6 empty

8 6130337196748591896 1 1 14 yahoo organic desktop 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

9 6130337196748591896 2 1 7 yahoo organic desktop 6 empty

10 6130337196748591896 2 1 7 yahoo organic desktop 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

11 6130339241153012821 1 1 4 (direct) (none) mobile 6 app110.tcw.catn.com

12 6130339241153012821 1 1 4 (direct) (none) mobile 14 9bd5a75c-51f1-d82a-0e9f-a9fc89306bd5

13 6130339241153012821 2 1 2 (direct) (none) mobile 6 app110.tcw.catn.com

14 6130387520880384798 1 1 2 (direct) (none) tablet 6 app110.tcw.catn.com

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

GA BQ Schema

Record Row fullVisitorId visitNumber totals.visits totals.hits trafficSource.source trafficSource.medium device.deviceCategory customDimensions.index customDimensions.value

1 1 6130261119992881099 1 1 1 (direct) (none) tablet 14 f47a7633-32ef-e197-f5a4-422097508d32

2 2 6130277539652806858 1 1 2 google organic mobile 6 app110.tcw.catn.com

3 3 6130291760289571604 1 1 55 google organic desktop 6 app110.tcw.catn.com

4 4 6130323856580164952 1 1 4 aol organic desktop 6 app110.tcw.catn.com

5 6130326622539110857 1 1 13 google organic tablet 6 empty

6 6130326622539110857 1 1 13 google organic tablet 14 7d40ee2f-4b6b-9bdc-2e12-3c0b79a9723d

7 6130337196748591896 1 1 14 yahoo organic desktop 6 empty

8 6130337196748591896 1 1 14 yahoo organic desktop 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

9 6130337196748591896 2 1 7 yahoo organic desktop 6 empty

10 6130337196748591896 2 1 7 yahoo organic desktop 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

11 6130339241153012821 1 1 4 (direct) (none) mobile 6 app110.tcw.catn.com

12 6130339241153012821 1 1 4 (direct) (none) mobile 14 9bd5a75c-51f1-d82a-0e9f-a9fc89306bd5

9 13 6130339241153012821 2 1 2 (direct) (none) mobile 6 app110.tcw.catn.com

10 14 6130387520880384798 1 1 2 (direct) (none) tablet 6 app110.tcw.catn.com

5

6

7

8

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

GA BQ Schema

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

Record Row fullVisitorId visitNumber totals.visits totals.hits trafficSource.source trafficSource.medium device.deviceCategory customDimensions.index customDimensions.value

1 1 6130261119992881099 1 1 1 (direct) (none) tablet 14 f47a7633-32ef-e197-f5a4-422097508d32

2 2 6130277539652806858 1 1 2 google organic mobile 6 app110.tcw.catn.com

3 3 6130291760289571604 1 1 55 google organic desktop 6 app110.tcw.catn.com

4 4 6130323856580164952 1 1 4 aol organic desktop 6 app110.tcw.catn.com

5 6130326622539110857 1 1 13 google organic tablet 6 empty

6 6130326622539110857 1 1 13 google organic tablet 14 7d40ee2f-4b6b-9bdc-2e12-3c0b79a9723d

7 6130337196748591896 1 1 14 yahoo organic desktop 6 empty

8 6130337196748591896 1 1 14 yahoo organic desktop 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

9 6130337196748591896 2 1 7 yahoo organic desktop 6 empty

10 6130337196748591896 2 1 7 yahoo organic desktop 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

11 6130339241153012821 1 1 4 (direct) (none) mobile 6 app110.tcw.catn.com

12 6130339241153012821 1 1 4 (direct) (none) mobile 14 9bd5a75c-51f1-d82a-0e9f-a9fc89306bd5

9 13 6130339241153012821 2 1 2 (direct) (none) mobile 6 app110.tcw.catn.com

10 14 6130387520880384798 1 1 2 (direct) (none) tablet 6 app110.tcw.catn.com

5

6

7

8

GA BQ Schema

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

Record Row fullVisitorId visitNumber totals.visits totals.hits trafficSource.source trafficSource.medium device.deviceCategory customDimensions.index customDimensions.value

1 1 6130261119992881099 1 1 1 (direct) (none) tablet 14 f47a7633-32ef-e197-f5a4-422097508d32

2 2 6130277539652806858 1 1 2 google organic mobile 6 app110.tcw.catn.com

3 3 6130291760289571604 1 1 55 google organic desktop 6 app110.tcw.catn.com

4 4 6130323856580164952 1 1 4 aol organic desktop 6 app110.tcw.catn.com

5 6 empty

6 14 7d40ee2f-4b6b-9bdc-2e12-3c0b79a9723d

7 6 empty

8 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

9 6 empty

10 14 e7ed0ceb-8812-f7f0-5cb7-b74cc76775c4

11 6 app110.tcw.catn.com

12 14 9bd5a75c-51f1-d82a-0e9f-a9fc89306bd5

9 13 6130339241153012821 2 1 2 (direct) (none) mobile 6 app110.tcw.catn.com

10 14 6130387520880384798 1 1 2 (direct) (none) tablet 6 app110.tcw.catn.com

6130339241153012821 1

7

8

5

6

13

14

7

4

google

yahoo

yahoo

(direct)

1

1

1

1

organic

organic

organic

(none)

tablet

desktop

desktop

mobile

6130326622539110857 1

6130337196748591896 1

6130337196748591896 2

GA BQ Schema

fullVisitorId

visitNumber

totals.visits trafficSource.source device.deviceCategory customDimensions.index

totals.pageviews trafficSource.medium customDimensions.value

totals trafficSource device

session

customDimensions

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

GA BQ Schema

fullVisitorId

visitNumber

totals.visits trafficSource.source device.deviceCategory customDimensions.index hits.hitNumber

totals.pageviews trafficSource.medium customDimensions.value

totals trafficSource device

session

customDimensions hits

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value, hits.hitNumber

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

GA BQ Schema

fullVisitorId

visitNumber

totals.visits trafficSource.source device.deviceCategory customDimensions.index hits.hitNumber

totals.pageviews trafficSource.medium customDimensions.value

totals trafficSource device

session

customDimensions hits

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value, hits.hitNumber

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

GA BQ Schema

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value, hits.hitNumber

FROM [dataset.table]

LIMIT 10

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope)

GA BQ Schema

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope)

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, customDimensions.index,

customDimensions.value, hits.hitNumber

FROM [dataset.table]

LIMIT 10

GA BQ Schema

fullVisitorId

visitNumber

totals.visits trafficSource.source device.deviceCategory hits.hitNumber

totals.pageviews trafficSource.medium

hits.customDimensions.index

hits.customDimensions.value

totals trafficSource device

session

hits

hits.customDimensions

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, hits.hitNumber,

hits.customDimensions.index, hits.customDimensions.value

FROM [dataset.table]

LIMIT 2

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope)

GA BQ Schema

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, hits.hitNumber,

hits.customDimensions.index, hits.customDimensions.value

FROM [dataset.table]

LIMIT 2

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope)

Record Row fullVisitorId visitNumber totals

.visits

totals

.hits

trafficSource.

source

trafficSource.

medium

device.

deviceCategory

hits.

hitNumber

hits.

customDimensions.

index

hits.

customDimensions.

value

1 1601049288368706138 4 1 1 yahoo organic tablet 1 1 Birmingham

2 1601049288368706138 4 1 1 yahoo organic tablet 1 6 app110.tcw.catn.com

3 1601119700070139668 1 1 2 google cpc desktop 1 1 LGW

4 1601119700070139668 1 1 2 google cpc desktop 1 6 app110.tcw.catn.com

5 1601119700070139668 1 1 2 google cpc desktop 2 6 app110.tcw.catn.com

1

2

GA BQ Schema

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, hits.hitNumber,

hits.customDimensions.index, hits.customDimensions.value

FROM [dataset.table]

LIMIT 2

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope)

Record Row fullVisitorId visitNumber totals

.visits

totals

.hits

trafficSource.

source

trafficSource.

medium

device.

deviceCategory

hits.

hitNumber

hits.

customDimensions.

index

hits.

customDimensions.

value

1 1 Birmingham

2 6 app110.tcw.catn.com

3 1 LGW

4 6 app110.tcw.catn.com

5 2 6 app110.tcw.catn.com

1 1601049288368706138 4 1 1 yahoo organic tablet 1

cpc desktop1

2 1601119700070139668 1 1 2 google

GA BQ Schema

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, hits.hitNumber,

hits.customDimensions.index, hits.customDimensions.value

FROM [dataset.table]

LIMIT 2

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope)

Record Row fullVisitorId visitNumber totals

.visits

totals

.hits

trafficSource.

source

trafficSource.

medium

device.

deviceCategory

hits.

hitNumber

hits.

customDimensions.

index

hits.

customDimensions.

value

1 1 Birmingham

2 6 app110.tcw.catn.com

3 1 LGW

4 6 app110.tcw.catn.com

5 2 6 app110.tcw.catn.com

1

1 1 2 google cpc desktop1

1

2

4 1 1 yahoo1601049288368706138

1601119700070139668

organic tablet

GA BQ Schema

Non-repeated

Repeated Session level Hit level

GA BQ Schema

Non-repeated

Repeated Session level Hit level

GA BQ Schema

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, hits.hitNumber,

hits.customDimensions.index, hits.customDimensions.value

FROM [dataset.table]

LIMIT 2

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope)

GA BQ Schema

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope) 2 = Destination (hit scope)

SELECT fullVisitorId, visitNumber, totals.visits, totals.hits, trafficSource.source,

trafficSource.medium, device.deviceCategory, hits.hitNumber,

hits.customDimensions.index, hits.customDimensions.value

FROM [dataset.table]

LIMIT 2

GA BQ Schema

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope) 2 = Destination (hit scope)

Record Row fullVisitorId visitNumber totals

.visits

totals

.hits

trafficSource.

source

trafficSource.

medium

device.

deviceCategory

hits.

hitNumber

hits.

customDimensions.

index

hits.

customDimensions.

value

1 2589003092003712819 11 1 4 google organic desktop 1 1 Any Airport

2 2589003092003712819 11 1 4 google organic desktop 1 2 Any destination

3 2589003092003712819 11 1 4 google organic desktop 1 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

4 2589003092003712819 11 1 4 google organic desktop 2 null null

5 2589003092003712819 11 1 4 google organic desktop 3 1 Any Airport

6 2589003092003712819 11 1 4 google organic desktop 3 2 Any destination

7 2589003092003712819 11 1 4 google organic desktop 3 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

8 2589003092003712819 11 1 4 google organic desktop 4 null null

9 2589004891599999905 1 1 4 google organic tablet 1 1 any

10 2589004891599999905 1 1 4 google organic tablet 1 2 any

11 2589004891599999905 1 1 4 google organic tablet 1 6 app110.tcw.catn.com

12 2589004891599999905 1 1 4 google organic tablet 2 1 any

13 2589004891599999905 1 1 4 google organic tablet 2 2 any

14 2589004891599999905 1 1 4 google organic tablet 2 6 app110.tcw.catn.com

15 2589004891599999905 1 1 4 google organic tablet 3 1 any

16 2589004891599999905 1 1 4 google organic tablet 3 2 any

17 2589004891599999905 1 1 4 google organic tablet 3 6 app110.tcw.catn.com

18 2589004891599999905 1 1 4 google organic tablet 4 1 any

19 2589004891599999905 1 1 4 google organic tablet 4 2 any

20 2589004891599999905 1 1 4 google organic tablet 4 6 app110.tcw.catn.com

1

2

GA BQ Schema

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope) 2 = Destination (hit scope)

Record Row fullVisitorId visitNumber totals

.visits

totals

.hits

trafficSource.

source

trafficSource.

medium

device.

deviceCategory

hits.

hitNumber

hits.

customDimensions.

index

hits.

customDimensions.

value

1 1 Any Airport

2 2 Any destination

3 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

4 2 null null

5 1 Any Airport

6 2 Any destination

7 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

8 4 null null

9 1 any

10 2 any

11 6 app110.tcw.catn.com

12 1 any

13 2 any

14 6 app110.tcw.catn.com

15 1 any

16 2 any

17 6 app110.tcw.catn.com

18 1 any

19 2 any

20 6 app110.tcw.catn.com

2

2589003092003712819

2589004891599999905

11 1 41 google organic desktop

3

4

1

3

1 1 4 google organic tablet

1

2

GA BQ Schema

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope) 2 = Destination (hit scope)

Record Row fullVisitorId visitNumber totals

.visits

totals

.hits

trafficSource.

source

trafficSource.

medium

device.

deviceCategory

hits.

hitNumber

hits.

customDimensions.

index

hits.

customDimensions.

value

1 1 Any Airport

2 2 Any destination

3 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

4 2 null null

5 1 Any Airport

6 2 Any destination

7 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

8 4 null null

9 1 any

10 2 any

11 6 app110.tcw.catn.com

12 1 any

13 2 any

14 6 app110.tcw.catn.com

15 1 any

16 2 any

17 6 app110.tcw.catn.com

18 1 any

19 2 any

20 6 app110.tcw.catn.com

2 2589004891599999905 1 1 4 google

1 2589003092003712819 11 1 4 google

organic tablet

1

2

3

4

organic desktop

1

3

GA BQ Schema

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope) 2 = Destination (hit scope)

Record Row fullVisitorId visitNumber totals

.visits

totals

.hits

trafficSource.

source

trafficSource.

medium

device.

deviceCategory

hits.

hitNumber

hits.

customDimensions.

index

hits.

customDimensions.

value

1 1 Any Airport

2 2 Any destination

3 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

4 2 null null

5 1 Any Airport

6 2 Any destination

7 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

8 4 null null

9 1 any

10 2 any

11 6 app110.tcw.catn.com

12 1 any

13 2 any

14 6 app110.tcw.catn.com

15 1 any

16 2 any

17 6 app110.tcw.catn.com

18 1 any

19 2 any

20 6 app110.tcw.catn.com

2 2589004891599999905 1 1 4 google

1 2589003092003712819 11 1 4 google

organic tablet

1

2

3

4

organic desktop

1

3

GA BQ Schema

Custom Dimensions: 6 = Server (session scope) 14 = SessionId (session scope) 1 = DepartureAirport (hit scope) 2 = Destination (hit scope)

Record Row fullVisitorId visitNumber totals

.visits

totals

.hits

trafficSource.

source

trafficSource.

medium

device.

deviceCategory

hits.

hitNumber

hits.

customDimensions.

index

hits.

customDimensions.

value

1 1 Any Airport

2 2 Any destination

3 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

4 2 null null

5 1 Any Airport

6 2 Any destination

7 14 ea5cc42a-4cd7-7a45-6495-4e0262955167

8 4 null null

9 1 any

10 2 any

11 6 app110.tcw.catn.com

12 1 any

13 2 any

14 6 app110.tcw.catn.com

15 1 any

16 2 any

17 6 app110.tcw.catn.com

18 1 any

19 2 any

20 6 app110.tcw.catn.com

2 2589004891599999905 1 1 4 google

1 2589003092003712819 11 1 4 google

organic tablet

1

2

3

4

organic desktop

1

3

GA BQ Schema

The results

All visitors: • Didn’t sign into the site No action • No email on record No action • No permission to contact No action • Didn’t view a product Generic email • Bought a product No action • Viewed product but didn’t buy Custom email

The results

Generic emails have a 35% bounce rate www.ving.no 17% -18pp www.ving.se 6% -29pp www.spies.dk 16% -19pp www.tjarborg.fi 10% -25pp

The results

Generic emails have a 0.8% conversion rate www.ving.no 2.4% +1.6pp www.ving.se 3.7% +2.9pp www.spies.dk 1.2% +0.4pp www.tjarborg.fi 0.4% -0.4pp

Thank you