The Data School - Backwards is the New Forwards

This blog is to explore my second day in Alteryx. This session was to find out what are the best performing products? Then to compare each SKU's revenue contribution towards the total revenue so we can find the top contributors accounting for 20% of contribution. The reason this blog is called backwards is the new forward is because to understand what we need to do, we need to work backwards to work out what is needed to get to this situation.

Step 1: What is actually being asked.

We need to break this down. This 20% contribution comes from the idea of the 80:20 rule. This rule states that 80% of revenue comes from 20% of your products (Once again this question is somewhat backwards because we want to find the top 20% of contributors).

Step 2: Using the information gathered from previous day what is needed to get to this answer.

We needed to get a % of the total. But to get here we needed the following equations

Revenue= Unit price x Total Sales

Total costs= (Unit cost+ Average Distribution Cost) x Sum Sales

Profit=[Revenue]-[Total costs]

Percentage (%) of Total= Revenue/Total

Step 3: Time to go forward

Now we have the information needed to get to the answer we can start the analysis by creating new columns by adding to the work flow.

So this workflow is to show after joining the distribution costs to the unit costs and price and sales. We can start with a formula. This is where we started to add revenue, total costs and profit. This was then followed by sum of revenue and creating total revenue as we needed these later to find the % of total. This was then needed to be appended. This means it adds data to all rows so for this case a sum of revenue for each SKU and a total number of revenue to each SKU. Now this has been completed for each SKU we can now work out the percentage of the total. Next we need to sort so that this percentage is descending so we could find the top to bottom, this will allow for the running order for us to find the top 20. Finally we would filter running total % to be less then 20 to show final results.