Run Shipment state analysis for FR, UK, AUS, CAN & US

[jaycmb]

To investigate in more detail how hubs are using shipment states, we need to identify the hubs that are using shipment states per instance the most. By running number of orders with shippment state= shipped against total number of orders per hub, we can narrow down to hubs that are using the feature for (almost) all orders

• [x] Jana Brauch to define queries and run in Metabase for FR, UK, AUS

• [ ] @andrewpbrett to run queries for US & CAN

[jaycmb]

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!

[filipefurtad0]

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 ?

[jaycmb]

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

[jaycmb]

Queries for US & CAN run and documented in here as well by @filipefurtad0 :pray::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.