How to Group SKUs based on Number of Orders

[ltaparia]

Hi,

I want to classify SKUs based on Number of orders placed per day. This Classification/ ranking will help our asset team to understand which SKUs are sold most in the selected time period and they can plan their stock level based on the historical purchase trend on our website. Let's take below definition for my case

Definition (Per Day)	Demand Label
Orders >= 100	A
90 <=Orders< 100	B
70 <=Orders< 90	C
50<=Orders< 70	D
Orders<50	E

How can I create a report in Work space which shows demand label against SKU in Omniture as below?

Product Code	Demand Label
1215561	A
23F004	A
SA01504	B
1123951	D
1123435	D

ursboller​ Can you please help?

Similar grouping I want to do based on Revenue generated as well as on Units sold.

Regards

Lalit

[David_Jerome]

Not the perfect solution but if you are willing to use 1,2,3,4,5 rather than A,B,C,D,E then you should be able to do this using a nested if statement.

[David_Jerome]

So - I think this is what you are after (using the 1 to 5 system I mentioned this morning).

Based on some data today, I see the following:

The logic to get the ranking system is as follows:

and the last 30 days segment is simply "visits in the last 30 days".

Hope that helps.

Dave

[David_Jerome]

One other - better way however (if I was doing this for myself) - would be to use the Adobe data feed into your data lake. Create a daily calculation here in SQL and then push that back in as a classification each day via automated FTP.

More likely one for your data scientists and I.T team however.

[ltaparia]

Thanks @davidj85759080 , I was thinking of solving my problem using classification of Product but there are challenges which I over see here.

Let's assume I create a classification name: "Product Grouping on Order" and update this on daily basis, won't this overwrite ranking/grouping of product daily(assuming all products have order). How do you view "Product Group On Order"  report for a time period?

[David_Jerome]

Afraid you are correct - it absolutely will over right the existing dimension and time based classifications have now been removed.

It will be interesting to see if anybody has an idea for this because I can only think of two solutions at this point:

A)Crunch the data in SQL from a data feed and keep it separate from Adobe (easier but not ideal)

B)Crunch the data in SQL and work with your ecommerce / I.T team to see if you can get the value in your data layer. From here you would be able to use it as a merchandising evar.

[ltaparia]

Hi davidj85759080, Earlier, your this approach looked promising to me but it also has some issues.

If a business wants to see SKU Demand for "last 7 days" and do grouping as defined earlier by you then it is messing up because in define format you are comparing it every time with the last 30 days units /30 and maybe some products are introduced only a week ago or few did not perform well in the last 7 days but have done good in last 30 days.

What I see as a solution right now, if we can have a dynamic segment based on date range selected in Calender and the same number of days divides it then it can solve the problem. What do you say?

• Is it possible to create such a dynamic segment and
• Get the number of days selected in calendar dynamically in a calculated metric?

Thanks for your time and effort davidj85759080 ! I really appreciate it.

[David_Jerome]

Hi there,

Yes you can do that. There are two parts to this.

Part 1 - Dynamic dates

If you create a calculated metric (I have called it count of days):

This will tell you how many days there are in the time period selected.

In my original (and very long metric) you can then switch the basic logic from:

to:

Part 2 - Dynamic products

You could now take the logic I have explained above and use distinct days where the product has be viewed / sold however as an analyst I would advise against this (depending on the company you work in). The reason is because if nobody is looking at the product on day 1,2,3,4,5,6 and on day 7 you sell 100 units it would appear that you are selling 100 per day. but in reality that is not the case.

Thanks

Dave

[ltaparia]

Thanks, davidj85759080 but you missed this part in the last query:

Is it possible to create such a dynamic segment?

(for the date range selected as you had last 30 days)

If that segment is possible then your question (would appear that you are selling 100 per day.) would be answered. Right?

[David_Jerome · Accepted solution]

Hi ya,

The segment is dynamic.

The approx distinct count (day) will return a value of 1 if you pick just yesterday, 7 if you pick last week, 28 if you select the last 4 weeks etc).

So if for example you have sold 3,444 units in the last 4 weeks and 300 in the last 7 days you would see the following:

28 day report

3444 units      /  28 = 123.

This would give the product a ranking of 5 (average daily units above 100).

7 day report

300 units      /  7 = 42.85.

This would give the product a ranking of 1 (average daily units below 50).

[ltaparia]

Hey Dave,

Instead of defining days manually in the segment and calculating units/day, can't we have any method, where a number of days can be taken automatically from calender (date rage) which user select and we divide it by same?

If we get Days by doing Approx Count Distinct at (Day) dimension, It won't provide the total number of days in a date range selected by the user. Hope you got challenge faced by me.

Regards

Lalit

[David_Jerome]

Hi Lalit,

I think there may be some confusion. :-)

If you look at my solution (post at 21-Aug-2019 00:37) you will see I reference a before and after.

In the after I have removed the 30 day reference.

Thanks

Dave

[ltaparia]

Here is the final working logic. Thanks for all the help davidj85759080​

[David_Jerome]

No worries.

[jantzen_b]

Do any of these replies provide an answer to your original question? If so, please mark the most correct answer. If not, can you provide some additional details to help the community better answer your question.