Order

Table name (referenced in Insights SQL queries): marts_mdc__order

The report provides order data including shipping cost estimates and adjustments

Columns

Column	Type	Description
order_id	number	Unique identifier for the order in the system.
order_key	varchar	Unique key identifier for the order used for external reference.
order_number	varchar	The primary order identifier as defined by the order source. Typically the customer-facing order number (e.g. shown in confirmation emails), but is also commonly set to an internal reference such as a shipment or fulfillment number when sent from an ERP.
order_type	varchar	Order type indicating the sales channel or fulfillment purpose. Possible values: direct_to_consumer, retail, amazon, transfer, removal_liquidation, disposal, bonded_inventory_clearance, work_order.
account_id	number	Identifier for the account associated with the order.
customer_name	varchar	Name of the customer who placed the order.
customer_email	varchar	Stores the email address of the customer who placed the order.
company	varchar	Company name associated with the customer's address.
street	varchar	The first line in a street address and contains the primary information for locating a recipient. It typically includes the building or house number, predirectionals, street name/PO Box, postdirectionals, and street suffix.
street2	varchar	Address field that allows users to add secondary address information, such as numbers for apartments, suites, PO Boxes
city	varchar	Represents the name of the city associated with the address.
postal_code	varchar	Contains information pertaining to the postal code associated with the customer's address.
customer_shipping_address_country_id	varchar	Two-letter ISO country code of the order destination (customer) address.
address_state	varchar	State of the order destination (customer) address.
customer_shipping_address_zip3	varchar	ZIP3 (as defined by United States Postal Service) of the order destination (customer) address. NULL outside USA.
country_name	varchar	Name of the country for the customer’s shipping address.
order_status	varchar	Overall status of the order. Possible values: invalid, cancelled, processing, submitted, complete, onHold, pick_in_progress, pending_batching, fulfilled_by_3rd, awaiting_package_forwarding, awaiting_label_request_from_merchant_facility.
processing_status	varchar	Current processing status of the order. Possible values: backorder, unable_to_submit, queued_to_submit, subscription, package_forwarding, awaiting_label_request_from_merchant_facility, on_hold, submitted, pick_in_progress, pack_in_progress, packed, awaiting_pick_up, awaiting_carrier_processing, en_route, delivered, undeliverable, shipped_untrackable, fulfilled_by_3rd, cancellation_requested, cancelled, updating.
store_name	varchar	Name of the store the order was created within.
trading_partner_name	varchar	Name of the account trading partner if set
order_source	varchar	Source system or channel where the order originated. Possible values: manual, imported (automatically imported via integration), manually_imported (imported by user in the app), merged, cloned, splitted, oversize_split, wholesale_split, claim_reship, automated_bonded_clearance_order.
customer_notes	varchar	Additional notes or comments provided by the customer with the order.
gift_from	varchar	Name of the person sending the gift order, if applicable.
gift_message	varchar	Gift message included with the order, if applicable.
is_monk_protected	varchar	Boolean flag indicating if the order is covered by Monk Protect.
merged_from_orders_list	varchar	A list of order ids this order was merged from
customer_shipping_address_country_iso3	varchar	Three-letter ISO country code of the order destination (customer) address.
estimated_shipping_related_charges	number	The estimated cost associated with shipping-related charges for the order.
estimated_packaging_material_charges	number	The estimated cost associated with packaging materials for the order.
estimated_pick_and_pack_charges	number	The estimated cost associated with picking and packing labor for the order.
item_quantity	number	Total number of order item units, excluding deleted items.
product_quantity	number	Total physical product units ordered; bundle items are expanded by their component quantities. Excludes deleted items.
pallet_count	number	Number of pallets used to pack the order. Null when the order was not packed using pallets.
box_count	number	Number of boxes used to pack the order. Null when no packing configuration exists for the order.
estimated_net_amount	number	The estimated net amount charged for the order, including surcharges.
adjusted_amount	number	The amount of any adjustments made to the original order cost.
adjusted_net_amount	number	The final net amount charged for the order after all adjustments.
estimated_total_surcharges	number	The total amount of estimated surcharges applied to the order.
adjusted_total_surcharges	number	The final total amount of surcharges applied to the order after verification.
estimated_shipping_base	number	The estimated base shipping fee.
adjusted_shipping_base	number	The adjusted base shipping fee.
estimated_residential_surcharge	number	The estimated surcharge for residential delivery.
adjusted_residential_surcharge	number	The actual surcharge for residential delivery charged to the order.
estimated_delivery_area_surcharge	number	The estimated surcharge for delivering to certain areas, such as remote or difficult-to-access locations.
adjusted_delivery_area_surcharge	number	The final delivery area surcharge after adjustments.
estimated_fuel_surcharge	number	The estimated fuel surcharge for the shipment.
adjusted_fuel_surcharge	number	The final fuel surcharge after adjustments.
estimated_peak_surcharge	number	The estimated surcharge applied during peak seasons or periods of high demand.
adjusted_peak_surcharge	number	The final peak surcharge applied after any adjustments.
estimated_additional_handling_surcharge	number	The estimated surcharge for additional handling requirements (e.g. oversized packages).
adjusted_additional_handling_surcharge	number	The final surcharge for additional handling requirements (e.g. oversized packages).
estimated_other_surcharges	number	The total of any other estimated surcharges applied to the order.
estimated_other_surcharge_types	varchar	A list of other types of estimated surcharges applied to the order.
adjusted_other_surcharges	number	The total amount of other surcharges applied after adjustments.
adjusted_other_surcharge_types	varchar	A list of other types of surcharges applied to the order after adjustments.
ordered_at_utc	timestamp_ntz	Timestamp when the customer placed the order in their marketplace, in UTC.
created_at_utc	timestamp_ntz	Timestamp when the order was created in ShipMonk, in UTC.
submit_at_utc	timestamp_ntz	Planned submission time for the order, in UTC. Null until set.
submitted_at_utc	timestamp_ntz	Timestamp when the order was submitted in the system and processing began, in UTC. Null until the order is submitted.
picked_at_utc	timestamp_ntz	Timestamp when the order fulfillment was picked in the warehouse, in UTC. Null until items are picked.
packed_at_utc	timestamp_ntz	Timestamp when the order fulfillment was packed in the warehouse, in UTC. Null until items are packed.
shipped_at_utc	timestamp_ntz	UTC timestamp when the order was finished and is waiting for carrier pickup. Null until the order is sorted for shipping.
picked_up_at_utc	timestamp_ntz	Timestamp when the order fulfillment was picked up by the carrier, in UTC. Null until the carrier picks up the order.
marketplace_notified_at_utc	timestamp_ntz	Timestamp when the marketplace was notified about the shipment, in UTC. Null if the marketplace has not been notified.
loaded_to_truck_at_utc	timestamp_ntz	Timestamp when the last package of the order was loaded to the truck, in UTC. Null if no truck assignment exists for the order.
last_updated_at_utc	timestamp_ntz	Timestamp (UTC) of the most recent update to the order.
delivered_at_utc	timestamp_ntz	Timestamp when the carrier sent a delivery notification for the order fulfillment, in UTC. Null until the delivery notification is received.
earliest_ship_date	date	The earliest date the order can be shipped.
latest_ship_date	date	The latest date by which the order should be shipped.
cancel_date	date	The date by which the order can be canceled.
requested_ship_date	date	The date requested for shipping the order.
requested_delivery_date	date	The date requested for order delivery.
earliest_delivery_date	date	The earliest possible date for order delivery.
latest_delivery_date	date	The latest date the order should be delivered.
ref_order_number	—	Merchant-defined order reference value for type 'order_number'. Distinct from the system-assigned order_number column. Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.
ref_original_imported_order_number	—	Merchant-defined order reference value for type 'original_imported_order_number'. Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.
ref_customer_order_number	—	Merchant-defined order reference value for type 'customer_order_number'. Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.
ref_po_number	—	Merchant-defined order reference value for type 'po_number' (purchase order number). Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.
ref_reference_number	—	Merchant-defined order reference value for type 'reference_number'. Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.
ref_b2b_routing_reference	—	Merchant-defined order reference value for type 'b2b_routing_reference'. Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.
ref_release_number	—	Merchant-defined order reference value for type 'release_number'. Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.
ref_job_number	—	Merchant-defined order reference value for type 'job_number'. Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.
ref_depositor_order_number	—	Merchant-defined order reference value for type 'depositor_order_number'. Comma-separated if multiple values exist. Null if the merchant did not set this reference type for the order.