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 |
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). |
combined_google_ad_data
One row per (date, Google Ads creative unit). Union of src_google_ads.ad_performance
(Search/Display/Shopping/Video, grained at ad_group_ad) and src_google_ads.pmax_ad_performance
(Performance Max, grained at asset_group). PMax campaigns don't have ad_groups or ads —
the asset_group is the closest analog, so creative_unit_type disambiguates the two:
'ad' for rows from ad_performance and 'asset_group' for rows from pmax_ad_performance.
Campaign fields and metrics are harmonized across both sources; search-only columns
(ad_group_, ad_) are null on PMax rows and pmax-only columns (asset_group_*) are null
on search rows. Cost is converted from micros to dollars.
Model tests: dbt_utils.unique_combination_of_columns, dbt_utils.recency
| Column | Description | Tests |
|---|---|---|
date |
Reporting date for the row (from SEGMENTS.DATE). |
not_null |
customer_name |
Descriptive name of the Google Ads customer/account. | |
campaign_id |
Google Ads campaign id. | not_null |
campaign_name |
Campaign display name. | |
campaign_status |
Campaign status (for example, ENABLED, PAUSED, REMOVED). | |
bidding_strategy_type |
Bidding strategy type applied to the campaign. | |
advertising_channel_type |
Advertising channel type (for example, SEARCH, DISPLAY, SHOPPING, VIDEO, PERFORMANCE_MAX). | |
advertising_channel_sub_type |
Advertising channel sub-type, when applicable. | |
creative_unit_type |
Discriminator for the source feed and creative unit grain: 'ad' for rows from src_google_ads.ad_performance (Search/Display/etc., grained at ad_group_ad), or 'asset_group' for rows from src_google_ads.pmax_ad_performance (PMax, grained at asset_group). |
not_null, accepted_values |
creative_unit_id |
Unified creative unit id — ad id on 'ad' rows, asset_group id on 'asset_group' rows. |
not_null |
creative_unit_name |
Unified creative unit display name — ad name or asset_group name. | |
creative_unit_status |
Unified creative unit status — ad status (from AD_GROUP_AD.STATUS) or asset_group status. |
|
creative_unit_final_urls |
Unified array of final landing-page URLs — ad final_urls or asset_group final_urls. | |
ad_group_id |
Ad group id. Populated on 'ad' rows; null on 'asset_group' rows (PMax has no ad groups). |
|
ad_group_name |
Ad group display name. Null on 'asset_group' rows. |
|
ad_group_type |
Ad group type. Null on 'asset_group' rows. |
|
ad_group_status |
Ad group status. Null on 'asset_group' rows. |
|
ad_id |
Ad id (matches creative_unit_id on 'ad' rows). Null on 'asset_group' rows. |
|
ad_name |
Ad display name. Null on 'asset_group' rows. |
|
ad_type |
Ad type (for example, RESPONSIVE_SEARCH_AD). Null on 'asset_group' rows. |
|
ad_status |
Ad status within its ad group. Null on 'asset_group' rows. |
|
asset_group_id |
Asset group id (matches creative_unit_id on 'asset_group' rows). Null on 'ad' rows. |
|
asset_group_name |
Asset group display name. Null on 'ad' rows. |
|
asset_group_status |
Asset group status. Null on 'ad' rows. |
|
asset_group_ad_strength |
Google's ad-strength rating for the asset group (for example, POOR, AVERAGE, GOOD, EXCELLENT). Null on 'ad' rows. |
|
clicks |
Number of clicks for the row. | |
impressions |
Number of impressions for the row. | |
cost |
Spend in currency units for the row, converted from METRICS.COST_MICROS (divided by 1,000,000). |
|
ctr |
Click-through rate (clicks / impressions) for the row. | |
average_cpc |
Average cost per click for the row (currency units). | |
conversions |
Number of primary conversions attributed to the row. | |
conversions_value |
Monetary value of primary conversions for the row. | |
all_conversions |
Number of all conversions (primary + secondary) for the row. | |
all_conversions_value |
Monetary value of all conversions for the row. | |
view_through_conversions |
View-through conversions for the row (mainly meaningful for display/video/PMax inventory). | |
airbyte_extracted_at |
Extraction timestamp from Airbyte for the underlying source row. |
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_from_service_version |
Service version of the caller-side Tin Can device at call time; defaults to v1 when caller is Tin Can and version is unavailable. | |
call_to_service_version |
Service version of the recipient-side Tin Can device at call time; defaults to v1 when recipient is Tin Can and version is unavailable. |
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). |
dim_device
One row per physical device line, spanning both the current device system and the frozen
legacy_devices system (frozen 2026-08-06). Replaced devices_joined_full_history, which was built
only on legacy_devices and so was blind to every device created after the freeze.
GRAIN is one row per routing_id. routing_id is the dial identifier that both raw transaction tables actually use (legacy_cdr.src/dst and cdr.src/dst) and is the same number as legacy_devices.device_id. It is the only device identifier that spans both systems, so it is the intended foreign key for transaction-derived models.
IMPORTANT, THREE ID COLUMNS: routing_id joins the raw CDR tables. device_id (= device.id) joins call_logs rows where cdr_version = 'version 2'. legacy_device_id (= legacy_devices.device_id) joins call_logs rows where cdr_version = 'version 1'. Never join on a bare device id without knowing which system it came from: the device.id and legacy_devices.device_id ranges OVERLAP, and no device kept its number across the migration, so a wrong join returns plausible WRONG rows rather than zero rows.
NAMING CAUTION: here device_id means device.id. Elsewhere in this project (call_logs,
participant_daily_activity, fct_customer_scores) the bare name device_id still means the
LEGACY id. Check which one you have before joining.
routing_id must always be treated as VARCHAR. Some devices carry a routing_id of the form
'
The 'xN' suffix marks a SECOND DEVICE ON THE SAME HOUSEHOLD (a sibling's phone, or a replacement handset) -- it is NOT a number recycled to a different customer, and the base and suffixed devices belong to the same customer. This is why joining in routing space is safe across both eras. Note this is a DIFFERENT pair of columns from the device.id / legacy_devices.device_id collision described above -- that one is the surrogate space against the routing space, and this model's spine never touches device.id.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
routing_id |
Dial identifier and the grain of this model. Present on every row. Same number space as legacy_devices.device_id. Use this to join raw legacy_cdr / cdr. Always VARCHAR, never coerced to a number -- some values are non-numeric, such as '1243230x4'. | unique, not_null |
device_id |
device.id from the current system. Null for retired devices that have no device row. |
|
legacy_device_id |
legacy_devices.device_id. Null for devices created after the 2026-08-06 freeze. | |
subscription_id |
Owning subscription id (device.subscription_id). Null for legacy-only rows. | |
stripe_subscription_id |
Stripe subscription id for the line, and the intended bridge to RAW_DB.STRIPE.SUBSCRIPTIONS for plan, price and billing status. Prefers subscription.stripe_subscription_id; falls back to legacy_devices.stripe_subscription_id for retired devices with no current row. Current wins because legacy is frozen, so a line that re-subscribed after the freeze carries a stale legacy id. NOT UNIQUE, and not a subscription count. Multi-device households share a single Stripe subscription, and a small number of Stripe subscriptions span more than one line, so counting distinct routing_id per Stripe subscription is the only safe direction. Use this to look up billing attributes, never to count subscriptions. |
|
customer_id |
Owning customer id, via subscription then account; falls back to legacy_devices.customer_id. | |
created_at |
True device activation timestamp, UTC. Sourced from legacy_devices.created_at where available because device.created_at is a migration stamp (every pre-migration device was stamped ~May 2026); device.created_at is used only for genuinely new post-freeze devices. | |
updated_at |
Last update timestamp, UTC. Prefers device.updated_at; legacy_devices is frozen so its value is stale. | |
device_name |
Device display name. | |
device_avatar |
Avatar URL. | |
device_type |
Hardware model (WP816 is the Wi-Fi Tin Can, HT801 the discontinued Flashback variant). | |
device_mac |
Device MAC address. Substantially more complete than the legacy column it replaces. | |
thingsboard_device_id |
ThingsBoard entity id, and the join key to RAW_DB.THINGSBOARD.THINGSBOARD_REPORTS.ID for firmware, online status and device health. That table stores repeated snapshots, so dedupe it to the latest EXTRACTED_AT per id before joining or the join will fan out. Current-system only — null for legacy-only rows, which have no ThingsBoard equivalent. A NON-NULL VALUE DOES NOT GUARANTEE TELEMETRY. Thousands of devices carry an id that ThingsBoard has never reported on, and roughly half of those have call activity, so they are in service rather than dormant. Treat a missing telemetry row as "not reported" and not as "device offline"; the two are different, and only the first is what a failed join tells you. |
|
caller_id |
Caller id display name. | |
did_number |
DID / outbound phone number. | |
enabled_911 |
Whether emergency calling is enabled (boolean; legacy stored this as 0/1). | |
onboarding_complete |
Whether onboarding is complete (boolean; legacy stored this as 0/1). | |
dnd_enabled |
Whether do-not-disturb is on (boolean; legacy stored this as text 'active'/'inactive'). | |
dnd_until |
Do-not-disturb expiry timestamp, UTC. | |
twilio_sid |
Twilio phone SID. | |
street |
Emergency address street line 1. | |
street_secondary |
Emergency address street line 2. | |
city |
Emergency address city. | |
state_province |
Emergency address state or province. | |
zip_postal_code |
Emergency address zip or postal code. | |
country |
Emergency address country. | |
twilio_emergency_address_sid |
Twilio emergency address SID. | |
emergency_address_confirmed_date |
When the emergency address was registered, UTC. From emergency_address_status.created_at, the current-system equivalent of the legacy confirmed date. This being non-null only means a status row exists — it is NOT proof that E911 registration succeeded. Read emergency_address_status for that. |
|
emergency_address_status |
E911 registration status for the line, from emergency_address_status.status. Values are registered, unregistered, failed and pending_removal. This is the column that answers "is emergency calling actually set up", and it is the reason not to infer setup from emergency_address_confirmed_date alone: a line can carry a confirmed date and still sit in failed or unregistered. Current-system only — null for legacy-only rows, which have no equivalent. Do not read a null as "not registered"; it means the device predates the current emergency-address system. |
participant_daily_activity
One row per (participant device, call_day). Built from call_logs, plus legacy_devices for pre-cutover
device identity and the device table for current-era device identity.
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 |
this_participant_service_version |
Service version for the participant device used for this daily rollup row. | |
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 |
this_participant_service_version |
Service version for the participant device in full-history daily activity (v1 for legacy historical rows where assigned). | |
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
Full-history participant-side call log. Each row represents one Tin Can participant on one call, not one deduplicated underlying call. Calls between two Tin Can devices can therefore appear once for each participant. Historical participant-slice records and current participant_logs records are unioned into the same schema.
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. | |
this_participant_cdr_version |
Which CDR recorded this row: 'version 1' or 'version 2'. Determines which id space this_participant_device_id is in, so it is the key to joining dim_device -- 'version 1' joins dim_device.legacy_device_id, 'version 2' joins dim_device.device_id. Do NOT use this_participant_service_version for that: it describes the participant's device, not the recording CDR, and the two disagree on ~421k rows. | |
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. |
fct_ga_events
Event-level Google Analytics 4 fact table from src_big_query.events_all.
Includes event metadata, page context, device/geo dimensions, ecommerce revenue fields,
and three distinct traffic-attribution scopes, with event timestamps converted to
America/Los_Angeles.
The attribution column families are not interchangeable:
- first_click_* - user-scoped first touch, from GA4 traffic_source.
- event_* - the UTM parameters (utm_source, utm_medium, utm_campaign, utm_term,
utm_content, utm_id), from GA4 collected_traffic_source. Event-scoped, so these
populate only on the event that carried the UTMs (typically the session landing hit)
and are NULL on later events in the session, including purchase. To attribute a
conversion, join back to the session's session_start event.
- last_click_* - session-scoped last click, from GA4 cross_channel_campaign. Broadest
coverage, but has no utm_term or utm_content equivalent.
Data begins on April 3, 2026 and should be populated continuously going forward from that date. This data source will never contain traffic data for dates before April 3 2026.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
_airbyte_raw_id |
Raw Airbyte record identifier from the source event row. | |
event_timestamp |
Event timestamp converted from UTC to America/Los_Angeles. | |
event_name |
GA4 event name. | |
user_pseudo_id |
Anonymous GA4 user identifier. | |
ga_session_id |
GA4 session identifier extracted from event parameters. | |
platform |
Platform where the event occurred (for example, WEB, IOS, or ANDROID). | |
page_host |
Host/domain parsed from page_location. | |
page_path |
URL path parsed from page_location. | |
page_title |
Page title extracted from event parameters. | |
referrer_page_host |
Host/domain parsed from page_referrer. | |
referrer_page_path |
URL path parsed from page_referrer. | |
device_category |
Device category reported by GA4 (for example, desktop, mobile, or tablet). | |
device_mobile_brand_name |
Mobile device brand name. | |
device_mobile_model_name |
Mobile device model name. | |
device_operating_system |
Device operating system. | |
device_operating_system_version |
Device operating system version. | |
device_browser |
Browser name from GA4 web_info. | |
device_browser_version |
Browser version from GA4 web_info. | |
geo_city |
Event city from GA4 geo metadata. | |
geo_region |
Event region/state from GA4 geo metadata. | |
geo_country |
Event country from GA4 geo metadata. | |
geo_metro |
Event metro area from GA4 geo metadata. | |
geo_continent |
Event continent from GA4 geo metadata. | |
first_click_medium |
First-touch traffic medium from GA4 traffic_source. | |
first_click_source |
First-touch traffic source from GA4 traffic_source. | |
first_click_campaign_name |
First-touch campaign name from GA4 traffic_source. | |
event_medium |
Event-level manual medium from GA4 collected_traffic_source parameters. | |
event_source |
Event-level manual source from GA4 collected_traffic_source parameters. | |
event_campaign_name |
Event-level manual campaign name from GA4 collected_traffic_source parameters. | |
event_campaign_id |
Event-level manual campaign id from GA4 collected_traffic_source parameters. | |
event_term |
Event-level manual term from GA4 collected_traffic_source parameters. | |
event_content |
Event-level manual ad/content tag from GA4 collected_traffic_source parameters. | |
last_click_channel |
Last-click primary channel group from GA4 cross-channel campaign attribution. | |
last_click_platform |
Last-click source platform from GA4 cross-channel campaign attribution. | |
last_click_medium |
Last-click medium from GA4 cross-channel campaign attribution. | |
last_click_source |
Last-click source from GA4 cross-channel campaign attribution. | |
last_click_campaign_name |
Last-click campaign name from GA4 cross-channel campaign attribution. | |
last_click_campaign_id |
Last-click campaign id from GA4 cross-channel campaign attribution. | |
items |
Array of ecommerce item objects associated with the event. | |
transaction_id |
Ecommerce transaction identifier. | |
revenue_amount |
Ecommerce purchase revenue in USD; defaults to 0 when missing. | |
shipping_amount |
Ecommerce shipping amount in USD; defaults to 0 when missing. | |
tax_amount |
Ecommerce tax amount in USD; defaults to 0 when missing. |
fct_marketing_attributed_orders
One row per (Shopify order × marketing touchpoint) — a v3 multi-touch 33/33/33 position-based attribution model. Built from fct_ga_events purchase events joined to shopify.orders (test orders filtered out). GA occasionally double-fires the purchase event on confirmation-page refresh, so events are deduped to keep the earliest per transaction.
Attribution logic: - Brand-strip: remove sessions belonging to brand campaigns from every user's clickstream. Brand campaigns are identified via a campaign_id lookup against combined_google_ad_data. - Bucket each order into one of four attribution_bucket values — 'multi_touch', 'Brand only', 'Direct only', 'no_clicks' — and apply LNDC (last-non-direct-click) + 33/33/33 credit for the multi_touch bucket. Special buckets produce one row per order.
Revenue convention: revenue_gross and units_ordered are FRACTIONALIZED per touchpoint (= order-total × credit_fraction). SUM(revenue_gross) across any grouping is safe and correctly-scaled. Use SUM(credit_fraction) as the fractional order count in place of COUNT(order_id). Post-discount but pre-returns; a returns join will add revenue_net later.
Brand handling: brand-attributed revenue lands in attribution_bucket = 'Brand only' with ad_platform = 'Google Ads (Brand Only)' so brand is visible as its own line item in ad_platform breakdowns. The is_brand flag mirrors the same flag on fct_marketing_spend — filter both with WHERE NOT is_brand to hide brand end-to-end.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
order_id |
Shopify order id. Composite PK with touchpoint_position. | not_null |
user_pseudo_id |
GA4 anonymous user identifier of the buyer at purchase time. | |
order_ts |
Order timestamp (Pacific time), from GA's purchase event. | |
order_date |
Order date (Pacific time), derived from order_ts. | not_null |
cart_price_with_taxes_and_shipping |
Total cart price including taxes and shipping. Deliberately verbose; NOT a usable revenue figure. Order-level value repeated on every touchpoint row — use with COUNT(DISTINCT order_id) if summing. | |
shipping |
Shipping amount on the order (USD pass-through, not company revenue). | |
tax |
Tax amount on the order (USD pass-through, not company revenue). | |
attribution_bucket |
One of 'multi_touch', 'Brand only', 'Direct only', 'no_clicks'. multi_touch = order had ≥1 non-brand paid/organic touch; Brand only = only brand-campaign touches (or brand + direct); Direct only = only direct sessions; no_clicks = no GA sessions in the lookback. | not_null, accepted_values |
is_brand |
TRUE iff attribution_bucket = 'Brand only'. Symmetric with the same flag on fct_marketing_spend — filter both with WHERE NOT is_brand to hide brand end-to-end. Brand only rows also carry ad_platform = 'Google Ads (Brand Only)' so brand shows up as a distinct line item in ad_platform breakdowns. | not_null |
touchpoint_position |
1-indexed position of this touchpoint within the order's non-brand touch sequence. Always 1 for Brand only / Direct only / no_clicks rows. Composite PK with order_id. | not_null |
n_touchpoints |
Number of non-brand touchpoints attributed to this order. Always 1 for Brand only / Direct only / no_clicks rows. | |
credit_fraction |
Fraction of the order attributed to this touchpoint (sums to 1.0 across an order). 33/33/33 for multi_touch; 1.0 for the other three buckets. Use SUM(credit_fraction) in place of COUNT(order_id) when counting attributed orders. | |
revenue_gross |
FRACTIONALIZED gross revenue for this touchpoint (= order revenue × credit_fraction). Safe to SUM across any grouping — the total across all rows equals total order revenue. Post-discount but pre-returns. | |
units_ordered |
FRACTIONALIZED unit count for this touchpoint (= order units × credit_fraction). Same pattern as revenue_gross. | |
source |
GA session source (utm_source) for the touchpoint. NULL on special buckets. | |
medium |
GA session medium (utm_medium) for the touchpoint. NULL on special buckets. | |
ad_platform |
'Meta Ads' | 'Google Ads' |
campaign_id |
Vendor campaign id for the touchpoint (TEXT). NULL on special buckets. | |
campaign_name |
Vendor campaign name for the touchpoint. NULL on special buckets. | |
ad_set_id |
Unified ad-set-level identifier of the touchpoint session, from event_term: adset_id (Meta) | ad_group_id (Google standard) |
ad_id |
Ad id of the touchpoint session, from event_content. NULL on special buckets, non-paid touches, Google PMax (which has no ad concept), and paid touches where UTM tagging hasn't yet flowed IDs. |
fct_marketing_spend
One row per (date, ad platform, campaign, ad-set-or-asset-group, ad). Pure-spend model: spend, clicks, and impressions only. Platform-claimed conversions/revenue belong in a separate reconciliation model so our house ROAS (= attributed revenue / this spend) stays cleanly computable without platform self-attribution leaking in.
Sources: - Meta: src_facebook_ads.custom_ad_performance (native ad-grain). - Google: combined_google_ad_data, which unions PMAX_AD_PERFORMANCE and AD_PERFORMANCE, normalizes column names, and converts cost from micros to USD. - TikTok: src_tiktok_ads.ads_reports_daily for daily metrics, joined to src_tiktok_ads.ads to resolve parent campaign_id, ad_group_id, and the corresponding names. Spend/clicks/impressions live in the metrics JSON OBJECT as string-y values and are cast to FLOAT/NUMBER on extraction.
Grain notes by source: Meta rows have campaign_id, ad_set_id, and ad_id all populated. Google PMax rows have campaign_id and ad_set_id (= the asset_group_id) populated, with ad_id NULL because PMax has no ad concept — the asset group is the lowest creative grain. Google non-PMax rows (when running) populate campaign_id, ad_set_id (= ad_group_id), and ad_id. TikTok rows have campaign_id, ad_set_id (= adgroup_id), and ad_id all populated. ad_platform values match the same-named column on fct_marketing_attributed_orders so the two models join cleanly. All IDs are cast to TEXT for a uniform join key against GA UTM values.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
spend_date |
Date of ad spend. | not_null |
ad_platform |
Paid-ad platform: 'Meta Ads', 'Google Ads', 'TikTok Ads', or 'Google Ads (Brand Only)'. Matches the same-named column on fct_marketing_attributed_orders. Brand campaigns are routed to 'Google Ads (Brand Only)' so brand shows up as its own line item in ad_platform breakdowns. | not_null, accepted_values |
campaign_id |
Campaign id (TEXT). Meta's campaign_id, Google Ads CAMPAIGN.ID cast to text, or TikTok's campaign_id (resolved from the ADS dimension table). | not_null |
campaign_name |
Campaign display name. | |
ad_set_id |
Unified ad-set-level identifier (TEXT): adset_id (Meta), ad_group_id (Google non-PMax), asset_group_id (Google PMax), or adgroup_id (TikTok). | |
ad_set_name |
Ad set / ad group / asset group display name. | |
ad_id |
Ad id (TEXT). Populated for Meta, Google non-PMax, and TikTok rows. NULL for Google PMax rows because PMax has no ad concept — the asset group is the lowest creative grain. | |
ad_name |
Ad display name. NULL for Google PMax rows. | |
is_brand |
TRUE for brand-campaign rows (currently all Google: Shopping_Brand_US, Search_Brand_US, Branded Search). Match rule: campaign_name ILIKE '%brand%' AND NOT ILIKE '%nonbrand%' / '%non-brand%'. Brand rows also carry ad_platform = 'Google Ads (Brand Only)' so brand shows up as a distinct line item in ad_platform breakdowns. Symmetric pair to is_brand on fct_marketing_attributed_orders — filter both with WHERE NOT is_brand to fully hide brand from a report. | not_null |
spend |
Spend in USD for the row. | |
clicks |
Number of clicks for the row. | |
impressions |
Number of impressions for the row. |
fct_customer_scores
One row per active subscriber with daily churn and engagement scores. Materialized as a table and fully rebuilt on every dbt run (no history is kept). Covers three subscriber types, distinguished by plan_type:
monthly — active monthly paid subscribers (Party Line plan), ≥35 days since first_paid_start, first_paid_start ≥ 2025-07-01 (the training-cohort start; earlier payers are not scored), not past_due and no pending cancellation. Receives churn_score + monthly_churn_score + ces_score + monthly_ces_score. annual — active annual paid subscribers, same eligibility rules. Receives all four scores; churn scores are a soft approximation (model was trained on monthly subscribers only). free — active free (can-to-can) subscribers with a live device, ≥35 days since GREATEST(first sub start, first device online). churn_score and monthly_churn_score are null; ces_score and monthly_ces_score serve as an engagement index.
Score definitions (all four are calibrated probabilities from forward-labelled models; none is normalized or rescaled): churn_score — Party Line churn risk: probability that a paid subscriber requests cancellation within the next 56 days (0.0–1.0). Null for free. monthly_churn_score — the same churn model fitted to a 28-day horizon: probability of a cancellation request within the next 28 days. Null for free. ces_score — Customer Engagement Score: probability that the customer requests cancellation within the next 56 days (0.0–1.0); lower = more engaged. Populated for all subscriber types. monthly_ces_score — the CES feature set fitted to a 28-day horizon: probability of a cancellation request within the next 28 days.
Models: both trained with a forward-labelled design (score at date t using only data before t; label = cancellation request within t+56d or t+28d) on 12 semi-monthly snapshots Feb–Jul 2026 of monthly-only paid subscribers with first_paid_start ≥ 2025-07-01 and ≥35 days observed (~325k customer-snapshots, ~43k customers). CES (trained 2026-09-15, 8 features): out-of-time AUC 0.668–0.697, calibration 3.69% predicted vs 3.74% observed. Churn (trained 2026-09-16, the 8 CES features + r28_can_call_pct): out-of-time AUC 0.670–0.701, calibration 3.68% vs 3.74%. The two models rank paid customers almost identically; the can-to-can share is the only feature that differs, and the other Party Line features tested (ext_no_answer_rate, ever_ext_call) added no out-of-sample power. Annual subscribers are scored with the monthly-trained formulas as an as-if-monthly index. Coefficients are hardcoded as SQL arithmetic; the retraining procedure is documented in the SQL file header.
Model tests: dbt_utils.unique_combination_of_columns
| Column | Description | Tests |
|---|---|---|
stripe_cust |
Stripe customer id. Primary key — one row per active subscriber. | unique, not_null |
plan_type |
Subscriber type — 'monthly', 'annual', or 'free'. | not_null, accepted_values |
score_date |
Date on which scores were computed (current_date at run time). | not_null |
cohort_start |
Eligibility start date used for feature computation. first_paid_start for paid subscribers; GREATEST(first_sub_start, first_device_online) for free subscribers. | |
tenure_days |
Days from cohort_start to score_date. | |
churn_score |
Party Line churn risk: calibrated probability (0.0–1.0) that the subscriber requests cancellation within the next 56 days. Forward-labelled model trained on monthly paid subscribers; annual subscribers receive the same formula as an as-if-monthly index. Null for free subscribers. Training-period monthly-paid rate ≈ 0.037; production mean not yet measured on a live run. | |
monthly_churn_score |
Probability (0.0–1.0) that the subscriber requests cancellation within the next 28 days, from the same feature set as churn_score fitted to a 28-day label. Null for free subscribers. Training-period monthly-paid rate ≈ 0.020. E.g. 0.032 = "~3.2% chance of requesting cancellation in the next four weeks." | |
ces_score |
Customer Engagement Score: calibrated probability (0.0–1.0) that the customer requests cancellation within the next 56 days. Lower = more engaged. Forward-labelled model trained on monthly paid subscribers; populated for all subscriber types (annual and free use it as an engagement index). Training-period monthly-paid rate ≈ 0.038; measured production mean 0.035 on 2026-09-15. | |
monthly_ces_score |
Probability (0.0–1.0) that the customer requests cancellation within the next 28 days, from the same feature set as ces_score fitted to a 28-day label. Directly comparable in scale to monthly_churn_score. Populated for all subscriber types. Training-period monthly-paid rate ≈ 0.020; measured production mean 0.018 on 2026-09-15. | |
w1_active_days |
Days in week 1 (days 1–7 from cohort_start) with at least one answered call. | |
pct_weeks_healthy |
Fraction of weeks from week 2 onward (up to score_date) in which the subscriber participated in ≥2 calls. 0.0–1.0. | |
num_devices |
Number of Tin Can devices registered to this subscriber. | |
r28_active_days |
Distinct days with at least one answered call in the trailing 28 days. | |
r28_calls_per_active_day |
Average calls per active day in the trailing 28 days (0 when r28_active_days = 0). | |
log_r28_max_call_sec |
Natural log of (1 + longest answered call duration in seconds) in the trailing 28 days. | |
r28_vm_backlog_5plus |
1 if the subscriber has 5 or more unchecked voicemails at score time (voicemails_received − voicemail_checks ≥ 5 in the trailing 28 days), else 0. | |
ever_ext_call |
1 if the subscriber has ever made a successful external (Party Line) call, else 0. Null for free subscribers (no Party Line access). | |
ever_had_ticket |
1 if the subscriber has ever submitted a Zendesk support ticket, else 0. | |
r28_can_call_pct |
Can-to-can dependency index for the trailing 28 days, computed as greatest(0, successful_calls_made − num_successful_external_calls) / successful_calls_made, and 0 when there were no successful outgoing calls. NOTE the two counts are not on the same basis: the external count covers both directions while the denominator is outgoing only, so the guard floors the value at 0 for roughly 45% of active paid subscribers and about 60% of rows with outgoing calls read exactly 0. It is therefore an indicator that is high only for households whose outgoing calls are almost entirely can-to-can, not a clean share. Training and production compute it identically, so the churn model is valid for the feature as defined here; a redefinition on a consistent basis would require a retrain. The one feature the churn model uses that the CES does not. Null for free subscribers. |