Integrating Outbound Call Data Into BI Tools: Pipelines, Schemas, and Useful Queries

Why BI Integration Is Worth the Engineering Investment

Your telephony stack generates granular data on every call: timestamps, durations, SIP response codes, agent IDs, campaign IDs, caller IDs, and disposition codes. Your CRM generates contact-level data: lead source, industry segment, deal stage, conversion event. Your financial system generates seat costs and revenue.

None of these systems talks to the others by default. The BI layer is the integration point. A well-built pipeline means your VP of Sales can answer "what is our cost per booked demo broken down by market and list source" in 90 seconds with a dashboard query, instead of in 3 days with a spreadsheet pulled by someone from IT.

On a flat-rate network at $99 per seat per month, cost allocation is deterministic — you divide your monthly seat cost by the number of hours that seat was productive. That simplicity makes cost-per-X analyses in BI straightforward. On per-minute billing, you need to model variable costs into every query, which complicates the schema and introduces reconciliation risk.

The Data Model: Fact Tables and Dimensions

Design your call analytics data model around a central fact table with dimension tables joined to it:

Fact table: call_events

• call_id (primary key, from SIP CDR)
• agent_id (FK to agents dimension)
• campaign_id (FK to campaigns dimension)
• caller_id_did (FK to caller IDs dimension)
• destination_number (normalized E.164 format)
• destination_country_code
• destination_area_code
• call_start_utc (timestamp, UTC)
• ring_duration_seconds (SIP PDD through answer or abandonment)
• talk_duration_seconds (0 if unanswered)
• total_duration_seconds
• sip_response_code
• outcome_category (derived: RPC, Voicemail, No Answer, Busy, Network Error)
• abandon_flag (boolean: call connected to agent but disconnected before connection to customer)
• crm_contact_id (FK to CRM contact, null if not matched)

Dimension: agents — name, team, supervisor, hire date, employment status

Dimension: campaigns — name, type, start/end dates, target market, dialing mode

Dimension: caller_ids — DID number, country, provisioned date, status (active/retired)

Dimension: date — standard date dimension with day of week, week number, month, quarter, fiscal period

This star schema supports the full range of outbound analytics queries without complex joins.

ETL Pipeline Design

A reliable ETL pipeline for call data has three stages:

Extract: Pull CDRs from the telephony API on a schedule — every 5 minutes for near-real-time, hourly for operational reporting, nightly for historical analysis. The UnlimCall API returns CDRs with SIP-level fields in a structured JSON format. Use cursor-based pagination keyed on call_start_utc to avoid re-fetching records you've already processed.

Transform: Normalize destination numbers to E.164. Map SIP response codes to outcome categories using a lookup table you maintain (SIP code semantics vary slightly across carrier implementations). Calculate derived metrics: talk_duration / (ring_duration + talk_duration) gives you a per-call efficiency ratio. Join to your CRM on destination number to attach contact metadata. Check for duplicates using call_id as the idempotency key.

Load: Append to your columnar analytics database. ClickHouse, BigQuery, Snowflake, and Redshift all handle this workload. For smaller deployments (under 5 million CDRs/month), DuckDB running on a modest VM is sufficient and eliminates cloud data warehouse costs.

Connecting to BI Tools

Metabase, Looker, Power BI, Tableau, and Superset all connect to columnar databases via JDBC or native connectors. The setup process is identical for each tool: provide host, port, database name, credentials, and a read-only user scoped to the analytics schema.

Create a "certified dataset" in your BI tool that points to a view rather than the raw table. The view encapsulates your standard business rules — UTC-to-local-time conversion, outcome category labels, cost allocation formula — so analysts don't reimplement these rules in every dashboard they build.

Five Queries That Drive Outbound Operations

1. Contact rate by campaign, day of week, and time of day (local). Identifies optimal calling windows per campaign. Run this quarterly; patterns shift as caller ID reputation evolves and list demographics change.

2. Per-DID answer rate, 30-day rolling, flagged below threshold. A DID falling below 70 % of its 90-day baseline answer rate is a candidate for rotation. This query drives your DID health process automatically.

3. Agent efficiency index: contacts per logged-in hour, by agent and week. Controls for campaign difficulty by normalizing to campaign average. An agent producing 0.65 contacts per hour on a campaign averaging 0.72 needs coaching before the number widens further.

4. Abandon rate by campaign and 15-minute window. The question is not just "what is our abandon rate today" but "when during the day does abandon rate spike." The answer tells you where to tighten your pacing ratio.

5. Cost per right-party contact by market. On flat-rate billing: (seat_monthly_cost / business_days / 8 / contacts_per_agent_per_hour). Across 33 markets on UnlimCall's pricing, this calculation shows you which market expansions are producing above- or below-average unit economics.