# Insights Data Connector

Brandfolder's Insights Data Connector allows customers to connect directly to their own data. The data is housed in Google BigQuery within a unique Google Cloud Platform (GCP) Project which is established for the customer. The data can be accessed with any existing BigQuery connection method using a GCP account associated with the customer GCP project. The data is refreshed nightly.

![Assets](https://cdn.bfldr.com/27C9EC93/at/qc3hvt6bgb2nnkg8nf5h54n7/Insights_Data_Connector_Schema_-_assets.png?auto=webp&format=png)
![CDN Aggregates](https://cdn.bfldr.com/27C9EC93/at/r6s9bhmf8k34np5b5kj8jq3/Insights_Data_Connector_Schema_-_cdn_aggregates.png?auto=webp&format=png)
![Events](https://cdn.bfldr.com/27C9EC93/at/7b78fg8rkxmjqhghf3pz7k/Insights_Data_Connector_Schema_-_events.png?auto=webp&format=png)
![Events with Share Links](https://cdn.bfldr.com/27C9EC93/at/qsmpbcgbfknfp3ms9twc46/Insights_Data_Connector_Schema_-_events_with_sharelink.png?auto=webp&format=png)
![Events with User Sessions](https://cdn.bfldr.com/27C9EC93/at/ccpht4bvmk7wnjq7gwq4g5z/Insights_Data_Connector_Schema_-_events_with_user_session.png?auto=webp&format=png)

## Schemas

The current version of the Insights Data Connector includes the following schemas, with more tables and columns planned in future versions. These tables exist within the `brandfolder` dataset in the customer’s GCP project. Each table can be accessed with the path `<GCP-project-name>.brandfolder.<table_name>`.

> Here is an example test query to use, replacing `<org-slug>` with the slug (unique identifier) for your organization.



```sql
SELECT * FROM `bfdc-<org-slug>.brandfolder.assets` LIMIT 1000
```

### Assets

> Assets represent the container for attachments/files.


> The `collections` column is an array. For notes on how to query array-type columns in BigQuery, please reference this BigQuery documentation.


> `availability` possible values are: `published`, `unpublished`, `expired`, `pending approval`.


> `type` possible values are: `Color`, `ExternalMedium`, `Font`, `GenericFile`,  `Person`, `Press`, `Text`.


> Note: `created_by` may contain `null` values at times. This can be due to guest uploads from users without Brandfolder accounts, files uploaded through bulk migration methods (SFTP, Google Cloud Storage, etc.), or users who have since been hard deleted from the database in adherence with federal data compliance and security constraints.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `name` | String |
| `description` | String |
| `brandfolder_key` | String |
| `section_name` | String |
| `type` | String |
| `availability` | String |
| `availability_start` | Timestamp |
| `availability_end` | Timestamp |
| `collections` | ARRAY (String) |
| `created_at` | Timestamp |
| `created_by` | String |


### Attachments

> Attachments are structured data references to unstructured data files.


> `created_by` will contain the `user_key` of the user who uploaded the latest file to the attachment.


> Note: `created_by` may contain `null` values at times. This can be due to guest uploads from users without Brandfolder accounts, files uploaded through bulk migration methods (SFTP, Google Cloud Storage, etc.), or users who have since been hard deleted from the database in adherence with federal data compliance and security constraints.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `filename` | String |
| `asset_key` | String |
| `created_at` | Timestamp |
| `created_by` | String |
| `source` | String |


### Brandfolders

> `privacy` possible values are: `public`, `private`, `stealth`.


> `slug` is a unique identifier for your organization.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `name` | String |
| `slug` | String |
| `privacy` | String |
| `created_at` | Timestamp |


### CDN Aggregates

> This table contains daily aggregates of CDN hits, grouped by object and referrer link.


> Objects can either be assets or attachments. It is recommended to join to both assets and attachments when consuming this table to get a full picture of CDN hits.


> `object_type` possible values are: `asset`, `attachment`.


| Column | Data Type |
|  --- | --- |
| `object_key` | String |
| `object_type` | String |
| `event_date` | Date |
| `referrer_link` | String |
| `hit_count` | Integer |
| `bytes_sum` | Integer |
| `cdn_link` | String |
| `brandfolder_key` | String |


### Collections

> `privacy` can contain three values: `public`, `private`, or `stealth`.


> `slug` is a unique identifier for your organization.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `name` | String |
| `slug` | String |
| `privacy` | String |
| `brandfolder_key` | String |
| `created_at` | Timestamp |


### Custom Field Values

> This table custom field values related to assets.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `custom_field_key_name` | String |
| `value` | String |
| `asset_key` | String |
| `created_at` | Timestamp |


### Events

> This table contains events on objects, where prepositional objects are the location from which the user accessed the object. For example, if a user downloaded an asset from inside a collection, the object would be that asset, and the prepositional object would be the collection.


> `object_type` possible values are: `asset`, `brandfolder`, `collection`.


> `type` possible values are: `viewed`, `downloaded`, `searched`, `shared`, `add_attachments`.


> `prepositional_object_type` possible values are: `brandfolder`, `collection`, `organization`, `sharelink`.


> Note when `object_type` and `prepositional_object_type` are both set to brandfolder or collection, the respective keys have the same value.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `object_key` | String |
| `object_type` | String |
| `type` | String |
| `prepositional_object_key` | String |
| `prepositional_object_type` | String |
| `brandfolder_key` | String |
| `session_key` | String |
| `user_key` | String |
| `created_at` | Timestamp |


### Sessions

> This table contains sessions related to events.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `anonymous` | Boolean |
| `email` | String |
| `city` | String |
| `country` | String |
| `created_at` | Timestamp |


### Tags

> This table contains tags related to assets.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `name` | String |
| `asset_key` | String |
| `auto_generated` | Boolean |
| `created_at` | Timestamp |
| `deleted_at` | Timestamp |


### Users

> This table contains your users that may be related to events.


> `saml_teams` are common separated when there is more than one.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `company` | String |
| `country` | String |
| `created_at` | Timestamp |
| `created_with_sso` | String |
| `deleted_at` | Timestamp |
| `department` | String |
| `email` | String |
| `first_name` | String |
| `last_active_at` | Timestamp |
| `last_name` | String |
| `locale` | String |
| `saml_teams` | String |
| `title` | String |
| `updated_at` | Timestamp |


### User Permissions

> This table contains all of the user permission records each user has within your organization.


> `permissible_type` and `permissible_key` are used to determine which environment a user has permissions on, e.g., `permissible_type` = `Collection` means the user has a collection-level permission, and the `permissible_key` will be the `key` for the collection.


> Note: A single user may have many permissions assigned to it across environments under your organization.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `user_key` | String |
| `permission_level` | String |
| `created_at` | Timestamp |
| `updated_at` | Timestamp |
| `permissible_key` | String |
| `permissible_type` | String |
| `deleted_at` | Timestamp |


### Sharelink

> This table contains data about sharelinks related to asset groups.


> `context_type` possible values are: `Brandfolder`, `Collection`, `Organization`.


| Column | Data Type |
|  --- | --- |
| `key` | String |
| `availability_start` | Timestamp |
| `availability_end` | Timestamp |
| `context_type` | String |
| `context_brandfolder_key` | String |
| `created_at` | Timestamp |
| `deleted_at` | Timestamp |
| `name` | String |
| `private_link` | Boolean |
| `link` | String |
| `view_only` | Boolean |


### Share Link Items

> This table contains data about sharelinks related to  assets.


| Column | Data Type |
|  --- | --- |
| `share_link_key` | String |
| `asset_key` | String |