Combining data from two data schemas

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

16-03-2018

Hi,

I am trying to query data from two data schemas and combine all records that exist in both but I'm struggling.

  • I have a query that looks at all profiles where the email address is not empty.
  • I have another query that looks at the address scheme where the address is not empty.

I then want to take both sets of data and to combine them to produce one set of data that consists of all profile email addresses who also exist within the address schema.

I have tried using an intersection activity but it falls over because the two types of inbound events are incompatible.

Can anyone advise on the best way to achieve this?

Many thanks,

Bob

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

florentlb

Total Posts

1.1K

Likes

234

Correct Answer

239

Avatar

florentlb

Total Posts

1.1K

Likes

234

Correct Answer

239
florentlb

19-04-2018

Hi Bob,

Sorry for answering so late.

I've tried the following:

1469158_pastedImage_5.png

In this workflow, I query all addresses (dimension AddressStatus) for which the address field is not empty (Query). Then, I add a reconciliation activity, where I only fill the Identification tab as follows:

1469168_pastedImage_6.png

As you can see here:

  • I reconciliate the addresses from the Query with the Profiles schema, and specifically the email and address fields.
  • Unidentified source lines are ignored (so only identified records - which are found in both schemas - are kept).

The data of the transition after the Identification is one the Profiles dimension and corresponds to email addresses that were found in the address schema.

Would this work for you?

Florent

Answers (14)

Answers (14)

Avatar

Avatar

davidk21713691

Avatar

davidk21713691

davidk21713691

09-04-2018

Hi Bob,

You have to bring both transition to the one reconciliation activity. i'm my previous screenshot i want to keep users last transaction and retrieve their card information.

- In properties tab, You have to select the primary set (the one you want to keep)

1461464_pastedImage_0.png

- In advanced relations tab, you will define the reconciliation criteria

1461465_pastedImage_1.png

- in additional data tab, you will define the variables you want to retrieve for the second set9in my case i retrieved card information).

1461468_pastedImage_2.png

Hope that helps.

Thanks

David

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

19-04-2018

Florent, you hero! Thank you very much, that has worked perfectly.

I had actually just about managed to work out a way around it thanks to David (thank you David!), but my workflow included about 10 activities. This allows me to do it in only 2 and was staring me in the face the whole time.

I appreciate all the help.

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

09-04-2018

Thanks David. I've tried adding a Reconciliation activity but I'm not sure I'm configuring it correctly.

Do I bring both sets of data into the one reconciliation activity? If so do I then need to specify both dimensions in the 'List of relations with other dimensions' section?

It's driving me crazy trying to get this to work so a big high five is awaiting anyone who can help me resolve this!

Thanks,

Bob

Avatar

Avatar

davidk21713691

Avatar

davidk21713691

davidk21713691

29-03-2018

Hi,

You can use reconciliation or enrichment activity then you will need to do a split to keep profile reconciled.

1453910_pastedImage_0.png

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

29-03-2018

Bump. Is anyone able to help with this issue?

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

26-03-2018

Hi Amit,

Just checking in - Are you able to advise on the error in my workflow?

Thanks,

Bob

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

20-03-2018

Hi Amit,

Thanks for your advice. I have set up the following but I am still experiencing problems where it says the ('head:profile' and 'head:addressStatus') are incompatible. Please let me know if I've misunderstood any of the steps.

1446480_pastedImage_0.png

Avatar

Avatar

Amit_Kumar

MVP

Avatar

Amit_Kumar

MVP

Amit_Kumar
MVP

19-03-2018

Hi Bob,

Use Adobe Campaign Help | Exclusion

1. Query all profiles with the nonEmpty email address. Q1

2. Get all blacklisted address  Q2

3. Use a https://helpx.adobe.com/campaign/standard/automating/using/fork.html to split query Q1 in two parts i.e Q1A and Q1B

4. Use Exclusion with Q1A and Q2 with Q1 as the primary target and email address and a filter will give you E1

5. Use Intersection activity with E1 and Q1B

6. this intersection will give you a list of Quarantined profiles

Alternatively, add a new column in profiles i.e blacklistedFlag and update this flag from your nms:address  query using an email address as a key.

Regards,

Amit

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

19-03-2018

Hi Florent,

All of our blacklisted and quarantined contacts are listed within the nms:address schema. All of these contacts are automatically suppressed from all of our communications, so periodically I want to run a workflow to query how many of those contacts have active profiles and remove those profiles from our database.

I assumed the easiest way to achieve this would be by bringing the data of two queries together. I just don't know how to combine the data of the two where the same value is present in both. If you think there's an alternative solution I'm all ears!

Appreciate the advice.

Avatar

Avatar

florentlb

Total Posts

1.1K

Likes

234

Correct Answer

239

Avatar

florentlb

Total Posts

1.1K

Likes

234

Correct Answer

239
florentlb

19-03-2018

Hi Bob,

To make sure I understand, why do you need to query the second schema? From what I read you simply want to have all profiles who have their email address filled, but I'm not sure of why you are considering that other schema.

Florent

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

19-03-2018

No worries, appreciate your help anyway.

I'll wait for florentlb​ to swoop in with the answer!​

Avatar

Avatar

menno_74

Avatar

menno_74

menno_74

19-03-2018

Hi Bob,

Indeed: change dimension is part of campaign classic and seems not to be available in campaign standard.

My guess is that you need to match the data somehow

Perhaps you can find some answers here.

Adobe Campaign Help | Reconciliation https://helpx.adobe.com/campaign/standard/automating/using/read-audience.html

Adobe Campaign Help | Read audience

Avatar

Avatar

bobbyjss

MVP

Avatar

bobbyjss

MVP

bobbyjss
MVP

19-03-2018

Thanks for your response. I failed to mention that I am using Adobe Campaign Standard - does your suggestion still apply as I'm not familiar with the 'Change dimension' option?

Thanks,

Bob

Avatar

Avatar

menno_74

Avatar

menno_74

menno_74

16-03-2018

Hi Bob,

Assuming u are using nms:recipient   and nms:address: you can use a "Change dimension" from the 'target' repository on address to change its dimension to match it to the dimension of recipient.

Kind Regards,

Menno