A WooCommerce store needs seven tables in BigQuery for Claude Desktop Live Artifacts to be useful in 2026: fact_event, fact_purchase, fact_session, dim_traffic_source, dim_campaign, dim_product and dim_customer. Default WooCommerce-to-BigQuery ETL exports cover four of those at most — orders, products, customers and refunds — and ship none of the event-level rows, session continuity or campaign attribution that dashboards actually query. Half the schema, all the daily questions blocked. Server-side event capture writes the missing tables on the way in.
Why the schema question stopped being academic
Before April 20, 2026, your BigQuery schema lived behind a Looker Studio dashboard. Live Artifacts dragged it into the prompt response.
For years, most WooCommerce operators with a BigQuery instance had a vague sense that “orders are in there somewhere” and never had to think harder. The schema was an engineering concern, dealt with by whoever built the dashboard.
That changed on April 20, 2026, when Claude Desktop Live Artifacts shipped — and then again in early May, when Google’s remote BigQuery MCP server hit general availability with OAuth 2.0 authentication and no API keys required. Now Claude Desktop will literally list the tables in your dataset, describe the columns, and tell the operator — in plain English — what queries are possible.
The schema is no longer hidden behind a chart. Claude Desktop tells the operator what’s there and what isn’t — and most stores discover within two minutes of OAuth that the answer is “almost nothing useful.”
The default WooCommerce-to-BigQuery ETL plugins sync the six entity types the WooCommerce REST API exposes: orders, products, customers, coupons, refunds and shipping. Claude Desktop reads that, politely confirms what it sees, and then can’t answer the dashboard questions a 2026 operator actually wants to ask.
The three fact tables that carry the work
Fact tables are the high-volume, event-grained tables Live Artifact queries run over. Three of them carry almost every question a store owner will ask.
fact_event — every behavioural event, microsecond timestamp
The single most important table. One row per user action: page_view, view_item, add_to_cart, begin_checkout, add_payment_info, purchase, refund. Minimum columns:
event_id(string, unique)event_name(e.g.add_to_cart)event_timestamp_micros(int64, microsecond precision)user_id(string, stable across sessions)session_id(string)page_location(string, full URL)parameters(repeated record — key/value pairs for event-specific data)
What fact_event unlocks: “Which products are getting added to cart but not bought today?”, “Top product velocity this hour vs same hour last week”, “Where in the checkout are users dropping off?”
fact_purchase — completed orders with attribution stamped at the row
Conceptually a subset of fact_event, but materialised as its own table because revenue queries are so common. Minimum columns:
order_iduser_id,event_timestamp_microsrevenue_net,revenue_gross,currencypayment_methodproducts(repeated record — line items with sku, qty, price)utm_source,utm_medium,utm_campaign(stamped at capture, not joined later)
What fact_purchase unlocks: “Which campaign drove revenue in the last hour?”, “ROAS per campaign this morning”, “Today’s revenue by traffic source.”
fact_session — the connective tissue across events
One row per session. The table that makes “returning vs new” and “drop-off after step 2” queries possible. Minimum columns:
session_iduser_idsession_start,session_endtraffic_source,medium,campaignutm_source,utm_medium,utm_campaign,utm_content,utm_termdevice_category,country
What fact_session unlocks: “Returning vs new customer split this week”, “Average session length by channel”, “Which channel brings sessions that actually convert?”
You may be interested in: What Does a Good WooCommerce Data Stack Look Like in 2026?
The four dim tables that make queries possible
Dimension tables describe the things events happen to. They’re small, slow-changing and absolutely necessary for joins.
dim_traffic_source
One row per channel: direct, google_organic, google_paid, meta_paid, email, referral, and so on. Columns: source_id, source_name, medium, is_paid, cost_per_event_method. This is the table that makes “by channel” cuts possible in a single line of SQL instead of a string-matching mess.
dim_campaign
One row per campaign, joined to ad-platform spend. Columns: campaign_id, campaign_name, platform (google_ads, meta_ads, etc.), daily_spend, start_date, end_date. Without this table, ROAS queries can’t run — you can only count conversions, never compare to cost.
dim_product
Product catalog as it exists right now. Columns: product_id, sku, name, category, price_current, cost_current, inventory_qty, active. Joins to fact_purchase.products. Without this, “stockout-risk tracker” queries are impossible.
dim_customer
One row per customer, with lifetime metrics pre-aggregated. Columns: user_id, email_hashed, first_seen, last_seen, lifetime_orders, lifetime_revenue, preferred_channel, days_since_last_order. Joins to fact_purchase.user_id and fact_session.user_id.
Three fact tables and four dim tables. Seven objects in BigQuery, queryable in any combination by Claude Desktop. Anything less and the dashboard prompt returns “I can see your data, but I can’t answer that.”
What standard WooCommerce ETL exports actually contain — and don’t
The default ETL plugins sync the WooCommerce REST API. The REST API does not expose half of what Live Artifacts will be asked to query.
| Table | Default WooCommerce ETL export | Server-side event warehouse |
|---|---|---|
| fact_event (all behavioural events) | ❌ Not present | ✅ Yes — every event captured server-side |
| fact_purchase (with utm at row level) | Partial — orders table, no utm columns | ✅ utm_source/medium/campaign stamped at capture |
| fact_session | ❌ Not present | ✅ One row per session, full attribution |
| dim_traffic_source | ❌ Not present | ✅ Maintained as channel list |
| dim_campaign | ❌ Not present | ✅ Joined to ad-platform spend |
| dim_product | ✅ Products table | ✅ Products table |
| dim_customer (with lifetime metrics) | Partial — customers table, no lifetime aggregates | ✅ Lifetime metrics rolled up |
Counting generously, the default ETL covers two of seven tables fully (products, refunds — neither shown above), two partially (orders without attribution, customers without lifetime metrics), and zero of the three fact tables that drive most Live Artifact queries.
That’s not 50% of the schema. It’s closer to 15% — and the missing 85% is the part that makes Claude Desktop dashboards useful.
You may be interested in: Your WooCommerce BigQuery Integration Is Missing 90% of Your Data
How to fill the schema without re-platforming
The missing tables don’t require ripping out WooCommerce or replacing the ETL. They require a parallel stream that writes the event-level rows directly into BigQuery.
The fix isn’t to abandon the ETL plugin or migrate the store. The orders, products and customer ETL feeds are fine where they are. What’s missing is the event-level capture — the stream that writes fact_event, fact_purchase with attribution, and fact_session as they happen.
Transmute Engine™ is a first-party Node.js server that runs on your subdomain (for example, data.yourstore.com). The inPIPE WordPress plugin captures the WooCommerce hooks and sends events via API to your Transmute Engine server, which streams the full enhanced-ecommerce event spectrum directly to BigQuery — every event with its session, every purchase with its attribution, every customer with their lifetime trajectory, all stamped server-side at capture. The dim tables roll up nightly from the fact tables, no manual maintenance required.
The result is that the seven-table schema described above exists end-to-end in BigQuery, and the Claude Desktop prompt that returned “I can’t answer that” against the ETL warehouse now returns a working Live Artifact.
Key Takeaways
Five facts to keep — the seven tables, the ETL gap, and the path to closing it without rebuilding the store.
- Seven tables: The minimum BigQuery schema for WooCommerce Live Artifacts is fact_event, fact_purchase, fact_session, dim_traffic_source, dim_campaign, dim_product and dim_customer.
- Attribution at the row: utm_source, utm_medium and utm_campaign must be stamped on fact_purchase at capture — not joined later.
- Default ETL ships ~15% of this: The standard WooCommerce-to-BigQuery export covers two tables fully and two partially, with zero fact tables.
- Microsecond timestamps: fact_event needs
event_timestamp_microsto support intra-hour velocity queries. - Server-side capture closes the gap: A first-party event server writes the missing five tables directly into BigQuery alongside the ETL data.
Frequently Asked Questions
Quick answers to the five questions store owners ask after seeing what their current BigQuery schema can actually answer.
event_timestamp
Because intra-hour queries — “top product velocity this hour vs same hour last week” — depend on ordering events within the same second precisely. Seconds-precision timestamps collapse the ordering of click → add_to_cart → purchase events that all happen within a one-second window during a checkout flow. GA4’s BigQuery export uses event_timestamp in microseconds for the same reason.
Not really. user_id tells you who, but session_id tells you when “the visit” started and ended — which is the unit most dashboard questions are actually asked at. “Bounce rate”, “session conversion rate”, “session length by channel” all collapse without it. You can derive sessions from events with a sessionisation query at read time, but most stores find it cheaper to materialise the session table at write time.
For pure attribution counts, no — you can group fact_purchase by utm_campaign directly. For ROAS you do need dim_campaign because it carries the daily_spend join from Google Ads and Meta Ads. Without that join, you can count conversions per campaign but never compare them to cost.
Not without a substantial WooCommerce REST API expansion. The REST API exposes six entity types — orders, products, customers, coupons, refunds, shipping — all of which are completed-transaction records. It does not emit page views, add-to-cart events, checkout-step events or session boundaries. ETL plugins can only sync what the API exposes. The missing tables have to come from a separate event capture layer.
Indefinitely if storage cost is reasonable. BigQuery storage is around $0.02 per GB per month for active storage and roughly half that for long-term. A high-traffic WooCommerce store typically writes a few GB of event data per month. Retaining two years lets Live Artifacts answer year-on-year comparison questions; retaining only the GA4 free-tier default of 60 days makes them impossible.
References
Primary sources behind the schema choices above — Google’s official export specs, the GA4 ecommerce event taxonomy and the WooCommerce REST API reference.
- Google. (2025). GA4 BigQuery export schema. developers.google.com
- Google. (2025). GA4 recommended ecommerce events. developers.google.com
- WooCommerce. (2025). REST API reference. woocommerce.github.io
- Google Cloud. (2026). BigQuery MCP server documentation. cloud.google.com
- Google Cloud. (2026). BigQuery storage pricing. cloud.google.com
- Anthropic. (2026). Live Artifacts documentation. support.claude.com
The minimum-viable BigQuery schema for a 2026 WooCommerce store is no longer a debate — it’s a checklist. Transmute Engine writes it server-side, end-to-end, at seresa.io.
