Seedtrakr Analytics

BigQuery Tables (Schema)

BiqQuery Tables (Schema)

(NOTE: The Analytics features requires an Enterprise subscription)

The following entities in Seedtrakr are replicated into BigQuery daily (overnight), listed here with the corresponding table names in BigQuery:

Products (product)
Inventory Lots (inventory_lot)
Inventory Amounts (inventory_amount)
Customers (customer_account)
CRM (contact_engagement)
Bookings (booking, booking _product_line, booking_retail_line)
Pickups (pickup, pickup_product_line, pickup_service_line, pickup_retail_line)
Invoices (invoice, invoice_product_line, invoice_service_line, invoice_retail_line)
Scale tickets (scale_ticket)
Shipment Orders (shipment_order)
Totes (tote)

This page contains details on the fields in each table with notes.

Booking table (booking)

This table contains a row for every booking (cancelled bookings are excluded).

Query notes:

The customer_id field can be used to join to the customer_account table.

Booking product line table (booking_product_line)

This table contains a row for every booking product line item

Query notes:

The booking_id can be used to join to the booking table.
The product_id can be used to join to the product table.
The lot_id can be used to join to the lot table.

Booking retail line table (booking_retail_line)

This table contains a row for every booking retail line item

Query notes:

The booking_id can be used to join to the booking table.

CRM table (contact_engagement)

This table contains a row for every CRM contact engagement

Query notes:

The customer_id can be used to join to the customer_account table.

Customer table (customer_account)

This table contains a row for every customer

Query notes:

Other tables (i.e. bookings, pickups) can join to this table using customer_id

Inventory lot table (inventory_lot)

This table contains a row for every inventory lot

Query notes:

product_id be used to join to the product table.
parent_lot_id can join to a parent lot if the record is a demoted lot.
third_party_customer_id, contract_customer_id can be used to join to the customer_account table if the lot is third party or contract

Invoice table (invoice)

This table contains a row for every invoice. Invoice line items can be joined to this table.

Query notes:

The booking_id can be used to join to the booking table.
The customer_id can be used to join to the customer table.

Invoice products table (invoice_product_line)

This table contains a row for every invoice product line item

Query notes:

The invoice_id can be used to join to the invoice table.
The lot_id can be used to join to the inventory_lot table.

Invoice retail products table (invoice_retail_line)

This table contains a row for every invoice retail line item

Query notes:

The invoice_id can be used to join to the invoice table.

Invoice services table (invoice_services_line)

This table contains a row for every invoice service line item

Query notes:

The invoice_id can be used to join to the invoice table.

Pickup table (pickup)

This table contains a row for every pickup

Query notes:

booking_id can be used to join to the booking table.

Pickup products table (pickup_product_line)

This table contains a row for every pickup product line item

Query notes:

pickup_id can be used to join to the pickup table.
lot_id can be used to join to the inventory_lot table.
product_id can be used to join to the product table.

Pickup retail products table (pickup_retail_line)

This table contains a row for every pickup retail line item

Query notes:

The pickup_id can be used to join to the pickup table.

Pickup services table (pickup_service_line)

This table contains a row for every pickup service line item

Query notes:

The pickup_id can be used to join to the pickup table.

Products table (product)

This table contains a row for every pickup service line item

Query notes:

Other tables (booking_product_line) can join to this table using product_id.

Scale ticket (scale_ticket)

This table contains a row for every pickup service line item

Query notes:

customer_name can be used to join to the customer_account table.
pickup_id can be used to join to the pickup_table
product_sequence_number can be used, along with pickup_id, to join to the pickup_product_line table
inventory_lot_id can be used to join the inventory_lot table
shipment_order_id can be used to join to the sihpment_order table

Scale ticket (scale_ticket)

This table contains a row for every pickup service line item

Query notes:

customer_name can be used to join to the customer_account table.
pickup_id can be used to join to the pickup_table
product_sequence_number can be used, along with pickup_id, to join to the pickup_product_line table
inventory_lot_id can be used to join the inventory_lot table
shipment_order_id can be used to join to the sihpment_order table

Seed crop records table (seed_crop_record)

This table contains a row for every seed_crop_record

Query notes:

lot_id can be used to join to the inventory_lot table.

Shipment orders table (shipment_order)

This table contains a row for every shipment order

Query notes:

contract_customer_id can be used to join to the customer_account table via customer_id

Totes table (tote)

This table contains a row for every tote

Query notes:

lot_id can be used to join to the inventory_lot table
pickup_id can be used to join to the pickup table
shipment_order_id can be used to join to the shipment_order table
is_shipped will be true for any totes that have been shipped via pickup or shipment order

Page updated

Report abuse