Expand my Community achievements bar.

SOLVED

CJA - Multi values concatenated in a field, lookup challenge

Avatar

Level 5

Hi,

 

In our website, multiple product SKUs can be selected and we would like to track and analyze the popularity of the multi-select combinations. We are capturing the SKUs selected in datalayer like below,

 

"SKU001|SKU002|SKU003" when user selected 3 products

"SKU004|SKU005" when 2 products are selected

 

The SKU ID to Product name mapping CSV is ready and uploaded to AEP as Lookup dataset (there are ~10K SKU IDs). 

 

In the workspace report, we would like to have the field showing the product names like,

 

"Product Name 001|Product Name 002|Product Name 003"

"Product Name 004|Product Name 005"

 

We tried with Derived Field but seems it's not possible to do something like "Split the field, perform Lookup per items and concatenate them back".

 

Would like to see if there is any advice on this? 

 

Thanks,

John

 

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Yes! if cant do on client for any reason. I'm not sure data prep can support combinations of functions as of now. Another option is using Query Service 

View solution in original post

6 Replies

Avatar

Community Advisor

@Hey_John  I'm sure you have already tried available options for new derived field.  I'd use object array to solve this in CJA

https://experienceleague.adobe.com/en/docs/analytics-platform/using/cja-usecases/complex-data/object...

Avatar

Level 5

Hi @Asheesh_Pandey 

 

Thanks for your reply. I've review the documentation on array of objects. It's powerful to store the list structure of objects. However, our challenge is on reporting of the combinations of the object fields.

 

Below is a simplifed example based on the documentation,

 

{
  "ID": "1",
  "product": [
    {
      "SKU": "123",
      "units": 1
    },
    {
      "SKU": "456",
      "units": 1
    },
{
      "SKU": "789",
      "units": 1
    }
  ],
  "timestamp": 1534219229
}
 
{
  "ID": "2",
  "product": [
    {
      "SKU": "ABC",
      "units": 1
    },
    {
      "SKU": "DEF",
      "units": 1
    }
  ],
  "timestamp": 1534219230
}
 
What I am trying to achieve, is to have a report like below, so that we can understand the popularity of product combinations,
 
SKU selected in a Single EventNo. of Event
123+456+7891
ABC+DEF1
 
Is there anyway to somehow concat all the field values in an array?
 
Thanks,
John
 
 

Avatar

Community Advisor

Hm, may create a new field dimension say "bundle" with in the array of object and set these product combinations using "product name" in place of SKU.  This new dimension "bundle"  would enable to report these combinations in desired reporting structure while maintaining unique SKU reporting in ID, Name dimensions.

Thanks,

Asheesh

Avatar

Level 5

Hi @Asheesh_Pandey 

 

Do you mean set the product names combinations to the "bundle" field in Adobe Launch client side code?

 

For some reasons, we can only capture the product SKU IDs. The IDs are now sent to Edge in object array. To show the product names combination, we tried on the Data Prep level but there is no lookup in Data Prep. On the CJA level, the Lookup on product names works fine for object array, but we cannot find a way to join the values in the array and set it to the "bundle" field.

 

Thanks.

Avatar

Correct answer by
Community Advisor

Yes! if cant do on client for any reason. I'm not sure data prep can support combinations of functions as of now. Another option is using Query Service 

Avatar

Level 5

Hi @Asheesh_Pandey 

 

Thanks for the direction on Query Services. Studied the documentation and was thinking to use a scheduled SQL to retrieve the array values, do a concat and write it back to a XDM field. However, it seems SQL Update is not supported, would like to see any advice or if I'm missing something?

 

Thanks,

John