Models
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 and sales-vs-goals reporting.
| Column | Type | Description | Tests |
|---|---|---|---|
date |
Calendar date (Pacific time) for the daily aggregate. | ||
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.
| Column | Type | Description | Tests |
|---|---|---|---|
date |
Calendar date for the daily row. | ||
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_cleaned, call_was_answered, talk_time_corrected, and left_voicemail.
| Column | Type | Description | Tests |
|---|---|---|---|
uniqueid |
Asterar unique call id (from CDR). | ||
call_date |
Timestamp of the call (group first calldate). | ||
call_from |
Source identifier (device_id or raw src) for the call. | ||
tc_call_from_name |
Human-readable label for call_from (caller_id or external number). | ||
call_is_from_tin_can |
True if call_from matches a Tincan device or DID. | ||
dest_id_raw |
Raw destination identifier used for routing. | ||
dest_number_raw |
Raw destination number (E.164-style when external). | ||
call_to |
Destination identifier (device_id or raw dst) for the call. | ||
tc_call_to_name |
Human-readable label for call_to. | ||
call_is_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 |
Cleaned disposition (NA for voicemail check, else ANSWERED/NO ANSWER/etc). | ||
call_was_answered |
True if the call had a human conversation (answered, not voicemail-only). | ||
talk_time_corrected |
Billable talk time (interval); null when not answered and no voicemail. | ||
left_voicemail |
True if caller left a voicemail. | ||
from_shopify_user_id |
Shopify user id for caller (currently null). | ||
to_shopify_user_id |
Shopify user id for callee (currently null). | ||
from_customer_email |
Customer email for caller (currently null). | ||
to_customer_email |
Customer email for callee (currently null). | ||
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.
| Column | Type | Description | Tests |
|---|---|---|---|
uniqueid |
Unique call id from source (FreePBX, Twilio, or Asterar). - not null - unique | ||
call_date |
Timestamp of the call. | ||
call_from |
Source identifier for the call. | ||
tc_call_from_name |
Human-readable label for call_from. | ||
call_is_from_tin_can |
True if call_from matches a Tincan device or DID. | ||
dest_id_raw |
Raw destination identifier. | ||
dest_number_raw |
Raw destination number. | ||
call_to |
Destination identifier for the call. | ||
tc_call_to_name |
Human-readable label for call_to. | ||
call_is_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 |
Cleaned disposition. | ||
call_was_answered |
True if the call had a human conversation. | ||
talk_time_corrected |
Billable talk time (interval); null when not answered and no voicemail. | ||
left_voicemail |
True if caller left a voicemail. | ||
from_shopify_user_id |
Shopify user id for caller (currently null). | ||
to_shopify_user_id |
Shopify user id for callee (currently null). | ||
from_customer_email |
Customer email for caller (currently null). | ||
to_customer_email |
Customer email for callee (currently null). | ||
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.
| Column | Type | Description | Tests |
|---|---|---|---|
customer_id |
Tincan customer id. | ||
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.
| Column | Type | Description | Tests |
|---|---|---|---|
device_id |
Device identifier (Tincan or historical dial number). | ||
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: total_talk_duration, 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.
| Column | Type | Description | Tests |
|---|---|---|---|
call_day |
Date (Pacific) for the daily aggregate. | ||
day_of_week |
Day of week (mon, tues, …, sun). | ||
this_participant_name |
Participant caller_id or name. | ||
this_participant_device_id |
Participant device id. | ||
total_talk_duration |
Sum of talk_time_corrected for the day. | ||
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.
| Column | Type | Description | Tests |
|---|---|---|---|
call_day |
Date for the daily aggregate. | ||
day_of_week |
Day of week (mon, tues, …, sun). | ||
this_participant_name |
Participant name. | ||
this_participant_device_id |
Participant device or dial number id. | ||
total_talk_duration |
Sum of talk time for the day. | ||
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.
| Column | Type | Description | Tests |
|---|---|---|---|
this_participant_device_id |
Participant device id. | ||
this_participant_name |
Participant caller_id or name. | ||
call_day |
Date (Pacific) of the call. | ||
num_entries_forcall |
Number of participant rows for this call (1 for non–voicemail-check, or 1 for voicemail check per participant). | ||
uniqueid |
Call unique id. | ||
call_date |
Call timestamp. | ||
call_from |
Source identifier for the call. | ||
tc_call_from_name |
Human-readable label for call_from. | ||
call_is_from_tin_can |
True if call_from is Tincan. | ||
dest_id_raw |
Raw destination id. | ||
dest_number_raw |
Raw destination number. | ||
call_to |
Destination identifier. | ||
tc_call_to_name |
Human-readable label for call_to. | ||
call_is_to_tin_can |
True if call_to is Tincan. | ||
call_classification |
Type of call. | ||
is_voicemail_check |
True if voicemail check. | ||
disposition_cleaned |
Cleaned disposition. | ||
call_was_answered |
True if answered. | ||
talk_time_corrected |
Billable talk time. | ||
left_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.
| Column | Type | Description | Tests |
|---|---|---|---|
this_participant_device_id |
Participant device or dial number id. | ||
this_participant_name |
Participant name. | ||
call_day |
Date of the call. | ||
num_entries_forcall |
Participant rows per call. | ||
uniqueid |
Call unique id. | ||
call_date |
Call timestamp. | ||
call_from |
Source identifier. | ||
tc_call_from_name |
Label for call_from. | ||
call_is_from_tin_can |
True if call_from is Tincan. | ||
dest_id_raw |
Raw destination id. | ||
dest_number_raw |
Raw destination number. | ||
call_to |
Destination identifier. | ||
tc_call_to_name |
Label for call_to. | ||
call_is_to_tin_can |
True if call_to is Tincan. | ||
call_classification |
Type of call. | ||
is_voicemail_check |
True if voicemail check. | ||
disposition_cleaned |
Cleaned disposition. | ||
call_was_answered |
True if answered. | ||
talk_time_corrected |
Billable talk time. | ||
left_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, total_talk_duration, successful_calls_participated_in, calls_made/received, num_interlocutors, num_voicemail_checks.
| Column | Type | 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. | ||
this_participant_name |
Participant name. | ||
cal_week |
Week start (date) for the rollup. | ||
active_days |
Distinct days with calls made, participated in, or voicemails left. | ||
total_talk_duration |
Sum of talk time for the week. | ||
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. |