Query Library

Instead of building queries from scratch, use Query Builder's built-in queries to pull lists for marketing, customer engagement, or operational tasks. The Query Library provides several pre-built queries that perform cross-functional searches to pull customer, order, and product data.

1. Go to Query Builder and select a query from these categories:

   • Query Library: Customers

   • Query Library: Orders

   • Query Library: Products
     
     

2. Modify, rename, and save the query to create custom queries

3. Run it as you would any other query

 

QUERY LIBRARY: Customers

At-risk Customers (12-month attrition window)

This query returns customers who will churn in the next 30 days. (By default, SoundCommerce uses a 12-month attrition window.) Retention marketers can target these customers to prevent and manage churn.

This query returns a list of customers from the high CLS segment. The SoundCommerce CLS model predicts CLS for all customer records and leverages k-means clustering to build a segment of the highest CLS customers. 

Acquisition marketers can use this list of customers to create lookalike audiences or to perform further analysis to identify attributes specific to high-value customers. 

High CLV Customers

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

Use this query to return a list of customers from the high CLV segment. The SoundCommerce CLV model predicts CLV for all customer records and leverages k-means clustering to build a segment of the highest CLV customers. 

Acquisition marketers can use this list to create lookalike audiences or to identify attributes specific to high-value customers. 

High CLV At-risk Customers

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of high CLV customers who will churn in the next 30 days. (By default, SoundCommerce uses a 12-month attrition window.) Retention marketers can target these customers to prevent and manage churn of high-value customers.

High CLV Customers with Late Delivered Orders

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of high CLV customers who received a late order in the last 30 days. 

Here, we define a late order as an order that meets one of these characteristics:

• The time from conversion to shipment (or days to ship) is 5 or more days.

• The time from shipment to delivery (or delivery days) is 5 or more days.

Retention marketers can use this query to appease high-value customers who have experienced operational exceptions.

High CLV Customers with Late Open Orders

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of high CLV customers who have outstanding open orders (that is, orders with a shipment status of “unfulfilled”) that are also late. SoundCommerce defines late open orders as orders that have been open for more than five days. 

Operations managers can prioritize these orders to ensure that high-value customers have satisfactory shopping experiences.

High CLV Recently Churned Customers

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of high CLV customers who churned in the last 30 days. (By default, SoundCommerce uses a 12-month attrition window.) Retention marketers can target these customers to reactivate high-value customers.  

Low CLS Customers

Requirements: The SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of customers from the low CLS segment. The SoundCommerce CLS model predicts CLS for all customer records and leverages k-means clustering to build a segment of the lowest CLS customers. 

Retention marketers can target these customers to drive overall CLS by converting low CLS customers to high CLS customers.

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of customers from the low CLV segment. The SoundCommerce CLV model predicts CLV for all customer records and leverages k-means clustering to build a segment of the lowest CLV customers.

Retention marketers can target these customers to drive overall CLV by converting low CLV customers to high CLV customers.

One-time Purchasers

This query returns a list of active customers who have only ever placed one order. Retention marketers can target these customers to convert them from one-time purchasers to repeat customers. 

QUERY LIBRARY: Orders

All Open Orders

This query returns a list of all orders placed in the last 30 days that are currently open (that is, orders that have a shipment status of "unfulfilled"). Operations managers can use this list to ensure that these orders are fulfilled in a timely manner. 

This query returns a list of all canceled orders placed in the last 30 days. Operations and Merchandising managers can analyze this list to identify customer, product, or order attributes commonly associated with canceled orders. 

High CLV Customers All Orders

Requirements: SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

Marketers can use this query to identify order attributes associated with High CLV customers’ orders. For example, which order channels or marketing sources are most commonly associated with orders placed by high CLV customers?

Late Orders (Open)

This query returns orders placed in the last 30 days that have been open more than five days and are considered late.

Operations managers can use this list to prioritize the fulfillment of late orders.

Open Orders for High CLS Customers

Requirements: The SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of open orders placed by high CLS customers. Operations managers can use this query to prioritize the fulfillment of orders placed by high-value customers. 

Open Orders for High CLV Customers

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of open orders placed by high customer lifetime value (CLV) customers. Operations managers can use this query to prioritize the fulfillment of orders placed by high-value customers. 

QUERY LIBRARY: Products

All Products for High CLV Customers

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of products ordered by high CLV Customers. Merchandising managers can use this query to identify products and categories that are most popular amongst high-value customers to ensure that they're well-stocked and promoted.  

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of products that high CLV customers order first. Acquisition marketers can use this query to identify products to promote to convert prospective customers to high-value, repeat purchasers. 

 

Frequently Canceled Products

This query returns a list of products that are frequently canceled. Frequently canceled means that over 10% of the orders that included the product were canceled in the last 365 days.

Second Order Products for High CLV Customers

Requirements:  SoundCommerce Profit, in addition to the SoundCommerce Campaign or SoundCommerce Customer module

This query returns a list of products purchased second by high CLV customers. Retention marketers can use this product list to determine which products to promote to one-time purchasers. By promoting these products, retention marketers can increase the likelihood of one-time purchasers becoming high-value, repeat customers.