Analyzing Business Data with T-SQL
Kevin Feasel (@feaselkl)https://csmore.info/on/business
Who Am I? What Am I Doing Here?
Motivation
My goals in this talk:
- Introduce the concept of key performance indicators (KPIs).
- Describe common KPIs in the retail business space.
- Show how to calculate these KPIs in T-SQL against an OLTP system.
- Introduce great analytic functionality in T-SQL.
What We'll Learn
What We'll Learn
What We'll Learn
Agenda
- What is a KPI?
- Dive into KPIs
What is a Key Performance Indicator?
A key performance indicator (KPI) is a performance measurement of how well an organization is doing at achieving a specific goal.
KPIs typically follow a pattern:
- Numeric measurements
- Tied to an asserted organizational goal
- Expected to identify whether the organization is meeting its goal
- Usually has a target--a minimum (or maximum) threshold for identifying success
- Has a specific grain
The Grain of a Measure
In the data warehousing world, we have the notion of a grain. The grain of a fact (or measurement) is the maximum level of specificity for that measurement. We usually define grain in terms of dimensions, explanatory information which helps provide relevant context to the fact.
Ex: what is the grain for orders at a grocery store?
Grains and Granularity
Once we know the grain of our measure, we know we can't dig any deeper (without making certain potentially-scary assumptions). We can, however, aggregate results and move up in our grain.
We also cannot move "orthogonal" to an existing grain. If we collect order data by customer and date, we cannot later aggregate this data by "missing" features like store location or register number.
We can, however, aggregate if there is a mapping function from our initial grain to the new grain, such as from customer to customer's favorite color.
Aggregations and Additivity
We can aggregate any number, but some aggregations don't make sense. There are three levels of additivity:
- Additive -- We can sum up data rows without a problem. Ex: order revenue
- Semi-additive -- We can sum up data rows along certain dimensions but not all dimensions. Ex: bank account balance
- Non-additive -- We cannot sum up rows along any dimension. Ex: completion percentage, P/E ratio
Facts, Measures, and KPIs
A fact is a record in a dataset which tells us about something which has happened. A measure is some computation or explanation about the fact. A KPI is a measure which ties back to business need.
- Fact -- We made a sale to Alice on April 9, 2023. We sold her 6 bags of red mulch at a price of $2.99 per bag.
- Measure -- The total sale price, before tax, was $17.94. We sold this at 1:28 PM. Alice checked out at register 3. The SKU of the red mulch is M123-A.
- KPI -- Our store has sold $1,800 in red mulch during the month of April. Our target for red mulch sales in April is 3,100.
Agenda
- What is a KPI?
- Dive into KPIs
Dive into KPIs
From here on out, it's all KPIs all the time! We will cover four main bases of KPI:
- Financial KPIs (revenue, cost, profit)
- Customer KPIs (customer counts, customers by geography, customer retention rate)
- Sales KPIs (time to first conversion)
- Avidity KPIs (top customers by month)
Review the code repository for bonus KPIs as well!
Demo Time
Wrapping Up
Over the course of this talk, we introduced the concept of key performance indicators (KPIs). We showed how to calculate a series of KPIs for a retail company using a variety of functions and capabilities in T-SQL, as well as a powerful calendar table.
Wrapping Up
To learn more, go here:
https://csmore.info/on/business
And for help, contact me:
feasel@catallaxyservices.com | @feaselkl
Catallaxy Services consulting:
https://CSmore.info/on/contact