Integration of WooCommerce with BigQuery
Learn how to connect WooCommerce to Google BigQuery using WitCloud to collect orders, products and product categories from your store.
Updated
Introduction
This module connects your WooCommerce store to Google Cloud BigQuery. WitCloud reads your orders, products and product categories through the WooCommerce REST API and stores them in your project's BigQuery dataset, ready for analysis and for the All in One tables.
Configuration guide
Follow the steps below to connect WooCommerce with BigQuery. WitCloud needs the address of your store and a WooCommerce REST API key pair (a consumer key and a consumer secret).
Create the REST API key before you start: you will paste it into WitCloud in the Authorization step. Read-only permission is enough, because WitCloud only reads data from your store.
Create the REST API key in WooCommerce
This step takes place in your WordPress / WooCommerce admin panel, which is not part of WitCloud. The menu names below come from WooCommerce and may differ between its versions; see the WooCommerce documentation if you cannot find them.
- Log in to your WordPress admin panel as an administrator.
- Go to WooCommerce > Settings > Advanced > REST API and click Add key.
- Enter a description, e.g. "WitCloud", choose the user and set Permissions to Read.
- Click Generate API key.
- Copy the Consumer key (it starts with
ck_) and the Consumer secret (it starts withcs_). WooCommerce shows the secret only once.
Keep both values private: anyone who has them can read your store data.
Module setup
Go to Integrations and click Add integration.

Select WooCommerce from the list of available integrations.

Enter the Collect name, e.g. "WooCommerce PL". A clear name helps you recognise the data source later.

Enter the Shop url: the address of your store, including https://, e.g. https://your-shop.com. The Authorize button stays inactive until you enter a valid address.

Authorization
If you have already authorized this WooCommerce store in WitCloud, you can pick the existing token from the WooCommerce authorization drop-down and skip the next two steps.
Click Authorize.

In the Authorize pop-up enter the Client ID (the WooCommerce consumer key) and the Client secret (the WooCommerce consumer secret) and click Create. WitCloud checks the key against your store. If the store cannot be reached, the address or the key is wrong, or the key has no permission to read data, the reason is shown under the form.

The saved token is selected automatically. You can also choose a token from the WooCommerce authorization drop-down.

Advanced settings
Click Show advanced settings to see three more fields.
In Data freshness (products, stocks) enter how often, in hours, tables that hold the whole catalogue (such as products) are refreshed. The value must be between 4 and 12 hours; the default is 4.

Turn on Static IP if your store sits behind extra network protection such as Cloudflare. All data requests are then sent from one fixed IP address, 34.118.32.227, which you can add to the allowed list of your store.

In Tables to collect you can remove the tables you do not need. The field offers orders, products, categories and returns; all of them are selected by default and at least one must stay selected. Collecting fewer tables makes the data retrieval faster.

Click Next.

Historical data and scheduling
In Historical Data enter the number of days to fetch on the first run, up to 365.

A data synchronization schedule is created automatically. More information on this topic can be found here.
Click Create to complete the configuration.

Done! Your WooCommerce data will now be synchronized with BigQuery. Orders are collected day by day; products and categories are collected in full on each run.
Data schema
The tables below show the data collected from WooCommerce: orders, products and product categories.
Data schema for woocommerce_categories
| Field Name | Mode | Type |
|---|---|---|
| count | NULLABLE | INTEGER |
| description | NULLABLE | STRING |
| display | NULLABLE | STRING |
| id | NULLABLE | INTEGER |
| image | NULLABLE | RECORD |
| image.alt | NULLABLE | STRING |
| image.date_created | NULLABLE | TIMESTAMP |
| image.date_created_gmt | NULLABLE | TIMESTAMP |
| image.date_modified | NULLABLE | TIMESTAMP |
| image.date_modified_gmt | NULLABLE | TIMESTAMP |
| image.id | NULLABLE | INTEGER |
| image.name | NULLABLE | STRING |
| image.src | NULLABLE | STRING |
| menu_order | NULLABLE | INTEGER |
| name | NULLABLE | STRING |
| parent | NULLABLE | INTEGER |
| slug | NULLABLE | STRING |
Data schema for woocommerce_orders
| Field Name | Mode | Type |
|---|---|---|
| billing | NULLABLE | RECORD |
| billing.address_1 | NULLABLE | STRING |
| billing.address_2 | NULLABLE | STRING |
| billing.city | NULLABLE | STRING |
| billing.company | NULLABLE | STRING |
| billing.country | NULLABLE | STRING |
| billing.email | NULLABLE | STRING |
| billing.first_name | NULLABLE | STRING |
| billing.last_name | NULLABLE | STRING |
| billing.phone | NULLABLE | STRING |
| billing.postcode | NULLABLE | STRING |
| billing.state | NULLABLE | STRING |
| cart_hash | NULLABLE | STRING |
| cart_tax | NULLABLE | FLOAT |
| coupon_lines | REPEATED | RECORD |
| coupon_lines.code | NULLABLE | STRING |
| coupon_lines.discount | NULLABLE | FLOAT |
| coupon_lines.discount_tax | NULLABLE | FLOAT |
| coupon_lines.id | NULLABLE | INTEGER |
| coupon_lines.meta_data | REPEATED | RECORD |
| coupon_lines.meta_data.display_key | NULLABLE | STRING |
| coupon_lines.meta_data.display_value | NULLABLE | JSON |
| coupon_lines.meta_data.id | NULLABLE | INTEGER |
| coupon_lines.meta_data.key | NULLABLE | STRING |
| coupon_lines.meta_data.value | NULLABLE | JSON |
| created_via | NULLABLE | STRING |
| currency | NULLABLE | STRING |
| currency_symbol | NULLABLE | STRING |
| customer_id | NULLABLE | INTEGER |
| customer_ip_address | NULLABLE | STRING |
| customer_note | NULLABLE | STRING |
| customer_user_agent | NULLABLE | STRING |
| date_completed | NULLABLE | TIMESTAMP |
| date_completed_gmt | NULLABLE | TIMESTAMP |
| date_created | NULLABLE | TIMESTAMP |
| date_created_gmt | NULLABLE | TIMESTAMP |
| date_modified | NULLABLE | TIMESTAMP |
| date_modified_gmt | NULLABLE | TIMESTAMP |
| date_paid | NULLABLE | TIMESTAMP |
| date_paid_gmt | NULLABLE | TIMESTAMP |
| discount_tax | NULLABLE | FLOAT |
| discount_total | NULLABLE | FLOAT |
| id | NULLABLE | INTEGER |
| is_editable | NULLABLE | BOOLEAN |
| line_items | REPEATED | RECORD |
| line_items.id | NULLABLE | INTEGER |
| line_items.image | NULLABLE | RECORD |
| line_items.image.id | NULLABLE | INTEGER |
| line_items.image.src | NULLABLE | STRING |
| line_items.meta_data | REPEATED | RECORD |
| line_items.meta_data.display_key | NULLABLE | STRING |
| line_items.meta_data.display_value | NULLABLE | JSON |
| line_items.meta_data.id | NULLABLE | INTEGER |
| line_items.meta_data.key | NULLABLE | STRING |
| line_items.meta_data.value | NULLABLE | JSON |
| line_items.name | NULLABLE | STRING |
| line_items.price | NULLABLE | FLOAT |
| line_items.product_id | NULLABLE | INTEGER |
| line_items.quantity | NULLABLE | INTEGER |
| line_items.sku | NULLABLE | STRING |
| line_items.subtotal | NULLABLE | FLOAT |
| line_items.subtotal_tax | NULLABLE | FLOAT |
| line_items.tax_class | NULLABLE | STRING |
| line_items.taxes | REPEATED | RECORD |
| line_items.taxes.id | NULLABLE | INTEGER |
| line_items.taxes.subtotal | NULLABLE | FLOAT |
| line_items.taxes.total | NULLABLE | FLOAT |
| line_items.total | NULLABLE | FLOAT |
| line_items.total_tax | NULLABLE | FLOAT |
| line_items.variation_id | NULLABLE | INTEGER |
| meta_data | REPEATED | RECORD |
| meta_data.id | NULLABLE | INTEGER |
| meta_data.key | NULLABLE | STRING |
| meta_data.value | NULLABLE | JSON |
| needs_payment | NULLABLE | BOOLEAN |
| needs_processing | NULLABLE | BOOLEAN |
| number | NULLABLE | INTEGER |
| order_key | NULLABLE | STRING |
| parent_id | NULLABLE | INTEGER |
| payment_method | NULLABLE | STRING |
| payment_method_title | NULLABLE | STRING |
| payment_url | NULLABLE | STRING |
| prices_include_tax | NULLABLE | BOOLEAN |
| shipping | NULLABLE | RECORD |
| shipping.address_1 | NULLABLE | STRING |
| shipping.address_2 | NULLABLE | STRING |
| shipping.city | NULLABLE | STRING |
| shipping.company | NULLABLE | STRING |
| shipping.country | NULLABLE | STRING |
| shipping.first_name | NULLABLE | STRING |
| shipping.last_name | NULLABLE | STRING |
| shipping.phone | NULLABLE | STRING |
| shipping.postcode | NULLABLE | STRING |
| shipping.state | NULLABLE | STRING |
| shipping_lines | REPEATED | RECORD |
| shipping_lines.id | NULLABLE | INTEGER |
| shipping_lines.instance_id | NULLABLE | INTEGER |
| shipping_lines.meta_data | REPEATED | RECORD |
| shipping_lines.meta_data.display_key | NULLABLE | STRING |
| shipping_lines.meta_data.display_value | NULLABLE | JSON |
| shipping_lines.meta_data.id | NULLABLE | INTEGER |
| shipping_lines.meta_data.key | NULLABLE | STRING |
| shipping_lines.meta_data.value | NULLABLE | JSON |
| shipping_lines.method_id | NULLABLE | STRING |
| shipping_lines.method_title | NULLABLE | STRING |
| shipping_lines.total | NULLABLE | FLOAT |
| shipping_lines.total_tax | NULLABLE | FLOAT |
| shipping_tax | NULLABLE | FLOAT |
| shipping_total | NULLABLE | FLOAT |
| status | NULLABLE | STRING |
| summary_page | NULLABLE | STRING |
| tax_lines | REPEATED | RECORD |
| tax_lines.compound | NULLABLE | BOOLEAN |
| tax_lines.id | NULLABLE | INTEGER |
| tax_lines.label | NULLABLE | STRING |
| tax_lines.rate_code | NULLABLE | STRING |
| tax_lines.rate_id | NULLABLE | INTEGER |
| tax_lines.rate_percent | NULLABLE | INTEGER |
| tax_lines.shipping_tax_total | NULLABLE | FLOAT |
| tax_lines.tax_total | NULLABLE | FLOAT |
| refunds | REPEATED | RECORD |
| refunds.id | NULLABLE | INTEGER |
| refunds.reason | NULLABLE | STRING |
| refunds.total | NULLABLE | FLOAT |
| total | NULLABLE | FLOAT |
| total_tax | NULLABLE | FLOAT |
| transaction_id | NULLABLE | STRING |
| version | NULLABLE | STRING |
Data schema for woocommerce_products
| Field Name | Mode | Type |
|---|---|---|
| attributes | REPEATED | RECORD |
| attributes.id | NULLABLE | INTEGER |
| attributes.name | NULLABLE | STRING |
| attributes.options | REPEATED | STRING |
| attributes.position | NULLABLE | INTEGER |
| attributes.variation | NULLABLE | BOOLEAN |
| attributes.visible | NULLABLE | BOOLEAN |
| average_rating | NULLABLE | FLOAT |
| backordered | NULLABLE | BOOLEAN |
| backorders | NULLABLE | STRING |
| backorders_allowed | NULLABLE | BOOLEAN |
| brand_name | NULLABLE | STRING |
| button_text | NULLABLE | STRING |
| catalog_visibility | NULLABLE | STRING |
| categories | REPEATED | RECORD |
| categories.id | NULLABLE | INTEGER |
| categories.name | NULLABLE | STRING |
| categories.slug | NULLABLE | STRING |
| date_created | NULLABLE | TIMESTAMP |
| date_created_gmt | NULLABLE | TIMESTAMP |
| date_modified | NULLABLE | TIMESTAMP |
| date_modified_gmt | NULLABLE | TIMESTAMP |
| description | NULLABLE | STRING |
| dimensions | NULLABLE | RECORD |
| dimensions.height | NULLABLE | STRING |
| dimensions.length | NULLABLE | STRING |
| dimensions.width | NULLABLE | STRING |
| download_expiry | NULLABLE | INTEGER |
| download_limit | NULLABLE | INTEGER |
| downloadable | NULLABLE | BOOLEAN |
| external_url | NULLABLE | STRING |
| featured | NULLABLE | BOOLEAN |
| has_options | NULLABLE | BOOLEAN |
| id | NULLABLE | INTEGER |
| images | REPEATED | RECORD |
| images.alt | NULLABLE | STRING |
| images.date_created | NULLABLE | TIMESTAMP |
| images.date_created_gmt | NULLABLE | TIMESTAMP |
| images.date_modified | NULLABLE | TIMESTAMP |
| images.date_modified_gmt | NULLABLE | TIMESTAMP |
| images.id | NULLABLE | INTEGER |
| images.name | NULLABLE | STRING |
| images.src | NULLABLE | STRING |
| manage_stock | NULLABLE | BOOLEAN |
| menu_order | NULLABLE | INTEGER |
| meta_data | REPEATED | RECORD |
| meta_data.id | NULLABLE | INTEGER |
| meta_data.key | NULLABLE | STRING |
| meta_data.value | NULLABLE | JSON |
| name | NULLABLE | STRING |
| on_sale | NULLABLE | BOOLEAN |
| parent_id | NULLABLE | INTEGER |
| permalink | NULLABLE | STRING |
| price | NULLABLE | FLOAT |
| price_html | NULLABLE | STRING |
| product_brand | REPEATED | RECORD |
| product_brand.count | NULLABLE | INTEGER |
| product_brand.description | NULLABLE | STRING |
| product_brand.filter | NULLABLE | STRING |
| product_brand.name | NULLABLE | STRING |
| product_brand.parent | NULLABLE | INTEGER |
| product_brand.slug | NULLABLE | STRING |
| product_brand.taxonomy | NULLABLE | STRING |
| product_brand.term_group | NULLABLE | INTEGER |
| product_brand.term_id | NULLABLE | INTEGER |
| product_brand.term_taxonomy_id | NULLABLE | INTEGER |
| product_type | REPEATED | RECORD |
| product_type.count | NULLABLE | INTEGER |
| product_type.description | NULLABLE | STRING |
| product_type.filter | NULLABLE | STRING |
| product_type.name | NULLABLE | STRING |
| product_type.parent | NULLABLE | INTEGER |
| product_type.slug | NULLABLE | STRING |
| product_type.taxonomy | NULLABLE | STRING |
| product_type.term_group | NULLABLE | INTEGER |
| product_type.term_id | NULLABLE | INTEGER |
| product_type.term_taxonomy_id | NULLABLE | INTEGER |
| purchasable | NULLABLE | BOOLEAN |
| purchase_note | NULLABLE | STRING |
| rating_count | NULLABLE | INTEGER |
| regular_price | NULLABLE | FLOAT |
| related_ids | REPEATED | INTEGER |
| reviews_allowed | NULLABLE | BOOLEAN |
| sale_price | NULLABLE | STRING |
| shipping_class | NULLABLE | STRING |
| shipping_class_id | NULLABLE | INTEGER |
| shipping_required | NULLABLE | BOOLEAN |
| shipping_taxable | NULLABLE | BOOLEAN |
| short_description | NULLABLE | STRING |
| sku | NULLABLE | STRING |
| slug | NULLABLE | STRING |
| sold_individually | NULLABLE | BOOLEAN |
| status | NULLABLE | STRING |
| stock_quantity | NULLABLE | INTEGER |
| stock_status | NULLABLE | STRING |
| tax_class | NULLABLE | STRING |
| tax_status | NULLABLE | STRING |
| total_sales | NULLABLE | INTEGER |
| type | NULLABLE | STRING |
| upsell_ids | REPEATED | INTEGER |
| virtual | NULLABLE | BOOLEAN |
| weight | NULLABLE | STRING |
