Filter and Search Order Data with Kusto Query Language (KQL)

Table of Contents

1. Challenge

   Connect to the Globomantics order data in Azure Data Explorer

   1. Locate the Azure Data Explorer cluster and connect to it in the web UI.
   2. Select the GlobomanticsDB database and confirm the order data is reachable.
   3. Inspect the record count, column schema, and a sample of rows.
2. Challenge

   Narrow the order dataset with where filters

   1. Filter orders with comparison and logical operators, including a date and time range.
   2. Select and exclude groups of values with in and !in.
   3. Identify orders with missing data using isempty, isnotempty, and isnull.
3. Challenge

   Search order text and patterns with string operators

   1. Match substrings, prefixes, and suffixes with contains, startswith, and endswith.
   2. Match whole terms and prefixes with has and hasprefix.
   3. Choose between contains and has based on accuracy and performance.
4. Challenge

   Create conditional fields with iif and case

   1. Add a calculated order value column with extend.
   2. Flag orders with a two-way condition using iif.
   3. Band orders by value with a multi-way condition using case.
5. Challenge

   Isolate the promotion cohort and identify revenue drivers

   1. Combine conditional fields with where filters to isolate promotional revenue.
   2. Exclude cancelled and returned orders from the cohort.
   3. Summarize revenue by product category and region to find the leading drivers.