10.3 Data Aggregation Patterns

You've learned to process individual rows (pipelines) and respond to events (event-driven). Now let's combine data across multiple rows—calculating totals, averages, trends, and insights from your entire dataset.

---

What Is Data Aggregation?

Simple Analogy: Spreadsheet Totals

In a spreadsheet:

• Column A: Individual prices ($10, $20, $15, $30)
• Bottom cell: =SUM(A1:A4) → Total: $75
• You're aggregating (combining) many values into one

In Taibles:

• Multiple rows: Each with data
• Aggregation column: Combines data from all rows
• Result: Summary statistic, total, average, count, etc.

---

Types of Aggregation

1. Roll-up (Child to Parent)

Common

Summarize child data up to parent rows

Order (parent)
└─ Line Items: $20, $30, $25
→ Order Total: $75

Example: Calculate invoice totals from line items

2. Cross-Row Analysis

Powerful

Analyze data across multiple rows

All customers in taible
→ Avg Lifetime Value: $1,247
→ Total Customers: 2,459
→ Top Customer: Acme Corp

Example: Customer analytics and segmentation

3. Time-Series Tracking

Trending

Track metrics over time with snapshots

Daily Revenue:
Mon: $5,000, Tue: $6,200, Wed: $5,800
→ 7-Day Avg: $5,714
→ Growth: +12%

Example: Revenue tracking and trend analysis

4. Batch Processing

Automation

Process groups of rows together

All leads added today
→ Enrich all at once
→ Classify into segments
→ Assign to sales reps

Example: Bulk lead assignment and enrichment

---

Pattern 1: Child-to-Parent Roll-Up

Use Case

Problem: Parent needs summary of child data

Example: Order needs total from line items

Solution: Aggregate child data upward to parent

---

How It Works

When you have a parent taible with a sub-taible (child rows), you can automatically calculate summaries in the parent row based on all its children.

Order #12345

Complete

Order Total:$75.50

This total is automatically calculated from all line items below

⬆️

Aggregated Up

Order Line Items (Sub-taible)

Product	Quantity	Unit Price	Line Total
Widget A	2	$10.00	$20.00
Widget B	1	$30.00	$30.00
Widget C	3	$8.50	$25.50
Total:			$75.50

---

Setting Up a Roll-Up Column

Step 1: Identify Your Parent-Child Relationship

Make sure you have:

• Parent taible: Orders
• Sub-taible: Order Line Items (connected to parent)
• Child data: Line totals you want to sum

Step 2: Add a Custom Code Column in Parent

In your parent taible (Orders), add a new column:

• Column Name: order_total
• Column Type: Custom Code
• Run Mode: Dependent - Always (recalculates when line items change)

Step 3: Access Child Data

The system gives you access to child data through the sub-taible connection. You can calculate:

• Sum: Total of all line items
• Count: Number of items
• Average: Average line total
• Min/Max: Smallest or largest item

Example Calculations:

Calculate Total:

Sum all line item totals → $75.50

Count Items:

Count number of line items → 3 items

Find Average:

Total ÷ Count → $25.17 average per item

---

Other Roll-Up Examples

Project Management:

• Parent: Project
• Children: Tasks
• Roll-up: Count completed tasks, calculate % complete

Invoice Management:

• Parent: Invoice
• Children: Invoice lines
• Roll-up: Subtotal, tax, grand total

Order Fulfillment:

• Parent: Shipment
• Children: Items
• Roll-up: Total weight, package count

---

Pattern 2: Cross-Row Analysis

Use Case

Problem: Need insights from entire dataset

Examples:

• Customer lifetime value (need all orders for each customer)
• Market share analysis (compare against all competitors)
• Performance ranking (where does this salesperson rank?)

Solution: Pull data from multiple rows, calculate across dataset

---

Example: Customer Analytics

Customer Analytics Dashboard

Real-time Metrics

Total Customers

3

Total Revenue

$16,500

Avg Customer Value

$5500.00

Customer	Orders	Revenue	Avg Order	Last Order	Segment
Acme Corp Since 2023-01-15	24	$12,450	$518.75	2024-01-10 40 days ago	Champions
TechStart Inc Since 2023-06-20	8	$3,200	$400.00	2023-12-05 76 days ago	Loyal
BuildRight LLC Since 2023-09-10	3	$850	$283.33	2023-11-22 120 days ago	Potential

How it works: Each customer row automatically calculates total orders, revenue, average order value, and recency from all their related orders. The system segments customers based on their behavior patterns (RFM scoring).

---

How to Build Customer Analytics

Step 1: Add Pull Data Column

In your Customers taible:

• Add column: all_customer_orders
• Type: Pull Data
• Configure:
  • Source Taible: Orders
  • Source Column: total_amount
  • Filter by Parent: OFF (gets all orders)

Pull Data Configuration

Data Source

What is Pull Data? This column type fetches data from another taible so you can use it for calculations, aggregations, or analysis.

Source Taible

Choose which taible to pull data from

Source Column

Choose which column's data to pull

Filter by Parent Row

When enabled, only pulls data from child rows belonging to this parent row. When disabled, pulls data from all rows in the sub-taible.

Configuration Summary:

• ✓ Pulling total_amount from orders
• ✓ Pulling from all rows (no filtering)

Step 2: Add Custom Code Column to Filter and Aggregate

Add column: customer_metrics

• Type: Custom Code
• Dependencies: customer_id, all_customer_orders

This column will:

1. Get this customer's ID
2. Filter all orders to just this customer's orders
3. Calculate total orders, revenue, average order value
4. Find first and last order dates
5. Calculate days since last order

Step 3: Add Segmentation Column

Add column: customer_segment

• Type: Custom Code
• Dependencies: customer_metrics

Based on the metrics, classify customers:

• Champions: Recent, frequent, high-value customers
• Loyal: Regular customers with good history
• Potential: New or growing customers
• At Risk: Haven't ordered recently
• Lost: No orders in a long time

---

RFM Analysis (Recency, Frequency, Monetary)

What is RFM?

A customer segmentation method based on three factors:

• Recency: How recently did they purchase?
• Frequency: How often do they purchase?
• Monetary: How much do they spend?

How Taibles Calculates RFM:

1. Recency Score (1-5 points):

   • Last 30 days → 5 points
   • Last 90 days → 4 points
   • Last 180 days → 3 points
   • Last year → 2 points
   • Over a year → 1 point

2. Frequency Score (1-5 points):

   • 20+ orders → 5 points
   • 10+ orders → 4 points
   • 5+ orders → 3 points
   • 2+ orders → 2 points
   • 1 order → 1 point

3. Monetary Score (1-5 points):

   • $10,000+ → 5 points
   • $5,000+ → 4 points
   • $1,000+ → 3 points
   • $500+ → 2 points
   • Under $500 → 1 point

Average the scores → Customer segment automatically assigned!

---

Pattern 3: Time-Series Analysis

Use Case

Problem: Need to track metrics over time

Examples:

• Daily revenue tracking
• Weekly signup trends
• Monthly churn rate
• Seasonal patterns

Solution: Daily/weekly snapshot taible with aggregated metrics

---

How It Works

Daily Metrics Dashboard

Live Tracking

Today's Revenue

$6,500

7-Day Average

$5671.43

Growth Rate

8.5%

📉

Revenue Trend (7 Days)

01-15

01-16

01-17

01-18

01-19

01-20

01-21

Date	Revenue	Orders	New Customers	Avg Order Value
2024-01-15	$4,200	12	8	$350.00
2024-01-16	$5,100	15	11	$340.00
2024-01-17	$4,800	13	9	$369.23
2024-01-18	$6,200	18	14	$344.44
2024-01-19	$5,800	16	12	$362.50
2024-01-20	$7,100	21	16	$338.10
2024-01-21 Today	$6,500	19	15	$342.11

How it works: A scheduled trigger runs every night at midnight, creating a new row in this metrics taible. Each row automatically calculates the day's totals by aggregating data from your orders and customers taibles. This gives you historical tracking and trend analysis over time.

---

Building a Time-Series Dashboard

Step 1: Create Metrics Taible

Create a new taible: "Daily Metrics"

Step 2: Add Scheduled Trigger

Add trigger:

• Type: Scheduled (Cron)
• Schedule: Every day at midnight
• Action: Create new row

Step 3: Add Metric Columns

For each metric you want to track, add columns:

Today's Revenue:

• Column: revenue_today
• Type: Custom Code
• Calculates: Sum of all orders from today

Today's Order Count:

• Column: orders_today
• Type: Custom Code
• Calculates: Count of orders from today

New Customers Today:

• Column: new_customers_today
• Type: Custom Code
• Calculates: Count of customers who signed up today

Step 4: Add Trend Columns

7-Day Average:

• Column: revenue_7day_avg
• Type: Custom Code
• Calculates: Average revenue from last 7 rows

Growth Rate:

• Column: growth_rate
• Type: Custom Code
• Calculates: % change from yesterday

---

Benefits of Time-Series Tracking

✅ Historical tracking: See trends over weeks/months

✅ Pattern detection: Identify seasonal patterns

✅ Forecasting: Use historical data for predictions

✅ Alerting: Detect anomalies (sudden drops/spikes)

✅ Reporting: Export for dashboards and reports

---

Pattern 4: Batch Processing

Use Case

Problem: Need to process groups of rows together

Examples:

• Bulk email campaigns (send to all qualified leads)
• Batch enrichment (enrich 100 contacts at once)
• Group assignments (distribute leads across team)
• Scheduled cleanup (archive old records)

Solution: Use scheduled trigger + custom code that processes multiple rows

---

Example: Batch Lead Assignment

Taible: Leads

Trigger: Scheduled (runs every hour)

Goal: Assign all unassigned qualified leads to sales reps (round-robin)

How It Works:

1. Scheduled trigger runs (every hour)
2. Creates a "batch job" row in a Jobs taible
3. Custom code in that row:
   • Queries all unassigned qualified leads
   • Distributes them across available sales reps
   • Updates each lead's assigned_rep column
4. Returns summary: "Assigned 47 leads to 5 reps"

---

Example: Daily Cleanup Job

Taible: Maintenance Jobs

Trigger: Scheduled (daily at 2 AM)

Goal: Archive leads older than 180 days with no activity

How It Works:

1. Scheduled trigger runs (daily at 2 AM)
2. Creates a "cleanup job" row
3. Custom code:
   • Finds leads with no activity in 180+ days
   • Marks them as archived
   • Counts how many were archived
4. Returns summary: "Archived 142 inactive leads"

---

Aggregation Techniques Summary

Technique 1: Pull Data Column

What it does: Pulls ALL data from specified taible/column

Best for: Getting all data as starting point

Limitation: No filtering, no aggregation functions

Use when: You'll filter/aggregate in custom code afterward

---

Technique 2: Custom Code with Pull Data

What it does: Pull Data → Custom code filters and aggregates

Best for: Filtered aggregations (e.g., orders for one customer)

Process:

1. Pull Data column gets all data
2. Custom Code column depends on Pull Data
3. Custom code filters by criteria
4. Custom code calculates aggregation

---

Technique 3: Sub-Taible Data Access

What it does: Parent accesses child sub-taible data

Best for: Parent-child aggregations

Process:

1. Access sub-taible data in custom code
2. Aggregate child values
3. Return result to parent

See: Section 7.3 for sub-taible data flow details

---

Technique 4: Time-Series Taible

What it does: Daily/weekly snapshots with aggregations

Best for: Tracking metrics over time

Process:

1. Scheduled trigger creates daily row
2. Columns aggregate data for that day
3. Historical rows show trends

---

Common Aggregation Functions

Count

What it does: Counts how many items exist

Example: Count number of orders for a customer

Result: 24 orders

---

Sum

What it does: Adds up all values

Example: Sum all order totals

Result: $12,450.00

---

Average

What it does: Calculates mean value

Example: Average order value

Result: $518.75

---

Min/Max

What it does: Finds smallest or largest value

Example:

• Minimum order: $15.00
• Maximum order: $2,500.00

---

Group By

What it does: Groups items by category and counts/sums each group

Example: Count orders by status

• Pending: 5
• Shipped: 12
• Delivered: 24
• Cancelled: 2

---

Filter and Count

What it does: Counts items matching specific criteria

Example: Count qualified leads with score > 50

Result: 47 qualified leads

---

Best Practices

Aggregation Best Practices

✅ Cache Expensive Aggregations

If you're aggregating thousands of rows, save the result and only recalculate when needed.

Example: Calculate customer lifetime value once per day instead of on every row update.

✅ Use Scheduled Aggregations

Pre-calculate metrics on a schedule (hourly, daily) instead of in real-time on every row.

Example: Create a "Daily Metrics" taible with a scheduled trigger that runs at midnight.

✅ Limit Data You Pull

Don't pull more data than you need. Filter early to reduce processing time.

Example: Only pull orders from the last 30 days instead of all historical orders.

✅ Handle Empty Data Gracefully

Always check if data exists before aggregating to avoid errors.

Example: Return 0 or a default value when there are no items to aggregate.

❌ Common Mistakes to Avoid

• • Aggregating thousands of rows on every single row update (too slow)
• • Not filtering data before aggregation (processing too much)
• • Forgetting to handle empty or missing data (causes errors)
• • Using complex aggregations when simple counts or sums would work

---

Real-World Examples

E-Commerce Dashboard

Use Case: Track daily sales performance

Setup:

• Taible: Daily Sales Metrics
• Trigger: Scheduled (midnight)
• Columns:
  • date (from trigger)
  • total_revenue (sum of orders)
  • order_count (count of orders)
  • avg_order_value (revenue ÷ orders)
  • new_customers (count first-time buyers)
  • top_product (most sold item)

Result: Automatic daily dashboard with trends

---

Customer Health Scoring

Use Case: Identify at-risk customers

Setup:

• Taible: Customers
• Columns:
  • total_orders (count from orders)
  • total_revenue (sum from orders)
  • days_since_last_order (calculated)
  • health_score (RFM-based scoring)
  • risk_level (Champions, Loyal, At Risk, etc.)

Result: Automatic customer segmentation and risk alerts

---

Sales Team Performance

Use Case: Track individual rep performance

Setup:

• Taible: Sales Reps
• Columns:
  • deals_closed_month (count from deals)
  • revenue_month (sum from deals)
  • avg_deal_size (revenue ÷ deals)
  • win_rate (won ÷ total)
  • ranking (compared to all reps)

Result: Automatic leaderboard and performance tracking

---

Summary: Data Aggregation Patterns

You now understand data aggregation:

✅ Four main patterns:

• Roll-up (child to parent aggregation)
• Cross-row calculations (analyze entire dataset)
• Time-series (track metrics over time)
• Batch processing (process groups together)

✅ Implementation techniques:

• Pull Data column (gets all data)
• Custom Code + Pull Data (filter and aggregate)
• Sub-taible access (parent-child)
• Time-series taible (scheduled snapshots)

✅ Common aggregations:

• Count, Sum, Average, Min/Max
• Group By, Filter and Count

✅ Best practices:

• Cache expensive aggregations
• Use scheduled pre-calculations
• Limit data pulled
• Handle empty data gracefully

✅ Real examples:

• Customer lifetime value (RFM analysis)
• Daily metrics tracking
• Order totals from line items

---

Next Steps

Now that you understand data aggregation, Section 10.4 will cover:

• Human-in-the-loop workflows
• Approval gates and review steps
• Manual overrides
• Balancing automation with human oversight

Let's master human-automation collaboration!