Orders
Discover Order reports provided by HotWax Commerce
HotWax Order Count Report
The HotWax Order Count Report offers a daily snapshot of new orders in the Order Management System (OMS), providing a quick reference for monitoring overall order activity and facilitating proactive decision-making. In the Shopify context, this report ensures seamless synchronization by comparing daily order counts. It serves as a vital tool for merchants, quickly identifying and addressing any discrepancies in the synchronization process, ensuring accurate and efficient order fulfillment.
Glossary
Query Logic
Data Selection: The SQL query selects specific data fields required for the report. This typically includes information about orders, such as order IDs and other relevant details.
Filtering HotWax Orders: The primary objective of the report is to count orders processed by the HotWax system. Therefore, the query includes conditions to filter the data and include only orders processed by HotWax.
Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins tables containing order information. This typically involves joining the order_header
table with other relevant tables based on common fields like ORDER_ID
.
Selecting Necessary Information: From the joined tables, the query selects columns related to order details needed for analysis or reporting. This could include order IDs, order dates, and other relevant information.
Filtering and Grouping Data: Conditions are applied to filter the data and include only orders processed by HotWax. This might involve checking if certain attributes or flags indicate that the order was processed by HotWax. The query groups the selected data based on certain criteria, such as order IDs or dates. Grouping data helps summarize and organize it for analysis.
Counting HotWax Orders: Within each group, the query counts the number of orders processed by HotWax. This provides insights into the volume of orders processed by the system over a specified period.
Sales Order Count by Channel Report
Retailers receive orders through various channels, including eCommerce websites, social media platforms, online marketplaces, and point-of-sale (POS) systems. Retailers need visibility of their sales into channel performance. The Sales Order Count by Channel report offers a clear summary of order counts per channel, enabling retailers to identify high-performing channels and areas needing improvement. Retailers can easily discern the total count of orders per channel, whether it's from POS, Shopify, or any other channel enabling effective tracking and analysis.
Glossary
Query Logic
Data Selection: The SQL query selects data from the order_header
table, focusing on sales orders. It calculates the count of orders received through each sales channel, excluding cancelled orders, within a specific date range.
Sales Channel: This field extracts the sales channel information from the SALES_CHANNEL_ENUM_ID
column in the order_header
table. If the SALES_CHANNEL_ENUM_ID
is POS_SALES_CHANNEL
, it assigns "POS" to the SALES_CHANNEL
column; otherwise, it assigns "Shopify".
Count of Orders: This field calculates the count of orders for each sales channel. It uses the count()
function to count the occurrences of EXTERNAL_ID
, representing each order in the dataset. The query aggregates the data by sales channel, counting the number of orders for each channel.
Filtering: Orders with a status of ORDER_CANCELLED
are excluded from the analysis. Additionally, the query filters orders based on their order date, selecting only those within the specified time frame.
Ordering: The results are ordered based on the count of orders in descending order, providing insight into which sales channel has the highest volume of orders.
Limitation: The query limits the output to the top 100 sales channels by order count.
Missing Netsuite Order ID Report
Retailers often encounter synchronization issues between Shopify and Netsuite, leading to orders not being properly recorded. This can result in delays and disruptions in order processing and fulfillment, impacting customer satisfaction and revenue. This report is helpful for retailers to troubleshoot issues when orders, that have all the necessary attributes like SIGNIFYD_APPROVED
, and NETSUITE_CUSTOMER_ID
are not synchronized with Netsuite. It highlights orders in approved or completed status that lack Netsuite order IDs, indicating synchronization problems. Retailers can view this report to identify problematic orders and rectify errors to ensure all orders are synced with Netsuite. The synchronization process between HotWax and Netsuite may encounter delays. Therefore, this report exclusively displays orders without NetSuite order IDs for over 4 hours.
Glossary
Query Logic
Data Selection: The query starts by selecting specific data from a table named order_header
. This table contains information about orders, including the order name, order ID, entry date, and sales channel.
Defining Criteria for Missing IDs: The report specifically targets orders without a Netsuite order ID (ORDER_IDENTIFICATION_TYPE_ID = 'NETSUITE_ORDER_ID'
) by filtering data where the Netsuite order ID is null or not present in the database.
Joining Relevant Tables: The query joins the order_header
and order_identification
tables based on common fields (e.g., ORDER_ID
) to identify orders and associated identification information.
Selecting Necessary Information: Selected columns, such as ORDER_NAME
, HC_ORDER_ID
, and SALES_CHANNEL
, provide context for the missing Netsuite order IDs. Including these details aids in understanding the orders affected by the missing IDs.
Ordering Results: The results are ordered by the ENTRY_DATE
in ascending order, which arranges the orders from oldest to most recent. This organization aids in identifying patterns or trends over time.
POS Cash Sale Exp Failed Report
The POS Cash Sale Exp Failed Report is a tool for tracking synchronization failures related to Point of Sale (POS) cash sales. This report is instrumental in identifying instances where the synchronization process encountered issues, allowing for a proactive approach to address and resolve these failures. By leveraging this report, organizations can ensure the accuracy and completeness of their POS cash sale data, contributing to a more reliable and efficient sales reporting process.
Glossary
Query Logic
Data Selection: The query selects specific data from the ORDER_HEADER
table, including order names, IDs, entry dates, sales channels, and customer information.
Data Filtering: Only completed orders (STATUS_ID = 'ORDER_COMPLETED'
) from the POS sales channel (SALES_CHANNEL_ENUM_ID = 'POS_SALES_CHANNEL'
) with no associated Netsuite order ID are included.
Defining Criteria for Failed Export: The report targets POS cash sales transactions with failed expense exports.
Joining Relevant Tables: Tables are joined based on common fields to identify transactions and associated details such as sales channels and error information.
Selecting Necessary Information: Selected columns provide context for identifying failed POS cash sales transactions.
Grouping Data: The query implicitly groups records based on filtering criteria to focus solely on transactions with failed expense exports.
Calculating Subsidiary: In this report, "subsidiary" is the division responsible for each transaction, based on the facility's external ID. Facility ID "376" maps to subsidiary "5," while all others map to subsidiary "1." This helps in financial tracking, operational management, and regulatory compliance. A conditional statement determines the subsidiary based on the facility's external ID, adding context to the order data.
Duplicate Order
Retailers often face the issue of duplicate orders, which can cause inventory discrepancies and financial losses. For instance, if an order is duplicated and subsequently brokered differently, it can result in ATP (Available to Promise) discrepancies and financial losses for the retailer. Furthermore, it may cause errors when reconciling orders with Netsuite.
The "Duplicate Order" report provides crucial insights into the frequency of duplicate orders by presenting the total count of duplicate orders associated with the same order name. By understanding the extent of duplicate orders, retailers can take necessary actions to ensure that duplicate orders are removed from the system.
Glossary
Query Logic
Data Selection: The query selects four columns: ORDER_NAME
, COUNT
, HC_Order_Ids
, and Netsuite_Order_Ids
from the order_header
and order_identification
tables.
Counting Duplicate Orders: Duplicate orders are identified based on their ORDER_NAME
and EXTERNAL_ID
, and the COUNT()
function calculates the number of occurrences of each unique EXTERNAL_ID
.
Concatenation of Order IDs: For each duplicated order, the associated HC_Order_Ids
(HotWax Commerce Order IDs) and Netsuite_Order_Ids
(NetSuite Order IDs) are concatenated into comma-separated lists using the GROUP_CONCAT()
function.
Filtering Criteria: Orders with a status of "ORDER_CANCELLED" and a specific ORDER_NAME
are excluded from the analysis.
Grouping and Aggregation: Data is grouped by EXTERNAL_ID
and ORDER_NAME
to aggregate duplicate orders, and the HAVING
clause filters out orders with a count of occurrences less than 1, ensuring that only duplicate orders are included.
Ordering by Entry Date: Results are ordered based on the maximum entry date (max_entry_date
) of the duplicated orders in descending order, prioritizing the most recent occurrences of duplicate orders for resolution.
Shopify Order Import Error
When an order fails to transfer from Shopify to Hotwax, it doesn't show up in the OMS, and an error is logged in the EXIM Import record for Shopify orders. The Shopify Order Import Error report details these failed imports, providing the specific errors generated by the system. There are several potential causes for these errors, including data exported from Shopify not being compatible with the OMS, or issues with specific API endpoints, such as inadequate permissions or temporary unavailability. Retailers can use this report to identify problematic orders and troubleshoot accordingly.
Glossary
Query Logic
Data Selection: The query selects specific data from the logInsights
table, likely containing information related to Shopify orders, including the Shopify website name, error messages, Shopify order names, tags, and order creation dates.
Defining Selection Criteria: Data is filtered to include only entries where the document type is SHOPIFY_ORDER
, ensuring that only relevant Shopify order information is retrieved.
Identification of Shopify Order Errors: Within a subquery (virtual_table
), records with a docType
of SHOPIFY_ORDER
are isolated, capturing entries specific to Shopify order imports. The errorMessage_txt_en
field captures system-generated error messages, providing insights into the nature of import failures.
Canceled Order Report
Retailers often face challenges in managing and reducing order cancellations. Customers or CSRs may cancel orders due to reasons such as changes in preference, product unavailability, errors in orders, delivery delays, financial constraints, product defects, better offers elsewhere, and miscommunication. The Cancelled Order Report provides weekly insights into cancellation rates, enabling retailers to pinpoint peak cancellation weeks and address underlying causes. It outlines the total number of canceled orders within the OMS, helping retailers businesses enhance customer satisfaction and operational efficiency by addressing root issues effectively. A weekly report is generated for retailers, and the frequency of the report can be updated as per the requirements.
Glossary
Query Logic
Data Selection:
Order IDs and Statuses: The query selects
oid.ID_VALUE
(order ID) andos.STATUS_DATETIME
(cancellation date) to track canceled orders.Order Details: Additional information like
oi.ITEM_DESCRIPTION
(item description),oi.UNIT_PRICE
(price), andoi.EXTERNAL_ID
(external ID) is also selected.Product Information: The query includes
gi.ID_VALUE
(SKU) to identify specific products.Cancellation Reasons:
en.DESCRIPTION
provides reasons for cancellations.
Defining Cancellation Criteria: The query filters the data to include only orders where the status indicates cancellation (e.g., STATUS_ID = 'ITEM_CANCELLED'
).
Joining Relevant Tables: The SQL query joins these tables together based on common fields (e.g., ORDER_ID
, ORDER_ITEM_SEQ_ID
, PRODUCT_ID
) to compile a comprehensive dataset.
Selecting Necessary Information: Relevant columns are selected from the joined tables, such as order IDs, cancellation dates, SKUs (product identifiers), item descriptions, prices, cancellation reasons, and external IDs.
Grouping Data: The selected data is grouped based on certain criteria, such as order ID, canceled date, SKU, item description, price, cancellation reason, and external ID, to summarize and organize it for analysis.
Counting Distinct Orders: Within each group, the query counts the number of distinct order IDs, providing insights into how many unique orders were canceled on each date.
Calculating Date: The query calculates the date of the canceled orders by finding the first day of the week for each canceled date, facilitating grouping the cancellations by week.
Order Approval Duration Graph
Order approval delays can happen due to missing order attributes such as missing customer ID or municipio ID. Delays in order approval can lead to missed delivery deadlines and potential revenue loss.
The Order Approval Duration graph provides information on how long it takes the OMS to approve orders. It has two main attributes: order volume and average approval duration in minutes. This Graph helps in tracking the average approval duration over time. This helps in identifying any deviations from expected performance levels and taking corrective actions promptly.
Glossary
Data Selection: The SQL query begins by selecting specific data from two tables: order_header
and order_status
. These tables likely contain information about orders and their statuses.
Defining Criteria: The query filters the data to include only orders with a specific status, in this case, orders that are approved. This is achieved by specifying the condition os.status_id = 'ORDER_APPROVED'
.
Joining Relevant Tables: To combine relevant information from both tables, the query joins order_header
and order_status
using the common field ORDER_ID
.
Selecting Necessary Information: From the joined tables, the query selects columns such as order ID, Shopify order ID, approval duration (calculated in seconds), creation date (ORDER_DATE
), and approval date (status_datetime
).
Grouping Data: The query groups the selected data by order ID. This means that all entries with the same order ID are grouped together, allowing for aggregation and analysis of approval durations for each order. The query groups the results by the date part of the creation date (ENTRY_DATE
), allowing for a summary of average approval durations and order volumes for each day.
Calculating Average Approval Duration: Within each group, the query calculates the average approval duration in minutes (AVG(duration in sec) / 60
).
Counting Distinct Orders: Additionally, the query counts the number of distinct order IDs to determine the volume of orders for each day.
Missing Order Attribute Report
The Missing Order Attribute Report is a vital report for tracking order synchronization. By monitoring the presence of essential attributes, it identifies orders lacking crucial information, ensuring a seamless synchronization process. This report enables proactive resolution of discrepancies, preventing any orders from failing to synchronize effectively. In the NetSuite context, the report ensures accurate order synchronization by verifying essential attributes. By highlighting orders lacking these attributes, it prevents synchronization issues, providing assurance that orders seamlessly integrate with NetSuite.
Order attributes are specific pieces of information or data associated with an order that are essential for its processing and synchronization. These attributes can include details such as whether an order has been approved by a fraud detection system (e.g., Signifyd), whether it has been exported to NetSuite, or customer identification numbers. Ensuring these attributes are correctly populated is crucial for smooth order management and integration across different systems.
Glossary
Query Logic
Data Selection: The SQL query starts by selecting specific data fields relevant to the report. This includes information about orders and their attributes.
Defining Missing Attributes: The primary objective of the report is to identify orders with missing attributes. Therefore, criteria are established to filter the data and include only orders where certain attributes are absent or null.
Joining Relevant Tables: To compile a comprehensive dataset, the SQL query joins tables containing order information and attributes. This typically involves joining the order_header
table with the order_attribute
table based on a common field like ORDER_ID
.
Selecting Necessary Information: From the joined tables, the query selects columns related to order attributes and any other relevant information needed for analysis or reporting.
Filtering Data: Conditions are applied to filter the data and include only orders with missing attributes. This involves checking if certain attribute values are null or absent in the dataset.
Grouping Data: The query groups the selected data based on certain criteria, such as order ID or attribute type. Grouping data helps summarize and organize it for analysis.
Counting Missing Attributes: Within each group, the query counts the number of orders with missing attributes. This provides insights into the frequency and distribution of missing attributes across orders.
Allocation Pending Report
The Allocation Pending Report displays orders assigned to facilities that currently lack sufficient inventory for immediate fulfillment. Monitoring the orders and items listed in this report is essential for initiating replenishment actions at the store. By doing so, retailers can ensure that inventory levels are maintained adequately, facilitating the successful completion of fulfillment for the identified orders.
Glossary
Last updated