> ## Documentation Index > Fetch the complete documentation index at: https://docs.eykdata.com/llms.txt > Use this file to discover all available pages before exploring further. # BigQuery hook tables > Load orders, customers, product variants and refunds into the hook tables of your Eyk BigQuery project with a scheduled query. Load your orders, customers, product variants and refunds into the hook tables with a scheduled query that runs every night. A hook table is an append-only table in your Eyk BigQuery project that Eyk reads in its night run. You write rows into it with a normal `INSERT`. ## Before you start You need: * The [Custom data](/guides/custom-data) page, read once. * A BigQuery project that holds your source data and can run scheduled queries. * The Custom BigQuery source added in Eyk under **Sources**, and write access to the hook tables. Ask your Eyk contact person if you need help with this step. Setup has two parts. First you confirm the tables and shape one entity, then you schedule the inserts. ## Tables | Setting | Value | | --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Dataset | `._import`. Your Eyk contact person gives you the project and dataset name. | | Tables | `eyk_ingest_orders`, `eyk_ingest_customers`, `eyk_ingest_product_variants`, `eyk_ingest_refunds`, `eyk_ingest_shipping_methods`, `eyk_ingest_payment_methods` | | Location | The location of your Eyk project, `EU` unless agreed otherwise. A query in another location cannot insert into it. | | Schedule | Once a day at 00:00 UTC. Eyk processes the tables every night. | | Organization id | Not needed. The dataset is yours. | | Duplicates | Allowed. Per `id` the row with the latest `updated_at` wins. | Open the `_import` dataset in the BigQuery console. All 6 tables are there, with the columns listed under [Columns](#columns). Run a `SELECT` on one of them to confirm you have access. Write a `SELECT` over your catalog that returns one row per variant, with every column cast to the table type. `id` must be the same value your order lines carry in `variant_id`. Categories are arrays of segments, never a joined string. ```sql theme={null} SELECT CAST(variant_id AS STRING) AS id, CAST(sku AS STRING) AS sku, CAST(variant_name AS STRING) AS name, CAST(product_id AS STRING) AS parent_id, CAST(product_name AS STRING) AS parent_name, CAST(purchase_price_ex_vat AS NUMERIC) AS fallback_unit_cost, ARRAY(SELECT s FROM UNNEST(SPLIT(category_breadcrumb, ' > ')) AS s WHERE TRIM(s) != '') AS category_path, CAST(created AS TIMESTAMP) AS created_at, CAST(modified AS TIMESTAMP) AS updated_at FROM `your-project.catalog.variants` ``` Wrap the `SELECT` in an `INSERT` with `LIMIT 10`, run it, and read the rows back from the hook table. Check that names are readable, ids match your order lines, and arrays hold no NULL element. ```sql theme={null} INSERT INTO `eyk-project.yourstore_import.eyk_ingest_product_variants` (id, sku, name, parent_id, parent_name, fallback_unit_cost, category_path, created_at, updated_at) SELECT ... LIMIT 10; ``` A NULL element inside `category_path`, `additional_category_paths`, `discount_codes` or `items` fails the nightly load for the whole table. Filter empty segments out before you insert. Repeat the last two steps for the other tables. Orders carry their lines in `items`, an `ARRAY`. Build it with `ARRAY_AGG(STRUCT(...))` over your order lines, grouped by order. Every amount excludes tax. Amounts that include tax inflate gross sales in every report by the tax rate. Divide by 1 plus the rate before you insert. In the BigQuery console, save each `INSERT` as a scheduled query that runs daily at 00:00 UTC over the rows changed in the last 2 days. Remove the `LIMIT`. The 2-day window catches late changes. The same row twice is harmless. ```sql theme={null} WHERE modified >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY) ``` Optional. Insert one row per shipping and payment method with what the method costs you. Profit then includes shipping and payment cost for orders that carry no cost of their own. ```sql theme={null} INSERT INTO `eyk-project.yourstore_import.eyk_ingest_shipping_methods` (method_id, method_name, flat_cost, percentage_cost, valid_from) VALUES ('postnl-standard', 'PostNL standard', 4.35, NULL, NULL); ``` Run each `INSERT` once without the date filter, product variants and customers first, then orders, then refunds. Oldest orders first when the history is large. ## Columns Every amount excludes tax. Every timestamp is UTC. Columns marked required are `NOT NULL` in the table. ### eyk\_ingest\_orders | Column | Type | Meaning | | ----------------------- | -------------- | ----------------------------------------------------------------------------------------------------------- | | `id` | STRING | Required. Your order id, stable and never reused. | | `payment_status` | STRING | Required. Payment state as your platform names it, for example `paid`. Stored as sent, not filtered. | | `store_view_id` | STRING | Required. A stable code for the shop, country or store view. Drives the store filter. | | `created_at` | TIMESTAMP | Required. When the order was placed. | | `updated_at` | TIMESTAMP | Required. When the order last changed. The latest version per `id` wins. | | `currency` | STRING | Required. ISO 4217 code, for example `EUR`. | | `subtotal` | NUMERIC | Required. Goods before discounts, shipping and tax. Gross sales. | | `customer_id` | STRING | Required. The `id` of the customer. | | `items` | ARRAY\ | Required. The order lines, see below. | | `order_number` | STRING | The number your customer sees, for example `#1043`. Also links to tracking. | | `fulfillment_status` | STRING | Fulfillment state as your platform names it. | | `store_view_name` | STRING | Label for `store_view_id`. Falls back to the id. | | `sales_channel` | STRING | Where the order was placed, for example `web` or `pos`. Falls back to `Default`. | | `shipping` | NUMERIC | Shipping charged to the customer. | | `shipping_tax` | NUMERIC | The part of `taxes` that is tax on shipping. | | `shipping_discount` | NUMERIC | The part of `discounts` that applies to shipping. | | `taxes` | NUMERIC | Total tax on the order, including tax on shipping. | | `discounts` | NUMERIC | Total discount on the order, including line discounts. Positive number. | | `discount_codes` | ARRAY\ | Coupon codes used. | | `customer_email` | STRING | Email of the customer at order time. Used to identify the customer before the email on the customer record. | | `billing_country_code` | STRING | ISO 3166-1 alpha-2, for example `NL`. | | `shipping_country_code` | STRING | Same format as `billing_country_code`. | | `shipping_cost` | NUMERIC | What shipping cost you, for example the carrier invoice. | | `payment_cost` | NUMERIC | What the payment provider charged you. | | `shipping_method_id` | STRING | Joins `method_id` in `eyk_ingest_shipping_methods`. | | `payment_method_id` | STRING | Joins `method_id` in `eyk_ingest_payment_methods`. | | `total` | NUMERIC | Deprecated. Leave NULL. | Each element of `items` is a `STRUCT` with these fields, in this order: | Field | Type | Meaning | | ------------ | ------- | -------------------------------------------------------------------------------------------- | | `id` | STRING | Required. Your line id, unique within the order. | | `quantity` | INT64 | Required. Units ordered, more than 0. | | `unit_price` | NUMERIC | Required. Price per unit before discounts. | | `unit_cost` | NUMERIC | Cost of goods per unit. Falls back to `fallback_unit_cost` on the variant. | | `discounts` | NUMERIC | Discount on this line, all units together. Positive number. | | `taxes` | NUMERIC | Tax on this line, all units together. | | `variant_id` | STRING | The `id` of the product variant. Without a match the line has no product in product reports. | | `sku` | STRING | Your SKU. | | `product_id` | STRING | Ignored. Eyk takes the parent product from `parent_id` on the variant. | ### eyk\_ingest\_customers | Column | Type | Meaning | | ------------ | --------- | ----------------------------------------------------------------- | | `id` | STRING | Required. Your customer id, matches `customer_id` on orders. | | `email` | STRING | Required. Drives new versus returning customers and RFM segments. | | `created_at` | TIMESTAMP | Required. When the account was created. | | `updated_at` | TIMESTAMP | Required. When the record last changed. | | `first_name` | STRING | First name. | | `last_name` | STRING | Last name. | ### eyk\_ingest\_product\_variants One row per sellable variant. A product without variants is one row with no `parent_id`. | Column | Type | Meaning | | ---------------------------------- | ------------------------------------ | ----------------------------------------------------------------------------------------------------------- | | `id` | STRING | Required. Your variant id, matches `variant_id` on order lines. | | `name` | STRING | Required. Variant name, for example `Blue`. | | `created_at` | TIMESTAMP | Required. When the variant was created. | | `updated_at` | TIMESTAMP | Required. When the record last changed. | | `parent_id` | STRING | Your id of the parent product. Groups variants into one product. | | `parent_name` | STRING | Name of the parent product. | | `sku` | STRING | Your SKU. | | `image_url` | STRING | Image of the variant. | | `parent_image_url` | STRING | Image of the parent product. | | `fallback_unit_cost` | NUMERIC | Cost of goods per unit when the order line carries no `unit_cost`. | | `brand_id`, `brand_name` | STRING | Brand. | | `supplier_id`, `supplier_name` | STRING | Supplier. | | `category_path` | ARRAY\ | Main category as segments from root to leaf, for example `['Home', 'Vases']`. Applies to the whole product. | | `additional_category_paths` | ARRAY\>> | Other categories, same format: `[STRUCT(['Sale'] AS path)]`. | | `store_view_id`, `store_view_name` | STRING | Deprecated. Leave NULL. | ### eyk\_ingest\_refunds | Column | Type | Meaning | | ----------------- | -------------- | -------------------------------------------------------------------------- | | `id` | STRING | Required. Your refund id. | | `order_id` | STRING | Required. The `id` of the refunded order. | | `store_view_id` | STRING | Required. Same code as on the order. | | `created_at` | TIMESTAMP | Required. When the refund was issued. | | `updated_at` | TIMESTAMP | Required. When the record last changed. | | `amount` | NUMERIC | Required. Goods refunded, excluding shipping. | | `shipping` | NUMERIC | Shipping refunded. Not part of `amount`. | | `taxes` | NUMERIC | Tax refunded, including tax on refunded shipping. | | `discounts` | NUMERIC | Discount reversed by the refund. | | `reason` | STRING | Reason text. | | `store_view_name` | STRING | Label for the store. | | `items` | ARRAY\ | Refund lines, see below. Leave empty for a refund on the order as a whole. | Each element of `items` is a `STRUCT` with these fields, in this order: | Field | Type | Meaning | | ------------ | ------- | ---------------------------------------------------- | | `item_id` | STRING | Required. The `id` of the order line being refunded. | | `variant_id` | STRING | The variant refunded. | | `quantity` | INT64 | Required. Units refunded, more than 0. | | `amount` | NUMERIC | Required. Goods refunded for this line. | | `taxes` | NUMERIC | Tax refunded for this line. | | `discounts` | NUMERIC | Discount reversed for this line. | ### eyk\_ingest\_shipping\_methods and eyk\_ingest\_payment\_methods A rate card: what a shipping or payment method costs you, per method, from a date onward. Eyk applies it to orders that name the method and carry no `shipping_cost` or `payment_cost` of their own. | Column | Type | Meaning | | ----------------- | --------- | ---------------------------------------------------------------------------------------------------------------------------------- | | `method_id` | STRING | Required. Matches `shipping_method_id` or `payment_method_id` on orders. | | `method_name` | STRING | Label. | | `flat_cost` | NUMERIC | Cost per order, spread over the order's item lines. | | `percentage_cost` | NUMERIC | Cost as a percentage of the item gross sales, for example `1.5`. Added to `flat_cost` when both are set. | | `valid_from` | TIMESTAMP | The rate applies from this moment until the next row for the same `method_id`. NULL covers all history before the first dated row. | ## Full insert examples Complete statements for the four main tables. Replace the project and dataset and the source tables. ```sql Orders theme={null} INSERT INTO `eyk-project.yourstore_import.eyk_ingest_orders` (id, order_number, payment_status, store_view_id, store_view_name, sales_channel, created_at, updated_at, currency, subtotal, discounts, discount_codes, shipping, shipping_tax, taxes, customer_id, customer_email, billing_country_code, shipping_country_code, shipping_cost, payment_cost, shipping_method_id, payment_method_id, items) SELECT CAST(o.order_id AS STRING), CAST(o.order_number AS STRING), CAST(o.payment_status AS STRING), CAST(o.shop_code AS STRING), CAST(o.shop_name AS STRING), 'web', CAST(o.ordered_at AS TIMESTAMP), CAST(o.modified_at AS TIMESTAMP), 'EUR', CAST(o.goods_ex_vat AS NUMERIC), CAST(o.discount_ex_vat AS NUMERIC), ANY_VALUE(o.coupon_codes), CAST(o.shipping_ex_vat AS NUMERIC), CAST(o.shipping_vat AS NUMERIC), CAST(o.vat_total AS NUMERIC), CAST(o.customer_id AS STRING), o.customer_email, o.billing_country, o.shipping_country, CAST(o.carrier_cost AS NUMERIC), CAST(o.psp_fee AS NUMERIC), o.shipping_method, o.payment_method, ARRAY_AGG(STRUCT( CAST(l.line_id AS STRING) AS id, CAST(l.quantity AS INT64) AS quantity, CAST(l.unit_price_ex_vat AS NUMERIC) AS unit_price, CAST(l.unit_cost AS NUMERIC) AS unit_cost, CAST(l.discount_ex_vat AS NUMERIC) AS discounts, CAST(l.vat AS NUMERIC) AS taxes, CAST(l.variant_id AS STRING) AS variant_id, CAST(l.sku AS STRING) AS sku, CAST(NULL AS STRING) AS product_id )) FROM `your-project.sales.orders` o JOIN `your-project.sales.order_lines` l ON l.order_id = o.order_id WHERE o.modified_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY) GROUP BY o.order_id, o.order_number, o.payment_status, o.shop_code, o.shop_name, o.ordered_at, o.modified_at, o.goods_ex_vat, o.discount_ex_vat, o.shipping_ex_vat, o.shipping_vat, o.vat_total, o.customer_id, o.customer_email, o.billing_country, o.shipping_country, o.carrier_cost, o.psp_fee, o.shipping_method, o.payment_method; ``` ```sql Customers theme={null} INSERT INTO `eyk-project.yourstore_import.eyk_ingest_customers` (id, email, first_name, last_name, created_at, updated_at) SELECT CAST(customer_id AS STRING), email, first_name, last_name, CAST(created_at AS TIMESTAMP), CAST(modified_at AS TIMESTAMP) FROM `your-project.crm.customers` WHERE email IS NOT NULL AND modified_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY); ``` ```sql Product variants theme={null} INSERT INTO `eyk-project.yourstore_import.eyk_ingest_product_variants` (id, sku, name, image_url, parent_id, parent_name, parent_image_url, fallback_unit_cost, brand_id, brand_name, category_path, additional_category_paths, created_at, updated_at) SELECT CAST(variant_id AS STRING), CAST(sku AS STRING), variant_name, variant_image_url, CAST(product_id AS STRING), product_name, product_image_url, CAST(purchase_price_ex_vat AS NUMERIC), CAST(brand_id AS STRING), brand_name, ARRAY(SELECT s FROM UNNEST(SPLIT(category_breadcrumb, ' > ')) AS s WHERE TRIM(s) != ''), ARRAY(SELECT STRUCT(SPLIT(p, ' > ') AS path) FROM UNNEST(extra_category_breadcrumbs) AS p WHERE TRIM(p) != ''), CAST(created AS TIMESTAMP), CAST(modified AS TIMESTAMP) FROM `your-project.catalog.variants` WHERE modified >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY); ``` ```sql Refunds theme={null} INSERT INTO `eyk-project.yourstore_import.eyk_ingest_refunds` (id, order_id, store_view_id, created_at, updated_at, amount, shipping, taxes, discounts, reason, items) SELECT CAST(r.refund_id AS STRING), CAST(r.order_id AS STRING), CAST(r.shop_code AS STRING), CAST(r.refunded_at AS TIMESTAMP), CAST(r.modified_at AS TIMESTAMP), CAST(r.goods_ex_vat AS NUMERIC), CAST(r.shipping_ex_vat AS NUMERIC), CAST(r.vat_total AS NUMERIC), CAST(r.discount_ex_vat AS NUMERIC), r.reason, ARRAY_AGG(STRUCT( CAST(l.line_id AS STRING) AS item_id, CAST(l.variant_id AS STRING) AS variant_id, CAST(l.quantity AS INT64) AS quantity, CAST(l.amount_ex_vat AS NUMERIC) AS amount, CAST(l.vat AS NUMERIC) AS taxes, CAST(NULL AS NUMERIC) AS discounts )) FROM `your-project.sales.refunds` r JOIN `your-project.sales.refund_lines` l ON l.refund_id = r.refund_id WHERE r.modified_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY) GROUP BY r.refund_id, r.order_id, r.shop_code, r.refunded_at, r.modified_at, r.goods_ex_vat, r.shipping_ex_vat, r.vat_total, r.discount_ex_vat, r.reason; ``` ## Check it worked Run these on your hook tables the morning after the first scheduled run. Then open the Custom BigQuery source in Eyk under **Sources** and read the **Monitor** tab: it lists orders, refunds, product variants and customers received per day. Data appears in reports the morning after Eyk's night run. ```sql theme={null} -- Rows per day SELECT DATE(inserted_at) AS day, COUNT(*) AS rows_inserted FROM `eyk-project.yourstore_import.eyk_ingest_orders` GROUP BY day ORDER BY day DESC LIMIT 7; -- Order lines whose variant is missing from the catalog. Expected: no rows. SELECT i.variant_id, COUNT(*) AS lines FROM `eyk-project.yourstore_import.eyk_ingest_orders` o, UNNEST(o.items) AS i LEFT JOIN `eyk-project.yourstore_import.eyk_ingest_product_variants` v ON v.id = i.variant_id WHERE v.id IS NULL GROUP BY i.variant_id ORDER BY lines DESC LIMIT 20; -- Refunds whose order is missing. Expected: no rows. SELECT r.id, r.order_id FROM `eyk-project.yourstore_import.eyk_ingest_refunds` r LEFT JOIN `eyk-project.yourstore_import.eyk_ingest_orders` o ON o.id = r.order_id WHERE o.id IS NULL LIMIT 20; ``` ## Troubleshooting The account that runs the scheduled query has no write access to the `_import` dataset, or the query runs in another location than the dataset. Ask your Eyk contact person if you need help with this step. A required column received NULL. Common causes: a customer without email, a refund without order id, an order line without id or quantity. One of the array columns holds a NULL. Filter with `WHERE s IS NOT NULL AND TRIM(s) != ''` inside the `ARRAY(...)` subquery. Your INSERT names a column that does not exist in the table. Compare it with [Columns](#columns). Field order and types inside `STRUCT(...)` must match the table exactly, in the order listed under [Columns](#columns). Name every field with `AS`. Nothing to fix. Eyk keeps one version per `id`, the one with the latest `updated_at`.