Dataset Queries & Segment Support

Overview
Instructions
Queries
Properties to Select/Match for Dataset Type

Overview

Standard queries to be populated within the Census Dataset function allow for optimal performance for standing up a subsequent Segment using the Census UI and, ultimately, a Sync to the outbound destination.

The Segment Use Cases listed below cover a wide range of standard criteria, noting which specific fields should be selected and filtered to achieve the intended segmentation. These fields will be input in the Segment function, leveraging the Census UI.

How to use

Follow the steps to set up a Dataset and use the queries below, updating each query to your specific data warehouse name.

Segment field recommendations are available for some common use cases.

Queries

DATASET DESCRIPTION

DATASET - SQL QUERY 

SEGMENT - KEY FIELDS & USE CASE EXAMPLES


Customer Behavior - Basic
Surfaces aggregated behavior of the customer for the Previous 2 years
 
**2 years is the recommended amount of time for this query as it will support the majority of use cases for Historical one-time, as well as reoccurring syncs.

SELECT 
c.* 
, i.email
, i.first_name
, i.last_name
, i.phone
FROM soundcommerce-client-XYZ.client_directaccess.sc_customers c
LEFT JOIN soundcommerce-client-XYZ.priv.sc_customer_pii i ON c.cuid = i.cuid
WHERE c.last_order_date > current_date() - 730


Active Customers who have purchased on a specific channel since Y timeframe
channel or node [equals/does not equal] “Name”
last_order_date [>] # of Days Ago

Active Customers who have not purchased since Y timeframe
last_order_date [<] # of Days Ago

High CLV Customers who have not purchased since Y timeframe
clv_segment [equals] “High”
last_order_date [<] # of Days Ago

Active Customers who have spent > $XXX
customer_current_chs [>] X

Active Customers with > 4mo between orders
avg_days_between_orders [>] X

Active Customers with > X number of purchases
customer_running_order_count [>] X




Customers & Orders - Complex
Surfaces aggregated Order Line Item detail by Customer to populate customer profiles (email/sms

with customer_orders as (
select 
  o.cuid
  , o.order_date
  , o.node,
  array_agg (
    struct (
      o.order_id,
      i.sku,
      i.price,
      i.quantity,
      i.title
    )
  ) as order_data
from `soundcommerce-client-XYZ.client_directaccess.sc_orders` o
  inner join `soundcommerce-client-XYZ.client_directaccess.sc_items` i
    on o.scid = i.scid
group by 1, 2, 3
)
select
  c.cuid
  , o.order_date
  , o.node
  , pii.first_name
  , pii.last_name
  , pii.email
  , pii.shipping_address_address1
  , pii.shipping_address_address2
  , pii.shipping_address_city
  , pii.shipping_address_state
  , pii.shipping_address_zip
  , o.order_data
from `soundcommerce-client-XYZ.client_directaccess.sc_customers` c
join `soundcommerce-client-XYZ.priv.sc_customer_pii` pii
  on c.cuid = pii.cuid
join customer_orders o
  on o.cuid = c.cuid


In the use case of a One Time historical sync, filter this data to customers with
order_date [more than] X number of days ago

Filter this data to match the cadence in the event of a recurring sync, for example, if you want the sync to execute weekly, filter the data to customers with
order_date [more than] 7 days ago

Properties to Select/Match for Dataset Type

PERSON - Select all that are applicable

email
cuid
phone number
first name
last name
country code
state
city
zip
birthday Day
birthday Month
Birthday Year
Gender

Screenshot 2024-04-24 at 11.42.08 AM.png

Search

Table of Contents

Overview

How to use

Queries

Properties to Select/Match for Dataset Type

Comments