Introduction
BigCommerce provides a powerful set of reporting and analytics tools to help store owners to understand their visitors’ behaviour and plan merchandising strategies. Although these tools are a great aid to most businesses, Priocept have recently helped a number of customers that had very specific requirements for reporting that were not able to be fulfilled using the out-of-box BigCommerce functionality.
These customers predominantly sell products to other businesses – they are B2B focussed, rather than B2C. Their reporting requirements are therefore less centred around the individual customer as they are more concerned with the performance of their trade accounts and their sales agents for those accounts. For B2B store owners, the Ecommerce Analytics functionality within BigCommerce usually falls short of their requirements.
To help these customers Priocept used the native BigCommerce integration with Google BigQuery and developed a custom integration to ship all the data required for their reports. This article gives an overview of custom reporting using Google BigQuery and other key considerations of building custom reports, based on Priocept’s experience.
Syncing Data to BigQuery
BigCommerce provides an out-of-the-box integration with Google BigQuery. Google describes BigQuery as an “Enterprise Data Warehouse”. As the name suggests it is built to handle “big data”, or very large datasets, and can provide an extremely cost-effective way to query billions of rows of data, without requiring an upfront investment in the infrastructure that would be required to support data storage and processing queries over so many records.
These impressive capabilities aside, to the end user BigQuery is fundamentally a cloud-based database with a nice UI for running SQL queries. It is also very cheap, particularly with smaller data sets, which makes it applicable for a range of data storage and query scenarios, including integrating with BigCommerce. The trade-off is that query processing resources are shared with other users of BigQuery and response times are usually a minimum of 2 seconds for queries, even if querying over a tiny amount of data. This makes BigQuery unsuitable as the data source for most customer-facing applications, but it is perfectly acceptable in support of internal business reporting.
Enabling the BigCommerce integration with BigQuery will automate the synchronisation of customer, order, and product data from BigCommerce to tables in BigQuery. The sync job is run on a schedule, which can be defined in BigCommerce.
Below is a screen shot of the tables that are created in Google BigQuery, once the integration is enabled:
The integration affectively creates a copy of a BigCommerce store’s data in BigQuery and then keeps it synchronised on a schedule. It is worth noting that since the data synchronisation process is scheduled, there will be a period where the latest data is not reflected in BigQuery, but users can manually force immediate synchronisation when required.
With the data in BigQuery, data analysts can run queries and build views to collate the data into an understandable format. This approach is great for users with SQL skills, but business users are unlikely to be able to interrogate the data directly in BigQuery. It is therefore preferable to use BigQuery as the data source for more friendly reports that can be created in data visualisation systems.
Building Reports
Tools such as Google Data Studio, Tableau, Redash and Microsoft Power BI, can be used to visualise data and build custom reports without requiring users to write their own SQL queries.
Google Data Studio is a good option for creating custom reports for BigCommerce since it keeps the entire data solution within Google Cloud Platform. Connecting to Google BigQuery data sources is natively supported by Google Data Studio, which provides a full set of features for displaying data tables, charts and graphs. The platform is also free to use.
Below is a sample report dashboard from Google Data Studio, which gives an idea on the type of reports that can be built.
Sourcing Missing BigCommerce Data for B2B Stores
The BigCommerce integration to BigQuery was developed with B2C stores in mind. Priocept has been helping a number of customers to implement BigCommerce B2B which adds functionality such as account management, sales staff accounts, and company-specific pricing. We have found that for customers using BigCommerce B2B, some of the data they need is not present in the native integration.
To solve this issue, Priocept developed custom integrations to augment the data available in BigQuery. This was achieved by developing event-driven integration code using Google Cloud Functions that retrieves data via APIs from BigCommerce and BundleB2B (the BigCommerce-owned app that provides B2B functionality). We then write this data to custom tables in BigQuery, using the BigQuery API.
Summary
Store owners with advanced reporting requirements can build customised reports to meet their needs using the BigCommerce integration to Google BigQuery and by connecting data visualisation tools such as Google Data Studio. B2B store owners may find the native integration lacking, but thanks to the API-first approach of BigCommerce, custom integrations can be easily developed to fulfil the most complex of requirements.
If you need support with implementing custom reports in BigCommerce then please get in touch. Priocept are an experienced and accredited implementation partner for both BigCommerce and Google Cloud Platform.
See Also
Google BigQuery – https://cloud.google.com/bigquery
Google BigQuery API – https://cloud.google.com/bigquery/docs/reference/rest
Google Data Studio – https://datastudio.google.com/
BigCommerce BigQuery Connector – https://support.bigcommerce.com/s/article/Setting-up-Google-BigQuery
BigCommerce API – https://developer.bigcommerce.com/docs/
BundleB2B API – https://developers.bundleb2b.cc/docs/openapi/