Understanding BigCommerce Data in WordPress

For years, traditional SaaS (Software as a Service) models for ecommerce have stored their data in their own servers, making it only accessible through the platform’s admin interface. BigCommerce has disrupted that with their Open SaaS model. This model enables a headless build, requiring local data storage in your Content Management System (CMS). In this post we’ll look at how WordPress handles local storage of BigCommerce data and use that as an example for any other CMS.

Why We Need Local Data Storage

Headless ecommerce presents product data on the store head (WordPress for the purposes of this article) while transactions occur on the ecommerce SaaS. In WordPress, products are stored as a custom post type, a cart page is generated on the fly, and checkout happens in an embedded iframe (or optionally a full page on the BigCommerce servers).

The product data is presented so often on the front end it makes a lot of sense to keep a local cache of that data, and update it over the API as needed. In the BigCommerce for WordPress plugin there’s a Settings UI for setting how often the local data is refreshed. It can range from 5 minutes to a month.

So now we have a local cache of that data, but how is it stored? How do we access it?

If your CMS or framework has a built in method for storing Settings, and object data like products then you should use it. If not, then you’ll need to build your own storage mechanism. I’m going to show how we do it in WordPress, other frameworks should store the data in whatever format is best for that framework.

NOTE: if you’re thinking “Why not use GraphQL and hit the API on every call and avoid local storage altogether?”, you’re right, you could do that. It comes with all of its own issues with latency etc., and if you’re willing to solve for those it should work great. That’s not what this article is about.

WordPress Data Storage

Product Data

WordPress has a concept called Custom Post Types. Custom Post Types use the same database table as Posts and Pages (which are themselves Custom Post Types). There’s simply a field for type, and each type has a dedicated keyword which identifies that post type. Posts and Pages are post and page respectively, and BigCommerce products are called bigcommerce_product.

WordPress database tables have a unique name prefix for each install, which I’ll refer to from here on as $PREFIX. This means the posts table is called $PREFIX_posts.

The only fields in the $PREFIX_posts table that hold BigCommerce relevant data are

• post_title
  Holds the product name
• post_content
  Holds the product description
• post_status
  This is actually just normal WordPress statuses, but like all post types, Draft content is never rendered on the front of the website.
• post_name
  This is the “slug” of the post. It’s typically the title, converted to lowercase, spaces replaced with hyphens, and all punctuation removed.
• post_type
  For BigCommerce products, this holds bigcommerce_product.

That’s all the data stored in the posts table, the rest of the data about products is in the $PREFIX_postmeta table.

Product Meta Data

Very little of the actual product data is stored in the main posts table, the majority of it is stored in the postmeta table. Post meta is associated with the original product via a WordPress post_id. Here’s an example for a product whose post_id is 44.

Let’s go through some of what we have available to us in the postmeta table. We won’t look at every option because some of them are for internal use only, and won’t help you with anything.

bigcommerce_id

This is the original product ID set on the BigCommerce side. This is different from the WordPress post_id which you might use to get images etc. If you were to leverage the BigCommerce Meta Fields API for example, you’d want to attach your fields to the bigcommerce_id, not the WordPress post_id.

bigcommerce_sku

This is exactly what it sounds like, and would allow you to do a query to get the product and post ID to get the rest of the product’s data.

bigcommerce_rating

This is the raw number that drives the 5 star rating widget. You can build any other kind of ratings rendering using it.

bigcommerce_sales

This is simply a total of the number of sales. At this time the WordPress plugin doesn’t use this number for anything, but it could be used to get a list of Most Popular products.

bigcommerce_calculated_price

This is the price of the product as seen on the storefront. It will be equal to the sale_price, if set, and the price if there is not a sale_price. As we’ll see in a moment, all the various price options are stored here in the database, but this is the one you’ll want most often, so it’s stored here as a simple integer for easy access.

bigcommerce_inventory_level

Just like it sounds, this lists how many of this product are available. This would allow you to get creative with how products are marketed or rendered. It could help dictate where products are shown on the site.

bigcommerce_price_range

As mentioned above, a given product can have more than one price. If there is a range then this field holds it in a serialized string. You can unserialize it and get an array of the data with WordPress’ maybe_unserialize() function.

bigcommerce_listing_data

This field holds a serialized string containing all the information about how this product is listed. This could be used to override any number of default listing options to make unique listings.

{
    “channel_id”: 19968,
    “listing_id”: 95299805,
    “product_id”: 112,
    “state”: “active”,
    “date_created”: “2019–09–03T21:34:38+00:00”,
    “variants”: [{
        “product_id”: 112,
        “variant_id”: 77,
        “state”: “active”,
        “date_created”: “2019–09–03T21:34:38Z”
    }]
}

bigcommerce_modifier_data

This field holds a serialized string containing all the possible modifiers for this product. This could allow you to recreate the way modifiers are rendered on the product single template, making for a unique way for customers to choose their options.

bigcommerce_options_data

Similar to the modifier data, this serialized string holds all of a products options.

bigcommerce_custom_fields

Custom fields have the potential to be tremendously powerful because you can put in such a wide variety of data. For example, BigCommerce doesn’t support Vimeo the same way it does YouTube, but using custom fields you could create an entire Vimeo collage for a product. This is also a serialized string.

bigcommerce_gallery

This holds a serialized string of WordPress post_ids of the images that the plugin imported into the WordPress Media Library. Here’s an example: a:4:{i:0;i:46;i:1;i:47;i:2;i:48;i:3;i:49;}

To use this you would unserialize the string and then run the appropriate WordPress image functions to get the images. At that point you can render the gallery using any custom tool you wish.

bigcommerce_variant_images

Similar to the gallery, this holds a serialized string of WordPress post_ids for the variant images for each product.

bigcommerce_source_data

This field holds all meta information about the product in one giant serialized string. I won’t go into detail for every item, but you should read through the list to see what might be valuable to you. This could be used to render anything about a given product. An example might be to show a list of products smaller than a specific size, so they could easily go in an airplane carryon. Another example would be to show all products with a certain color before a sporting event.

{
    “name”: “Think Big Hat”,
    “type”: “physical”,
    “sku”: “Think-Big-Hat”,
    “description”: “<p>If you’re looking for a made in America hat, the Bayside 3630 is it! It’s unstructured with a curved visor and adjustable strap. And it has a small American flag sewed onto&nbsp;the back.</p>rn<ul>rn<li>100% washed cotton chino twill</li>rn<li>Unstructured, 6-panel, low-profile</li>rn<li>6 embroidered eyelets</li>rn<li>Adjustable strap with hide-away buckle</li>rn<li>Head circumference: 19 &frac14;&rdquo; — 23 u215d&rdquo;</li>rn<li>Made in the USA</li>rn</ul>”,
    “weight”: 0.5,
    “width”: 10,
    “depth”: 10,
    “height”: 10,
    “price”: 30,
    “cost_price”: 0,
    “retail_price”: 0,
    “sale_price”: 0,
    “tax_class_id”: 0,
    “product_tax_code”: “”,
    “categories”: [27],
    “brand_id”: 0,
    “inventory_level”: 0,
    “inventory_warning_level”: 0,
    “inventory_tracking”: “none”,
    “fixed_cost_shipping_price”: 0,
    “is_free_shipping”: false,
    “is_visible”: true,
    “is_featured”: true,
    “related_products”: [-1],
    “warranty”: “”,
    “bin_picking_number”: “”,
    “layout_file”: “product.html”,
    “upc”: “”,
    “search_keywords”: “”,
    “availability”: “available”,
    “availability_description”: “”,
    “gift_wrapping_options_type”: “any”,
    “gift_wrapping_options_list”: [],
    “sort_order”: 0,
    “condition”: “New”,
    “is_condition_shown”: false,
    “order_quantity_minimum”: 0,
    “order_quantity_maximum”: 0,
    “page_title”: “”,
    “meta_keywords”: [],
    “meta_description”: “”,
    “view_count”: 7,
    “preorder_message”: “”,
    “is_preorder_only”: false,
    “is_price_hidden”: false,
    “price_hidden_label”: “”,
    “custom_url”: {
    “url”: “/think-big-hat/”,
    “is_customized”: false
    },
    “open_graph_type”: “product”,
    “open_graph_title”: “”,
    “open_graph_description”: “”,
    “open_graph_use_meta_description”: true,
    “open_graph_use_product_name”: true,
    “open_graph_use_image”: true,
    “id”: 112,
    “calculated_price”: 30,
    “reviews_rating_sum”: 0,
    “reviews_count”: 0,
    “total_sold”: 0,
    “custom_fields”: [],
    “bulk_pricing_rules”: [],
    “date_created”: “2019–09–02T19:08:37+00:00”,
    “date_modified”: “2019–09–10T02:28:06+00:00”,
    “images”: [{
        “is_thumbnail”: false,
        “sort_order”: 0,
        “description”: “”,
        “id”: 376,
        “product_id”: 112,
        “image_file”: “i/655/BC_Swag_Store_Images__23990.jpg”,
“url_zoom”: “https://cdn11.bigcommerce.com/s-1ek0692msi/products/112/images/376/BC_Swag_Store_Images__23990.1567452437.1280.1280.jpg?c=1”,
“url_standard”: “https://cdn11.bigcommerce.com/s-1ek0692msi/products/112/images/376/BC_Swag_Store_Images__23990.1567452437.386.513.jpg?c=1”,
“url_thumbnail”: “https://cdn11.bigcommerce.com/s-1ek0692msi/products/112/images/376/BC_Swag_Store_Images__23990.1567452437.220.290.jpg?c=1”,
“url_tiny”: “https://cdn11.bigcommerce.com/s-1ek0692msi/products/112/images/376/BC_Swag_Store_Images__23990.1567452437.44.58.jpg?c=1”,
“date_modified”: “2019–09–02T19:27:17+00:00”
    }],
“videos”: [],
“variants”: [
    {
    “cost_price”: 0,
    “is_free_shipping”: false,
    “purchasing_disabled”: false,
    “purchasing_disabled_message”: “”,
    “image_url”: “”,
    “upc”: “”,
    “inventory_level”: 0,
    “inventory_warning_level”: 0,
    “bin_picking_number”: “”,
    “id”: 105,
    “product_id”: 112,
    “sku”: “Think-Big-Hat-Black/White”,
    “sku_id”: 141,
    “option_values”: [],
    “calculated_price”: 30
    }
],
“options”: [],
“modifiers”: []
}

Store Options

The BigCommerce for WordPress Settings page stores its options in the $PREFIX_options table. The vast majority of them aren’t very useful to a developer, but there are several that are absolutely key.

Authentication Keys

In order to make an API call back to BigCommerce there must be a valid client_id and access_token in the headers of the request. These are stored in the $PREFIX_options table as bigcommerce_client_id and bigcommerce_access_token. When making an API call with the WordPress HTTP API, be sure to include these in the headers, or your API call won’t work.

Channel ID

The channel ID helps determine what products appear on your website. It’s possible to connect more than one channel ID to your store, and have different products in each channel. Then you would provide a mechanism on the front of the site for switching channels.

This could be for allowing the customer to choose different languages, auto-assigning a different channel to a logged in user for B2B sales, etc. Knowing what channel IDs are available and which one the customer is looking at allows for tremendous flexibility.

Summary

The biggest advantage of headless ecommerce is the power and flexibility it provides. The BigCommerce data stored in the WordPress database is the raw materials available to you to build anything you want. The plugin does a great job at giving you a basic store with common store functionality, but with the raw tools and some creativity you have the ability to make something really extraordinary.

Understanding BigCommerce Data in WordPress was originally published in BigCommerce Developer Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.