How to use multiple data transformation techniques together to get KPIs from raw data?
We started this skilleton with the objective of learning to calculate KPIs from raw data. We first understood different raw data formats in internet businesses & then learnt about different data transformation techniques. Let's now put these ideas together.
Calculating new metrics from raw data is typically not limited to a single step but a series of data transformations, each building upon the other to refine and enrich the data into meaningful KPIs.
Below is a detailed step-by-step guide using two comprehensive examples: creating a user conversion funnel analysis and analyzing monthly sales performance.
Sequential Approach to Calculating New Metrics:
Each step in transforming raw data to new metrics involves specific techniques, ensuring the data becomes progressively more insightful and actionable. We start with the user conversion funnel analysis as our first example and then go into monthly sales performance analysis.
Use case #1: User Conversion Funnel Analysis
Starting with Raw Data: The raw data consists of detailed user interactions on a website, such as pages visited, actions taken, timestamps, etc.
- 1. Filtering and Cleaning: Filter the data to a specific campaign period and clean it by removing any irrelevant or corrupted entries.
- 2. Sorting and Structuring: Sort the cleaned data by user and time of interaction to structure the data chronologically for each user's journey.
- 3. Grouping and Aggregating: Group the data by stages in the user journey (e.g., homepage visit, product view, add to cart, purchase) and aggregate to count users at each stage.
- 4. Calculating Conversion Rates: Calculate the conversion rate at each stage by dividing the number of users moving to the next stage by the total at the current stage.
Use case #2: Monthly Sales Performance Analysis
Starting with Raw Data: The raw data includes transaction records for each product sold, including date of sale, product ID, quantity sold, and sale price.
- 1. Filtering and Cleaning: Isolate the data for the desired month and remove any transactions that were voided or returned.
- 2. Sorting and Categorizing: Sort the transactions by product ID and categorize them by product type or category for a segmented analysis.
- 3. Aggregating: Aggregate the data within each category to calculate total sales, quantities sold, and average sale price per item.
- 4. Calculating Performance Metrics: Determine metrics such as total revenue per category, best-selling products, and month-over-month growth in sales.
Implementing in Excel:
Here’s how these steps can be implemented in Excel for both examples:
- 1. Filtering and Cleaning: Apply filters and manually inspect data for accuracy or relevance.
- 2. Sorting and Structuring/Categorizing: Use the 'Sort' function to organize data by relevant criteria like user ID, timestamp, or product ID.
- 3. Grouping and Aggregating: Employ PivotTables for advanced aggregation and grouping, facilitating the calculation of total counts, sums, or averages.
- 4. Calculating Conversion Rates/Performance Metrics: Use Excel formulas or calculated fields in PivotTables to derive new metrics such as conversion rates or sales performance indicators.
Takeaway:
Through this skilleton, we got introduced to different data transformation techniques. And, we also learnt how they can be combined to transform raw data of different types and from different sources, to arrive at the KPIs that can be easily understood and tracked.