-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_pin_promotion_report_data_projected" AS (
    -- Projection: Selecting 10 out of 11 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "date_",
        "pin_promotion_id",
        "ad_group_id",
        "campaign_id",
        "advertiser_id",
        "impression_1",
        "impression_2",
        "clickthrough_1",
        "clickthrough_2",
        "spend_in_micro_dollar"
    FROM "pinterest_pin_promotion_report_data"
),

"pinterest_pin_promotion_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- impression_1 -> primary_impressions
    -- impression_2 -> secondary_impressions
    -- clickthrough_1 -> primary_clickthroughs
    -- clickthrough_2 -> secondary_clickthroughs
    -- spend_in_micro_dollar -> spend_micro_dollars
    SELECT 
        "date_",
        "pin_promotion_id",
        "ad_group_id",
        "campaign_id",
        "advertiser_id",
        "impression_1" AS "primary_impressions",
        "impression_2" AS "secondary_impressions",
        "clickthrough_1" AS "primary_clickthroughs",
        "clickthrough_2" AS "secondary_clickthroughs",
        "spend_in_micro_dollar" AS "spend_micro_dollars"
    FROM "pinterest_pin_promotion_report_data_projected"
),

"pinterest_pin_promotion_report_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- ad_group_id: from INT to VARCHAR
    -- advertiser_id: from INT to VARCHAR
    -- campaign_id: from INT to VARCHAR
    -- date_: from VARCHAR to TIMESTAMP
    -- pin_promotion_id: from INT to VARCHAR
    SELECT
        "primary_impressions",
        "secondary_impressions",
        "primary_clickthroughs",
        "secondary_clickthroughs",
        "spend_micro_dollars",
        CAST("ad_group_id" AS VARCHAR) AS "ad_group_id",
        CAST("advertiser_id" AS VARCHAR) AS "advertiser_id",
        CAST("campaign_id" AS VARCHAR) AS "campaign_id",
        CAST("date_" AS TIMESTAMP) AS "date_",
        CAST("pin_promotion_id" AS VARCHAR) AS "pin_promotion_id"
    FROM "pinterest_pin_promotion_report_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_pin_promotion_report_data_projected_renamed_casted"

version: 2
models:
- name: stg_pinterest_pin_promotion_report_data
  description: The table is about Pinterest pin promotion performance data. It contains
    daily metrics for individual promoted pins. Each row includes the date, pin promotion
    ID, ad group ID, campaign ID, advertiser ID, impression counts, clickthrough counts,
    and spend amount. The data allows tracking and analysis of pin promotion effectiveness
    and costs over time.
  columns:
  - name: primary_impressions
    description: Number of primary impressions
    tests:
    - not_null
  - name: secondary_impressions
    description: Number of secondary impressions
    cocoon_meta:
      missing_acceptable: No secondary impressions occurred for that ad interaction
  - name: primary_clickthroughs
    description: Number of primary clickthroughs
    tests:
    - not_null
  - name: secondary_clickthroughs
    description: Number of secondary clickthroughs
    cocoon_meta:
      missing_acceptable: No secondary clicks occurred for that ad interaction
  - name: spend_micro_dollars
    description: Amount spent in micro dollars
    tests:
    - not_null
  - name: ad_group_id
    description: Unique identifier for the ad group
    tests:
    - not_null
  - name: advertiser_id
    description: Unique identifier for the advertiser
    tests:
    - not_null
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
  - name: date_
    description: Date of the recorded data
    tests:
    - not_null
  - name: pin_promotion_id
    description: Unique identifier for the promoted pin
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_ad_group_history_data_projected" AS (
    -- Projection: Selecting 11 out of 12 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "campaign_id",
        "created_time",
        "name",
        "status",
        "start_time",
        "end_time",
        "pacing_delivery_type",
        "placement_group",
        "summary_status",
        "ad_account_id"
    FROM "pinterest_ad_group_history_data"
),

"pinterest_ad_group_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> ad_group_id
    -- created_time -> creation_timestamp
    -- name -> ad_group_name
    -- status -> ad_group_status
    -- start_time -> start_timestamp
    -- end_time -> end_timestamp
    SELECT 
        "id" AS "ad_group_id",
        "campaign_id",
        "created_time" AS "creation_timestamp",
        "name" AS "ad_group_name",
        "status" AS "ad_group_status",
        "start_time" AS "start_timestamp",
        "end_time" AS "end_timestamp",
        "pacing_delivery_type",
        "placement_group",
        "summary_status",
        "ad_account_id"
    FROM "pinterest_ad_group_history_data_projected"
),

"pinterest_ad_group_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- ad_account_id: from INT to VARCHAR
    -- ad_group_id: from INT to VARCHAR
    -- campaign_id: from INT to VARCHAR
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- end_timestamp: from DECIMAL to TIMESTAMP
    -- start_timestamp: from VARCHAR to TIMESTAMP
    SELECT
        "ad_group_name",
        "ad_group_status",
        "pacing_delivery_type",
        "placement_group",
        "summary_status",
        CAST("ad_account_id" AS VARCHAR) AS "ad_account_id",
        CAST("ad_group_id" AS VARCHAR) AS "ad_group_id",
        CAST("campaign_id" AS VARCHAR) AS "campaign_id",
        strptime("creation_timestamp", '%Y-%m-%d %H:%M:%S.%f %z') AS "creation_timestamp",
        CAST("end_timestamp" AS TIMESTAMP) AS "end_timestamp",
        CAST(strptime(SUBSTRING("start_timestamp", 1, 23), '%Y-%m-%d %H:%M:%S.%f') AS TIMESTAMP) AS "start_timestamp"
    FROM "pinterest_ad_group_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_ad_group_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_pinterest_ad_group_history_data
  description: The table is about Pinterest ad group history data. It contains details
    of ad groups, including their ID, campaign ID, creation time, name, status, start
    and end times, pacing delivery type, placement group, summary status, and associated
    ad account ID. Each row represents a unique ad group with its specific attributes
    and settings.
  columns:
  - name: ad_group_name
    description: Name or identifier of the ad group
    tests:
    - not_null
  - name: ad_group_status
    description: Current status of the ad group
    tests:
    - not_null
    - accepted_values:
        values:
        - ACTIVE
        - PAUSED
        - REMOVED
        - PENDING
        - DELETED
  - name: pacing_delivery_type
    description: Type of pacing delivery for the ad group
    tests:
    - not_null
    - accepted_values:
        values:
        - STANDARD
        - ACCELERATED
        - EVEN
  - name: placement_group
    description: Group where the ad is placed
    tests:
    - not_null
    - accepted_values:
        values:
        - ALL
        - BROWSE
        - OTHER
        - SEARCH
        - HOME
        - FEED
        - PROFILE
        - VIDEO
        - STORIES
        - MARKETPLACE
        - EVENTS
        - GROUPS
        - MESSENGER
        - NEWS
        - APPS
        - GAMES
  - name: summary_status
    description: Summary of the ad group's overall status
    tests:
    - not_null
    - accepted_values:
        values:
        - RUNNING
        - PAUSED
        - REMOVED
        - ENABLED
        - DISABLED
        - PENDING
        - ENDED
        - ARCHIVED
  - name: ad_account_id
    description: Unique identifier for the ad account
    tests:
    - not_null
  - name: ad_group_id
    description: Unique identifier for the ad group
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is the unique identifier for the ad group. For this
        table, each row represents a unique ad group. Ad group IDs are typically designed
        to be unique across the entire system.
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
  - name: creation_timestamp
    description: Timestamp when the ad group was created
    tests:
    - not_null
  - name: end_timestamp
    description: Timestamp when the ad group ends
    cocoon_meta:
      missing_acceptable: Not applicable for currently active/running ad groups.
  - name: start_timestamp
    description: Timestamp when the ad group starts
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_keyword_report_data_projected" AS (
    -- Projection: Selecting 32 out of 33 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "ad_group_id",
        "advertiser_id",
        "campaign_id",
        "date_",
        "keyword_id",
        "pin_id",
        "pin_promotion_id",
        "ad_group_name",
        "ad_group_status",
        "campaign_daily_spend_cap",
        "campaign_lifetime_spend_cap",
        "campaign_name",
        "campaign_status",
        "clickthrough_1",
        "clickthrough_1_gross",
        "cpc_in_micro_dollar",
        "cpm_in_micro_dollar",
        "ctr",
        "ecpc_in_micro_dollar",
        "ecpm_in_micro_dollar",
        "ectr",
        "engagement_1",
        "impression_1",
        "impression_1_gross",
        "outbound_click_1",
        "paid_impression",
        "pin_promotion_name",
        "pin_promotion_status",
        "spend_in_micro_dollar",
        "targeting_type",
        "targeting_value",
        "total_engagement"
    FROM "pinterest_keyword_report_data"
),

"pinterest_keyword_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> performance_date
    -- clickthrough_1 -> clickthrough_count
    -- clickthrough_1_gross -> clickthrough_count_gross
    -- cpc_in_micro_dollar -> cost_per_click_micro
    -- cpm_in_micro_dollar -> cost_per_mille_micro
    -- ctr -> click_through_rate
    -- ecpc_in_micro_dollar -> effective_cost_per_click_micro
    -- ecpm_in_micro_dollar -> effective_cost_per_mille_micro
    -- ectr -> effective_click_through_rate
    -- engagement_1 -> engagement_count
    -- impression_1 -> impression_count
    -- impression_1_gross -> impression_count_gross
    -- outbound_click_1 -> outbound_click_count
    -- paid_impression -> paid_impression_count
    -- spend_in_micro_dollar -> spend_micro
    -- targeting_value -> keyword_category
    SELECT 
        "ad_group_id",
        "advertiser_id",
        "campaign_id",
        "date_" AS "performance_date",
        "keyword_id",
        "pin_id",
        "pin_promotion_id",
        "ad_group_name",
        "ad_group_status",
        "campaign_daily_spend_cap",
        "campaign_lifetime_spend_cap",
        "campaign_name",
        "campaign_status",
        "clickthrough_1" AS "clickthrough_count",
        "clickthrough_1_gross" AS "clickthrough_count_gross",
        "cpc_in_micro_dollar" AS "cost_per_click_micro",
        "cpm_in_micro_dollar" AS "cost_per_mille_micro",
        "ctr" AS "click_through_rate",
        "ecpc_in_micro_dollar" AS "effective_cost_per_click_micro",
        "ecpm_in_micro_dollar" AS "effective_cost_per_mille_micro",
        "ectr" AS "effective_click_through_rate",
        "engagement_1" AS "engagement_count",
        "impression_1" AS "impression_count",
        "impression_1_gross" AS "impression_count_gross",
        "outbound_click_1" AS "outbound_click_count",
        "paid_impression" AS "paid_impression_count",
        "pin_promotion_name",
        "pin_promotion_status",
        "spend_in_micro_dollar" AS "spend_micro",
        "targeting_type",
        "targeting_value" AS "keyword_category",
        "total_engagement"
    FROM "pinterest_keyword_report_data_projected"
),

"pinterest_keyword_report_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- ad_group_name: The problem is that the ad_group_name column contains a single unusual value that combines a date-time stamp with 'Ad group'. This is not a typical format for an ad group name, which usually consists of a descriptive name related to the ad campaign or target audience. The correct value should be a meaningful ad group name without the date-time prefix. 
    -- keyword_category: The problem is inconsistency in representing cat-related interests. We have 'cat lover', 'cat drawing', and 'cat', which are all related to cats but represented differently. The correct approach would be to standardize these under a single category. Since 'cat lover' is the most frequent among these, we'll use it as the standard representation. 
    SELECT
        "ad_group_id",
        "advertiser_id",
        "campaign_id",
        "performance_date",
        "keyword_id",
        "pin_id",
        "pin_promotion_id",
        CASE
            WHEN "ad_group_name" = '2022-06-07 14:30 | Ad group' THEN 'Ad group'
            ELSE "ad_group_name"
        END AS "ad_group_name",
        "ad_group_status",
        "campaign_daily_spend_cap",
        "campaign_lifetime_spend_cap",
        "campaign_name",
        "campaign_status",
        "clickthrough_count",
        "clickthrough_count_gross",
        "cost_per_click_micro",
        "cost_per_mille_micro",
        "click_through_rate",
        "effective_cost_per_click_micro",
        "effective_cost_per_mille_micro",
        "effective_click_through_rate",
        "engagement_count",
        "impression_count",
        "impression_count_gross",
        "outbound_click_count",
        "paid_impression_count",
        "pin_promotion_name",
        "pin_promotion_status",
        "spend_micro",
        "targeting_type",
        CASE
            WHEN "keyword_category" = 'cat drawing' THEN 'cat lover'
            WHEN "keyword_category" = 'cat' THEN 'cat lover'
            ELSE "keyword_category"
        END AS "keyword_category",
        "total_engagement"
    FROM "pinterest_keyword_report_data_projected_renamed"
),

"pinterest_keyword_report_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- ad_group_id: from INT to VARCHAR
    -- advertiser_id: from INT to VARCHAR
    -- campaign_daily_spend_cap: from INT to VARCHAR
    -- campaign_id: from INT to VARCHAR
    -- campaign_lifetime_spend_cap: from INT to VARCHAR
    -- clickthrough_count: from DECIMAL to INT
    -- clickthrough_count_gross: from DECIMAL to INT
    -- engagement_count: from DECIMAL to INT
    -- keyword_id: from INT to VARCHAR
    -- outbound_click_count: from DECIMAL to INT
    -- performance_date: from VARCHAR to TIMESTAMP
    -- pin_id: from INT to VARCHAR
    -- pin_promotion_id: from INT to VARCHAR
    -- total_engagement: from DECIMAL to VARCHAR
    SELECT
        "ad_group_name",
        "ad_group_status",
        "campaign_name",
        "campaign_status",
        "cost_per_click_micro",
        "cost_per_mille_micro",
        "click_through_rate",
        "effective_cost_per_click_micro",
        "effective_cost_per_mille_micro",
        "effective_click_through_rate",
        "impression_count",
        "impression_count_gross",
        "paid_impression_count",
        "pin_promotion_name",
        "pin_promotion_status",
        "spend_micro",
        "targeting_type",
        "keyword_category",
        CAST("ad_group_id" AS VARCHAR) AS "ad_group_id",
        CAST("advertiser_id" AS VARCHAR) AS "advertiser_id",
        CAST("campaign_daily_spend_cap" AS VARCHAR) AS "campaign_daily_spend_cap",
        CAST("campaign_id" AS VARCHAR) AS "campaign_id",
        CAST("campaign_lifetime_spend_cap" AS VARCHAR) AS "campaign_lifetime_spend_cap",
        CAST("clickthrough_count" AS INT) AS "clickthrough_count",
        CAST("clickthrough_count_gross" AS INT) AS "clickthrough_count_gross",
        CAST("engagement_count" AS INT) AS "engagement_count",
        CAST("keyword_id" AS VARCHAR) AS "keyword_id",
        CAST("outbound_click_count" AS INT) AS "outbound_click_count",
        CAST("performance_date" AS TIMESTAMP) AS "performance_date",
        CAST("pin_id" AS VARCHAR) AS "pin_id",
        CAST("pin_promotion_id" AS VARCHAR) AS "pin_promotion_id",
        CAST("total_engagement" AS VARCHAR) AS "total_engagement"
    FROM "pinterest_keyword_report_data_projected_renamed_cleaned"
),

"pinterest_keyword_report_data_projected_renamed_cleaned_casted_missing_handled" AS (
    -- Handling missing values: There are 9 columns with unacceptable missing values
    -- click_through_rate has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- clickthrough_count has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- clickthrough_count_gross has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- cost_per_click_micro has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- effective_click_through_rate has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- effective_cost_per_click_micro has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- engagement_count has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- outbound_click_count has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- total_engagement has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "ad_group_name",
        "ad_group_status",
        "campaign_name",
        "campaign_status",
        "cost_per_mille_micro",
        "effective_cost_per_mille_micro",
        "impression_count",
        "impression_count_gross",
        "paid_impression_count",
        "pin_promotion_name",
        "pin_promotion_status",
        "spend_micro",
        "targeting_type",
        "keyword_category",
        "ad_group_id",
        "advertiser_id",
        "campaign_daily_spend_cap",
        "campaign_id",
        "campaign_lifetime_spend_cap",
        "keyword_id",
        "performance_date",
        "pin_id",
        "pin_promotion_id"
    FROM "pinterest_keyword_report_data_projected_renamed_cleaned_casted"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_keyword_report_data_projected_renamed_cleaned_casted_missing_handled"

version: 2
models:
- name: stg_pinterest_keyword_report_data
  description: The table is about Pinterest keyword report data. It contains daily
    performance metrics for ad campaigns. Key entities are advertisers, campaigns,
    ad groups, keywords, and pin promotions. Metrics include impressions, spend, CPM,
    and CTR. The data shows how different keywords perform for specific ad campaigns
    and promotions on Pinterest.
  columns:
  - name: ad_group_name
    description: Name of the ad group
    tests:
    - not_null
  - name: ad_group_status
    description: Current status of the ad group
    tests:
    - not_null
    - accepted_values:
        values:
        - ENABLED
        - PAUSED
        - REMOVED
        - COMPLETED
        - PENDING
        - DRAFT
        - ARCHIVED
  - name: campaign_name
    description: Name of the campaign
    tests:
    - not_null
  - name: campaign_status
    description: Current status of the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - DRAFT
        - SCHEDULED
        - ACTIVE
        - PAUSED
        - COMPLETED
        - CANCELLED
        - ARCHIVED
  - name: cost_per_mille_micro
    description: Cost per thousand impressions in micro dollars
    tests:
    - not_null
  - name: effective_cost_per_mille_micro
    description: Effective cost per thousand impressions in micro dollars
    tests:
    - not_null
  - name: impression_count
    description: Number of impressions
    tests:
    - not_null
  - name: impression_count_gross
    description: Gross number of impressions
    tests:
    - not_null
  - name: paid_impression_count
    description: Number of paid impressions
    tests:
    - not_null
  - name: pin_promotion_name
    description: Name of the pin promotion
    tests:
    - not_null
  - name: pin_promotion_status
    description: Current status of the pin promotion
    tests:
    - not_null
    - accepted_values:
        values:
        - PENDING
        - APPROVED
        - REJECTED
        - SUSPENDED
        - COMPLETED
        - CANCELED
  - name: spend_micro
    description: Amount spent in micro dollars
    tests:
    - not_null
  - name: targeting_type
    description: Type of targeting used
    tests:
    - not_null
    - accepted_values:
        values:
        - KEYWORD
        - DEMOGRAPHIC
        - BEHAVIORAL
        - CONTEXTUAL
        - GEOGRAPHIC
        - DEVICE
        - TIME
        - RETARGETING
        - INTEREST
        - LOOKALIKE
        - CUSTOM AUDIENCE
        - PLACEMENT
  - name: keyword_category
    description: Keyword or category targeted by the ad campaign
    tests:
    - not_null
  - name: ad_group_id
    description: Unique identifier for the ad group
    tests:
    - not_null
  - name: advertiser_id
    description: Unique identifier for the advertiser
    tests:
    - not_null
  - name: campaign_daily_spend_cap
    description: Daily spending limit for the campaign
    tests:
    - not_null
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
  - name: campaign_lifetime_spend_cap
    description: Lifetime spending limit for the campaign
    tests:
    - not_null
  - name: keyword_id
    description: Unique identifier for the keyword
    tests:
    - not_null
  - name: performance_date
    description: Date of the performance data
    tests:
    - not_null
  - name: pin_id
    description: Unique identifier for the pin
    tests:
    - not_null
  - name: pin_promotion_id
    description: Unique identifier for the pin promotion
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_campaign_report_data_projected" AS (
    -- Projection: Selecting 24 out of 25 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "advertiser_id",
        "campaign_id",
        "date_",
        "campaign_daily_spend_cap",
        "campaign_lifetime_spend_cap",
        "campaign_name",
        "campaign_status",
        "clickthrough_1",
        "clickthrough_1_gross",
        "cpc_in_micro_dollar",
        "cpm_in_micro_dollar",
        "ctr",
        "ecpc_in_micro_dollar",
        "ecpm_in_micro_dollar",
        "ectr",
        "engagement_1",
        "impression_1",
        "impression_1_gross",
        "outbound_click_1",
        "paid_impression",
        "spend_in_micro_dollar",
        "total_engagement",
        "total_impression_frequency",
        "total_impression_user"
    FROM "pinterest_campaign_report_data"
),

"pinterest_campaign_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- campaign_daily_spend_cap -> daily_spend_cap_micro
    -- campaign_lifetime_spend_cap -> lifetime_spend_cap_micro
    -- clickthrough_1 -> clicks
    -- clickthrough_1_gross -> total_clicks
    -- cpc_in_micro_dollar -> cpc_micro
    -- cpm_in_micro_dollar -> cpm_micro
    -- ecpc_in_micro_dollar -> ecpc_micro
    -- ecpm_in_micro_dollar -> ecpm_micro
    -- engagement_1 -> engagements
    -- impression_1 -> impressions
    -- impression_1_gross -> total_impressions
    -- outbound_click_1 -> outbound_clicks
    -- paid_impression -> paid_impressions
    -- spend_in_micro_dollar -> spend_micro
    -- total_engagement -> total_engagements
    -- total_impression_frequency -> avg_impression_frequency
    -- total_impression_user -> unique_users_reached
    SELECT 
        "advertiser_id",
        "campaign_id",
        "date_",
        "campaign_daily_spend_cap" AS "daily_spend_cap_micro",
        "campaign_lifetime_spend_cap" AS "lifetime_spend_cap_micro",
        "campaign_name",
        "campaign_status",
        "clickthrough_1" AS "clicks",
        "clickthrough_1_gross" AS "total_clicks",
        "cpc_in_micro_dollar" AS "cpc_micro",
        "cpm_in_micro_dollar" AS "cpm_micro",
        "ctr",
        "ecpc_in_micro_dollar" AS "ecpc_micro",
        "ecpm_in_micro_dollar" AS "ecpm_micro",
        "ectr",
        "engagement_1" AS "engagements",
        "impression_1" AS "impressions",
        "impression_1_gross" AS "total_impressions",
        "outbound_click_1" AS "outbound_clicks",
        "paid_impression" AS "paid_impressions",
        "spend_in_micro_dollar" AS "spend_micro",
        "total_engagement" AS "total_engagements",
        "total_impression_frequency" AS "avg_impression_frequency",
        "total_impression_user" AS "unique_users_reached"
    FROM "pinterest_campaign_report_data_projected"
),

"pinterest_campaign_report_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- advertiser_id: from INT to VARCHAR
    -- campaign_id: from INT to VARCHAR
    -- clicks: from DECIMAL to INT
    -- date_: from VARCHAR to TIMESTAMP
    -- outbound_clicks: from DECIMAL to INT
    SELECT
        "daily_spend_cap_micro",
        "lifetime_spend_cap_micro",
        "campaign_name",
        "campaign_status",
        "total_clicks",
        "cpc_micro",
        "cpm_micro",
        "ctr",
        "ecpc_micro",
        "ecpm_micro",
        "ectr",
        "engagements",
        "impressions",
        "total_impressions",
        "paid_impressions",
        "spend_micro",
        "total_engagements",
        "avg_impression_frequency",
        "unique_users_reached",
        CAST("advertiser_id" AS VARCHAR) AS "advertiser_id",
        CAST("campaign_id" AS VARCHAR) AS "campaign_id",
        CAST("clicks" AS INT) AS "clicks",
        CAST("date_" AS TIMESTAMP) AS "date_",
        CAST("outbound_clicks" AS INT) AS "outbound_clicks"
    FROM "pinterest_campaign_report_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_campaign_report_data_projected_renamed_casted"

version: 2
models:
- name: stg_pinterest_campaign_report_data
  description: The table is about a Pinterest advertising campaign named "My Cat Is
    Purrfect". It contains daily performance metrics for this campaign. The data includes
    spend, impressions, clicks, engagement, and various calculated rates like CPM
    and CTR. The campaign has a lifetime spend cap of $25,000,000 and its status is
    "COMPLETED". The table tracks the campaign's performance across different dates
    in June 2022.
  columns:
  - name: daily_spend_cap_micro
    description: Daily spending limit for the campaign in micro-dollars
    tests:
    - not_null
  - name: lifetime_spend_cap_micro
    description: Total spending limit for the campaign in micro-dollars
    tests:
    - not_null
  - name: campaign_name
    description: Name of the advertising campaign
    tests:
    - not_null
  - name: campaign_status
    description: Current status of the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - DRAFT
        - SCHEDULED
        - ACTIVE
        - PAUSED
        - COMPLETED
        - CANCELLED
        - ARCHIVED
  - name: total_clicks
    description: Total number of clicks including non-billable clicks
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred in campaign
  - name: cpc_micro
    description: Cost per click in micro-dollars
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred in campaign
  - name: cpm_micro
    description: Cost per thousand impressions in micro-dollars
    tests:
    - not_null
  - name: ctr
    description: Click-through rate (clicks divided by impressions)
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred in campaign
  - name: ecpc_micro
    description: Effective cost per click in micro-dollars
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred in campaign
  - name: ecpm_micro
    description: Effective cost per thousand impressions in micro-dollars
    tests:
    - not_null
  - name: ectr
    description: Effective click-through rate
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred in campaign
  - name: engagements
    description: Number of engagements with the ad
    tests:
    - not_null
  - name: impressions
    description: Number of times the ad was displayed
    tests:
    - not_null
  - name: total_impressions
    description: Total number of impressions including non-billable impressions
    tests:
    - not_null
  - name: paid_impressions
    description: Number of paid impressions
    tests:
    - not_null
  - name: spend_micro
    description: Amount spent on the campaign in micro-dollars
    tests:
    - not_null
  - name: total_engagements
    description: Total number of engagements across all metrics
    tests:
    - not_null
  - name: avg_impression_frequency
    description: Average number of times users saw the ad
    tests:
    - not_null
  - name: unique_users_reached
    description: Number of unique users who saw the ad
    tests:
    - not_null
  - name: advertiser_id
    description: Unique identifier for the advertiser
    tests:
    - not_null
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
  - name: clicks
    description: Number of clicks on the ad
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred in campaign
  - name: date_
    description: Date of the performance metrics
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the date of the performance metrics. For
        this table, each row is for a daily performance of the campaign. date_ is
        unique across rows as there is only one entry per day for the campaign.
  - name: outbound_clicks
    description: Number of clicks leading outside Pinterest
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_ad_group_report_data_projected" AS (
    -- Projection: Selecting 27 out of 28 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "ad_group_id",
        "advertiser_id",
        "date_",
        "ad_group_name",
        "ad_group_status",
        "campaign_daily_spend_cap",
        "campaign_id",
        "campaign_lifetime_spend_cap",
        "campaign_name",
        "campaign_status",
        "clickthrough_1",
        "clickthrough_1_gross",
        "cpc_in_micro_dollar",
        "cpm_in_micro_dollar",
        "ctr",
        "ecpc_in_micro_dollar",
        "ecpm_in_micro_dollar",
        "ectr",
        "engagement_1",
        "impression_1",
        "impression_1_gross",
        "outbound_click_1",
        "paid_impression",
        "spend_in_micro_dollar",
        "total_engagement",
        "total_impression_frequency",
        "total_impression_user"
    FROM "pinterest_ad_group_report_data"
),

"pinterest_ad_group_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> report_date
    -- clickthrough_1 -> clickthrough_count
    -- clickthrough_1_gross -> clickthrough_count_gross
    -- cpc_in_micro_dollar -> cost_per_click_micro
    -- cpm_in_micro_dollar -> cost_per_thousand_impressions_micro
    -- ctr -> click_through_rate
    -- ecpc_in_micro_dollar -> effective_cost_per_click_micro
    -- ecpm_in_micro_dollar -> effective_cost_per_thousand_impressions_micro
    -- ectr -> effective_click_through_rate
    -- engagement_1 -> engagement_count
    -- impression_1 -> impression_count
    -- impression_1_gross -> impression_count_gross
    -- outbound_click_1 -> outbound_click_count
    -- paid_impression -> paid_impression_count
    -- spend_in_micro_dollar -> spend_micro
    -- total_engagement -> total_engagement_count
    -- total_impression_frequency -> average_impression_frequency
    -- total_impression_user -> total_impression_users
    SELECT 
        "ad_group_id",
        "advertiser_id",
        "date_" AS "report_date",
        "ad_group_name",
        "ad_group_status",
        "campaign_daily_spend_cap",
        "campaign_id",
        "campaign_lifetime_spend_cap",
        "campaign_name",
        "campaign_status",
        "clickthrough_1" AS "clickthrough_count",
        "clickthrough_1_gross" AS "clickthrough_count_gross",
        "cpc_in_micro_dollar" AS "cost_per_click_micro",
        "cpm_in_micro_dollar" AS "cost_per_thousand_impressions_micro",
        "ctr" AS "click_through_rate",
        "ecpc_in_micro_dollar" AS "effective_cost_per_click_micro",
        "ecpm_in_micro_dollar" AS "effective_cost_per_thousand_impressions_micro",
        "ectr" AS "effective_click_through_rate",
        "engagement_1" AS "engagement_count",
        "impression_1" AS "impression_count",
        "impression_1_gross" AS "impression_count_gross",
        "outbound_click_1" AS "outbound_click_count",
        "paid_impression" AS "paid_impression_count",
        "spend_in_micro_dollar" AS "spend_micro",
        "total_engagement" AS "total_engagement_count",
        "total_impression_frequency" AS "average_impression_frequency",
        "total_impression_user" AS "total_impression_users"
    FROM "pinterest_ad_group_report_data_projected"
),

"pinterest_ad_group_report_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- ad_group_name: The problem is that the ad_group_name column contains a single unusual value that combines a date-time stamp with 'Ad group'. This is not a typical format for an ad group name, which usually consists of a descriptive name related to the ad campaign or target audience. The correct value should be a meaningful ad group name without the date-time prefix. 
    SELECT
        "ad_group_id",
        "advertiser_id",
        "report_date",
        CASE
            WHEN "ad_group_name" = '2022-06-07 14:30 | Ad group' THEN 'Ad group'
            ELSE "ad_group_name"
        END AS "ad_group_name",
        "ad_group_status",
        "campaign_daily_spend_cap",
        "campaign_id",
        "campaign_lifetime_spend_cap",
        "campaign_name",
        "campaign_status",
        "clickthrough_count",
        "clickthrough_count_gross",
        "cost_per_click_micro",
        "cost_per_thousand_impressions_micro",
        "click_through_rate",
        "effective_cost_per_click_micro",
        "effective_cost_per_thousand_impressions_micro",
        "effective_click_through_rate",
        "engagement_count",
        "impression_count",
        "impression_count_gross",
        "outbound_click_count",
        "paid_impression_count",
        "spend_micro",
        "total_engagement_count",
        "average_impression_frequency",
        "total_impression_users"
    FROM "pinterest_ad_group_report_data_projected_renamed"
),

"pinterest_ad_group_report_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- ad_group_id: from INT to VARCHAR
    -- advertiser_id: from INT to VARCHAR
    -- campaign_id: from INT to VARCHAR
    -- clickthrough_count: from DECIMAL to INT
    -- clickthrough_count_gross: from DECIMAL to INT
    -- outbound_click_count: from DECIMAL to INT
    -- report_date: from VARCHAR to TIMESTAMP
    SELECT
        "ad_group_name",
        "ad_group_status",
        "campaign_daily_spend_cap",
        "campaign_lifetime_spend_cap",
        "campaign_name",
        "campaign_status",
        "cost_per_click_micro",
        "cost_per_thousand_impressions_micro",
        "click_through_rate",
        "effective_cost_per_click_micro",
        "effective_cost_per_thousand_impressions_micro",
        "effective_click_through_rate",
        "engagement_count",
        "impression_count",
        "impression_count_gross",
        "paid_impression_count",
        "spend_micro",
        "total_engagement_count",
        "average_impression_frequency",
        "total_impression_users",
        CAST("ad_group_id" AS VARCHAR) AS "ad_group_id",
        CAST("advertiser_id" AS VARCHAR) AS "advertiser_id",
        CAST("campaign_id" AS VARCHAR) AS "campaign_id",
        CAST("clickthrough_count" AS INT) AS "clickthrough_count",
        CAST("clickthrough_count_gross" AS INT) AS "clickthrough_count_gross",
        CAST("outbound_click_count" AS INT) AS "outbound_click_count",
        CAST("report_date" AS TIMESTAMP) AS "report_date"
    FROM "pinterest_ad_group_report_data_projected_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_ad_group_report_data_projected_renamed_cleaned_casted"

version: 2
models:
- name: stg_pinterest_ad_group_report_data
  description: The table is about Pinterest ad group report data. It contains daily
    metrics for ad groups within campaigns. Details include ad group and campaign
    identifiers, names, statuses, spend caps, and performance metrics like impressions,
    clicks, engagement, and spend. Each row represents one day's data for a specific
    ad group.
  columns:
  - name: ad_group_name
    description: Name of the ad group
    tests:
    - not_null
  - name: ad_group_status
    description: Current status of the ad group
    tests:
    - not_null
    - accepted_values:
        values:
        - ENABLED
        - PAUSED
        - REMOVED
        - COMPLETED
        - PENDING
        - DRAFT
        - ARCHIVED
  - name: campaign_daily_spend_cap
    description: Daily spending limit for the campaign
    tests:
    - not_null
  - name: campaign_lifetime_spend_cap
    description: Total spending limit for the campaign
    tests:
    - not_null
  - name: campaign_name
    description: Name of the campaign
    tests:
    - not_null
  - name: campaign_status
    description: Current status of the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - DRAFT
        - SCHEDULED
        - ACTIVE
        - PAUSED
        - COMPLETED
        - CANCELLED
        - ARCHIVED
  - name: cost_per_click_micro
    description: Cost per click in micro dollars
    cocoon_meta:
      missing_acceptable: Not applicable when there are zero clicks.
  - name: cost_per_thousand_impressions_micro
    description: Cost per thousand impressions in micro dollars
    tests:
    - not_null
  - name: click_through_rate
    description: Click-through rate
    cocoon_meta:
      missing_acceptable: Not applicable when there are zero clicks.
  - name: effective_cost_per_click_micro
    description: Effective cost per click in micro dollars
    cocoon_meta:
      missing_acceptable: Not applicable when there are zero clicks.
  - name: effective_cost_per_thousand_impressions_micro
    description: Effective cost per thousand impressions in micro dollars
    tests:
    - not_null
  - name: effective_click_through_rate
    description: Effective click-through rate
    cocoon_meta:
      missing_acceptable: Not applicable when there are zero clicks.
  - name: engagement_count
    description: Number of engagements
    cocoon_meta:
      missing_acceptable: Not applicable when there are zero engagements.
  - name: impression_count
    description: Number of impressions
    tests:
    - not_null
  - name: impression_count_gross
    description: Gross number of impressions
    tests:
    - not_null
  - name: paid_impression_count
    description: Number of paid impressions
    tests:
    - not_null
  - name: spend_micro
    description: Amount spent in micro dollars
    tests:
    - not_null
  - name: total_engagement_count
    description: Total number of engagements
    cocoon_meta:
      missing_acceptable: Not applicable when there are zero engagements.
  - name: average_impression_frequency
    description: Average impression frequency per user
    tests:
    - not_null
  - name: total_impression_users
    description: Total number of users who saw impressions
    tests:
    - not_null
  - name: ad_group_id
    description: Unique identifier for the ad group
    tests:
    - not_null
  - name: advertiser_id
    description: Unique identifier for the advertiser
    tests:
    - not_null
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
  - name: clickthrough_count
    description: Number of clickthroughs
    cocoon_meta:
      missing_acceptable: Not applicable when there are zero clicks.
  - name: clickthrough_count_gross
    description: Gross number of clickthroughs
    cocoon_meta:
      missing_acceptable: Not applicable when there are zero clicks.
  - name: outbound_click_count
    description: Number of outbound clicks
    tests:
    - not_null
  - name: report_date
    description: Date of the reported metrics
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_advertiser_history_data_projected" AS (
    -- Projection: Selecting 13 out of 14 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "updated_time",
        "billing_profile_status",
        "billing_type",
        "country",
        "created_time",
        "currency",
        "merchant_id",
        "name",
        "owner_user_id",
        "status",
        "owner_username",
        "permissions"
    FROM "pinterest_advertiser_history_data"
),

"pinterest_advertiser_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> advertiser_id
    -- updated_time -> last_updated_date
    -- billing_profile_status -> billing_status
    -- billing_type -> payment_method
    -- created_time -> account_creation_date
    -- currency -> account_currency
    -- name -> account_name
    -- owner_user_id -> owner_id
    -- status -> account_status
    -- permissions -> account_permissions
    SELECT 
        "id" AS "advertiser_id",
        "updated_time" AS "last_updated_date",
        "billing_profile_status" AS "billing_status",
        "billing_type" AS "payment_method",
        "country",
        "created_time" AS "account_creation_date",
        "currency" AS "account_currency",
        "merchant_id",
        "name" AS "account_name",
        "owner_user_id" AS "owner_id",
        "status" AS "account_status",
        "owner_username",
        "permissions" AS "account_permissions"
    FROM "pinterest_advertiser_history_data_projected"
),

"pinterest_advertiser_history_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- owner_username: The problem is that 'username' is a generic placeholder value and not a real username. In a real dataset, this column should contain actual usernames of the owners. Since we don't have any real usernames to map to, and a generic placeholder is not meaningful data, we should map this to an empty string. 
    -- account_permissions: The problem is that 'string' is being used as a placeholder value for the account_permissions column, which is not a meaningful representation of actual account permissions. This suggests that either the real permission data is missing or was not properly populated. In a real-world scenario, account permissions would typically be specific descriptors like 'read', 'write', 'admin', or combinations of these. Without knowing the intended permission structure for this system, we can't map 'string' to a correct value. The best approach is to map it to an empty string to indicate missing data. 
    SELECT
        "advertiser_id",
        "last_updated_date",
        "billing_status",
        "payment_method",
        "country",
        "account_creation_date",
        "account_currency",
        "merchant_id",
        "account_name",
        "owner_id",
        "account_status",
        CASE
            WHEN "owner_username" = 'username' THEN ''
            ELSE "owner_username"
        END AS "owner_username",
        CASE
            WHEN "account_permissions" = 'string' THEN ''
            ELSE "account_permissions"
        END AS "account_permissions"
    FROM "pinterest_advertiser_history_data_projected_renamed"
),

"pinterest_advertiser_history_data_projected_renamed_cleaned_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- owner_username: ['']
    -- account_permissions: ['']
    SELECT 
        CASE
            WHEN "owner_username" = '' THEN NULL
            ELSE "owner_username"
        END AS "owner_username",
        CASE
            WHEN "account_permissions" = '' THEN NULL
            ELSE "account_permissions"
        END AS "account_permissions",
        "owner_id",
        "payment_method",
        "advertiser_id",
        "billing_status",
        "account_status",
        "country",
        "account_creation_date",
        "account_currency",
        "merchant_id",
        "account_name",
        "last_updated_date"
    FROM "pinterest_advertiser_history_data_projected_renamed_cleaned"
),

"pinterest_advertiser_history_data_projected_renamed_cleaned_null_casted" AS (
    -- Column Type Casting: 
    -- account_creation_date: from VARCHAR to TIMESTAMP
    -- advertiser_id: from INT to VARCHAR
    -- last_updated_date: from VARCHAR to TIMESTAMP
    -- merchant_id: from DECIMAL to VARCHAR
    -- owner_id: from INT to VARCHAR
    SELECT
        "owner_username",
        "account_permissions",
        "payment_method",
        "billing_status",
        "account_status",
        "country",
        "account_currency",
        "account_name",
        CAST("account_creation_date" AS TIMESTAMP) AS "account_creation_date",
        CAST("advertiser_id" AS VARCHAR) AS "advertiser_id",
        CAST("last_updated_date" AS TIMESTAMP) AS "last_updated_date",
        CAST("merchant_id" AS VARCHAR) AS "merchant_id",
        CAST("owner_id" AS VARCHAR) AS "owner_id"
    FROM "pinterest_advertiser_history_data_projected_renamed_cleaned_null"
),

"pinterest_advertiser_history_data_projected_renamed_cleaned_null_casted_missing_handled" AS (
    -- Handling missing values: There are 2 columns with unacceptable missing values
    -- account_permissions has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- owner_username has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "payment_method",
        "billing_status",
        "account_status",
        "country",
        "account_currency",
        "account_name",
        "account_creation_date",
        "advertiser_id",
        "last_updated_date",
        "merchant_id",
        "owner_id"
    FROM "pinterest_advertiser_history_data_projected_renamed_cleaned_null_casted"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_advertiser_history_data_projected_renamed_cleaned_null_casted_missing_handled"

version: 2
models:
- name: stg_pinterest_advertiser_history_data
  description: The table is about Pinterest advertiser accounts. It contains details
    such as account ID, billing information, country, creation date, currency, merchant
    ID, account name, owner details, status, and permissions. This data represents
    the history and current state of advertiser accounts on the Pinterest platform.
  columns:
  - name: payment_method
    description: Method of billing for the advertiser account
    tests:
    - not_null
    - accepted_values:
        values:
        - CASH_MONEY
        - CREDIT_CARD
        - BANK_TRANSFER
        - PAYPAL
        - CHECK
        - DIRECT_DEBIT
        - INVOICE
        - PREPAID_BALANCE
        - WIRE_TRANSFER
        - ACH
        - CRYPTOCURRENCY
  - name: billing_status
    description: Indicates the validity of the billing profile
    tests:
    - not_null
    - accepted_values:
        values:
        - VALID
        - INVALID
        - PENDING
  - name: account_status
    description: Current operational status of the account
    tests:
    - not_null
    - accepted_values:
        values:
        - ACTIVE
        - INACTIVE
        - SUSPENDED
        - CLOSED
        - PENDING
        - LOCKED
        - RESTRICTED
        - DORMANT
  - name: country
    description: Country where the advertiser account is registered
    tests:
    - not_null
  - name: account_currency
    description: Currency used for transactions in the account
    tests:
    - not_null
  - name: account_name
    description: Name of the advertiser account
    tests:
    - not_null
  - name: account_creation_date
    description: Timestamp when the account was created
    tests:
    - not_null
  - name: advertiser_id
    description: Unique identifier for the advertiser account
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for the advertiser account. For
        this table, each row is for a unique advertiser account. Advertiser ID is
        designed to be unique across rows, ensuring each account has a distinct identifier.
  - name: last_updated_date
    description: Timestamp of the last account update
    tests:
    - not_null
  - name: merchant_id
    description: Identifier for the merchant, if applicable
    cocoon_meta:
      missing_acceptable: Not applicable for non-merchant advertisers.
  - name: owner_id
    description: Unique identifier of the account owner
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_advertiser_report_data_projected" AS (
    -- Projection: Selecting 19 out of 20 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "advertiser_id",
        "date_",
        "clickthrough_1",
        "clickthrough_1_gross",
        "cpc_in_micro_dollar",
        "cpm_in_micro_dollar",
        "ctr",
        "ecpc_in_micro_dollar",
        "ecpm_in_micro_dollar",
        "ectr",
        "engagement_1",
        "impression_1",
        "impression_1_gross",
        "outbound_click_1",
        "paid_impression",
        "spend_in_micro_dollar",
        "total_engagement",
        "total_impression_frequency",
        "total_impression_user"
    FROM "pinterest_advertiser_report_data"
),

"pinterest_advertiser_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> report_date
    -- clickthrough_1 -> click_count
    -- clickthrough_1_gross -> total_click_count
    -- cpc_in_micro_dollar -> cost_per_click_micro
    -- cpm_in_micro_dollar -> cost_per_mille_micro
    -- ctr -> click_through_rate
    -- ecpc_in_micro_dollar -> effective_cost_per_click_micro
    -- ecpm_in_micro_dollar -> effective_cost_per_mille_micro
    -- ectr -> effective_click_through_rate
    -- engagement_1 -> engagement_count
    -- impression_1 -> impression_count
    -- impression_1_gross -> total_impression_count
    -- outbound_click_1 -> outbound_click_count
    -- paid_impression -> paid_impression_count
    -- spend_in_micro_dollar -> total_spend_micro
    -- total_engagement -> total_engagement_count
    -- total_impression_frequency -> average_impression_frequency
    -- total_impression_user -> unique_user_reach
    SELECT 
        "advertiser_id",
        "date_" AS "report_date",
        "clickthrough_1" AS "click_count",
        "clickthrough_1_gross" AS "total_click_count",
        "cpc_in_micro_dollar" AS "cost_per_click_micro",
        "cpm_in_micro_dollar" AS "cost_per_mille_micro",
        "ctr" AS "click_through_rate",
        "ecpc_in_micro_dollar" AS "effective_cost_per_click_micro",
        "ecpm_in_micro_dollar" AS "effective_cost_per_mille_micro",
        "ectr" AS "effective_click_through_rate",
        "engagement_1" AS "engagement_count",
        "impression_1" AS "impression_count",
        "impression_1_gross" AS "total_impression_count",
        "outbound_click_1" AS "outbound_click_count",
        "paid_impression" AS "paid_impression_count",
        "spend_in_micro_dollar" AS "total_spend_micro",
        "total_engagement" AS "total_engagement_count",
        "total_impression_frequency" AS "average_impression_frequency",
        "total_impression_user" AS "unique_user_reach"
    FROM "pinterest_advertiser_report_data_projected"
),

"pinterest_advertiser_report_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- advertiser_id: from INT to VARCHAR
    -- click_count: from DECIMAL to INT
    -- outbound_click_count: from DECIMAL to INT
    -- report_date: from VARCHAR to TIMESTAMP
    -- total_click_count: from DECIMAL to INT
    SELECT
        "cost_per_click_micro",
        "cost_per_mille_micro",
        "click_through_rate",
        "effective_cost_per_click_micro",
        "effective_cost_per_mille_micro",
        "effective_click_through_rate",
        "engagement_count",
        "impression_count",
        "total_impression_count",
        "paid_impression_count",
        "total_spend_micro",
        "total_engagement_count",
        "average_impression_frequency",
        "unique_user_reach",
        CAST("advertiser_id" AS VARCHAR) AS "advertiser_id",
        CAST("click_count" AS INT) AS "click_count",
        CAST("outbound_click_count" AS INT) AS "outbound_click_count",
        CAST("report_date" AS TIMESTAMP) AS "report_date",
        CAST("total_click_count" AS INT) AS "total_click_count"
    FROM "pinterest_advertiser_report_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_advertiser_report_data_projected_renamed_casted"

version: 2
models:
- name: stg_pinterest_advertiser_report_data
  description: The table is about Pinterest advertiser report data. It contains daily
    metrics for a specific advertiser, identified by advertiser_id. The data includes
    impression counts, engagement rates, click-through rates, and cost metrics like
    CPC and CPM. It also shows spend amounts and frequency of impressions per user,
    providing a comprehensive view of the advertiser's campaign performance on Pinterest.
  columns:
  - name: cost_per_click_micro
    description: Cost per click in millionths of a dollar
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred.
  - name: cost_per_mille_micro
    description: Cost per thousand impressions in millionths of a dollar
    tests:
    - not_null
  - name: click_through_rate
    description: Click-through rate
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred.
  - name: effective_cost_per_click_micro
    description: Effective cost per click in millionths of a dollar
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred.
  - name: effective_cost_per_mille_micro
    description: Effective cost per thousand impressions in millionths of a dollar
    tests:
    - not_null
  - name: effective_click_through_rate
    description: Effective click-through rate
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred.
  - name: engagement_count
    description: Number of engagements with the ad
    tests:
    - not_null
  - name: impression_count
    description: Number of times the ad was displayed
    tests:
    - not_null
  - name: total_impression_count
    description: Total number of impressions including repeat views
    tests:
    - not_null
  - name: paid_impression_count
    description: Number of paid impressions
    tests:
    - not_null
  - name: total_spend_micro
    description: Total amount spent in millionths of a dollar
    tests:
    - not_null
  - name: total_engagement_count
    description: Total number of engagements
    tests:
    - not_null
  - name: average_impression_frequency
    description: Average number of times users saw the ad
    tests:
    - not_null
  - name: unique_user_reach
    description: Number of unique users who saw the ad
    tests:
    - not_null
  - name: advertiser_id
    description: Unique identifier for the advertiser
    tests:
    - not_null
  - name: click_count
    description: Number of clicks on the ad
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred.
  - name: outbound_click_count
    description: Number of clicks leading outside Pinterest
    tests:
    - not_null
  - name: report_date
    description: Date of the reported metrics
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the date of the reported metrics. For this
        table, each row is a daily report for a specific advertiser. Assuming there's
        only one report per day per advertiser, this column combined with the advertiser_id
        could form a composite candidate key.
  - name: total_click_count
    description: Total number of clicks including repeat clicks
    cocoon_meta:
      missing_acceptable: Not applicable when no clicks occurred.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_campaign_history_data_projected" AS (
    -- Projection: Selecting 9 out of 10 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "created_time",
        "name",
        "status",
        "advertiser_id",
        "default_ad_group_budget_in_micro_currency",
        "is_automated_campaign",
        "is_campaign_budget_optimization",
        "is_flexible_daily_budgets"
    FROM "pinterest_campaign_history_data"
),

"pinterest_campaign_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> campaign_id
    -- created_time -> campaign_creation_time
    -- name -> campaign_name
    -- status -> campaign_status
    -- default_ad_group_budget_in_micro_currency -> default_ad_group_budget_micro
    -- is_automated_campaign -> is_automated
    -- is_campaign_budget_optimization -> has_budget_optimization
    -- is_flexible_daily_budgets -> has_flexible_daily_budgets
    SELECT 
        "id" AS "campaign_id",
        "created_time" AS "campaign_creation_time",
        "name" AS "campaign_name",
        "status" AS "campaign_status",
        "advertiser_id",
        "default_ad_group_budget_in_micro_currency" AS "default_ad_group_budget_micro",
        "is_automated_campaign" AS "is_automated",
        "is_campaign_budget_optimization" AS "has_budget_optimization",
        "is_flexible_daily_budgets" AS "has_flexible_daily_budgets"
    FROM "pinterest_campaign_history_data_projected"
),

"pinterest_campaign_history_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- campaign_name: The problem is that the campaign_name column contains a single hexadecimal string value ('19e757f946601de26307d8182635b716') which appears to be a hash or unique identifier rather than a descriptive campaign name. This is unusual for a campaign name column, which typically contains human-readable text describing the campaign. The correct value should be a meaningful campaign name, but since we don't have that information, we'll map it to an empty string to indicate missing data. 
    SELECT
        "campaign_id",
        "campaign_creation_time",
        CASE
            WHEN "campaign_name" = '19e757f946601de26307d8182635b716' THEN ''
            ELSE "campaign_name"
        END AS "campaign_name",
        "campaign_status",
        "advertiser_id",
        "default_ad_group_budget_micro",
        "is_automated",
        "has_budget_optimization",
        "has_flexible_daily_budgets"
    FROM "pinterest_campaign_history_data_projected_renamed"
),

"pinterest_campaign_history_data_projected_renamed_cleaned_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- campaign_name: ['']
    SELECT 
        CASE
            WHEN "campaign_name" = '' THEN NULL
            ELSE "campaign_name"
        END AS "campaign_name",
        "campaign_id",
        "is_automated",
        "has_flexible_daily_budgets",
        "campaign_status",
        "default_ad_group_budget_micro",
        "advertiser_id",
        "has_budget_optimization",
        "campaign_creation_time"
    FROM "pinterest_campaign_history_data_projected_renamed_cleaned"
),

"pinterest_campaign_history_data_projected_renamed_cleaned_null_casted" AS (
    -- Column Type Casting: 
    -- advertiser_id: from INT to VARCHAR
    -- campaign_creation_time: from VARCHAR to TIMESTAMP
    -- campaign_id: from INT to VARCHAR
    SELECT
        "campaign_name",
        "is_automated",
        "has_flexible_daily_budgets",
        "campaign_status",
        "default_ad_group_budget_micro",
        "has_budget_optimization",
        CAST("advertiser_id" AS VARCHAR) AS "advertiser_id",
        strptime("campaign_creation_time", '%Y-%m-%d %H:%M:%S.%f %z') AS "campaign_creation_time",
        CAST("campaign_id" AS VARCHAR) AS "campaign_id"
    FROM "pinterest_campaign_history_data_projected_renamed_cleaned_null"
),

"pinterest_campaign_history_data_projected_renamed_cleaned_null_casted_missing_handled" AS (
    -- Handling missing values: There are 1 columns with unacceptable missing values
    -- campaign_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "is_automated",
        "has_flexible_daily_budgets",
        "campaign_status",
        "default_ad_group_budget_micro",
        "has_budget_optimization",
        "advertiser_id",
        "campaign_creation_time",
        "campaign_id"
    FROM "pinterest_campaign_history_data_projected_renamed_cleaned_null_casted"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_campaign_history_data_projected_renamed_cleaned_null_casted_missing_handled"

version: 2
models:
- name: stg_pinterest_campaign_history_data
  description: The table is about Pinterest advertising campaigns. It includes details
    such as campaign ID, creation time, name, status, advertiser ID, budget information,
    and campaign settings. Each row represents a unique campaign with its associated
    attributes. The table tracks campaign characteristics like automated status, budget
    optimization, and flexible daily budgets.
  columns:
  - name: is_automated
    description: Indicates if the campaign is automated
    tests:
    - not_null
  - name: has_flexible_daily_budgets
    description: Indicates if flexible daily budgets are enabled
    tests:
    - not_null
  - name: campaign_status
    description: Current status of the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - ACTIVE
        - PAUSED
        - COMPLETED
        - SCHEDULED
        - DRAFT
        - CANCELLED
        - ARCHIVED
  - name: default_ad_group_budget_micro
    description: Default budget for ad groups in micro currency
    tests:
    - not_null
  - name: has_budget_optimization
    description: Indicates if budget optimization is enabled
    tests:
    - not_null
  - name: advertiser_id
    description: Unique identifier for the advertiser
    tests:
    - not_null
  - name: campaign_creation_time
    description: Timestamp when the campaign was created
    tests:
    - not_null
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the unique identifier for the campaign. For
        this table, each row represents a unique campaign. campaign_id is unique across
        rows as it is designed to uniquely identify each campaign.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"pinterest_keyword_history_data_projected" AS (
    -- Projection: Selecting 10 out of 11 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "_fivetran_id",
        "ad_group_id",
        "advertiser_id",
        "archived",
        "bid",
        "campaign_id",
        "id",
        "match_type",
        "parent_type",
        "value_"
    FROM "pinterest_keyword_history_data"
),

"pinterest_keyword_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- _fivetran_id -> fivetran_sync_id
    -- archived -> is_archived
    -- bid -> bid_amount
    -- id -> keyword_id
    -- value_ -> keyword_text
    SELECT 
        "_fivetran_id" AS "fivetran_sync_id",
        "ad_group_id",
        "advertiser_id",
        "archived" AS "is_archived",
        "bid" AS "bid_amount",
        "campaign_id",
        "id" AS "keyword_id",
        "match_type",
        "parent_type",
        "value_" AS "keyword_text"
    FROM "pinterest_keyword_history_data_projected"
),

"pinterest_keyword_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- ad_group_id: from INT to VARCHAR
    -- advertiser_id: from DECIMAL to VARCHAR
    -- bid_amount: from DECIMAL to VARCHAR
    -- campaign_id: from DECIMAL to VARCHAR
    -- keyword_id: from INT to VARCHAR
    SELECT
        "fivetran_sync_id",
        "is_archived",
        "match_type",
        "parent_type",
        "keyword_text",
        CAST("ad_group_id" AS VARCHAR) AS "ad_group_id",
        CAST("advertiser_id" AS VARCHAR) AS "advertiser_id",
        CAST("bid_amount" AS VARCHAR) AS "bid_amount",
        CAST("campaign_id" AS VARCHAR) AS "campaign_id",
        CAST("keyword_id" AS VARCHAR) AS "keyword_id"
    FROM "pinterest_keyword_history_data_projected_renamed"
),

"pinterest_keyword_history_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 3 columns with unacceptable missing values
    -- advertiser_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- bid_amount has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- campaign_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "fivetran_sync_id",
        "is_archived",
        "match_type",
        "parent_type",
        "keyword_text",
        "ad_group_id",
        "keyword_id"
    FROM "pinterest_keyword_history_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "pinterest_keyword_history_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_pinterest_keyword_history_data
  description: The table is about Pinterest keyword history data. It contains details
    of keywords used in ad campaigns, including the ad group ID, campaign ID, keyword
    value, match type, and whether it's archived. Each keyword is identified by a
    unique ID and associated with a specific ad group. The data appears to be used
    for tracking and managing keywords in Pinterest advertising campaigns.
  columns:
  - name: fivetran_sync_id
    description: Unique identifier for Fivetran data sync
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for Fivetran data sync. For this
        table, each row represents a keyword in an ad campaign. The fivetran_sync_id
        appears to be unique across rows in the given sample.
  - name: is_archived
    description: Indicates if the keyword is archived
    tests:
    - not_null
  - name: match_type
    description: Type of keyword match
    tests:
    - not_null
    - accepted_values:
        values:
        - BROAD
        - PHRASE
        - EXACT
        - BROAD_MATCH_MODIFIER
  - name: parent_type
    description: Type of parent entity
    tests:
    - not_null
    - accepted_values:
        values:
        - AD_GROUP
        - CAMPAIGN
        - ACCOUNT
        - CUSTOMER
        - MANAGER_ACCOUNT
  - name: keyword_text
    description: The actual keyword text
    tests:
    - not_null
  - name: ad_group_id
    description: Identifier for the ad group
    tests:
    - not_null
  - name: keyword_id
    description: Unique identifier for the keyword
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for each keyword. For this table,
        each row represents a unique keyword, and the keyword_id is likely to be unique
        across all rows.

-- Slowly Changing Dimension: Dimension keys are "advertiser_id"
-- Effective date columns are "last_updated_date"
-- We will create Type 1 SCD (latest snapshot)
SELECT 
    "payment_method",
    "billing_status",
    "account_status",
    "country",
    "account_currency",
    "account_name",
    "account_creation_date",
    "advertiser_id",
    "merchant_id",
    "owner_id"
FROM (
     SELECT 
            "payment_method",
            "billing_status",
            "account_status",
            "country",
            "account_currency",
            "account_name",
            "account_creation_date",
            "advertiser_id",
            "merchant_id",
            "owner_id",
            ROW_NUMBER() OVER (
                PARTITION BY "advertiser_id" 
                ORDER BY "last_updated_date" 
            DESC) AS "cocoon_rn"
    FROM "stg_pinterest_advertiser_history_data"
) ranked
WHERE "cocoon_rn" = 1

version: 2
models:
- name: snapshot_pinterest_advertiser_history_data
  description: The table is about current Pinterest advertiser accounts. It tracks
    the most recent version of each advertiser's account details. It includes information
    such as payment method, billing status, account status, country, currency, account
    name, creation date, advertiser ID, merchant ID, and owner ID. This snapshot represents
    the latest state of active advertiser accounts on the Pinterest platform.
  columns:
  - name: payment_method
    description: Method of billing for the advertiser account
    tests:
    - not_null
    - accepted_values:
        values:
        - CASH_MONEY
        - CREDIT_CARD
        - BANK_TRANSFER
        - PAYPAL
        - CHECK
        - DIRECT_DEBIT
        - INVOICE
        - PREPAID_BALANCE
        - WIRE_TRANSFER
        - ACH
        - CRYPTOCURRENCY
  - name: billing_status
    description: Indicates the validity of the billing profile
    tests:
    - not_null
    - accepted_values:
        values:
        - VALID
        - INVALID
        - PENDING
  - name: account_status
    description: Current operational status of the account
    tests:
    - not_null
    - accepted_values:
        values:
        - ACTIVE
        - INACTIVE
        - SUSPENDED
        - CLOSED
        - PENDING
        - LOCKED
        - RESTRICTED
        - DORMANT
  - name: country
    description: Country where the advertiser account is registered
    tests:
    - not_null
  - name: account_currency
    description: Currency used for transactions in the account
    tests:
    - not_null
  - name: account_name
    description: Name of the advertiser account
    tests:
    - not_null
  - name: account_creation_date
    description: Timestamp when the account was created
    tests:
    - not_null
  - name: advertiser_id
    description: Unique identifier for the advertiser account
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: Unique dimension key, derived from the slowly changing dimension
  - name: merchant_id
    description: Identifier for the merchant, if applicable
    cocoon_meta:
      missing_acceptable: Not applicable for non-merchant advertisers.
  - name: owner_id
    description: Unique identifier of the account owner
    tests:
    - not_null
cocoon_meta:
  scd_base_table: stg_pinterest_advertiser_history_data