Table: shopify_customer - Query Shopify Customers using SQL
Shopify is a commerce platform that allows anyone to set up an online store and sell their products. It is also used by merchants to sell products in person with Shopify POS. Shopify Customers are the group of people who have made an account or purchased from the Shopify store, their data includes personal information, purchase history, and interaction with the store.
Table Usage Guide
The shopify_customer
table provides insights into customers within Shopify. As a store owner or a sales analyst, explore customer-specific details through this table, including personal information, purchase history, and interaction with the store. Utilize it to uncover information about customers, such as their preferences, buying behavior, and interaction with the store.
Examples
Basic info
Explore customer details to understand their contact information, which can be useful for customer service or marketing purposes. This query can help identify key customer data, enhancing customer relationships and driving personalized engagement strategies.
select id, email, first_name, last_name, phonefrom shopify_customer;
select id, email, first_name, last_name, phonefrom shopify_customer;
Get the total number of orders placed and the total amount spent by a customer
Explore the purchasing behavior of a specific customer by determining the total number of purchases made and the overall amount spent. This can aid in understanding customer loyalty and spending habits, which is crucial for targeted marketing and sales strategies.
select id, email, first_name, last_name, phone, orders_count, total_spentfrom shopify_customerwhere email = 'russel.winfield@example.com';
select id, email, first_name, last_name, phone, orders_count, total_spentfrom shopify_customerwhere email = 'russel.winfield@example.com';
Get the latest order details of a customer
Explore the most recent order made by a specific customer to gain insights into their purchase history and preferences. This can aid in personalizing their shopping experience and improving customer retention strategies.
select id, email, first_name, last_name, last_order_id, last_order_namefrom shopify_customerwhere email = 'russel.winfield@example.com';
select id, email, first_name, last_name, last_order_id, last_order_namefrom shopify_customerwhere email = 'russel.winfield@example.com';
Get the details of the customer who spent the most
Discover the customer who has made the highest total purchases. This can be useful for identifying your most valuable customer for targeted marketing or reward programs.
select id, email, phone, first_name, last_name, total_spentfrom shopify_customerwhere total_spent = ( select max(total_spent) from shopify_customer );
select id, email, phone, first_name, last_name, total_spentfrom shopify_customerwhere total_spent = ( select max(total_spent) from shopify_customer );
Get the default address of a customer
Discover the default address linked to a specific customer's email. This is useful for verifying the primary location tied to a customer's account.
select id, email, jsonb_pretty(default_address) as default_addressfrom shopify_customerwhere email = 'russel.winfield@example.com';
select id, email, default_addressfrom shopify_customerwhere email = 'russel.winfield@example.com';
Get all the addresses for a customer
Discover the segments that contain all the addresses associated with a specific customer's email. This can be useful for businesses looking to understand customer location distribution or for customer service purposes.
select id, email, jsonb_pretty(addresses) as addressesfrom shopify_customerwhere email = 'russel.winfield@example.com';
select id, email, addressesfrom shopify_customerwhere email = 'russel.winfield@example.com';
List customers with unverified emails
Pinpoint the specific customers who have yet to verify their email addresses. This is beneficial for sending reminders or follow-ups to increase the rate of verified customers.
select id, email, phone, verified_emailfrom shopify_customerwhere not verified_email;
select id, email, phone, verified_emailfrom shopify_customerwhere verified_email = 0;
List customers who opted-in for email marketing.
Explore which customers have chosen to receive email marketing, allowing for targeted communication and promotional strategies. This can aid in understanding customer preferences and enhancing marketing efforts.
select id, email, phone, first_name, last_name, accepts_marketingfrom shopify_customerwhere accepts_marketing;
select id, email, phone, first_name, last_name, accepts_marketingfrom shopify_customerwhere accepts_marketing;
List tax exempted customers
Discover the segments of customers who have been granted tax exemption. This can be useful for understanding the demographics of customers who may be more likely to make purchases due to their tax exempt status.
select id, email, first_name, last_name, phone, tax_exemptfrom shopify_customerwhere tax_exempt;
select id, email, first_name, last_name, phone, tax_exemptfrom shopify_customerwhere tax_exempt = 1;
List disabled customers
Explore which customers have been disabled in your Shopify store. This can be useful for understanding customer engagement, identifying potential issues or errors, and maintaining accurate customer records.
select id, email, first_name, last_name, phone, statefrom shopify_customerwhere state = 'disabled';
select id, email, first_name, last_name, phone, statefrom shopify_customerwhere state = 'disabled';
List customers created within the last 30 days
Discover the segments that have newly joined your customer base in the past month. This can help in tailoring new marketing strategies or promotional offers to engage them effectively.
select id, email, first_name, last_name, phone, created_atfrom shopify_customerwhere created_at >= now() - interval '30' dayorder by created_at;
select id, email, first_name, last_name, phone, created_atfrom shopify_customerwhere created_at >= datetime('now', '-30 day')order by created_at;
Schema for shopify_customer
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
accepts_marketing | boolean | Whether the customer subscribed to email marketing campaign. | |
addresses | jsonb | Other addresses of the customer. | |
created_at | timestamp with time zone | Time when the customer created their account. | |
default_address | jsonb | Customer default address. | |
text | The Email ID of the customer. | ||
first_name | text | First name of the customer. | |
id | bigint | = | The customer ID. |
last_name | text | Last name of the customer. | |
last_order_id | bigint | The last order ID of the customer. | |
last_order_name | text | The last order name of the customer. | |
metafields | jsonb | Customer metafields. | |
multipass_identifier | text | Customer multi-pass identifier. | |
note | text | A specific note related to the customer. | |
orders_count | bigint | The customer order count. | |
phone | text | Customer phone number. | |
state | text | Customer state. | |
tags | text | Tags attached to the customer. | |
tax_exempt | boolean | Whether the customer is tax exempted. | |
title | text | Title of the resource. | |
total_spent | double precision | Total amount spent by the customer. | |
updated_at | timestamp with time zone | Time when the customer last updated their account. | |
verified_email | boolean | Whether the customer email is verified. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- shopify
You can pass the configuration to the command with the --config
argument:
steampipe_export_shopify --config '<your_config>' shopify_customer