Run Shipment state analysis for FR, UK, AUS, CAN & US about inception-pipe HOT 4 CLOSED

It´s a bit different what you are proposing.

What I did for UK, FR, and AUS is:
calculating the share of

• orders that have been shipped using shipment feature, i.e. orders with shipment state of the order =shipped
  from
• total number of order that were completed and could have used the shipment feature, i.e. order with shipment state of the order = shipped and payment state of the order = paid or credit owed

from inception-pipe.

Results for UK, FR, AUS in here:
https://docs.google.com/spreadsheets/d/1HKtquVdIovSPnQCkW9husnJcWk3p2YOOBX65y4JboBc/edit?usp=sharing

Query:

1.To identify hubs that using shipment state = shipped the most per instance
(for orders created: Jan 1st 2021 - Jul 15th 2021, to exclude orders that are still in progress)

SELECT "public"."spree_orders"."distributor_id" AS "distributor_id", count(*) AS "count"
FROM "public"."spree_orders"
WHERE ("public"."spree_orders"."shipment_state" = 'shipped'
AND "public"."spree_orders"."state" = 'complete' AND ("public"."spree_orders"."created_at" >= timestamp with time zone '2021-01-01 00:00:00.000Z' AND "public"."spree_orders"."created_at" < timestamp with time zone '2021-07-16 00:00:00.000Z'))
GROUP BY "public"."spree_orders"."distributor_id"
ORDER BY "count" DESC, "public"."spree_orders"."distributor_id" ASC

2. And then, to check shipped orders against total # of completed orders with Payment State = paid or credit owed for each distributors (identified in the previous step)

SELECT "public"."spree_orders"."distributor_id" AS "distributor_id", count(*) AS "count"
FROM "public"."spree_orders"
WHERE ("public"."spree_orders"."state" = 'complete'
AND ("public"."spree_orders"."created_at" >= timestamp with time zone '2021-01-01 00:00:00.000Z' AND "public"."spree_orders"."created_at" < timestamp with time zone '2021-07-16 00:00:00.000Z') AND ("public"."spree_orders"."payment_state" = 'paid'
OR "public"."spree_orders"."payment_state" = 'credit_owed') AND "public"."spree_orders"."distributor_id" = 2584)
GROUP BY "public"."spree_orders"."distributor_id"
ORDER BY "public"."spree_orders"."distributor_id" ASC

@andrewpbrett if you could run them for US and CAN and send me as csv or paste results in the sheet in respective tabs would be great!

from inception-pipe.

Maybe using shipment states could provide the info we need. If understand correctly we're trying to access the turnover of

orders which can be shipped (shipment_state=ready) -> shipped orders (shipment_state=shipped)

This would be (1.) / (2.) from the queries below:

1. Count of orders, from all Enterprises which use this feature (shipment_state=shipped) :

SELECT "public"."spree_orders"."distributor_id" AS "distributor_id", count(*) AS "count"
FROM "public"."spree_orders"
WHERE ("public"."spree_orders"."shipment_state" = 'shipped'
AND ("public"."spree_orders"."created_at" >= timestamp with time zone '2021-01-01 00:00:00.000Z' AND "public"."spree_orders"."created_at" < timestamp with time zone '2021-07-16 00:00:00.000Z'))
GROUP BY "public"."spree_orders"."distributor_id"
ORDER BY "count" DESC, "public"."spree_orders"."distributor_id" ASC

2. Count of orders which have shipment_state=ready, listed by the enterprises above:

SELECT "public"."spree_orders"."distributor_id" AS "distributor_id", count(*) AS "count"
FROM "public"."spree_orders"
WHERE ("public"."spree_orders"."shipment_state" = 'ready'
AND ("public"."spree_orders"."created_at" >= timestamp with time zone '2021-01-01 00:00:00.000Z' AND "public"."spree_orders"."created_at" < timestamp with time zone '2021-07-16 00:00:00.000Z') AND "public"."spree_orders"."distributor_id" IN (enteprise_id1, enteprise_id2, enteprise_id3, ...))
GROUP BY "public"."spree_orders"."distributor_id"
ORDER BY "count" DESC, "public"."spree_orders"."distributor_id" ASC

in which IN (enteprise_id1, enteprise_id2, enteprise_id3, ...) is the list of enterprises obtained in 1. What do you think @jaycmb ?

from inception-pipe.

Queries for US & CAN run and documented in here as well by @filipefurtad0 🙏:skin-tone-2:
https://docs.google.com/spreadsheets/d/1HKtquVdIovSPnQCkW9husnJcWk3p2YOOBX65y4JboBc/edit?usp=sharing

So Hubs for all 5 instances that are using the shipment states are identified and can be contacted in a next step by instance managers or customer support to investigate whether and how they use shipment states.

from inception-pipe.