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!
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???
