We recently worked with Aron Clymer, CEO of the data consulting firm Data Clymer to show you how you can consolidate all of your analyze your Shopify ecommerce data from any number of sources into a data warehouse and display critical business KPI’s on a dashboard to provide visibility into every aspect of ecommerce operations. Sit back and enjoy. When you are ready to build your own ecommerce analytics stack, get started with your free trial.
Ecommerce Dashboard Tutorial
Hello, we're here today to show you how to set up a data stack for eCommerce retail on your own in minutes, using Panoply. My name's Aron Clymer, and I'm the CEO of a consulting firm called Data Clymer. We have a ton of experience helping organizations democratize data and instill a data-driven culture, and Panoply is one of our favorite cloud data warehouses to use. A Panoply instance can be spun up in minutes, and with over 150 connectors, it's easy to get all your data centralized and ready for analysis.
Let me show you just how easy it is. Today, I'll walk you through the process for an eCommerce retailer. As a retailer, you often will want to analyze sales, purchase orders, web traffic marketing campaigns, sales goals, etc. The very first thing you'll want to do is spin up an instance of the Panoply data warehouse and connect your data sources. To get started, you can create a free trial of Panoply which gives you 14 days of unlimited access.
Importing Ecommerce Data From Shopify
I already have an instance going here, so I will dive in and show you the very first step, which is to create some data sources. I'm going to click on the Data Sources tab on the left hand side once I'm in Panoply, and here I'm going to connect to my eCommerce data source, which in this case is Shopify. I can simply search for the source, select it, and for Shopify I just have to enter my shop name and connect.
Now I'm connected to my shop, and the only other thing I have to do is inform Panoply which objects I'd like to bring into my data warehouse. In this case, I'd like all of them, just have to click the Collect button, and voilà, the data has begun to stream into the Panoply data warehouse.
While we wait for that, another common type of data that a retailer might want to bring into their analytics system is goals data, so they can compare actuals to goal and adjust strategy as necessary. These kinds of goals are often maintained in spreadsheets. Luckily, Panoply has an easy connector to Google that we can use to ingest all sorts of spreadsheet data, whether it's in Excel, CSV, or Google Sheets directly.
So let me show you how to do that. I do have a Google Sheet here with monthly targets of user acquisition goals. I'm going to connect to that with Panoply by ... Again, I'm going to click Add Data Source to add another data source. I will search for Google Sheets in this case, click on that. And now I can click the Connect button, I just have to choose the appropriate Google account, and complete the authentication.
Now that's done, I can click the Next button, and Panoply will go out and detect all of the files in that Google Drive, all the Google Sheets in this case in that Google Drive. In this case, we only have that one, so I'm going to collect that. And again, Panoply will start to sync that data momentarily.
So, the final step with these connectors is you'll wanna schedule when the data is going to be synced. Now, with Shopify, go down here, you just click on the Schedule Data Source icon, and I'm going to have Shopify sync every hour, because I do want that data to come in fairly rapidly. However, with goals data, that's not changing within the day, so I think it'd be fine to just sync that on a daily basis. So I'll select Every Day, and keep the 1 AM default. Click accept.
Now, as you can see, we've completed the setup of two different data sources, and you can continue along that path, connect as many data sources as you want.
Now let me show you what that looks like in Panoply. You can see, by clicking on the Tables tab, all the tables that are created from our data sources. For instance, here's our goals, I'm going to click on that. I can show you the details of that data, Panoply will give you a preview of the first 10 rows of data and some metadata about the table. We can also look at orders to see some of our order data coming into Panoply as well.
Okay, so that's basically it, we've got all our data in Panoply, and the final step is to connect a business intelligence tool to Panoply for easy analysis, dashboarding, and sharing across your organization. In this case, I've connected Looker to the Panoply data warehouse and built some dashboards for the team.
KPI’s for an Executive Summary Dashboard
Let's start with an executive summary, which really makes your data come alive with easy-to-interpret visualizations. The first tile on the left hand side, for instance, shows a metric, New Users Acquired, and here we can compare it against one of the goals that we uploaded in that spreadsheet, to do a 38% month-to-date comparison. And then we've got other KPIs across the top, and really nice visualizations down below like a sales trend year by year, demographic location information for our users, users' behavior by source, some cohort analysis, and so on.
We can continue to build this out, so for instance say we wanted to add another visualization. I would open up Looker's Explore menu, which is a view into a specific data set, and I wanna build out a visualization that'll show traffic by gender. I know I have a gender dimension in here, let me find that. And we also have a traffic source dimension. I'm going to pivot on that, because I wanna create a matrix solution, and then I'm going to get a metric of item count so we have an order item count metric here.
Click Run, that'll give me the data I want, Looker defaults to a bar chart, but I wanna do some donut multiples to easily show this. There we go, I think I'll choose a different color palette that's a little more pleasing. And there we have it, I've got a new visualization.
Now all I need to do is add it to the dashboard. There's a Save to Dashboard option on the upper righthand corner in the menu. Click on the dashboard I wanna save it to, and voilà. Looker'll then actually give us a link back to the dashboard. I already have it open here, I'm just gonna refresh, and we'll see the new visualization down below. And of course you can just continue to do this until your dashboard is complete.
Click Edit there to be able to resize and move my tile, here it is at the bottom. I don't want it at the bottom, I want it up here a little higher, and I wanna resize it and make it the same size as these other tiles. So I can drag this there, and then I'm going to edit this and create a new title, because that title was generic. We'll call it Order Items by Traffic Source. Save that, and I've got a brand new tile on my dashboard that gives us the gender breakdown by source.
Building a Customer Dashboard
And finally, an obvious example of a dashboard you'd wanna build is a customer dashboard. It gives you all the information about the individual customer, showing overall user information, lifetime spend, favorite categories purchased, and so on. On top of all this, these dashboards will run 10 times faster thanks to Panoply's automated query optimization engine.
So, now you have all your data centralized in one place that allows you to do easy analytics and create actionable insights for your retail business. I hope you've found this tutorial helpful in understanding how easy it is to set up a comprehensive analytic stack with Panoply in no time.
You can start your free 14 day trial by signing up on panoply.io. You can also sign up for a customized demo on their website, and of course you can reach out to me, Aron, at aron@dataclymer.com. Thank you.
