5.4 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

• Informatica: https://apse1.dm-ap.informaticacloud.com/diUI/products/integrationDesign/main/home
• Adobe Experience Platform: https://experience.adobe.com/platform/

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.

You’ll then see the Informatica homepage.

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

You’ll then see this popup.

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

Click Create to start creating your mapping workflow.

You’ll then see this screen:

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.

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

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

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

Click Delete on the popup window.

Your workflow now looks like this.

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

In the Properties window, click Source.

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

You’ll then see this.

Click Select….

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.

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.

You’ll then see this.

Click Formatting Options to define the structure of the template.

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

Accept the default settings and click OK.

On the Properties screen, click Preview Data.

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

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.

You should now have this Design:

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.

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

You’ll then see this.

Click Select….

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.

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.

You’ll then see this.

Click Formatting Options to define the structure of the template.

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

Accept the default settings and click OK.

On the Properties screen, click Preview Data.

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

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:

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.

Drag and drop the Joiner object on the canvas.

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.

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

Let’s define the Properties of the Joiner now.

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.

You’ll see this window now.

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

Enter the prefix m_.

Click OK.

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.

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

You’ll then see this.

Click the little + icon.

You’ll then see a Join Condition appear.

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

m_email (string) = email (string)

When done, click Save.

Your 2 Sources are now joined with each other.

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

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.

Drag and drop the Filter object onto the canvas.

Next, have a look at the Properties window.

In the left menu, go to Filter.

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

Change the Filter Condition to Advanced.

Click the Define Filter Condition button.

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

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.

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.

Drag and drop the Expression object onto the canvas.

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.

You’ll then see this popup:

In the popup, define the Field Name and Type:

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

Click OK to save your field.

You’ll then see this:

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'))

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.

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.

Connect the Expression object to the target object.

Have a look at the Properties windows.

In the left menu, go to Target. In the Connection-dropdown, select Experience Platform International (Adobe Experience Platform).

You’ll then have this:

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

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

Select the dataset AEP Demo - ETL Profile Data.

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

Map the Output to the Schema attributes as per below:

Field	Element Name
m_credit_score	--aepTenantId--.etl_profile_attributes.creditScore
m_income	--aepTenantId--.etl_profile_attributes.income
fav_designer	--aepTenantId--.etl_customer_preferences.favDesigner
fav_shop	--aepTenantId--.etl_customer_preferences.favBrand
fav_color	--aepTenantId--.etl_customer_preferences.favColor
m_email	--aepTenantId--.identification.emailId

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

Click Save.

You now have a finished workflow which can be Run.

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

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

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)

Click Run.

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.

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

You’ll then see something like this:

Click the Refresh button to see updates.

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

Go to Adobe Experience Platform, to Datasets and enter the search term etl. You’ll then see these datasets:

Open the dataset AEP Demo - ETL Profile Data.

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

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. You’ll also see the output of your Expression/Transformation in the column --aepTenantId--.etl_profile_attributes.income.

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