BigCommerce

Knowi connects directly to BigCommerce via its REST Management API (v2 + v3), enabling order, revenue, catalog, and customer analytics on your store data. Join BigCommerce with Google Ads, Stripe, Klaviyo, your data warehouse, and 30+ other sources - no ETL required.

Overview

1. Connect to BigCommerce using a store-level API account access token from your store control panel.
2. Pull from pre-built collections (Orders, Products, Customers, Categories, Brands, Coupons, Store Info) or use the Custom Query collection for any BigCommerce endpoint.
3. Join BigCommerce data with Google Ads, Stripe, MongoDB, PostgreSQL, REST APIs, and 30+ other sources without a warehouse.
4. Build revenue dashboards, run product and cohort analysis, and ask questions in natural language.

Connecting

BigCommerce uses API token authentication (not OAuth). You create a store-level API account inside BigCommerce, copy two values - the Access Token and the Store Hash - and paste them into Knowi.

Step 1: Create an API Account in BigCommerce

1. Log in to your BigCommerce store control panel.
2. Go to Settings, then under the API section click Store-level API accounts.
3. Click Create API Account. If BigCommerce asks for the token type, choose V2/V3 API Token. Give it a name (e.g., "Knowi Analytics").
4. Under OAuth Scopes, select read-only access for the data you want to query:
   • Orders: read-only
   • Products: read-only
   • Customers: read-only
   • Marketing: read-only (for Coupons)
   • Information & Settings: read-only
   • (add any other read-only scopes you plan to query)
5. Click Save. BigCommerce displays the credentials once. Copy two things:
   • Access Token — the access_token value.
   • Store Hash — found in the API Path shown: https://api.bigcommerce.com/stores/{store_hash}/v3/. The store hash is the value between /stores/ and the next /.

Step 2: Connect in Knowi

1. Log in to Knowi and navigate to Queries from the left sidebar.
2. Click New Datasource (the + icon), then search for BigCommerce in the datasource selector.
3. Fill in the form:
   • Datasource Name: A label for this connection (e.g., "BigCommerce Production")
   • Store Hash: The store hash you copied from the API Path
   • Access Token: The access_token from the API account
4. Click Save to store the datasource.

Step 3: Create a Query

1. Click Start Querying (or create a new query and select your BigCommerce datasource).
2. Select a Collection from the "BigCommerce Options" dropdown:
   • Orders — All orders with status, totals, payment method, and customer associations
   • Products — Catalog products with pricing, inventory, and visibility
   • Customers — Customer records with contact details and group membership
   • Categories — Catalog category tree
   • Brands — Catalog brands
   • Coupons — Coupon codes with type, amount, and usage
   • Store Info — Store-level profile and configuration
   • Custom Query — Hit any BigCommerce API endpoint
3. Fill in the optional filter parameters (status, date ranges, customer ID).
4. Click Preview to see results.
5. Name your dataset and click Create & Run to save and schedule.

Available Collections

BigCommerce mixes two API versions. v3 collections (Products, Customers, Categories, Brands) wrap rows in a data array alongside a meta.pagination object, so they begin with select expand(data); to flatten the rows. v2 collections (Orders, Coupons, Store Info) return a flat array (or a single object for Store Info), so no expand is needed.

Orders

Returns all orders with status, totals, payment method, and customer associations. BigCommerce v2 API - returns a flat array. Core data for revenue and order dashboards.

Parameters:

Default Cloud9QL:

select id, customer_id, status, status_id, total_inc_tax, subtotal_inc_tax,
       total_tax, currency_code, payment_method, items_total,
       date_created, date_modified
order by date_created desc;

Products

Catalog products with pricing, inventory, visibility, and brand associations. BigCommerce v3 catalog API - rows are wrapped in a data array, so the query starts with select expand(data);.

Parameters:

Default Cloud9QL:

select expand(data);
select id, name, sku, type, price, cost_price, sale_price,
       inventory_level, inventory_warning_level, is_visible, availability,
       brand_id, total_sold, date_created, date_modified
order by date_modified desc;

Customers

Customer records with contact details and group membership. BigCommerce v3 API - rows are wrapped in a data array, so the query starts with select expand(data);.

Parameters:

Default Cloud9QL:

select expand(data);
select id, email, first_name, last_name, company, phone,
       customer_group_id, registration_ip_address, date_created, date_modified
order by date_created desc;

Categories

Catalog category tree (v3). Rows are wrapped in a data array, so the query starts with select expand(data);.

Brands

Catalog brands (v3). Rows are wrapped in a data array, so the query starts with select expand(data);.

Coupons

Coupon codes with type, amount, and usage (v2 - flat array).

Parameters: Coupon Code (exact match), Type (per_item_discount, percentage_discount, per_total_discount, shipping_discount, free_shipping, promotion).

Store Info

Store-level profile returned as a single object (v2). Useful as a constant join key when working across multiple BigCommerce stores. No expand needed.

Custom Query

Hit any endpoint not covered by the pre-built collections. Specify the Resource path including the version, e.g. v3/catalog/products, v3/catalog/variants, v3/customers/addresses, v3/pricelists, v2/orders, v2/orders/{order_id}/products. For v3 resources keep the default select expand(data); Cloud9QL; for v2 resources (bare arrays) remove it.

Date Tokens

BigCommerce accepts ISO-8601 (and RFC-2822) timestamps. Knowi date tokens automatically format for you:

{$c9_today:yyyy-MM-dd}                  - today
{$c9_today-30d:yyyy-MM-dd}              - 30 days ago
{$c9_thismonth:yyyy-MM-dd}              - first day of this month
{$c9_today-1d:yyyy-MM-dd}               - yesterday (for daily incremental syncs)

Cloud9QL Examples

Remember: v3 collections (Products, Customers, Categories, Brands) need select expand(data); first to flatten the data array. v2 collections (Orders, Coupons, Store Info) return flat arrays, so no expand is needed.

Revenue by Day (Orders, v2)

select date_format(date_created, 'yyyy-MM-dd') as order_date,
       total_inc_tax
where status_id = 10;

select order_date,
       sum(total_inc_tax) as revenue,
       count(*) as orders
group by order_date
order by order_date desc;

Top Products by Units Sold (Products, v3)

select expand(data);
select name, sku, brand_id, total_sold, price
where is_visible = true;

select name, sku, total_sold, (total_sold * price) as gross_revenue
order by total_sold desc;

New Customers per Week (Customers, v3)

select expand(data);
select date_format(date_created, 'yyyy-ww') as signup_week, id
where date_created >= '{$c9_today-90d:yyyy-MM-dd}';

select signup_week, count(*) as new_customers
group by signup_week
order by signup_week desc;

Scheduling Queries

BigCommerce data changes constantly. Schedule queries to refresh on a cadence that matches your reporting needs:

1. Open the query, click the schedule icon.
2. Set a frequency (every hour, every 6 hours, daily, weekly).
3. Use the Modified After parameter with {$c9_lastrun:yyyy-MM-dd} to fetch only orders/products changed since the last run.

Cross-Source Joins

BigCommerce becomes much more powerful when joined with other sources. Some common patterns:

BigCommerce + Google Ads: Revenue vs Ad Spend (ROAS)

select o.order_date,
       o.revenue as store_revenue,
       g.spend as ad_spend,
       o.revenue / g.spend as roas
from bigcommerce_revenue_by_day as o
left join google_ads_spend_by_day as g
  on o.order_date = g.date
order by o.order_date desc;

BigCommerce + Stripe: Order Revenue minus Processing Fees

select o.id as order_id,
       o.total_inc_tax as gross,
       s.fee as stripe_fee,
       o.total_inc_tax - s.fee as net_revenue
from bigcommerce_orders as o
left join stripe_balance_transactions as s
  on o.id = s.metadata_order_id
where o.status_id = 10;

BigCommerce + Klaviyo: Did Win-Back Emails Drive Repeat Orders?

Compare customers who received Klaviyo win-back campaigns vs those who did not, tracking repeat-order rates 30/60/90 days after the campaign.

See Cross-source joins documentation for details.

Rate Limits and Pagination

• Rate limit: BigCommerce applies per-plan request quotas (token-bucket). Knowi automatically respects this with backoff on 429 responses.
• Pagination: Page-based, up to 250 records per page. Knowi pulls all pages automatically using the page parameter and stops when the API returns no more rows.
• Backfills: Large catalogs and order histories complete in minutes to a few hours depending on volume. Run during off-hours and rely on incremental (Modified After) syncs after that.

Troubleshooting

Resources

• BigCommerce REST Management API
• Creating store-level API accounts
• BigCommerce API rate limits
• Cloud9QL reference
• Cross-source joins
• Embedded analytics with SSO