Switchboard Docs
ReferenceSource Models

CallRail Models

Copy-paste SQL models for turning raw CallRail data into business-ready call and form-submission reporting.

Copy and Paste these CallRail Models into your Data Layer to quickly go from raw data sources to business ready CallRail Enhancement to other models. 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.

callrail_companies_base

SELECT
  TRIM(id)::VARCHAR AS id --PK
  , account_id::VARCHAR AS account_id
  , TRIM(name)::VARCHAR AS name
  , status
  , time_zone
  , created_at
  , disabled_at
  , lead_scoring_enabled
  , call_score_enabled
  , call_scribe_enabled
  , form_capture
  , dni_active
FROM {{sources.callrail.company}}
WHERE _fivetran_deleted IS FALSE

callrail_trackers_base

SELECT
  TRIM(id)::VARCHAR AS id --PK
  , account_id::VARCHAR AS account_id
  , company_id::VARCHAR AS company_id
  , TRIM(name)::VARCHAR AS name
  , type
  , source_type
  , status
  , created_at
  , disabled_at
  , sms_enabled
  , sms_supported
  , call_flow_type
  , call_flow_destination_number::VARCHAR AS call_flow_destination_number
  , call_flow_recording_enabled
  , whisper_message::VARCHAR AS whisper_message
  , tracking_number
FROM {{sources.callrail.tracker}}
WHERE _fivetran_deleted IS FALSE

callrail_tags_base

SELECT
  id::VARCHAR AS id. --PK
  , account_id::VARCHAR AS account_id
  , company_id::VARCHAR AS company_id
  , TRIM(name)::VARCHAR AS name
  , tag_level
  , status
  , color
  , created_at
FROM {{sources.callrail.tags}}
WHERE _fivetran_deleted IS FALSE

callrail_calls_base

SELECT
  TRIM(id)::VARCHAR AS id --PK
  , account_id::VARCHAR AS account_id
  , company_id::VARCHAR AS company_id
  , tracker_id::VARCHAR AS tracker_id
  , person_id::VARCHAR AS person_id
  , created_at
  , start_time
  , duration
  , recording_duration
  , direction
  , type
  , answered
  , voicemail
  , first_call
  , lead_status
  , sentiment
  , note
  , summary
  , transcription
  -- caller info
  , TRIM(LOWER(customer_name))::VARCHAR AS customer_name
  , customer_phone_number::VARCHAR AS customer_phone_number
  , RIGHT(REGEXP_REPLACE(customer_phone_number::VARCHAR, '[^0-9]', '', 'g'), 10)::VARCHAR AS customer_phone_number_clean
  , customer_city::VARCHAR AS customer_city
  , customer_state::VARCHAR AS customer_state
  , customer_country::VARCHAR AS customer_country
  -- tracking
  , tracking_phone_number::VARCHAR AS tracking_phone_number
  , RIGHT(REGEXP_REPLACE(tracking_phone_number::VARCHAR, '[^0-9]', '', 'g'), 10)::VARCHAR AS tracking_phone_number_clean
  , business_phone_number::VARCHAR AS business_phone_number
  , RIGHT(REGEXP_REPLACE(business_phone_number::VARCHAR, '[^0-9]', '', 'g'), 10)::VARCHAR AS business_phone_number_clean
  , source_name::VARCHAR AS source_name
  , source::VARCHAR AS source
  , medium::VARCHAR AS medium
  , campaign::VARCHAR AS campaign
  , keywords::VARCHAR AS keywords
  , device_type::VARCHAR AS device_type
  , referrer_domain::VARCHAR AS referrer_domain
  , referring_url::VARCHAR AS referring_url
  , landing_page_url::VARCHAR AS landing_page_url
  -- UTM params
  , utm_source::VARCHAR AS utm_source
  , utm_medium::VARCHAR AS utm_medium
  , utm_campaign::VARCHAR AS utm_campaign
  , utm_content::VARCHAR AS utm_content
  , utm_term::VARCHAR AS utm_term
  , gclid::VARCHAR AS gclid
  -- first touch attribution
  , first_touch_source::VARCHAR AS first_touch_source
  , first_touch_medium::VARCHAR AS first_touch_medium
  , first_touch_campaign::VARCHAR AS first_touch_campaign
  , first_touch_keywords::VARCHAR AS first_touch_keywords
  , first_touch_landing::VARCHAR AS first_touch_landing
  , first_touch_referrer::VARCHAR AS first_touch_referrer
  , first_touch_device::VARCHAR AS first_touch_device
  , first_touch_event_date
  -- last touch attribution
  , last_touch_source::VARCHAR AS last_touch_source
  , last_touch_medium::VARCHAR AS last_touch_medium
  , last_touch_campaign::VARCHAR AS last_touch_campaign
  , last_touch_keywords::VARCHAR AS last_touch_keywords
  , last_touch_landing::VARCHAR AS last_touch_landing
  , last_touch_referrer::VARCHAR AS last_touch_referrer
  , last_touch_device::VARCHAR AS last_touch_device
  , last_touch_event_date
  -- call quality
  , speaker_percent_agent
  , speaker_percent_customer
  , prior_calls
  , total_calls
  , tags
  , keywords_spotted
  , highlights
  , recording
  , timeline_url
FROM {{sources.callrail.call}}
WHERE _fivetran_deleted IS FALSE

callrail_form_submissions_base

SELECT
  TRIM(id)::VARCHAR AS id --PK
  , account_id::VARCHAR AS account_id
  , company_id::VARCHAR AS company_id
  , person_id::VARCHAR AS person_id
  , submitted_at
  , first_form
  -- customer info
  , TRIM(LOWER(customer_name))::VARCHAR AS customer_name
  , TRIM(LOWER(customer_email))::VARCHAR AS customer_email
  , customer_phone_number::VARCHAR AS customer_phone_number
  , RIGHT(REGEXP_REPLACE(customer_phone_number::VARCHAR, '[^0-9]', '', 'g'), 10)::VARCHAR AS customer_phone_number_clean
  -- attribution
  , source::VARCHAR AS source
  , medium::VARCHAR AS medium
  , campaign::VARCHAR AS campaign
  , keywords::VARCHAR AS keywords
  , referrer::VARCHAR AS referrer
  , referring_url::VARCHAR AS referring_url
  , landing_page_url::VARCHAR AS landing_page_url
  , form_url::VARCHAR AS form_url
  , custom_gclid::VARCHAR AS gclid
FROM {{sources.callrail.form_submission}}
WHERE _fivetran_deleted IS FALSE

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.

callrail_calls_facts

SELECT
  c.id --PK
  , c.account_id
  , c.company_id
  , co.name AS company_name
  , co.time_zone AS company_time_zone
  , c.tracker_id
  , t.name AS tracker_name
  , t.source_typeAS tracker_source_type
  , t.type AS tracker_type
  , c.person_id
  , c.created_at
  , c.start_time
  , c.duration
  , ROUND(c.duration / 60.0, 2) AS duration_minutes
  , c.recording_duration
  , c.direction
  , c.type
  , c.answered
  , c.voicemail
  , c.first_call
  , c.lead_status
  -- derived flags
  , CASE WHEN c.lead_status IN ('good_lead', 'previously_marked_good_lead') 
					  THEN TRUE 
				 ELSE FALSE 
	   END AS is_lead
  , CASE WHEN c.answered IS TRUE THEN TRUE ELSE FALSE END                                            AS is_answered
  , CASE WHEN c.voicemail IS TRUE THEN TRUE ELSE FALSE END                                          AS is_voicemail
  , c.sentiment
  , c.note
  , c.summary
  , c.transcription
  , c.customer_name
  , c.customer_phone_number
  , c.customer_phone_number_clean
  , c.customer_city
  , c.customer_state
  , c.customer_country
  , c.tracking_phone_number
  , c.tracking_phone_number_clean
  , c.business_phone_number
  , c.business_phone_number_clean
  , c.source_name
  , c.source
  , c.medium
  , c.campaign
  , c.keywords
  , c.device_type
  , c.referrer_domain
  , c.referring_url
  , c.landing_page_url
  , c.utm_source
  , c.utm_medium
  , c.utm_campaign
  , c.utm_content
  , c.utm_term
  , c.gclid
  , c.first_touch_source
  , c.first_touch_medium
  , c.first_touch_campaign
  , c.first_touch_keywords
  , c.first_touch_landing
  , c.first_touch_referrer
  , c.first_touch_device
  , c.first_touch_event_date
  , c.last_touch_source
  , c.last_touch_medium
  , c.last_touch_campaign
  , c.last_touch_keywords
  , c.last_touch_landing
  , c.last_touch_referrer
  , c.last_touch_device
  , c.last_touch_event_date
  , c.speaker_percent_agent
  , c.speaker_percent_customer
  , c.prior_calls
  , c.total_calls
  , c.tags
  , c.keywords_spotted
  , c.highlights
  , c.recording
  , c.timeline_url
FROM {{models.callrail_calls_base}} c
LEFT JOIN {{models.callrail_companies_base}} co ON c.company_id = co.id
LEFT JOIN {{models.callrail_trackers_base}} t ON c.tracker_id = t.id

callrail_form_submissions_facts

SELECT
  f.id --pk
  , f.account_id
  , f.company_id
  , co.name AS company_name
  , co.time_zone AS company_time_zone
  , f.person_id
  , f.submitted_at
  , f.first_form
  , f.customer_name
  , f.customer_email
  , f.customer_phone_number
  , f.customer_phone_number_clean
  , f.source
  , f.medium
  , f.campaign
  , f.keywords
  , f.referrer
  , f.referring_url
  , f.landing_page_url
  , f.form_url
  , f.gclid
FROM {{models.callrail_form_submissions_base}} f
LEFT JOIN {{models.callrail_companies_base}} co ON f.company_id = co.id

Output

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

callrail_calls_output

SELECT
  f.id
  , f.account_id
  , f.company_id
  , f.company_name
  , f.company_time_zone
  , f.tracker_id
  , f.tracker_name
  , f.tracker_source_type
  , f.tracker_type
  , f.person_id
  , f.created_at
  , f.start_time
  , f.duration
  , f.duration_minutes
  , f.direction
  , f.type
  , f.answered
  , f.voicemail
  , f.first_call
  , f.lead_status
  , f.is_lead
  , f.is_answered
  , f.is_voicemail
  , f.sentiment
  , f.note
  , f.summary
  , f.customer_name
  , f.customer_phone_number
  , f.customer_phone_number_clean
  , f.customer_city
  , f.customer_state
  , f.customer_country
  , f.tracking_phone_number
  , f.tracking_phone_number_clean
  , f.business_phone_number
  , f.business_phone_number_clean
  , f.source_name
  , f.source
  , f.medium
  , f.campaign
  , f.keywords
  , f.device_type
  , f.referrer_domain
  , f.referring_url
  , f.landing_page_url
  , f.utm_source
  , f.utm_medium
  , f.utm_campaign
  , f.utm_content
  , f.utm_term
  , f.gclid
  , f.first_touch_source
  , f.first_touch_medium
  , f.first_touch_campaign
  , f.first_touch_keywords
  , f.first_touch_landing
  , f.first_touch_referrer
  , f.first_touch_device
  , f.first_touch_event_date
  , f.last_touch_source
  , f.last_touch_medium
  , f.last_touch_campaign
  , f.last_touch_keywords
  , f.last_touch_landing
  , f.last_touch_referrer
  , f.last_touch_device
  , f.last_touch_event_date
  , f.speaker_percent_agent
  , f.speaker_percent_customer
  , f.prior_calls
  , f.total_calls
  , f.tags
  , f.keywords_spotted
  , f.recording
  , f.timeline_url
  , CASE WHEN f.is_lead THEN 1 ELSE 0 END AS is_lead_int
  , CASE WHEN f.is_answered THEN 1 ELSE 0 END AS is_answered_int
  , CASE WHEN f.is_voicemail THEN 1 ELSE 0 END AS is_voicemail_int
  , CASE
      WHEN f.duration > 60
        AND ROW_NUMBER() OVER (
          PARTITION BY f.customer_phone_number
          ORDER BY CASE WHEN f.duration > 60 THEN f.start_time END
        ) = 1
      THEN 1
      ELSE 0
    END AS phone_number_call_sequence_over_60_seconds
FROM {{models.callrail_calls_facts}} f

callrail_form_submissions_output

SELECT
  f.id --pk
  , f.account_id
  , f.company_id
  , f.company_name
  , f.company_time_zone
  , f.person_id
  , f.submitted_at
  , f.first_form
  , f.customer_name
  , f.customer_email
  , f.customer_phone_number
  , f.customer_phone_number_clean
  , f.source
  , f.medium
  , f.campaign
  , f.keywords
  , f.referrer
  , f.referring_url
  , f.landing_page_url
  , f.form_url
  , CASE WHEN f.first_form THEN 1 ELSE 0 END AS first_form_int
FROM {{models.callrail_form_submissions_facts}} f

Applovin Models

Copy-paste SQL models for turning raw Applovin advertiser reports into business-ready campaign performance reporting.

GA4 Models

Copy-paste SQL models for turning raw GA4 (Google Analytics 4) data into business-ready traffic and transaction attribution reporting.

On this page

Basecallrail_companies_basecallrail_trackers_basecallrail_tags_basecallrail_calls_basecallrail_form_submissions_baseFactcallrail_calls_factscallrail_form_submissions_factsOutputcallrail_calls_outputcallrail_form_submissions_output