Skip to content

Tin Can Customer Identity

Canonical records

  • RAW_DB.TINCAN.ACCOUNT (joined to RAW_DB.TINCAN.TINCAN_USER on TINCAN_USER.ACCOUNT_ID = ACCOUNT.ID) is the standard customer-level profile source for Tin Can analysis.
  • Grain: one row per CUSTOMER_ID (ACCOUNT is 1:1 on CUSTOMER_ID; each account has exactly one TINCAN_USER).
  • Use ACCOUNT for CUSTOMER_ID, STRIPE_CUSTOMER_ID, SHOPIFY_CUSTOMER_ID, NAME, and TIME_ZONE; use TINCAN_USER for EMAIL, PHONE, FIRST_NAME, and LAST_NAME.
  • Treat these as complete. CUSTOMER_ID, NAME, EMAIL, FIRST_NAME/LAST_NAME, and TIME_ZONE are present on effectively every row, across both the 2025 and 2026 cohorts. STRIPE_CUSTOMER_ID is present on nearly all. Query them directly.
  • ANALYTICS_DB.ANALYTICS.DIM_DEVICE is the standard device dimension for Tin Can analysis.
  • Grain: one row per ROUTING_ID — the dial identifier that spans both the current and the legacy device systems.
  • It carries three id columns, and the distinction matters: ROUTING_ID (the grain; also the key used by the raw CDR tables), DEVICE_ID (= RAW_DB.TINCAN.DEVICE.ID; null for retired devices with no current row), and LEGACY_DEVICE_ID (= RAW_DB.TINCAN.LEGACY_DEVICES.DEVICE_ID; null for devices created after the 2026-08-06 freeze).
  • Never join on DEVICE_ID alone when the activity could be pre-cutover. The DEVICE_ID and LEGACY_DEVICE_ID number ranges overlap and no device kept its number across the migration, so joining on the wrong key returns plausible wrong rows rather than zero rows. See Standard join path below.
  • Use it to map devices to customers through CUSTOMER_ID, and for device fields such as DEVICE_NAME, CALLER_ID, DID_NUMBER, DEVICE_TYPE, DEVICE_MAC, and CREATED_AT.
  • DIM_DEVICE sees the whole fleet, including every device created after the 2026-08-06 legacy freeze.
  • There is no customer dimension anywhere in ANALYTICS_DB. DIM_DEVICE is the only dimension model in that schema. Do not go looking for a DIM_CUSTOMER or a customers table — the two customer-level models there are CUSTOMERS_TO_STRIPE (an identity-matching bridge to Stripe; not a profile source and not a signup-date source) and FCT_CUSTOMER_SCORES (a scoring fact). Customer grain is built, not looked up: roll DIM_DEVICE up to CUSTOMER_ID and join ACCOUNT for profile fields, as in Standard join path below.

Tables that no longer exist

RAW_DB.TINCAN.LEGACY_CUSTOMERS, RAW_DB.TINCAN.LEGACY_CONTACTS, and ANALYTICS_DB.ANALYTICS.DEVICES_JOINED_FULL_HISTORY were dropped in the 2026-08-06 V1 retirement. They are not deprecated-but-queryable — they are gone, and any query naming one fails to compile. Do not try them to verify a number or to fill a gap; there is nothing behind them. Use ACCOUNT + TINCAN_USER for customers, CONTACT + CONTACT_NUMBER for contacts, and DIM_DEVICE for devices.

(RAW_DB.TINCAN.LEGACY_DEVICES and RAW_DB.TINCAN.LEGACY_CDR are a separate case: both were retained and are still queryable. They hold the V1 device dimension and the V1 call record respectively.)

Customer fields that ACCOUNT / TINCAN_USER do not carry

Fields a V1-era analysis might expect on a customer record, and where they actually live now: - Address (street / city / state / zip / country): not on ACCOUNT or TINCAN_USER. Address data lives in RAW_DB.TINCAN.ADDRESS, which is linked as an emergency / service address via SUBSCRIPTION.EMERGENCY_ADDRESS_ID = ADDRESS.ID — not as a direct customer attribute. Map it explicitly if you need it, and do not assume it is the customer's billing or mailing address. - Shopify id → ACCOUNT.SHOPIFY_CUSTOMER_ID (a different identifier from the V1 one). Populated for a minority of customers, so never use it as a primary customer key or as a join spine — only as an enrichment from an existing CUSTOMER_ID. - A single notifications flag → split into TINCAN_USER.SMS_NOTIFICATIONS and TINCAN_USER.EMAIL_NOTIFICATIONS (two booleans). - Language, Firestore uid, referral code: no equivalent on ACCOUNT / TINCAN_USER. Treat as unavailable rather than hunting for a substitute.

Standard join path

For customer-level analyses that start from device or participant activity: 1. Start from the activity table at its native grain, usually ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY. 2. Join to ANALYTICS_DB.ANALYTICS.DIM_DEVICE to recover CUSTOMER_ID, keyed by era. THIS_PARTICIPANT_DEVICE_ID is mixed-space: it holds a legacy device id where THIS_PARTICIPANT_CDR_VERSION = 'version 1', and a current DEVICE.ID where it is 'version 2'. Use two equi-joins rather than one OR condition, so Snowflake can hash-join:

LEFT JOIN ANALYTICS_DB.ANALYTICS.DIM_DEVICE dv1
       ON a.THIS_PARTICIPANT_DEVICE_ID = dv1.LEGACY_DEVICE_ID::VARCHAR
      AND a.THIS_PARTICIPANT_CDR_VERSION = 'version 1'
LEFT JOIN ANALYTICS_DB.ANALYTICS.DIM_DEVICE dv2
       ON a.THIS_PARTICIPANT_DEVICE_ID = dv2.DEVICE_ID::VARCHAR
      AND a.THIS_PARTICIPANT_CDR_VERSION = 'version 2'
-- then, in the SELECT:
COALESCE(dv1.CUSTOMER_ID, dv2.CUSTOMER_ID) AS CUSTOMER_ID,
COALESCE(dv1.ROUTING_ID,  dv2.ROUTING_ID)  AS ROUTING_ID

Either id column used on its own is wrong: LEGACY_DEVICE_ID is null for every device created after the 2026-08-06 freeze, so joining on it alone silently drops the entire post-freeze fleet; DEVICE_ID alone mis-attributes pre-cutover activity to the wrong customer through id collision. 3. Aggregate to CUSTOMER_ID if the final output should be one customer per row. 4. Join to RAW_DB.TINCAN.ACCOUNT on CUSTOMER_ID (and to RAW_DB.TINCAN.TINCAN_USER on ACCOUNT_ID = ACCOUNT.ID) to add customer profile fields.

This keeps call and usage metrics correct for customers with multiple devices.

Choose the right grain

Use customer grain when the output is meant to represent a customer or outreach audience, including: - outreach or CRM lists - review or research candidate lists - first-call and activation milestone reporting by customer - customer profile exports with usage rolled up across all devices

Use device grain when the question is specifically about a device, including: - device activation and CREATED_AT - DEVICE_NAME, CALLER_ID, DID_NUMBER, DEVICE_TYPE, or DEVICE_MAC - per-device adoption, inventory, or call behavior

If a device-level field is requested in a customer-level analysis, either: - aggregate it explicitly to customer grain, or - return a separate device-level table

Do not let a device field implicitly turn a customer list into one row per device.

Common rollups

Customer usage

For customer usage lists, sum device-level metrics across every device owned by the same CUSTOMER_ID before joining profile fields.

First successful call by customer

For customer milestone reporting, define a customer's first successful call as the earliest CALL_DAY across any of their devices where SUCCESSFUL_CALLS_PARTICIPATED_IN > 0.

Activation and signup dates

DIM_DEVICE.CREATED_AT is the device activation timestamp and is device-level, so only include it directly in device-grain outputs. If a customer-level activation date is needed, define the rollup explicitly, such as the earliest device CREATED_AT across that customer's devices. (CREATED_AT is sourced from LEGACY_DEVICES.CREATED_AT wherever available, because DEVICE.CREATED_AT is a migration timestamp that stamped every pre-cutover device around May 2026.)

RAW_DB.TINCAN.ACCOUNT.CREATED_AT is the customer signup timestamp, and it is trustworthy. Use it directly for signup cohorts, and do not go looking for an earlier or more authoritative source.

The migration-timestamp problem is specific to DEVICE.CREATED_AT. Do not generalize it. It is tempting to assume every CREATED_AT in RAW_DB.TINCAN was flattened by the V1 to V2 migration, and to read the earliest ACCOUNT.CREATED_AT (March 2025) as a platform-launch artifact hiding real earlier history. It is not. ACCOUNT.CREATED_AT is never null, spreads organically across months, and its single largest day is 2025-12-25 — Christmas morning, exactly the shape this product should have. The March 2025 floor is genuine: there are no Tin Can customers before it.

Klaviyo mapping

Use RAW_DB.KLAVIYO.PROFILES for Klaviyo profile resolution.

Recommended matching order from Tin Can customers to Klaviyo profiles: 1. Primary match: RAW_DB.TINCAN.ACCOUNT.CUSTOMER_ID = RAW_DB.KLAVIYO.PROFILES.ATTRIBUTES:properties:customer_id::NUMBER 2. Email fallback: LOWER(RAW_DB.TINCAN.TINCAN_USER.EMAIL) = LOWER(RAW_DB.KLAVIYO.PROFILES.ATTRIBUTES:email::VARCHAR) (join TINCAN_USER to ACCOUNT on ACCOUNT_ID = ACCOUNT.ID), only when no customer-id match exists

Implementation notes: - Treat literal 'None' values in ATTRIBUTES:properties:customer_id as missing, not valid customer IDs. - Prefer the customer-id match when both methods could resolve a profile. - When using both methods in one output, include a match_method field so downstream users can distinguish customer_id matches from email fallbacks.

Practical defaults

  • For one-row-per-customer outputs, start from ACCOUNT (joined to TINCAN_USER) as the profile source and roll device activity up to CUSTOMER_ID.
  • For one-row-per-device outputs, start from DIM_DEVICE and add customer fields only after deciding that device grain is correct.
  • When in doubt, treat requests for “customer”, “user”, or an outreach audience as customer-grain unless the user explicitly asks for per-device detail.