Aircall Models

Copy and Paste these Aircall Models into your Data Layer to quickly go from raw data sources to business ready AirCall Reporting. Please note:

Models must be added chronological order from top to bottom (so that they can reference each other).
When adding your own models you must update the reference to include your source names. For example, in the model templates below.

Base

Transforms Raw Data to Clean Tables focused on Core Concepts.

aircall_users_base

select
    id::varchar as user_id --pk
  , NULLIF(TRIM(name), '') as user_name
  , LOWER(TRIM(email)) as email
  , available as is_available
  , LOWER(TRIM(availability_status)) as availability_status
  , LOWER(TRIM(languages)) as languages
  , wrap_up_time as wrap_up_time_seconds
  , TRIM(time_zone) as timezone
  , created_at as created_at
  , _fivetran_synced as __updated_at
from {{sources.aircall.users}}
where _fivetran_deleted is not true

aircall_tags_base

select
    id::varchar as tag_id --pk
  , NULLIF(TRIM(name), '') as tag_name
  , NULLIF(TRIM(description), '') as tag_description
  , TRIM(color) as color
  , _fivetran_synced as __updated_at
from {{sources.aircall.tags}}
where _fivetran_deleted is not true

aircall_contacts_base

select
    id::varchar as contact_id --pk
  , LOWER(TRIM(first_name)) as first_name
  , LOWER(TRIM(last_name)) as last_name
  , NULLIF(TRIM(company_name), '') as company_name
  , NULLIF(TRIM(information), '') as information
  , is_shared as is_shared
  -- created_at / updated_at arrive as epoch STRINGS — cast to bigint before to_timestamp
  , to_timestamp(created_at::bigint) as created_at
  , to_timestamp(updated_at::bigint) as updated_at
  , _fivetran_synced as __updated_at
from {{sources.aircall.contact}}
where _fivetran_deleted is not true

aircall_contact_emails_base

select
    id::varchar as contact_email_id --pk
  , contact_id::varchar as contact_id
  , LOWER(TRIM(value)) as email
  , LOWER(TRIM(label)) as label
  , _fivetran_synced as __updated_at
from {{sources.aircall.contact_email}}
where _fivetran_deleted is not true

aircall_calls_base

select
    id::varchar as call_id --pk
  , user_id::varchar as user_id
  , number_id::varchar as number_id
  , LOWER(TRIM(direction)) as direction
  , LOWER(TRIM(status)) as status
  , LOWER(TRIM(missed_call_reason)) as missed_call_reason
  , raw_digits as raw_digits
  , RIGHT(REGEXP_REPLACE(raw_digits, '[^0-9]', '', 'g'), 10)::varchar as phone_clean
  , duration as duration_seconds
  , cost as cost
  , archived as is_archived
  , (answered_at is not null) as is_answered
  -- started_at / answered_at / ended_at arrive as epoch INTEGERS
  , to_timestamp(started_at) as started_at
  , to_timestamp(answered_at) as answered_at
  , to_timestamp(ended_at) as ended_at
  , _fivetran_synced as __updated_at
from {{sources.aircall.call}}
where _fivetran_deleted is not true

aircall_call_tags_base

select
    call_id::varchar || '-' || tag_id::varchar as id --pk (composite call_id + tag_id)
  , call_id::varchar as call_id
  , tag_id::varchar as tag_id
  , tagged_by::varchar as tagged_by_user_id
  , to_timestamp(created_at) as tagged_at
  , _fivetran_synced as __updated_at
from {{sources.aircall.call_tag}}
where _fivetran_deleted is not true

Fact

Creates a "Fact" about a core concept. For example, creating a customer order fact that defines when a customer placed their first order. This then gets joined downstream in output.

aircall_call_tag_facts

select
    ct.call_id as call_id --pk
  , STRING_AGG(t.tag_name, ', ' ORDER BY t.tag_name) as list_tags
  , count(*) as n_tags
from {{models.aircall_call_tags_base}} ct
left join {{models.aircall_tags_base}} t on ct.tag_id = t.tag_id
group by 1

aircall_call_facts

select
    c.call_id as call_id --pk
  , c.user_id
  , c.direction
  , c.status
  , c.missed_call_reason
  , c.duration_seconds
  , c.cost
  , c.is_answered
  , c.is_archived
  , c.started_at
  , c.answered_at
  , c.ended_at
  -- seconds spent waiting before the call was answered
  , extract(epoch from (c.answered_at - c.started_at)) as ring_seconds
  , ct.list_tags
  , ct.n_tags
from {{models.aircall_calls_base}} c
left join {{models.aircall_call_tag_facts}} ct on c.call_id = ct.call_id

aircall_user_call_facts

select
    u.user_id as user_id --pk
  , count(c.call_id) as n_calls
  , count(c.call_id) filter (where c.direction = 'inbound') as n_inbound_calls
  , count(c.call_id) filter (where c.direction = 'outbound') as n_outbound_calls
  , count(c.call_id) filter (where c.is_answered) as n_answered_calls
  , count(c.call_id) filter (where not c.is_answered) as n_missed_calls
  , sum(c.duration_seconds) as total_talk_seconds
  , avg(c.duration_seconds) as avg_talk_seconds
  , min(c.started_at) as first_call_at
  , max(c.started_at) as last_call_at
from {{models.aircall_users_base}} u
left join {{models.aircall_call_facts}} c on u.user_id = c.user_id
group by 1

Output

Final tables ready for use in explores in Switchboard. Output tables typically represent core business concepts that get joined together.

aircall_calls_output

select
    c.call_id as call_id --pk
  , c.started_at
  , c.answered_at
  , c.ended_at
  , c.direction
  , c.status
  , c.missed_call_reason
  , c.is_answered
  , c.is_archived
  , c.duration_seconds
  , c.ring_seconds
  , c.cost
  , c.user_id
  , u.user_name as agent_name
  , u.email as agent_email
  , c.list_tags
  , c.n_tags
from {{models.aircall_call_facts}} c
left join {{models.aircall_users_base}} u on c.user_id = u.user_id

aircall_users_output

select
    u.user_id as user_id --pk
  , u.user_name
  , u.email
  , u.is_available
  , u.availability_status
  , u.timezone
  , uf.n_calls
  , uf.n_inbound_calls
  , uf.n_outbound_calls
  , uf.n_answered_calls
  , uf.n_missed_calls
  , uf.total_talk_seconds
  , uf.avg_talk_seconds
  , uf.first_call_at
  , uf.last_call_at
from {{models.aircall_users_base}} u
left join {{models.aircall_user_call_facts}} uf on u.user_id = uf.user_id

Aircall Models

On this page