Querying Shopify Data in Postgres

Introduction to Shopify

Shopify is a SaaS company that simplifies the process of setting up and running an online business. Businesses that use Shopify have access to important information about their products, orders, customers, and more within Shopify. In most cases, businesses will want to extract this data and combine it with other data into a central database. In this article, we will be discussing how to migrate data from Shopify to Postgres.
​

Create the Postgres Database

You need to have a Postgres database in which you will store your Shopify data. You can create the database directly from Postgres, in which you will use the CREATE DATABASE database_name command. For example to give the database the name shopify, run the following command:

CREATE DATABASE shopify;

The command will create the database within seconds.

You can also use Heroku’s database-as-a-service platform to create a Postgres database instance. First, create a Heroku account and you will be presented with the “Create New App” screen. Follow the on-screen instructions to create the app.

Once the app has been created, click “Resources”. Search for “Postgres”

With Heroku’s free plan, you can’t fully extract enough rows, so choose “Hobby Basic ($9/month)”.

Open your dashboard by clicking Heroku Postgres in your addons list. Click the “Settings” tab and then choose “View Credentials”. You will be asked for those credentials later.
​

Migrate your Shopify Data to Postgres

Our goal is to migrate data about Shopify orders to the Postgres database. Now that our Postgres database is ready, we will use an ETL (Extract, Transform, Load) service to pull Shopify orders data into the database. The three ETL functions enable us to extract data from one database and load it into another.
​
The good thing is that there are many ETL tools that you can use. Examples include Stitchdata and Fivetran. The latter will be good for you as you scale, thus, we will use Stitchdata.
​
Sign up for a Stitchdata account. Click the “Destination” tab from the top and choose “Postgres”. Enter the credentials of your Postgres database. For screenshots of this part of the process, refer to our article on hooking up Stripe to Postgres.
​
Next, you should create an integration between Shopify and Stitchdata. Click the “Integrations” tab and choose “Shopify”. Enter the details of the integration, including the URL of the Shopify shop:

Stitchdata will then begin to extract your Shopify data and load it into Postgres. The process will take less than an hour.
​

Query your Shopify Data

As Stitchdata loads the data, you can take time to explore its structure. There are different SQL client tools that you can use for this,
​
Download the right SQL client tool and add your Heroku database to it. You can then use the SQL’s “SELECT *” statement to query the tables created in your database. For example, the following SQL query shows the orders issued by customers:

SELECT * FROM shopify.orders;

The statement will return the data added to the orders table of the shopify database.
​

Visualize the Data

There are different solutions to data visualizations. Arctype is free to use and has build-in dashboard tools to quickly build visualizations.
​
Arctype will ask you to establish a connection to your Heroku Postgres database. You can then start to pull data from your table and visualize it.
​
For example, let us create a bar chart that shows the number of items ordered by different clients.
​
First, let us write a SQL query to fetch order names and the corresponding number of items for each order:

SELECT name, items FROM orders;

The query will return a table similar to this:

To generate a bar chart from this data, click the “Chart” button next to the “Table” button. Next, click the “Select chart type” drop down button on the right and choose “Bar Chart”.

You will see a section written “DRAG COLUMNS HERE” at the bottom right corner of the Arctype screen.
​
Drag one of the columns to the x-axis and the other one to the y-axis. A bar chart will be generated as shown below:

To change the type of chart generated, click the drop-down button for “Select chart type” and select the type of chart you want.
​

Conclusion

The Shopify platform makes it easy for individuals to set up and run online businesses. When running a Shopify business, your store will generate a lot of data. You will want to extract this data and combine it with your other data into a central database to make meaningful decisions about your business. You can use an ETL tool such as Stitchdata to extract your Shopify data and load it to a database of choice such as Postgres. You can then use tools like Arctype to query the data and create visualizations from it.