Adobe Analytics

philliptrost · 11/3/22

I appreciate any help the group can provide.

I'm interested in a method to identify the price of an individual item within an order total and then return the number of orders where the item in order was $x.

We have items generally for sale on the site, but depending on a promo code applied, discount, etc. that individual item could be $19.99 in the cart or sell for $24.99 for example. Even though the order total could include accessories, warranty, and assembly I'd like to know if the SPECIFIC ITEM in the order sold for $19.99 or not.

Custom segment, metric using a canned formula, other ideas?

Jennifer_Dungan · 3/1/23

I've been playing with some sample data, but I haven't been able to really get it to work..

I was trying to make a calculated metric using functions to try and evaluate the unit price... the problem is the IF function only works on true/false, and then there is a Greater Than or Equal To and a Less Than or Equal To... but trying to stack these to get bucketed lists based on a calculated Unit Price metric is really complicated.. then to add this for each bucket....

I still believe my original thoughts of using a merchandising eVar might be the way to go... It would still require some testing to see if it works, but basically create a "Unit Price" merchandising eVar to hold a text value of the unit price (rather than calculating your Revenue / Units)....

I think you should be able to create classifications on this merchandising eVar to create a pricing tier classification ($600-$699, $700-$799, $800-$899, etc).. then you can should be able to use a calculated metric using IF statements on the bucket value (if price tier = $600-$699, value if true units, value if false (nested if for the next tier, etc

I am not sure if it will work.. Products and Merchandising eVars have really odd segment logic...

I know that there has been a long running struggle with that....

For example, let's say that a purchase contains products from 3 different departments: apparel, sporting goods, groceries (for instance)... you can't pull a report of revenue just for apparel... or just for sporting goods.. or just for groceries...

The reason being.. whether you use a segment that looks for contains apparel or if you use equals apparel.. any purchase that contains apparel will return all the product and revenue data from the purchase event that contained the value (which includes revenue for the other departments)... this may also cause issues with the above....

But for some reason, when you don't build segments, but actually pull data directly in the tables with breakdowns, things work fine...

Maybe you could actually just use the eVar directly to track the pricing tier... then you should be able to just correlate Units with Price Tier???

View solution in original post

Jennifer_Dungan · 11/3/22

You could use merchandising eVars in your product notation to store things like promo code applied, discount, actual purchase price, etc. Setting these eVars up to stored as text, you would be able to break down the individual values per product separate from the revenue collected as a whole.

philliptrost · 2/24/23

I can't believe it's almost been a year, but this request has come up again so I'd love to get it resolved. I don't have a reliable promo code evar, so need to go another route please.

I'm able to get the data I need in a freeform table, but then end up exporting and doing it in Excel. I'd like to eliminate that step.

Can you think of a way to do it with an advanced formula metric? I'm going to give it a try over the weekend but would love any input the group might have.

Thanks so much for the help!

Jennifer_Dungan · 2/24/23

Can you maybe provide a sample of what data is collected during a purchase event? It doesn't have to be real data if you don't want to share that, but show me exactly what is collected during a purchase of multiple items.. it can be in the s.products notation, I can read and parse that easily.... but since I really don't know what you have to work with I can't really suggest much until I understand more about your tracking.

philliptrost · 3/1/23

Thanks, Jennifer,

We have a pretty standard implementation. When a purchase even occurs we generate an external Order ID, the quantity and product purchased, and the revenue of that item.

The example below shows an Order ID that has 14 items in the basket for an order total of $4,161. Of these items, one item has a quantity of 2 for a total of $798 for both items - but a simple custom formula "Unit Price" shows me the product was $399.08. I need the count of how many times this product sold between $300-$399, $400-$499, etc.

The main question I have is "within a date range, how many units are sold at each price tier". To use another example below, I have isolated the table to only one product and every order ID that the product appeared in. Using "units", "revenue", and the custom metric "Unit price" we can see that some orders sold this product at $899 while other orders sold the product at $799.

So of these 905 orders, how many sold the product within each price tier ($600-$699, $700-$799, $800-$899, etc)? I'd like to use the custom formula across any product, date range, and visuals. I could do this simply in Excel, Power BI, even SQL . . . it should be easy in AA using a custom formula I feel.

Thanks in advance!

Jennifer_Dungan · 3/1/23