- What is it?
- Who can use it?
- When should I use it?
- How can I unload this data?
- FAQs
- Table & column properties
What is it?
RyderShip Data is a powerful data warehouse solution built on Amazon’s Redshift that allows RyderShip customers to self-serve their data needs with SQL queries. We provide a daily copy of your account data from the RyderShip core database into your Redshift instance, allowing you to plug in your Business Intelligence (BI) tools and create custom reports.
RyderShip Data offers comprehensive data access similar to what's available in the RyderShip app or the v2 API, but with greater flexibility and performance.
Who can use it?
If you're interested, you can contact your customer success manager or email support@whiplash.com
Users can view data with SQL viewers like Postico, TablePlus, or DBeaver. SQL proficiency is required to effectively use these tools.
When should I use it?
RyderShip Data is great for building reports when pulling large amounts of data across an API is impractical. Because RyderShip Data is updated nightly at 10 PM Pacific (5 am UTC) and read-only, it is a helpful addition to the API, but not a replacement.
|
|
How can I unload this data?
Here's a guide from Amazon specific to S3 but applicable to a variety of data warehouse solutions.
FAQs
How often is the data in RyderShip Data refreshed?
The data in RyderShip Data is refreshed nightly at 10 PM Pacific (5 AM UTC).
Are inventory deltas directly available in Redshift?
Redshift does not include explicit inventory deltas in its tables, but users can calculate the deltas by using two distinct inventory snapshot values.
Can I access the same data through the RyderShip app or API?
Yes, RyderShip Data provides similar data access to what is available in the RyderShip app or the v2 API, but with greater flexibility and performance for large-scale queries.
Can I use RyderShip Data to update or modify my account data?
No, RyderShip Data is read-only and cannot be used to update or modify your account data. It is intended for querying and reporting purposes only.
Where can I find additional assistance?
If you have any additional questions or need further assistance, you can contact your customer success manager or email support@whiplash.com
Table & column properties
|
• accounts • invoices • items |
• lots • order_delivered_transactions • orders |
• packages • shops |
| Column | Data Type | Definition |
|---|---|---|
| account_transaction_items | ||
| id | int | |
| account_id | int | |
| account_transaction_id | int | |
| income_account | varchar | |
| amount | decimal(12,2) | |
| account_transactions | ||
| id | int | the account transaction ID |
| account_id | int | the account transaction account ID |
| description | varchar | the account transaction description |
| amount | decimal(12,2) | the account transaction amount |
| invoice_id | int | the account transaction invoice ID |
| created_at | timestamp | the account transaction creation date and time |
| updated_at | timestamp | the account transaction last update date and time |
| income_account | varchar | the account transaction income account |
| balance | decimal(12,2) | the account balance after the account transaction is applied |
| warehouse_id | int | the account transaction warehouse ID |
| currency | varchar | the account transaction currency |
| owner_type | varchar | the type of account transaction |
| owner_id | int | the account transaction owner ID |
| accounts | ||
| id | int | the account ID |
| balance | decimal(12,2) | the current account balance |
| currency | varchar | the account currency (USD, GBP, CAD. etc) |
| accountable_id | int | the ID of the account parent |
| accountable_type | varchar | the type of account (Warehouse, Customer, Partner) |
| refill_amount | decimal(12,2) | the account refill amount |
| payment_method_token | varchar | the account payment method token |
| created_at | timestamp | the account creation date and time |
| updated_at | timestamp | the date and time of the last account update |
| customers | ||
| id | int | the customer ID |
| name | varchar | customer name |
| partner_id | int | customer partner id (admins only) |
| inventory_snapshots | ||
| extraction_date_utc | timestamp | Coordinated Universal Time (UTC) the snapshot was generated |
| warehouse_name | varchar | Rydership warehouse name |
| local_time | timestamp | local time at the warehouse that the snapshot was generated |
| customer_id | int | the ID of the customer |
| item_id | int | the ID of the item |
| warehouse_id | int | the ID of the warehouse |
| shelf_stock | int | sum of all unpicked units on shelf (Sum of Pickable + Backstock roles) |
| in_process_stock | int | the sum of orders that have been batched and picked + any picked transfers |
| damaged_stock | int | damaged stock |
| deactivated_stock | int | deactived stock |
| invoices | ||
| id | int | statement ID |
| account_id | int | account associated with this invoice |
| grand_total | decimal(12,2) | adjusted total of all credit/debit transactions for this billing period |
| billing_period_start | timestamp | billing period start |
| billing_period_end | timestamp | billing period end |
| pdf_url | varchar | url of the invoice as a pdf |
| csv_url | varchar | url of the invoice as a csv |
| item_allocations | ||
| id | int | ID of the allocation of the item(s) - Allocation is defined as assigning the item(s) to a shelf |
| item_id | int | item ID that was allocated to a shelf |
| quantity | int | qty of items that were allocated |
| created_at | timestamp | timestamp the allocation occurred |
| location_id | int | internal Rydership ID of the location that the item(s) were assigned to |
| customer_id | int | ID of the customer |
| item_transactions | ||
| id | int | |
| customer_id | varchar | |
| item_id | int | |
| order_id | int | |
| shipnotice_id | int | |
| user_id | int | |
| rule_id | int | |
| description | varchar | |
| quantity | int | |
| created_at | timestamp | |
| items | ||
| id | int | the item ID |
| sku | varchar | the item SKU number |
| title | varchar | the item title |
| description | varchar | the item description |
| original_location | varchar | the original warehouse location for the item |
| created_at | timestamp | the date and time the item was created |
| updated_at | timestamp | the date and time the item was last updated |
| customer_id | int | the ID of the customer the item belongs to |
| quantity | int | the available quantity of the item |
| weight | decimal(10,3) | the weight of the item (in pounds) |
| available | boolean | is the item available? |
| image_originator_url | varchar | the originator url for the item image |
| vendor | varchar | the item vendor |
| scancode | varchar | the item scancode |
| price | decimal(8,2) | the item price |
| media_mail | boolean | is the item eligible for media mail? |
| packaging | boolean | is the item packaging? |
| length | decimal(8,2) | the item length (in inches) |
| width | decimal(8,2) | the item width (in inches) |
| height | decimal(8,2) | the item height (in inches) |
| active | boolean | is the item active? |
| wholesale_cost | decimal(10,2) | the wholesale cost of the item |
| is_bundle | boolean | is the item a bundle? |
| packaging_type | varchar | |
| promo | boolean | is the item a promo? |
| street_date | timestamp | the item street date |
| category | varchar | the item category |
| include_inbound_in_published | boolean | include inbound items in published? |
| returnable | boolean | is the item returnable? |
| return_sku_match | varchar | the item return SKU match |
| return_price_restricted | boolean | is the item return price restricted? |
| request_serial_number | boolean | does the item require a serial number when shipping? |
| currency | varchar | the item currency |
| tariff_number | varchar | the item harmonized tariff number (for international shipping) |
| label_format | varchar | the item postage label format (epl, png, pdf, etc) |
| notify_originator_inventory | int | notify originator inventory? |
| hazmat | boolean | is the item hazmat? |
| misc | varchar | miscellaneous information about the item |
| ean | varchar | the EAN number for the item |
| low_inventory_threshold | int | the item quantity where this item would be considered having low inventory |
| lot_control | boolean | does this item require lot control? |
| location_adjustments | ||
| id | int | ID of the adjustment |
| location_id | int | ID of the location |
| item_id | int | ID of the item that was adjusted |
| user_id | int | ID of the user carrying out the adjustment |
| customer_id | int | ID of the customer |
| quantity_change | int | quantity that was adjusted (positive or negative) |
| reason_code_id | int | ID of the reason for the adjustment |
| created_at | timestamp | time the adjustment was created |
| updated_at | timestamp | time of last update/change to the adjustment |
| location_audits | ||
| id | int | ID of the location audit |
| item_id | int | Rydership ID of the item that was audited |
| quantity_actual | int | quantity actually physically counted at the conclusion of the audit |
| created_at | timestamp | time the audit started |
| updated_at | timestamp | time of last change/update to the audit |
| quantity_expected | int | quantity before the audit is completed |
| state | varchar | |
| warehouse_id | int | warehouse ID |
| customer_id | int | customer ID |
| location_names | ||
| id | int | |
| location_id | int | |
| location_name_part | varchar | |
| locations | ||
| id | int | the location ID |
| item_id | int | item_id of item in location |
| quantity | int | the number of items at the location |
| warehouse_id | int | warehouse_id of location |
| role | varchar | Pickable, Backstock, etc. See Location Roles for more detailed definitions |
| lot_id | int | associated lot |
| lots | ||
| id | int | the location ID |
| number | varchar | lot number |
| expires_on | timestamp | when merchandise in the lot expires |
| shipnotice_item_id | int | associated Ship Notice Item |
| meta_keys | ||
| id | int | key ID |
| customer_id | int | customer who owns this key (admins only) |
| name | varchar | key name |
| description | varchar | key description |
| value_type | varchar | value type |
| owner_type | varchar | resource this key will be attached to |
| created_at | timestamp | key creation date and time |
| meta_values | ||
| id | int | Rydership ID of the meta field |
| customer_id | int | customer ID associated to the meta field |
| key | varchar | the key of the meta field, example ~ Color |
| value | varchar | value of the meta field, example ~ "Blue" |
| value_type | varchar | type of value: boolean, string, integer, date, datetime, float |
| owner_type | varchar | resource that "owns" the meta field: order,orderbatch, orderitem, item, shipnotice, shipnoticeitem, shop, customer, carrieraccount |
| owner_id | int | |
| created_at | timestamp | timestamp of the creation of the meta field |
| order_delivered_transactions | ||
| id | int | |
| order_id | int | |
| status | varchar | |
| created_at | timestamp | |
| customer_id | int | |
| order_documents | ||
| id | int | the order document ID |
| order_id | int | the order document order ID |
| customer_id | int | the order document customer ID |
| created_at | timestamp | the order document creation date and time |
| updated_at | timestamp | the order document last update date and time |
| order_items | ||
| id | int | the order item ID |
| order_id | int | the order item order ID |
| customer_id | int | the order item customer ID |
| item_id | int | the order item item ID |
| package_id | int | the order item package ID |
| quote_item_id | int | the order item quote item ID |
| sku | varchar | the SKU of this item |
| description | varchar | description for this order item. This will appear in packing slips. If not set the description will be inherited from the parent Item. |
| quantity | int | number of this item in Order |
| price | decimal(8,2) | price of this item |
| created_at | timestamp | the order item creation date and time |
| updated_at | timestamp | the order item last update date and time |
| unshippable | boolean | is this item unshippable? |
| available | boolean | is this item available? |
| packed | int | number of items packed |
| packaging | boolean | is this item packaging? |
| wholesale_cost | decimal(10,2) | wholesale cost of the item |
| is_bundle | boolean | is this item a bundle? |
| retail_fee | decimal(10,2) | retail fee of this item |
| promo | boolean | is this item a promo? |
| returnable | boolean | is this item returnable? |
| currency | varchar | currency code for this item |
| wholesale_fee | decimal(8,2) | wholesale fee of this item |
| hazmat | boolean | is the order item hazmat? |
| misc | varchar | miscellaneous information about the order item |
| orders | ||
| id | int | the order ID |
| customer_id | int | the order customer ID |
| order_batch_id | int | the order batch ID |
| quote_id | int | the order quote ID |
| warehouse_id | int | the order warehouse ID |
| shipping_method_id | int | the order shipping method ID |
| status | int | the order status |
| previous_status | int | the previous order status |
| order_orig | varchar | the order original ID (from the shop that sent RyderShip the order) |
| level1_token | varchar | the order level 1 token |
| level2_token | varchar | the order level 2 token |
| workable_at | timestamp | the date and time the order is workable |
| skip_street_date | boolean | skip street date for this order? |
| due_at | timestamp | the order due at date and time |
| created_at | timestamp | the order creation date and time |
| updated_at | timestamp | the order last update date and time |
| meta_data | varchar | meta data for this order as stringified JSON |
| shop_warehouse_id | int | |
| shop_warehouse_ids | varchar | the order shop warehouse IDs |
| shop_shipping_method_currency | varchar | the order shop shipping method currency |
| shop_created_at | timestamp | the order shop creation date and time |
| shop_updated_at | timestamp | the order shop last update date and time |
| shop_shipping_method_text | varchar | the order shop shipping method text |
| shop_shipping_method_price | decimal(10,2) | the order shop shipping method price |
| billing_company | varchar | the order billing company |
| billing_address_1 | varchar | the order billing street address 1 |
| billing_address_2 | varchar | the order billing street address 2 |
| billing_address_3 | varchar | the order billing street address 3 |
| billing_city | varchar | the order billing city |
| billing_state | varchar | the order billing state |
| billing_zip | varchar | the order billing zip |
| billing_country | varchar | the order billing country |
| billing_phone | varchar | the order billing phone number |
| billed | boolean | has the order been billed? |
| billing_name | varchar | the order billing name |
| first_name | varchar | the first name of the person the order is being shipped to |
| last_name | varchar | the last name of the person the order is being shipped to |
| full_name | varchar | the full name of the person the order is being shipped to |
| shipping_name | varchar | the order shipping name |
| shipping_company | varchar | the order shipping company |
| shipping_address_1 | varchar | the order shipping street address 1 |
| shipping_address_2 | varchar | the order shipping street address 2 |
| shipping_city | varchar | the order shipping city |
| shipping_state | varchar | the order shipping state |
| shipping_zip | varchar | the order shipping zip |
| shipping_country | varchar | the order shipping country |
| shipping_country_iso2 | varchar | the order shipping country iso2 (US, GB, CA, etc) |
| shipping_phone | varchar | the order shipping phone number |
| varchar | the order shipping email | |
| requested_address | varchar | the order requested address |
| residential | boolean | is the order address residential? |
| address_verified | boolean | is the order shipping address verified? |
| address_message | varchar | the order address message |
| require_signature | boolean | does the order require a signature upon delivery? |
| require_adult_signature | boolean | does the order require an adult signature upon delivery? |
| saturday_delivery | boolean | require a carrier that delivers on Saturdays for this order? |
| gift | boolean | is the order a gift? |
| insure | boolean | is the order insured? |
| contains_alcohol | boolean | does the order contain alcohol? |
| customer_provided_label_carrier | varchar | the carrier on the customer-provided label (for tracking purposes) |
| ship_actual_cost | decimal(8,2) | the order actual cost of shipping |
| shipped_on | timestamp | the order shipping date and time |
| ship_notes | varchar | the order ship notes |
| shipping_confirmation_sent | boolean | has the order shipping confirmation been sent? |
| ship_3rdparty_cost | decimal(10,2) | the order third party shipping cost |
| ship_3rdparty_account | varchar | the order third party shipping account |
| ship_3rdparty_zip | varchar | the order third party shipping zip |
| ship_3rdparty_country | varchar | the order third party shipping country |
| incoterm | varchar | desired incoterm for the order |
| public_note | varchar | the order public note |
| days_in_transit | int | the order estimated days in transit (used for release planning) |
| days_in_transit_carrier_estimate | int | the order estimated days in transit estimate from the carrier |
| req_insurance_value | decimal(8,2) | the order requested insurance value |
| return_name | varchar | the order return name |
| return_company | varchar | the order return company |
| return_address_1 | varchar | the order return street address 1 |
| return_address_2 | varchar | the order return street address 2 |
| return_city | varchar | the order return city |
| return_state | varchar | the order return state |
| return_country | varchar | the order return country |
| return_zip | varchar | the order return zip |
| return_phone | varchar | the order return phone number |
| return_email | varchar | the order return email |
| return_address_verified | boolean | is the order return address verified? |
| return_time_limit | int | the order return time limit |
| originators | ||
| id | int | the originator ID |
| customer_id | int | Rydership customer ID |
| originated_id | int | the ID of the object created by the originator |
| originated_type | varchar | the type of object created by the originator |
| shop_id | int | the originator shop ID |
| provider | varchar | the originator provider (shopify, magento, bandcamp, etc) |
| original_id | varchar | the originator original ID (from the provider) |
| group_id | varchar | the originator group ID |
| misc | varchar | miscellaneous info for the originator |
| active | boolean | is the originator active? |
| integration_id | int | the originator integration ID |
| last_notified_at | timestamp | the originator last notified date and time |
| last_notification_status | varchar | the originator last notification status |
| distinct_originator_key | varchar | the originator distinct key |
| created_at | timestamp | the originator creation date and time |
| updated_at | timestamp | the originator last update date and time |
| application_id | int | the ID of the oauth application |
| packages | ||
| id | int | the package ID |
| order_id | int | the package order ID |
| shipped_on | timestamp | the package shipped on date and time |
| ship_actual_cost | decimal(6,2) | the package shipping cost |
| actual_weight | decimal(10,2) | the package weight (in ounces) |
| actual_width | decimal(6,2) | the package width (in inches) |
| actual_height | decimal(6,2) | the package height (in inches) |
| actual_length | decimal(6,2) | the package length (in inches) |
| actual_dimensional_weight | decimal(15,2) | the package dimensional weight (in ounces) |
| tracking | varchar | the package tracking number |
| bill_of_lading | varchar | |
| created_at | timestamp | the package creation date and time |
| updated_at | timestamp | the package last update date and time |
| ucc128 | varchar | the UCC128 carton label for the package |
| manifest_id | int | the package manifest ID |
| reason_codes | ||
| id | int | |
| tag_name | varchar | |
| owner_type | varchar | |
| context | varchar | |
| description | varchar | |
| scancodes | ||
| id | int | |
| item_id | int | the item ID that the scancode is associated to |
| "primary" | boolean | true/false whether or not the scancode is the primary scancode |
| customer_id | int | ID of the customer associated to that scancode |
| name | varchar | |
| active | boolean | |
| created_at | timestamp | timestamp the scancode was created |
| updated_at | timestamp | last time the scancode was updated/edited |
| label_format | varchar | |
| serial_numbers | ||
| id | int | the serial number ID |
| order_item_id | int | the order_item_id for the serial number |
| shipnotice_item_id | int | the shipnotice_item_id for the serial number (returns only) |
| text | varchar | the serial number itself |
| deleted_at | timestamp | if not null, the DateTime when the serial was deleted |
| shipnotice_items | ||
| id | int | the shipnotice item ID |
| shipnotice_id | int | the ID of the shipnotice that contains this shipnotice_item |
| item_id | int | the shipnotice item item ID |
| customer_id | int | Rydership customer ID |
| quantity | int | the number of this shipnotice item in the shipnotice |
| quantity_good | int | how many of this item arrived in acceptable condition |
| quantity_damaged | int | how many of this item arrived in damaged condition |
| created_at | timestamp | the shipnotice item creation date and time |
| updated_at | timestamp | the shipnotice item last update date and time |
| available | varchar | |
| description | varchar | description of the item |
| extended_description | varchar | an extended description of the item |
| include_in_published | varchar | include this item in published amount? |
| return_action | varchar | action to perform on item return |
| purchase_order_number | varchar | the purchase order number associated with the shipnotice item |
| damaged_reason_code | varchar | |
| damaged_reason_description | varchar | |
| shipnotices | ||
| id | int | the shipnotice ID |
| customer_id | int | the shipnotice customer ID |
| warehouse_id | int | the shipnotice warehouse ID |
| sender | varchar | the shipnotice sender |
| eta | timestamp | the shipnotice estimated arrival date |
| status | int | status of the shipnotice |
| received_by | varchar | the name of the person who received the shipnotice |
| notes_by_whiplash | varchar | notes by Rydership (formerly Whiplash) |
| notes_by_customer | varchar | notes by customer |
| total_boxes | int | total number of boxes in the shipnotice |
| created_at | timestamp | the shipnotice creation date and time |
| updated_at | timestamp | the shipnotice last update date and time |
| processing_started_at | timestamp | time the operator allocated the first item on the shipnotice |
| skip_images | boolean | allow skipping of images for this shipnotice? |
| easypostable_id | varchar | the ID of the easypost shipment or tracker |
| easypostable_type | varchar | the type of the easypost object (shipment or tracker) |
| shipping_label_url | varchar | url of the shipping label |
| ship_actual_cost | decimal(7,2) | the shipnotice actual cost of shipping |
| shipping_method_text | varchar | the method of shipping for the shipnotice |
| tracking | varchar | the tracking number of the shipnotice |
| order_id | int | the order_ID of the shipnotice |
| type | varchar | shipnotice type (Shipnotice, ConsumerReturn) |
| postage_billed | boolean | was postage billed? |
| handling_billed | boolean | was handling billed? |
| ship_3rdparty_cost | decimal(7,2) | 3rd party cost for the shipnotice |
| requires_label | boolean | does this shipnotice require a label? |
| completed_at | timestamp | the shipnotice completion date and time |
| ship_actual_currency | varchar | currency used for this shipnotice |
| delivered_at | timestamp | date when was the shipnoticed delivered |
| due_at | timestamp | date when the shipnotice is due to be delivered |
| meta_data | varchar | meta data for this shipnotice as stringified JSON |
| reference | varchar | purchase order number or other human readable token to another system |
| shipping_methods | ||
| id | int | the shipping method ID |
| carrier | varchar | the shipping method carrier (FedEx, UPS, etc) |
| service | varchar | the shipping method service (NextDayAir, Ground, etc) |
| description | varchar | the shipping method service description (Next Day Air, Ground, etc) |
| international | boolean | is the shipping method international? |
| active | boolean | is the shipping method active? |
| flat_rate | boolean | is the shipping method flat rate? |
| expedited | boolean | is the shipping method expedited? |
| trackable | boolean | is the shipping method trackable? |
| origins | varchar | the shipping method origin countries ([CA, US, GB]) |
| extended_description | varchar | the shipping method extended description |
| created_at | timestamp | the shipping method creation date and time |
| updated_at | timestamp | the shipping method last update date and time |
| shops | ||
| id | int | |
| provider | varchar | |
| domain | varchar | |
| customer_id | int | |
| warehouses | ||
| id | int | the warehouse ID |
| name | varchar | the warehouse name |