Order LifeCycle throughput

You can calculate the throughput of orders going through various lifecycle states.

Review the following example:


   
   select pipeline_key, status, substr(order_release_status_key,1,10) time,
          count(*) count
   from yantra.yfs_order_release_status
   where order_release_status_key > '20110704000000' and
         order_release_status_key < '20110704999999'
   group by pipeline_key,
          status,
          substr(order_release_status_key,1,10);
   
   PIPELINE_KEY             STATUS          TIME                          COUNT
------------------------ --------------- ------------------------ ----------
   2011070409425525425230   1100            2011102906                    13333
   2011070409425525425230   1100            2011102907                    13464
   2011070409425525425230   1100            2001102908                    13333
   2011070409425525425230   1300            2011102906                       50
   2011070409425525425230   1300            2011102907                       23
   2011070409425525425230   1300            2011102908                       50
   2011070409425525425230   3200            2011102906                    13234
   2011070409425525425230   3200            2011102907                    13477
   2011070409425525425230   3200            2011102908                    13290

The definition of the STATUS is found in YFS_STATUS and PIPELINE_KEY in YFS_PIPELINE. For example, status of 1100 indicates order lines being created. In the example above, there were 13,333 order lines created for one pipeline and another 4,333 order lines created in another pipeline.

Note: You should baseline the throughput of individual agents and key APIs to get an idea of the potential throughput. You should then monitor the agents in production against the baseline. This continual monitoring may reveal issues - for example, a credit authorization agency providing slower response times, issues with the database, and so forth.

Note: You can monitor the flow of the orders on an hourly basis by pivoting the data so that STATUS is in the column and TIME is in the row. For example, the data above can be displayed as follows:


   Time              1100       1300       3200
   2011070406       13333         50      13234
   2011070407       13464         23      13447
   2011070408       13333         50      13290

In the pivot example above, 13,333 order lines were created on 2011/07/04 at 06am. At that same time period, 50 order lines went to Backorder and 13,234 were Released. More importantly, one may conclude that the flow of the orders through the pipeline is good because order releases are keeping pace with order creation.

There are many ways to create pivot tables including Microsoft Excel (use Data > PivotTable and PivotChart Report...).