# Contacts

## v\_Contact\_{TenantName}

Customer contact information.

### Columns

| Column                | Type     | Description               |
| --------------------- | -------- | ------------------------- |
| `id`                  | varchar  | Unique contact identifier |
| `firstName`           | varchar  | First name                |
| `lastName`            | varchar  | Last name                 |
| `fullName`            | varchar  | Full display name         |
| `email`               | varchar  | Primary email address     |
| `phone`               | varchar  | Primary phone number      |
| `created`             | datetime | When contact was created  |
| `updated`             | datetime | Last update timestamp     |
| `contactCustomFields` | varchar  | JSON custom field data    |

### Example Queries

```sql
-- New contacts this month
SELECT COUNT(*) AS NewContacts
FROM v_Contact_YourCompany
WHERE created >= DATEADD(month, -1, GETDATE())

-- Contacts with email
SELECT COUNT(*) AS WithEmail
FROM v_Contact_YourCompany
WHERE email IS NOT NULL AND email != ''

-- Contact growth by month
SELECT
    YEAR(created) AS Year,
    MONTH(created) AS Month,
    COUNT(*) AS NewContacts
FROM v_Contact_YourCompany
GROUP BY YEAR(created), MONTH(created)
ORDER BY Year DESC, Month DESC
```

### Joining with Conversations

```sql
-- Conversations with contact names
SELECT
    c.id,
    co.fullName,
    co.email,
    c.started,
    c.statusName
FROM v_Conversation_YourCompany c
JOIN v_Contact_YourCompany co ON c.contactId = co.id

-- Top customers by conversation count
SELECT
    co.fullName,
    co.email,
    COUNT(*) AS Conversations
FROM v_Conversation_YourCompany c
JOIN v_Contact_YourCompany co ON c.contactId = co.id
GROUP BY co.fullName, co.email
ORDER BY Conversations DESC
```

### Dynamic Custom Field Columns

Your custom fields are automatically available as proper columns, prefixed with `custom_`. No JSON parsing needed!

**Example - if you have custom fields `company` and `employee_count`:**

```sql
SELECT
    id,
    fullName,
    custom_company,
    custom_employee_count
FROM v_Contact_YourCompany
WHERE custom_company IS NOT NULL;
```

**Result:**

```
id                                   | fullName      | custom_company | custom_employee_count
-------------------------------------|---------------|----------------|----------------------
8ae2aed0-cc44-4a7e-b272-a5ddafe9b5cf | John Smith    | Acme Corp      | 500
3cdc6422-60db-463d-8901-82c495845bd9 | Jane Doe      | TechStart      | 25
```

Custom field columns are:

* **Automatically generated** from your `v_CustomFieldDefinition_{TenantName}` table
* **Refreshed daily** at 2:00 AM UTC
* **Named as** `custom_{field_key}` (special characters become underscores)

> **Note:** These columns are dynamically generated by extracting values from the `contactCustomFields` JSON column. When you add or remove custom field definitions in Superagent, the new columns will appear after the next daily refresh. Contact support if you need an immediate refresh.

### Raw Custom Fields JSON (Advanced)

The `contactCustomFields` column contains the raw JSON if you need it:

```json
{
  "company": { "stringValue": "Acme Corp" },
  "employeeCount": { "integerValue": 500 },
  "isEnterprise": { "boolValue": true },
  "tags": { "arrayValues": ["vip", "partner"] }
}
```

| Field Type   | JSON Property  | Description               |
| ------------ | -------------- | ------------------------- |
| Text         | `stringValue`  | Free text                 |
| Integer      | `integerValue` | Whole numbers             |
| Number       | `numberValue`  | Decimal numbers           |
| Boolean      | `boolValue`    | true/false                |
| SingleSelect | `stringValue`  | Selected option           |
| MultiSelect  | `arrayValues`  | Array of selected options |

**Manual JSON extraction (if needed):**

```sql
-- Extract text/single-select value
SELECT
    fullName,
    JSON_VALUE(contactCustomFields, '$.company.stringValue') AS Company
FROM v_Contact_YourCompany

-- Extract numeric value
SELECT
    fullName,
    JSON_VALUE(contactCustomFields, '$.employeeCount.integerValue') AS EmployeeCount
FROM v_Contact_YourCompany

-- Filter by custom field
SELECT fullName, email
FROM v_Contact_YourCompany
WHERE JSON_VALUE(contactCustomFields, '$.isEnterprise.boolValue') = 'true'
```

***

## v\_ContactAddress\_{TenantName}

Contact addresses including email, phone, and social media identifiers.

### Columns

| Column        | Type     | Description                   |
| ------------- | -------- | ----------------------------- |
| `id`          | varchar  | Unique address identifier     |
| `contactId`   | varchar  | Link to parent contact        |
| `channel`     | int      | Channel type code             |
| `channelName` | varchar  | Channel type name (see below) |
| `address`     | varchar  | The actual address/identifier |
| `created`     | datetime | When address was added        |
| `updated`     | datetime | Last update timestamp         |

### Channel Type Values

| Code | Name              | Description                               |
| ---- | ----------------- | ----------------------------------------- |
| 0    | Unknown           | Unknown channel                           |
| 1    | FacebookMessenger | Facebook Messenger ID                     |
| 2    | WhatsApp          | WhatsApp number                           |
| 3    | Sms               | SMS phone number                          |
| 4    | EmbeddedChat      | Embedded chat session                     |
| 5    | Email             | Email address (includes form submissions) |
| 6    | VoiceCall         | Voice call phone number                   |

### Example Queries

```sql
-- All addresses for a contact
SELECT channelName, address
FROM v_ContactAddress_YourCompany
WHERE contactId = 'contact-id'

-- Contacts with WhatsApp
SELECT c.fullName, ca.address AS WhatsAppNumber
FROM v_Contact_YourCompany c
JOIN v_ContactAddress_YourCompany ca ON c.id = ca.contactId
WHERE ca.channelName = 'WhatsApp'

-- Count contacts by channel
SELECT channelName, COUNT(DISTINCT contactId) AS Contacts
FROM v_ContactAddress_YourCompany
GROUP BY channelName
ORDER BY Contacts DESC
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://atender-labs.gitbook.io/atender-labs-docs/analytics/views/contacts.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.