Channel99 Data Share (Snowflake)

Channel99 provides a low latency data share for customers with the need to export and combine performance data with internal data streams. This is commonly a necessity for data teams, high security/compliance organizations and those taking part in internal custom projects to support marketing efforts. 

Overview

Channel99 uses a Fact + Dimensional table structure in Snowflake to share marketing data with customers via Snowflake Secure Data Sharing. Below is the documentation for each view, including its purpose and key columns.

Setup

For initial setup instructions work with your Channel99 representative. 

Entity Relationship Diagram

The following diagram provides a visual representation of the relationships between the tables and views:

Fact Table

traffic

Purpose: Stores event-level data representing marketing activities and interactions.

Key Columns:

• event_id (STRING): Unique identifier for the event.
• event_type (STRING): Type of event (e.g., impression, click).
• event_date (STRING): Date of the event.
• channel_id (STRING): Identifier for the marketing channel.
• vendor_id (STRING): Identifier for the vendor.
• company_id (STRING): Identifier for the company.
• audience_id_list (ARRAY): List of audience IDs associated with the event.
• tag_id (STRING): Identifier for associated tags.
• ad_account_id (STRING): Identifier for the ad account.
• ad_campaign_group_id (STRING): Identifier for the ad campaign group.
• ad_campaign_id (STRING): Identifier for the related ad campaign.
• ad_group_id (STRING): Identifier for the associated ad group.
• ad_id (STRING): Identifier for the associated ad.
• ad_detail_id (STRING): Identifier for the associated ad detail.

Dimension Tables

channel

Purpose: Stores metadata about marketing channels.

Key Columns:

• channel_id (STRING): Unique identifier for the channel.
• channel_name (STRING): Name of the channel.

vendor

Purpose: Stores metadata about vendors involved in marketing activities.

Key Columns:

• vendor_id (STRING): Unique identifier for the vendor.
• vendor_name (STRING): Name of the vendor.

company

Purpose: Stores metadata about companies.

Key Columns:

• company_id (STRING): Unique identifier for the company.
• company_domain (STRING): Domain of the company.
• company_name (STRING): Name of the company.
• sector_id (STRING): Identifier for the sector the company belongs to.
• region_id (STRING): Identifier for the region the company is in.
• revenue_range_id (STRING): Identifier for the revenue range of the company.

sector

Purpose: Stores information about industry sectors.

Key Columns:

• sector_id (STRING): Unique identifier for the sector.
• sector_name (STRING): Name of the sector.

region

Purpose: Stores information about geographical regions.

Key Columns:

• region_id (STRING): Unique identifier for the region.
• region_name (STRING): Name of the region.

audience

Purpose: Stores metadata about audiences.

Key Columns:

• audience_id (STRING): ID of the audience.
• audience_name (STRING): Name of the audience.
• audience_description (STRING): Description of the audience.
• is_deleted (STRING): Flag indicating if the record is deleted.

tag

Purpose: Stores information about tags used for categorizing data.

Key Columns:

• tag_id (STRING): Unique identifier for the tag.
• tag_type (STRING): Type of the tag.
• tag_code (STRING): Code representing the tag.
• tag_name (STRING): Name of the tag.
• tag_description (STRING): Description of the tag.
• is_deleted (STRING): Flag indicating if the record is deleted.
• is_enabled (STRING): Flag indicating if the tag is enabled.

ad_account

Purpose: Stores metadata about ad accounts.

Key Columns:

• ad_account_id (STRING): Unique identifier for the ad account.
• ad_account_name (STRING): Name of the ad account.
• is_deleted (STRING): Flag indicating if the record is deleted.

ad_campaign_group

Purpose: Stores metadata about ad campaign groups.

Key Columns:

• ad_campaign_group_id (STRING): Unique identifier for the ad campaign group.
• ad_campaign_group_name (STRING): Name of the ad campaign group.
• is_deleted (STRING): Flag indicating if the record is deleted.

ad_campaign

Purpose: Stores metadata about marketing campaigns.

Key Columns:

• ad_campaign_id (STRING): Unique identifier for the ad campaign.
• ad_campaign_name (STRING): Name of the ad campaign.
• is_deleted (STRING): Flag indicating if the record is deleted.

ad_group

Purpose: Stores metadata about ad groups.

Key Columns:

• ad_group_id (STRING): Unique identifier for the ad group.
• ad_group_name (STRING): Name of the ad group.
• is_deleted (STRING): Flag indicating if the record is deleted.

ad

Purpose: Stores metadata about ads.

Key Columns:

• ad_id (STRING): Unique identifier for the ad.
• ad_name (STRING): Name of the ad.
• is_deleted (STRING): Flag indicating if the record is deleted.

ad_detail

Purpose: Stores metadata about ad details.

Key Columns:

• ad_detail_id (STRING): Unique identifier for the ad detail.
• ad_detail_name (STRING): Name of the ad detail.
• is_deleted (STRING): Flag indicating if the record is deleted.

Usage Notes

• Each dimension table can be joined to the traffic fact table using the respective IDs.
• The is_deleted column in dimension tables allows filtering out inactive or deleted records.
• Data is shared securely with customers via Snowflake Secure Data Sharing.

Contact

For questions or support regarding this data model, please contact Channel99 Support at support@channel99.com.