Customer Analytics SPARQL Dashboard

Each card below represents an example query from /Users/kidehen/Documents/Database Scripts /Queries/SPARQL/Sales Reports/customer-analysis.rq. Click the button to run it against http://oplussol10.intranet.openlinksw.com:8893/sparql and see the live results rendered by the SPARQL service.

Query 1 · Most Recent Order Date (Basic)

Run query

QUERY 1: Most Recent Order Date (Basic) ============================================================================== Finds companies whose most recent order was 2+ years ago Use Case: Identify inactive customers for re-engagement

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>

SELECT DISTINCT ?company ?mostRecentOrderDate
WHERE {
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  FILTER (?mostRecentOrderDate <= (NOW() - "P2Y"^^xsd:duration))
}
ORDER BY ?mostRecentOrderDate
LIMIT 10 ;
        

Query 1 · Company Name Insight

Run query

Tweak that adds company name, and for most recent 5 years

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
  PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
  PREFIX schema:        <http://schema.org/>

  SELECT DISTINCT ?company ?companyName ?mostRecentOrderDate round(?daysSinceLastOrder) as ?daysSinceLastOrder
  WHERE {
    {
      SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
      WHERE {
        ?order a ecrm-ontology:Order ;
               ecrm-ontology:hasCompany ?company ;
               ecrm-ontology:orderDate ?orderDate .

        FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
      }
      GROUP BY ?company
    }

    FILTER (?mostRecentOrderDate <= (NOW() - "P5Y"^^xsd:duration))

    OPTIONAL { ?company schema:name|rdfs:label ?companyName }

    BIND((NOW() - ?mostRecentOrderDate) AS ?daysSinceLastOrder)
  }
  ORDER BY DESC(?mostRecentOrderDate)
  LIMIT 20 ;


## ==============================================================================
        

Query 2 · Order Count for Dormant Customers

Run query

QUERY 2: Most Recent Order Date with Order Count ============================================================================== Adds total order count to identify high-value dormant customers Use Case: Prioritize re-engagement by customer lifetime value

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>

SELECT ?company ?mostRecentOrderDate ?totalOrders
WHERE {
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate) (COUNT(DISTINCT ?order) AS ?totalOrders)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  FILTER (?mostRecentOrderDate <= (NOW() - "P2Y"^^xsd:duration))
}
ORDER BY DESC(?totalOrders) ?mostRecentOrderDate
LIMIT 10 ;


## ==============================================================================
        

Query 2A · Customer Lifetime Analysis

Run query

QUERY 2A: With Oldest Order Date - Customer Lifetime Analysis ============================================================================== Includes both oldest and most recent order dates to show customer lifetime Use Case: Understand customer tenure and engagement span

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>

SELECT ?company ?oldestOrderDate ?mostRecentOrderDate ?totalOrders ?customerLifetimeYears
WHERE {
  {
    SELECT ?company
           (MIN(?orderDate) AS ?oldestOrderDate)
           (MAX(?orderDate) AS ?mostRecentOrderDate)
           (COUNT(DISTINCT ?order) AS ?totalOrders)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  FILTER (?mostRecentOrderDate <= (NOW() - "P2Y"^^xsd:duration))

  # Calculate customer lifetime in years
  BIND((?mostRecentOrderDate - ?oldestOrderDate) / 365.25 AS ?customerLifetimeYears)
}
ORDER BY DESC(?totalOrders) ?mostRecentOrderDate
LIMIT 50 ;


## ==============================================================================
        

Query 2B · Executive Lifetime Metrics

Run query

QUERY 2B: Comprehensive Customer Lifetime with Details ============================================================================== Full customer analysis with first/last order, tenure, and company details Use Case: Executive dashboard showing complete customer lifecycle metrics

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?company ?companyName
       ?oldestOrderDate ?mostRecentOrderDate
       ?totalOrders
       (ROUND(?customerLifetimeYears * 10) / 10 AS ?lifetimeYears)
       (ROUND(?daysSinceLastOrder) AS ?daysSinceLastOrder)
       (ROUND(?avgOrdersPerYear * 10) / 10 AS ?avgOrdersPerYear)
WHERE {
  {
    SELECT ?company
           (MIN(?orderDate) AS ?oldestOrderDate)
           (MAX(?orderDate) AS ?mostRecentOrderDate)
           (COUNT(DISTINCT ?order) AS ?totalOrders)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  FILTER (?mostRecentOrderDate <= (NOW() - "P2Y"^^xsd:duration))

  # Get company name
  OPTIONAL { ?company schema:name|rdfs:label ?companyName }

  # Calculate metrics
  BIND((?mostRecentOrderDate - ?oldestOrderDate) / 365.25 AS ?customerLifetimeYears)
  BIND((NOW() - ?mostRecentOrderDate) AS ?daysSinceLastOrder)
  BIND(
    IF(?customerLifetimeYears > 0,
       ?totalOrders / ?customerLifetimeYears,
       0)
    AS ?avgOrdersPerYear
  )
}
ORDER BY DESC(?totalOrders) ?mostRecentOrderDate
LIMIT 50 ;


## ==============================================================================
        

Query 2C · Aggregated Sample Orders

Run query

QUERY 2C: Aggregated with Sample Order URI (Best of Both) ============================================================================== Aggregated view with a sample order URI for drill-down capability Use Case: Summary metrics with ability to click through to an example order

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?company ?companyName
       ?oldestOrderDate ?mostRecentOrderDate
       ?totalOrders
       ?sampleOrder
WHERE {
  {
    SELECT ?company
           (MIN(?orderDate) AS ?oldestOrderDate)
           (MAX(?orderDate) AS ?mostRecentOrderDate)
           (COUNT(DISTINCT ?order) AS ?totalOrders)
           (SAMPLE(?order) AS ?sampleOrder)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  FILTER (?mostRecentOrderDate <= (NOW() - "P2Y"^^xsd:duration))

  # Get company name
  OPTIONAL { ?company schema:name|rdfs:label ?companyName }
}
ORDER BY DESC(?totalOrders) ?mostRecentOrderDate
LIMIT 50 ;


## ==============================================================================
        

Query 2D · All Orders for Dormant Customers

Run query

QUERY 2D: Individual Orders for Inactive Customers (Full Drill-Down) ============================================================================== Shows all individual orders for companies with no orders in the last 2 years Use Case: Click on ?order URIs to drill down into order details

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?company ?companyName ?order ?orderDate ?orderNumber
WHERE {
  # Subquery to find inactive companies
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
    HAVING (MAX(?orderDate) <= (NOW() - "P2Y"^^xsd:duration))
  }

  # Get all orders for these inactive companies
  ?order a ecrm-ontology:Order ;
         ecrm-ontology:hasCompany ?company ;
         ecrm-ontology:orderDate ?orderDate .

  # Get company name
  OPTIONAL { ?company schema:name|rdfs:label ?companyName }

  # Get order number if available
  OPTIONAL { ?order ecrm-ontology:hasOrderNumber ?orderNumber }

  FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
}
ORDER BY ?company DESC(?orderDate)
LIMIT 200 ;


## ==============================================================================
        

Query 2E · Orders with Items

Run query

QUERY 2E: Orders with Items - Product Trend Analysis ============================================================================== Shows orders with item details for inactive customers Use Case: Analyze what products inactive customers were buying

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>

SELECT DISTINCT ?company ?companyName ?order ?orderDate
       ?orderItem ?itemLabel
       (YEAR(?orderDate) AS ?orderYear)
WHERE {
  # Subquery to find inactive companies
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
    HAVING (MAX(?orderDate) <= (NOW() - "P2Y"^^xsd:duration))
  }

  # Get all orders for these inactive companies
  ?order a ecrm-ontology:Order ;
         ecrm-ontology:hasCompany ?company ;
         ecrm-ontology:hasOrderItem ?orderItem ;
         ecrm-ontology:orderDate ?orderDate .

  # Get order item label
  OPTIONAL { ?orderItem schema:name|rdfs:label ?itemLabel }

  # Get company name
  OPTIONAL { ?company schema:name|rdfs:label ?companyName }

  FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
}
ORDER BY ?company DESC(?orderDate)
LIMIT 300 ;


## ==============================================================================
        

Query 2F · Product Trends by Year

Run query

QUERY 2F: Product Trends by Year for Inactive Customers ============================================================================== Aggregates items by year to show purchasing trends over time Use Case: Identify which products inactive customers bought most in each year

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?orderYear ?itemLabel
       (COUNT(DISTINCT ?orderItem) AS ?itemCount)
       (COUNT(DISTINCT ?company) AS ?customerCount)
WHERE {
  # Subquery to find inactive companies
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
    HAVING (MAX(?orderDate) <= (NOW() - "P2Y"^^xsd:duration))
  }

  # Get all orders for these inactive companies
  ?order a ecrm-ontology:Order ;
         ecrm-ontology:hasCompany ?company ;
         ecrm-ontology:hasOrderItem ?orderItem ;
         ecrm-ontology:orderDate ?orderDate .

  # Get order item label
  ?orderItem schema:name|rdfs:label ?itemLabel .

  FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))

  BIND(YEAR(?orderDate) AS ?orderYear)
}
GROUP BY ?orderYear ?itemLabel
ORDER BY DESC(?orderYear) DESC(?itemCount)
LIMIT 200 ;


## ==============================================================================
        

Query 2G · Purchase History Timeline

Run query

QUERY 2G: Customer Purchase History with Items (Timeline View) ============================================================================== Shows complete purchase timeline for each inactive customer with items Use Case: See what each customer bought over their lifetime, chronologically

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>

SELECT DISTINCT ?company ?companyName
       ?orderDate (YEAR(?orderDate) AS ?year)
       ?order ?orderItem ?itemLabel
       ?quantity ?price
WHERE {
  # Subquery to find inactive companies
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
    HAVING (MAX(?orderDate) <= (NOW() - "P2Y"^^xsd:duration))
  }

  # Get all orders for these inactive companies
  ?order a ecrm-ontology:Order ;
         ecrm-ontology:hasCompany ?company ;
         ecrm-ontology:hasOrderItem ?orderItem ;
         ecrm-ontology:orderDate ?orderDate .

  # Get company name
  OPTIONAL { ?company schema:name|rdfs:label ?companyName }

  # Get order item label
  OPTIONAL { ?orderItem schema:name|rdfs:label ?itemLabel }

  # Get item quantity and price if available
  OPTIONAL { ?orderItem ecrm-ontology:hasQuantity ?quantity }
  OPTIONAL { ?orderItem ecrm-ontology:hasPrice ?price }

  FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
}
ORDER BY ?company DESC(?orderDate)
LIMIT 500 ;


## ==============================================================================
        

Query 2H · Top Products Summary

Run query

QUERY 2H: Top Products for Inactive Customers (Summary) ============================================================================== Shows which products are most common among inactive customers Use Case: Identify products that may need attention or improved support

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>

SELECT ?itemLabel
       (COUNT(DISTINCT ?company) AS ?inactiveCustomerCount)
       (COUNT(DISTINCT ?orderItem) AS ?totalItemsSold)
       (MIN(?orderDate) AS ?firstSold)
       (MAX(?orderDate) AS ?lastSold)
WHERE {
  # Subquery to find inactive companies
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
    HAVING (MAX(?orderDate) <= (NOW() - "P2Y"^^xsd:duration))
  }

  # Get all orders for these inactive companies
  ?order a ecrm-ontology:Order ;
         ecrm-ontology:hasCompany ?company ;
         ecrm-ontology:hasOrderItem ?orderItem ;
         ecrm-ontology:orderDate ?orderDate .

  # Get order item label
  ?orderItem schema:name|rdfs:label ?itemLabel .

  FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
}
GROUP BY ?itemLabel
ORDER BY DESC(?inactiveCustomerCount) DESC(?totalItemsSold)
LIMIT 100 ;


## ==============================================================================
        

Query 3 · Inactive Customers with Details

Run query

QUERY 3: Inactive Customers with Company Details ============================================================================== Includes company name and calculates days since last order Use Case: Generate detailed customer retention reports

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>

SELECT DISTINCT ?company ?companyName ?mostRecentOrderDate ?daysSinceLastOrder
WHERE {
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  FILTER (?mostRecentOrderDate <= (NOW() - "P2Y"^^xsd:duration))

  OPTIONAL { ?company schema:name ?companyName }
  OPTIONAL { ?company rdfs:label ?companyName }

  BIND((NOW() - ?mostRecentOrderDate) AS ?daysSinceLastOrder)
}
ORDER BY ?mostRecentOrderDate
LIMIT 10 ;


## ==============================================================================
        

Query 4 · One-Year Threshold

Run query

QUERY 4: Configurable Time Period - 1 Year ============================================================================== Same as Query 1 but for 1 year inactivity threshold Use Case: Earlier intervention for at-risk customers

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>

SELECT DISTINCT ?company ?mostRecentOrderDate
WHERE {
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  # Change duration as needed: P1Y (1 year), P6M (6 months), P90D (90 days)
  FILTER (?mostRecentOrderDate <= (NOW() - "P1Y"^^xsd:duration))
}
ORDER BY ?mostRecentOrderDate
LIMIT 10 ;


## ==============================================================================
        

Query 5 · Six-Month Threshold

Run query

QUERY 5: Configurable Time Period - 6 Months ============================================================================== For more frequent customer health checks

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>

SELECT DISTINCT ?company ?mostRecentOrderDate
WHERE {
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  FILTER (?mostRecentOrderDate <= (NOW() - "P6M"^^xsd:duration))
}
ORDER BY ?mostRecentOrderDate
LIMIT 10 ;


## ==============================================================================
        

Query 6 · Comprehensive Customer Activity

Run query

QUERY 6: Comprehensive Customer Activity Analysis ============================================================================== Includes order count, total revenue, account manager, and customer stage Use Case: Executive dashboard for customer retention

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>
PREFIX rdfs:          <http://www.w3.org/2000/01/rdf-schema#>
PREFIX foaf:          <http://xmlns.com/foaf/0.1/>

SELECT ?company ?companyName ?mostRecentOrderDate
       ?totalOrders ?accountManagerName ?stage
WHERE {
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate) (COUNT(DISTINCT ?order) AS ?totalOrders)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  FILTER (?mostRecentOrderDate <= (NOW() - "P2Y"^^xsd:duration))

  # Get company details
  OPTIONAL { ?company schema:name ?companyName }
  OPTIONAL { ?company rdfs:label ?companyName }

  # Get account manager
  OPTIONAL {
    ?company ecrm-ontology:hasAccountManager ?accountManager .
    ?accountManager foaf:name ?accountManagerName .
  }

  # Get customer lifecycle stage
  OPTIONAL { ?company ecrm-ontology:hasStage ?stage }
}
ORDER BY DESC(?totalOrders) ?mostRecentOrderDate
LIMIT 20 ;


## ==============================================================================
        

Query 7 · At-Risk High-Value Customers

Run query

QUERY 7: At-Risk High-Value Customers ============================================================================== Identifies customers who haven't ordered in 1-2 years but have significant order history Use Case: Targeted re-engagement campaigns for valuable customers

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>

SELECT ?company ?companyName ?mostRecentOrderDate ?totalOrders
WHERE {
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate) (COUNT(DISTINCT ?order) AS ?totalOrders)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  # Between 1 and 2 years since last order
  FILTER (?mostRecentOrderDate <= (NOW() - "P1Y"^^xsd:duration))
  FILTER (?mostRecentOrderDate > (NOW() - "P2Y"^^xsd:duration))

  # High-value customers (10+ orders)
  FILTER (?totalOrders >= 10)

  OPTIONAL { ?company schema:name ?companyName }
}
ORDER BY DESC(?totalOrders) ?mostRecentOrderDate
LIMIT 10 ;


## ==============================================================================
        

Query 8 · RFM Recency Segments

Run query

QUERY 8: Customer Segmentation by Order Recency ============================================================================== Categorizes all customers by how recently they ordered Use Case: RFM (Recency, Frequency, Monetary) analysis

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>
PREFIX schema:        <http://schema.org/>

SELECT ?company ?companyName ?mostRecentOrderDate ?totalOrders ?recencySegment
WHERE {
  {
    SELECT ?company (MAX(?orderDate) AS ?mostRecentOrderDate) (COUNT(DISTINCT ?order) AS ?totalOrders)
    WHERE {
      ?order a ecrm-ontology:Order ;
             ecrm-ontology:hasCompany ?company ;
             ecrm-ontology:orderDate ?orderDate .

      FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
    }
    GROUP BY ?company
  }

  OPTIONAL { ?company schema:name ?companyName }

  BIND(
    IF(?mostRecentOrderDate > (NOW() - "P6M"^^xsd:duration), "Active",
    IF(?mostRecentOrderDate > (NOW() - "P1Y"^^xsd:duration), "At Risk",
    IF(?mostRecentOrderDate > (NOW() - "P2Y"^^xsd:duration), "Dormant",
    "Inactive")))
    AS ?recencySegment
  )
}
ORDER BY ?mostRecentOrderDate
LIMIT 100 ;


## ==============================================================================
        

Original Query · Orders Older Than 2 Years

Run query

ORIGINAL QUERY (For Reference) ============================================================================== Returns any order older than 2 years (can return multiple rows per company)

          PREFIX ecrm-ontology: <http://www.openlinksw.com/ontology/ecrm#>
PREFIX xsd:           <http://www.w3.org/2001/XMLSchema#>

SELECT DISTINCT ?company ?orderDate
WHERE {
  ?order a ecrm-ontology:Order ;
         ecrm-ontology:hasCompany ?company ;
         ecrm-ontology:orderDate ?orderDate .

  FILTER (datatype(?orderDate) IN (xsd:date, xsd:dateTime))
  FILTER (?orderDate <= (NOW() - "P2Y"^^xsd:duration))
}
LIMIT 10 ;


## ==============================================================================
## NOTES
## ==============================================================================
#
# Key Differences Between Original and Derivative Queries:
#
# 1. AGGREGATION: Derivative queries use MAX(?orderDate) to find most recent order
# 2. GROUP BY: Groups by company to get one row per company
# 3. DISTINCT COUNTING: Uses COUNT(DISTINCT ?order) to ensure accurate order counts
#    (prevents duplicate counting if order data has redundant triples)
# 4. BUSINESS LOGIC: Most recent order date better identifies truly inactive customers
# 5. ACTIONABILITY: Results are more useful for customer retention efforts
#
# eCRM Ontology Properties Used:
# - ecrm-ontology:Order (class)
# - ecrm-ontology:hasCompany (property linking Order to Company)
# - ecrm-ontology:orderDate (property for order timestamp)
# - ecrm-ontology:hasAccountManager (property linking Company to Account Manager)
# - ecrm-ontology:hasStage (property for customer lifecycle stage)
#
# Duration Syntax (xsd:duration):
# - P2Y = 2 years
# - P1Y = 1 year
# - P6M = 6 months
# - P90D = 90 days
# - P30D = 30 days
#
# Common Use Cases:
# - Customer retention analysis
# - Re-engagement campaign targeting
# - Account health monitoring
# - Churn prediction and prevention
# - Sales pipeline management
#
## ==============================================================================
        

Dashboard generated from customer analysis queries maintained under Sales Reports.