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.

  1. Log in to your WordPress admin panel as an administrator.
  2. Go to WooCommerce > Settings > Advanced > REST API and click Add key.
  3. Enter a description, e.g. "WitCloud", choose the user and set Permissions to Read.
  4. Click Generate API key.
  5. Copy the Consumer key (it starts with ck_) and the Consumer secret (it starts with cs_). 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.

Integrations list with the Add integration button

Select WooCommerce from the list of available integrations.

Integration catalog filtered to WooCommerce

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

Collect name field filled in with the example name WooCommerce PL

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.

Shop url field filled in with the example address https://demo-shop.example

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.

Authorize button next to the WooCommerce authorization field, active once the shop address is filled in

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.

Authorize pop-up with the Client ID and Client secret fields filled with example values

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

WooCommerce authorization drop-down open with one example token

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.

Advanced settings open with the Data freshness field set to 4 hours

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.

Static IP switch in the advanced settings

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.

Tables to collect field with all four offered tables selected

Click Next.

Collect settings filled in with the Next button

Historical data and scheduling

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

Historical Data field filled in with 365 days

A data synchronization schedule is created automatically. More information on this topic can be found here.

Click Create to complete the configuration.

Data collection settings with the Create button

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 NameModeType
countNULLABLEINTEGER
descriptionNULLABLESTRING
displayNULLABLESTRING
idNULLABLEINTEGER
imageNULLABLERECORD
image.altNULLABLESTRING
image.date_createdNULLABLETIMESTAMP
image.date_created_gmtNULLABLETIMESTAMP
image.date_modifiedNULLABLETIMESTAMP
image.date_modified_gmtNULLABLETIMESTAMP
image.idNULLABLEINTEGER
image.nameNULLABLESTRING
image.srcNULLABLESTRING
menu_orderNULLABLEINTEGER
nameNULLABLESTRING
parentNULLABLEINTEGER
slugNULLABLESTRING
Data schema for woocommerce_orders
Field NameModeType
billingNULLABLERECORD
billing.address_1NULLABLESTRING
billing.address_2NULLABLESTRING
billing.cityNULLABLESTRING
billing.companyNULLABLESTRING
billing.countryNULLABLESTRING
billing.emailNULLABLESTRING
billing.first_nameNULLABLESTRING
billing.last_nameNULLABLESTRING
billing.phoneNULLABLESTRING
billing.postcodeNULLABLESTRING
billing.stateNULLABLESTRING
cart_hashNULLABLESTRING
cart_taxNULLABLEFLOAT
coupon_linesREPEATEDRECORD
coupon_lines.codeNULLABLESTRING
coupon_lines.discountNULLABLEFLOAT
coupon_lines.discount_taxNULLABLEFLOAT
coupon_lines.idNULLABLEINTEGER
coupon_lines.meta_dataREPEATEDRECORD
coupon_lines.meta_data.display_keyNULLABLESTRING
coupon_lines.meta_data.display_valueNULLABLEJSON
coupon_lines.meta_data.idNULLABLEINTEGER
coupon_lines.meta_data.keyNULLABLESTRING
coupon_lines.meta_data.valueNULLABLEJSON
created_viaNULLABLESTRING
currencyNULLABLESTRING
currency_symbolNULLABLESTRING
customer_idNULLABLEINTEGER
customer_ip_addressNULLABLESTRING
customer_noteNULLABLESTRING
customer_user_agentNULLABLESTRING
date_completedNULLABLETIMESTAMP
date_completed_gmtNULLABLETIMESTAMP
date_createdNULLABLETIMESTAMP
date_created_gmtNULLABLETIMESTAMP
date_modifiedNULLABLETIMESTAMP
date_modified_gmtNULLABLETIMESTAMP
date_paidNULLABLETIMESTAMP
date_paid_gmtNULLABLETIMESTAMP
discount_taxNULLABLEFLOAT
discount_totalNULLABLEFLOAT
idNULLABLEINTEGER
is_editableNULLABLEBOOLEAN
line_itemsREPEATEDRECORD
line_items.idNULLABLEINTEGER
line_items.imageNULLABLERECORD
line_items.image.idNULLABLEINTEGER
line_items.image.srcNULLABLESTRING
line_items.meta_dataREPEATEDRECORD
line_items.meta_data.display_keyNULLABLESTRING
line_items.meta_data.display_valueNULLABLEJSON
line_items.meta_data.idNULLABLEINTEGER
line_items.meta_data.keyNULLABLESTRING
line_items.meta_data.valueNULLABLEJSON
line_items.nameNULLABLESTRING
line_items.priceNULLABLEFLOAT
line_items.product_idNULLABLEINTEGER
line_items.quantityNULLABLEINTEGER
line_items.skuNULLABLESTRING
line_items.subtotalNULLABLEFLOAT
line_items.subtotal_taxNULLABLEFLOAT
line_items.tax_classNULLABLESTRING
line_items.taxesREPEATEDRECORD
line_items.taxes.idNULLABLEINTEGER
line_items.taxes.subtotalNULLABLEFLOAT
line_items.taxes.totalNULLABLEFLOAT
line_items.totalNULLABLEFLOAT
line_items.total_taxNULLABLEFLOAT
line_items.variation_idNULLABLEINTEGER
meta_dataREPEATEDRECORD
meta_data.idNULLABLEINTEGER
meta_data.keyNULLABLESTRING
meta_data.valueNULLABLEJSON
needs_paymentNULLABLEBOOLEAN
needs_processingNULLABLEBOOLEAN
numberNULLABLEINTEGER
order_keyNULLABLESTRING
parent_idNULLABLEINTEGER
payment_methodNULLABLESTRING
payment_method_titleNULLABLESTRING
payment_urlNULLABLESTRING
prices_include_taxNULLABLEBOOLEAN
shippingNULLABLERECORD
shipping.address_1NULLABLESTRING
shipping.address_2NULLABLESTRING
shipping.cityNULLABLESTRING
shipping.companyNULLABLESTRING
shipping.countryNULLABLESTRING
shipping.first_nameNULLABLESTRING
shipping.last_nameNULLABLESTRING
shipping.phoneNULLABLESTRING
shipping.postcodeNULLABLESTRING
shipping.stateNULLABLESTRING
shipping_linesREPEATEDRECORD
shipping_lines.idNULLABLEINTEGER
shipping_lines.instance_idNULLABLEINTEGER
shipping_lines.meta_dataREPEATEDRECORD
shipping_lines.meta_data.display_keyNULLABLESTRING
shipping_lines.meta_data.display_valueNULLABLEJSON
shipping_lines.meta_data.idNULLABLEINTEGER
shipping_lines.meta_data.keyNULLABLESTRING
shipping_lines.meta_data.valueNULLABLEJSON
shipping_lines.method_idNULLABLESTRING
shipping_lines.method_titleNULLABLESTRING
shipping_lines.totalNULLABLEFLOAT
shipping_lines.total_taxNULLABLEFLOAT
shipping_taxNULLABLEFLOAT
shipping_totalNULLABLEFLOAT
statusNULLABLESTRING
summary_pageNULLABLESTRING
tax_linesREPEATEDRECORD
tax_lines.compoundNULLABLEBOOLEAN
tax_lines.idNULLABLEINTEGER
tax_lines.labelNULLABLESTRING
tax_lines.rate_codeNULLABLESTRING
tax_lines.rate_idNULLABLEINTEGER
tax_lines.rate_percentNULLABLEINTEGER
tax_lines.shipping_tax_totalNULLABLEFLOAT
tax_lines.tax_totalNULLABLEFLOAT
refundsREPEATEDRECORD
refunds.idNULLABLEINTEGER
refunds.reasonNULLABLESTRING
refunds.totalNULLABLEFLOAT
totalNULLABLEFLOAT
total_taxNULLABLEFLOAT
transaction_idNULLABLESTRING
versionNULLABLESTRING
Data schema for woocommerce_products
Field NameModeType
attributesREPEATEDRECORD
attributes.idNULLABLEINTEGER
attributes.nameNULLABLESTRING
attributes.optionsREPEATEDSTRING
attributes.positionNULLABLEINTEGER
attributes.variationNULLABLEBOOLEAN
attributes.visibleNULLABLEBOOLEAN
average_ratingNULLABLEFLOAT
backorderedNULLABLEBOOLEAN
backordersNULLABLESTRING
backorders_allowedNULLABLEBOOLEAN
brand_nameNULLABLESTRING
button_textNULLABLESTRING
catalog_visibilityNULLABLESTRING
categoriesREPEATEDRECORD
categories.idNULLABLEINTEGER
categories.nameNULLABLESTRING
categories.slugNULLABLESTRING
date_createdNULLABLETIMESTAMP
date_created_gmtNULLABLETIMESTAMP
date_modifiedNULLABLETIMESTAMP
date_modified_gmtNULLABLETIMESTAMP
descriptionNULLABLESTRING
dimensionsNULLABLERECORD
dimensions.heightNULLABLESTRING
dimensions.lengthNULLABLESTRING
dimensions.widthNULLABLESTRING
download_expiryNULLABLEINTEGER
download_limitNULLABLEINTEGER
downloadableNULLABLEBOOLEAN
external_urlNULLABLESTRING
featuredNULLABLEBOOLEAN
has_optionsNULLABLEBOOLEAN
idNULLABLEINTEGER
imagesREPEATEDRECORD
images.altNULLABLESTRING
images.date_createdNULLABLETIMESTAMP
images.date_created_gmtNULLABLETIMESTAMP
images.date_modifiedNULLABLETIMESTAMP
images.date_modified_gmtNULLABLETIMESTAMP
images.idNULLABLEINTEGER
images.nameNULLABLESTRING
images.srcNULLABLESTRING
manage_stockNULLABLEBOOLEAN
menu_orderNULLABLEINTEGER
meta_dataREPEATEDRECORD
meta_data.idNULLABLEINTEGER
meta_data.keyNULLABLESTRING
meta_data.valueNULLABLEJSON
nameNULLABLESTRING
on_saleNULLABLEBOOLEAN
parent_idNULLABLEINTEGER
permalinkNULLABLESTRING
priceNULLABLEFLOAT
price_htmlNULLABLESTRING
product_brandREPEATEDRECORD
product_brand.countNULLABLEINTEGER
product_brand.descriptionNULLABLESTRING
product_brand.filterNULLABLESTRING
product_brand.nameNULLABLESTRING
product_brand.parentNULLABLEINTEGER
product_brand.slugNULLABLESTRING
product_brand.taxonomyNULLABLESTRING
product_brand.term_groupNULLABLEINTEGER
product_brand.term_idNULLABLEINTEGER
product_brand.term_taxonomy_idNULLABLEINTEGER
product_typeREPEATEDRECORD
product_type.countNULLABLEINTEGER
product_type.descriptionNULLABLESTRING
product_type.filterNULLABLESTRING
product_type.nameNULLABLESTRING
product_type.parentNULLABLEINTEGER
product_type.slugNULLABLESTRING
product_type.taxonomyNULLABLESTRING
product_type.term_groupNULLABLEINTEGER
product_type.term_idNULLABLEINTEGER
product_type.term_taxonomy_idNULLABLEINTEGER
purchasableNULLABLEBOOLEAN
purchase_noteNULLABLESTRING
rating_countNULLABLEINTEGER
regular_priceNULLABLEFLOAT
related_idsREPEATEDINTEGER
reviews_allowedNULLABLEBOOLEAN
sale_priceNULLABLESTRING
shipping_classNULLABLESTRING
shipping_class_idNULLABLEINTEGER
shipping_requiredNULLABLEBOOLEAN
shipping_taxableNULLABLEBOOLEAN
short_descriptionNULLABLESTRING
skuNULLABLESTRING
slugNULLABLESTRING
sold_individuallyNULLABLEBOOLEAN
statusNULLABLESTRING
stock_quantityNULLABLEINTEGER
stock_statusNULLABLESTRING
tax_classNULLABLESTRING
tax_statusNULLABLESTRING
total_salesNULLABLEINTEGER
typeNULLABLESTRING
upsell_idsREPEATEDINTEGER
virtualNULLABLEBOOLEAN
weightNULLABLESTRING