steampipe plugin install shopify

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,
phone
from
shopify_customer;
select
id,
email,
first_name,
last_name,
phone
from
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_spent
from
shopify_customer
where
email = 'russel.winfield@example.com';
select
id,
email,
first_name,
last_name,
phone,
orders_count,
total_spent
from
shopify_customer
where
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_name
from
shopify_customer
where
email = 'russel.winfield@example.com';
select
id,
email,
first_name,
last_name,
last_order_id,
last_order_name
from
shopify_customer
where
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_spent
from
shopify_customer
where
total_spent = (
select
max(total_spent)
from
shopify_customer
);
select
id,
email,
phone,
first_name,
last_name,
total_spent
from
shopify_customer
where
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_address
from
shopify_customer
where
email = 'russel.winfield@example.com';
select
id,
email,
default_address
from
shopify_customer
where
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 addresses
from
shopify_customer
where
email = 'russel.winfield@example.com';
select
id,
email,
addresses
from
shopify_customer
where
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_email
from
shopify_customer
where
not verified_email;
select
id,
email,
phone,
verified_email
from
shopify_customer
where
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_marketing
from
shopify_customer
where
accepts_marketing;
select
id,
email,
phone,
first_name,
last_name,
accepts_marketing
from
shopify_customer
where
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_exempt
from
shopify_customer
where
tax_exempt;
select
id,
email,
first_name,
last_name,
phone,
tax_exempt
from
shopify_customer
where
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,
state
from
shopify_customer
where
state = 'disabled';
select
id,
email,
first_name,
last_name,
phone,
state
from
shopify_customer
where
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_at
from
shopify_customer
where
created_at >= now() - interval '30' day
order by
created_at;
select
id,
email,
first_name,
last_name,
phone,
created_at
from
shopify_customer
where
created_at >= datetime('now', '-30 day')
order by
created_at;

Schema for shopify_customer

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
accepts_marketingbooleanWhether the customer subscribed to email marketing campaign.
addressesjsonbOther addresses of the customer.
created_attimestamp with time zoneTime when the customer created their account.
default_addressjsonbCustomer default address.
emailtextThe Email ID of the customer.
first_nametextFirst name of the customer.
idbigint=The customer ID.
last_nametextLast name of the customer.
last_order_idbigintThe last order ID of the customer.
last_order_nametextThe last order name of the customer.
metafieldsjsonbCustomer metafields.
multipass_identifiertextCustomer multi-pass identifier.
notetextA specific note related to the customer.
orders_countbigintThe customer order count.
phonetextCustomer phone number.
statetextCustomer state.
tagstextTags attached to the customer.
tax_exemptbooleanWhether the customer is tax exempted.
titletextTitle of the resource.
total_spentdouble precisionTotal amount spent by the customer.
updated_attimestamp with time zoneTime when the customer last updated their account.
verified_emailbooleanWhether 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