Client:
The client is a California based Distribution, Records & Publishing company and has many Shopify Stores which they operate.
Challenges:
The client wanted us to build a dashboard for each of the Shopify Store and a final Master Dashboard to see the combined data for all the Stores at one single dashboard. They wanted to see the Sales & Refund information in a Tabular format in a manner that they could see Sales and Refund(if any) for a particular order next to each other.
There were two main challenges for us, they are described below:
- The major challenge for us was to get data from Shopify into Klipfolio since there was a huge amount of data in the client’s Shopify Stores.
- The second one was to organize them in the required Tabular format where Sales and Refund(if any) can be seen next to each other.
Solution:
Resolution of the First Challenge:
Instead of fetching the data via API, we used a third-party application built for Shopify to export the daily Sales and Refund data from Shopify to Google Sheets. These exports were scheduled to run daily so that at the end of the day we can get sales & refund details for that day in our respective Google Sheet for Daily Sales & Daily Refunds. These Google Sheets were connected to Klipfolio via Klipfolio Standard Google Drive Connector.
Other than this we set up a couple of other files that contain the data for Historical Sales & Historical Refunds prior to the date when we setup the daily Sales & Refunds reports in Shopify. Historical data is just the data for the past dates downloaded directly from Shopify and stored in CSV Files.
Finally, using the Historical Sales, Historical Refunds, Daily Sales & Daily Refunds report we have developed the required chart.
Resolution of the Second challenge:
Let say, a user ordered 3 products/items at a time but they returned two of the products later. For any returned product(s) a full/partial refund is generated. Since a refund is usually made later to the order date so the record for the refund order will be generated on a different date w.r.t the order sales date.
There are a couple of fields in Shopify which can be used to track any order in any stage(sales, refund, etc.). Though the Sales and Refund(if any) have different dates, the order id/name will still be the same. So, we have combined the data of Sales & Refund in a Table and sorted the whole table by the order name column to show the sale in one row & refund(if any) in the following line for the same order.
Result:
So by following the above-mentioned approach, we were able to fetch data from Shopify Stores to the client’s Klipfolio instance and we were able to deliver the Shopify Dashboards as per the client’s requirement.