# 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.adobesandbox.com/comprehensive-technical-tutorial-archive/module12/ex4.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.