SQL sample queries

Here's a list of SQL sample queries for many common use cases to get you started in your SoundCommerce Direct Access account. Unless otherwise noted, all queries apply to the SoundCommerce Core product.

To query your data without using SQL, check out Query Builder in the SoundCommerce Retail Platform.

Contents:

Campaign queries
Order-related queries
Customer-related queries

Campaign queries

Use these SQL queries to retrieve marketing performance details, such as total ad spend and attributed orders. These queries require marketing data, available only through SoundCommerce Campaign.

Note: Replace all instances of COMPANYNAME with the abbreviated name of your business as it appears in BigQuery.

Get marketing ad spend

List the total ad spend for a specified date.

SELECT
  date AS date,   ROUND(SUM(cost),2) AS total_ad_cost
  FROM
`soundcommerce-client-COMPANYNAME.client_directaccess.sc_marketing_campaign_performance`
  WHERE
    cost>0
  GROUP BY
    1
  ORDER BY
    date DESC

Get attributed orders

Run this query to return all orders, revenue, and ad cost that are attributed to a digital marketing campaign with ad spend.

SELECT
    a.*,
    o.discounted_sales AS sales
  FROM
`soundcommerce-client-COMPANYNAME.client_directaccess.sc_adspend_order_attribution` a
  JOIN
    `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` o
  ON
    a.order_id=o.order_id
    AND a.scope=o.scope
  WHERE
    adcost>0

Get performance metrics for a digital marketing campaign

Retrieve campaign performance metrics (spend, conversions, sales, CPA, CTR) by digital marketing channel (for example, Facebook, Bing, etc.) for a specified date range.

SELECT 
     traffic_source, 
     campaign AS campaign_name, 
     ad_set, 
     ROUND (SUM (cost),2) AS ad_spend, 
     ROUND (SUM (conversion),0) AS total_conversions, 
     ROUND (SUM (revenue),2) AS revenue, 
     ROUND (SUM (cost)/ ( NULLIF (SUM(conversion),0)),2) AS cost_per_acquisition, 
     ROUND (SUM(clicks)/NULLIF (SUM (impressions),0),4) AS click_through_rate 
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_marketing_campaign_performance` 
WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' 
GROUP BY traffic_source, campaign_name, ad_set 
ORDER BY total_conversions DESC

Order-related queries

Use these queries to get order details from your web store data.

Note: Replace all instances of COMPANYNAME with the abbreviated name of your business as it appears in BigQuery. 

Get order counts and total revenue by date

List all order counts for a specific date and the total revenue for those orders.

SELECT
    order_date AS date,
    SUM(discounted_sales) AS total_revenue,
    COUNT(order_id) AS total_order_count
  FROM
    `soundcommerce-client-[COMPANYNAME].client_directaccess.sc_orders` o
  GROUP BY
    1
  ORDER BY
    1 DESC

Period-over-period orders by NRF year

This query displays period-over-period order metrics by NRF Year.

SELECT 
  YearNumber as nrf_year, 
  cast(round(sum(discounted_sales),0) as int64) as discounted_sales,
  count(distinct cuid) as number_customers
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` o
  LEFT JOIN `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` d
    ON d.date = o.order_date
WHERE YearNumber >= 2017
GROUP BY YearNumber
ORDER BY YearNumber desc;

Period-over-period orders by NRF year and quarter

The result of this query expands the preceding example (Period-over-period orders by NRF year) by including the quarter and also includes a more real-world scenario of shipped vs demand metrics and gross profit.

WITH order_info AS
(
  SELECT 
    order_date as date,
    count(distinct order_id) as demand_orders,
    sum(discounted_sales) as demand_sales,
    sum(discounted_sales - ifnull(total_cogs,0) - total_refund_principal) as gross_profit
  FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` 
  WHERE NOT canceled
    AND financial_status != 'voided'
  GROUP BY order_date
),
shipped_sales_by_date AS
(
  SELECT
    fulfillment_shipped_date as date,
    sum(fulfillment_total_discounted_sales) as shipped_sales
  FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_order_fulfillment_items` 
  GROUP BY date
),
shipped_orders_by_date AS 
(
  SELECT date, count(distinct scid) as shipped_orders
  FROM 
  (
    SELECT scid, max(fulfillment_shipped_date) as date
    FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_order_fulfillment_items` f
    GROUP BY scid
  )
  GROUP BY date
)
SELECT
  YearNumber as nrf_year, 
  QuarterName as nrf_quarter,
  format("%'10.0f", round(sum(demand_sales),0)) as demand_sales,
  format("%'10.0f", round(sum(shipped_sales),0)) as shipped_sales,
  format("%'10.0f", round(sum(demand_orders),0)) as demand_orders,
  format("%'10.0f", round(sum(shipped_orders),0)) as orders_shipped,
  format("%'10.0f", round(sum(gross_profit),0)) as gross_profit
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` d
  LEFT JOIN order_info o
    on o.date = d.date
  LEFT JOIN shipped_sales_by_date ss
    on ss.date = d.date
   LEFT JOIN shipped_orders_by_date so
    on so.date = d.date
WHERE YearNumber BETWEEN 2017 AND 2021
GROUP BY YearNumber, QuarterName
ORDER BY YearNumber desc, QuarterName desc;

An alternative way of grouping year and quarter is by using the QuarterYearName field.

SELECT 
  QuarterYearName as nrf_year_and_quarter, 
  cast(round(sum(discounted_sales),0) as int64) as discounted_sales,
  count(distinct cuid) as number_customers
FROM `soundcommerce-client-COMPANYNAME.client_directaccess.sc_orders` o
  LEFT JOIN `soundcommerce-client-COMPANYNAME.client_directaccess.sc_nrf_date` d
    on d.date = o.order_date
WHERE YearNumber >= 2017
GROUP BY QuarterYearName
ORDER BY QuarterYearName;

Customer-related queries

Use these queries to retrieve customer information from your e-commerce orders data.

Note: Replace all instances of COMPANYNAME with the abbreviated name of your business as it appears in BigQuery.

Get count of new customers acquired by channel

Retrieve the counts of customers acquired by channel over a given time frame

Note the following criteria:

customer_order_count = 1
first_order_month = '2021-07-01' (July 2021)
canceled = FALSE

SELECT 
    channel, 
    first_order_month, 
COUNT(DISTINCT cuid) AS customers_acquired_by_channel 
FROM `soundcommerce-client-CLIENTNAME.client_directaccess.sc_orders` WHERE canceled IS FALSE AND customer_running_order_count = 1 
AND first_order_month = '2021-07-01' 
GROUP BY first_order_month, channel 
ORDER BY channel

Get email addresses of lapsed customers

Retrieve the email addresses of lapsed customers to build a Facebook Ads look-a-like campaign.

Note the following criteria:

customer_order_count > 5
last_order_date < 1/1/2020
Exclude CUIDs with no associated email address

Note: This query assumes that you have adequate permissions to access customers' personal identifiable information (PII) in the SoundCommerce platform.

SELECT  
    c.cuid,
    pii.email,
    c.customer_current_chs,
    c.customer_order_count,
    c.last_order_date
FROM `soundcommerce-client-CLIENTNAME.client_directaccess.sc_customers` c
LEFT JOIN `soundcommerce-client-CLIENTNAME.priv.sc_customer_pii` pii ON c.cuid = pii.cuid
WHERE c.customer_order_count > 5
AND c.last_order_date < '2020-01-01'
AND email IS NOT NULL
ORDER BY c.customer_order_count DESC
LIMIT 1000

Get monthly order totals for new customers

Retrieve order counts by month for new and repeat customers.

SELECT 
    EXTRACT (YEAR FROM order_date) AS order_year, 
    EXTRACT (MONTH FROM order_date) AS order_month, 
    COUNTIF (days_since_first_order = 0) AS new_customer_orders, 
    COUNT(*)-COUNTIF (days_since_first_order = 0) AS repeat_customer_orders, 
    COUNT(*) AS total_orders 
FROM `soundcommerce-client-CLIENTNAME.client_directaccess.sc_orders` 
WHERE order_date > '2020-12-31' 
GROUP BY order_year, order_month 
ORDER BY order_year, order_month

Get started with BigQuery

SQL queries for the NRF calendar

Search