Skip to main content

Channel99 Data Share (Snowflake)

Mark Yatman
Updated

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  using listing or direct share.  Channel99 snowflake region is held within the East 1 region using AWS (region chart) . If you are using East 1 today we will direct share within region or we will need to use a listing for cross region 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 Tables

Visit

Purpose: Stores visit event-level data representing marketing activities and interactions at the visit level which can be referenced by unique visit even Id and can be joined to the pageview table. 

Key Columns:

  • visit_event_id (STRING): Unique identifier for the visit event.
  • 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.
  • current_page (STRING): complete page URL for the first pageview within the visit. Sometimes known as landing page. 
  • c99_user_cookie_id (STRING): alphanumeric user cookie id placed by the Channel99 Tag to each website user. 

Pageview

Purpose: Stores pageview event-level data recorded by the channel99 tag. 

Key Columns:

  • pv_event_id (STRING): Unique identifier for the pageview vent.
  • visit_event_id (STRING): Unique identifier for the visit event. This is commonly used to join pageview and visit table data. 
  • 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.
  • 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.
  • current_page (STRING): complete page URL for every page hit.
  • c99_user_cookie_id (STRING): alphanumeric user cookie id placed by the Channel99 Tag to each website user. 

Impression

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

Key Columns:

  • impression_event_id (STRING): Unique identifier for the event.
  • 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.
  • has_click (STRING): Boolean value reporting back from Google Ad Manager on the ad impression level. This is only available for Google Ad Manager integrated accounts and not Google Ads accounts. 

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.

 

SLATED FOR SUNSETTING : Fact Table

traffic - This table will be sunset but is supported for currently integrated accounts. All accounts using the snowflake share moving forward will be using the Pageview, Visit and Impression tables which provide more data granularity. 

Purpose: Stores event-level data representing marketing activities and interactions including impressions and visits but not pageview data. 

Key Columns:

  • event_id (STRING): Unique identifier for the event.
  • event_type (STRING): Type of event (e.g., impression, click, pageview).
  • 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.

Contact

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

Related articles

Comments

0 comments

Article is closed for comments.