Models
dim_legacy_devices
Legacy device slowly changing dimension.
Model tests: dbt_utils.mutually_exclusive_ranges
| Column | Description | Tests |
|---|---|---|
legacy_devices_key |
Unique legacy device key for slowly changing dimension. | unique, not_null |
device_id||'_'||valid_from |
Unique natural key for slowly changing dimension. | unique, not_null |
device_id |
Unique source id for legacy devices. | |
valid_from |
Timestamp that reflects the valid start date of the record. | |
valid_to |
Timestamp that reflects the valid end date of the record. | |
is_current |
Flag to denote this record is the current record for the device. | |
customer_id |
Owning Tincan customer id for the device. | |
device_mac |
Device MAC address. | |
extension |
Internal extension number assigned to the device. | |
did_number |
Direct inward dial (DID) or outbound caller id number. | |
caller_id |
Caller id display name for this device. | |
enabled_911 |
Flag indicating whether 911 emergency calling is enabled. | |
is_discoverable |
Flag indicating whether the device is discoverable in the app. | |
device_name |
Human-readable device name. | |
device_meta |
JSON metadata about the device. | |
device_avatar |
URL or identifier for the device avatar. | |
external_network |
Flag indicating whether the device is on an external network. | |
status |
Raw device status value in Tincan. | |
created_at |
Timestamp when the device record was created. | |
updated_at |
Timestamp when the device record was last updated. | |
subscription_type |
Subscription type associated with the device. | |
dnd_until |
Timestamp until which do-not-disturb is active. | |
dnd_status |
Current do-not-disturb status for the device. | |
onboarding_complete |
Flag indicating whether device onboarding is complete. | |
device_status |
Operational device status (for example, online or offline). | |
device_type |
Device type (for example, desk phone or softphone). | |
country |
Country associated with the device address. | |
city |
City associated with the device address. | |
zip_postal_code |
Zip or postal code for the device address. | |
state_province |
State or province for the device address. | |
street |
Primary street address for the device. | |
street_secondary |
Secondary street address line for the device. | |
twilio_sid |
Twilio SID for the associated phone number. | |
twilio_emergency_address_sid |
Twilio emergency address SID for the device. | |
emergency_address_confirmed_date |
Date when the emergency address was confirmed. | |
emergency_address_validated_date |
Date when the emergency address was validated. | |
service_version |
Service version of device. | |
first_online |
First online / install date. | |
stripe_subscription_id |
Stripe payment system subscription id for device. |
daily_sales
One row per calendar day (America/Los_Angeles) with daily sales metrics from Shopify. Aggregates orders and refunds into items_sold, gross_sales, discounts, refunds, and net_sales_revenue. Used as the base for blended CAC. Orders use order date and refunds use refund date.
Model tests: dbt_utils.unique_combination_of_columns, dbt_utils.recency, dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart
| Column | Description | Tests |
|---|---|---|
date |
Calendar date (Pacific time) for the daily aggregate. Orders use order date and refunds use refund date. | unique, not_null |
items_sold |
Total units sold (before refunds) for the day. | |
items_refunded |
Total units refunded for the day. | |
net_items_sold |
items_sold minus items_refunded. | |
gross_sales |
Total sales before discounts and refunds. | |
discounts |
Total order discounts applied. | |
refunds |
Total refund amount for the day. | |
net_sales_revenue |
gross_sales minus discounts minus refunds. |
daily_sales_blended_cac
One row per calendar day with ad spend (Meta + Google), attributed purchases, and sales. Includes blended CAC, ROAS, and month-to-date metrics (mtd_ad_spend, mtd_items_sold, mtd_net_revenue, mtd_cac, mtd_roas). Built from daily_sales and Meta/Google ad sources.
Model tests: dbt_utils.unique_combination_of_columns, dbt_utils.recency, dbt_expectations.expect_row_values_to_have_data_for_every_n_datepart
| Column | Description | Tests |
|---|---|---|
date |
Calendar date for the daily row. | unique, not_null |
total_ad_spend |
Combined Meta + Google ad spend for the day. | |
direct_ad_attributed_sales |
Purchases attributed to Meta and Google ads. | |
total_units_sold |
Net units sold (from daily_sales) for the day. | |
net_sales_revenue |
Net sales revenue for the day. | |
mtd_ad_spend |
Month-to-date ad spend through this date. | |
mtd_items_sold |
Month-to-date net units sold through this date. | |
mtd_net_revenue |
Month-to-date net revenue through this date. | |
blended_cac |
Total ad spend divided by total units sold for the day (null when no ad spend). | |
roas |
Net sales revenue divided by total ad spend for the day (null when no ad spend). | |
mtd_cac |
Month-to-date ad spend divided by month-to-date units sold. | |
mtd_roas |
Month-to-date net revenue divided by month-to-date ad spend (null when mtd ad spend is zero). |
call_logs
One row per call from Asterar CDR, filtered and deduplicated by linkedid. Joins CDR to Tincan devices (by device_id / DID) to resolve call_from and call_to labels. Excludes AppDial/voicemail-check noise; filters to calls from 2025-05-05 onward. Output includes call_classification (can_to_can, can_to_external, external_to_can, failed_call, etc.), disposition, call_was_answered, talk_time_seconds, and left_voicemail.
Model tests: dbt_utils.unique_combination_of_columns, dbt_utils.recency
| Column | Description | Tests |
|---|---|---|
unique_id |
Asterar unique call id (from CDR). | unique, not_null |
call_date |
Timestamp of the call (group first calldate) in PST. | |
call_from |
Source identifier (device_id for calls from other Tin Cans, raw src for calls from other types of phones) of the device that initiated the call. | |
call_from_name |
Human-readable label for call_from (caller_id or external number of device that initiated the call). | |
is_call_from_tin_can |
True if call_from matches a Tincan device or DID. | |
destination_id |
Raw destination identifier used for routing. | |
destination_number |
Raw destination number (E.164-style when external). | |
call_to |
Destination identifier (device_id for calls to other Tin Cans, raw src for calls to other types of phones) for the device to which the call was made. | |
call_to_name |
Human-readable label for call_to (caller_id or external number of device to which the call was made). | |
is_call_to_tin_can |
True if call_to matches a Tincan device or DID. | |
call_classification |
Type of call (can_to_can, can_to_external, external_to_can, failed_call, cancelled_call, voicemail_check, UNKNOWN). | |
is_voicemail_check |
True if call was a voicemail check (*97 or src = dst). | |
disposition |
Cleaned disposition (NA for voicemail check, else ANSWERED/NO ANSWER/etc). | |
is_call_answered |
True if the call had a human conversation (answered, not voicemail-only). | |
talk_time_seconds |
Billable talk time in seconds; null when not answered and no voicemail. | |
is_voicemail |
True if caller left a voicemail. | |
cdr_key_concat |
CDR composite key used for joins. |
call_logs_full_history
Union of (1) FreePBX fused call logs (bi_call_logs_fusion_freepbx), (2) Twilio external-to-Canada calls, and (3) call_logs (Asterar from 2025-05-05). Same schema as call_logs; provides full historical call set.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
unique_id |
Unique call id from source (FreePBX, Twilio, or Asterar). | unique, not_null |
call_date |
Timestamp of the call in PST. | |
call_from |
Source identifier (device_id for calls from other Tin Cans, raw src for calls from other types of phones) of the device that initiated the call. | |
call_from_name |
Human-readable label for call_from (caller_id or external number of device that initiated the call). | |
is_call_from_tin_can |
True if call_from matches a Tincan device or DID. | |
destination_id |
Raw destination identifier. | |
destination_number |
Raw destination number. | |
call_to |
Destination identifier (device_id for calls to other Tin Cans, raw src for calls to other types of phones) for the device to which the call was made. | |
call_to_name |
Human-readable label for call_to (caller_id or external number of device to which the call was made). | |
is_call_to_tin_can |
True if call_to matches a Tincan device or DID. | |
call_classification |
Type of call (can_to_can, can_to_external, external_to_can, etc.). | |
is_voicemail_check |
True if call was a voicemail check. | |
disposition |
Cleaned disposition. | |
is_call_answered |
True if the call had a human conversation. | |
talk_time_seconds |
Billable talk time in seconds; null when not answered and no voicemail. | |
is_voicemail |
True if caller left a voicemail. | |
cdr_key_concat |
CDR composite key (uniqueid in FreePBX branch). |
customers_to_stripe
One row per Tincan customer. Maps each customer to a Stripe customer via (1) explicit stripe_customer_id, or (2) email match, or (3) phone match. Best match chosen by priority_order and stripe created date. Used to join Tincan entities to Stripe billing data.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
customer_id |
Tincan customer id. | unique, not_null |
stripe_customer_id |
Matched Stripe customer id (null if no match). | |
match_type |
How the match was made (base, email, or phone). | |
stripe_customer_created_date |
Stripe customer creation timestamp (null when no match). |
devices_joined_full_history
Unified device list: full outer join of Tincan devices and historical dial numbers (all_dial_numbers). One row per device_id. Uses Tincan data when present, else historical; coalesces extension, did_number, caller_id, first_online, etc. Supports participant and call-log models that need full device history.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
device_id |
Device identifier (Tincan or historical dial number). | unique, not_null |
customer_id |
Owning customer id. | |
device_mac |
Device MAC address. | |
extension |
Extension number. | |
did_number |
DID or outbound caller id number. | |
caller_id |
Caller id display name. | |
enabled_911 |
Whether 911 is enabled. | |
is_discoverable |
Whether device is discoverable. | |
device_name |
Device display name. | |
device_meta |
Device metadata (JSON). | |
device_avatar |
Avatar URL. | |
external_network |
External network flag. | |
status |
Device status. | |
created_at |
Record creation timestamp. | |
updated_at |
Record update timestamp. | |
subscription_type |
Subscription type. | |
dnd_until |
DND until timestamp. | |
dnd_status |
DND status. | |
onboarding_complete |
Whether onboarding is complete. | |
device_status |
Device status. | |
device_type |
Device type. | |
country |
Country. | |
city |
City. | |
zip_postal_code |
Zip or postal code. | |
state_province |
State or province. | |
street |
Street address. | |
street_secondary |
Secondary street. | |
twilio_sid |
Twilio SID. | |
twilio_emergency_address_sid |
Twilio emergency address SID. | |
emergency_address_confirmed_date |
Emergency address confirmed date. | |
emergency_address_validated_date |
Emergency address validated date. | |
first_online |
First online / install date. |
participant_daily_activity
One row per (participant device, call_day). Built from call_logs and devices_joined_full_history. Aggregates per-device, per-day metrics: talk_time_seconds, successful_calls_participated_in, calls_made, calls_received, num_external_calls, num_voicemails_left/received, num_voicemail_checks. Excludes device 3635. Pacific time used for call_day.
Model tests: dbt_utils.recency, dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
call_day |
Date (Pacific) for the daily aggregate. | not_null |
day_of_week |
Day of week (mon, tues, …, sun). | |
this_participant_name |
Participant caller_id or name. | |
this_participant_device_id |
Participant device id. | not_null |
talk_time_seconds |
Sum of talk_time_seconds for the day. Measured in seconds. | |
successful_calls_participated_in |
Count of answered, non–voicemail-check calls participated in. | |
calls_made |
Outbound calls placed by this participant. | |
successful_calls_made |
Answered outbound calls. | |
calls_received |
Inbound calls received by this participant. | |
successful_calls_received |
Answered inbound calls. | |
num_interlocutors |
Distinct call counterparties (call_from/call_to) for the day. | |
num_external_calls |
Count of can_to_external or external_to_can calls. | |
num_successful_external_calls |
Answered external calls. | |
num_voicemails_left |
Voicemails left by this participant. | |
num_voicemails_received |
Voicemails received by this participant. | |
diff_from_first_call_day |
call_day minus first call day for this participant. | |
num_voicemail_checks |
Count of voicemail-check calls. |
participant_daily_activity_full_history
Union of participant_daily_activity and historical view_tc_participant_slice_daily_summary. Excludes historical rows where (call_day, this_participant_device_id) already exists in participant_daily_activity. Provides full-history participant daily metrics for reporting.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
call_day |
Date for the daily aggregate. | not_null |
day_of_week |
Day of week (mon, tues, …, sun). | |
this_participant_name |
Participant name. | |
this_participant_device_id |
Participant device or dial number id. | not_null |
talk_time_seconds |
Sum of talk time for the day. Measured in seconds. | |
successful_calls_participated_in |
Answered, non–voicemail-check calls participated in. | |
calls_made |
Outbound calls by this participant. | |
successful_calls_made |
Answered outbound calls. | |
calls_received |
Inbound calls received. | |
successful_calls_received |
Answered inbound calls. | |
num_interlocutors |
Distinct counterparties for the day. | |
num_external_calls |
External (can_to_external / external_to_can) calls. | |
num_successful_external_calls |
Answered external calls. | |
num_voicemails_left |
Voicemails left. | |
num_voicemails_received |
Voicemails received. | |
diff_from_first_call_day |
Days since first call day for this participant. | |
num_voicemail_checks |
Voicemail-check count (null in historical slice). |
participant_logs
Call-level participant view: one row per participant per call. Built from call_logs joined to Tincan devices (call_from or call_to). Includes this_participant_device_id, this_participant_name, call_day, num_entries_forcall, and all call fields. Excludes device 3635; uses Pacific time for call_day.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
this_participant_device_id |
Participant device id. | not_null |
this_participant_name |
Participant caller_id or name. | |
call_day |
Date (Pacific) of the call. | not_null |
num_entries_forcall |
Number of participant rows for this call (1 for non–voicemail-check, or 1 for voicemail check per participant). | |
unique_id |
Call unique id. | not_null |
call_date |
Call timestamp. | |
call_from |
Source identifier (device_id for calls from other Tin Cans, raw src for calls from other types of phones) of the device that initiated the call. | |
call_from_name |
Human-readable label for call_from (caller_id or external number of device that initiated the call). | |
is_call_from_tin_can |
True if call_from is Tincan. | |
destination_id |
Raw destination id. | |
destination_number |
Raw destination number. | |
call_to |
Destination identifier (device_id for calls to other Tin Cans, raw src for calls to other types of phones) for the device to which the call was made. | |
call_to_name |
Human-readable label for call_to (caller_id or external number of device to which the call was made). | |
is_call_to_tin_can |
True if call_to is Tincan. | |
call_classification |
Type of call. | |
is_voicemail_check |
True if voicemail check. | |
disposition |
Cleaned disposition. | |
is_call_answered |
True if answered. | |
talk_time_seconds |
Billable talk time in seconds. | |
is_voicemail |
True if voicemail left. | |
cdr_key_concat |
CDR key for the call. |
participant_logs_full_history
Union of participant_slice_underlying (historical) and participant_logs. Same schema as participant_logs. Full-history call-level participant view.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
this_participant_device_id |
Participant device or dial number id. | not_null |
this_participant_name |
Participant name. | |
call_day |
Date of the call. | not_null |
num_entries_forcall |
Participant rows per call. | |
unique_id |
Call unique id. | not_null |
call_date |
Call timestamp. | |
call_from |
Source identifier (device_id for calls from other Tin Cans, raw src for calls from other types of phones) of the device that initiated the call. | |
call_from_name |
Human-readable label for call_from (caller_id or external number of device that initiated the call). | |
is_call_from_tin_can |
True if call_from is Tincan. | |
destination_id |
Raw destination id. | |
destination_number |
Raw destination number. | |
call_to |
Destination identifier (device_id for calls to other Tin Cans, raw src for calls to other types of phones) for the device to which the call was made. | |
call_to_name |
Human-readable label for call_to (caller_id or external number of device to which the call was made). | |
is_call_to_tin_can |
True if call_to is Tincan. | |
call_classification |
Type of call. | |
is_voicemail_check |
True if voicemail check. | |
disposition |
Cleaned disposition. | |
is_call_answered |
True if answered. | |
talk_time_seconds |
Billable talk time in seconds. | |
is_voicemail |
True if voicemail left. | |
cdr_key_concat |
CDR key for the call. |
participant_weekly_summary_full_history
One row per (participant device, cal_week). Weekly rollup from participant_daily_activity_full_history and participant_logs_full_history. Covers weeks from each device’s first_online through latest data. Metrics include active_days, talk_time_seconds, successful_calls_participated_in, calls_made/received, num_interlocutors, num_voicemail_checks.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
first_online_date |
Date the device first came online. | |
first_online_week |
Week (truncated) of first_online. | |
this_participant_device_id |
Participant device id. | not_null |
this_participant_name |
Participant name. | |
cal_week |
Week start (date) for the rollup. | not_null |
active_days |
Distinct days with calls made, participated in, or voicemails left. | |
talk_time_seconds |
Sum of talk time for the week. Measured in seconds. | |
successful_calls_participated_in |
Answered, non–voicemail-check calls in the week. | |
calls_made |
Outbound calls in the week. | |
successful_calls_made |
Answered outbound calls. | |
calls_received |
Inbound calls in the week. | |
successful_calls_received |
Answered inbound calls. | |
num_interlocutors |
Distinct counterparties in the week. | |
num_voicemail_checks |
Voicemail-check count for the week. |