Looker and Shopify data models
This document will walk through integrating Chord's Looker Vs Shopify differences in data models
For Performance clients using Shopify's OMS, data will vary slightly depending on the metric
What are data models?
Data models are tables that are created by our analytics engineering team in order to present data in a shape that is easy to consume for analytics purposes. Orders facts, product dimensions, and user dimensions are all examples of potential entities (or tables) in a data model.
Data and Time
The default timezone in Looker for OMS timestamps is UTC, whereas the OMS UI displays EST.
Within Looker, we've added additional timezone options for some of our timestamps but not for Order Approved Date. Custom time zones can be created with a custom field and can be submitted as a feature request
Chord currently provides both completed and created timestamps.Short-term: Use Explore to change the date from completed to created date.
PST (-8 hours)Note: Shopify natively tracks by UTC (why raw data and Looker are in UTC), this UI timezone is configurable by Shopify account
UTC is considered a more standardized way to report time and most tools will default to UTC. In addition, UTC makes reporting more apples to apples across time zones and countries. Timezone choice is up to the business and can usually be customized in most tools like Shopify.Longer-term: Chord is planning to provide more flexibility for preferred time zones in data modeling.
Orders and Revenue
Completed orders only.
Includes any order placed regardless of fulfillment status or cancellation
Use the “Orders” Explore to view all orders regardless of fulfillment status or cancellation.Short-term: Chord to consider providing a view or filter for the main dashboards to show completed vs. all orders in process.
Included in gross revenue, net revenue, and orders.
Includes product only and not gift cards.
Use “Line Items” Explore to filter for “Product Type = Gift Card”.
Does not include partial refunds. Full refunds would be included since the order is effectively canceled.
Includes any return made in the period regardless of order date.
Chord to provide a view of returns on both order and timestamp level.
Excludes fulfillment revenue from net revenue while included in gross revenue.
Includes fulfillment revenue.
Use the “Orders” Explore to add back fulfillment revenue to net revenue.Short-term: Chord to consider adding fulfillment revenue to net revenue, especially since it’s included in gross revenue.
Main dashboard shows net AOV.
Main UI shows gross AOV calculated with order conditions above (all gross revenue regardless of fulfillment or cancellation status, excludes gift cards)
Use “Orders” Explore to see gross AOV.Short-term: Chord to provide a view of both gross and net AOV on the main das