schema.json

{
	"tables": [
		{
			"lineageID": "a68424b6-219b-3c06-b476-18f3e53c7128",
			"columns": [
				{
					"lineageID": "d0f9a019-5cc9-496b-b439-4646641e5e4b",
					"name": "proper_first_name",
					"kind": "dimension",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "left((customers.first_name), 1) + lower(right(customers.first_name, len(customers.first_name) - 1))\n",
					"label": "Proper First Name",
					"description": "",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				}
			],
			"datasource": {}
		},
		{
			"lineageID": "1f371d31-9c9b-35a7-aa8b-a58ee77911a0",
			"columns": [
				{
					"lineageID": "4a6c4bed-37b8-40de-92c0-448e93acc389",
					"name": "total_shipping_amount_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "order_adjustments.sum_x(order_adjustments.shipping_amount / orders.shop_to_presentment_rate)",
					"label": "",
					"description": "",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				}
			],
			"datasource": {}
		},
		{
			"lineageID": "0203f35e-9f8c-34a0-b813-bb5f0a401209",
			"columns": [
				{
					"lineageID": "7a2cd9cb-ec8c-413c-a7e2-b9e8c618874a",
					"name": "num_subs",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "order_items.sum_x(if(orders.pre_paid_sub, \n                  int(dimension(order_items.total_quantity) / order_items.quantity), \n                  int(dimension(order_items.total_quantity))\n  )\n)",
					"label": "",
					"description": "",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				}
			],
			"datasource": {}
		},
		{
			"lineageID": "da2189f5-7013-34c2-a50d-bb9676477265",
			"columns": [
				{
					"lineageID": "245921e6-2ad7-47fc-b47d-f003beb19628",
					"name": "wholesale",
					"kind": "dimension",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "len(regexp_match(orders.name, \"[A-Z]+\")) \u003e 0",
					"label": "",
					"description": "",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "3035f180-7c71-4c50-9e30-2931fa6b0f48",
					"name": "pre_paid_sub",
					"kind": "dimension",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "str_contains(orders.tags, \"PP 1st:\")",
					"label": "",
					"description": "",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "95cf9091-1e80-4712-948c-0b5b98dfe6a7",
					"name": "avg_net_sales",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "orders.total_net_sales/orders.count",
					"label": "",
					"description": "",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "6b36edc8-61cc-45f5-b4ee-1057cdc7debc",
					"name": "sub_month_year",
					"kind": "dimension",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "regexp_match(orders.tags, \"[A-Z][a-z]{2}\\s\\d{2}\")",
					"label": "",
					"description": "",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "42f08a5a-c420-4b34-bd63-32963f250f20",
					"name": "shipping_price_presentment",
					"kind": "dimension",
					"dataType": "float",
					"isUnique": false,
					"isHidden": true,
					"expression": "",
					"label": "",
					"description": "",
					"valueFormat": "money",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "40d24d6f-d5e8-48cc-b7c1-9c3f6e0ac336",
					"name": "total_shipping_price_presentment",
					"kind": "measure",
					"dataType": "string",
					"isUnique": false,
					"isHidden": false,
					"expression": "orders.sum_x(orders.shipping_price / orders.shop_to_presentment_rate)",
					"label": "",
					"description": "",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "17439d98-2e0f-415d-a342-76747be0b0d4",
					"name": "original_currency",
					"kind": "dimension",
					"dataType": "string",
					"isUnique": false,
					"isHidden": false,
					"expression": "",
					"label": "",
					"description": "",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "599cfb17-db57-428e-bf91-8bbdade13286",
					"name": "shop_to_presentment_rate",
					"kind": "dimension",
					"dataType": "float",
					"isUnique": false,
					"isHidden": false,
					"expression": "",
					"label": "",
					"description": "",
					"valueFormat": "0.00000",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "7d59a7b8-2d04-48ad-8639-3093ff31c0de",
					"name": "total_line_items_price_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "orders.sum_x(orders.gross_amount / orders.shop_to_presentment_rate)",
					"label": "Gross Sales / Presentment",
					"description": "The sum of all line item prices in the shop currency.",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "4a694ee9-a213-41bf-939a-5ad9beccfeda",
					"name": "total_discounts_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "orders.sum_x(orders._total_discounts / orders.shop_to_presentment_rate)",
					"label": "Discounts / Presentment",
					"description": "The total discounts applied to the price of the order in the shop currency.",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "3cc714c3-5000-404c-acb4-195cd4764e9a",
					"name": "total_net_sales_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "orders.total_line_items_price_presentment - orders.total_discounts_presentment - refunds.total_gross_amount_presentment",
					"label": "Net Sales / Presentment",
					"description": "",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "8654da85-f823-439d-b0df-bee6d242ed23",
					"name": "total_shipping_amount_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "orders.total_shipping_price_presentment - order_adjustments.total_shipping_amount_presentment",
					"label": "Shipping / Presentment",
					"description": "",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "a823e6a5-b619-4854-8cb2-32756101cb0a",
					"name": "total_tax_amount_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "orders.sum_x(orders._total_tax / orders.shop_to_presentment_rate) - refund_items.total_tax_amount_presentment",
					"label": "Tax / Presentment",
					"description": "",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "8d32def8-5087-4827-bf38-24cab6b20ff9",
					"name": "total_sales_amount_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "orders.total_net_sales_presentment + orders.total_shipping_amount_presentment + orders.total_tax_amount_presentment",
					"label": "Total Sales / Presentment",
					"description": "",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				}
			],
			"datasource": {
				"columns": [
					{
						"lineageID": "28c7acc2-51a4-4fba-ab8d-f95510bbcc05",
						"name": "shipping_price_presentment",
						"dataType": "float",
						"sql": "COALESCE(({{ TABLE }}.total_shipping_price_set-\u003e'presentment_money'-\u003e\u003e'amount')::DECIMAL(38,4), 0)"
					},
					{
						"lineageID": "715a6716-207b-4ce9-bd21-c27b9f7470bc",
						"name": "original_currency",
						"dataType": "string",
						"sql": "{{ TABLE }}.total_shipping_price_set-\u003e'presentment_money'-\u003e\u003e'currency_code'"
					},
					{
						"lineageID": "401361c1-01bb-40b2-bc30-16db1afbe7fb",
						"name": "shop_to_presentment_rate",
						"dataType": "float",
						"sql": "COALESCE(({{ TABLE }}.total_shipping_price_set-\u003e'shop_money'-\u003e\u003e'amount')::DECIMAL(38,4), 0) / COALESCE(({{ TABLE }}.total_shipping_price_set-\u003e'presentment_money'-\u003e\u003e'amount')::DECIMAL(38,4), 0)"
					}
				]
			}
		},
		{
			"lineageID": "ff179673-7d04-31f0-af6d-64f9be050ad8",
			"columns": [
				{
					"lineageID": "a0f32454-fbe7-48c3-b852-4c2ccc219e8e",
					"name": "inv_tag",
					"kind": "dimension",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "case(and(str_contains(products.tags, \"Out of Stock\"), inventory_levels.total_available = 0), \"OoS-0\",\n     and(str_contains(products.tags, \"Out of Stock\"), inventory_levels.total_available \u003e 0), \"OoS-gt0\",\n     and(str_contains(products.tags, \"Inv 1-3\")     , inventory_levels.total_available = 0), \"Inv1to3-0\",\n     and(str_contains(products.tags, \"Inv 1-3\")     , and(inventory_levels.total_available \u003c= 3, inventory_levels.total_available \u003e= 1)), \"Inv1to3-1to3\",\n     and(str_contains(products.tags, \"Inv 1-3\")     , inventory_levels.total_available \u003e 3), \"Inv1to3-gt3\",\n     inventory_levels.total_available \u003c= 3, \"lte3\",\n     \"gt3\"\n)",
					"label": "",
					"description": "Ben created to re-create Inventory levels report",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				}
			],
			"datasource": {}
		},
		{
			"lineageID": "4892effe-4ade-3cdd-97e9-79085fecc8f7",
			"columns": [
				{
					"lineageID": "de2f1d99-d970-4413-a609-827b27d8dae7",
					"name": "total_tax_amount_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "refund_items.sum_x(refund_items._tax_amount / orders.shop_to_presentment_rate)",
					"label": "",
					"description": "",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				}
			],
			"datasource": {}
		},
		{
			"lineageID": "2ba341e1-cded-382d-9528-7c35783678dc",
			"columns": [
				{
					"lineageID": "0f0ee627-c6bf-4541-a2af-7f2787aba5c0",
					"name": "total_gross_amount_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": false,
					"expression": "(refunds._total_gross_amount_presentment - order_adjustments.sum_x(order_adjustments.amount / orders.shop_to_presentment_rate)",
					"label": "Returns / Presentment",
					"description": "",
					"valueFormat": "default",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				},
				{
					"lineageID": "73158b8e-1688-4341-ad09-b26c47d7134f",
					"name": "_total_gross_amount_presentment",
					"kind": "measure",
					"dataType": "",
					"isUnique": false,
					"isHidden": true,
					"expression": "(refund_items.sum_x(refund_items._subtotal_amount / orders.shop_to_presentment_rate) - IF(RELATED(orders.taxes_included), refund_items.total_tax_amount_presentment, 0))",
					"label": "",
					"description": "",
					"valueFormat": "",
					"labelShort": "",
					"groupLabel": "",
					"requiredFields": [],
					"drillColumns": [],
					"aliases": [],
					"timeframes": []
				}
			],
			"datasource": {}
		}
	]
}