How to Determine When Returned Orders Have Been Fulfilled

datadiva · June 2020

I'm struggling with how to handle this problem conceptually. My company delivers our products to customers and in the delivery tables, there's a field for Units Ordered, Units Delivered, Units Accepted, and Units Returned. This table is aggregated by month so the dates are always for the first of the month for the customer order for each row and orders can be delivered over the course of many months. So when there is a Return in one month, the first units delivered and accepted would replace the return before becoming billable units again. Management wants a report to tell them when a return has been fulfilled. The challenge is that sometimes the full return amount is not fulfilled the following month and may take a few months to fully complete.

I have tried tons of ways over the last two weeks and there's some kind of mental block on this one. At this point, I'm sure I'm overthinking this, and I would appreciate any ideas on how best to figure out if/when a return amount has been fulfilled (I'm attaching a sanitized sample of the data I'm working with).

jaeW_at_Onyx · June 2020

https://www.youtube.com/watch?v=ZPf41Fjn1H8&t=523s

Here you go! I did a tutorial on this topic a while ago on youtube.

The term you're looking for is a cumulative sum (sorry if that sounds pedantic, but you can do a google search now for cumulative sum SQL and you'll find more in-depth articles etc.)

Add a column at the end of your data called NetChange (where you calculate the net change in that month. Then you can do a cumulative sum on the new net change column

sum(sum(NetChange)) over (order by Delivery Month)

You may have to contact your CSM to have 'windowed functions in beast modes' enabled in your instance of Domo.

Good luck!

jaeW_at_Onyx · June 2020

Having taken a closer look at your data though ... it's unclear if your 'ordered' column is total order amount or just new quantity of units ordered in that month. if your data just calculates 'orders placed that month' then your task will be easier.

Try to define very explicitly how you would answer the 'outstanding balance' question as a series of steps you could define to a Kindergartner. Look at edge cases like 2662. 'how it happened' isn't as important as understanding how to calculate 'the right answer.'

I did make a tutorial video in my Youtube Channel, to walk through how to build this. Hope that helps!

https://www.youtube.com/watch?v=RrT4pjvht1M

datadiva · June 2020

Thank you sooooooo much. I really appreciate the help.

datadiva · June 2020

Thank you so much for your help. I have requested the window function enablement for beast modes and I think this is the right track, but I’m still not quite there. I’ll provide some additional details, and I would appreciate any additional advice you have.

The use case for this is that when units are returned, the revenue associated with them is changed to a liability until those units are fulfilled (we guarantee out units and replace returns at our expense). So while this makes sense for accounting, for the Sales team this impacts their quota attainment and they want to see when the liability of a return impacts their quota attainment and when the liability is removed. I built a report showing when the returns come in and the amount of the liability, but they want to know when the returns are fulfilled - they don’t particularly care about when an order is complete in this report anyway. New deliveries are applied to returns before they applied to the order quantity.

However, ours is not a simple model of ordering and fulfilling as soon as possible. Our customers choose delivery pacing and that’s where the order’s StartDate and EndDate come into play. Some customers front load, so you might see Customer A with a 3-month campaign with 90 units ordered in the first month, and over the course of the campaign dates, they will accept any units as we get them prepared. While Customer X will have orders where they want something like 10 units per month for every month in 2020 which complicates this. For example, if Customer X returns 5 units in February and only 10 units are delivered in March, the first 5 units cover the return, and from the Sales perspective the revenue that was subtracted out gets added back in. Then if we deliver 15 units in April to make up our deficit then we just have additional billable units – I hope that isn’t too confusing. But the challenge with this is that I have to figure out when the return was fulfilled even though the overall order may still be open, as is the case with this example because they have orders in all the way through December. You see why this makes my head hurt?

I think the beast mode route makes sense so we can filter. Part of the issue though is that I have to filter out any orders that don’t have any returns associated with them, but if you filter on that field alone you lose the rows with deliveries for other months. I’d appreciate any help you can offer. This is a very specific use case and like nothing I’ve had to do before.

jaeW_at_Onyx · June 2020

@datadiva , hate to flog my wares ? but I am a freelance consultant and I do offer a service to help clients like you model and solve reporting issues like this at an hourly rate. contact me at jae@onyxreporting.com

with regards to returns. just extend the model.

"we prioritize fulfillment on returns" -- fine that keeps things easy ... alter your model so that you ignore order rows BEFORE the first return. then adjust your calculations to track the outstanding balance on returns. then adjust your calculation to calculate when return IsFullfilled. Then add a filter that determines if each row has an outstanding return quantity.

@datadiva wrote:
Thank you so much for your help. I have requested the window function enablement for beast modes and I think this is the right track, but I’m still not quite there. I’ll provide some additional details, and I would appreciate any additional advice you have.

The use case for this is that when units are returned, the revenue associated with them is changed to a liability until those units are fulfilled (we guarantee out units and replace returns at our expense). So while this makes sense for accounting, for the Sales team this impacts their quota attainment and they want to see when the liability of a return impacts their quota attainment and when the liability is removed. I built a report showing when the returns come in and the amount of the liability, but they want to know when the returns are fulfilled - they don’t particularly care about when an order is complete in this report anyway. New deliveries are applied to returns before they applied to the order quantity.

However, ours is not a simple model of ordering and fulfilling as soon as possible. Our customers choose delivery pacing and that’s where the order’s StartDate and EndDate come into play. Some customers front load, so you might see Customer A with a 3-month campaign with 90 units ordered in the first month, and over the course of the campaign dates, they will accept any units as we get them prepared. While Customer X will have orders where they want something like 10 units per month for every month in 2020 which complicates this. For example, if Customer X returns 5 units in February and only 10 units are delivered in March, the first 5 units cover the return, and from the Sales perspective the revenue that was subtracted out gets added back in. Then if we deliver 15 units in April to make up our deficit then we just have additional billable units – I hope that isn’t too confusing. But the challenge with this is that I have to figure out when the return was fulfilled even though the overall order may still be open, as is the case with this example because they have orders in all the way through December. You see why this makes my head hurt?

I think the beast mode route makes sense so we can filter. Part of the issue though is that I have to filter out any orders that don’t have any returns associated with them, but if you filter on that field alone you lose the rows with deliveries for other months. I’d appreciate any help you can offer. This is a very specific use case and like nothing I’ve had to do before.

How to Determine When Returned Orders Have Been Fulfilled

Comments

Categories