Skip to content

Tin Can Customer Identity

Canonical records

  • RAW_DB.TINCAN.LEGACY_CUSTOMERS is the standard customer-level profile table for Tin Can analysis.
  • Grain: one row per CUSTOMER_ID
  • Use it for customer attributes such as FIRST_NAME, LAST_NAME, EMAIL, PHONE, address, timezone, and linked IDs.
  • ANALYTICS_DB.ANALYTICS.DEVICES_JOINED_FULL_HISTORY is the standard device dimension for Tin Can analysis.
  • Grain: one row per DEVICE_ID
  • Use it to map devices to customers through CUSTOMER_ID, and for device fields such as DEVICE_NAME, DID_NUMBER, DEVICE_TYPE, FIRST_ONLINE, and device status.
  • RAW_DB.TINCAN.ACCOUNT and RAW_DB.TINCAN.TINCAN_USER are not the approved customer dimension for Tin Can reporting. In the current warehouse copy they are sparse and should not be used to represent the full customer population.

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.DEVICES_JOINED_FULL_HISTORY on THIS_PARTICIPANT_DEVICE_ID = DEVICE_ID::VARCHAR to recover CUSTOMER_ID. 3. Aggregate to CUSTOMER_ID if the final output should be one customer per row. 4. Join to RAW_DB.TINCAN.LEGACY_CUSTOMERS on CUSTOMER_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 FIRST_ONLINE - DEVICE_NAME, DID_NUMBER, DEVICE_TYPE, or device status - 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 dates

FIRST_ONLINE is device-level. Only include it directly in device-grain outputs. If a customer-level activation date is needed, define the rollup explicitly, such as earliest device FIRST_ONLINE across that customer's devices.

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.LEGACY_CUSTOMERS.CUSTOMER_ID = RAW_DB.KLAVIYO.PROFILES.ATTRIBUTES:properties:customer_id::NUMBER 2. Email fallback: LOWER(RAW_DB.TINCAN.LEGACY_CUSTOMERS.EMAIL) = LOWER(RAW_DB.KLAVIYO.PROFILES.ATTRIBUTES:email::VARCHAR) 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 LEGACY_CUSTOMERS as the profile table and roll device activity up to CUSTOMER_ID.
  • For one-row-per-device outputs, start from DEVICES_JOINED_FULL_HISTORY 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.