Part 1 of 3 on the most important tables in ecommerce
What is Order data is part 1 of a 3-part series on the most commonly used tables in ecommerce: Orders, Products, and Customers. We will be using Shopify’s data API as examples. Since most of the data platforms have similar structures, the analysis should apply to other platforms as well.
My friends often ask why I chose Shopify to build a SaaS business, and what’s the difference between Shopify and Amazon? The answer is simple: the availability of data owned by the Merchants.
Amazon is an aggregator (for more on this, check out Aggregation Theory by Ben Thompson). While it offers tremendous access to high intent online shoppers with AmazonPrime and potential sales, it monopolizes the customer relationship.
Shopify is the complete opposite. On the surface, it’s more like a simple website builder. On the backend, it’s like a Cloud-based Point-Of-Sales system that simplifies billing, ordering, inventory, and hosting. It allows anyone to start selling online in 30 mins, that’s the power of Shopify.
Aggregation Theory — source (https://stratechery.com/2015/aggregation-theory/)
It’s selling a dream — that anyone can become a millionaire ecommerce entrepreneur, the great equalizer of opportunities.
Shopify invested in making data simple for merchants. They help merchants track, store, and maintain all the data from their stores. Their infrastructure is not only a tremendous benefit and cost savings for the merchants; it’s also the perfect playground for data scientists.
At Segments, we want to make data science accessible. We want to give the same data science insights enjoyed by the tech giants and big brands to the millions of SMB digitally native brands on platforms like Shopify.
This is why we do what we do.
Far too many people don’t know what’s possible and don’t care to find out. I aim to change this by offering simple explanations so people can know what’s available, understand what’s possible, and build a profitable business.
What is the order table
Everything starts with the order table. Buried beneath the rows of transaction logs are tiny nuggets of data science gold to be mined for understanding customer behavior.
Think of the order data as a row in excel, containing data about the order, the customer, and the product. Most of us have seen data like this before. It’s called receipts.
It says John bought a cup of coffee for $3.23 at a particular location with a credit card on a specific date. The order table may contain a variety of information from simple to complex. However, it should always include the key pieces of information: who’s the customer, what products did they buy, and detailed information about the order itself.
If you are using Shopify, have you ever wondered what did Shopify collects on your behalf about the customers’ orders?
What’s in the order table
As mentioned earlier, the order table contains information about who’s the customer, what products did they buy, and order details.
Let’s keep our focus on the order details.
First, we have time: These are order open and close dates. Not only do they help you with book-keeping, but you can also aggregate orders across days, weeks, months, or years to spot trends, and review performance.
Next comes the payment information: Price, Discount, and Checkout.
There are two types of prices tracked — total price and subtotal price. The subtotal price is the list price after discounts without shipping and taxes, and the total price includes shipping and taxes. It is usually more accurate to use subtotal price to calculate your KPIs & metrics, so shipping and taxes don’t skew your results.
Discounts are promotions, and there are a couple of things captured here, the discount code, the type of discount, the level of the discount, and whether it applies to the product or shipping. Promotions work hand in hand with prices to help get customers in the door. The extra level of details captured here can help you assess what types of promotion at what levels work best for different kinds of customers and products.
Checkout captures how the customer chose to pay, whether they paid through online checkout, Paypal, or other e-pay services. Usually, the more ways people can pay, the better it is for conversion. Having details about the customers’ payment information can help you segment your customer base to look at whether there are differences in their Avg. Order Value or customer lifetime value.
Finally, we have website traffic information.
We start with the source. The source field tells you where the orders are coming from. Did they order using a web browser, a smartphone (iPhone vs. Android), or was it a manually created by you.
example google analytics page
Then we have the landing site, which tracks the first page they see when customers visit your site. When the customer types in your URL directly, or look it up from Google search, they usually land on the home page. However, if they clicked through an email or an ad, then they might land on a page of your choosing.
Lastly, the referring site captures information about your referral traffic. Referral traffic is web traffic coming from your social media engagements, media coverage, or partner channels. For example, someone clicks on your Facebook ads, Instagram influencers’ posts, or from your email newsletters are all counting towards your referral traffic. Have you noticed extra codes at the end of the URL that you are clicking on? That gibberish contains information that Google Analytics is using to show you where your traffic is coming from.
You can see that Shopify captures quite a bit of order information for stores. Next, we will look at some examples of why you should care.
What can Order data do for me
When you aggregate multiple purchases from each customer, you can look at the timing between orders to find out the average days it takes between the first and second order. Once aggregated, we can check for seasonality and holiday patterns so we can better prepare for those big holiday spikes and avoid costly stockouts.
As for KPIs, average order value (AOV), average revenue per user (ARPU), and repurchase rate are a few examples of what you can build with the order table.
Segments Executive Summary (https://segments.tresl.co/)
Merchants often want to know whether it’s better to run a $10 dollar-off promotion vs. a 10% off promotion. How about finding out if it’s more profitable to offer free shipping instead of 25% off?
We can study historical promotions to help find answers to these questions.
Once you know how customers visit your site and how they choose to pay, you can segment your customers to find profitable channels and prioritize acquisition efforts. These are all information you can use to optimize your operations and grow sales and revenue.
In summary, we talked about what information is contained in the order table, what do they mean and what analyses are possible. It’s important to know what information is available to you so you can take full advantage of them and formulate your strategy. Study what’s available, understand what’s possible, and build a profitable business through data-driven decision making.
Finally, data is power, and we want to give ecommerce data powers back to the people. So join us and start growing with your data today!
Ecom Data Talk Video
We’re the founders behind Segments, the best customer analytics app for your Shopify store. Our mission is to democratize data science access to the millions of online stores and give ecommerce back to the people. We'd love for you to check us out to see how we can help you optimize your store and grow sales!