BigQuery hook tables

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 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	`<eyk-project>.<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.

Confirm the tables

Open the _import dataset in the BigQuery console. All 6 tables are there, with the columns listed under Columns. Run a SELECT on one of them to confirm you have access.

Shape your product variants

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.

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`

Insert 10 rows and inspect them

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.

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.

Shape customers, orders and refunds

Repeat the last two steps for the other tables. Orders carry their lines in items, an ARRAY<STRUCT>. 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.

Schedule the inserts

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.

WHERE modified >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)

Add rate cards

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.

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);

Backfill history

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<STRUCT>	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<STRING>	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<STRING>	Main category as segments from root to leaf, for example `['Home', 'Vases']`. Applies to the whole product.
`additional_category_paths`	ARRAY<STRUCT<path ARRAY<STRING>>>	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<STRUCT>	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.

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;

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);

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);

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.

-- 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

Access denied on INSERT

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.

Null value in non-nullable column

A required column received NULL. Common causes: a customer without email, a refund without order id, an order line without id or quantity.

Array cannot have a null element

One of the array columns holds a NULL. Filter with WHERE s IS NOT NULL AND TRIM(s) != '' inside the ARRAY(...) subquery.

Column not found

Your INSERT names a column that does not exist in the table. Compare it with Columns.

The items STRUCT does not match

Field order and types inside STRUCT(...) must match the table exactly, in the order listed under Columns. Name every field with AS.

I inserted the same rows twice

Nothing to fix. Eyk keeps one version per id, the one with the latest updated_at.

Server-side tracking

Destinations

Agentic

How-to guides

BigQuery hook tables

Before you start

Tables

Columns

eyk_ingest_orders

eyk_ingest_customers

eyk_ingest_product_variants

eyk_ingest_refunds

eyk_ingest_shipping_methods and eyk_ingest_payment_methods

Full insert examples

Check it worked

Troubleshooting

​Before you start

​Tables

​Columns

​eyk_ingest_orders

​eyk_ingest_customers

​eyk_ingest_product_variants

​eyk_ingest_refunds

​eyk_ingest_shipping_methods and eyk_ingest_payment_methods

​Full insert examples

​Check it worked

​Troubleshooting

Before you start

Tables

Columns

eyk_ingest_orders

eyk_ingest_customers

eyk_ingest_product_variants

eyk_ingest_refunds

eyk_ingest_shipping_methods and eyk_ingest_payment_methods

Full insert examples

Check it worked

Troubleshooting