dbt_orders.yml

version: 2
models:
  - name: dbt_orders
    description: 'This table contains information on all the confirmed orders and
      their status'
    meta:
      parameters:
        orders_dim_toggle:
          label: "Browser/Partner/Referrer"
          description: "Swap between browser, partner, referrer"
          options:
            - "browser"
            - "referrer"
            - "partner_name"
          default: "partner_name"
          multiple: false
      explores:
        orders_users:
          label: 'Garden of Sweden Orders w/Users'
          description: All orders from Garden of Sweden, plus user info
          sql_filter: ${partner_name} = 'Garden of Sweden'
          joins:
            - join: dbt_users
              relationship: many-to-one
              sql_on: ${dbt_orders.user_id} = ${dbt_users.user_id}
      label: Orders
      group_label: Sales
      required_attributes:
        can_access_orders: "true"
      sql_filter: ${TABLE}.partner_name = ${lightdash.attributes.visible_partners} or
        ${lightdash.attributes.visible_partners} = 'All'
    columns:
      - name: order_id
        description: 'Unique Order ID for the order.'
        meta:
          metrics:
            count_distinct_order_id:
              type: count_distinct
              label: 'Order count'
              description: 'This metric counts unique orders based on the Order ID field from
                the Orders table.'
          dimension:
            type: string
      - name: order_date
        description: 'Timestamp of order placement by user.'
        meta:
          dimension:
            time_intervals:
              [
                'HOUR',
                'MINUTE_OF_HOUR_NUM',
                'HOUR_OF_DAY_NUM',
                'DAY',
                'DAY_OF_WEEK_INDEX',
                'DAY_OF_MONTH_NUM',
                'DAY_OF_YEAR_NUM',
                'DAY_OF_WEEK_NAME',
                'WEEK',
                'WEEK_NUM',
                'MONTH',
                'MONTH_NUM',
                'MONTH_NAME',
                'QUARTER',
                'QUARTER_NUM',
                'QUARTER_NAME',
                'YEAR'
              ]
            sql: >
              timestamp_add(
                ${TABLE}.order_date,
                interval timestamp_diff(current_timestamp(), timestamp '2025-02-13 00:00:00', second) second
              )
            type: timestamp
          additional_dimensions:
            is_sunday:
              description: true if the date is a sunday
              type: boolean
              sql: EXTRACT(DAYOFWEEK FROM ${order_date}) = 1
      - name: partner_id
        description: 'ID of the partner that is fulfilling the order.'
        meta:
          dimension:
            type: string
      - name: partner_name
        description: 'Full name of the partner that is fulfilling the order.'
        meta:
          dimension:
            urls:
              - url: https://analytics.lightdash.cloud/projects/8a223608-a1e4-426e-adcd-0598ea72b9f8/dashboards/3ee80f6e-5cb2-4375-b313-d2ad9126aece/view?filters=%7B"dimensions"%3A%5B%7B"id"%3A"fc5f8c1a-7673-4054-ba00-765d33033bfe"%2C"operator"%3A"equals"%2C"target"%3A%7B"fieldId"%3A"dbt_orders_partner_name"%2C"tableName"%3A"dbt_orders"%2C"fieldName"%3A"partner_name"%7D%2C"disabled"%3Afalse%2C"values"%3A%5B"${
                  value.formatted | url_encode
                  }"%5D%7D%5D%2C"metrics"%3A%5B%5D%2C"tableCalculations"%3A%5B%5D%7D
                label: View partner KPI dashboard
              - url: https://docs.lightdash.com/references/dimensions#urls
                label: View partner account in Salesforce
            colors:
              "Trowels R Us": "#DAF7A6"
              "Garden of Sweden": "#FFC300"
              "Plant Paradise": "#FF5733"
              "Redwood Ranch": "#C70039"
              "Peat, Fruits and Leaves": "#581845"
            type: string
          additional_dimensions:
            orders_dim:
              label: Browser/Partner/Referrer
              type: string
              description: Use this dimension to swap between referrer, partner_name, browser
              sql: |
                case 
                  when ${lightdash.parameters.dbt_orders.orders_dim_toggle} = 'partner_name' then ${partner_name}
                  when ${lightdash.parameters.dbt_orders.orders_dim_toggle} = 'browser' then ${browser}
                  when ${lightdash.parameters.dbt_orders.orders_dim_toggle} = 'referrer' then ${referrer}
                end
            selected_dim:
              label: Selected dimension
              type: string
              description: The dimension that is currently selected in the orders_dim_toggle
                parameter
              sql: ${lightdash.parameters.dbt_orders.orders_dim_toggle}
      - name: partner_commission
        description: 'Commission rate the specified parter charges, as a decimal fraction.'
        meta:
          dimension:
            type: number
      - name: currency
        description: 'Three letter international currency code for the currency the
          order was paid with.'
        meta:
          dimension:
            type: string
      - name: basket_total
        description: 'Sum of item prices for each item within the basket.'
        meta:
          dimension:
            format: 'usd'
            type: number
          metrics:
            sum_of_basket_total:
              type: sum
              format: 'usd'
              description: 'Sums the basket total for orders in your query, grouped by the
                dimensions you have chosen.'
            average_of_basket_total:
              type: average
              format: 'usd'
              description: 'Takes the average of basket total for orders in your query,
                grouped by the dimensions you have chosen.'
      - name: profit
        description: 'Sum of item profits within the basket. Thus, the total profit of
          the order.'
        meta:
          dimension:
            format: 'usd'
            type: number
          metrics:
            sum_of_profit:
              type: sum
              format: 'usd'
              description: 'Sums the profit on all orders in your query, grouped by the
                dimensions you have chosen.'
            profit_average_of_profit:
              label: Average of Profit
              description: "Average of Profit on the table Orders "
              type: average
              format: "[$£]#,##0.00"
              filters: []
      - name: referrer
        description: 'Source from which the user was linked to the Thyme to Shine website.'
        meta:
          dimension:
            type: string
      - name: user_id
        description: 'Unique user ID for the user that placed the order.'
        meta:
          metrics:
            count_distinct_user_id:
              type: count_distinct
              label: 'Total unique users'
              description: 'Counts the number of unique users (customers) with orders based on
                their User ID.'
          dimension:
            type: string
      - name: email
        description: 'Email address of the user that placed the order.'
        meta:
          dimension:
            urls:
              - url: 'mailto:${value.raw}?subject=Hello from Thyme Team!'
                label: Send email
            type: string
      - name: user_created_date
        description: 'Timestamp of user creation.'
        meta:
          dimension:
            sql: >
              timestamp_add(
                ${TABLE}.user_created_date,
                interval timestamp_diff(current_timestamp(), timestamp '2025-02-13 00:00:00', second) second
              )
            type: timestamp
      - name: browser
        description: 'Web browser that the user used to place the order.'
        meta:
          dimension:
            type: string
      - name: shipping_city
        description: 'Shipping city of the user that placed the order.'
        meta:
          dimension:
            type: string
      - name: shipping_country
        description: 'Shipping country of the user that placed the order.'
        meta:
          dimension:
            type: string