All purchase_supplier_price in order_detail table wrong!?

[wakabayashi]

Looking a bit into earnings I checked out order_detail table. In my case, it seems that the column purchase_supplier_price is always calculated wrongly.

This column should show the price, you need to pay if you reorder the product at the supplier. The currency is your default currency. That's where my issue comes from instead of $price/$currencyRate it does $price*currencyRate...

Can you please check, if it's the same in yours? (only relevant when you set up supplier price in a different currency)

It's also helpful, if you show me a screenshot of your currency rates:

Edited December 23, 2022 by wakabayashi

[wakabayashi]

Nobody?

Looking deeper into all these numbers, I have to say: PS did a solid job in saving the numbers. But a horrible job calaculation/accounting. There are a lot of places, where the system is just doing dubious things. Best example is statsordersprofit... If you have a simple setup without multiple currencies, it might be solid. If your shop has multiple currencies (suppliers) and a lot of price changes (suppliers) it's useless.

But I can't fix this without other merchants, supplying me with their data. 

Edited December 29, 2022 by wakabayashi

[datakick]

Indeed, looks like a bug

[wakabayashi]

To me it's also questionable in which currency all the numbers of an order (ps_orders and ps_order_detail table) are (or should be) saved. 

@datakick do you know that?

• Are they ALL saved in the currency that the customer used?
• Are they ALL saved in the default currency?
• Or is there a mixture?

Only this understanding would allow a serious usage of these numbers. 

After testing a bit, I think most values are in currency, which the customer chosed. But not all. That's why it's no surprise that devs use it wrongly.

Edited December 29, 2022 by wakabayashi

[Briljander]

I have worked with a lot of ERP systems with currency handling and in those the orders have always been saved in the currency used for that customer. 

But beside this there have also been a separate currency rate table were the conversion rates have been saved for each day for all specified currencies. This made it possible to always convert each order to different currencies by changing currency on the order by a select dropdown.

Edited December 31, 2022 by Briljander

[wakabayashi]

On 12/31/2022 at 2:01 AM, Briljander said:

I have worked with a lot of ERP systems with currency handling and in those the orders have always been saved in the currency used for that customer. 

This makes sense imo. Also PS has this approach, but lacked of consistency.

 

@datakick If I see it correctly, the columns "purchase_supplier_price" and "original_wholesale_price" are stored in shops default currency. The other numbers in orders currency. My proposal would be, that we use for all numbers order currency. As we have the conversion rate, we can simply calculate them back. What do you think?

This is actually what most devs would expect. They surely don't always recheck which column is in which currency. Ok I admit, that these columns probably weren't used a lot ^^

If you agre, I would make a PR.

[x97wehner]

1 hour ago, wakabayashi said:

This makes sense imo. Also PS has this approach, but lacked of consistency.

 

@datakick If I see it correctly, the columns "purchase_supplier_price" and "original_wholesale_price" are stored in shops default currency. The other numbers in orders currency. My proposal would be, that we use for all numbers order currency. As we have the conversion rate, we can simply calculate them back. What do you think?

This is actually what most devs would expect. They surely don't always recheck which column is in which currency. Ok I admit, that these columns probably weren't used a lot ^^

If you agre, I would make a PR.

This stuff is where I think I can help. I'm a business application software architect for enterprise-level ERP and CRM systems by day.

For the situation you're outlining, the reality is that you almost always need to capture both a operators local currency price as well as the converted to shop's default currency price in nearly every scenario. The reason is that for most backend operations, the main value than needs to be factored and used is the shops default currency value. For the front-end operations, ordering, refunds, etc, the customer's (operators) local currency value is nearly always the number needed. Plus, from the front end standpoint, knowing the customer's localized value and it's converted default value at point of entry it's vital, rather than having to go back and try and figure it out later for reporting. It's tough to know exactly when you'll need one value over the other in all scenarios, so the best practice in our development is to store both at point of data entry/change.

Now, I don't know the exact purpose of the columns mentioned above, but they seem to be servicing the backend store owner, who generally will operate within just their primary default currency.

Hope that helps.

[wakabayashi]

Yeah I understand your point. I am personally just not a fan of mixed currencies in the same table, that are not marked by name or so. But ok it's not so an important thing.

More important is to fix the bug, which is really there...

[wakabayashi]

PR is out https://github.com/thirtybees/thirtybees/pull/1598 😎

[datakick]

On 1/5/2023 at 6:18 PM, wakabayashi said:

If I see it correctly, the columns "purchase_supplier_price" and "original_wholesale_price" are stored in shops default currency. The other numbers in orders currency. My proposal would be, that we use for all numbers order currency. As we have the conversion rate, we can simply calculate them back. What do you think?

When you look at code usage of purchase_supplier_price, then it looks like the amount was supposed to be stored in order currency. For example, AdminStatsController divides the amount by conversion rate. 

However, usage of `original_wholesale_price` is more complicated (and buggy). For example, statsbestcategories:

						IFNULL(SUM(
							CASE
								WHEN cp.`original_wholesale_price` <> "0.000000"
								THEN cp.`original_wholesale_price` * cp.`product_quantity`
								WHEN pa.`wholesale_price` <> "0.000000"
								THEN pa.`wholesale_price` * cp.`product_quantity`
								WHEN pr.`wholesale_price` <> "0.000000"
								THEN pr.`wholesale_price` * cp.`product_quantity`
							END
						), 0) / o.conversion_rate AS totalWholeSalePriceSold

this does sum of all original_wholesale_price if exists, or wholesale_price from product if not. This sum is then divided by conversion rate, suggesting that the amount should be in order currency. However, in product cuntext, the amount is stored in default currency. So this stats never worked again.

I suggest we fix this as well, and save original_wholesale_price in order currency as well. Then let's fix the stats sql

[wakabayashi]

On 1/7/2023 at 10:33 PM, datakick said:

this does sum of all original_wholesale_price if exists, or wholesale_price from product if not. This sum is then divided by conversion rate, suggesting that the amount should be in order currency. However, in product cuntext, the amount is stored in default currency. So this stats never worked again.

This is no big problem IMO. You just have to divide original_wholesale_price/o.conversion_rate and not the sum. First taking the sum is even mathematically wrong, isnt it!? 😅 I mean there is not a single static conversion_rate. It changes every day 🤔

On 1/7/2023 at 10:33 PM, datakick said:

I suggest we fix this as well, and save original_wholesale_price in order currency as well. Then let's fix the stats sql

We are on the same page 😎 I will update my PR.

[wakabayashi]

Who of you is using this configuration?

My goal is to set up all costs of an order correctly. Then calculate the net earning and display it on orderpage. Basically this is, what orderprofit stats tries to do. But this stat is not designed correctly. For having consistent numbers, we would have to save per order costs in the tables. Also we shipping fees need to have an absolute costs value (as payment fees already has).

If there is interest in the community, I can implement this in the core. If not, maybe I will just do it in own of my modules 😅

[x97wehner]

15 minutes ago, wakabayashi said:

Who of you is using this configuration?

My goal is to set up all costs of an order correctly. Then calculate the net earning and display it on orderpage. Basically this is, what orderprofit stats tries to do. But this stat is not designed correctly. For having consistent numbers, we would have to save per order costs in the tables. Also we shipping fees need to have an absolute costs value (as payment fees already has).

If there is interest in the community, I can implement this in the core. If not, maybe I will just do it in own of my modules 😅

I try to, but the lack of accuracy in calculating actual shipping costs restricts it's accuracy and effectiveness. We have to use a separate reporting tool to figure this stuff out. Allowing a percentage over 100% in cost calculations as well as a fixed fee option would make this reporting so much better.

[wakabayashi]

16 hours ago, x97wehner said:

We have to use a separate reporting tool to figure this stuff out.

Does this tool anything special or is "just" about getting the earning per order correct?

[x97wehner]

3 hours ago, wakabayashi said:

Does this tool anything special or is "just" about getting the earning per order correct?

I have built a dashboard in Microsoft Power BI that connects to the TB table structure to give me better analytics across the board.

[Pedalman]

"I try to, but the lack of accuracy in calculating actual shipping costs restricts it's accuracy and effectiveness." .... would be really great if we could rely on this table since it's shown on the main dashboard and that very prominently.

[wakabayashi]

Ok I have submitted a PR https://github.com/thirtybees/thirtybees/pull/1623.

• This will allow the usage of fixed fees for carriers.
• It will also save the costs to the DB. Which means, that your old order profits don't change, if you change your costs config values 🙂

[datakick]

On 2/2/2023 at 3:18 PM, wakabayashi said:

Ok I have submitted a PR https://github.com/thirtybees/thirtybees/pull/1623.

• This will allow the usage of fixed fees for carriers.
• It will also save the costs to the DB. Which means, that your old order profits don't change, if you change your costs config values 🙂

I'm not sure how I feel about this 🙂

The original purpose of dashboad configuration parameters

• AVERAGE BANK FEES PER PAYMENT METHOD
• AVERAGE SHIPPING FEES PER SHIPPING METHOD

was to calculate approximate profit margin by deducting payment and shipping fees from order profit.

What you are proposing by your PR is to use this dashboard settings, calculate payment and shipping costs per order, and persist them into database.

Unfortunately, those would not be an actual costs, those are just approximate numbers. For example, cost of using stripe is 

• 0.5% + €0.25 for standard European Economic Area cards
• 1.9% + €0.25 for premium European Economic Area cards
• 2.5% + €0.25 for UK cards
• 3.25% + €0.25 for international cards

So the actual cost of using stripe depends on amount payed + country in which card was issued. Of course you can calculate some average fee based on your historical sales. But saving this info to order does not makes sense. For example, let's say that our average order amount is €1000, so the average stripe payment fee is €10. If somebody places very small order with total €1, should we apply and save this €10 average stripe fee to this order as well? When the real cost associated with this order payment is €0.26?

Also, the other issue is that we can't really calculate the order profit in the first place. We don't know the cost of goods sold, because thirty bees does not track this. At the time of order placement we save current wholesale price, but that's not really COGS. We can't calculate profit from that. Well, we do that -- but it's once again just an approximation. Let's say we purchased the product 2 years ago for price €1. Current wholesale price is €5, because our supplier increased price. We sold the product for €10. The profit should be €9 (sales price - purchase price), but the reporting says €5 (sales price - wholesale price). 

Until we implement proper stock valuation in thirty bees, the reporting will always be just a very approximate overview of your sales.

I'm inclined to decline your PR. I don't think it's a good idea to save these approximate/untrue costs numbers into orders. That could only cause troubles. If you want to have better reporting, I suggest we convert those dashboard config parameters to some sort of timeline. So you could say that the average stripe fee in 2021 was 3.21 and average stripe fee in 2022 was 4.12 (because they changed the pricing, for example).

Of course, I'm open to your argument.

[wakabayashi]

Sorry for my late reply. Unfortunately I am very busy right now with internal stuff. I will reply to this in 1-2 weeks. Please keep it open till my answer 🙂

[wakabayashi]

Ok finally I am getting back to coding / tb-work 🥵

Actually I agree with all your examples, but isn't my PR at least giving a bit more options to get accurate numbers? Nothing is worse than before, but instead you can at least create a simple system:

• 0.5% + €0.25 

Before this was only possible for payments. Now it's also possible for shipping. In general we can argue till death, how much detailed settings we want to have in configs. Accounting can get complex, as I will demonstrate later in this post. IMO we should not try to become an acocunting software, but give merchants an approximation of their profits. In my example I calculate for all orders a profit and display it on the order detail page. Let me show you a (fictive) example:

We use this now since 2-3 months. It's sick what we learned about our gains. Never thought that before. Not sure if there is any interest to include this in core. It could also be a functionality of a module.

 

On 2/21/2023 at 9:30 AM, datakick said:

Also, the other issue is that we can't really calculate the order profit in the first place. We don't know the cost of goods sold, because thirty bees does not track this.

In ASM we have some kind of tracking. But you are right: it's hard to calculate a precise order profit, especially when prices are changing. When I was studying econmics it was also kind of a "hot" topic. Imagine such an process (it happens in my store):

1. You buy product-x for 30€ from your supplier. You set the selling price to 45€.
2. It's inflation and the supplier raises his wholesale price to 50€. 
3. -
4. You sell your item for 45€ to customer 1.
5. You reorder one item and set the selling price to 70€.
6. You sell the item for 70€ to customer 2.

What is your gain of the first order with customer 1? I don't know, what practice (and tax law) say. But the answer can be tricky imo. A lot of people would say, you made a gain of 15€. Ok then let's calculate the second order. This gain is: 70€-50€ = 20€. So total gain: 15€ + 20€ = 35€.

I throw in, that you actually lost 5€ by the first order. Don't believe it? Ok let's imagine the same story like this:

1. Same.
2. Same.
3. You react immediately and raise the price to 70€.
4. Customer 1 doesn't buy the product now (it's too expensive in his opinion).
5. You don't reorder.
6. Same.

What was the gain for the first order? Well its 0 as customer 1 didn't buy anything. What is your gain with customer 2: 70€ - 30€ = 40 €. Total gain: 0€ + 40€ = 40€. Oops 🙈 As I argued above: we lost 5€ with the first order 😅

Ok I admit: my story is also not always true. Third story goes like this:

1. Same.
2. Same.
3. -
4. Same. 
5. Now you say: fuck you supplier. I don't reorder with this wholesale price (as maybe nobody will pay 70€).

What is your gain by first order? Obviously 15€.

So your gain of order 1 depends basically on your decision, if you will reorder or not. Anybody is welcomed to bring up arguments against my point - it's in my opinion a very interesting topic.

 

@datakick I saw now your comment on github. I have no problem to use such a system. I agree, that the idea of saving parameters with a time frame is a bit more clean and flexible. I believe it's main advantage is in backward compatibility. You can always change this values to improve your dashboard values. While I saw this as a disadvantage, it's an advantage (but only combined with the time frame ofc)! But there is currently no table, that can handle it, right? Let's keep the technical discussion on github. 😉

 

[30knees]

I think the approximate order profit overview proposed by @wakabayashi would be helpful. We do something similar in Excel. It's not about accuracy to the cent but just a rough overview. 

@x97wehnerWhat does your Power BI connection all do?  

Regarding the interesting profit discussion, I think what is key is the definition of "lost". I don't see scenario 1 as a loss of 5 Euro compared to scenario 2 because the loss you're referring to is the difference between actual and potential profit. For me, a loss in a business sense is whether I have less money on my account after the transaction than before. For me, what you're looking at is profit maximisation and not loss avoidance. :)

 

[datakick]

1 hour ago, 30knees said:

I think the approximate order profit overview proposed by @wakabayashi would be helpful. We do something similar in Excel. It's not about accuracy to the cent but just a rough overview. 

I agree. I just want to see different implementation of such feature, and not pollute transactional data with guesstimates. Order table should contain facts only