How to convert string values to row wise data?

[aagk123]

I want to convert the string values collected in evar as table format as shown below. How to do it?

[Jennifer_Dungan · Accepted solution]

So you have a single eVar passing multiple values ("inStock,inStock,outOfStock,outOfStock,outOfStock") which is associated to a single Revenue value? And you want to split and display only the unique values in the string ("inStock" or "outOfStock") as rows, but both associated to the same revenue value??

Is this a one time thing? Or does this need to be automated?

For hypothetical, I am going to assume automated, so let's say your value of eVar117 is in cell A1 in excel

You can use this formula to split the list into sorted and unique values:

=SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(",",,A1),,",",TRUE,0)))

So essentially:

Now, you could set up in the next column (H), something like 

=IF(H1<>"", $F$1, "")

As you copy the formula down, it will become "H1" the "H2" then "H3", etc, but "F1" will be locked, so:

But, I wonder if there is a better way to optimize your analytics so that you can use the data without all this manipulation, and having appropriate Revenue associated to each item.

[Jennifer_Dungan]

If you are talking about making this display in Adobe itself, then you will need to change and optimize your tracking.

[aagk123]

Yes I want to push the values to Adobe Analytics something like how we break product string values. How to achieve it? Right now this is how we get values from data layer.

[Jennifer_Dungan]

I would consider using a prop (which is set up as a list) - but ONLY if your list will always be under 100 characters.

Otherwise, I would use one of your three list variables (s.list1, s.list2 or s.list3) which have no length limitations (aside from 255 characters per item, which you are clearly within).

Or if this is specifically related to your products, why not use a Merchandising eVar for each product and pass the corresponding value so that each product is explicitly joined to the correct value?

s.products = ";prd1;1;9.99;;eVar117=inStock,;prd2;2;5.49;;eVar117=outOfStock"

You could continue to use eVar117, you would just have to change it to be a Product Syntax Merchandising eVar... then when you pull up eVar117 you will see:

eVar117                            Revenue

    inStock                          9.99

    outOfStock                    10.98

[aagk123]

I finally ended up something like this and now trying to make use of classification rule builder but don't know how to write regex. Any tools to generate regex so that I can store stock status and name in different classifications.

[Jennifer_Dungan]

The problem with this is even with Classifications (unless you plan on having a separate classification for each potential item, which then will only hold one item type), this isn't a good implementation.

When you have the above string, you cannot split it the way you want... you need to track this into a list variable.

Your format looks like:

availability:product name (each of these is separated by a comma)

However, if you were to create Classifications (i.e. "availability" and "product name") for eVar117, you can only map one availability classification and one product name classification because these are not a list.

If you track the exact same data in a list variable, using comma as the delimiter, each set of data would be it's own line inside the dimension:

i.e. "inStock:iPhoneX,inStock:IPadY,outOfStock:deviceJ"

Would come through as:

inStock:iPhoneX
inStock:IPadY
outOfStock:deviceJ

Then when you create a classifications on the list dimension, each item will be processed individually

so you would get:

List1	Availability (classification)	Product Name (classification)
inStock:iPhoneX	inStock	iPhoneX
inStock:IPadY	inStock	IPadY
outOfStock:deviceJ	outOfStock	deviceJ

I can help you with the Regex, but again, only if each "set" is isolated will this work.

Regex:

(.*):(.*)

In the Rule Builder, for the Availability classification use $1 to get the first group value (i.e. "inStock" or "outOfStock")

You can use the same regex again for the product name, using $2 to get the second group.

Note, having either commas or colons in the Product Name values may break how the data is extracted, as these are your delimiters (comma for the list processing in Adobe, and colon for your regex)

[yuhuisg]

Based on your screenshot, I agree with @Jennifer_Dungan that Merchandising eVars are your best solution. They're designed for exactly this purpose: to associate a specific value with a specific product.

[Jennifer_Dungan]

This may be something like an impression (hence the inStock and outOfStock)... but again, I would use products for that, so that when product is purchased it's completely cor-relatable to the "Product Views"... but the middle ground is list var if for some reason they don't want to use products and merchandising eVars... (which can be harder to understand starting out)...