Discover Order reports provided by HotWax Commerce
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.
ORDER_COUNT
The count of the orders in the HotWax Commerce
Count of OrderHeader.EXTERNAL_ID
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.
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.
Sales Channel
Indicates the sales channel, either "POS" or "Shopify".
OrderHeader.SALES_CHANNEL_ENUM_ID
COUNT(EXTERNAL_ID)
Denotes the total number of orders received through each sales channel.
OrderHeader.ORDER_ID
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.
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.
ORDER_NAME
The name or identifier of the order
OrderHeader.ORDER_NAME
HC_ORDER_ID
The ID assigned to the order in the OMS
OrderHeader.ORDER_ID
HC_ENTRY_DATE
The date and time when the order was entered into the OMS
OrderHeader.ENTRY_DATE
TIME_SINCE_ENTRY
The time elapsed since the order was entered into the OMS
OrderHeader.ENTRY_DATE
SALES_CHANNEL
Indicates the sales channel through which the order was received
OrderHeader.SALES_CHANNEL_ENUM_ID
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.
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.
ORDER_ID
The ID of the cash sale order in HotWax Commerce
OrderHeader.ORDER_ID
EXTERNAL_ID
The ID of the cash sale order in external systems
OrderHeader.EXTERNAL_ID
SALES_CHANNEL
Indicates the sales channel through which the order was received
OrderHeader.SALES_CHANNEL_ENUM_ID
LOCATION
The location where the cash sale order was placed
Facility.EXTERNAL_ID
DATE
The date of the cash sale
OrderHeader.ORDER_DATE
CUSTOMER
The customer associated with the cash sale order
OrderHeader.CUSTOMER_CLASSIFICATION_ID
SUBSIDIARY
The subsidiary information for the cash sale
F.EXTERNAL_ID (Subsidiary is set to 5 specifically for facility 376; rest of the facilities have subsidiary 1)
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.
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.
ORDER_NAME
Refers to the name or identifier of the order
OrderHeader.ORDER_NAME
COUNT
The total number of duplicate orders associated with the same order name
OrderHeader.EXTERNAL_ID (Count of all the unique ids of orders as stored in the external system)
HC_Order_Ids
Comma-separated list of HotWax Commerce Order IDs
OrderHeader.ORDER_ID
Netsuite_Order_Ids
Comma-separated list of NetSuite Order IDs
oid.ORDER_IDENTIFICATION_TYPE_ID
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.
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.
Shopify Website
URL of the order on the Shopify website
logInsights.SITE_TXT_EN
Error Message
System-generated error message
logInsights.ERRORMESSAGE_TXT_EN
Shopify Order Name
Name of the order in Shopify
OrderHeader.ORDER_NAME
Order Created Date
Date when the order was created
OrderHeader.ORDER_DATE
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.
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.
CANCELLED_DATE
The date range during which orders were cancelled (2024-01-28 to 2024-02-04)
OrderStatus.STATUS_DATETIME
COUNT_DISTINCT(ORDER_ID)
The count of distinct order IDs for cancelled orders (11)
OrderIdentification.ID_VALUE
Data Selection:
Order IDs and Statuses: The query selects oid.ID_VALUE
(order ID) and os.STATUS_DATETIME
(cancellation date) to track canceled orders.
Order Details: Additional information like oi.ITEM_DESCRIPTION
(item description), oi.UNIT_PRICE
(price), and oi.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 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.
Order Volume
This refers to the number of orders received by the system over a specific period.
OrderHeader.ORDER_ID
Average Approval Duration (Minutes)
This is the average amount of time it takes for the OMS to approve an order.
Calculated difference of OrderHeader.ORDER_DATE and OrderStatus.STATUS_DATETIME(For “APPROVED” status) divided by 60 to display in minutes
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.
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.
ORDER_NAME
The identifier for the order
OrderHeader.ORDER_NAME
HC_ENTRY_DATE
The date and time of the order entry
OrderHeader.ENTRY_DATE
TIME_SINCE_ENTRY
Time elapsed since order entry
Calculated
SALES_CHANNEL_ENUM_ID
The sales channel through which the order was placed
OrderHeader.SALES_CHANNEL_ENUM_ID
SIGNIFYD_APPROVED
Indicates whether the order has been approved by Signifyd
OrderAttribute.ATTR_NAME such as SIGNIFYD_APPROVED and OrderAttribute.ATTR_VALUE
NETSUITE_ORDER_EXPORTED
Indicates whether the order has been exported to NetSuite
OrderAttribute.ATTR_NAME with value as “NETSUITE_ORDER_EXPORTED” and OrderAttribute.ATTR_VALUE
NETSUITE_CUSTOMER_ID
The customer identifier in NetSuite
PartyIdentification.ID_VALUE with PartyIdentification.PARTY_IDENTIFICATION_TYPE_ID as “NETSUITE_CUSTOMER_ID”
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.
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.
FACILITY_ID
The identifier of the facility in external systems
FACILITY_NAME
The name of the facility in external systems
SKU
Unique identifier
QUANTITY
Quantity required of the SKU