Order.co Data Dictionary

Order.co is committed to helping businesses make data-driven decisions and get full visibility into their accounts. To help with that, we've put together an in-depth data dictionary to define each data point and how filters impact their reports. 

Filters

Filters allow you to quickly drill down into the data in each report. Filters at the top of each dashboard will filter each report on the page when updated, though some reports may not contain columns impacted by the filter and will not change. 

To help you get the most out of your reports, here is a list of unique filters and how they impact reports:

• Completed Date: This filter lets you choose specific dates or ranges to filter.
• Time Interval: This filter groups events, spend, etc. by day, week, month or year in charts and graphs.
• As of Date: This filter for Credit and Financing dashboard shows what your credit use was for at different moments in your invoicing period.
• Payment Via:  This filter uses vendor payment settings to filter reports. If you pay a vendor directly, but order through Order.co, you can use this filter to find that data!
• Spend Type: For reports that show both Catalog and Virtual Card data, this Spend Type allows you to pick one type of spend to view.
• Associated to: This filters for the Accounting Code mapped objects such as Location, Cost Center, User, etc. This helps finance users quickly review code mappings for different segments of their accounting codes.
• Is Invoiced: Quickly filter for orders and transactions that have been invoiced or are completed but not yet on an invoice (Daily, Weekly, Monthly invoice issue date has not yet passed for these).
• Is Active Product: Active products are still available for purchase in your My Product catalog. Inactive products have been deleted, but purchase and pricing data is still available through our Analytics reports.
• Event Type: This filter helps users of our Audit reports using options Create, Update, Destroy to represent when an object was added to Order.co, when it's properties were changed, or when the object was deleted or deactivated.
• Received at: This date filters for when products were marked received using our Confirm receipt feature.

Column Headers

Use this section to understand how Order.co defines each data point in your Analytics reports.

Order Details

• order_id: This is the Order.co order number, as seen on the order page or My Orders
• order_line_item_id: An Order.co ID for each product within an order
• invoice_id: The Invoice number, as found on each invoice and the Pay page
• order_nickname: The (optional) custom name given to the order by the ordering user.
• order_created_at: The submission date of the order.
• order_last_approved_at: The date that the order was fully approved.
• order_approved_by: List of approver names for this order.
• completed_at: Date that the order moved to Completed status.
• order_status: The order status as displayed in "My Orders" page.
• order_last_updated_at: The date when the order status last changed. 

Product Details

• product_name: The name of the product.
• product_nickname: The custom name given to the product by the user.
• product_id: The Order.co universal catalog product ID number
• organizations_product_id: The Order.co unique identifier for your business’s product.
• product_category_name: The assigned product category.
• substitution_preference: The default substitution preference of the product (e.g. When savings are available, Never, or Always).

Vendor Details

• vendor_name: The vendor name.
• vendor_id: The Order.co vendor ID number.
• vendor_order_number: The vendor order number assigned by the vendor.
• original_vendor_name: The name of the original vendor for an uploaded product.
• original_vendor_id: The Order.co identifier associated with the original vendor.

Pricing and Payments

• price: The Order.co price per item.
• list_price: The product price at time of upload or last update, determines rewards amount
• subtotal: The price multiplied by the quantity.
• total: The total price (includes subtotal, tax, shipping, and discount).
• currency: The currency for the line item’s monetary values (price, list_price, tax, total, etc.).
• discount: A coupon or discount offered by the vendor.
• tax: The tax for the line item or order.
• shipping: The shipping fee for the line item or order.
• payment_via: Describes who the vendor billed for this order. Either Order.co (consolidated_bill) or directly to your business (linked_account)
• payment_method_type: The type of payment method used to pay an Order.co invoice
• payment_method_nickname: The nickname of the payment method on the Pay page.
• payment_method_institution_name: The card or bank name associated with the payment method.

Location and Address Details

• location_value: The location name associated with the order.
• location_description: The location description associated with the order.
• location_id: The Order.co Location ID associated with the order. This ID can be used to update Budgets using the bulk update template
• address_name: The Attn to: line of the address
• address_line_1: The first line of the address.
• address_line_2: The second line of the address.
• address_city: The city name of the address.
• address_state: The state name of the address.
• address_zipcode: The zip code of the address.
• address_country: The country of the address.

User Details

• user_id: The Order.co User ID for a given user.
• user_name: The user name for a given user
• user_email: The user email address for a given user

Return Details

• return_id: The Order.co Return ID for the line item.
• return_reason: The reason for the return, as provided when submitting a return.
• return_completed_at: The date when the return was completed or refunded.

Tracking and Delivery Details

• tracking_number: The tracking number(s).
• tracking_status: The current status of the tracking number (e.g. In Transit, Delivered).
• tracking_url: The quick link to view the tracking details.
• last_location: The last location of the delivery.
• first_delivery_attempted_at: The date when the courier first attempted delivery.
• delivered_at: The date the order was delivered.
• courier_name: The name of the courier.
• received_by_user_names: The user names of the users that marked the items received
• received_by_user_ids: The Order.co User IDs of the user the marked the order received

Cost Center Details

• cost_center_value: The Cost Center name
• cost_center_description: The Cost Center description
• cost_center_id: The Order.co cost center ID

Accounting Code Categories

• code_category_[1-9]: The code category name associated with the order line item.
• code_category_[1-9]_value: The value associated with the code category.
• code_category_[1-9]_description: The description associated with the code category.

 

Unique Column Headers

The data here are unique to the reports and dashboards they appear on. 

Raw Data

• spend_type: Distinguishes between Catalog and Virtual Card spend.
• source_id: The data source identifier. For example, when source is order_line_item, source_id is the order line item ID. When source is virtual_card_order_charge, source_id is the card transaction ID.
• source: The data source (e.g., order_line_item, return_line_item, etc.) for this line in the report. This helps differentiate between amounts from Virtual Card Transactions and Catalog Orders.
• substitution_preference: The substitution preference for the product at the time of purchase.
• rewards: Credits earned by using an Order.co substitution. This amount will be passed back as a credit memo after invoice payment.
• order_last_approved_at: The date that the order was last approved.
• order_approved_by: A list of order approver names.
• virtual_card_id: The Virtual Card ID, represents a single virtual card. All transactions for that card will be tied to this ID.
• virtual_card_name: The name given to the Virtual Card.
• virtual_card_created_at: Date that the Virtual Card was created.
• virtual_card_recurrence: The (optional) cadence of the Virtual Card.
• virtual_card_transaction_description: The merchant descriptor of the Virtual Card transaction. This information is similar to transaction descriptions on a credit card statement
• is_credit_memo: Distinguishes between invoices and credit memos.
• credit_memo_from_invoice_id: The invoice that generated the credit memo, if rewards or an overpayment.
• is_cancelled: Indicates whether the line item was cancelled from the order.

Order Tracking 

• is_product_fully_delivered: Indicates whether the vendor or courier has marked all packages for your product (order_line_item_id) as delivered.
• is_line_item_cancelled: Indicates whether the line item is canceled due from the order.
• cancelled_at: The date of when the order was canceled.
• cancellation_reason: Reason for why the order was canceled.
• order_status: The order status, as displayed in the “My Orders” page.
• vendor_order_number: The vendor order number associated with the line items or order.
• tracking_number(s): The tracking number associated with the order line item.
• delivered_at: The date that the package was marked delivered by the courier.
• quantity_ordered: The quantity of the ordered product.
• latest_estimated_delivery_date: Latest estimated delivery date as indicated by the vendor
• tracking_number_details:This field shows a list of tracking numbers, couriers, tracking statuses, estimated delivery dates and delivery dates for a line item or order
• tracking_numbers: List of tracking numbers associated with the product (order_line_item_id) without details.
• vendor_order_numbers: List of vendor order numbers as seen in the vendor’s order page.
• quantity_received: The quantity of the product marked as received using the confirm receipt feature.
• is_quantity_ordered_fully_received: Indicates whether the total product ordered has been marked as received using the confirm receipt feature.
• received_at: The dates the product was received.
• received_by_user_names: The user names that confirmed receipt of product
• received_by_user_ids: The Order.co user IDs of the users that confirmed receipt of the product 

Approvals

• approval_rule_name: The approval rule name.
• approval_step: The approval step (e.g. 1, 2, 3, etc.).
• approvers: List of approvers who can approve the order.
• approval_step_condition: The criteria that will trigger the approval (e.g. user, total, etc.).
• approval_step_comparator: The approval step comparator that should be read with the approval_step_condition and approval_step_values (e.g. not_one_of, greater_than, etc.).
• approval_step_values: The value given to be compared with the approval_step_condition and approval_step_comparator (e.g. list of ordering users, $5000 order total, etc.).
• approval_step_and_or: Indicates if conditions have And/Or links in the rule. (e.g. Greater than $1000 And/Or Location is HQ)
• approval_rule_created_at: The date when the approval rule was created.
• approval_step_created_at: The date when the approval step was created
• approval_step_conditiion_created_at: The date when the approval step condition was created.
• is_approval_rule_catch_all: Indicates whether the approval rule is a “catch all orders” approval rule, as found on the Approvals page.
• approval_rule_id: The Order.co ID of the approval rule.
• approval_step_id: The Order.co ID of the approval step.
• approval_rule_id: The Order.co ID of the approval step condition.
• product_count: The count of products in the order requested for approval.
• order_total: The subtotal of the order requested for approval.
• order_product_categories: List of product categories in the order requested for approval.
• approval_status: The current status of the approval.
• hours_to_approve_from_creation: Hours between order submission and the approval of the order or approval step. Helpful for evaluating approver performance!
• rejection_reason: The reason for rejecting the order, as written by the approver.
• ordering_user_id: The Order.co identifier of the user who created the order.

Catalog

• is_active_product: Indicates whether the product is currently active (not deleted).
• created_by: Indicates how the product was created in the catalog (e.g. Vendor Onboarding Upload, Chrome Extension, etc.).
• event_at: The timestamp indicating when the substitution preference was changed.
• event: The event type (create or update).
• before_event_state: The substitution preference before it was changed.
• after_event_state: The substitution preference after it was changed.
• created_at: The date that the product was added to the catalog.
• deleted_at: The date that the product was deleted from the catalog.
• product_categories: All of the product categories associated with the product.
• last_ordered_at: The most recent date the item was ordered.
• num_times_ordered: The number of times the product was ordered (all-time).
• num_times_returned: The total number of times the product was returned (all-time).
• created_by: The product upload method to the catalog.
• created_by_user_name: The name of the user who added the product.
• created_by_user_email: The email of the user who added the product.
• deleted_by_user_name: The name of the user who removed the product.
• deleted_by_user_email: The email of the user who removed the product.
• url: The original URL of the product from the vendor’s website.
• created_by_user_id: The unique ID of the user who added the product.
• deleted_by_user_id: The unique ID of the user who removed the product.

Accounting Codes 

• code_category_position: The position of your accounting code category (e.g. 1, 2, 3, etc.), as determined on your code category settings page
• associated_to: The object type that the accounting code category is associated with (e.g. Product Category, Product, User, Location, etc.) for mapping purposes
• associated_to_value: The value of the object that a code is mapped to (e.g. if a user, “John Doe” or if a location “New York/23rd street branch”). A single code can be mapped to multiple objects.
• tax_exemption: Indicates whether the accounting code is tax exempt.
• code_created_at: The date indicating when the accounting code was created.
• before_event_state: The substitution preference before it was changed.
• after_event_state: The substitution preference after it was changed.

Vendor Fulfillment Rules 

• num_active_products_for_vendor: The number of active products in your catalog associated with the vendor.
• order_volume: The sum of all order totals associated with the vendor including both Order.co paid orders and those paid for by your business.
• paid_by_order_co_volume: The sum of all order totals associated with the vendor paid by Order.co.
• num_orders: The number of orders fulfilled by the vendor.
• linked_account_username: The linked vendor account user name used when fulfilling orders with the vendor.
• linked_account_status: The status of the linked account (e.g. verified, undetermined, incorrect, or incomplete_verified). Incorrect or Incomplete may result in outreach from Order.co Support to finish validating your linked vendor account
• account_linked_at: The date when the account was linked.
• contact_email_addresses: List of the vendor contact’s email addresses used for emailed purchase orders.

Invoice and Credit Memo Breakdown 

• id: The invoice or credit memo ID.
• credit_memo_id: The credit memo ID.
• due_at: The date the invoice is due.
• paid_at: The date the invoice was paid. This will be blank if unpaid
• used_at: The date when the credit memo was used.
• late_fee: Any late fees associated with the invoice.
• charge_id: The Stripe charge ID associated with the invoice. This is helpful to provide to Support when troubleshooting payment issues.
• credit_memos_total: The total of the credit memos used to pay the invoice. Varies when paying multiple invoices or using multiple credit memos at once.
• affected_invoices_total: The total of the invoices before using credit memos. Varies when paying multiple invoices or using multiple credit memos at once.
• credit_memo_ids: The credit memos used to pay the invoice(s). Varies when paying multiple invoices or using multiple credit memos at once.
• affected_invoice_ids: The invoices partial paid by credit memos. Varies when paying multiple invoices or using multiple credit memos at once.
• credit_memo_from_invoice_ids: This field shows the Invoice IDs that contributed to a credit memo (e.g. Rewards)
• is_credit_memo: Determines whether the invoice_id is a credit memo.
• transaction_id: The transaction ID for Virtual Card charges and refunds.
• virtual_card_name: The name of the Virtual Card order.
• transaction_date: The date of the Virtual Card transaction.

User Activity

• event_id: The Order.co event ID.
• event_at: The date and time when the event occurred (UTC Timezone).
• event: The event that happened (e.g., create, update, or destroy).
• event_domain: The area of activity (e.g., Accounting Code, Approval, etc.).
• event_change_type: The type of change made.
• changed_by_id: The user ID making the change.
• changed_by_name: The user name making the change.
• changed_by_email: The user email making the change.
• before_event_state: The state before the change.
• after_event_state: The state after the change.
• current_state: The current state of the variable.
• assigned_by_type: The object affected by the change.
• assigned_by_detail: The detail of the object affected by the change.

Spend by Cost Center 

• completed_time_interval: The completion date of orders, returns, Virtual Card charges, and refunds truncated to a time interval (e.g., Week, Month).
• number_of_ordering_users: The number of unique users ordering.
• number_of_ordering_locations: The number of unique locations ordering.
• number_of_orders: The number of unique Catalog and Virtual Cards.
• number_of_returns: The number of unique returns.
• total_spend: The total amount of spend.

Spend by Location 

• completed_time_interval: The completion date of orders, returns, Virtual Card charges, and refunds truncated to a time interval.
• number_of_ordering_users: The number of unique users ordering.
• number_of_ordering_locations: The number of unique locations ordering.
• number_of_orders: The number of unique Catalog and Virtual Cards.
• number_of_returns: The number of unique returns.
• total_spend: The total amount of spend.

Spend by User

• completed_time_interval: The completion date of orders, returns, Virtual Card charges, and refunds truncated to a time interval.
• number_of_orders: The number of unique Catalog and Virtual Cards.
• number_of_returns: The number of unique returns.
• total_spend: The total amount of spend.

Spend by Vendor 

• completed_time_interval: The completion date of orders, returns, Virtual Card charges, and refunds truncated to a time interval.
• return_rate: The number of returns over the number of orders.
• total_spend: The total amount of spend.
• number_of_ordering_users: The number of unique users ordering.
• number_of_orders: The number of unique Catalog and Virtual Cards.
• number_of_returns: The number of unique returns.

Spend by Code 

• code_category: The code category position as seen in the Manage Organization > Coding page.
• value: The given name for the code category.
• code: The concatenated value of the code and description.
• number_of_disctinct_products: The number of unique products ordered.
• total_spend: The total amount of spend.

Spend by Product and Product Category 

• total_quantity: The total quantity of the product ordered and returned.
• average_ppu: The average price per unit for the line item.
• total_spend: The total amount of money spent on this product or category.