Coupon meta data in orders: what to keep, if anything is to be kept at all? Feedback wanted!

[Konamiman]

We have an ongoing issue regarding coupon data storage as part of the order metadata and while in general the discussion about a possible fix has been directed towards reducing the size of these coupon_data metadata entries, a closer examination of the WooCommerce core code reveals that these entries aren't needed at all for regular operation: they aren't used for displaying, recalculating or otherwise using the order totals.

The only place where these entries are used is in the REST API: they are returned as part of the order data by the orders endpoint inside a meta_data key that is part of the objects returned in coupon_lines. Here's an example of how the data looks for a given order (not including display_key and display_value items that are identical to key and value):

"coupon_lines": [
        {
            "id": 1558,
            "code": "the_coupon",
            "discount": "1",
            "discount_tax": "0.24",
            "meta_data": [
                {
                    "id": 11801,
                    "key": "coupon_data",
                    "value": {
                        "id": 10530,
                        "code": "the_coupon",
                        "amount": "1",
                        "status": "publish",
                        "date_created": {
                            "date": "2023-10-16 07:25:39.000000",
                            "timezone_type": 1,
                            "timezone": "+00:00"
                        },
                        "date_modified": {
                            "date": "2023-10-16 07:25:39.000000",
                            "timezone_type": 1,
                            "timezone": "+00:00"
                        },
                        "date_expires": null,
                        "discount_type": "fixed_cart",
                        "description": "",
                        "usage_count": 1,
                        "individual_use": false,
                        "product_ids": [],
                        "excluded_product_ids": [],
                        "usage_limit": 0,
                        "usage_limit_per_user": 0,
                        "limit_usage_to_x_items": null,
                        "free_shipping": false,
                        "product_categories": [],
                        "excluded_product_categories": [],
                        "exclude_sale_items": false,
                        "minimum_amount": "",
                        "maximum_amount": "",
                        "email_restrictions": [],
                        "virtual": false,
                        "meta_data": [
                            {
                                "id": 133184,
                                "key": "_wcs_number_payments",
                                "value": ""
                            }
                        ]
                    }
                }
            ]
        }
    ],

As you can see, as part of the coupon metadata there's information about:

• The date and time the coupon was created and modified.
• The coupon type and description.
• The coupon usage restrictions (allowed products/categories/emails, usage limit, minimum/maximum amount, can be combined with other coupons or not).
• The coupon usage count.

We consider that this kind of data isn't really relevant in the context of retrieving information about an order, so one possible solution we are considering is completely getting rid of these coupon_data entries: we wouldn't include them in new orders, and we would offer a tool to remove existing entries. This would kind of partially break backwards compatibility in the orders endpoint of the REST API, since we would stop returning data that we were returning previously; but on the other hand the documentation of the endpoint only states that what's returned is "metadata" in a generic way, without specifying what's really returned.

However, we want to hear you! A less radical approach would be reducing the amount of data that is stored... but what should be kept? Maybe you would like to know the coupon type and whether it can be combined with others, but you don't care about the creation date or the coupon restrictions? Something else? (Remember that this is in the context of the order details REST API endpoint: the coupons endpoint is not going to be touched).

Please write down your thoughts! In principle we aim to take a decision about this in early December 2023.

[masteradhoc]

@Konamiman thanks for letting us know about this.

For us much of the data wouldnt be necessary. though we're interested to know which coupon code was inserted (id, code) and how much the deduction was before and after tax.

the discount type can further help us in calculations. else we only need to know if the coupon is again usable (if its one that we might need to deacticate on our side also).

FYI: #30308 - we want to know exactly where a deducation has been made on a product level or even here on discounts. This would make it better for us to prepare data when we sync it externally to ERP and/or Billing Systems.

a good architecture here would help us a lot.

[bryceadams]

+1 for not requiring this meta data in the API. As long as we have the code in the coupon line, we can use that to find the matching coupon. One argument for storing this data may be so you can have a historical record of the coupon's settings at the time of the order, but this would rarely be required and perhaps could be enabled through a filter if stores had a need for it.

[patrick-leb]

Maybe i'm not caffeinated enough and I'm not grasping the problem correctly, but this seems to be like a "tank to kill a fly" solution to me. the Original issue #36295 raised by @seanconklin rightly points out that the problem is what's being stored in the metadata, not the fact there is coupon metada by itself.

From the original ticket:

We're storing data from 340 users of a coupon code into new orders where the coupon code was used just once.

that's the silly part that needs to be addressed. The rest I think can stay and is valuable, as @bryceadams says, it allows to see what the coupon was at the time of the order, which I think is important to keep for auditability.

The question is rather, imo:

• Why save coupon usage in new orders
• Why save the data in serialized format rather than ready-to-be-used json for the API

[seanconklin]

Looks good @patrick-leb

Why save large and historic coupon usage into new orders?

I see that our wp_woocommerce_order_itemmeta table is at 966.64MB currently. After this gets fixed and we're no longer storing 340 users of a coupon into a new order, we'll need to clear that overload from the historic orders. That could be a separate plugin, a WP-CLI job, an admin tool, or otherwise.

[juagonala]

Hi @Konamiman,

As the developer of the WooCommerce Store Credit extension, which uses the WC coupon system intensively, I suggest preserving at least the discount_type info. In our case, we use this parameter to quickly identify which of the applied coupons to the order are Store Credit coupons.

Note: We have other ways to obtain this info from the order metadata in case the coupon_data meta is finally removed, but it might be the case that other extensions that create custom coupons are not ready for this change.

Creating a new Order_Item meta called discount_type is another valid option for us.

Finally, to mention that all coupon data can be accessed by retrieving the coupon object by code, with the exception of the removed coupons. For example, our extension doesn't remove the Store Credit coupons until all orders where they were used are completed or canceled.

[superbmario]

these entries aren't needed at all for regular operation: they aren't used for displaying, recalculating or otherwise using the order totals.

imho, if this is indeed the case then I'd vote to remove the data entirely rather than optimise the way it is stored. A primary concern for the stores I manage is database size and speed.

[martinhinrichs]

In our case, 95% of the coupon_data size, in the order of hundreds of Gigabytes, consists of pointless _maybe_used_by entries. For every single order, the full list of email addresses of all customers having used the coupon previously is added. Removing this would solve our main issue and has no downsides whatsoever.

[FawadNL]

I agree with @martinhinrichs here, its the same for our case. That data of _maybe_used_by serves no purpose for us and has no downside in removing it.

[Konamiman]

A pull request is ready: #44113

TLDR: No more coupon_data metadata entries will be created when applying a coupon to an order; instead, a simplified coupon_info entry will be created containing just coupon id, coupon code, discount type, coupon amount (or percent) and allows free shipping or not. A tool is provided to convert existing coupon_data entries to coupon_info entries in a background process.