Skip to main content

Peliqan

Try for free
Request a demo

Shopify

Analyze revenue by customer location

SQL query for Shopify

Get revenue data by customer location from Shopify tables.

SELECT customers.location, SUM(line_items.price * order_items.quantity) AS total_revenue FROM customers JOIN orders ON customers.id = orders.customer_id JOIN line_items ON orders.id = line_items.order_id JOIN order_items ON line_items.id = order_items.line_item_id GROUP BY customers.location

Try this query now in Shopify

Shopify data model

Shopify
Abandoned Checkouts

View 47 columns
IdInteger
NameString
NoteString
EmailString
PhoneString
TokenString
SourceString
GatewayString
User_idInteger
CurrencyString
CustomerObject
Closed_atDate-time
Device_idInteger
Tax_linesArray
Total_taxSinger.decimal
Cart_tokenString
Created_atDate-time
Line_itemsArray
Source_urlString
Updated_atDate-time
Location_idInteger
Source_nameString
Total_priceSinger.decimal
_sdc_shop_idInteger
Completed_atDate-time
Landing_siteString
Total_dutiesSinger.decimal
Total_weightInteger
_sdc_shop_nameString
Discount_codesArray
Referring_siteString
Shipping_linesArray
Subtotal_priceSinger.decimal
Taxes_includedBoolean
Billing_addressObject
Customer_localeString
Note_attributesArray
Total_discountsSinger.decimal
Shipping_addressObject
Source_identifierString
Sms_marketing_phoneString
Presentment_currencyString
Abandoned_checkout_urlString
Total_line_items_priceSinger.decimal
Buyer_accepts_marketingBoolean
_sdc_shop_myshopify_domainString
Buyer_accepts_sms_marketingBoolean
Shopify
Collects

View 10 columns
IdInteger
PositionInteger
Created_atDate-time
Product_idInteger
Sort_valueString
Updated_atDate-time
_sdc_shop_idInteger
Collection_idInteger
_sdc_shop_nameString
_sdc_shop_myshopify_domainString
Shopify
Custom Collections

View 14 columns
IdInteger
ImageObject
TitleString
HandleString
Body_htmlString
Sort_orderString
Updated_atString
_sdc_shop_idInteger
Published_atString
_sdc_shop_nameString
Published_scopeString
Template_suffixString
Admin_graphql_api_idString
_sdc_shop_myshopify_domainString
Shopify
Inventory Items

View 15 columns
IdInteger
SkuString
CostSinger.decimal
TrackedBoolean
Created_atDate-time
Updated_atDate-time
_sdc_shop_idInteger
_sdc_shop_nameString
Requires_shippingBoolean
Admin_graphql_api_idString
Country_code_of_originString
Harmonized_system_codeInteger
Province_code_of_originString
_sdc_shop_myshopify_domainString
Country_harmonized_system_codesArray
Shopify
Inventory Levels

View 8 columns
AvailableInteger
Updated_atDate-time
Location_idInteger
_sdc_shop_idInteger
_sdc_shop_nameString
Inventory_item_idInteger
Admin_graphql_api_idString
_sdc_shop_myshopify_domainString
Shopify
Locations

View 22 columns
IdInteger
ZipString
CityString
NameString
PhoneString
ActiveBoolean
LegacyBoolean
CountryString
Address1String
Address2String
ProvinceString
Created_atDate-time
Updated_atDate-time
_sdc_shop_idInteger
Country_codeString
Country_nameString
Province_codeString
_sdc_shop_nameString
Admin_graphql_api_idString
Localized_country_nameString
Localized_province_nameString
_sdc_shop_myshopify_domainString
Shopify
Metafields

View 15 columns
IdInteger
KeyString
TypeString
ValueInteger
Owner_idInteger
NamespaceString
Created_atDate-time
Updated_atDate-time
Value_typeString
DescriptionString
_sdc_shop_idInteger
_sdc_shop_nameString
Owner_resourceString
Admin_graphql_api_idString
_sdc_shop_myshopify_domainString
Shopify
Products

View 21 columns
IdInteger
TagsString
ImageObject
TitleString
HandleString
ImagesArray
StatusString
VendorString
OptionsArray
VariantsArray
Body_htmlString
Created_atDate-time
Updated_atDate-time
_sdc_shop_idInteger
Product_typeString
Published_atDate-time
_sdc_shop_nameString
Published_scopeString
Template_suffixString
Admin_graphql_api_idString
_sdc_shop_myshopify_domainString
Shopify
Transactions

View 25 columns
IdInteger
KindString
TestBoolean
AmountSinger.decimal
StatusString
GatewayString
MessageString
ReceiptObject
User_idInteger
CurrencyString
Order_idInteger
Device_idInteger
Parent_idInteger
Created_atString
Error_codeString
Location_idInteger
Source_nameString
_sdc_shop_idInteger
Processed_atString
AuthorizationString
_sdc_shop_nameString
Payment_detailsObject
Admin_graphql_api_idString
_sdc_shop_myshopify_domainString
Payments_refund_attributesObject

Ready to get instant access to all your company data ?​

Connect 300+ sources, serve any BI tool, and give every AI agent one governed endpoint to read, and write back where the app supports it.

  • 14-day free trial
  • No credit card needed