Skip to content

Instantly share code, notes, and snippets.

@NazarenkoDenis

NazarenkoDenis/schema.json

Created January 3, 2025 11:55
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save NazarenkoDenis/4cfaa0aea8678675566a6e54618cd780 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save NazarenkoDenis/4cfaa0aea8678675566a6e54618cd780 to your computer and use it in GitHub Desktop.
Download ZIP
schema.json
Raw
schema.json
{
"explores": [
{
"lineageID": "1fe426dd-c1f3-3d7c-af6b-22b233fa9aa4",
"relationships": [
{
"lineageID": "dea0031f-5c51-40e4-808d-02cdeb8b378f",
"isRemoved": true
},
{
"lineageID": "b0b21fc0-7395-42ce-9658-816ac56d85d5",
"from": "fulfillment_items.fulfillment_id",
"to": "fulfillments.id"
}
]
},
{
"lineageID": "fb25cba2-4703-3d80-a4a3-89e3df978800",
"relationships": [
{
"lineageID": "715a4a3f-2987-4b59-b42b-1cbfa30c9564",
"from": "order_items.id",
"to": "fulfillment_items.id"
}
]
}
],
"tables": [
{
"lineageID": "a68424b6-219b-3c06-b476-18f3e53c7128",
"columns": [
{
"lineageID": "3cd96ff8-2b48-4b8e-931a-85de00d96dea",
"name": "primary_customer_id",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "convert(json_path(customers.meta_fields, 'custom.primarycustomerid'),STRING)",
"label": "PrimaryCustomerID",
"description": "",
"valueFormat": "0.00000",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "7051ebfb-0ca7-4e4e-9755-a2d42f77fde1",
"name": "CID",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "\"S\"+\nconvert(if(\n\tconvert(customers.primary_customer_id, INT) = 0,\n\tcustomers.id,\n\tconvert(customers.primary_customer_id, INT)\n),STRING)",
"label": "Customer ID (Formatted)",
"description": "",
"valueFormat": "default",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "38cabfab-e98e-45a6-8c16-a7e28abd155a",
"name": "c7CustomerID",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "json_path(customers.meta_fields, 'custom.commerce7id')",
"label": "C7 Customer ID",
"description": "Commerce7 Customer Identifier",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "82983a6d-eabc-4529-ae38-cec767af3cd6",
"name": "customer_admin_url",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "'https://admin.shopify.com/store/apnee-sante.myshopify.com/customers/' + convert(customers.id, STRING)",
"label": "",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "9f4fff49-ded7-4c2b-85b4-817643040903",
"name": "customer_name_id",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": " customers.CID + \" \" + customers.name",
"label": "Customer Name (Formatted)",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "e290480d-5be0-4563-862c-789e02e9d30e",
"name": "Commerce7Notes",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "convert(json_path(customers.meta_fields, 'custom.commerce7_notes'),STRING)",
"label": "Commerce7 Notes",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "d25d6dbe-37be-3b8f-aa3b-b21d652f1a95",
"isHidden": false
}
],
"datasource": {}
},
{
"lineageID": "4ad4189b-e74c-4d8e-8030-48970291f966",
"isHidden": false,
"columns": [
{
"lineageID": "69c218be-0782-4311-9698-fd416b4f7dc2",
"isRemoved": false
},
{
"lineageID": "6b128b05-368d-496a-9dfe-23984ba0d3a2",
"isRemoved": false
},
{
"lineageID": "1299640c-4c09-42ef-8646-cdd28f46ff92",
"isRemoved": false
},
{
"lineageID": "2c0c6b11-e374-47ce-a420-c3ab4a8bf930",
"isRemoved": false
},
{
"lineageID": "6da2ea4e-3682-4231-8056-edb1aefff2f4",
"isRemoved": false
},
{
"lineageID": "6b3f9826-91a6-4ddd-864b-01a45d821b0c",
"isRemoved": false
},
{
"lineageID": "d65a8837-216b-4cd7-b0f5-0cbb43945115",
"isRemoved": false
},
{
"lineageID": "47290004-c9db-4005-bdf8-1fdcf0f47f8d",
"isRemoved": false
},
{
"lineageID": "0892f1c4-096d-4463-94f3-d725d5afbecf",
"isRemoved": false
},
{
"lineageID": "a2994370-8bcc-4f9d-ae3a-7810c50fde31",
"isRemoved": false
},
{
"lineageID": "80ed1110-de41-47ae-827d-7f7bebf99f8c",
"isRemoved": false
},
{
"lineageID": "017bcc87-9374-48d8-8491-36437a465df7",
"isRemoved": false
},
{
"lineageID": "e89b0768-1c3e-4efd-853f-1c7db3bb138e",
"name": "qty",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "\"left_join(order_items,fulfillment_items,order_items.id,fulfillment_items.id)\"",
"label": "Fulfilled",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "d3d311b8-1f1e-44b6-a3ec-9b99582f5af4",
"name": "qtyfulfilled",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": " if(fulfillment_items.id=order_items.id,fulfillment_items.quantity,0)",
"label": "Qty",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "1996c274-61f2-4ef7-89e3-42fd5aa5145b",
"name": "custom",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "",
"label": "Please check your emails - [email protected] and [email protected] for our responses",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "ad11a52e-4012-44fc-9fee-42f46bbc3ab0",
"isHidden": false
}
],
"datasource": {}
},
{
"lineageID": "0203f35e-9f8c-34a0-b813-bb5f0a401209",
"label": "Order Lines",
"columns": [
{
"lineageID": "8f2497da-274b-49c1-8665-c1198e6f75c1",
"name": "fulfilledqty",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": " sum(if(and(fulfillment_items.order_id=order_items.order_id,fulfillment_items.product_id=order_items.product_id),fulfillment_items.quantity,0))",
"label": "Qty Fulfilled",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "629d4e9e-21d3-483d-be99-db727f06d762",
"name": "Subtotal",
"kind": "measure",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "order_items.total_gross_sales - refund_items.total_subtotal_amount",
"label": "SubTotal",
"description": "Order Gross sales minus refunds",
"valueFormat": "money",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "250a4794-0838-4768-9eaa-b398638de52d",
"name": "total_fulfilled_quantity",
"kind": "measure",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "order_items.total_net_quantity - order_items.total_fulfillable_quantity",
"label": "Fulfilled Quantity",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "adeabfb0-7dd3-484e-b78e-91549a4099d6",
"name": "delivery_remainder",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": " order_items.total_net_quantity - order_items.total_fulfilled_quantity",
"label": "Delivery Remainder",
"description": "",
"valueFormat": "default",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "d7c1dc7f-0ad7-479a-a566-bffcb46d9c0f",
"name": "shipping_total",
"kind": "measure",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": " order_items.total_shipping_price - order_adjustments.shipping_amount",
"label": "Shipping Total",
"description": "",
"valueFormat": "money",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "2fb7ad96-aa3b-4716-a0a6-0de266936522",
"name": "financial_date",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": " if(transactions.processed_at_date = \"\",left(convert(orders.processed_at,STRING),10),left(convert(transactions.processed_at,STRING),10))",
"label": "Financial Date",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "1d79098e-4cd1-4938-b3d8-f5f56aa263cf",
"name": "UnfulfilledItems",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "order_items.sum_x(order_items.total_net_quantity - order_items.total_fulfilled_quantity)",
"label": "Unfulfilled Items",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "dbefcc1a-e0b3-4d2d-9120-41198db4da1f",
"name": "offering_name",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "case(\n convert(json_path(orders.offering_items, '0.LineId'), INT) = order_items.id, json_path(orders.offering_items, '0.OfferingName'),\n convert(json_path(orders.offering_items, '1.LineId'), INT) = order_items.id, json_path(orders.offering_items, '1.OfferingName'),\n convert(json_path(orders.offering_items, '2.LineId'), INT) = order_items.id, json_path(orders.offering_items, '2.OfferingName'),\n convert(json_path(orders.offering_items, '3.LineId'), INT) = order_items.id, json_path(orders.offering_items, '3.OfferingName'),\n convert(json_path(orders.offering_items, '4.LineId'), INT) = order_items.id, json_path(orders.offering_items, '4.OfferingName'),\n convert(json_path(orders.offering_items, '5.LineId'), INT) = order_items.id, json_path(orders.offering_items, '5.OfferingName'),\n convert(json_path(orders.offering_items, '6.LineId'), INT) = order_items.id, json_path(orders.offering_items, '6.OfferingName'),\n convert(json_path(orders.offering_items, '7.LineId'), INT) = order_items.id, json_path(orders.offering_items, '7.OfferingName'),\n convert(json_path(orders.offering_items, '8.LineId'), INT) = order_items.id, json_path(orders.offering_items, '8.OfferingName'),\n convert(json_path(orders.offering_items, '9.LineId'), INT) = order_items.id, json_path(orders.offering_items, '9.OfferingName'),\n convert(json_path(orders.offering_items, '10.LineId'), INT) = order_items.id, json_path(orders.offering_items, '10.OfferingName'),\n convert(json_path(orders.offering_items, '11.LineId'), INT) = order_items.id, json_path(orders.offering_items, '11.OfferingName'),\n convert(json_path(orders.offering_items, '12.LineId'), INT) = order_items.id, json_path(orders.offering_items, '12.OfferingName'),\n convert(json_path(orders.offering_items, '13.LineId'), INT) = order_items.id, json_path(orders.offering_items, '13.OfferingName'),\n convert(json_path(orders.offering_items, '14.LineId'), INT) = order_items.id, json_path(orders.offering_items, '14.OfferingName'),\n convert(json_path(orders.offering_items, '15.LineId'), INT) = order_items.id, json_path(orders.offering_items, '15.OfferingName'),\n convert(json_path(orders.offering_items, '16.LineId'), INT) = order_items.id, json_path(orders.offering_items, '16.OfferingName'),\n convert(json_path(orders.offering_items, '17.LineId'), INT) = order_items.id, json_path(orders.offering_items, '17.OfferingName'),\n convert(json_path(orders.offering_items, '18.LineId'), INT) = order_items.id, json_path(orders.offering_items, '18.OfferingName'),\n convert(json_path(orders.offering_items, '19.LineId'), INT) = order_items.id, json_path(orders.offering_items, '19.OfferingName'),\n convert(json_path(orders.offering_items, '20.LineId'), INT) = order_items.id, json_path(orders.offering_items, '20.OfferingName'),\n convert(json_path(orders.offering_items, '21.LineId'), INT) = order_items.id, json_path(orders.offering_items, '21.OfferingName'),\n convert(json_path(orders.offering_items, '22.LineId'), INT) = order_items.id, json_path(orders.offering_items, '22.OfferingName'),\n convert(json_path(orders.offering_items, '23.LineId'), INT) = order_items.id, json_path(orders.offering_items, '23.OfferingName'),\n convert(json_path(orders.offering_items, '24.LineId'), INT) = order_items.id, json_path(orders.offering_items, '24.OfferingName'),\n convert(json_path(orders.offering_items, '25.LineId'), INT) = order_items.id, json_path(orders.offering_items, '25.OfferingName'),\n convert(json_path(orders.offering_items, '26.LineId'), INT) = order_items.id, json_path(orders.offering_items, '26.OfferingName'),\n convert(json_path(orders.offering_items, '27.LineId'), INT) = order_items.id, json_path(orders.offering_items, '27.OfferingName'),\n convert(json_path(orders.offering_items, '28.LineId'), INT) = order_items.id, json_path(orders.offering_items, '28.OfferingName'),\n convert(json_path(orders.offering_items, '29.LineId'), INT) = order_items.id, json_path(orders.offering_items, '29.OfferingName'),\n)\n",
"label": "",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "6d740bd1-4522-4cd7-8ceb-76fa5eada406",
"name": "order_bottles_purchased",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": " if(or(orders.Order_Type=\"Test\",orders.Order_Type=\"test\",orders.Order_Type=\"Legacy Membership\"),0,products.membership_bottles) * (order_items.total_net_quantity - refund_items.quantity)",
"label": "Bottles Purchased",
"description": "",
"valueFormat": "default",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "011379fc-4758-4450-a219-011b9999fc54",
"name": "membership_bottles_sent",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "if(and(orders.Order_Type=\"Membership Shipment\",products.product_type\u003c\u003e\"Shipment Inclusion\"),order_items.quantity-refund_items.quantity,0)",
"label": "Member Bottles Sent",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "caa7c06c-9ca9-4bac-8290-d556114e0ee7",
"name": "membership_amount_collected",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "if(order_items.order_bottles_purchased\u003c\u003e0,order_items.Subtotal,0)",
"label": "Member Amount Collected",
"description": "",
"valueFormat": "money",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "e5939038-d458-4a81-aa3b-8af24833b91d",
"name": "membership_bottles_fulfilled",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "if(and(orders.Order_Type=\"Membership Shipment\",products.product_type\u003c\u003e\"Shipment Inclusion\"),(order_items.total_quantity - refund_items.total_quantity)-order_items.fulfillable_quantity,0)",
"label": "Member Bottles Fulfilled",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "2381cf76-fb74-4e16-997d-2aaf599af4ef",
"name": "membership_unfulfilled_bottles",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "if(and(orders.Order_Type=\"Membership Shipment\",products.product_type\u003c\u003e\"Shipment Inclusion\"), (order_items.quantity- refund_items.total_quantity)-order_items.fulfilledqty,0)",
"label": "Member Unfulfilled Bottles",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "41b0eedb-23c1-4cec-8486-41c094cf09c3",
"name": "membership_discount",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "if(order_items.order_bottles_purchased\u003c\u003e0,order_items.discount,0)",
"label": "Member Discount",
"description": "",
"valueFormat": "money",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
}
],
"datasource": {}
},
{
"lineageID": "da2189f5-7013-34c2-a50d-bb9676477265",
"columns": [
{
"lineageID": "24367733-82cb-4c4d-89f0-53c835bb6c61",
"name": "allocation_date",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": true,
"expression": "json_path(orders.meta_fields, 'custom.allocation_date')",
"label": "Allocation Date",
"description": "Order allocation date metafield",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "ef30b754-7bed-48ac-b955-6fb804440e24",
"name": "Legacy_Order_Date",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.meta_fields, 'custom.legacy_order_date')",
"label": "Legacy Order Date",
"description": "Legacy Order Date metafield",
"valueFormat": "default",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "4da1c8c7-9efb-4c64-947e-54283aeea0ac",
"name": "Order_Type",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.meta_fields, 'custom.ordertype')",
"label": "Order Type",
"description": "Order Type metafield",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "e5d6a923-b5c4-4a83-8158-830044545719",
"name": "Membership_Tier",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.meta_fields, 'custom.membership_tier')",
"label": "Membership Tier",
"description": "membership tier metafield",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "fcb40bb6-dd11-4aac-8aac-35d5d020952c",
"name": "order_admin_url",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "'https://admin.shopify.com/store/apnee-sante.myshopify.com/orders/' + convert(orders.id, STRING)",
"label": "",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "f94bf997-1446-4968-8047-868be396bab6",
"name": "customer_birthday",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.note_attributes, \"#(name=\"\"customer_birthday\"\").value\")",
"label": "Customer Birthday",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "a0e3ef3a-a15e-42a8-bc38-db92766710ff",
"name": "compliance_check_performed_on",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.note_attributes, \"#(name=\"\"age_verification_performed_on\"\").value\")",
"label": "Compliance Checked",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "a6548b05-424d-4a08-94cb-8f5ee6df4035",
"name": "allocation_cycle",
"kind": "dimension",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.meta_fields, 'custom.allocation_cycle')",
"label": "Allocation Cycle",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "6616b495-9f84-4f81-85d1-2c4d03756e03",
"name": "sc_tags",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "str_replace(orders.tags,\", \",\"| \")",
"label": "SC Tags",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "427bd6b8-e40a-4532-ada0-1140cb2f16d8",
"name": "ordertype",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.meta_fields, \"custom.ordertype\")",
"label": "Type",
"description": "",
"valueFormat": "default",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "912dde60-b451-4c2c-a627-6922d2b2de86",
"name": "offering_line_id",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.meta_fields, \"custom.offering_items\")",
"label": "Line ID",
"description": "",
"valueFormat": "default",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "5bbcb8b4-9ef4-4f27-af9f-80304baa6271",
"name": "line_id",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "str_replace(\nstr_replace(\norders.offering_line_id, \"[\", \"\"),\n\"]\",\"\"\n)",
"label": "Line ID",
"description": "",
"valueFormat": "default",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "941ac3b6-9a0e-4934-a1f6-fa4b7f487642",
"name": "line_id_new",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.line_id, \"LineId\")",
"label": "Line ID",
"description": "",
"valueFormat": "default",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "43c4a57c-42be-448a-950c-5e56a8eba571",
"name": "offering_items",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "json_path(orders.meta_fields, 'custom.offering_items')",
"label": "",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "c422fd85-6eb2-4ead-b6e6-238ed0a00631",
"name": "test",
"kind": "dimension",
"dataType": "",
"isUnique": false,
"isHidden": false,
"expression": "'123'",
"label": "",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "5ed1ca50-7650-4f39-ae8f-f8aea2eb3e2e",
"name": "order_bottles_purchased",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "order_items.sum_x(order_items.order_bottles_purchased)",
"label": "Member Bottles Purchased",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "ce45456b-e8b4-4d7a-9619-08434c238927",
"name": "membership_bottles_sent",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "order_items.sum_x(order_items.membership_bottles_sent)*-1",
"label": "Member Bottles Sent",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "2ba9d5ab-6125-44e4-a10e-0d3e3e63ad30",
"name": "membership_amount_collected",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "order_items.sum_x(order_items.membership_amount_collected)",
"label": "Member Amount Collected",
"description": "",
"valueFormat": "money",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "50a76bc9-f042-4807-bf1c-0a5b5ea549e4",
"name": "membership_price_per_bottle",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "case(\n(order_items.sum_x(order_items.order_bottles_purchased)+(order_items.sum_x(order_items.membership_bottles_sent)*-1))\u003e0,order_items.sum_x(order_items.membership_amount_collected) / order_items.sum_x(order_items.order_bottles_purchased),\n(order_items.sum_x(order_items.order_bottles_purchased)+(order_items.sum_x(order_items.membership_bottles_sent)*-1))=0,order_items.sum_x(order_items.membership_amount_collected) / order_items.sum_x(order_items.order_bottles_purchased),\n(order_items.sum_x(order_items.order_bottles_purchased)+(order_items.sum_x(order_items.membership_bottles_sent)*-1))\u003c0,order_items.sum_x(order_items.membership_amount_collected) / order_items.sum_x(order_items.membership_bottles_sent))",
"label": "Member Price Per Bottle",
"description": "",
"valueFormat": "money",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "708afce2-8032-4e25-8fac-2121d3e5cf60",
"name": "membership_remaining_balance",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "order_items.sum_x(order_items.membership_amount_collected)+((order_items.sum_x(order_items.membership_amount_collected) / if((order_items.sum_x(order_items.order_bottles_purchased)+(order_items.sum_x(order_items.membership_bottles_sent)*-1))\u003c0,order_items.sum_x(order_items.membership_bottles_sent),order_items.sum_x(order_items.order_bottles_purchased))*(order_items.sum_x(order_items.membership_bottles_fulfilled)*-1))",
"label": "Member Remaining Balance",
"description": "",
"valueFormat": "money",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "00deb2d5-98f0-4f63-b0f4-fbcb4584a5a4",
"name": "membership_pending_balance",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "order_items.sum_x(order_items.membership_amount_collected)+((order_items.sum_x(order_items.membership_amount_collected) / if((order_items.sum_x(order_items.order_bottles_purchased)+(order_items.sum_x(order_items.membership_bottles_sent)*-1))\u003c0,order_items.sum_x(order_items.membership_bottles_sent),order_items.sum_x(order_items.order_bottles_purchased))*(order_items.sum_x(order_items.membership_unfulfilled_bottles)*-1))",
"label": "Member Pending Balance",
"description": "",
"valueFormat": "money",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "a0519001-2505-44e5-b0f7-305cde86809c",
"name": "membership_bottles_total",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "order_items.sum_x(order_items.order_bottles_purchased)+(order_items.sum_x(order_items.membership_bottles_sent)*-1)",
"label": "Member Bottle Total",
"description": "",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
},
{
"lineageID": "95f1713a-7ab4-4b3a-a32d-d7f35e2a3b8c",
"name": "membership_status",
"kind": "measure",
"dataType": "string",
"isUnique": false,
"isHidden": false,
"expression": "case(\nand(int(orders.order_bottles_purchased)=0,int(orders.membership_bottles_sent)=0,int(orders.membership_pending_balance)=0,int(orders.membership_bottles_total)=0),'Inactive','Active')",
"label": "Membership Status",
"description": "Determines the status of membership based on the provided values",
"valueFormat": "",
"labelShort": "",
"groupLabel": "",
"requiredFields": [],
"drillColumns": [],
"aliases": [],
"timeframes": []
}
]
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment