Best Practice for Data Prep

[Ankit_Chaudhary]

Hi

I am looking for some best practices for data prep especially while mapping XDM data from one field to another field.

If anyone have any interesting use cases please share.

[kautuk_sahni]

@Pradeep_Kumar_Srivastav@Jennifer_Kunz@narendragandhi@shuaib27811@Abishek_mathav12345678@user48

Kindly take a moment to review this question and share your valuable insights. Your expertise would be greatly appreciated!

[brekrut]

Hello @Ankit_Chaudhary 

It depends on the data which is in the source to the target.  

I refer back to the data prep functions and determine which is best when mapping source to target with the data prep functions.

https://experienceleague.adobe.com/en/docs/experience-platform/data-prep/functions

[Jennifer_Kunz · Accepted solution]

I don't know about "best practices" as I'm still muddling through figuring it out myself. If you're simply mapping one XDM field to another, then it is pretty straightforward. Things get trickier once you start using calculated fields. Some tips:
I will say, at least in my own experience, I've found that leaving things UNescaped works best, despite what documentation says.
Adding a full JSON object each time you open data prep is a pain, but it is necessary if you want to preview how your calculated fields are working. 

Your question was pretty open-ended so I'll just show some calculated fields I set up in the past, though whether or not they were the BEST way to do a thing is up for debate. These were all clearly for an AEP-to-Analytics implementation, so sorry for the lack of variety :

• If eventType is one thing, increment an analytics event by 1 *:
  iif(xdm.eventType.equals("articleComplete"),1,"")
• If pageType is one thing, increment an analytics event by 1 *:
  iif(xdm.web.webPageDetails.pageType.equals("Article"),1,"")
• Join an array to make a pipe-delimited string
  join("|",xdm._tenantID.users.profile.feeds.topicName)
• If articlePrimaryTopic has a value, use it, otherwise use topicFollowed: iif(matches_regex(xdm.web.webPageDetails._tenantID.attributes.articlePrimaryTopic,".*"),xdm.web.webPageDetails._tenantID.attributes.articlePrimaryTopic,xdm._tenantID.users.profile.events.topicFollowed)
• Devs passed the wrong date format, I needed to fix it:
  format(date(xdm.web.webPageDetails._tenantID.attributes.articlePublishDate,"MMM dd, yyyy"),"yyyy-MM-dd")
• If error message has a value, increment our error event 1 *:
  iif(matches_regex(xdm._tenantID.errors.errorMessage, ".*[a-z].*"),1,"")
• If either the pagename started with Article, or the pagetype was “article”, increment our Article Viewed event 1 *:
  iif(xdm.web.webPageDetails.pageType && xdm.web.webPageDetails.pageType.equals("Article"),1,iif(matches_regex(lower(xdm.web.webPageDetails.name),"^article:.*"),1,""))
• Stick the eventType into the prop we usually use for Launch property/rule information (I highly recommend everyone do something like this with their eventtype):
  "Mobile App : " + xdm.eventType
• We were trying to match the web data, which would have page names like “Topic Landing Page”. The Mobile devs were setting “TopicLandingPage”. So we did this to add spaces:
  iif(matches_regex(xdm.web.webPageDetails.name, ".* .*"),xdm.web.webPageDetails.name,join(" ",extract_regex(xdm.web.webPageDetails.name,"[A-Z][a-z]*")))
  
  

(I'm not proud we had to do a lot of these transformations- in a happier world, we'd have gone back to the devs and had them fix things. "Correcting" data in data prep should be a last resort, IMO). 

*A lot of them have asterisks because I'm still wondering if I regret doing it this way. The problem with the approach I took is that I told all those iif statements to either return "1" or ""... but unfortunately, returning "" throws "source field was null" errors in Assurance. If you have it return "0" instead, then when you look at your analytics event field it will have something like "event1=0,event2=0,event3=0" etc, for every event you've given a "0" to. With 20+ events it gets really ugly. So if I did it again, I'd need to choose between the harmless but annoying AEP "source field was null" errors, or a big long ugly events string. Neither is ideal (I'm open to suggestions if there is a better way).

[sirisha_ganti]

If I need to extract a string from the string version of a set of key value pairs, how can I do it?

Example -

"{\"userContextDoubleValue\":32624.62,\"valueNumber\":30311.48,\"ucStringValue\":\"Sample value\",\"fieldName\":\"Sample value\",\"userContextIntegerValue\":5521,\"userContextDateValue\":\"2018-01-12\",\"valueDate\":\"2018-11-12T20:20:39+00:00\",\"valueString\":\"Sample value\",\"ucDateValue\":\"2018-11-12T20:20:39+00:00\",\"ucDoubleValue\":32225.57,\"valueDouble\":18612.33,\"ucIntegerValue\":28996,\"codesArrBeta\":[17302],\"userContextStringValue\":\"Sample value\"}"

If I need to extract the string "Sample value" (corresponding to valueString), only if the "fieldName" is "Sample value"?

The output should be "Sample value". 

"{\"userContextDoubleValue\":32624.62,\"valueNumber\":30311.48,\"ucStringValue\":\"Sample value\",\"fieldName\":\"Sample value2\",\"userContextIntegerValue\":5521,\"userContextDateValue\":\"2018-01-12\",\"valueDate\":\"2018-11-12T20:20:39+00:00\",\"valueString\":\"Sample value\",\"ucDateValue\":\"2018-11-12T20:20:39+00:00\",\"ucDoubleValue\":32225.57,\"valueDouble\":18612.33,\"ucIntegerValue\":28996,\"codesArrBeta\":[17302],\"userContextStringValue\":\"Sample value\"}"

for the above string, I should get no output, also if I have more than one fieldName in the string, I should still be able to extract only for "Sample value".

"{\"userContextDoubleValue\":32624.62,\"valueNumber\":30311.48,\"ucStringValue\":\"Sample value\",\"fieldName\":\"Sample value2\",\"userContextIntegerValue\":5521,\"userContextDateValue\":\"2018-01-12\",\"valueDate\":\"2018-11-12T20:20:39+00:00\",\"valueString\":\"Sample value\",\"ucDateValue\":\"2018-11-12T20:20:39+00:00\",\"ucDoubleValue\":32225.57,\"valueDouble\":18612.33,\"ucIntegerValue\":28996,\"codesArrBeta\":[17302],\"userContextStringValue\":\"Sample value\"};{\"userContextDoubleValue\":32624.62,\"valueNumber\":30311.48,\"ucStringValue\":\"Sample value\",\"fieldName\":\"Sample value2\",\"userContextIntegerValue\":5521,\"userContextDateValue\":\"2018-01-12\",\"valueDate\":\"2018-11-12T20:20:39+00:00\",\"valueString\":\"xyz\",\"ucDateValue\":\"2018-11-12T20:20:39+00:00\",\"ucDoubleValue\":32225.57,\"valueDouble\":18612.33,\"ucIntegerValue\":28996,\"codesArrBeta\":[17302],\"userContextStringValue\":\"Sample value\"}"

should return "Sample value"