KQL Choose-Your-Own-Adventure: Interactive Query Learning
Your mission: craft a query to uncover insights from this bustling digital marketplace.
Welcome, data adventurer, to a thrilling journey through the Kusto Query Language (KQL)! In this interactive blog post, you'll build a KQL query step-by-step by making choices that shape your path. Our dataset? A lively e-commerce sales log, packed with juicy details about online purchases. Your mission: craft a query to uncover insights from this bustling digital marketplace. Ready? Let's dive in!
Grab the dataset for this blog post: https://github.com/rod-trent/ShopSphere_Sales
The Setup: Our E-Commerce Dataset
Imagine you're an analyst for ShopSphere, an online store selling gadgets, clothes, and home goods. The Sales table contains:
Timestamp: When the sale happened.
UserId: Unique customer identifier.
Category: Product type (e.g., Electronics, Clothing, Home).
Item: Specific product (e.g., Smartphone, T-Shirt, Lamp).
Price: Sale amount in USD.
Region: Customer's location (e.g., NorthAmerica, Europe, Asia).
Your goal is to build a query that extracts meaningful insights. But how? You'll choose your path at each step, learning KQL operators along the way. Let's start!
Step 1: Where to Begin?
You want to analyze recent sales, but the dataset is massive. What's your first move?
A. Filter by a time range to focus on recent sales. (Go to Filter by Time)
B. Filter by product category to zoom in on specific items. (Go to Filter by Category)
Filter by Time
Great choice! Filtering by time narrows our focus to recent activity. In KQL, the where operator filters rows based on conditions. To limit by time, we use the Timestamp column and a time range.
Example
To get sales from the last 7 days:
Sales
| where Timestamp > ago(7d)
The ago(7d) function calculates "7 days ago" from the current time. This keeps only rows where Timestamp is more recent.
What's Next?
You've filtered by time, but the data is still broad. What's your next step?
C. Group by product category to see sales trends. (Go to Group by Category)
D. Summarize total sales revenue. (Go to Summarize Revenue)
Filter by Category
Nice! Focusing on a product category, like Electronics, helps analyze specific trends. The where operator shines again, filtering rows where Category matches your choice.
Example
To get only Electronics sales:
Sales
| where Category == "Electronics"
The == operator checks for exact matches. Double quotes ensure KQL treats "Electronics" as a string.
What's Next?
You've zeroed in on Electronics. Now what?
C. Group by product category to see sales trends. (Go to Group by Category)
D. Summarize total sales revenue. (Go to Summarize Revenue)
Group by Category
Smart move! Grouping by category reveals trends across product types. The summarize operator groups rows by a column (like Category) and applies aggregations, such as counting sales.
Example
If you came from Filter by Time:
Sales
| where Timestamp > ago(7d)
| summarize SaleCount = count() by Category
If you came from Filter by Category:
Sales
| where Category == "Electronics"
| summarize SaleCount = count() by Category
The count() function tallies rows per group. This shows how many sales each category has.
What's Next?
You see category trends, but want more detail. What's your move?
E. Add average price per category. (Go to Average Price)
F. Sort by sale count to find top categories. (Go to Sort by Count)
Summarize Revenue
Show me the money! Summarizing revenue gives a big-picture view. The summarize operator with sum() calculates total Price across rows.
Example
If you came from Filter by Time:
Sales
| where Timestamp > ago(7d)
| summarize TotalRevenue = sum(Price)
If you came from Filter by Category:
Sales
| where Category == "Electronics"
| summarize TotalRevenue = sum(Price)
The sum(Price) function adds up the Price column for all matching rows.
What's Next?
You've got total revenue, but want to dig deeper. What's next?
E. Add average price per category. (Go to Average Price)
F. Sort by sale count to find top categories. (Go to Sort by Count)
Average Price
Excellent! Adding average price per category shows what customers are spending. The summarize operator can handle multiple aggregations, like avg() for averages.
Example
If you came from Group by Category via Filter by Time:
Sales
| where Timestamp > ago(7d)
| summarize SaleCount = count(), AvgPrice = avg(Price) by Category
If you came from Group by Category via Filter by Category:
Sales
| where Category == "Electronics"
| summarize SaleCount = count(), AvgPrice = avg(Price) by Category
The avg(Price) function computes the average Price per group.
Final Step
Your query is almost complete! Want to polish it?
G. Finalize and run your query! (Go to Finalize Query)
Sort by Count
Top categories, coming up! The top or order by operators sort results. Let’s use order by to rank categories by sale count.
Example
If you came from Group by Category via Filter by Time:
Sales
| where Timestamp > ago(7d)
| summarize SaleCount = count() by Category
| order by SaleCount desc
If you came from Group by Category via Filter by Category:
Sales
| where Category == "Electronics"
| summarize SaleCount = count() by Category
| order by SaleCount desc
The order by SaleCount desc sorts in descending order, putting top sellers first.
Final Step
Your query is ready to shine! Ready to wrap it up?
G. Finalize and run your query! (Go to Finalize Query)
Finalize Query
Congratulations, adventurer! You've built a powerful KQL query. Depending on your choices, your query might look like one of these:
If you chose Time → Group by Category → Average Price:
Sales
| where Timestamp > ago(7d)
| summarize SaleCount = count(), AvgPrice = avg(Price) by Category
What it does: Shows sales count and average price per category for the last 7 days.
If you chose Category → Summarize Revenue:
Sales
| where Category == "Electronics"
| summarize TotalRevenue = sum(Price)
What it does: Calculates total revenue for Electronics sales.
Try It Out!
Copy your query into a KQL-compatible platform like Azure Data Explorer. Tweak it further—maybe add Region to the summarize or try top 5 by SaleCount. The KQL world is yours to explore!
Keep Learning
Check out KQL documentation for more operators.
Experiment with
joinorprojectto level up.
Thanks for adventuring with KQL! What's your next query quest?