Create reports with the Data Studio connector for Shopify

[Michael_Whitak1]

I created a connector for Shopify that allows you to create sales reports in Data Studio. If you're not familiar with Data Studio, here is an overview. The connector comes with a good-looking template to get you started.

This connector is not yet published as I am hoping for some feedback. If you could check it out, I'd appreciate it. It should only take a couple of minutes to go from creating a private app to being able to pull and share live marketing reports.

Details on how to get started are here

Kind regards,

Michael

[Soldat]

Hey,

Thanks for the connector, I was wondering if it was possible for you to add some additional fields, mainly for revenue attribution and various other reports that are available in shopify, that we could then transfer to data studio?

Thanks!

[Michael_Whitak1]

Hi there,

Many thanks for your suggestion. At this time the connector only fetches data from the Order API. You can see the response object returned, so if there is a specific data point you're interested in, just let me know and I'll see if I can add it.

Kind regards,

Michael

[AmolAgnihotri]

Thanks Michael for sharing this. I have been using Daton's Shopify Connector to replicate data on client server. Its very easy to integrate and very cost effective too. 

[Adam_Hanin]

@Michael_Whitak1 Is this connector still being updated? Looks extremely helpful but I want to make sure you are still supporting this before I implement.

[EugeniuS]

I use it now and it’s a working solution.

[Michael_Whitak1]

Hi Adam,

The connector is used by quite a few Shopify stores and I am updating it
when necessary. The connector itself is free to use because the data from
your Shopify store is queried live and sent straight to Data Studio, so
there is no DB or servers for me to maintain (never mind the benefits to
your data privacy). The downside is that the connector will not be very
performant if you have lots of orders.

Michael

[Adam_Hanin]

Thanks for the update Michael, how many orders would be too many for the connector?

Would it only be an issue if data studio was pulling a large amount of data?

[Michael_Whitak1]

It's hard to say as it's dependent on the report timeframe and how many
widgets you have in the report. If your report timeframe includes 10k
orders that may be too much. I'd say the connector is ideal for reporting
weekly or daily data that you send out on a schedule.

[KevinW]

Hey @Michael_Whitak1, thanks for sharing this connector and template. I'm going to give it a try this week. I'm a big fan of Data Studio so I'll provide feedback once I've worked with it a bit. Can you help me understand how your connector solution compares to the Stitch integration for Shopify? 

[Michael_Whitak1]

Hi Kevin,

As far as I can tell, Stitch is a hosted app/solution that imports your Shopify data (and any other data source) to Stitch first. Once it's in Stitch you can connect to Data Studio. My community connector connects to Shopify directly, so no intermediaries involved or data stored with third parties. On the flip side, since the connector queries the Shopify API directly, it's not very performant if you have lots of orders and data. 

[KevinW]

Thanks @Michael_Whitak1. I successfully created the connector and can now visualize my data in DS. The instructions were easy to follow so nice work. And I appreciate the clarification on how your connector works. I totally understand. The only thing I couldn't figure out is where you put the link to dashboard template. Maybe I'm just overlooking it, but I don't see it anywhere on your site. I don't really need it per se, but I thought I'd point it out for the users' sake. Thanks again Michael. I'll report back if I find any bugs, otherwise I'd say great work! 

[Michael_Whitak1]

Thanks Kevin,

When you create a connector you have the option to ship it with a template to make it easy for folks to get started, but the link to template gets a bit hidden.

Here is the direct link https://datastudio.google.com/open/1xv-Ph9kNEPNt9a_59A0iDcksk5FJrr1v

[KevinW]

Thanks @Michael_Whitak1! I was able to access the report and populate it with my store's data. For some reason, the tables and charts were showing some weird data and duplicate rows, but they all refreshed and corrected themselves once I clicked into them. 

> but the link to template gets a bit hidden.

Can you share a screenshot showing where it is? I'm just curious. I swear I looked everywhere 🙂

Also, just FYI, I'd recommend updating the font size for the date picker from 18 > auto. For some dates the text runs off the element. 

Finally, I'd recommend updating the decimal precision for Revenue (column 1) in the bottom table from auto > 0. That way all the digits line up evenly. 

Thanks again for sharing this. How can we stay up to speed on what's going on at Analytics Buddy? Are you planning on building off of this? 

[SergeRouvinet]

Hi, thanks alot for the connector!

Is it possible to add discounts and returns to match the Net Total Sales from Shopify, as right now we can only get Gross Sales.

Thanks!

[Michael_Whitak1]

Hi there,

Great suggestions! I should be able to add discounts, but refunds are a little different. Refunds can happen at any time, potentially many days after an order is placed. So the refunds amount can change based on when you run a report. There is also no Shopify API that can return refunds in aggregate: you have to query each order to get the refunds, which is not scalable. I'll think about this so more.

[SergeRouvinet]

Hi Michael, glad to hear you can add discounts. It would be ideal to have refunds somehow as well, otherwise our data will never match the Shopify interface.

Apparently its not possible either to take the Sessions data either through APIs, do you know if it possible somehow?

Thanks for your work!

[SergeRouvinet]

Hi Michael, any idea when you can get discounts and refunds integrated?

thanks!

[Michael_Whitak1]

Hi there,

I added Net Sales, Tax, Shipping, Discounts, Open/Closed, Active/Cancelled Orders. Just refresh the fields in the connection.

I also added refunds to the connector. Note that refunds are attached to orders. For example, if you process a refund today for an order that was placed a few days ago, and you run a Data Studio report for today, the refund will not be listed in the report.

Let me know what you think!

Michael

[Enrique1]

Hi, sorry I didn't know where to contact you for a request.

I would like to create Vendor sales reports with your Data Studio connector, but I don't see any VENDOR field. Is that something you can add?

Thanks,

Enrique

[Michael_Whitak1]

Thanks Enrique,

I added Vendor to the connector.

Michael

[Enrique1]

Awesome, thanks! I'll probably will need a couple more variables, but I'm
not sure yet. I'm new to DataStudio.

One quick question. In layman's terms, should I worry about Shopify Api
calls limits?

Thanks,

Enrique

[Michael_Whitak1]

You can just leave the defaults and see if that works for you. The data goes straight and live from the Shopify API to Data Studio, so if you have 1000s of orders that have to be paginated through, it's possible that something will fail if the data gets too large or it takes too long. 

I can add fields if they are in the Order API response.

Good luck trying it out!

[SergeRouvinet]

Hi Michael, thanks alot for the updates! Where can I contact you directly as I am using your connector and seeing different numbers from my Shopify account.

Thanks!

[Michael_Whitak1]

You can reach me at info@analyticsbuddy.com - if you do see discrepancies please double-check that your timezone settings in the connector are the same as the timezone in your Shopify admin.

[J_221]

Hi Michael, thanks for the connector, do you think it is possible to modify the names of the products for a better visualization?

 Thanks!

[Michael_Whitak1]

Hi there,

Could you give me an example of what modifying names would look like? I'm trying to see if this is something that can be done in Data Studio itself (eg through calculated fields). You can also reach out to me at info@analyticsbuddy.com.

[CharlotteA]

Hi Michael

I understand you're the creator of the connector 🙂 

First thanks a lot for sharing it, it's a great start to get data out of Shopify. 

I do experience some issues though: since about 3m ago, all my time series charts do not display any data while the total metrics do. 

I tried to re-create them from scratch but it still doesn't work. 

Do you have any idea why?

[Michael_Whitak1]

Hi CharlotteA,

I tried the connector today and it was working fine for me. Would you
contact me directly at info@analyticsbuddy.com and I'll try to help.

Michael

[EugeniuS]

Hi, Michael_Whitak1

Tried today your connector and it seems it's working.

Can i ask you to add field of Product type to generate a report like Product Category (Enhanced Ecommerce) from GA?

Thank you!

[Michael_Whitak1]

Hi there,

The connector calls the Order API, so if the field Product Type is in the response I can add it. Can you confirm which field it is?

It may also be possible to use the Data Studio data blending feature. If the Product Type is not in the API response, you could add a second data source to Data Studio (say a Google Sheet) that has the Product SKU and the Product Type. Then you can blend the data from the connector to the Sheet based on the common SKU key. 

[EugeniuS]

Hi,

It seems OrderAPI, unfortunately doesn't contain Product Type, only Product API.  I can't use SKU, because i don't have it in my store. I'll try with product ID correlated to Product type.

Thx!

[EugeniuS]

Hello, Michael_Whitak1,
Today i observed a message:

Private apps need updating within 28 days
Your private apps may no longer work if they are not updated before April 1, 2020. This message is based on data from the last 30 days. Contact the developer of the following apps to update them before the deadline: Data Studio Connector

Can you help me to solve this issue?

[Michael_Whitak1]

Hi there,

I updated the app to use a newer API. Please let me know if you still get
that message in a few days.

Kind regards,
Michael

[fergmacdonald]

Thanks for building this. I had some discrepancies which took a little time to figure out so I'll lay out what I found to see if that helps anyone.

We have more than 1 store and are based in the UK so charge VAT.

Store A has product prices excluding tax and then tax is added on at checkout.

Store B has product prices including tax.

In Data Studio "Item Revenue" will be product prices excluding tax and shipping for store A and including tax for store B. Store B will match the 'sales' report in Shopify because the Shopify Gross Sales figure is excluding tax, but Store A will not.

You can create a 'Total Revenue' figure by creating a calculated field "Item Revenue+Shipping Cost-Total Discounts". You can create a Net Revenue figure by removing Refunds from that. However, as previous posts mention, refunds are based on the order date, not the date the refund is made. Shopify reports on the date the refund is made so the net revenue figure will not match Shopify reports.

Lastly, I'm getting an issue where time series (bar charts, line graphs) are not showing correctly. They're missing loads of data, while scorecard boxes are showing it OK.

[Michael_Whitak1]

Hi there,

Many thanks for taking the time to dig in. I don't know if there is
anything I can add, but just to let you know that I am actively updating
this free connector so if there are ideas, mistakes to fix and/or data
points that should be added, please let me know.

>From my perspective I have faced a couple of challenges:

1) Shopify is not consistent in their naming convention, so the fields in
the API do not correspond to how they name the fields in their reporting in
the admin. So we have "Total Sales" in the reporting, but "total_price" in
the API. That can be a source of inconsistencies.

2) Unfortunately Shopify does not have a "Refund API" that could be queried
by date. I can only display refunds that are attached to orders within the
report timeframe. Refunds are the biggest source of discrepancies between
the admin reports and Data Studio.

Cheers,
Michael

[fergmacdonald]

Hey, interesting, I don't have 'total sales' in my connection but I connected it a while ago. Perhaps it's been updated since then. Is there anything I'd need to do to refresh/update it?

[Michael_Whitak1]

No, it's not in there as it could indeed be calculated. According to
https://help.shopify.com/en/manual/reports-and-analytics/shopify-reports/report-types/sales-report
Total sales = Gross sales - discounts - returns + taxes + shipping charges

I can add it, but I left it out because of the potential for discrepancies
due to the returns issue.

[powerbackpack]

Hey Michael,

Fantastic application. We've been playing around with it and love it. Recently we had this error pop up:

Shopify API Connector requires your Shopify API Connector key or token in order to access your data. Do not enter your Google account information.

I think on your site the key is visible, but it's partially cut off: 

[emreisik]

Hey there, what is the key? Where can I find it?

[FergusMacdonald]

I think the key might be your example URL in the private app details that you set up in Shopify.

[emreisik]

Yup! That's it thank you! 👍🏻

[EugeniuS]

Hello,

it seems something is going wrong

How can i solve this issue?

Thanks!

[emreisik]

You must enter your example URL to there. 

[EugeniuS]

Sorry, didn't observed your previous post.

Solved with Example URL.

Thank you!

[Michael_Whitak1]

Sorry everybody about the change in authentication with the connector. In
order to publish the connector in the gallery, any credentials have to be
stored in user preferences and not in the config screen. Also, every
Shopify store has their own API endpoint (eg your-store.myshopify.com),
which Data Studio does not provide for, so I decided the easiest is to just
use the Example URL when you set up the private app because it contains
your Shopify hostname.

For privacy reasons I don't know the email addresses of the people using
the connector so I couldn't really give a heads up. Updated instructions
are here: https://www.analyticsbuddy.com/data-studio-connector-for-shopify/

Sorry again, but I hope the change will be easy.

Michael

[Jaydeeco]

Hi Michael. I gave this a go, but it doesn't seem to show any of our data properly - the numbers are wrong.

All I wanted to get were "Total number of orders", "Total quantities of items sold" and "NET Sales" for the day, but for some reason these numbers are way out.

I thought it might be a timezone issue and changed this to various other options (including the correct timezone) but this didn't really help (the numbers were always wrong no matter which timezone I chose).

I tried increasing the sleep variables etc and this didn't fix it, nor did changing the API version in the Shopify custom app settings.

Do you have any idea why it would only be showing some data from a given day instead of all of it? I tabulated the outputs and it only seems to show data from certain times of the day instead of the entire day.

Thanks

[Michael_Whitak1]

Hi Jaydeeco,

Even though some discrepancies are to be expected (notably due to refunds)
between what the connector shows and what you see in the Shopify admin
report, the data shouldn't be way off. Please reach out to me directly via
email and I'll try to help.

Best,
Michael

[Dan_Knowlson]

This is great Michael, thank you

I'd be interested in some customisations. Would you be up for that?

[Michael_Whitak1]

Hi Dan,

Sure I'd be up for that. Please reach out to me here https://www.analyticsbuddy.com/contact/

Best,

Michael