/
New Flow of FDR Widget

New Flow of FDR Widget

Owned by Shubham Kumar (Unlicensed)
Nov 24, 2022
4 min read

latest_out_for_delivery_attempt

orderitemid_pk

order_created_in_eshopbox

workspace

Number_of _failed _delivery _attempts _done

current_status

return_type

failed_delivery_action_status

reason_for_failed_delivery

payment_mode

courierPartner

latest_out_for_delivery_attempt

orderitemid_pk

order_created_in_eshopbox

workspace

Number_of _failed _delivery _attempts _done

current_status

return_type

failed_delivery_action_status

reason_for_failed_delivery

payment_mode

courierPartner

1

Filter

To calculate total order

Last 90/60/30 days depending

To divide the data for main query

To find out failed delivery

To find the failed delivery delivered in further attempts

To find out failed delivery returns

To find out failed delivery action pending

To group for the

failed delivery widget

needed for payment method widget

Filter

Creation of fixed Table for FDR Dashboard:

NOTE : For below steps to work a primary blocker would be moving partion column of source table bq_sale_orders.order_created_in_eshopbox to order_journey_update_time

 

  1. Create a cron to sync data from the source table to the destination table
    API : ESB/v1/dashboard/sync/failed_delivery

  2. Based on the sync frequency of the source table i.e load job updated_at

  3. Pull the data from the source table by the below query

    SELECT latest_out_for_delivery_attempt,orderitemid_pk,order_created_in_eshopbox,workspace,number_of_delivery_attempts_done,current_status,return_type,failed_delivery_action_status, reason_for_failed_delivery,payment_mode from `analytics-340903.bq_export_sync.bq_sale_orders` WHERE order_journey_update_time > DATETIME_SUB(CURRENT_DATE(), INTERVAL 90 Minutes) AND number_of_failed_delivery_attempts_done > 0 AND current_status IN ('On the way', 'Out for delivery', 'Delivered', 'Return on the way')

  4. Save this data via CSV sheet in cloud storage

  5. Run load query using the file path of the CSV sheet

  6. Delete previous items on the basis of data that we have got when converting the data to CSV sheet and run the delete query.

Sequence Diagram for the flow :

 

Now the secondary query for the dashboard widget (What's your current failed delivery orders ):

The scan size will be decreased since the table is portioned on latest_out_for_delivery_attempt

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_failed_delivery_dashboard` 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_failed_delivery_dashboard` 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_failed_delivery_attempts_done >0 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_failed_delivery_dashboard` 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_failed_delivery_attempts_done >0 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_failed_delivery_dashboard` 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_failed_delivery_attempts_done >0 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_failed_delivery_dashboard` 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_failed_delivery_attempts_done > 0 AND current_status IN ('On the way', 'Out for delivery') GROUP BY DATE(latest_out_for_delivery_attempt)) tot_act on dates.day = tot_act.day

Query Result :

Secondary Query for Widget number - 2 where we have to show the count on the basis of failed delivery reason :

Query

SELECT fd.reason_for_failed_delivery,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 COUNT(DISTINCT orderitemid_pk) AS total_failed,reason_for_failed_delivery 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_failed_delivery_attempts_done >0 GROUP BY reason_for_failed_delivery) fd left join (SELECT COUNT(DISTINCT orderitemid_pk) AS total_fd_del,reason_for_failed_delivery 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_failed_delivery_attempts_done >0 AND current_status = "Delivered" GROUP BY reason_for_failed_delivery) fd_del on fd.reason_for_failed_delivery = fd_del.reason_for_failed_delivery left join (SELECT COUNT(DISTINCT orderitemid_pk) AS total_fd_ret,reason_for_failed_delivery 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_failed_delivery_attempts_done >0 AND return_type = "Courier initated return" GROUP BY reason_for_failed_delivery) fd_ret on fd.reason_for_failed_delivery = fd_ret.reason_for_failed_delivery left join (SELECT COUNT(DISTINCT orderitemid_pk) AS total_act_pen,reason_for_failed_delivery 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_failed_delivery_attempts_done >0 AND failed_delivery_action_status IN("Action pending", "Action completed") GROUP BY reason_for_failed_delivery) tot_act on fd.reason_for_failed_delivery = tot_act.reason_for_failed_delivery

 

Output of the Query :

 

Add label