# 12.4 Load data from BigQuery into Adobe Experience Platform

## Objectives

* Map BigQuery data to an XDM schema
* Load BigQuery data into Adobe Experience Platform
* Become familiar with the BigQuery Source Connector UI

## Before you start

After exercise 12.3, you should have this page open in Adobe Experience Platform:

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-73535c6d6aba6013a495e7d13e164e3a02163977%2Fdatasets.png?alt=media)

**If you have it open, continue with exercise 12.4.1.**

**If you don't have it open, go to** [**Adobe Experience Platform**](https://experience.adobe.com/platform/home)**.**

In the left menu, go to Sources. You'll then see the **Sources** homepage. In the **Sources** menu, click on **Databases**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-ec15682b6778dae842f67ecd952fc3535a46ab07%2Fsourceshome.png?alt=media)

Select the **Google BigQuery** Source Connector and click on **+ Configure**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-d35a7450e8f1f5da8d4d204ef063cb512464587c%2Fbq.png?alt=media)

You'll then see the Google BigQuery Account selection screen.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-0d4e6c474304f1a123b93f0eae22741bf8cf0e61%2F0-c.png?alt=media)

Select your account and click **Next**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-e2093d8c811f6ba8e7459d7555af2eadb36573d6%2F0-d.png?alt=media)

You'll then see the **Add data** view.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-73535c6d6aba6013a495e7d13e164e3a02163977%2Fdatasets.png?alt=media)

## 12.4.1 BigQuery Table Selection

In the **Add data** view, select your BigQuery dataset.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-73535c6d6aba6013a495e7d13e164e3a02163977%2Fdatasets.png?alt=media)

You can now see a sample data preview of the Google Analytics data in BigQuery.

Click **Next**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-075ab1f0208259be7218516211d1623d7f2f2fa3%2F3.png?alt=media)

## 12.4.2 XDM mapping

You'll now see this:

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-d919a059e0bcd80f68799b243a35293488e214e9%2Fxdm4a.png?alt=media)

You now have to either create a new dataset or select an existing dataset to load the Google Analytics data into. For this exercise, a dataset and schema have already been created. You do not need to create a new schema or dataset.

Select **Existing dataset**. Open the dropdown menu to select a dataset. Search for the dataset named `Demo System - Event Dataset for BigQuery (Global v1.1)` and select it. Click **Next**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-157aa0cee840cc71c10ed86cf9e7cdb7d150f08b%2Fxdm6.png?alt=media)

Scroll down. You now need to map every **Source Field** from Google Analytics/BigQuery to an XDM **Target Field**, field by field.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-2c7ca1b9fe9c5f56a977393dc2dd44e806b9f33d%2Fxdm8.png?alt=media)

Use the below mapping table for this exercise.

| Source Field                 | Target Field                                    |
| ---------------------------- | ----------------------------------------------- |
| **\_id**                     | \_id                                            |
| **\_id**                     | channel.\_id                                    |
| timeStamp                    | timestamp                                       |
| GA\_ID                       | `--aepTenantId--`.identification.core.gaid      |
| customerID                   | `--aepTenantId--`.identification.core.loyaltyId |
| Page                         | web.webPageDetails.name                         |
| Device                       | device.type                                     |
| Browser                      | environment.browserDetails.vendor               |
| MarketingChannel             | marketing.trackingCode                          |
| TrafficSource                | channel.typeAtSource                            |
| TrafficMedium                | channel.mediaType                               |
| TransactionID                | commerce.order.payments.transactionID           |
| Ecommerce\_Action\_Type      | eventType                                       |
| Pageviews                    | web.webPageDetails.pageViews.value              |
| Unique\_Purchases            | commerce.purchases.value                        |
| Product\_Detail\_Views       | commerce.productViews.value                     |
| Adds\_To\_Cart               | commerce.productListAdds.value                  |
| Product\_Removes\_From\_Cart | commerce.productListRemovals.value              |
| Product\_Checkouts           | commerce.checkouts.value                        |

After copying and pasting the above mapping into the Adobe Experience Platform UI, please verify if you don't see any errors due to typos or leading/trailing spaces.

You now have a **Mapping** like this one:

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-2df44aef50eedaacf1721ce2ebeb4ec62ddfad3a%2Fxdm34.png?alt=media)

The source fields **GA\_ID** and **customerID** are mapped to an Identifier in this XDM Schema. This will allow you to enrich Google Analytics data (web/app behavior data) with other datasets such as Loyalty or Call Center-data.

Click **Next**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-7b9ede1daff0a10884f43764295faf11f953fc5d%2F38.png?alt=media)

## 12.4.3 Connection and the data ingestion scheduling

You'll now see the **Scheduling** tab:

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-9a7f067eaeb40fe162987e0c0908b3acd0d4e2dd%2Fxdm38a.png?alt=media)

In the **Scheduling** tab, you are able to define a frequency for the data ingestion process for this **Mapping** and data.

As you're using demo data in Google BigQuery that won't be refreshed, there's no real need for setting a schedule in this exercise. You do have to select something, and to avoid too many useless data ingestion processes, you need to set the frequency like this:

* Frequency: **Week**
* Interval: **200**

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-d583599b6301631a2141b4771e4b14c2884b7556%2F39.png?alt=media)

**Important**: be sure you activate the **Backfill** switch.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-60ea34ba6d1d630e41e2a2a477660319ce75fb19%2F39a.png?alt=media)

Last but not least, you must define a **delta** field.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-dc7219053487e8efea12c952ade620553e03b929%2F36.png?alt=media)

The **delta** field is used to schedule the connection and upload only new rows that come into your BigQuery dataset. A delta field is typically always a timestamp column. So for future scheduled data ingestions, only the rows with a new, more recent timestamp will be ingested.

Select **timeStamp** as the delta field.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-dbd65b7a59883ce8b63c18af83d00d5af37ba887%2F37.png?alt=media)

You now have this.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-3cfb80753f376081b5dd84e3bc63b1077475a97c%2Fxdm37a.png?alt=media)

Click **Next**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-b180c3f7aaa7da6e1901c271adb9b49984ef399d%2F42.png?alt=media)

## 12.4.4 Review and launch connection

In the **Dataset flow detail** view. you need to name your connection, which will help you to find it later.

Please use this naming convention:

| Field             | Naming                                         | Example                                            |
| ----------------- | ---------------------------------------------- | -------------------------------------------------- |
| Dataset flow name | DataFlow - ldap - BigQuery Website Interaction | DataFlow - vangeluw - BigQuery Website Interaction |
| Description       | DataFlow - ldap - BigQuery Website Interaction | DataFlow - vangeluw - BigQuery Website Interaction |

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-95c59ea9017a4ad807698993655e57996105c602%2Fxdm44.png?alt=media)

Click **Next**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-7e624001a1561ff77bef276510991d7dc9b6a2b2%2F45.png?alt=media)

You now see a detailed overview of your connection. Make sure everything is correct before you continue, as some settings can't be changed anymore afterwards, like for instance the XDM mapping.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-6e95231be7c1caa82bd254cfb9c2da2007f49213%2Fxdm46.png?alt=media)

Click **Finish**.

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-f9befcc8a69432f4441cc9e8508b1024deff867e%2Ffinish.png?alt=media)

Setting up the connection may take some time, so don't worry if you see this:

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-fe1bbe66168c0684ece01ea835ddf83702f87353%2F47.png?alt=media)

Once the connection has been created, you'll see this:

![demo](https://858372621-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FpBC8bA57il8Sj47B7QPJ%2Fuploads%2Fgit-blob-1558a125c3982ddc68f917cd83ac8941d9be5eea%2Fxdm48.png?alt=media)

You're now ready to continue with the next exercise, in which you'll use Customer Journey Analytics to build powerful visualizations on top of Google Analytics data.