Woocommerce to BigQuery: How to Move Your Data

Moving WooCommerce into BigQuery gives a store somewhere to analyse trading without running reports against the site customers are shopping on. WooCommerce reports adequately on recent orders and is a poor place to ask about three years of customer behaviour.

This guide covers the managed path with Airbyte. Two things shape the build: you are syncing from your own server rather than somebody's managed platform, and order records keep changing after they arrive.

Woocommerce to BigQuery at a glance:

Why move data from Woocommerce to BigQuery?

Two situations account for most of these pipelines.

The first is taking analysis off the shop. Running reports against the same database serving your storefront is a trade-off nobody wants to make during a busy period, and a warehouse removes the question entirely.

The second is joining orders to marketing spend and support contacts, which no storefront holds. If your priority is interactive speed over very large histories rather than joins across systems, Woocommerce to ClickHouse answers that better than a warehouse will.

What do you need before you start?

Four things, and the first two cause most of the failed first attempts:

Pretty permalinks enabled in WordPress. The REST API is not routed without them, and the resulting failure looks like a credentials problem rather than a settings one. The WooCommerce source documentation covers the requirement and the key generation.

Your shop name as a bare domain. No scheme and no trailing path, which is a surprisingly common reason for a configuration that looks correct and does not connect.

Read-only API keys. Generated in WooCommerce's settings, and read is all this pipeline needs. These credentials reach a system that takes payments, so write access is a risk with no benefit.

A BigQuery dataset in the right location. Location is fixed at creation and BigQuery will not join across locations, so put this where your marketing and support data already live.

If your server restricts traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a Woocommerce to BigQuery pipeline in Airbyte?

Step 1: Choose a sync window that avoids your trading peak

Look at when your store is busiest and schedule the sync well away from it, particularly the first one. This matters more than it would on a hosted platform because the server answering the connector is the server serving your customers, and a long backfill competing with a promotion is a self-inflicted outage. Decide the window before configuring anything, since it shapes everything else.

Step 2: Configure the WooCommerce source

Click Sources in the left navigation, then New Source, and select WooCommerce, following adding a source. Supply the shop name as a bare domain, your consumer key and secret, and a start date. If the connection fails, check permalinks and the domain format before regenerating perfectly good keys.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project identifier, dataset and service account credentials. Most stores are small enough that batched standard inserts are ample and Cloud Storage staging is unnecessary.

Step 4: Create the connection and go gently

Click Connections, then New connection, select your streams and a sync mode. Daily suits trading analysis, and a gentler schedule is also kinder to a server doing real work. Watch response times on the site during the first sync rather than assuming it is invisible.

Then build views, because raw order tables contain several versions of the same order and nobody wants to discover that in a revenue figure.

Whose server are you syncing from?

Yours, and that changes the etiquette of this pipeline entirely. Most connectors talk to a vendor's platform, where rate limits exist to protect infrastructure you do not pay for and the worst case is a slow sync. Here the API is served by your own WordPress installation, on the same hardware handling checkout.

So the constraint is not a published limit but your server's capacity, and the cost of exceeding it is paid by customers rather than by the pipeline. A backfill pulling years of orders during a sale is the version of this that gets noticed, and it is entirely avoidable by scheduling the first sync somewhere quiet.

WooCommerce also lets you configure its own rate limiting, which is worth using rather than relying on the connector to be polite. Set something conservative before the first sync, watch how the site behaves, and relax it if there is headroom. That is a more comfortable sequence than discovering the limit during trading and having to explain it afterwards.

What happens as orders change status?

They arrive again, and your table accumulates versions. An order moves from pending to processing to completed, and may later be refunded or cancelled, so the same order identifier appears several times with different statuses and different totals depending on when each sync caught it.

Counting rows in that table therefore overstates your orders, and summing totals overstates revenue, both in ways that look plausible enough to reach a report before anybody questions them. This is the most common way a WooCommerce dataset produces confidently wrong numbers, and it has nothing to do with the pipeline working correctly, which it is.

Build a view selecting the latest version of each order and have all reporting read that rather than the underlying table. Use the extraction timestamp to decide which version wins, since it is under your control while the store's own timestamps are not, and filter on the partitioning column in the same view so queries do not scan every snapshot you have ever taken. Both habits cost one piece of SQL and save every query afterwards.

Frequently asked questions

The connection fails but my keys are correct.

Check that pretty permalinks are enabled, since the REST API is not routed without them, and that the shop name is a bare domain with no scheme or path.

Will syncing slow down my shop?

It can, because the API runs on your own server. Schedule the first sync away from your trading peak and configure WooCommerce's rate limiting conservatively.

Why is my order count too high?

Order status changes over an order's life, so the same order arrives repeatedly. Report from a view selecting the latest version of each order rather than from the raw table.

Why are my queries expensive?

Probably not filtering on the extraction timestamp partitioning column, which means every query scans the full history. Build the filter into your views.

Can I do this without writing code?

The pipeline, yes. The deduplicating view is SQL, and without it your revenue figures will be wrong in a way that looks believable.

Get your Woocommerce data into BigQuery

Enable pretty permalinks and enter the shop name as a bare domain, since those two account for most failed first attempts and both look like credential problems. Schedule the first sync away from trading, because the server answering the connector is the one serving customers. Then report from a view that selects the latest version of each order and filters on the partitioning column, rather than from tables that contain every status an order passed through.

Airbyte's connector catalog includes 600+ pre-built connectors, so store data can be analysed beside the spend that drove it. For another storefront into the same destination, see Shopify to BigQuery, and for the same source into a search engine, Woocommerce to Elasticsearch.

Start syncing now →