> ## Documentation Index
> Fetch the complete documentation index at: https://docs.allium.so/llms.txt
> Use this file to discover all available pages before exploring further.

# Payment Pipeline Tables

> Schema reference for stablecoin payment classification tables

## Table Overview

All tables are located in the `crosschain.stablecoin` schema

| Table                           | Purpose                  | Row Grain                  | Update Frequency |
| ------------------------------- | ------------------------ | -------------------------- | ---------------- |
| **filtered\_transfers**         | Organic transfers only   | One row per transfer       | Real-time        |
| **wallet\_classification**      | Wallet profiling         | One row per wallet per day | Daily            |
| **transaction\_classification** | Transaction type tagging | One row per transaction    | Real-time        |
| **payment\_categorization**     | Granular payment purpose | One row per payment        | Real-time        |

***

## 1. filtered\_transfers

**Purpose:** Organic stablecoin transfers with CEX/DeFi/Infrastructure addresses removed.

**Use Case:** Calculate adjusted payment volume, track organic adoption.

### Schema

| Column Name        | Data Type | Description                                            |
| ------------------ | --------- | ------------------------------------------------------ |
| `chain`            | VARCHAR   | Blockchain network (ethereum, polygon, arbitrum, etc.) |
| `token_symbol`     | VARCHAR   | Stablecoin token symbol (USDC, USDT, DAI, etc.)        |
| `from_address`     | VARCHAR   | Sender address                                         |
| `to_address`       | VARCHAR   | Recipient address                                      |
| `usd_amount`       | NUMBER    | Transfer amount in USD                                 |
| `transaction_hash` | VARCHAR   | Transaction hash                                       |
| `block_timestamp`  | TIMESTAMP | Block timestamp                                        |
| `block_number`     | NUMBER    | Block number                                           |
| `unique_id`        | VARCHAR   | Unique transfer identifier (hash + log\_index)         |

### Key Features

* ✅ **Attribution filtered:** CEX/DeFi/Infrastructure excluded
* ✅ **Cross-chain:** All supported chains aggregated
* ✅ **Real-time updates:** New transfers added as they occur
* ❌ **No attribution labels exposed:** Labels are internal only

### Example Queries

#### Total Adjusted Volume by Chain (30d)

```sql theme={null}
SELECT
  chain,
  SUM(usd_amount) AS adjusted_volume_30d
FROM crosschain.stablecoin.filtered_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 2 DESC;
```

#### Daily Payment Volume Trend

```sql theme={null}
SELECT
  DATE_TRUNC('day', block_timestamp) AS day,
  COUNT(*) AS transfer_count,
  SUM(usd_amount) AS daily_volume
FROM crosschain.stablecoin.filtered_transfers
WHERE block_timestamp >= CURRENT_DATE - 90
GROUP BY 1
ORDER BY 1;
```

#### Top Stablecoins by Organic Volume

```sql theme={null}
SELECT
  token_symbol,
  COUNT(*) AS transfer_count,
  SUM(usd_amount) AS total_volume
FROM crosschain.stablecoin.filtered_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 3 DESC;
```

***

## 2. wallet\_classification

**Purpose:** Comprehensive wallet profiling with behavioral segments, engagement scores, and risk metrics.

**Use Case:** Customer segmentation, risk assessment, wallet analysis.

### Schema

| Column Name                | Data Type | Description                                                                                |
| -------------------------- | --------- | ------------------------------------------------------------------------------------------ |
| `chain`                    | VARCHAR   | Blockchain network                                                                         |
| `address`                  | VARCHAR   | Wallet address                                                                             |
| `snapshot_date`            | DATE      | Date of the classification snapshot                                                        |
| `balance_tier`             | VARCHAR   | Balance tier (whale, large, medium, small, micro, dust)                                    |
| `behavioral_segment`       | VARCHAR   | Activity segment (new, active, dormant, churned)                                           |
| `combined_segment`         | VARCHAR   | Combined classification (e.g., "Whale Active", "Small Dormant")                            |
| `engagement_score`         | NUMBER    | Overall engagement score (0-100)                                                           |
| `engagement_tier`          | VARCHAR   | Engagement tier (highly\_engaged, engaged, moderately\_engaged, low\_engagement, inactive) |
| `recency_score`            | NUMBER    | Recency component (0-40 points)                                                            |
| `frequency_score`          | NUMBER    | Frequency component (0-40 points)                                                          |
| `consistency_score`        | NUMBER    | Consistency component (0-20 points)                                                        |
| `risk_score`               | NUMBER    | Overall risk score (0-100)                                                                 |
| `risk_flag`                | VARCHAR   | Risk flag (high, medium, low)                                                              |
| `volatility_risk_score`    | NUMBER    | Balance volatility risk (0-50 points)                                                      |
| `anomaly_risk_score`       | NUMBER    | Balance anomaly risk (0-50 points)                                                         |
| `volume_30d_usd`           | NUMBER    | 30-day transaction volume in USD                                                           |
| `current_balance_usd`      | NUMBER    | Current wallet balance in USD                                                              |
| `days_since_last_activity` | NUMBER    | Days since most recent activity                                                            |
| `days_active_30d`          | NUMBER    | Number of active days in past 30 days                                                      |
| `days_active_lifetime`     | NUMBER    | Total number of active days (lifetime)                                                     |
| `lifetime_span_days`       | NUMBER    | Days between first and last activity                                                       |
| `tokens_used_count`        | NUMBER    | Number of unique stablecoin tokens used                                                    |
| `chains_used_count`        | NUMBER    | Number of chains wallet has been active on                                                 |

### Balance Tiers

| Tier       | Balance Range (USD) | Description                     |
| ---------- | ------------------- | ------------------------------- |
| **whale**  | > \$1M              | High-value accounts             |
| **large**  | $100K - $1M         | Institutional/business accounts |
| **medium** | $10K - $100K        | Active users/small businesses   |
| **small**  | $1K - $10K          | Regular users                   |
| **micro**  | $100 - $1K          | Casual users                    |
| **dust**   | \< \$100            | Minimal activity                |

### Behavioral Segments

| Segment     | Criteria                      | Description        |
| ----------- | ----------------------------- | ------------------ |
| **new**     | First activity \< 30 days ago | New wallet         |
| **active**  | Active in last 7 days         | Currently engaged  |
| **dormant** | Active 7-30 days ago          | Recently inactive  |
| **churned** | Active >30 days ago           | Long-term inactive |

### Engagement Scoring

**Total: 100 points**

| Component       | Points | Criteria                    |
| --------------- | ------ | --------------------------- |
| **Recency**     | 0-40   | Days since last activity    |
| **Frequency**   | 0-40   | Number of active days (30d) |
| **Consistency** | 0-20   | Activity pattern regularity |

**Engagement Tiers:**

* highly\_engaged: 80-100
* engaged: 60-79
* moderately\_engaged: 40-59
* low\_engagement: 20-39
* inactive: 0-19

### Risk Scoring

**Total: 100 points**

| Component      | Points | Criteria                 |
| -------------- | ------ | ------------------------ |
| **Volatility** | 0-50   | Balance fluctuation rate |
| **Anomaly**    | 0-50   | Unusual balance patterns |

**Risk Flags:**

* high: 70-100
* medium: 40-69
* low: 0-39

### Example Queries

#### Wallet Segmentation Distribution

```sql theme={null}
SELECT
  balance_tier,
  behavioral_segment,
  COUNT(*) AS wallet_count,
  AVG(engagement_score) AS avg_engagement,
  AVG(risk_score) AS avg_risk
FROM crosschain.stablecoin.wallet_classification
WHERE snapshot_date = CURRENT_DATE
GROUP BY 1, 2
ORDER BY 3 DESC;
```

#### High-Value Active Wallets

```sql theme={null}
SELECT
  chain,
  address,
  balance_tier,
  current_balance_usd,
  engagement_score,
  risk_flag
FROM crosschain.stablecoin.wallet_classification
WHERE snapshot_date = CURRENT_DATE
  AND balance_tier IN ('whale', 'large')
  AND behavioral_segment = 'active'
ORDER BY current_balance_usd DESC
LIMIT 100;
```

#### Risk Analysis

```sql theme={null}
SELECT
  risk_flag,
  COUNT(*) AS wallet_count,
  AVG(volume_30d_usd) AS avg_volume,
  AVG(volatility_risk_score) AS avg_volatility,
  AVG(anomaly_risk_score) AS avg_anomaly
FROM crosschain.stablecoin.wallet_classification
WHERE snapshot_date = CURRENT_DATE
GROUP BY 1;
```

***

## 3. transaction\_classification

**Purpose:** Classify transactions by type (Real-World Payment, Investment/Trade, Store as Value) and wallet type combinations.

**Use Case:** Payment type analysis, wallet interaction patterns.

### Schema

| Column Name               | Data Type | Description                                                          |
| ------------------------- | --------- | -------------------------------------------------------------------- |
| `chain`                   | VARCHAR   | Blockchain network                                                   |
| `token_symbol`            | VARCHAR   | Stablecoin token symbol                                              |
| `activity_date`           | DATE      | Date of transaction                                                  |
| `block_timestamp`         | TIMESTAMP | Block timestamp                                                      |
| `transaction_hash`        | VARCHAR   | Transaction hash                                                     |
| `from_address`            | VARCHAR   | Sender address                                                       |
| `to_address`              | VARCHAR   | Recipient address                                                    |
| `usd_amount`              | NUMBER    | Transfer amount in USD                                               |
| `from_wallet_type`        | VARCHAR   | Sender wallet type (Consumer, Business, Institutional, Unclassified) |
| `to_wallet_type`          | VARCHAR   | Recipient wallet type                                                |
| `from_balance_tier`       | VARCHAR   | Sender balance tier                                                  |
| `to_balance_tier`         | VARCHAR   | Recipient balance tier                                               |
| `transaction_type`        | VARCHAR   | Transaction classification                                           |
| `wallet_type_combination` | VARCHAR   | Wallet type pair (C2C, C2B, B2C, B2B, etc.)                          |
| `from_30d_volume`         | NUMBER    | Sender's 30-day volume in USD                                        |
| `to_30d_volume`           | NUMBER    | Recipient's 30-day volume in USD                                     |
| `from_avg_txn_size`       | NUMBER    | Sender's avg transaction size (30d)                                  |
| `to_avg_txn_size`         | NUMBER    | Recipient's avg transaction size (30d)                               |

### Transaction Types

| Type                   | Description                         | Examples                  |
| ---------------------- | ----------------------------------- | ------------------------- |
| **Real-World Payment** | P2P, retail, payroll, B2B payments  | Salary, purchase, invoice |
| **Investment/Trade**   | Speculative, trading, yield-seeking | Swap prep, arbitrage      |
| **Store as Value**     | Long-term holding, savings          | Accumulation, vault       |
| **Unclassified**       | Ambiguous or insufficient data      | -                         |

### Wallet Types

| Type              | Description                  | Identification Criteria                  |
| ----------------- | ---------------------------- | ---------------------------------------- |
| **Consumer**      | Individual users             | Low volume, small balances, P2P patterns |
| **Business**      | Merchants, service providers | Medium-high volume, regular receipts     |
| **Institutional** | Large entities, funds        | Very high volume, large balances         |
| **Unclassified**  | Insufficient data            | New wallets, low activity                |

### Wallet Type Combinations

| Combination | Description                    | Examples                           |
| ----------- | ------------------------------ | ---------------------------------- |
| **C2C**     | Consumer to Consumer           | P2P payments, personal transfers   |
| **C2B**     | Consumer to Business           | Retail purchases, service payments |
| **B2C**     | Business to Consumer           | Salaries, refunds, payouts         |
| **B2B**     | Business to Business           | Supplier payments, invoices        |
| **I2C**     | Institutional to Consumer      | Airdrops, grants                   |
| **I2B**     | Institutional to Business      | Investments, funding               |
| **I2I**     | Institutional to Institutional | Large settlements                  |
| **C2I**     | Consumer to Institutional      | Investments                        |
| **B2I**     | Business to Institutional      | -                                  |
| **U2U**     | Unclassified to Unclassified   | -                                  |
| **U2X**     | Unclassified to any            | -                                  |
| **Other**   | Mixed or edge cases            | -                                  |

### Example Queries

#### Transaction Type Distribution

```sql theme={null}
SELECT
  transaction_type,
  COUNT(*) AS tx_count,
  SUM(usd_amount) AS total_volume
FROM crosschain.stablecoin.transaction_classification
WHERE activity_date >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 3 DESC;
```

#### Wallet Combination Analysis

```sql theme={null}
SELECT
  wallet_type_combination,
  COUNT(*) AS tx_count,
  AVG(usd_amount) AS avg_amount,
  SUM(usd_amount) AS total_volume
FROM crosschain.stablecoin.transaction_classification
WHERE activity_date >= CURRENT_DATE - 30
  AND transaction_type = 'Real-World Payment'
GROUP BY 1
ORDER BY 4 DESC;
```

#### C2B Payment Trends

```sql theme={null}
SELECT
  DATE_TRUNC('week', activity_date) AS week,
  COUNT(*) AS payment_count,
  SUM(usd_amount) AS weekly_volume
FROM crosschain.stablecoin.transaction_classification
WHERE wallet_type_combination = 'C2B'
  AND transaction_type = 'Real-World Payment'
  AND activity_date >= CURRENT_DATE - 90
GROUP BY 1
ORDER BY 1;
```

***

## 4. payment\_categorization

**Purpose:** Granular payment purpose classification for real-world payments only.

**Use Case:** Payment category analytics, merchant insights, payroll tracking.

### Schema

Includes all columns from `transaction_classification` plus:

| Column Name             | Data Type | Description                 |
| ----------------------- | --------- | --------------------------- |
| `core_payment_category` | VARCHAR   | High-level payment category |
| `payment_purpose`       | VARCHAR   | Granular payment purpose    |

### Core Payment Categories

| Category                  | Description                               |
| ------------------------- | ----------------------------------------- |
| **C2C Payment**           | Consumer-to-Consumer transfers            |
| **C2B Payment**           | Consumer-to-Business purchases/services   |
| **B2C Payment**           | Business-to-Consumer payouts/refunds      |
| **B2B Payment**           | Business-to-Business settlements          |
| **I2C Payment**           | Institutional-to-Consumer grants/airdrops |
| **I2B Payment**           | Institutional-to-Business funding         |
| **Institutional Payment** | Institutional-to-Institutional            |
| **Other**                 | Mixed or unclassified                     |

### Payment Purposes

#### C2C (Consumer to Consumer)

| Purpose                | Amount Range | Description              |
| ---------------------- | ------------ | ------------------------ |
| **P2P Micro Payment**  | \< \$100     | Small personal transfers |
| **P2P Remittance**     | $100 - $10K  | Cross-border remittances |
| **P2P Large Transfer** | > \$10K      | Large personal transfers |

#### C2B (Consumer to Business)

| Purpose             | Amount Range | Description               |
| ------------------- | ------------ | ------------------------- |
| **Retail Purchase** | \< \$500     | Small retail transactions |
| **Service Payment** | $500 - $5K   | Professional services     |
| **Large Purchase**  | > \$5K       | High-value goods/services |

#### B2C (Business to Consumer)

| Purpose                | Amount Range | Description                |
| ---------------------- | ------------ | -------------------------- |
| **Micro Payout**       | \< \$100     | Small rewards, cashback    |
| **Gig/Creator Payout** | $100 - $2K   | Freelance, content creator |
| **Salary/Payroll**     | $2K - $20K   | Employee salaries          |
| **Large Disbursement** | > \$20K      | Bonuses, large payouts     |

#### B2B (Business to Business)

| Purpose                  | Amount Range | Description                  |
| ------------------------ | ------------ | ---------------------------- |
| **Service Fee**          | \< \$5K      | Subscription, small invoices |
| **Supplier Payment**     | $5K - $50K   | Vendor payments              |
| **Large B2B Settlement** | > \$50K      | Major contracts              |

### Example Queries

#### Payment Purpose Breakdown

```sql theme={null}
SELECT
  core_payment_category,
  payment_purpose,
  COUNT(*) AS payment_count,
  SUM(usd_amount) AS total_usd,
  AVG(usd_amount) AS avg_payment_size
FROM crosschain.stablecoin.payment_categorization
WHERE activity_date >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 4 DESC;
```

#### Payroll Volume Tracking

```sql theme={null}
SELECT
  DATE_TRUNC('month', activity_date) AS month,
  COUNT(*) AS payroll_count,
  SUM(usd_amount) AS total_payroll_usd
FROM crosschain.stablecoin.payment_categorization
WHERE payment_purpose = 'Salary/Payroll'
  AND activity_date >= CURRENT_DATE - 180
GROUP BY 1
ORDER BY 1;
```

#### Retail vs Service Payments

```sql theme={null}
SELECT
  payment_purpose,
  COUNT(*) AS payment_count,
  SUM(usd_amount) AS volume
FROM crosschain.stablecoin.payment_categorization
WHERE payment_purpose IN ('Retail Purchase', 'Service Payment')
  AND activity_date >= CURRENT_DATE - 30
GROUP BY 1;
```

***

## Table Relationships

```mermaid theme={null}
graph TD
    A[filtered_transfers] -->|Join on address| B[wallet_classification]
    A -->|Join on tx_hash| C[transaction_classification]
    C -->|Filter: transaction_type = 'Real-World Payment'| D[payment_categorization]
```

### Common Joins

#### Filtered Transfers + Wallet Classification

```sql theme={null}
SELECT
  ft.transaction_hash,
  ft.usd_amount,
  wc_from.balance_tier AS from_balance_tier,
  wc_from.engagement_score AS from_engagement,
  wc_to.balance_tier AS to_balance_tier,
  wc_to.engagement_score AS to_engagement
FROM crosschain.stablecoin.filtered_transfers ft
LEFT JOIN crosschain.stablecoin.wallet_classification wc_from
  ON ft.from_address = wc_from.address
  AND DATE(ft.block_timestamp) = wc_from.snapshot_date
LEFT JOIN crosschain.stablecoin.wallet_classification wc_to
  ON ft.to_address = wc_to.address
  AND DATE(ft.block_timestamp) = wc_to.snapshot_date
WHERE ft.block_timestamp >= CURRENT_DATE - 7;
```

#### Transaction Classification + Payment Categorization

```sql theme={null}
SELECT
  tc.transaction_type,
  pc.payment_purpose,
  COUNT(*) AS tx_count
FROM crosschain.stablecoin.transaction_classification tc
LEFT JOIN crosschain.stablecoin.payment_categorization pc
  ON tc.transaction_hash = pc.transaction_hash
WHERE tc.activity_date >= CURRENT_DATE - 30
GROUP BY 1, 2;
```

***

## Next Steps

<CardGroup cols={2}>
  <Card title="Adjusted Volume Methodology" icon="filter" href="/historical-data/payments/adjusted-volume">
    Learn how organic volume is calculated
  </Card>

  <Card title="Pipeline Overview" icon="sitemap" href="/historical-data/payments/overview">
    Back to payments pipeline overview
  </Card>
</CardGroup>