Display "Origin" column in Orders table in Orders Analytics

[ecgan]

Part of epic issue #46054.

Orders Analytics is accessed via the URL: /wp-admin/admin.php?page=wc-admin&path=%2Fanalytics%2Forders&period=year&compare=previous_period

Here's a screenshot of the current Orders table:

The data for the Orders table come from the following API call: GET /wp-json/wc-analytics/reports/orders?orderby=date&order=desc&after=2024-01-01T00%3A00%3A00&before=2024-04-09T23%3A59%3A59&page=1&per_page=25&extended_info=true&_locale=user

Here's the API response for the above API call:

[
    {
        "order_id": 61,
        "parent_id": 0,
        "date": "2024-03-26 16:21:38",
        "date_created": "2024-03-26 16:19:42",
        "date_created_gmt": "2024-03-26 16:19:42",
        "status": "completed",
        "customer_id": 1,
        "net_total": 18,
        "total_sales": 18,
        "num_items_sold": 1,
        "customer_type": "new",
        "extended_info": {
            "products": [
                {
                    "id": "18",
                    "name": "Beanie",
                    "quantity": "1"
                }
            ],
            "coupons": [],
            "customer": {
                "customer_id": 1,
                "user_id": "1",
                "username": "admin",
                "first_name": "",
                "last_name": "",
                "email": "admin@example.com",
                "date_last_active": "2024-03-26 16:19:42",
                "date_registered": "2024-03-12 03:59:09",
                "country": "MY",
                "postcode": "12312",
                "city": "KLKLKL",
                "state": "KUL"
            }
        },
        "order_number": 61,
        "total_formatted": "$18.00",
        "_links": {
            "order": [
                {
                    "href": "https:\/\/wcventures.ddev.site\/wp-json\/wc-analytics\/orders\/61"
                }
            ]
        }
    }
]

In this issue, we want to display a Origin column in the Orders table in Orders Analytics.

• The API should return origin data in the API response.
• The UI should display the origin data from the API response.
• Clicking on the "Download" button in the Orders table should download a CSV file that contains the new "Origin" column.

References:

• https://github.com/woocommerce/woocommerce/blob/04c3d9119c571a0b0526f328a18736bf6215a676/docs/reporting/adding-columns-to-analytics-reports-and-csv-downloads.md
• https://github.com/woocommerce/woocommerce/blob/de54195c3ab3a091c3c406984622b61a6f8fa413/docs/reporting/extending-woocommerce-admin-reports.md

[ecgan]

Dev note:

I looked into how we might want to make the changes in the API and database layer. From my findings, it seems like Order Analytics primarily operates on the wc_order_stats database table in the following two files:

• https://github.com/woocommerce/woocommerce/blob/b9a1f9742006bc337cac5b77c1f2dfaae2d14965/plugins/woocommerce/src/Admin/API/Reports/Orders/DataStore.php
• https://github.com/woocommerce/woocommerce/blob/b9a1f9742006bc337cac5b77c1f2dfaae2d14965/plugins/woocommerce/src/Admin/API/Reports/Orders/Stats/DataStore.php

Order attribution data are stored in the wc_orders_meta table (when HPOS is enabled). In Orders Analytics, currently there isn't any table join with wc_orders table or wc_orders_meta table. Though in https://github.com/woocommerce/woocommerce/blob/de54195c3ab3a091c3c406984622b61a6f8fa413/docs/reporting/extending-woocommerce-admin-reports.md, the example there illustrates table join with postmeta table (which should be similar to wc_orders_meta table).

If we implement this feature with table join on orders_meta table, we may need to do some performance testing or stress testing to make sure the table join does not cause issues.

[ecgan]

Dev note:

Instead of doing table join, we may also consider getting the order attribution data and put it under extended_info in the order data. See screenshot below:

Each extended info (coupons, customer, products) is a separate SQL query. Code reference:

woocommerce/plugins/woocommerce/src/Admin/API/Reports/Orders/DataStore.php

Line 340 in b9a1f97

protected function include_extended_info( &$orders_data, $query_args ) {

[ecgan]

I have updated the issue by changing "Channel" to "Origin". This is mainly because I now have a PR #46424 that works on origin data.

We can replace origin to channel later in issue #46415.