Flow of API For NDR shipping dashboard:
- Shubham Kumar (Unlicensed)
- Manas Singh (Unlicensed)
Filters To be applied :
Last delivery attempted on:
Dropdown date range with values same as orders listing page
Date range applied on big query
Table name : `analytics-340903.bq_export_sync.bq_sale_orders`
Column name : latest_out_for_delivery_attempt
Field value format : "yyyy-MM-dd HH:mm:ss"
Payment Method :
Dropdown string list with values (Prepaid ,Cash on delivery (COD))
Dropdown applied on :
Table name : `analytics-340903.bq_export_sync.bq_sale_orders`
Column name : payment_mode
Values :['Prepaid' , 'Cash on delivery (COD)']
Risk Score :
Dropdown string list with values (High,Low,Medium)
Implementation to be decided
Courier Partner :
Dropdown string list with values (Courier partner name)
Dropdown applied on :
Table name : `analytics-340903.bq_export_sync.bq_sale_orders`
Column name : forward_shipping_provider
Values :[courier partner name as received in the request ]
Shipping Method :
Open point to be target for next release
Figma Link -
Query which is prepared for the what's your current failed delivery orders :
SELECT dates.day, tot.total_order,
fd.total_failed,
((fd.total_failed/tot.total_order)*100.0) AS failed_percentage ,
fd_del.total_fd_del ,
((fd_del.total_fd_del/fd.total_failed)*100.0) AS failed_deliver_percent ,
fd_ret.total_fd_ret ,
((fd_ret.total_fd_ret/fd.total_failed)*100.0) AS failed_return_percent ,
tot_act.total_act_pen ,
((tot_act.total_act_pen/fd.total_failed)*100.0) AS action_pending_percent
from
(SELECT day
FROM UNNEST(
GENERATE_DATE_ARRAY(DATE('2022-10-27 00:00:00'), DATE('2022-11-03 23:59:59'), INTERVAL 1 DAY)
) AS day) dates left join
(SELECT DATE(latest_out_for_delivery_attempt) as day, COUNT(DISTINCT orderitemid_pk) AS total_order FROM `analytics-340903.bq_export_sync.bq_sale_orders` WHERE order_created_in_eshopbox IS NOT NULL
AND latest_out_for_delivery_attempt >= "2022-10-27 00:00:00" AND latest_out_for_delivery_attempt <= "2022-11-03 23:59:59"
AND workspace="blackberrys" GROUP BY DATE(latest_out_for_delivery_attempt)) tot on tot.day = dates.day
left join
(SELECT DATE(latest_out_for_delivery_attempt) as day, COUNT(DISTINCT orderitemid_pk) AS total_failed FROM `analytics-340903.bq_export_sync.bq_sale_orders` WHERE order_created_in_eshopbox IS NOT NULL
AND latest_out_for_delivery_attempt >= "2022-10-27 00:00:00" AND latest_out_for_delivery_attempt <= "2022-11-03 23:59:59"
AND workspace="blackberrys" AND number_of_delivery_attempts_done >1 GROUP BY DATE(latest_out_for_delivery_attempt)) fd on dates.day = fd.day
left join
(SELECT DATE(latest_out_for_delivery_attempt) as day, COUNT(DISTINCT orderitemid_pk) AS total_fd_del FROM `analytics-340903.bq_export_sync.bq_sale_orders` WHERE order_created_in_eshopbox IS NOT NULL
AND latest_out_for_delivery_attempt >= "2022-10-27 00:00:00" AND latest_out_for_delivery_attempt <= "2022-11-03 23:59:59"
AND workspace="blackberrys" AND number_of_delivery_attempts_done >1 AND current_status = "Delivered" GROUP BY DATE(latest_out_for_delivery_attempt)) fd_del on dates.day = fd_del.day
left join
(SELECT DATE(latest_out_for_delivery_attempt) as day, COUNT(DISTINCT orderitemid_pk) AS total_fd_ret FROM `analytics-340903.bq_export_sync.bq_sale_orders` WHERE order_created_in_eshopbox IS NOT NULL
AND latest_out_for_delivery_attempt >= "2022-10-27 00:00:00" AND latest_out_for_delivery_attempt <= "2022-11-03 23:59:59"
AND workspace="blackberrys" AND number_of_delivery_attempts_done >1 AND return_type = "Courier initated return" GROUP BY DATE(latest_out_for_delivery_attempt)) fd_ret on dates.day = fd_ret.day
left join
(SELECT DATE(latest_out_for_delivery_attempt) as day, COUNT(DISTINCT orderitemid_pk) AS total_act_pen FROM `analytics-340903.bq_export_sync.bq_sale_orders` WHERE order_created_in_eshopbox IS NOT NULL
AND latest_out_for_delivery_attempt >= "2022-10-27 00:00:00" AND latest_out_for_delivery_attempt <= "2022-11-03 23:59:59"
AND workspace="blackberrys" AND number_of_delivery_attempts_done >1 AND failed_delivery_action_status IN("Action pending", "Action completed") GROUP BY DATE(latest_out_for_delivery_attempt)) tot_act on dates.day = tot_act.day
This is the result which we will get from the above query