Skip to content

Instantly share code, notes, and snippets.

@benjaminsehl

benjaminsehl/original-orders.js

Last active June 11, 2022 04:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save benjaminsehl/9775b77a9a5e1665cefd79bd46596ac6 to your computer and use it in GitHub Desktop.
Save benjaminsehl/9775b77a9a5e1665cefd79bd46596ac6 to your computer and use it in GitHub Desktop.
Download ZIP
Current Order Cube
Raw
original-orders.js
const shopifyStores = [
{name: 'Canada Web', tablePrefix: 'ca_ecomm'},
{name: 'Canada Retail', tablePrefix: 'ca_retail'},
{name: 'Intl. Web', tablePrefix: 'us_ecomm'},
{name: 'Intl. Retail', tablePrefix: 'us_retail'}
];
let KotnEmailsArray = [
"kotn",
"ordinarysupply",
]
KotnEmailsStatement = "(array['%" + KotnEmailsArray.join("%','%")+"%'])"
function customerUnion() {
const derivedSelect = "select first_name, last_name, email, id, MIN(join_date) AS join_date, currency, zip, city, company, country, province, SUM(order_count) AS order_count, total_spent, last_order_id, store_name from ( "
const shopMap = shopifyStores.map(p => `select
id,
created_at AS join_date,
default_address__first_name AS first_name,
default_address__last_name AS last_name,
COALESCE(LOWER(email), id::varchar(255)) AS email,
currency,
default_address__zip AS zip,
TRIM(BOTH FROM REPLACE(INITCAP(SPLIT_PART(default_address__city,',',2)), 'é', 'e')) AS city,
default_address__company AS company,
CASE WHEN '${p.name}' LIKE 'ca_retail' THEN 'Canada'
WHEN '${p.name}' LIKE 'us_retail' THEN 'United States'
ELSE TRIM(BOTH FROM INITCAP(default_address__country)) END AS country,
default_address__province AS province,
CAST(orders_count AS INTEGER) as order_count,
CASE WHEN currency = 'USD' THEN CAST(total_spent AS DECIMAL) * 1.35
ELSE CAST(total_spent AS DECIMAL) END AS line_items_price,
CAST(total_spent AS DECIMAL) as total_spent,
last_order_id,
'${p.name}' as store_name
from ${p.tablePrefix}.customers
`).join(" UNION ALL ");
const derivedGroup = " ) AS customerUnion GROUP BY first_name, last_name, email, id, currency, zip, city, company, country, province, total_spent, last_order_id, store_name";
return derivedSelect.concat(shopMap,derivedGroup);
}
function orderUnion() {
const derivedSelect = "select id, customer_id, name, weight, email, phone, first_name, last_name, cc, currency, city, company, country, retail_location, latitude, longitude, province, zip, SUM(order_count) AS order_count, total, subtotal, discount, tax, shipping, order_date, MIN(join_date) AS join_date, channel, store_name from (";
const shopMap = shopifyStores.map(p => `select
DISTINCT ON (id) id,
customer__id AS customer_id,
name,
total_weight AS weight,
CASE WHEN (customer__email IS NULL OR customer__email = '') THEN id::varchar(255) ELSE LOWER(customer__email) END AS email,
REPLACE(
REPLACE(
REPLACE(
REPLACE(customer__default_address__phone, '(', '')
, ')', '')
, '-', '')
, ' ', '') AS phone,
customer__default_address__first_name AS first_name,
customer__default_address__last_name AS last_name,
payment_details__credit_card_company AS cc,
total_price_set__shop_money__currency_code AS currency,
CASE
WHEN location_id = 15532458043 OR location_id = 488452 OR location_id = 21731397 OR location_id = 5121349 OR location_id = 30623531091 THEN 'Toronto'
WHEN location_id = 20550877243 OR location_id = 8321957945 OR location_id = 30623563859 THEN 'Vancouver'
WHEN location_id = 20667301942 THEN 'New York'
WHEN location_id = 14239105098 THEN 'New York'
WHEN location_id = 22211395638 THEN 'Montreal'
ELSE TRIM(BOTH FROM REPLACE(INITCAP(shipping_address__city), 'é', 'e')) END AS city,
shipping_address__company AS company,
CASE WHEN ((shipping_address__country IS NULL
OR shipping_address__country = '')
AND currency = 'CAD') THEN 'Canada'
WHEN ((shipping_address__country IS NULL
OR shipping_address__country = '')
AND currency = 'USD') THEN 'United States'
ELSE INITCAP(shipping_address__country) END AS country,
CASE
WHEN location_id = 15532458043 OR location_id = 488452 OR location_id = 21731397 OR location_id = 5121349 OR location_id = 30623531091 THEN 'Toronto'
WHEN location_id = 20550877243 OR location_id = 8321957945 OR location_id = 30623563859 THEN 'Vancouver'
WHEN location_id = 20667301942 THEN 'New York'
WHEN location_id = 14239105098 THEN 'New York'
WHEN location_id = 22211395638 THEN 'Montreal'
WHEN location_id = 31806085 AND CAST(subtotal_price_set__shop_money__amount AS DECIMAL) < 500 THEN 'Online'
WHEN location_id = 31806085 AND CAST(subtotal_price_set__shop_money__amount AS DECIMAL) > 500 THEN 'Bulk'
WHEN location_id IS NULL OR location_id = 14422638651 THEN 'Online'
ELSE TO_CHAR( location_id, '99999999999' ) END AS retail_location,
shipping_address__latitude AS latitude,
shipping_address__longitude AS longitude,
CASE
WHEN location_id = 15532458043 OR location_id = 488452 OR location_id = 21731397 OR location_id = 5121349 OR location_id = 30623531091 THEN 'Ontario'
WHEN location_id = 20550877243 OR location_id = 8321957945 OR location_id = 30623563859 THEN 'British Columbia'
WHEN location_id = 20667301942 THEN 'New York'
WHEN location_id = 14239105098 THEN 'New York'
WHEN location_id = 22211395638 THEN 'Quebec'
ELSE TRIM(BOTH FROM REPLACE(INITCAP(shipping_address__province), 'é', 'e')) END AS province,
shipping_address__zip AS zip,
customer__orders_count AS order_count,
CASE WHEN currency = 'USD' THEN (CAST(subtotal_price_set__shop_money__amount AS DECIMAL) * 1.35)
ELSE CAST(subtotal_price_set__shop_money__amount AS DECIMAL) END AS subtotal,
CASE WHEN currency = 'USD' THEN (CAST(total_discounts_set__shop_money__amount AS DECIMAL) * 1.35)
ELSE CAST(total_discounts_set__shop_money__amount AS DECIMAL) END AS discount,
CASE WHEN currency = 'USD' THEN CAST(total_line_items_price_set__shop_money__amount AS DECIMAL) * 1.35
ELSE CAST(total_line_items_price_set__shop_money__amount AS DECIMAL) END AS line_items_price,
CASE WHEN currency = 'USD' THEN (CAST(total_tax_set__shop_money__amount AS DECIMAL) * 1.35)
ELSE CAST(total_tax_set__shop_money__amount AS DECIMAL) END AS tax,
CASE WHEN currency = 'USD' THEN (CAST(total_price_set__shop_money__amount AS DECIMAL) - CAST(total_tax_set__shop_money__amount AS DECIMAL) - CAST(total_discounts_set__shop_money__amount AS DECIMAL) + CAST(total_shipping_price_set__shop_money__amount AS DECIMAL)) * 1.35
ELSE CAST(total_price_set__shop_money__amount AS DECIMAL) - CAST(total_tax_set__shop_money__amount AS DECIMAL) - CAST(total_discounts_set__shop_money__amount AS DECIMAL) + CAST(total_shipping_price_set__shop_money__amount AS DECIMAL) END AS total,
CASE WHEN currency = 'USD' THEN CAST(total_shipping_price_set__shop_money__amount AS DECIMAL) * 1.35
ELSE CAST(total_shipping_price_set__shop_money__amount AS DECIMAL) END AS shipping,
created_at AS order_date,
customer__created_at AS join_date,
CASE WHEN source_name = 'pos' THEN 'Retail'
WHEN source_name = 'web' THEN 'Ecommerce'
WHEN source_name = 'shopify_draft_order' AND CAST(subtotal_price_set__shop_money__amount AS DECIMAL) < 500 THEN 'Ecommerce'
WHEN source_name = 'shopify_draft_order' AND CAST(subtotal_price_set__shop_money__amount AS DECIMAL) > 500 THEN 'Bulk'
ELSE 'Ecommerce' END AS channel,
'${p.name}' store_name
FROM ${p.tablePrefix}.orders
`).join(" UNION ALL ");
const derivedGroup = " ) AS orderUnion GROUP BY id, customer_id, name, weight, email, phone, first_name, last_name, cc, currency, city, company, country, retail_location, latitude, longitude, province, zip, subtotal, discount, tax, shipping, total, order_date, channel, store_name";
return derivedSelect.concat(shopMap,derivedGroup);
}
function refundUnion() {
const derivedSelect = "select id, order_id, note, refund_date, store_name from ( "
return shopifyStores.map(p => `select
id,
_sdc_source_key_id as order_id,
note,
created_at AS refund_date,
'${p.name}' store_name
FROM ${p.tablePrefix}.orders__refunds
`).join(" UNION ALL ");
const derivedGroup = " ) AS RefundUnion GROUP BY id, order_id, note, refund_date, store_name";
return derivedSelect.concat(shopMap,derivedGroup);
}
function refundLineItemsUnion() {
const derivedSelect = "select id, refund_id, title, price, restock_type, currency, total, store_name from ( "
return shopifyStores.map(p => `select
DISTINCT ON (id) id,
_sdc_source_key_id AS refund_id,
line_item__title AS title,
line_item__price AS price,
restock_type,
line_item__total_discount_set__shop_money__currency_code AS currency,
CASE WHEN line_item__total_discount_set__shop_money__currency_code = 'USD' THEN (CAST(subtotal AS DECIMAL) * 1.35)
ELSE CAST(subtotal AS DECIMAL) END AS total,
'${p.name}' store_name
FROM ${p.tablePrefix}.orders__refunds__refund_line_items
`).join(" UNION ALL ");
const derivedGroup = " ) AS RefundLineItemUnion GROUP BY id, refund_id, title, price, restock_type, currency, total, store_name";
return derivedSelect.concat(shopMap,derivedGroup);
}
function discountApplications() {
const derivedSelect = "select order_id, allocation_method, code, description, target_selection, target_type, type, value, value_type from ( "
const shopMap = shopifyStores.map(p => `select
_sdc_source_key_id AS order_id,
allocation_method,
code,
description,
target_selection,
target_type,
type,
value,
value_type
from ${p.tablePrefix}.orders__discount_applications
`).join(" UNION ALL ");
const derivedGroup = " ) AS DiscountUnion GROUP BY order_id, allocation_method, code, description, target_selection, target_type, type, value, value_type";
return derivedSelect.concat(shopMap,derivedGroup);
}
function ordersLineItemsUnion() {
return shopifyStores.map(p => `select
_sdc_source_key_id AS order_id,
sku,
split_part(sku, '.', 2) as year,
CASE
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 3) = '0') THEN 'N/A'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 3) = '1') THEN 'Pre-Spring'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 3) = '2') THEN 'Spring/Summer'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 3) = '3') THEN 'Pre-Fall'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 3) = '4') THEN 'Fall-Winter'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 3) = '5') THEN 'Holiday'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 3) = '6') THEN 'Resort'
ELSE 'Unknown' END as season,
CASE
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 4) = '0') THEN 'N/A'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 4) = '1') THEN 'Men'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 4) = '2') THEN 'Women'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 4) = '3') THEN 'Unisex'
WHEN sku LIKE '%AP-M-%' THEN 'Men'
WHEN sku LIKE '%AP-W-%' THEN 'Women'
ELSE 'Unknown' END as gender,
CASE
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '1') THEN 'Knit Top'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '2') THEN 'Woven Top'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '3') THEN 'Woven Bottom'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '4') THEN 'Knit Bottom'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '5') THEN 'Woven Dress'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '6') THEN 'Knit Dress'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '7') THEN 'Sweats'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '8') THEN 'Hoisery'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '9') THEN 'Undergarments'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '10') THEN 'Bags'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '11') THEN 'Hats'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '12') THEN 'Towels'
WHEN (sku LIKE '%.%') AND (split_part(sku, '.', 5) = '13') THEN 'Bedding'
ELSE 'Unknown' END as commodity,
CASE
WHEN title LIKE 'Boxer-Brief' THEN 'Boxer Briefs'
WHEN title LIKE 'Boxer-Short' THEN 'Boxer Shorts'
WHEN title LIKE 'Kotn x Kaye Blegvad Sweatshirt' THEN 'Kaye Blegvad Sweatshirt'
WHEN title LIKE 'Pique T-shirt NEW' THEN 'Pique T-shirt'
WHEN title LIKE 'Short-sleeve Henley' THEN 'Henley T-shirt'
WHEN title LIKE 'Kotn X Scott Sueme' THEN 'Artist Series: Scott Sueme'
WHEN title LIKE 'Cropped Sweatpant' THEN 'Straight Leg Sweatpant'
WHEN title LIKE 'Long Sleeve' THEN 'Essential Longsleeve'
WHEN title LIKE 'Easy V-neck T-shirt' THEN 'Easy V'
WHEN title LIKE 'Easy V T-shirt' THEN 'Easy V'
ELSE TRIM(BOTH FROM
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(title, 'é', 'e')
, 'The ', '')
, 'u̩', 'u')
,' (Sale)', '')
,'Tee', 'T-shirt')
,'T-Shirt', 'T-shirt')
,' in', '')
,' Hunter Green', '')
,' Alabaster', '')
,' Pomegranate', '')
,' Deep Nile', '')
,'Sweatpants', 'Sweatpant')
, 'Longsleeve T-shirt', 'Longsleeve')
, 'Brushed Crew T-shirt', 'Essential Crew')
, 'Easy Crew', 'Essential Crew')
, 'Essential Crew T-shirt', 'Essential Crew')
, '’', '''')
, '', '')
, '', '')
, '''', '')
) END AS title,
CASE
WHEN SPLIT_PART(variant_title,' / ',1) LIKE 'XS' THEN 'None'
WHEN SPLIT_PART(variant_title,' / ',1) LIKE 'S' THEN 'None'
WHEN SPLIT_PART(variant_title,' / ',1) LIKE 'M' THEN 'None'
WHEN SPLIT_PART(variant_title,' / ',1) LIKE 'L' THEN 'None'
WHEN SPLIT_PART(variant_title,' / ',1) LIKE 'XL' THEN 'None'
WHEN SPLIT_PART(variant_title,' / ',1) LIKE 'XXL' THEN 'None'
WHEN SPLIT_PART(variant_title,' / ',1) = '' THEN 'None'
WHEN SPLIT_PART(variant_title,' / ',1) LIKE 'Grey' THEN 'Heather Grey'
ELSE
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(SPLIT_PART(variant_title,' / ',1), 'Dark Pomegranate', 'Pomegranate')
, 'Kohl', 'Black')
, 'Carmine', 'Poppy')
, 'Deep Nile', 'Navy')
, 'Deep Pomegranate', 'Pomegranate')
, 'Camle', 'Camel')
, 'Tan', 'Sand') END AS colour,
SPLIT_PART(variant_title,' / ',2) AS size,
vendor,
quantity,
price_set__presentment_money__currency_code AS currency,
CASE WHEN price_set__presentment_money__currency_code = 'USD' THEN (CAST(price_set__presentment_money__amount AS DECIMAL) * 1.35) ELSE CAST(price_set__presentment_money__amount AS DECIMAL) END AS price,
CASE WHEN price_set__presentment_money__currency_code = 'USD' THEN (CAST(total_discount AS DECIMAL) * 1.35) ELSE CAST(total_discount AS DECIMAL) END AS discount,
'${p.name}' store_name
FROM ${p.tablePrefix}.orders__line_items
`).join(" UNION ALL ");
}
cube(`Customers`, {
sql: customerUnion(),
joins: {
Orders: {
relationship: `hasMany`,
sql: `${Customers}.email = ${Orders}.email`
},
LastOrder: {
sql: `${Customers}.last_order_id = ${LastOrder}.id`,
relationship: `hasOne`
}
},
dimensions: {
firstName: {
sql: `first_name`,
type: `string`,
},
lastName: {
sql: `last_name`,
type: `string`,
},
currency: {
sql: `currency`,
type: `string`
},
// TRIM(BOTH FROM REPLACE(INITCAP( , 'é', 'e')))
city: {
sql: `COALESCE(${LastOrder.city}, ${CUBE}.city)`,
type: `string`
},
company: {
sql: `INITCAP(company)`,
type: `string`
},
country: {
sql: `COALESCE(${LastOrder.country}, ${CUBE}.country)`,
type: `string`
},
province: {
sql: `INITCAP(COALESCE(${LastOrder.province}, ${CUBE}.province))`,
type: `string`
},
zip : {
sql: `LOWER(zip)`,
type: `string`
},
id : {
sql: `id`,
type: `string`,
primaryKey: true,
},
email : {
sql: `email`,
type: `string`,
},
customerAov : {
sql: `${Orders.sales} / ${Orders.count}`,
type: `number`,
format: `currency`,
subQuery: true,
title: `Average Order Value`,
},
lifetimeValue : {
sql: `${Orders.sales}`,
type: `number`,
format: `currency`,
subQuery: true
},
// lifetimeValueShopify: {
// sql: `total_spent`,
// type: `number`,
// format: `currency`,
// title: `LTV using Total Spent`
// },
uniqueCount : {
sql: `${Orders.customerCount}`,
type: `number`,
subQuery: true
},
orderCount : {
sql: `${Orders.count}`,
type: `number`,
subQuery: true
},
joinDate : {
sql: `join_date`,
type: `time`
},
monthNumber: {
sql: `CASE WHEN EXTRACT(MONTH FROM ${Orders.firstOrderDate}) = 13 THEN 12 ELSE EXTRACT(MONTH FROM ${Orders.firstOrderDate}) END`,
type: `time`,
subQuery: true
},
storeName: {
sql: `store_name`,
type: `string`,
title: `Store Name`
}
},
measures: {
count: {
type: `countDistinct`,
sql: `email`,
drillMembers: [email,orderCount,lifetimeValue,joinDate],
filters: [
{ sql: `${CUBE}.email LIKE '%@%'`},
{ sql: `${CUBE}.email NOT LIKE ALL ${KotnEmailsStatement}`},
{ sql: `${CUBE}.order_count > 0` }
]
},
newCustomers: {
type: `countDistinct`,
sql: `email`,
drillMembers: [email,orderCount,lifetimeValue, storeName, orderCount],
filters: [
{ sql: `${CUBE}.order_count = 1` },
{ sql: `${CUBE}.total_spent > 0` }
]
},
returnCustomers: {
type: `countDistinct`,
sql: `email`,
drillMembers: [email,orderCount,lifetimeValue],
filters: [
{ sql: `${CUBE}.order_count > 1` }
]
},
repeatPurchaseRate: {
sql: `(SELECT COUNT(*) WHERE ${CUBE}.order_count > 1)/(SELECT COUNT(*))*100.0`,
type: `avg`,
format: `percent`,
filters: [
{ sql: `${CUBE}.order_count > 0` },
{ sql: `${CUBE}.email LIKE '%@%'` }
],
},
monthlyActiveCustomers: {
type: `countDistinct`,
sql: `email`,
rollingWindow: {
trailing: `30 day`
}
},
avgLifetimeValue : {
type: `avg`,
format: `currency`,
sql: `${lifetimeValue}`,
drillMembers: [email,orderCount,customerAov,lifetimeValue]
},
// avgLifetimeValueShopify : {
// type: `avg`,
// format: `currency`,
// sql: `total_spent`,
// title: `Avg LTV using Total Spent`,
// drillMembers: [email,orderCount,customerAov,lifetimeValue]
// },
// ltv3Mo : {
// type: `avg`,
// format: `currency`,
// sql: `total_spent`,
// title: `3-Month LTV`,
// rollingWindow: {
// trailing: `3 month`
// },
// filters: [
// { sql: `${CUBE}.total_spent > 0` }
// ]
// },
// ltv6Mo : {
// type: `avg`,
// format: `currency`,
// sql: `total_spent`,
// title: `6-Month LTV`,
// rollingWindow: {
// trailing: `6 month`
// },
// filters: [
// { sql: `${CUBE}.total_spent > 0` }
// ]
// },
// ltv12Mo : {
// type: `avg`,
// format: `currency`,
// sql: `total_spent`,
// title: `12-Month LTV`,
// rollingWindow: {
// trailing: `12 month`
// },
// filters: [
// { sql: `${CUBE}.total_spent > 0` }
// ]
// },
avgOrderCount : {
type: `avg`,
sql: `${orderCount}`
}
}
});
cube(`Orders`, {
sql: orderUnion(),
joins: {
Customers: {
relationship: `belongsTo`,
sql: `${Orders}.email = ${Customers}.email`
},
LineItems: {
relationship: `hasMany`,
sql: `${Orders}.id = ${LineItems}.order_id`,
},
Refunds: {
relationship: `hasMany`,
sql: `${Orders}.id = ${Refunds}.order_id`,
},
Discounts: {
relationship: `hasMany`,
sql: `${Orders}.id = ${Discounts}.order_id`,
}
},
segments: {
canada: {
sql: `country = 'Canada'`
},
usa: {
sql: `country = 'United States'`,
title: `USA`
},
international: {
sql: `country <> 'United States' AND country <> 'Canada'`
},
ecommerce: {
sql: `channel = 'Ecommerce'`
},
retail: {
sql: `channel = 'Retail'`
}
},
measures: {
count: {
type: `count`,
filters: [
{ sql: `${CUBE}.total > 0` }
],
drillMembers: [name,email,totalAmount,discountTotal,channel,orderCount,orderDate]
},
totalWeight : {
sql: `weight`,
type: `sum`,
title: `Total Weight`
},
avgWeight : {
sql: `weight`,
type: `avg`,
title: `Avg. Weight`,
drillMembers: [name,totalAmount,weightAmount]
},
subtotal : {
sql: `subtotal`,
type: `sum`,
format: `currency`
},
sales : {
sql: `total`,
type: `sum`,
format: `currency`,
title: `Sales`,
filters: [
{ sql: `${CUBE}.total > 0` }
]
},
newSales : {
sql: `total`,
type: `sum`,
format: `currency`,
title: `New Sales`,
filters: [
{ sql: `${CUBE}.total > 0` },
{ sql: `EXTRACT(DOY FROM ${CUBE}.order_date) = EXTRACT(DOY FROM ${CUBE}.join_date) AND EXTRACT(YEAR FROM ${CUBE}.order_date) = EXTRACT(YEAR FROM ${CUBE}.join_date) OR ${CUBE}.order_count = 1` },
]
},
repeatSales : {
sql: `total`,
type: `sum`,
format: `currency`,
title: `Repeat Sales`,
filters: [
{ sql: `${CUBE}.total > 0` },
{ sql: `EXTRACT(DOY FROM ${CUBE}.order_date) > EXTRACT(DOY FROM ${CUBE}.join_date) OR EXTRACT(YEAR FROM ${CUBE}.order_date) > EXTRACT(YEAR FROM ${CUBE}.join_date) OR ${CUBE}.order_count > 1` },
]
},
discountTotal : {
sql: `discount`,
type: `sum`,
format: `currency`,
title: `Total Discounted`,
filters: [
{ sql: `${CUBE}.total > 0` }
],
},
lineItemTotalValue: {
sql: `line_items_price`,
type: `sum`,
format: `currency`,
title: `Gross sales`
},
avgOrderCount: {
sql: `order_count`,
type: `avg`,
title: `Average Order Count`,
filters: [
{ sql: `${CUBE}.email LIKE '%@%' AND ${CUBE}.email NOT LIKE ALL ${KotnEmailsStatement} AND ${CUBE}.order_count > 0` }
]
},
revenue: {
sql: `sum(${CUBE}.total) - sum(${refunded})`,
type: `number`,
format: `currency`,
},
dailyTotal: {
sql: `total / 7`,
type: `sum`,
rollingWindow: {
trailing: `7 day`
},
format: `currency`,
title: `Avg. Daily Revenue`
},
weeklyTotal: {
sql: `total`,
type: `sum`,
rollingWindow: {
trailing: `1 week`
},
format: `currency`,
title: `Avg. Weekly Revenue`
},
annualRunRate: {
sql: `total * 12`,
type: `sum`,
rollingWindow: {
trailing: `30 day`
},
format: `currency`,
title: `Annual Run Rate`
},
totalSum: {
sql: `total`,
type: `runningTotal`,
format: `currency`,
title: `Running Total`
},
averageOrderValue: {
sql: `total`,
type: `avg`,
format: `currency`,
title: `AOV`,
filters: [
{ sql: `${CUBE}.total > 0` }
],
drillMembers: [name,lineItemAmount,discountAmount,subtotalAmount,taxAmount,shippingAmount,totalAmount]
},
discountPercentAvg: {
sql: `CASE WHEN subtotal = 0 THEN 0 ELSE discount/line_items_price*100.0 END`,
type: `avg`,
format: `percent`,
title: `Avg. Percent Discount`,
drillMembers: [name,lineItemAmount,discountAmount,subtotalAmount,taxAmount,shippingAmount,totalAmount]
},
shippingTotal: {
sql: `shipping`,
type: `sum`,
format: `currency`,
title: `Shipping Total`
},
taxTotal: {
sql: `tax`,
type: `sum`,
format: `currency`,
title: `Tax Total`
},
// repeatPurchaseRate: {
// sql: `(SELECT COUNT(*) WHERE customer_type = 'Returning')/(SELECT COUNT(*))*100.0`,
// type: `avg`,
// format: `percent`,
// title: `RPR`
// },
firstOrderDate: {
sql: `order_date`,
type: `min`,
title: `First Order Date`,
shown: false
},
lastOrderDate: {
sql: `order_date`,
type: `max`,
title: `Last Order Date`,
shown: false
}
},
dimensions: {
name : {
sql: `name`,
type: `string`,
title: `Order Number`
},
orderCount : {
sql: `order_count`,
type: `number`,
title: `Order Count`
},
firstName : {
sql: `first_name`,
type: `string`
},
lastName : {
sql: `last_name`,
type: `string`
},
phone : {
sql: `phone`,
type: `string`
},
discountPercent: {
sql: `discount/line_items_price*100.0`,
type: `number`,
format: `percent`,
title: `Percent Discount`
},
id : {
sql: `id`,
type: `string`,
primaryKey: true,
},
retailLocation: {
sql: `retail_location`,
type: `string`,
},
customerId : {
sql: `customer_id`,
type: `string`,
primaryKey: true,
},
email : {
sql: `email`,
type: `string`,
title: `Email`,
},
lineItemAmount: {
sql: `line_items_price`,
type: `number`,
format: `currency`,
title: `Line Item Value`
},
discountAmount : {
sql: `discount`,
type: `number`,
format: `currency`,
title: `Discount`
},
subtotalAmount : {
sql: `subtotal`,
type: `number`,
format: `currency`,
title: `Subtotal`
},
shippingAmount: {
sql: `shipping`,
type: `number`,
format: `currency`,
title: `Shipping`
},
taxAmount: {
sql: `tax`,
type: `number`,
format: `currency`,
title: `Tax`
},
totalAmount: {
sql: `total`,
type: `number`,
format: `currency`,
title: `Total`
},
valueBucket: {
sql: `
CASE
WHEN total > 1000 THEN ROUND(total, -3)
WHEN total > 200 AND total <= 1000 THEN ROUND(total, -2)
ELSE ROUND(total,-1)
END
`,
type: `number`,
format: `currency`,
title: `Value Bucket`,
},
macroValueBucket: {
sql: `
CASE
WHEN total >= 1000 THEN '5. >$1000'
WHEN total >= 200 AND total < 1000 THEN '4. $200-1000'
WHEN total >= 100 AND total < 200 THEN '3. $100-200'
WHEN total >= 50 AND total < 100 THEN '2. $50-100'
WHEN total > 0 AND total < 50 THEN '1. <$50'
ELSE '0. Gift'
END
`,
type: `string`
},
refunded: {
sql: `CASE WHEN ${Refunds.total} <> NULL THEN ${Refunds.total} ELSE 0 END`,
type: `number`,
format: `currency`,
subQuery: true,
},
weightAmount : {
sql: `weight`,
type: `number`,
title: `Weight`
},
location: {
type: `geo`,
title: `Location`,
latitude: {
sql: `${CUBE}.latitude`
},
longitude: {
sql: `${CUBE}.longitude`
}
},
city: {
type: `string`,
sql: `city`,
title: `City`
},
province: {
sql: `CASE
WHEN ((${CUBE}.province IS NULL
OR ${CUBE}.province = '')
AND (${CUBE}.currency = 'CAD'
AND ${CUBE}.channel = 'pos')) THEN 'Ontario'
WHEN ((${CUBE}.province IS NULL
OR ${CUBE}.province = '')
AND (${CUBE}.currency = 'USD'
AND ${CUBE}.channel = 'pos')) THEN 'New York'
ELSE INITCAP(${CUBE}.province)
END`,
type: `string`,
title: `Province`
},
zip: {
sql: `LOWER(zip)`,
type: `string`,
title: `Zip Code`
},
country: {
sql: `country`,
type: `string`,
title: `Country`
},
company: {
sql: `company`,
type: `string`,
title: `Company`
},
channel: {
sql: `channel`,
type: `string`,
title: `Channel`
},
customerType: {
sql: `
CASE
WHEN (EXTRACT(DOY FROM ${CUBE}.order_date) > EXTRACT(DOY FROM ${CUBE}.join_date) OR EXTRACT(YEAR FROM ${CUBE}.order_date) > EXTRACT(YEAR FROM ${CUBE}.join_date)) AND ${CUBE}.order_count > 1 THEN 'Returning'
WHEN (EXTRACT(DOY FROM ${CUBE}.order_date) = EXTRACT(DOY FROM ${CUBE}.join_date) AND EXTRACT(YEAR FROM ${CUBE}.order_date) = EXTRACT(YEAR FROM ${CUBE}.join_date)) OR ${CUBE}.order_count = 1 THEN 'New'
ELSE 'New'
END
`,
type: `string`
},
currency: {
sql: `currency`,
type: `string`,
title: `Currency`
},
orderDate: {
sql: `order_date`,
type: `time`,
},
joinDate: {
sql: `join_date`,
type: `time`
},
dayOfWeek: {
sql:`CASE WHEN EXTRACT(DOW FROM order_date) = 0 THEN '1 - Sunday'
WHEN EXTRACT(DOW FROM order_date) = 1 THEN '2 - Monday'
WHEN EXTRACT(DOW FROM order_date) = 2 THEN '3 - Tuesday'
WHEN EXTRACT(DOW FROM order_date) = 3 THEN '4 - Wednesday'
WHEN EXTRACT(DOW FROM order_date) = 4 THEN '5 - Thursday'
WHEN EXTRACT(DOW FROM order_date) = 5 THEN '6 - Friday'
WHEN EXTRACT(DOW FROM order_date) = 6 THEN '7 - Saturday' END`,
type: `string`
},
weekNumber: {
sql: `CASE WHEN EXTRACT(WEEK FROM order_date) = 53 THEN 52 ELSE EXTRACT(WEEK FROM order_date) END`,
type: `time`
},
monthNumber: {
sql: `CASE WHEN EXTRACT(MONTH FROM order_date) = 13 THEN 12 ELSE EXTRACT(MONTH FROM order_date) END`,
type: `time`
},
yearNumber: {
sql: `EXTRACT(YEAR FROM order_date)`,
type: `time`
},
ccType: {
sql: `cc`,
type: `string`,
title: `Credit Card Type`
},
storeName: {
sql: `store_name`,
type: `string`,
title: `Store Name`
}
}
});
cube(`LastOrder`, {
extends: Orders
});
cube(`Discounts`, {
sql: discountApplications(),
joins: {
Orders: {
relationship: `belongsTo`,
sql: `${Discounts}._sdc_source_key_id = ${Orders}.id`,
}
},
measures: {
},
dimensions: {
id: {
shown: false,
sql: `_sdc_source_key_id`,
type: `string`,
primaryKey: true,
},
amount: {
sql: `value`,
type: `number`,
format: `currency`,
},
code: {
sql: `code`,
type: `string`,
},
description: {
sql: `description`,
type: `string`,
},
allocationMethod: {
sql: `allocation_method`,
type: `string`,
},
target_selection: {
sql: `target_selection`,
type: `string`,
},
target_type: {
sql: `target_selection`,
type: `string`,
},
},
});
cube(`LineItems`, {
sql: ordersLineItemsUnion(),
joins: {
Orders: {
relationship: `hasOne`,
sql: `${LineItems}.order_id = ${Orders}.id`,
}
},
segments: {
},
measures: {
orderCount: {
sql: `order_id`,
type: `countDistinct`,
},
totalQuantity : {
sql: `quantity`,
type: `sum`,
},
totalItems: {
sql: `sku`,
type: `countDistinct`
},
avgQuantity : {
sql: `${totalQuantity} / ${orderCount}`,
type: `number`,
title: `UPT`,
},
totalDiscount : {
sql: `discount`,
type: `sum`,
format: `currency`,
},
avgDiscount : {
sql: `discount`,
type: `avg`,
format: `currency`,
},
avgPercentDiscount : {
sql: `${CUBE}.discount::decimal / ${CUBE}.price::decimal * 100.00`,
type: `avg`,
format: `percent`,
},
avgPrice : {
sql: `${CUBE}.price::decimal - ${CUBE}.discount::decimal`,
type: `avg`,
format: `currency`,
},
listPrice : {
sql: `${CUBE}.price`,
type: `avg`,
format: `currency`,
},
totalPrice : {
sql: `${CUBE}.price * ${CUBE}.quantity`,
type: `sum`,
format: `currency`,
title: `Total Revenue`
},
velocity7: {
sql: `${totalQuantity} / 7`,
type: `number`,
format: `number`,
title: `7-Day Velocity`,
rollingWindow: {
trailing: `7 day`
},
description: `Uses a 7-day rolling window`,
},
velocity30: {
sql: `${totalQuantity} / 30`,
type: `number`,
format: `number`,
title: `30-Day Velocity`,
rollingWindow: {
trailing: `30 day`
}
},
velocity90: {
sql: `${totalQuantity} / 90`,
type: `number`,
format: `number`,
title: `90-Day Velocity`,
rollingWindow: {
trailing: `90 day`
}
}
},
dimensions: {
id : {
sql: `id`,
type: `string`,
},
orderId : {
sql: `order_id`,
type: `string`,
primaryKey: true,
},
origin : {
sql: `origin`,
type: `string`,
},
price : {
sql: `price`,
type: `number`,
format: `currency`,
},
currency : {
sql: `currency`,
type: `string`,
},
quantity : {
sql: `quantity`,
type: `number`,
},
sku : {
sql: `sku`,
type: `string`,
},
title : {
sql: `title`,
type: `string`,
},
discount : {
sql: `discount`,
type: `number`,
format: `currency`,
},
colour : {
sql: `colour`,
type: `string`,
},
size : {
sql: `size`,
type: `string`,
},
year : {
sql: `year`,
type: `string`,
},
season : {
sql: `season`,
type: `string`,
},
gender : {
sql: `gender`,
type: `string`,
},
commodity : {
sql: `commodity`,
type: `string`,
},
vendor : {
sql: `vendor`,
type: `string`,
},
orderDate : {
sql: `${Orders.orderDate}`,
type: `time`,
shown: false,
},
},
});
cube(`Refunds`, {
sql: refundUnion(),
joins: {
Orders: {
relationship: `belongsTo`,
sql: `${Refunds}.order_id = ${Orders}.id`
},
RefundLineItems: {
relationship: `hasMany`,
sql: `${Refunds}.id = ${RefundLineItems}.refund_id`
}
},
measures: {
total: {
sql: `${amount}`,
type: `sum`,
format: `currency`,
},
},
dimensions: {
id: {
sql: `id`,
type: `string`,
primaryKey: true,
},
orderId : {
sql: `order_id`,
type: `string`,
primaryKey: true,
},
amount: {
sql: `${RefundLineItems.refunded}`,
type: `number`,
format: `currency`,
subQuery: true,
},
refundDate: {
sql: `refund_date`,
type: `time`
},
},
});
cube(`RefundLineItems`, {
sql: refundLineItemsUnion(),
joins: {
Refunds: {
sql: `${RefundLineItems}.refund_id = ${Refunds}.id`,
relationship: `belongsTo`,
}
},
dimensions: {
id: {
type: `string`,
sql: `id`,
},
refund: {
sql: `refund_id`,
type: `string`,
primaryKey: true,
},
title: {
sql: `title`,
type: `string`,
},
price: {
sql: `price`,
type: `number`,
format: `currency`,
},
restockType: {
sql: `restock_type`,
type: `string`,
},
currency: {
sql: `currency`,
type: `string`,
},
total: {
sql: `price`,
type: `number`,
format: `currency`,
},
date: {
sql: `${Refunds.refundDate}`,
type: `time`,
},
},
measures: {
count: {
type: `count`,
},
refunded: {
sql: `total`,
type: `sum`,
format: `currency`,
}
},
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment