12.4 Load data from BigQuery into Adobe Experience Platform

Ingest & Analyze Google Analytics data in Adobe Experience Platform with the BigQuery Source Connector - 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

If you have it open, continue with exercise 12.4.1.

If you don't have it open, go to Adobe Experience Platform.

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

demo

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

demo

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

demo

Select your account and click Next.

demo

You'll then see the Add data view.

demo

12.4.1 BigQuery Table Selection

In the Add data view, select your BigQuery dataset.

demo

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

Click Next.

demo

12.4.2 XDM mapping

You'll now see this:

demo

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

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

demo

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

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

12.4.3 Connection and the data ingestion scheduling

You'll now see the Scheduling tab:

demo

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

Important: be sure you activate the Backfill switch.

demo

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

demo

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

You now have this.

demo

Click Next.

demo

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

Click Next.

demo

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

Click Finish.

demo

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

demo

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

demo

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.