What's trending? Dashboard API documentation

What is the purpose of this article?

This article contains the gist of the backend logic pertaining to “What's trending?” Dashboard APIs.

API Specifications.

Path: https://{{workspace}}.myeshopbox.com/api/v1/dashboard/trending

REST Method: GET

Query parameters:

days
startDate
endDate

Values passed in the “days” parameter:

last_7_day
today
yesterday
this_week
last_week
this_month
last_30_day
last_month
custom

“startDate” and “endDate” is only passed by the frontend when the custom filter is selected and the date range is specified by the user.

APIs workflow.

NOTE: The SQL queries mentioned below will replace the search requests to Algolia to optimize usage and significantly cut back on the cost impact of Algolia.

NOTE: Out of the 4 types of searches identified and used in Dashboard APIs, “Whats trending?“ API currently uses 2 types of search.

To fetch return completed count from Algolia.
To fetch all orders data from Algolia.

Let's take the example of the “Blackberrys” account and the days filter selected is “This month”.

Based on the request parameters, the cache key is created.
The cache key is created as follows:
trending{{accountId}}{{days}}
For instance, if the “days” param contains “this_month” and the request is made for the Blackberrys workspace, the cache key will look like “trending6this_month“.
Fetch the data from the cache using the key.
In case if any data is found and the “days” param does not contain “custom“, then return the data as the response.
If not data is found OR “days” param contains “custom“, then fetch live data based on input.
Fetch the endDate and startDate if the “days” param does not contains “custom“, else if the “days“ param contains “custom”, the startDate and endDate key will be present in the request param.
Based on endDate and startDate, calculate the difference between the two dates.
Subtract one day from the end date and the date difference from the start date to calculate the previous date slot. For instance, “custom” day with the start date as “2021-07-13“ and the end date as “2021-07-20“.
{ "startDate": "2021-07-13", "endDate": "2021-07-20", "oldStartDate": "2021-07-05", "oldEndDate": "2021-07-12" }
Fetch the total number of return completed order items (order items that have been processed and marked as completed via RMS), from the start date to the end date.
SELECT COUNT(id) from order_items WHERE brandAccountID = 6 AND order_items.warehouseInboundStatus = "COMPLETE" AND order_items.warehouseReversePickupLastUpdated >= "2021-07-13" AND order_items.warehouseReversePickupLastUpdated <= "2021-07-20";
Similarly, fetch the total number of return completed order items from oldStartDate and oldEndDate
Fetch order data from the database from the start date and the end date.
Fetch min and max id of order_items based on created_at from the start date to the end date.
The required order data is as follows:
1. The total number of order items from the start date to the end date.
  SELECT COUNT(id) FROM order_items WHERE brandAccountID = 6 AND creted_at >= "2021-07-13" AND created_at <= "2021-07-20";
2. The total number of order items placed per location (default warehouse) from the start date and the end date.
  SELECT warehouses.externalWarehouseID AS defaultWarehouseCode, COUNT(oi.id) AS ordersPlaced FROM order_items oi LEFT JOIN shipments sh ON sh.id = oi.shipment_id LEFT JOIN warehouses w ON w.id = sh.warehouse_id LEFT JOIN warehouses warehouses ON warehouses.`facility_id` = w.`facility_id` AND warehouses.`isDefault` = '1' WHERE oi.brandAccountId = 6 AND oi.id >= 19747880 AND oi.id <= 19908639 AND warehouses.externalWarehouseID IN ('ESBBLR_111','ESBMUM_112','MJPAT_109') GROUP BY warehouses.externalWarehouseID
3. The sum of MRP, shipping charges and invoice total of all the order items created from the start date to the end date.
  SELECT SUM(oi.mrp) AS totalMRP, SUM(oi.invoiceTotal) AS invoiceTotal, SUM(oi.shippingCharges) AS shippingCharge FROM order_items oi WHERE oi.brandAccountId = 6 AND oi.id >= 19747880 AND oi.id <= 19908639;
4. The count of order items placed per state between the start date and the end date.
  SELECT o.shipping_state AS state, COUNT(*) AS ordersPlaced FROM orders o LEFT JOIN order_items oi ON o.id = oi.order_id WHERE oi.brandAccountId = 6 AND oi.id >= 19747880 AND oi.id <= 19908639 GROUP BY o.shipping_state;
Repeat step 11 for oldStartDate and oldEndDate.
Based on the above-collected data, calculate the following:
1. Total Selling Price = Total invoice amt. - Total shipping charges
2. Total discount = Total MRP - Total selling price
3. Return percentage = (Total number of returns / Total number of order items placed) * 100
Calculate the same as mentioned in step 13 for the old data collected using oldStartDate and oldEndDate.
Now to determine the regional utilization, fetch all the shipments for the brand with their region from shipments created on the start date to the end date.
1. Calculate total LOCAL shipments.
2. Calculate total ZONAL shipments.
3. Calculate total shipments allocated.
Fetch the same data as mentioned in step 15 for oldStartDate and oldEndDate.
To calculate regional utilization:
1. Regional Utilization percentage = ( Sum of LOCAL + ZONAL shipments / Total shipments ) * 100
Calculate the old regional utilization percentage with the data from point 16.
Now to calculate the following percentages for “What’s trending?“ widget, we must compare the above-calculated current data with old data as follows.
1. Average daily orders = Total order count / Number of days (between the start date and end date).
2. Average daily orders comparison
  1. ((Current orders per day - Previous orders per day) / Previous orders per day ) * 100
    If the previous orders count is not 0.
  2. Current orders per day * 100
    If the previous orders count is 0.
3. Average selling price = Total selling price / Total orders count.
4. Average selling price comparison
  1. If the current order count and the sum of the previous selling price are not 0.
    (( Current selling price sum - Previous selling price sum ) / Previous selling price sum ) * 100.
  2. Else if the current order count is not 0,
    (Current selling price sum / Current order count) * 100
  3. Else if the current order count is 0,
    Current selling price sum * 100
5. Return completed percentage = ( Total current returned items / Current orders count ) * 100
6. Return completed percentage comparison
  1. If the previous return percentage is 0
    Current return completed percentage * 100
  2. Else if the previous return percentage is not 0
    (( Current returned% - Previous returned% ) / Previous returned% ) * 100
7. Averge discount percentage = ( Total current discount amt. / Total current MRP ) * 100
8. Averge discount percentage comparison
  ((Current average discount% - Previous average discount%) / Previous average discount%) * 100
9. Regional Utilization percentage = ( Sum of LOCAL + ZONAL shipments / Total shipments ) * 100
10. Regional Utilization percentage comparison
  (( Current regional util.% - Previous regional util.% ) / Previous regional util.% ) * 100
11. Total current order count.
12. Total previous order count.

Query to determine Return % data.

Inputs required:

brandAccountID
startDate and endDate (both current and previous date ranges)

SELECT 
  current_data.Rc AS current_total_returns, 
  current_data.Oc AS current_total_orders, 
  current_data.return_percentage AS current_percentage, 
  previous_data.Rc AS previous_total_returns, 
  previous_data.Oc AS previous_total_orders, 
  previous_data.return_percentage AS previous_percentge, 
  (
    (
      (
        current_data.return_percentage - previous_data.return_percentage
      ) / previous_data.return_percentage
    ) * 100
  ) AS percent_change 
FROM 
  (
    SELECT 
      *, 
      (
        (r.Rc / o.Oc) * 100
      ) AS return_percentage 
    FROM 
      (
        SELECT 
          COUNT(*) AS Rc 
        FROM 
          order_items 
        WHERE 
          brandAccountID = 3 
          AND warehouseReversePickupLastUpdated >= "2021-09-05 00:00:00" 
          AND warehouseReversePickupLastUpdated <= "2021-09-11 23:59:59" 
          AND warehouseInboundStatus = "COMPLETE"
      ) r, 
      (
        SELECT 
          COUNT(*) AS Oc 
        FROM 
          order_items 
        WHERE 
          brandAccountID = 3 
          AND created_at >= "2021-09-05 00:00:00" 
          AND created_at <= "2021-09-11 23:59:59"
      ) o
  ) current_data, 
  (
    SELECT 
      *, 
      (
        (r.Rc / o.Oc) * 100
      ) AS return_percentage 
    FROM 
      (
        SELECT 
          COUNT(*) AS Rc 
        FROM 
          order_items 
        WHERE 
          brandAccountID = 3 
          AND warehouseReversePickupLastUpdated >= "2021-08-29 00:00:00" 
          AND warehouseReversePickupLastUpdated <= "2021-09-04 23:59:59" 
          AND warehouseInboundStatus = "COMPLETE"
      ) r, 
      (
        SELECT 
          COUNT(*) AS Oc 
        FROM 
          order_items 
        WHERE 
          brandAccountID = 3 
          AND created_at >= "2021-08-29 00:00:00" 
          AND created_at <= "2021-09-04 23:59:59"
      ) o
  ) previous_data;

Orders and Shipping Engine

What's trending? Dashboard API documentation

What is the purpose of this article?

API Specifications.

APIs workflow.

Query to determine Return % data.

Related content