5.5 Ingest 2nd- and 3rd-party data into Adobe Experience Platform

In this exercise, you’ll learn how to import 2nd- and 3rd-party data into Informatica, join datasets and ingest transformed data into Adobe Experience Platform as Experience Events.

Learning Objectives

  • Learn how to load data in Informatica
  • Learn how to create a mapper workflow in Informatica.
  • Understand the process to join data sets, enrich data and ingest it in Platform.

Lab Resources

Lab Tasks

  • Load CSV files from your S3 bucket into Informatica for Offline Orders and Loyalty Program Profiles
  • Create a mapper workflow to join the above data sets, enrich and filter the data.
  • Run the job to ingest the data into Adobe Experience Platform

Business Context: Using Informatica to ingest 2nd and 3rd party data into Platform

Luma Retail fashion brand, has a partnership with Survey Corp which has agreed to share their latest survey results on people’s preferences for designers, colors, and brands. Luma Retail has also decided to buy some demographics data on a marketplace from Money Corp, providing details on people’s income and credit scores. By combining these two data sets Luma Retail is aiming to target their customer with more meaningful experiences based on their preferences as well as income.

5.4.1 Create Sources in a Mapping Workflow

In this exercise, you’ll load two CSV files from your S3 bucket into Informatica:

  • 3rdparty_data.csv
  • survey_data.csv

Go to https://apse1.dm-ap.informaticacloud.com/diUI/products/integrationDesign/main/home.

Login using the credentials that were sent to you by email.

ETL

You’ll then see the Informatica homepage.

ETL

On the Informatica homepage, click the + New… button.

ETL

You’ll then see this popup.

ETL

In the left menu in the popup, select Mappings. Next, select Mapping.

ETL

Click Create to start creating your mapping workflow.

ETL

You’ll then see this screen:

ETL

Let’s start by configuring the name of your mapping. For the name of your mapping, use LDAP - ex4. In this example, the name is vangeluw - ex4.

ETL

Click Save in the upper right corner of the screen to save your changes.

ETL

Next, let’s start the creation of your mapping workflow. Your workflow looks like this at the moment.

ETL

Let’s start by removing the Target object for the moment. Select the Target object and click the Delete icon.

ETL

Click Delete on the popup window.

ETL

Your workflow now looks like this.

ETL

Select the Source object. After selecting the Source object, you’ll see a Properties window at the bottom of your screen.

ETL

In the Properties window, click Source.

ETL

Open the Connection dropdown, locate your S3 - LDAP connection and select it.

ETL

You’ll then see this.

ETL

Click Select….

ETL

You’ll then see a popup window, which shows your S3 connection. In the Packages column, you’ll see your bucket name. Click your bucket name to select it.

ETL

After selecting your bucket name, you’ll see the four CSV files that you uploaded into your S3 bucket in exercise 1.

Select the file 3rdparty_data.csv and click OK.

ETL

You’ll then see this.

ETL

Click Formatting Options to define the structure of the template.

ETL

In the popup, change the Format Type from None to Delimited.

ETL

Accept the default settings and click OK.

ETL

On the Properties screen, click Preview Data.

ETL

You should then see a preview just like this one. Click Done to close the preview window.

ETL

As you can see in the preview, there are several empty lines so you’ll have to do some cleaning of the file before ingesting it into Adobe Experience Platform.

Next, you’ll set up a second Source object on the mapping-workflow.

Drag an drop the Source object from the left menu in the Design Overview onto the canvas.

ETL

You should now have this Design:

ETL

Select the second Source object. After selecting the second Source object, you’ll again see a Properties window at the bottom of your screen.

In the Properties window, click Source.

ETL

Open the Connection dropdown, locate your S3 - LDAP connection and select it.

ETL

You’ll then see this.

ETL

Click Select….

ETL

You’ll then see a popup window, which shows your S3 connection. In the Packages column, you’ll see your bucket name. Click your bucket name to select it.

ETL

After selecting your bucket name, you’ll see the four CSV files that you uploaded into your S3 bucket in exercise 1.

Select the file survey_data.csv and click OK.

ETL

You’ll then see this.

ETL

Click Formatting Options to define the structure of the template.

ETL

In the popup, change the Format Type from None to Delimited.

ETL

Accept the default settings and click OK.

ETL

On the Properties screen, click Preview Data.

ETL

You should then see a preview just like this one. Click Done to close the preview window.

ETL

You have now created the Source connectors required for this exercise!

5.4.2 Join Sources

In this exercise, you’ll join the above created Sources.

Your mapping workflow looks like this currently:

ETL

You now need to join those 2 datasets. The way to do that is using a Joiner. In the Design menu, scroll down until you see the Joiner object.

ETL

Drag and drop the Joiner object on the canvas.

ETL

Next, you have to connect the two Sources to the Joiner.

Click the orange + icon on the Joiner. You’ll now see a Master and a Detail node.

ETL

Connect Source to Master and Source 1 to detail as indicated below.

ETL

Let’s define the Properties of the Joiner now.

ETL

Go to the menu option Incoming Fields. You’ll see a notification message that certain fields from the 2 Sources have the same name. Let’s fix that first.

Click on Resolve Field Name Conflicts.

ETL

You’ll see this window now.

ETL

For Master > Source, open the dropdown list for Bulk Rename Options and select Prefix.

Enter the prefix m_.

Click OK.

ETL

In the Incoming Fields screen, you can now scroll down and you’ll see that all fields form the Master Source now have an m_ prefix and the error message is gone.

ETL

Next, you have to define the Join Condition. Click on Join Condition in the left menu.

You’ll then see this.

ETL

Click the little + icon.

You’ll then see a Join Condition appear.

ETL

Set the Join Type to Full Outer, and connect these 2 fields to each other:

m_email (string) = email (string)

ETL

When done, click Save.

ETL

Your 2 Sources are now joined with each other.

Don’t forget to click Save to save your mapping’s current state.

ETL

5.4.3 Filter Data

The next step is filtering data. Specifically, you need to remove potential empty lines like in the case of having an empty account_id.

In order to filter data, you need to add a Filter object onto the canvas. You can find the Filter object in the left menu on the Design workflow.

ETL

Drag and drop the Filter object onto the canvas.

ETL

Next, have a look at the Properties window.

ETL

In the left menu, go to Filter.

Click the + icon on the right side to add a Filter.

ETL

Change the Filter Condition to Advanced.

ETL

Click the Edit Filter Condition button.

ETL

In the Edit Filter-popup, paste this filter:
IIF(ISNULL(m_email),FALSE,TRUE)

ETL

Click OK to save your Filter.

You’ve now defined your Filter, let’s enrich your data.

Don’t forget to click Save to save your mapping’s current state.

ETL

5.4.4 Enrich Data

In the enrichment phase, you can add additional fields to your dataset or transform existing fields. In this example, we need to transform the existing field m_yearly_income. Luma Retail’s marketers shouldn’t see individual income numbers, instead they should see a class of income.
In this case, Luma Retail has decided that if someone’s yearly income is above 100000/year, that they should be classified as having a high income. If their yearly income is between 50000 and 100000/year, they should be classified as having a medium income and if a yearly income is below 50000, it’s classified as low.

To transform a field, you’ll be using an Expression so you need to add an Expression object onto the canvas. You can find the Expression object in the left menu on the Design workflow.

ETL

Drag and drop the Expression object onto the canvas.

ETL

Next, have a look at the Properties window.

In the left menu, go to Expression.

Click the + icon on the right side to add a Field/Expression.

ETL

You’ll then see this popup:

ETL

In the popup, define the Field Name and Type:

  • Field Name: m_income
  • Type: string
  • Precision: 256

ETL

Click OK to save your field.

You’ll then see this:

ETL

Click Configure…

In the Edit Expression-popup, paste this expression:
IIF(TO_INTEGER(m_yearly_income) > 100000, 'high', IIF(TO_INTEGER(m_yearly_income) > 50000, 'medium', 'low'))

ETL

Click OK to save your expression.

You’ve now defined your Expression, let’s output your data to Adobe Experience Platform.

Don’t forget to click Save to save your mapping’s current state.

ETL

5.4.5 Output Data to Target

The last step is to add the Target object to the workflow. From the left menu, drag and drop the Target object onto the canvas.

ETL

Connect the Expression object to the target object.

ETL

Have a look at the Properties windows.

ETL

In the left menu, go to Target. In the Connection-dropdown, select your Adobe Experience Platform connection.

ETL

You’ll then have this:

ETL

Click the Select button to select the Adobe Experience Platform-dataset to use.

Enter the search term ETL and click Search. You’ll then see these datasets being returned.

Select the dataset Demo System - Profile Dataset for ETL (Global v1.1).

ETL

In the left menu of the Properties window, go to Field Mapping.

ETL

Map the Output to the Schema attributes as per below:

Field Element Name
fav_designer --aepTenantId--.individualCharacteristics.retail.favoriteDesigner
fav_shop --aepTenantId--.individualCharacteristics.retail.favoriteFashionBrand
fav_color --aepTenantId--.individualCharacteristics.retail.favoriteColor
m_email --aepTenantId--.identification.core.email
m_id _id

Your Field Mapping should look similar to this (don’t forget about the mapping for m_email).

ETL

Click Save.

ETL

You now have a finished workflow which can be Run.

ETL

Click the Run button in the top right corner of the screen.

ETL

After 30 seconds, you’ll then see this popup. (Note: it can take a long time, please just wait)

ETL

You need to change the Runtime Environment to aepEmeaInformatica as indicated in the screenshot. (If you don’t select the correct Runtime Environment, your job won’t run successfully)

ETL

Click Run.

ETL

After 20-30 seconds, your Job will be executing.

You can review the status of your Job by going to the left menu option My Jobs.

ETL

Locate your Job in the list and click it to open it.

ETL

You’ll then see something like this:

ETL

Click the Refresh button to see updates.

ETL

Once your job has finished successfully, your data will be ingested in Adobe Experience Platform.

Log in to Adobe Experience Platform.

After logging in, you’ll land on the homepage of Adobe Experience Platform.

Data Ingestion

Before you continue, you need to select a sandbox. The sandbox to select is named --aepSandboxId--. You can do this by clicking the text Production Prod in the blue line on top of your screen.

Data Ingestion

After selecting the appropriate sandbox, you’ll see the screen change and now you’re in your dedicated sandbox.

Data Ingestion

Go to Datasets and enter the search term ETL. You’ll then see these datasets:

ETL

Open the dataset Demo System - Profile Dataset for ETL (Global v1.1).

ETL

Scroll down until you see the Batch IDs and locate your specific batch.

ETL

Write down the Batch ID as you’ll need it so submit completion of Module 5.

You can also preview the data that you ingested.

ETL

Congratulations, you have now successfully used Informatica to ingest Profile and Experience Event data into Adobe Experience Platform!

Next Step: Summary and benefits

Go Back to Module 5

Go Back to All Modules

On this page