Quick Start
- Step 1 - Specify HTTPS Endpoint URI
- Step 2 - Receive Data
- Step 3 - Request Baseload of Data
- Step 4 - Validate
RetailOps Reporting ETL Module
The standard reporting system which is built into the RetailOps platform contains the most robust, granular, and comprehensive representation of business data which passes through the operational portion of the RetailOps platform. The reason for this is that is was designed from the ground up to prepare and format this data in a way which is comprehensive, and specifically suitable for analytics queries.
However, one crucial way in which the RetailOps Reporting system as a whole is commonly unsatisfactory for servicing the stated goal above is that it cannot presently store or report on data of a general purpose nature and foreign origin, for instance click-through data from email campaigns, site traffic data, etc. While we have many capable partners which are capable of servicing this need, RetailOps itself is not focuses on these types of ancillary data at this time. Only data which RetailOps operates on, and for which it is the system-of-record may be present in the RetailOps reporting system.
Fortunately, these things may be decoupled. RetailOps is capable of exporting this high-quality data representation without requiring the use of the RetailOps analytics database or query UI.
We call this the RetailOps ETL1 Module.
The process flow for this is as follows:
- A change is made to the RetailOps Operational System, for instance and order is imported or shipped
- The RetailOps reporting system is notified of this change, and extracts the appropriate data from the operational system, sufficient to render it into a special format (discussed below)
- The data is saved to the RetailOps standard analytics "Target" database, but an external "Target" may be enabled such that this data is ALSO conveyed to an arbitrary HTTPS endpoint (or directly to and analytics provider/database, if a custom connector is created
1 ETL is an industry-standard term meaning Extract (from operational datastore), Transform (into a format appropriate for analytics querying), and Load (Into a datastore appropriate for analytic query performance)
The above pipeline ensures that operational data is materialized into the proper format suitable for analytics on a near-realtime basis, and that it is sent to the appropriate destination with all necessary referential integrity.
The manner of representation of this data, which the RetailOps reporting system utilizes, is known in the IT industry as a Star Schema2 (or more specifically in our case, a Snowflake Schema3.) The purpose for this representational format is to enable easy of querying, as well as efficient and accurate calculation of aggregated, histograms, and other OLAP query workloads necessary for serving business intelligence needs.
2 https://en.wikipedia.org/wiki/Star_schema
3 https://en.wikipedia.org/wiki/Snowflake_schema
BI, Query Visualization tools/services, Schematic Stability, and Suitability
Most Business Intelligence and query visualization tools/services are most suited for querying data which is represented using a star or snowflake schema, as is the case here. Some of these systems allow for foreign-key relationships to be defined in an automated fashion, manual entry of these relationships is also quite common in practice. Given that the RetailOps Reporting ETL System provides data for seventy-two4 different tables, hundreds of foreign-key relations, and potentially thousands5 of different field instances - It may therefore be prudent for implementers of analytics target database connectors to consider implementation of an automated update process for foreign key and field mappings in their respective schematic description systems. Please see subsequent sections for details on tables/fields exported, and a representative sample of interchangeable data. As shown below, the RetailOps Reporting ETL System provides this schematic metadata in a systematized fashion. For most users of OLAP databases or BI tools, this tends to be conceptualized as something which is manually configured just once, and remains static from that point onward. This is not a wholly unreasonable interpretation of the experience you will receive, as the changes overall will be modest over time, but it's not actually how the RetailOps Reporting ETL System should be conceptualized. It may create subtle and time-sensitive disruptions of Business Intelligence functions when such changes do occur, especially if they are reliant on recently created attributes or other user-defined fields. These user-defined fields are subject to additional or removal from the ETL data and matadata schema commensurate with their creation, enablement, or disablement in their respective administration tools. Wholly new tables and non-user-defined fields may be added to commensurate with future RetailOps product development. It is important to understand that existing non-user-defined tables and fields are subject to removal due to RetailOps product development efforts only with substantial forewarning and discussion.
4 As of the time of this writing.
5 Depending on the number of attributes defined in the account
Base Loading RetailOps Data
One major advantage of the RetailOps Reporting ETL module is that it is designed to perform base-loads. That means: conveyance of all historical data for all of the supported tables, and to do so in a referentially-correct fashion. It is designed to operate a maximally efficient and time-sensitive manner. This process may be activated initially, to populate a new or empty analytics database. It also may be employed as needed to re-synchronize in the event that any "drift" (i.e. data loss or omission) has occurred, or to reapply data to an analytics database in cases where the updating logic was intentionally changed.
It is very useful to note: During this process, updates which occur during the natural course of RetailOps usage (user edits, and etc...) are highly prioritized over base-load traffic. This has the desirable effect of avoiding log-jamming or stalling the conveyance of meaningful updates due to the huge volume of base-load traffic which pay be ongoing at the time. In other words, you don't need to be afraid of initiating a base-load during times of business operation - The regular updates will go through in a timely fashion, even if the base-load overall has many hours or days remaining until completion.
Incremental Updating and Continuity of RetailOps Data
After the initial base-load (and also during any base-load) it is important that operational system edits be conveyed to the target analytics database in a timely fashion. Using RetailOps Reporting ETL Module, updates are typically conveyed in under 60 seconds. This may occasionally be higher depending on the level of traffic, but it is exceptionally rare. The biggest determining factor in this responsiveness is the latency with which the endpoint processes and acknowledges the update request.
An important feature of the RetailOps Reporting ETL Module is that endpoint crashes or disconnections do not result in data loss. If the endpoint is properly implemented such that it only acknowledges received updates in the affirmative AFTER they have been committed to the destination analytics database, then it is impossible to have any data loss which is attributable to the transfer process. A failed attempt to convey a series of updates to the endpoint will simply be retired, and updates will not progress until the error has been cleared, causing the requisite update events to properly be committed
This behavior is essential to maintaining data integrity – Not only for the purpose of avoiding gaps in a single table, but also for maintaining referential integrity. Analytics queries can very easily misbehave, providing erroneous figures if referential integrity is not maintained. If a record which is referenced by a foreign key is missing from the analytics database, then any query which contains that foreign key will silently omit that record in any of its calculations. This error mode is insidiously difficult to detect, and can easily have a deleterious effect on informed business decision making.
Fidelity and Auditing of Data ( Optional, but recommended )
We believe that it’s not enough simply to hope your analytics data is accurate. Despite the above features to ensure continuity, there are various data-loss scenarios remain possible, and in fact inevitable in an analytics database. The only matter in doubt is that of scale and frequency of occurrence. Is it one record lost over a span of years, hundreds of records per month, or perhaps worse? In our experience, such data corruption is nearly always the result of some seemingly-trivial human error in the implementation process, such as a process that behaves well under normal circumstances, but fails subtly under network degradation, or other form of duress.
As such, we recommend a regime of automated verification, wherein the system is constantly scanning for discrepancies at a low level of intensity. When a discrepancy is detected, the user should be notified, and a partial or full base-load may be triggered as a remedial step. This mechanism is implemented as a separate HTTP endpoint which retrieves data for a specific record which was previously provided via the standard ETL Update mechanism described above.
Enablement of this functionality will afford not only a degree of prevention, but will also empower our technical support team to, in a very specific and limited way, probe the target analytics database as necessary to assist with diagnostic inquiries. It’s important to note that the absence of conclusive information on the present state of remote target databases which are outside of our control quite often leads to confusion and greatly-increased time and effort being required for the diagnostic process.
Management of operational schematic changes
Changes to RetailOps ETL data structures are almost always additive in terms of new fields and new tables. Any changes that remove fields or tables, or other breaking changes will only ever be done exceptionally rarely, and with ample forewarning from the RetailOps support team. Therefore, it's technically safe to take a minimal approach, and manually define the schema in the analytics database. When those additive changes occur, they are typically at the behest of a business user, and in response to some recent business consideration or requirement, for instance the collection of a new order attribute from a channel, new product attribute, etc. This applies both to existing data sources, and new data sources which are yet to be created in the course of the RetailOps product roadmap.
Per the above, interchanges received will contain metadata and data for each update. There is no expressed indication within a given interchange document that DDL6 may be required to apply it and subsequent interchanges to the target database. The list of tables and fields will simply grow commensurate with the aforementioned changes. These interchange documents contain data and metadata for a wide variety of data sources, but it should be noted that their overarching structure is consistent, and there is only a single type of interchange for all updates – No differentiation is made between those requiring DDL+DML7 versus those only requiring DML. It is the responsibility of the database connector logic to determine and apply the commensurate DDL when necessary.
6 https://en.wikipedia.org/wiki/Data_definition_language
7 https://en.wikipedia.org/wiki/Data_manipulation_language
Manual DDL Application
If desired, you can choose to manually perform the DDL when needed – it's not an entirely unacceptable option abstractly. However, it's important to be aware that there are some significant downsides to this: First, it will require the dev team notice that the field is missing and then rally their dev team to respond to the change. Second, at the point where they've noticed the missing field, they will have been acknowledging the updates received via the web endpoint for some time, despite not actually applying all of that data to their analytics database. Once the DDL has been performed manually, and the code updated to assimilate that data, a full base-load will have to be triggered for that table because the RetailOps ETL system does not know what prior acknowledgements were accurate, with regard to the application of the full set of fields, versus those that only applied the partial set of fields.
Key robustness features which are recommended
-
Automatic application of DDL to reflect changes in the provided metadata
- The correct ordering of interchanges and events when a DDL event must be applied
- The correct acknowledgement of of interchanges such that update data is not lost during, before, or after DDL statements
- Metadata recording in the target database
- Active data auditing( automated spot checks for data inaccuracies)
RetailOps Reporting System Table Names (Snowflake Schema)
Note: Subject to change. Dynamic/multivalued fields included only once below. May expand to many instances in production use
aclpriv
id
name
aclrole
id
name
adjustment
id
lot ref: lot.id (Lot)
date
quantity
reason
to_container_temporal ref: temporalcontainer.id (Temporal Container)
to_container ref: container.id (Container)
container ref: container.id (Container)
reason_code
abs_quantity
action
user ref: user.id (User)
date_civil ref: gregorian_day.id (Gregorian Day)
container_temporal ref: temporalcontainer.id (Temporal Container)
subaction
area
id
name
attribute_value
id
group2
name
group1
path
meta
brand
id
name
cancel_reason
id
name
code
cash_tray_session
id
date_closed_civil ref: gregorian_day.id (Gregorian Day)
status
printer_name
date_opened_civil ref: gregorian_day.id (Gregorian Day)
opened_by ref: user.id (User)
facility ref: facility.id (Facility)
date_opened
tray_name
closed_by ref: user.id (User)
date_closed
channel
id
name
conf_address
id
country ref: country.id (Country)
city
postal_code
address3
fax
address1
phone
state ref: state.id (State)
address2
container
id
zone ref: zone.id (Zone)
area ref: area.id (Area)
name
path
facility ref: facility.id (Facility)
type
country
id
name
currency
id
iso_4217_numeric
iso_4217
name
customer
id
name
tags
store_credit
customer_address
id
country ref: country.id (Country)
city
postal_code
customer ref: customer.id (Customer)
addressee
address1
address2
state ref: state.id (State)
phone_number
company
address_name
discount_reason
id
name
code
duplicate_event
id
duplicate_sku ref: sku.id (SKU)
date_detected_civil ref: gregorian_day.id (Gregorian Day)
primary_sku ref: sku.id (SKU)
status
date_detected
date_resolved
date_resolved_civil ref: gregorian_day.id (Gregorian Day)
extlot
id
received_quantity
date_created_civil ref: gregorian_day.id (Gregorian Day)
date_created
code
unit_cost
sku ref: sku.id (SKU)
received_by ref: user.id (User)
external_sku ref: extsku.id (External SKU)
po ref: po.id (PO)
present_quantity
extsku
id
cost
sku ref: sku.id (SKU)
mode
active
date_created
date_created_civil ref: gregorian_day.id (Gregorian Day)
vpc
vendor ref: vendor.id (Vendor)
weighted_cost
present_advice
adjusted_quantity
inflight_quantity
facility
id
name
fulfillment_actor
id
params
status
name
sortval
type
vendor ref: vendor.id (Vendor)
facility ref: facility.id (Facility)
fulfillment_item
id
sales_item ref: sales_item.id (Sales Item)
sku ref: sku.id (SKU)
shipped_by ref: user.id (User)
sequence
shipped_at_civil ref: gregorian_day.id (Gregorian Day)
local_lot ref: lot.id (Lot)
from_container ref: container.id (Container)
shipped_at
fulfillment_type
source_vendor ref: vendor.id (Vendor)
external_lot ref: extlot.id (Extlot)
ship_class
quantity
ship_unit_cost
procurement_type
goods_unit_cost
shipped_from ref: fulfillment_actor.id (Fulfillment Actor)
ship_tracking
ship_unit_weight
ship_carrier
unit_ship_price
unit_price
shipment ref: shipment.id (Shipment)
wave ref: fulfillment_wave.id (Shipment Wave)
unit_duties_cost
unit_freight_cost
external_sku ref: extsku.id (External SKU)
po ref: po.id (PO)
unit_discount
unit_duties_cost_per
unit_discount_per
unit_ship_price_per
ship_unit_cost_per
goods_unit_cost_per
unit_freight_cost_per
unit_price_per
package_id
fulfillment_wave
id
created
created_civil ref: gregorian_day.id (Gregorian Day)
shipments
ship_container ref: container.id (Container)
gregorian_day
id
month_en
month
dow_en
day_of_month
year
ymd
lot
id
received_quantity
sku ref: sku.id (SKU)
received_by ref: user.id (User)
date_created
extended_cost
unit_weight
date_created_civil ref: gregorian_day.id (Gregorian Day)
unit_cost
manifest_item ref: po_manifest_item.id (PO Manifest Item)
po_item ref: po_item.id (PO Item)
po ref: po.id (PO)
active
received_container ref: container.id (Container)
received_at_facility ref: facility.id (Facility)
unit_duties_cost
unit_freight_cost
expiry_date_civil ref: gregorian_day.id (Gregorian Day)
expiry_date
serial_number
media_format
id
handle
name
media_upstream_host
id
host
media_work
id
original_width
registered_at
origin_url
tags
host ref: media_upstream_host.id (Attribute Value)
versions
data_size
mediakey
original_format ref: media_format.id (Brand)
loaded_at
original_height
uploaded_by ref: user.id (User)
order
id
cost
grand_total
order_quantity
date_created
channel ref: channel.id (Channel)
tax
bill_addr ref: customer_address.id (Customer Address)
tags
date_created_civil ref: gregorian_day.id (Gregorian Day)
shipping_total
ship_addr ref: customer_address.id (Customer Address)
ship_service
customer ref: customer.id (Customer)
phys_ships
pct_margin
sub_total
reference_number
status
product_total
flat_discount
subchannels
original_order_quantity
channel_date
channel_date_civil ref: gregorian_day.id (Gregorian Day)
active
select_attr ref: attribute_value.id (Attribute Value)
text_attr
type
percent_discount
effective_discount_percent
total_discount
price_attr
number_attr
grand_total_per
shipping_total_per
sub_total_per
counterparty_currency ref: currency.id (Currency)
tax_total_per
product_total_per
base_currency ref: currency.id (Currency)
total_discount_per
discount_amt_per
cost_per
date_completed
date_completed_civil ref: gregor
payment_trans
id
date
status
series_id
message
account_id
parent_id
payment_processor
amount
order ref: order.id (Order)
user ref: user.id (User)
action
type
date_civil ref: gregorian_day.id (Gregorian Day)
select_attr ref: attribute_value.id (Attribute Value)
number_attr
price_attr
text_attr
cash_tray_session ref: cash_tray_session.id (Cash Tray Session)
po
id
shipto_actor ref: fulfillment_actor.id (Fulfillment Actor)
status
terms
date_created
date_submit_civil ref: gregorian_day.id (Gregorian Day)
date_start
discount_amt
creator_user ref: user.id (User)
date_stop
date_submit
date_submitted
date_stop_civil ref: gregorian_day.id (Gregorian Day)
ship_speed
routing_guide
total
discount_pct
date_created_civil ref: gregorian_day.id (Gregorian Day)
buyer_user ref: user.id (User)
ship_addr ref: customer_address.id (Customer Address)
payment_method
notes
date_submitted_civil ref: gregorian_day.id (Gregorian Day)
interchange_id
type
date_start_civil ref: gregorian_day.id (Gregorian Day)
vendor ref: vendor.id (Vendor)
idx
duties_amt
freight_amt
price_attr
text_attr
select_attr ref: attribute_value.id (Attribute Value)
number_attr
dock_date_latest
dock_date_first_civil ref: gregorian_day.id (Gregorian Day)
dock_date_first
dock_date_latest_civil ref: gregorian_day.id (Gregorian Day)
effective_discou
po_item
id
product_desc
vendor_product_code
sortval
brand ref: brand.id (Brand)
line_text
total_quant
item_id
group ref: po_item_group.id (PO Item Group)
discount_amt
po ref: po.id (PO)
date_added_civil ref: gregorian_day.id (Gregorian Day)
cost
product_ident
discount_pct
date_added
family
active
product ref: product.id (Product)
estimated_ship_date
estimated_ship_date_civil ref: gregorian_day.id (Gregorian Day)
effective_discount
po_item_group
id
status
name
po ref: po.id (PO)
sortval
po_manifest
id
date_created_civil ref: gregorian_day.id (Gregorian Day)
user ref: user.id (User)
date_created
idx
po_manifest_item
id
product_ident
map_price
quantity_expected
date_created
vendor_product_code
po_item ref: po_item.id (PO Item)
description
manifest ref: po_manifest.id (PO Manifest)
msrp_price
unit_cost
po ref: po.id (PO)
family
date_created_civil ref: gregorian_day.id (Gregorian Day)
type
po_quant
id
date_submitted
sku ref: sku.id (SKU)
quantity
po_item ref: po_item.id (PO Item)
quant_id
group ref: po_item_group.id (PO Item Group)
date_inflight_expires
extsku ref: extsku.id (External SKU)
date_added
date_submitted_civil ref: gregorian_day.id (Gregorian Day)
date_added_civil ref: gregorian_day.id (Gregorian Day)
po ref: po.id (PO)
date_inflight_expires_civil ref: gregorian_day.id (Gregorian Day)
column
active
inflight_quantity
product
id
map_price
number
name
offer_price
brand ref: brand.id (Brand)
select_attr ref: attribute_value.id (Attribute Value)
created_at
created_at_civil ref: gregorian_day.id (Gregorian Day)
text_attr
msrp_price
price_attr
number_attr
product_association
id
type
active
quantity
to_sku ref: sku.id (SKU)
params
from_sku ref: sku.id (SKU)
to_product ref: product.id (Product)
from_product ref: product.id (Zone)
return
id
order ref: order.id (Order)
rma ref: rma.id (RMA)
processed_by ref: user.id (User)
processed_at_civil ref: gregorian_day.id (Gregorian Day)
refund_action
processed_at
return_item
id
did_shipping
sales_item ref: sales_item.id (Sales Item)
return_product_amt
status
return_tax_amt
return_date_created_civil ref: gregorian_day.id (Gregorian Day)
return_date_created
inventory_action
quantity_returned
order ref: order.id (Order)
return_shipping_tax_amt
return_subtotal_amt
return_processed_by ref: user.id (User)
return_giftwrap_amt
rma_item ref: rma_item.id (RMA Item)
return ref: return.id (Return)
return_reason ref: return_reason.id (Return Reason)
did_refund
return_shipping_amt
return_discount_amt
rma ref: rma.id (RMA)
return_refund_amt
return_giftwrap_tax_amt
did_restock
return_giftwrap_amt_per
return_subtotal_amt_per
return_shipping_tax_amt_per
return_shipping_amt_per
return_refund_amt_per
return_discount_amt_per
return_giftwrap_tax_amt_per
return_tax_amt_per
return_product_amt_per
lot ref: lot.id (Lot)
return_reason
id
name
code
rma
id
order ref: order.id (Order)
rma_item
id
sales_item ref: sales_item.id (Sales Item)
status
item_refund_amt
will_shipping
date_created
restocking_fee
item_shipping_tax_amt
order ref: order.id (Order)
restocked_at ref: fulfillment_actor.id (Fulfillment Actor)
item_tax_amt
item_shipping_amt
item_giftwrap_amt
quantity_authorized
authorized_shipping_amt
item_product_amt
return_reason ref: return_reason.id (Return Reason)
date_created_civil ref: gregorian_day.id (Gregorian Day)
will_refund
item_subtotal_amt
will_restock
rma ref: rma.id (RMA)
item_discount_amt
item_giftwrap_tax_amt
item_shipping_amt_per
authorized_shipping_amt_per
item_subtotal_amt_per
item_discount_amt_per
item_giftwrap_amt_per
item_shipping_tax_amt_per
restocking_fee_per
item_product_amt_per
item_giftwrap_tax_amt_per
item_refund_amt_per
item_tax_amt_per
sales_item
id
cost_or_estimate
quantity
cost_or_zero
sku_id ref: sku.id (SKU)
sale_time
fulfilled_quantity
fulfillment_time_civil ref: gregorian_day.id (Gregorian Day)
order ref: order.id (Gregorian Day)
fulfillment_time
sale_time_civil ref: gregorian_day.id (Gregorian Day)
price
extended_price
flat_discount
subchannel
status
original_quantity
active
upstream_fulfillment
effective_unit_discount
effective_discount_percent
effective_tax_percent
effective_unit_tax
effective_unit_shipping
direct_unit_tax
unit_price_per
unit_tax_per
cost_or_zero_per
extended_price_per
effective_unit_shipping_per
effective_unit_tax_per
cost_or_estimate_per
effective_unit_discount_per
discount_amt_per
price_attr
text_attr
select_attr ref: attribute_value.id (Attribute Value)
number_attr
discount_reason ref: discount_reason.id (Discount Reason)
cancel_reason ref: cancel_reason.id (Cancel Reason)
price_at_sale
expected_ship_date_civil ref: gregorian_day.id (Gregori
shipment
id
shipped_at
total_weight
shipped_to ref: customer_address.id (Customer Address)
tracking
total_cost
shipped_at_civil ref: gregorian_day.id (Gregorian Day)
total_cost_per
status
shipment_item
id
extsku ref: extsku.id (External SKU)
sale ref: sales_item.id (Sales Item)
po_item ref: po_item.id (PO Item)
facility ref: facility.id (Facility)
shipment_pick
id
lot ref: lot.id (Lot)
shipment_item ref: shipment_item.id (Shipment Item)
sku
id
map_price
sku
offer_price
date_first_complete_civil ref: gregorian_day.id (Gregorian Day)
date_first_complete
mpn
brand ref: brand.id (Brand)
select_attr ref: attribute_value.id (Attribute Value)
created_at
created_at_civil ref: gregorian_day.id (Gregorian Day)
text_attr
msrp_price
date_last_complete_civil ref: gregorian_day.id (Gregorian Day)
date_last_complete
media
product_name
status
product_id
barcode
type
product ref: product.id (Product)
number_attr
price_attr
sku_media
id
media_work ref: media_work.id (Media Work)
sku ref: sku.id (SKU)
mapping_type
sku_multi_attribute
id
sku ref: sku.id (SKU)
value ref: attribute_value.id (Attribute Value)
state
id
country ref: country.id (Country)
name
storecredit_trans
id
payment_transaction ref: payment_trans.id (Area)
date
reason
amount
customer ref: customer.id (Customer)
agent ref: user.id (User)
date_civil ref: gregorian_day.id (Gregorian Day)
temporal_external_quant
id
date_inserted
active
date_created
quantity
date_created_civil ref: gregorian_day.id (Gregorian Day)
date_inserted_civil ref: gregorian_day.id (Gregorian Day)
extsku ref: extsku.id (External SKU)
user ref: user.id (User)
vendor ref: vendor.id (Vendor)
temporal_price
id
sku ref: sku.id (SKU)
attribute_id
min_quantity
end_date
max_quantity
price
start_date
temporal_userpriv
id
grant_type
date_granted
priv_id ref: aclpriv.id (ACL Privilege)
sequence
role_id ref: aclrole.id (ACL Role)
date_revoked
grant_user_id ref: user.id (User)
date_revoked_civil ref: gregorian_day.id (Gregorian Day)
date_granted_civil ref: gregorian_day.id (Gregorian Day)
revoke_user_id ref: user.id (User)
user_id ref: user.id (User)
temporalcontainer
id
parent ref: temporalcontainer.id (Temporal Container)
path
start_date
container ref: container.id (Container)
temporalquant
id
lot ref: lot.id (Lot)
end_date
quantity
start_date
container ref: container.id (Container)
timezone
id
system_name
name
user
id
name
vendor
id
name
default_terms
default_payment_method
default_discount_pct
status
fulfillment_modes
supports_jit
supports_dropship
supports_3pl
vendor_contact
id
conf_address ref: conf_address.id (Config Address)
jit_email_po
status
name
title
vendor ref: vendor.id (Gregorian Day)
Sample Interchange
Note: Many tables omitted. This is a small portion of a multipart interchange intended to be a representative sample only.
POST to HTTPS endpoint containing:
{
"table_name": "customer",
"data": [
{
"email": "person@gmail.com",
"id": "652393",
"name": "Test Person",
"store_credit": "0",
"tags": "VIP, Test"
}
],
"keys": ["id"],
"fields": [
{
"handle": "id",
"name": "ID",
"type": "reference"
},
{
"handle": "tags",
"name": "Tags",
"type": "string"
},
{
"handle": "email",
"name": "Email",
"type": "string"
},
{
"handle": "store_credit",
"name": "Store Credit",
"type": "currency"
},
{
"handle": "name",
"name": "Name",
"type": "string"
}
]
}
POST to HTTPS endpoint containing:
{
"table_name": "order",
"data": [
{
"active": "1",
"amazon_sfp": "0",
"base_currency": "143",
"bill_addr": "915184",
"cancel_reason": "",
"cash_tray_session": "",
"channel": "1",
"channel_date": "2018-07-12T00:00:00Z",
"channel_date_civil_8946": "736886",
"completed_by": "",
"cost": "98.6",
"cost_per_6688262": "98.6",
"counterparty_currency": "143",
"customer": "542436",
"date_completed": "1970-01-01T00:00:00Z",
"date_completed_civil_6700292": "",
"date_created": "2018-07-12T14:09:41Z",
"date_created_civil_8941": "736887",
"discount_amt_per_6688271": "0",
"discount_reason": "",
"effective_discount_percent": "0.00502613590672463",
"exchange_rate": "1",
"flat_discount": "0",
"grand_total": "198.95",
"grand_total_per_6688280": "198.95",
"id": "1531165",
"order_quantity": "2",
"original_order_quantity": "2",
"pct_margin": "50",
"percent_discount": "0",
"phys_ships": "1",
"product_total": "198.96",
"product_total_per_6688268": "198.96",
"reference_number": "Channel Refnum here",
"select_attr_6742003": "3890",
"select_attr_6742005": "3884",
"select_attr_6742923": null,
"ship_addr": "1018915",
"ship_service": "Standard",
"shipping_total": "0",
"shipping_total_per_6688277": "0",
"status": "Ready",
"sub_total": "198.95",
"sub_total_per_6688259": "198.95",
"subchannels": "",
"tags": "",
"tax": "0",
"tax_total_per_6688265": "0",
"total_discount": "0.1",
"total_discount_per_6688274": "0.1",
"type": "Ecommerce"
}
],
"keys": ["id"],
"fields": [
{
"handle": "type",
"name": "Type",
"type": "string"
},
{
"handle": "amazon_sfp",
"name": "Amazon SFP",
"type": "smallint"
},
{
"handle": "date_completed",
"name": "Date Completed",
"type": "date"
},
{
"handle": "grand_total",
"name": "Grand Total",
"type": "currency"
},
{
"handle": "sub_total_per",
"name": "Sub Total",
"type": "currency"
},
{
"handle": "cost",
"name": "Cost",
"type": "currency"
},
{
"handle": "cash_tray_session",
"name": "Cash Tray Session",
"references": "cash_tray_session",
"type": "reference"
},
{
"handle": "bill_addr",
"name": "Bill Address",
"references": "customer_address",
"type": "reference"
},
{
"handle": "number_attr",
"name": "Number Attr",
"type": "currency"
},
{
"handle": "status",
"name": "Status",
"type": "string"
},
{
"handle": "pct_margin",
"name": "Percent Margin",
"type": "int"
},
{
"handle": "reference_number",
"name": "Reference Number",
"type": "string"
},
{
"handle": "ship_service",
"name": "Ship Service",
"type": "string"
},
{
"handle": "counterparty_currency",
"name": "Counterparty Currency",
"references": "currency",
"type": "reference"
},
{
"handle": "total_discount_per",
"name": "Total Discount",
"type": "currency"
},
{
"handle": "discount_reason",
"name": "Discount Reason",
"references": "discount_reason",
"type": "reference"
},
{
"handle": "date_completed_civil",
"name": "Date Completed",
"references": "gregorian_day",
"type": "reference"
},
{
"handle": "exchange_rate",
"name": "Exchange Rate",
"type": "currency"
},
{
"handle": "date_created_civil",
"name": "Created",
"references": "gregorian_day",
"type": "reference"
},
{
"handle": "base_currency",
"name": "Base Currency",
"references": "currency",
"type": "reference"
},
{
"handle": "completed_by",
"name": "Completed By",
"references": "user",
"type": "reference"
},
{
"handle": "tags",
"name": "Tags",
"type": "string"
},
{
"handle": "phys_ships",
"name": "Physical shipment count",
"type": "int"
},
{
"handle": "sub_total",
"name": "Subtotal",
"type": "currency"
},
{
"handle": "original_order_quantity",
"name": "Original Order Quantity",
"type": "int"
},
{
"handle": "product_total",
"name": "Product Total",
"type": "currency"
},
{
"handle": "ship_addr",
"name": "Ship Address",
"references": "customer_address",
"type": "reference"
},
{
"handle": "total_discount",
"name": "Total Discount",
"type": "currency"
},
{
"handle": "active",
"name": "Active",
"type": "smallint"
},
{
"handle": "flat_discount",
"name": "Flat Discount",
"type": "currency"
},
{
"handle": "text_attr",
"name": "Text Attr",
"type": "string"
},
{
"handle": "date_created",
"name": "Created",
"type": "date"
},
{
"handle": "channel",
"name": "Channel",
"references": "channel",
"type": "reference"
},
{
"handle": "shipping_total_per",
"name": "Shipping Total",
"type": "currency"
},
{
"handle": "select_attr",
"name": "Select Attr",
"references": "attribute_value",
"type": "reference"
},
{
"handle": "customer",
"name": "Customer",
"references": "customer",
"type": "reference"
},
{
"handle": "cost_per",
"name": "Cost",
"type": "currency"
},
{
"handle": "price_attr",
"name": "Price Attr",
"type": "currency"
},
{
"handle": "effective_discount_percent",
"name": "Effective Discount Percent",
"type": "currency"
},
{
"handle": "discount_amt_per",
"name": "Flat Discount",
"type": "currency"
},
{
"handle": "channel_date",
"name": "Channel Date",
"type": "date"
},
{
"handle": "product_total_per",
"name": "Product Total",
"type": "currency"
},
{
"handle": "grand_total_per",
"name": "Grand Total",
"type": "currency"
},
{
"handle": "id",
"name": "ID",
"type": "reference"
},
{
"handle": "channel_date_civil",
"name": "Channel Date",
"references": "gregorian_day",
"type": "reference"
},
{
"handle": "order_quantity",
"name": "Order Quantity",
"type": "int"
},
{
"handle": "cancel_reason",
"name": "Cancel Reason",
"references": "cancel_reason",
"type": "reference"
},
{
"handle": "tax",
"name": "Tax",
"type": "currency"
},
{
"handle": "subchannels",
"name": "Subchannel(s)",
"type": "string"
},
{
"handle": "shipping_total",
"name": "Shipping",
"type": "currency"
},
{
"handle": "percent_discount",
"name": "Percent Discount",
"type": "currency"
},
{
"handle": "tax_total_per",
"name": "Tax Total",
"type": "currency"
}
]
}
POST to HTTPS endpoint containing:
{
"table_name": "sales__item",
"data": [
{
"active": "1",
"cancel_reason": "",
"cost_or_estimate": "34.85",
"cost_or_estimate_per_6688340": "34.85",
"cost_or_zero": "0",
"cost_or_zero_per_6688364": "0",
"direct_unit_tax": "0",
"discount_amt_per_6688349": "0",
"discount_reason": "",
"effective_discount_percent": "0",
"effective_tax_percent": "0",
"effective_unit_discount": "0",
"effective_unit_discount_per_6688355": "0",
"effective_unit_shipping": "0",
"effective_unit_shipping_per_6688358": "0",
"effective_unit_tax": "0",
"effective_unit_tax_per_6688352": "0",
"expected_arrival_date": "2018-07-29T00:00:00Z",
"expected_arrival_date_civil_6735934": "736903",
"expected_ship_date": "2018-07-20T00:00:00Z",
"expected_ship_date_civil_6735734": "736894",
"extended_price": "68.98",
"extended_price_per_6688346": "68.98",
"flat_discount": "0",
"fulfilled_quantity": "0",
"fulfillment_time": "1970-01-01T00:00:00Z",
"fulfillment_time_civil_8989": "",
"id": "2117684",
"order": "1531165",
"original_quantity": "1",
"price": "68.98",
"price_at_sale_6727366": "68.98",
"price_at_sale_6727370": "98",
"price_at_sale_6727422": null,
"quantity": "1",
"sale_time": "2018-07-12T14:09:41Z",
"sale_time_civil_8977": "736887",
"sku_id": "152004",
"status": "Active",
"subchannel": "",
"unit_price_per_6688343": "68.98",
"unit_tax_per_6688361": "0",
"upstream_fulfillment": ""
}
],
"keys": [‘id’],
"fields": [
{
"handle": "effective_unit_shipping",
"name": "Effective Unit Shipping",
"type": "currency"
},
{
"handle": "expected_ship_date",
"name": "Expected Ship",
"type": "date"
},
{
"handle": "effective_discount_percent",
"name": "Effective Discount Percent",
"type": "currency"
},
{
"handle": "extended_price",
"name": "Extended Price",
"type": "currency"
},
{
"handle": "quantity",
"name": "Quantity",
"type": "int"
},
{
"handle": "effective_unit_discount",
"name": "Effective Unit Discount",
"type": "currency"
},
{
"handle": "expected_ship_date_civil",
"name": "Expected Ship",
"references": "gregorian_day",
"type": "reference"
},
{
"handle": "cost_or_zero",
"name": "Cost or Zero",
"type": "currency"
},
{
"handle": "flat_discount",
"name": "Flat Discount",
"type": "currency"
},
{
"handle": "price",
"name": "Unit Price",
"type": "currency"
},
{
"handle": "fulfillment_time_civil",
"name": "Fulfillment Time",
"references": "gregorian_day",
"type": "reference"
},
{
"handle": "cost_or_estimate_per",
"name": "Cost or Estimate",
"type": "currency"
},
{
"handle": "expected_arrival_date",
"name": "Expected Arrival",
"type": "date"
},
{
"handle": "cost_or_zero_per",
"name": "Cost or Zero",
"type": "currency"
},
{
"handle": "active",
"name": "Active",
"type": "smallint"
},
{
"handle": "direct_unit_tax",
"name": "Direct Unit Tax",
"type": "currency"
},
{
"handle": "status",
"name": "Status",
"type": "string"
},
{
"handle": "sku_id",
"name": "SKU",
"references": "sku",
"type": "reference"
},
{
"handle": "sale_time_civil",
"name": "Sale Time",
"references": "gregorian_day",
"type": "reference"
},
{
"handle": "subchannel",
"name": "Subchannel",
"type": "string"
},
{
"handle": "unit_tax_per",
"name": "Direct Unit Tax",
"type": "currency"
},
{
"handle": "extended_price_per",
"name": "Extended Price",
"type": "currency"
},
{
"handle": "upstream_fulfillment",
"name": "Upstream Fulfillment",
"type": "string"
},
{
"handle": "effective_unit_discount_per",
"name": "Effective Unit Discount",
"type": "currency"
},
{
"handle": "number_attr",
"name": "Number Attr",
"type": "currency"
},
{
"handle": "fulfilled_quantity",
"name": "Fulfilled Quantity",
"type": "int"
},
{
"handle": "effective_tax_percent",
"name": "Effective Tax Percent",
"type": "currency"
},
{
"handle": "price_at_sale",
"name": "Price At Sale",
"type": "currency"
},
{
"handle": "id",
"name": "ID",
"type": "reference"
},
{
"handle": "effective_unit_shipping_per",
"name": "Effective Unit Shipping",
"type": "currency"
},
{
"handle": "price_attr",
"name": "Price Attr",
"type": "currency"
},
{
"handle": "text_attr",
"name": "Text Attr",
"type": "string"
},
{
"handle": "select_attr",
"name": "Select Attr",
"references": "attribute_value",
"type": "reference"
},
{
"handle": "effective_unit_tax",
"name": "Effective Unit Tax",
"type": "currency"
},
{
"handle": "cancel_reason",
"name": "Cancel Reason",
"references": "cancel_reason",
"type": "reference"
},
{
"handle": "expected_arrival_date_civil",
"name": "Expected Arrival",
"references": "gregorian_day",
"type": "reference"
},
{
"handle": "discount_amt_per",
"name": "Flat Discount",
"type": "currency"
},
{
"handle": "original_quantity",
"name": "Original Quantity",
"type": "int"
},
{
"handle": "fulfillment_time",
"name": "Fulfillment Time",
"type": "date"
},
{
"handle": "order",
"name": "Order",
"references": "order",
"type": "reference"
},
{
"handle": "sale_time",
"name": "Sale Time",
"type": "date"
},
{
"handle": "discount_reason",
"name": "Discount Reason",
"references": "discount_reason",
"type": "reference"
}}
Comments
0 comments
Please sign in to leave a comment.