Today we’re going to take a look at how to use best-in-class data warehousing and analytics solutions on top of Moltin, specifically in relation to orders and order items.
We’re going to cover how to get data out of Moltin and a common pattern for sending that data elsewhere. Then, using an example, we will send orders and order items to Google BigQuery in order to have the data inside a data warehouse so that it is queryable. Once the data is inside BigQuery, we will use Metabase to create a visual dashboard, as well as ask some questions about that data.
The tools we are going to use are:
- Moltin API as the commerce API in your application.
- Google BigQuery to securely store and expose data for visualization.
- Metabase to visualize and analyze our exposed order data.
- AWS KMS to encrypt/decrypt private variables.
- Google Cloud Functions to run code without thinking about servers.
- Serverless to add an easy abstraction layer above Google Cloud Functions.
Fetching data from Moltin
There are two ways in which we can grab data from Moltin. This example will only use one, the poll method, but for context, it’s useful to discuss both briefly.
Use push to trigger actions immediately when any change occurs. For this purpose, Moltin provides the Events (webhooks) service, which gives you the ability to trigger events when something happens in Moltin. Events can be created via the dashboard and via the API and can listen for any number of observable events. You would use push, for example, when sending an order confirmation email, once the order is created, or sending an SMS when an order is shipped. You can also make use of push via our Zapier integration.
Instead of learning about changes in real time from Moltin, we can always simply call the API at any time and receive a bulk of data in one go. Since our use case is not reliant on updating our analytics in real time, we can batch up orders and update our analytics twice a day instead. In order to do this, every time it runs, we will have our application fetch all orders created or updated in the past day.
Sending data to BigQuery
Once our application has received its data, either by calling for it or receiving webhooks, it needs to forward this information to Google BigQuery (BQ). Since BQ may expect information in a different layout to that which Moltin sends, we may also need to do some reformatting on the fly. We’ve found serverless functions work well for the purposes mentioned above.
Most cloud providers offer a serverless product, and you may want to change your provider over time. For this reason, we recommend using a framework to create an abstraction layer on top of the provider. We’re going to use Serverless as the abstraction, and Google as the provider.
In a “here’s one I made earlier” approach, let's take a look at a pre-baked serverless function which is already set up to do everything you need. You can find the repository within our integration examples on GitHub and it comes with its own set of instructions in a README file.
It does three things at its core:
- Gets the relevant orders from Moltin.
- Reformats the order and order item data in a way BigQuery expects.
- Sends the data to BigQuery.
You should follow along with the README for the example and come back to this guide once completed.
Visualizing data using Metabase
Welcome back! You now have order data flowing periodically to an external database! You can, of course, query this database using SQL directly, but we want to be able to show our data in a visual and meaningful way, through easy-to-interpret dashboards. While Google has its own product for this (Google Data Studio), we can make use of other agnostic third-party tools in case we add data from other sources, too. Here we’re going to use an open source software called Metabase.
Install the tool and follow the instructions to add a data source. You’re going to select BigQuery as the type, and Metabase will show you how to connect. Once your dataset is created, you’ll be able to ask questions of your data. You should follow this guide in order to learn how to do so. Once complete, you’ll know exactly how to create a smart dashboard showing you all kinds of information about your Moltin orders!
Total costs involved
We can model the costs of this solution fairly accurately, though it does depend on how many orders you are processing per day. Costs are prepared at the time of writing and may have changed since the publication of this post. We do not include Moltin as this guide presumes you are already a Moltin customer.
BigQuery: Split into storage and processing (queries). The first 10 GB per month of storage is free, as is the first 1 TB of query data processed per month - https://cloud.google.com/bigquery/pricing
Google KMS: One key at $0.06 per month plus $0.03 for up to 10 000 operations - https://cloud.google.com/kms/pricing
Google Cloud Functions: Variable based on how often you run the function and the number of orders you receive per day. However, for the sake of this simplicity, we will use a standard example on Googles pricing page, resulting in a total cost of $7.20 per month - https://cloud.google.com/functions/pricing
Metabase: Community tier free without hosting - https://metabase.com/enterprise/pricing.html
Total starting costs: $7.20 + $0.09 per month + hosting of Metabase