Replicating Shopify Canned Reports via API Data

[TX-Aggie]

We have recently brought the Shopify data into our datalake via the GraphQL API  and we are trying to replicate the canned reports available on the Shopify store webapp.  I have read some of the definitions of the metrics available on the Shopify Help Center.  That helps defined metrics like Gross Sales, Discounts, etc., but we can't seem to tie out to the reports.  For example, the "Sales by channel" report: what date does that use?  Fields we have tried are Order.CreatedAt/ProcessedAt/ClosedAt.

Is there any sort of mapping document available to replicate these types of reports with data retrieved via the API? 

Any help is appreciated,

Scott

[CloudlabSam]

Shopify reporting uses processed at

[TX-Aggie]

Thank you, but that was more an example of the type of mapping that I am looking for.  The data retrieved from the API is in GMT.  Are the reports using that timezone or converting to the offset time of the shop?

The help center defined gross sales as:

---

product price x quantity (before taxes, shipping, discounts, and returns) for a collection of sales. Canceled, pending, and unpaid orders are included. Test and deleted orders are not included.

---

What is product price: LineItem.originalUnitPriceSet.presentment.amount?

These details are pretty granular, but we have to be able to tie this data out as a baseline for our reporting.

Thanks,

Scott

[CloudlabSam]

That's the issue with trying to make a go of this yourself. Every Shopify attribute has a story and things are constantly changing. It took us the better part of 6 months to be about to match the Shopify reports given all of the possible scenarios. Even after that, maintaining based on changes is a full-time job.

[TX-Aggie]

I don't disagree with you, but there has to be some common definitions as it relates to the raw data.  We don't need to tie to every report but if the analytics dept can't come close to showing the same sales amount for a given time period, then there is a lack of trust with the data we are pulling in from Shopify.  They aren't trying to "copy" all the reports, but use the data in conjunction with ALL their other data (several shops, CRM, ERP, etc) so they can get a good holistic view of the business's operational reporting.  We are just trying to establish trust in both the integration to Shopify as well as the data we are pulling from Shopify.

[CloudlabSam]

I can assure you from experience that the data is all there and will balance with the Shopify reports, if used "properly". The path to that is far from a straight line.

Not trying to be evasive or rude, it's just not easy. And there really are no shortcuts. Just takes a lot of trail and error, research, etc.

My advice would be to start with detailed reports that show the numbers you need at an order level and get those to match. From there you can move up to higher levels of aggregation.

[TX-Aggie]

Thanks for your help!  Didn't perceive you as being evasive or rude, just honest, which I appreciate.  The timezone offset was the kicker.  Much closer now that we adjusted that.  Assuming Shopify reports uses the client browser TZ offset to adjust since the client and myself are off a bit (east coast vs west coast).

Thanks for your help.  It would still be useful to understand the metric/attribute mappings to API fields for easier integration.

Thanks,

Scott

[CloudlabSam]

You're welcome and I understand.

Regarding the timezone, I'm 99% sure that Shopify uses the timezone from the shop itself when displaying reports (see the shop object).

I think the hard part about doing a mapping is that things are very different between the REST and GraphQL APIs. since they seem to be supporting both for the foreseeable future, any mapping is twice the work.