Advanced Analytics and Reporting for Shopify

A header showing the Shopify logo next to a data warehouse icon

You’ve perfected your Shopify store, you’ve put together a strategic marketing mix, and you’ve got a steady stream of sales coming in. You’re on the road to retail genius—and for good reason.

But if all that success also means that you find yourself:

  • spending hours on your regular reporting each week…
  • cobbling together data from all over the company via a brittle chain of spreadsheets…
  • or just plain frustrated by a lack of visibility around your customers, orders, or inventory…

You are not alone. For many Shopify merchants, growth isn’t just a sign of success, it’s a problem to manage. But it shouldn’t be. By harnessing your Shopify data, you can make better business decisions more easily—and you don’t need an entire analytics team or an expensive consulting firm to do it.

In this guide, you’ll learn:

Why You Need a Shopify Data Stack

Getting the most value out of Shopify analytics requires more than just looking at their canned reports. Instead, you’ll need to build a data stack that allows you to gather, combine, and analyze all the data your business generates.

Why aren’t built-in Shopify reports good enough?

Shopify dashboards are great when you’re starting out, but they can only take you so far. For example, Shopify’s built-in reporting won’t let you answer a question like, “Which of my customers are the most valuable?”

If you’ve ever taken advantage of a $5-off coupon for a $6 item only to never return to that store again, you can see the importance of this kind of information. By identifying your most valuable customers, you can target your marketing and promotional efforts in a way that will encourage them to spend even more with you without subsidizing deal seekers who’ll only buy when your margins are at their thinnest.

Plus, Shopify’s standard reporting doesn’t give you a competitive edge. Those prepackaged dashboards are available to thousands of other Shopify merchants, which means they’re all basing their strategy around the same stock insights.

Can’t I just hook Shopify to a BI tool like Tableau?

Just because you can doesn’t mean you should. The problem with connecting Shopify directly to your business intelligence tool is that while it might give you prettier visuals than the native Shopify reporting, it doesn’t actually offer more insight.

That’s because you’d still be looking at Shopify data in isolation rather than taking advantage of multiple data sources to really understand your customers, sales, and inventory. Adam Beltz, Senior Manager of Information Architecture at Matilda Jane, a Shopify merchant that creates whimsical girls clothing, explains:

“Before Panoply, we had spreadsheets on spreadsheets and random transaction reporting tools all cobbled together.”

By using a data stack, you can sync all your data—including sources like Google Analytics, Facebook Ads, Zendesk, and QuickBooks—for a much more cohesive and meaningful view of your business.

What should a Shopify data stack look like?

To level up your Shopify analytics, you need three things:

  • A way to sync your data (aka, a data pipeline or ETL tool)
  • A place to store your data (aka, a data warehouse)
  • A way to access and analyze your data (aka, your favorite BI tool or analytical notebook)

A schematic or workflow moving from left to right. On the left are the logos of popular data sources like Shopify, Google Analytics, Intercom, and Salesforce. A dotted line represents the data moving through a data warehouse in the center and then into a BI or analytics tool on the left.

This visual simplifies the process just a bit. In reality, those pipelines wouldn’t just connect your data sources to the rest of your stack. They’d apply built-in logic that transforms raw data into analysis-ready tables so you could find insights more quickly and easily.

Sound complicated? It’s not. Bob Vermuelen, Director of CRM at Shinesty, a Shopify merchant known for its fun and quirky fashion, says,

“Panoply has done everything to make it easy to spawn a warehouse, import data, and get to work. The ease of use with Panoply is outstanding… The fact that Panoply just works is huge for me and us as a company.”

Syncing Your Shopify Data to Panoply

Setting up a data stack might sound intimidating, but it’s not…if you use the right tools. All you need to set up a data warehouse in Panoply and start syncing your Shopify data is your Shopify login and domain name. Take a look:

Want a step-by-step walkthrough? Check out our handy guide on connecting Shopify to Panoply.

How to Create a Shopify Sales Over Time Report

Once your data is in Panoply, a whole new world of insights is at your fingertips. Whether you’re already familiar with or have been longing for Shopify’s Sales Over Time report, it’s incredibly helpful for gauging the health of your sales as well as identifying seasonal trends.

A screenshot of Shopify’s Sales Over Time report showing a merchant’s monthly earnings.

To get a taste of this report in Panoply, just head to the workbench and paste in this SQL query:

    /* BASIC SHOPIFY GROSS SALES BY MONTH */
    SELECT date_trunc('month', o.created_at) AS "month",
    	count(o.id) AS total_orders,
    	sum(o.total_line_items_price) AS gross_sales,
    	sum(-o.total_discounts) AS discounts,
    	sum(o.total_tax) AS total_tax
    FROM shopify_orders o
    GROUP BY 1
    ORDER BY 1;

This example query will return three key metrics:

  • total_orders = the count of orders
  • gross_sales = sum of gross sales
  • discounts = sum of discounts
# month total_orders gross_sales discounts
1 2020-07-01T00:00:00.000Z 45 28053.01 -2213.76
2 2020-08-01T00:00:00.000Z 42 25687.44 -2532.0
3 2020-09-01T00:00:00.000Z 28 15096.56 -1047.0

In this example, the GROUP BY function lets us see a rollup by month, but you can adjust that to show daily, weekly, quarterly, or even yearly data.

Syncing Shopify data to Panoply is seamless, but there’s a lot going on behind the scenes. To really understand what’s happening in these queries, get a deep dive into how data is structured in Shopify and Panoply.

Now that you have a sense of how queries work in Panoply, let’s replicate the complete Sales Over Time report. To calculate the metrics the way Shopify does, you’ll need to join a few more tables into the query.

To get started, copy this SQL query and paste it into your workbench:

    /* SHOPIFY SALES REPORT BY MONTH */
    SELECT date_trunc('month', o.created_at) AS "month",
    	/* Standard metrics */
    	count(o.id) AS total_orders,
    	sum(o.total_line_items_price) AS gross_sales,
    	sum(-o.total_discounts) AS discounts,
    	sum(-nvl(r.refunds,0)) AS "returns",
    	gross_sales + discounts + "returns" AS net_sales,
    	sum(o.total_tax) - sum(nvl(r.tax_returned,0)) AS taxes,
    	sum(nvl(sl.shipping_charge,0)) AS shipping_cost,
    	gross_sales + discounts + "returns" + taxes + shipping_cost AS total_sales,
    	/* Additional metrics */
    	sum(nvl(li.number_of_items_sold,0)) - sum(nvl(r.number_of_items_returned,0)) AS net_quantity,
    	round(net_quantity::FLOAT / total_orders, 2) AS units_per_transaction,
    	round(sum(o.total_price) / count(o.id), 2) AS average_order_value
    FROM shopify_orders o
    LEFT JOIN (
    	/* LINE ITEMS TABLE */
    	SELECT li.shopify_orders_id,
    		sum(lipsm.amount * li.quantity) gross_price,
    		count(li.id) AS number_of_items_sold,
    		sum(li.total_discount) AS line_item_discount
    	FROM shopify_orders_line_items li
    	LEFT JOIN shopify_orders_line_items_price_set lips ON li.id = lips.shopify_orders_line_items_id
    	LEFT JOIN shopify_orders_line_items_price_set_shop_money lipsm ON lips.id = lipsm.shopify_orders_line_items_price_set_id
    	GROUP BY 1
    	) li ON o.id = li.shopify_orders_id
    LEFT JOIN (
    	/* REFUNDS TABLE */
    	SELECT r.shopify_orders_id,
    		count(r.id) number_of_items_returned,
    		sum(rli.subtotal) AS refunds,
    		sum(rli.total_tax) tax_returned
    	FROM shopify_orders_refunds r
    	LEFT JOIN shopify_orders_refunds_refund_line_items rli ON r.id = rli.shopify_orders_refunds_id
    	GROUP BY 1
    	) r ON o.id = r.shopify_orders_id
    LEFT JOIN (
    	/* SHIPPING LINES TABLE */
    	SELECT sl.shopify_orders_id,
    		sum(sl.price) shipping_charge,
    		sum(sl.discounted_price) shipping_discount
    	FROM shopify_orders_shipping_lines sl
    	GROUP BY 1
    	) sl ON o.id = sl.shopify_orders_id
    GROUP BY 1
    ORDER BY 1;

This query pulls information from several tables to return a whole lot more data, including details like returns, net sales, and shipping. To keep it simple, let’s take a look at one row from that table to get a sense of what you’d see:

# month total_orders gross_sales discounts returns net_sales taxes shipping_cost total_sales net_quantity units_per_transaction average_order_value
1 2020-09-01T00:00:00.000Z 28 15096.56 -1047.5 -700 13636.6 514.26 186.2 14337.06 51 1.82 539.16

Exploring this report can give you key insights into your business, from your average order size to how many returns you receive each month.

Ready to try it yourself? Get started now with a 14-day free trial of Panoply. Setup only takes a few minutes and once your data is synced, you can pull your own Sales Over Time report right in Panoply!

How to Create an RFM Report for Shopify Data

While every customer is important, if we’re being really honest, some customers are more valuable to your business than others. One way to identify those key customers is by running a Recency, Frequency, and Monetary Value report.

This report relies on three key metrics:

  • Recency, or when a customer’s last purchase took place.
  • Frequency, or how often a customer has purchased from you.
  • Monetary Value, or how much a customer spends within a particular time frame.

Together, these metrics reveal who your repeat purchasers are, which customers are the most loyal over time, and which customers spend the most in your shop. An RFM report provides critical information about your customers, but it’s not available through Shopify’s built-in analytics. Thankfully, though, it’s not hard to create in Panoply.

Wishing you could check out the data in your favorite analytical notebook or BI tool? No problem! Just connect a tool to Panoply, then copy and paste the SQL into the query window and you’re set.

To create an RFM report, you need just three key pieces of data about your customers’ purchase behavior during a particular time period:

  • Recency = most recent order date
  • Frequency = count of total orders
  • Monetary value = subtotal price

Why focus on a particular time period? Think about it this way: You may have a customer who bought from you multiple times and spent a great deal of money, which translates to high frequency and monetary value. However, if that customer’s most recent purchase was in 2016, they may not be a great target for an upcoming promotion.

To get that information, just sync your Shopify data to Panoply and enter the following query into the workbench:

    /* RFM VALUES */
    SELECT o.email, -- CUSTOMER
    	max(o.created_at) AS recent_order, 	-- RECENCY 
    	count(o.id) AS total_orders, 	-- FREQUENCY
    	sum(o.total_line_items_price) AS gross_sales, -- MONETARY (AS GROSS SALES)
    	sum(o.total_line_items_price)
    	+ sum(-nvl(o.total_discounts,0))
    	+ sum(-nvl(r.refunds,0))
    	+ (sum(nvl(o.total_tax,0)) - sum(nvl(r.tax_returned,0)))
    	+ sum(nvl(sl.shipping_charge,0)) AS total_sales -- MONETARY (AS TOTAL SALES)
    FROM shopify_orders o
    LEFT JOIN (
    	/* REFUNDS TABLE */
    	SELECT r.shopify_orders_id,
    		count(r.id) number_of_items_returned,
    		sum(rli.subtotal) AS refunds,
    		sum(rli.total_tax) tax_returned
    	FROM shopify_orders_refunds r
    	LEFT JOIN shopify_orders_refunds_refund_line_items rli ON r.id = rli.shopify_orders_refunds_id
    	GROUP BY 1
    	) r ON o.id = r.shopify_orders_id
    LEFT JOIN (
    	/* SHIPPING LINES TABLE */
    	SELECT sl.shopify_orders_id,
    		sum(sl.price) shipping_charge,
    		sum(sl.discounted_price) shipping_discount
    	FROM shopify_orders_shipping_lines sl
    	GROUP BY 1
    	) sl ON o.id = sl.shopify_orders_id
    GROUP BY 1
    ORDER BY 1;

The query above provides the values you’ll need to carry out an RFM analysis. The resulting data will look like this:

# email recent_order total_orders gross_sales total_sales
1 sarah@test.com 2020-01-22T19:57:59.000Z 2 3030 2524.4
2 eli@user.com 2020-09-01T19:57:59.000Z 1 1250 1293.35

The table above tells us that while Sarah has greater frequency (2 orders) and monetary value ($3030 in gross sales; $2524.40 net sales), Eli may actually be a better target for an end-of-year promotion because he’s purchased more recently.

To get our deep dive on how to leverage RFM data to supercharge your marketing efforts, .