-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:36:19.223629+00:00
WITH 
"product_ad_history_data_projected" AS (
    -- Projection: Selecting 9 out of 10 columns
    -- Columns projected out: ['id', 'last_updated_date', '_fivetran_synced', 'ad_group_id', 'asin', 'campaign_id', 'creation_date', 'serving_status', 'sku', 'state']
    SELECT 
        "id",
        "last_updated_date",
        "ad_group_id",
        "asin",
        "campaign_id",
        "creation_date",
        "serving_status",
        "sku",
        "state"
    FROM "memory"."main"."product_ad_history_data"
),

"product_ad_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> ad_id
    -- asin -> product_asin
    -- serving_status -> ad_serving_status
    -- sku -> product_sku
    -- state -> ad_state
    SELECT 
        "id" AS "ad_id",
        "last_updated_date",
        "ad_group_id",
        "asin" AS "product_asin",
        "campaign_id",
        "creation_date",
        "serving_status" AS "ad_serving_status",
        "sku" AS "product_sku",
        "state" AS "ad_state"
    FROM "product_ad_history_data_projected"
),

"product_ad_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- creation_date: from VARCHAR to TIMESTAMP
    -- last_updated_date: from VARCHAR to TIMESTAMP
    -- product_sku: from DECIMAL to VARCHAR
    SELECT
        "ad_id",
        "ad_group_id",
        "product_asin",
        "campaign_id",
        "ad_serving_status",
        "ad_state",
        CAST("creation_date" AS TIMESTAMP) 
        AS "creation_date",
        CAST("last_updated_date" AS TIMESTAMP) 
        AS "last_updated_date",
        CAST("product_sku" AS VARCHAR) 
        AS "product_sku"
    FROM "product_ad_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "product_ad_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_product_ad_history_data
  description: The table is about product ad history. It tracks changes in ad status
    over time for specific products. Each row represents a state change for an ad,
    identified by a unique ID. The table includes details like ad group ID, ASIN,
    campaign ID, creation date, serving status, and state. The 'last_updated_date'
    column shows when each change occurred, allowing for a chronological view of ad
    status changes.
  columns:
  - name: ad_id
    description: Unique identifier for the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: ad_group_id
    description: Identifier for the group the ad belongs to
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: product_asin
    description: Amazon Standard Identification Number for the product
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: campaign_id
    description: Identifier for the campaign the ad is part of
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: ad_serving_status
    description: Current status of the ad's serving
    tests:
    - not_null
    - accepted_values:
        values:
        - AD_STATUS_LIVE
        - CAMPAIGN_PAUSED
        - AD_STATUS_PAUSED
    cocoon_meta:
      future_accepted_values:
      - AD_STATUS_PENDING_REVIEW
      - AD_STATUS_REJECTED
      - AD_STATUS_ARCHIVED
      - AD_STATUS_DELETED
      - ACCOUNT_PAUSED
      - AD_GROUP_PAUSED
      data_type:
        current_data_type: VARCHAR
  - name: ad_state
    description: Overall state of the ad (enabled or disabled)
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
        - disabled
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: creation_date
    description: Timestamp when the ad was initially created
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: last_updated_date
    description: Timestamp of the most recent update to the ad
    tests:
    - not_null
    cocoon_meta:
      unique_reason: This column represents the timestamp of the most recent update
        to the ad. For this table, each row represents a state change for an ad at
        a specific point in time. The last_updated_date is unique across rows as it
        captures the exact moment of each state change, which is unlikely to be exactly
        the same for different updates.
      data_type:
        current_data_type: TIMESTAMP
  - name: product_sku
    description: Stock Keeping Unit, appears to be unused
    cocoon_meta:
      missing_reason: Not applicable when product is identified by ASIN instead.
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:29:36.726415+00:00
WITH 
"ad_group_history_data_projected" AS (
    -- Projection: Selecting 8 out of 9 columns
    -- Columns projected out: ['id', 'last_updated_date', '_fivetran_synced', 'campaign_id', 'creation_date', 'default_bid', 'name', 'serving_status', 'state']
    SELECT 
        "id",
        "last_updated_date",
        "campaign_id",
        "creation_date",
        "default_bid",
        "name",
        "serving_status",
        "state"
    FROM "memory"."main"."ad_group_history_data"
),

"ad_group_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> ad_group_id
    -- name -> ad_group_name
    -- state -> ad_group_state
    SELECT 
        "id" AS "ad_group_id",
        "last_updated_date",
        "campaign_id",
        "creation_date",
        "default_bid",
        "name" AS "ad_group_name",
        "serving_status",
        "state" AS "ad_group_state"
    FROM "ad_group_history_data_projected"
),

"ad_group_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- creation_date: from VARCHAR to TIMESTAMP
    -- last_updated_date: from VARCHAR to TIMESTAMP
    SELECT
        "ad_group_id",
        "campaign_id",
        "default_bid",
        "ad_group_name",
        "serving_status",
        "ad_group_state",
        CAST("creation_date" AS TIMESTAMP) 
        AS "creation_date",
        CAST("last_updated_date" AS TIMESTAMP) 
        AS "last_updated_date"
    FROM "ad_group_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "ad_group_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_ad_group_history_data
  description: The table is about ad group history data. It tracks changes to a single
    ad group over time. Each row represents a snapshot of the ad group's state at
    a specific date. The table includes details such as the ad group's ID, campaign
    ID, creation date, default bid, name, serving status, and state. The last_updated_date
    column shows when each change occurred, allowing for a chronological view of the
    ad group's modifications.
  columns:
  - name: ad_group_id
    description: Unique identifier for the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: campaign_id
    description: Identifier of the campaign the ad group belongs to
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: default_bid
    description: Default bid amount for the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: ad_group_name
    description: Name of the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: serving_status
    description: Current serving status of the ad group
    tests:
    - not_null
    - accepted_values:
        values:
        - AD_STATUS_LIVE
        - CAMPAIGN_PAUSED
    cocoon_meta:
      future_accepted_values:
      - AD_GROUP_PAUSED
      - AD_STATUS_PAUSED
      - REMOVED
      - PENDING_REVIEW
      - DISAPPROVED
      - ENDED
      - SUSPENDED
      data_type:
        current_data_type: VARCHAR
  - name: ad_group_state
    description: Current state of the ad group
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
        - disabled
        - paused
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: creation_date
    description: Timestamp of when the ad group was initially created
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: last_updated_date
    description: Timestamp of when the ad group was last modified
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents the timestamp when the ad group was last
        modified. For this table, each row represents a snapshot of the ad group's
        state at a specific date. last_updated_date is unique across rows as it captures
        the exact moment of each change.
      data_type:
        current_data_type: TIMESTAMP
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:31:44.842442+00:00
WITH 
"campaign_history_data_projected" AS (
    -- Projection: Selecting 15 out of 16 columns
    -- Columns projected out: ['id', 'last_updated_date', '_fivetran_synced', 'bidding_strategy', 'creation_date', 'budget', 'end_date', 'name', 'portfolio_id', 'profile_id', 'serving_status', 'start_date', 'state', 'targeting_type', 'budget_type', 'effective_budget']
    SELECT 
        "id",
        "last_updated_date",
        "bidding_strategy",
        "creation_date",
        "budget",
        "end_date",
        "name",
        "portfolio_id",
        "profile_id",
        "serving_status",
        "start_date",
        "state",
        "targeting_type",
        "budget_type",
        "effective_budget"
    FROM "memory"."main"."campaign_history_data"
),

"campaign_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> campaign_id
    -- budget -> budget_amount
    -- name -> campaign_name
    -- state -> campaign_state
    SELECT 
        "id" AS "campaign_id",
        "last_updated_date",
        "bidding_strategy",
        "creation_date",
        "budget" AS "budget_amount",
        "end_date",
        "name" AS "campaign_name",
        "portfolio_id",
        "profile_id",
        "serving_status",
        "start_date",
        "state" AS "campaign_state",
        "targeting_type",
        "budget_type",
        "effective_budget"
    FROM "campaign_history_data_projected"
),

"campaign_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- budget_amount: from INT to DECIMAL
    -- creation_date: from VARCHAR to TIMESTAMP
    -- effective_budget: from DECIMAL to VARCHAR
    -- end_date: from DECIMAL to DATE
    -- last_updated_date: from VARCHAR to TIMESTAMP
    -- start_date: from VARCHAR to DATE
    SELECT
        "campaign_id",
        "bidding_strategy",
        "campaign_name",
        "portfolio_id",
        "profile_id",
        "serving_status",
        "campaign_state",
        "targeting_type",
        "budget_type",
        CAST("budget_amount" AS DECIMAL) 
        AS "budget_amount",
        CAST("creation_date" AS TIMESTAMP) 
        AS "creation_date",
        CAST("effective_budget" AS VARCHAR) 
        AS "effective_budget",
        CAST("end_date" AS DATE) 
        AS "end_date",
        CAST("last_updated_date" AS TIMESTAMP) 
        AS "last_updated_date",
        CAST("start_date" AS DATE) 
        AS "start_date"
    FROM "campaign_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "campaign_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_campaign_history_data
  description: The table is about campaign history data. It tracks changes to a single
    campaign over time. The campaign has an ID, name, budget, and various status fields.
    Key attributes include bidding strategy, creation date, portfolio ID, serving
    status, and targeting type. The table shows how the campaign's budget and status
    changed on different dates.
  columns:
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: bidding_strategy
    description: Approach used for bidding in the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - autoForSales
    cocoon_meta:
      future_accepted_values:
      - Manual CPC
      - Enhanced CPC
      - Maximize Clicks
      - Maximize Conversions
      - Target CPA
      - Target ROAS
      - Target Impression Share
      - Maximize Conversion Value
      - CPM
      - vCPM
      - CPA
      - Cost Per View
      - Cost Per Completed View
      - First Page Bid
      - Top of Page Bid
      - Automated Bidding
      - Portfolio Bidding
      - Dayparting
      - Geo-bidding
      - Device-specific Bidding
      data_type:
        current_data_type: VARCHAR
  - name: campaign_name
    description: Name of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: portfolio_id
    description: Identifier for the associated portfolio
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: profile_id
    description: Identifier for the associated profile
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: serving_status
    description: Current status of campaign delivery
    tests:
    - not_null
    - accepted_values:
        values:
        - CAMPAIGN_STATUS_ENABLED
        - CAMPAIGN_PAUSED
    cocoon_meta:
      future_accepted_values:
      - CAMPAIGN_ARCHIVED
      - CAMPAIGN_PENDING
      data_type:
        current_data_type: VARCHAR
  - name: campaign_state
    description: Current operational state of the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
        - paused
    cocoon_meta:
      future_accepted_values:
      - archived
      data_type:
        current_data_type: VARCHAR
  - name: targeting_type
    description: Method used for targeting audience
    tests:
    - not_null
    - accepted_values:
        values:
        - manual
    cocoon_meta:
      future_accepted_values:
      - automated
      - demographic
      - behavioral
      - contextual
      - geographic
      - interest-based
      - lookalike
      - retargeting
      - custom audience
      - keyword
      - device
      - dayparting
      - affinity
      - in-market
      - life events
      - b2b
      data_type:
        current_data_type: VARCHAR
  - name: budget_type
    description: Type of budget allocation (e.g., daily)
    tests:
    - not_null
    - accepted_values:
        values:
        - daily
    cocoon_meta:
      future_accepted_values:
      - weekly
      - monthly
      - quarterly
      - annual
      - biannual
      - custom
      data_type:
        current_data_type: VARCHAR
  - name: budget_amount
    description: Amount allocated for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: creation_date
    description: Timestamp when the campaign was created
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: effective_budget
    description: Actual budget in effect
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: end_date
    description: Scheduled end date of the campaign
    cocoon_meta:
      missing_reason: Not applicable for campaigns without a set end date.
      data_type:
        current_data_type: DATE
  - name: last_updated_date
    description: Timestamp of the most recent update
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents the timestamp of the most recent update
        for each row. For this table, each row represents a state of a campaign at
        a specific point in time. The last_updated_date is unique across rows as it
        captures the exact moment when a change was made to the campaign.
      data_type:
        current_data_type: TIMESTAMP
  - name: start_date
    description: Scheduled start date of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:39:31.598218+00:00
WITH 
"targeting_keyword_report_data_projected" AS (
    -- Projection: Selecting 15 out of 16 columns
    -- Columns projected out: ['ad_group_id', 'campaign_id', 'date_', 'keyword_id', '_fivetran_synced', 'ad_keyword_status', 'campaign_budget_amount', 'campaign_budget_currency_code', 'campaign_budget_type', 'clicks', 'cost', 'impressions', 'keyword_bid', 'keyword_type', 'match_type', 'targeting']
    SELECT 
        "ad_group_id",
        "campaign_id",
        "date_",
        "keyword_id",
        "ad_keyword_status",
        "campaign_budget_amount",
        "campaign_budget_currency_code",
        "campaign_budget_type",
        "clicks",
        "cost",
        "impressions",
        "keyword_bid",
        "keyword_type",
        "match_type",
        "targeting"
    FROM "memory"."main"."targeting_keyword_report_data"
),

"targeting_keyword_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> performance_date
    -- ad_keyword_status -> keyword_status
    -- campaign_budget_amount -> daily_budget_amount
    -- campaign_budget_currency_code -> budget_currency
    -- campaign_budget_type -> budget_type
    -- targeting -> target_keyword
    SELECT 
        "ad_group_id",
        "campaign_id",
        "date_" AS "performance_date",
        "keyword_id",
        "ad_keyword_status" AS "keyword_status",
        "campaign_budget_amount" AS "daily_budget_amount",
        "campaign_budget_currency_code" AS "budget_currency",
        "campaign_budget_type" AS "budget_type",
        "clicks",
        "cost",
        "impressions",
        "keyword_bid",
        "keyword_type",
        "match_type",
        "targeting" AS "target_keyword"
    FROM "targeting_keyword_report_data_projected"
),

"targeting_keyword_report_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- performance_date: from VARCHAR to DATE
    SELECT
        "ad_group_id",
        "campaign_id",
        "keyword_id",
        "keyword_status",
        "daily_budget_amount",
        "budget_currency",
        "budget_type",
        "clicks",
        "cost",
        "impressions",
        "keyword_bid",
        "keyword_type",
        "match_type",
        "target_keyword",
        CAST("performance_date" AS DATE) 
        AS "performance_date"
    FROM "targeting_keyword_report_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "targeting_keyword_report_data_projected_renamed_casted"

version: 2
models:
- name: stg_targeting_keyword_report_data
  description: The table is about targeting keyword report data for digital advertising.
    It contains metrics like clicks, cost, and impressions for specific keywords within
    ad groups and campaigns. Each row represents performance data for a keyword on
    a particular date. The table includes campaign budget information, keyword bid
    amounts, and match types. This data allows advertisers to analyze and optimize
    their keyword targeting strategies.
  columns:
  - name: ad_group_id
    description: Unique identifier for the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: keyword_id
    description: Unique identifier for the keyword
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: keyword_status
    description: Current status of the keyword
    tests:
    - not_null
    - accepted_values:
        values:
        - ENABLED
    cocoon_meta:
      future_accepted_values:
      - DISABLED
      - PAUSED
      data_type:
        current_data_type: VARCHAR
  - name: daily_budget_amount
    description: Daily budget amount for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: budget_currency
    description: Currency code for the budget
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: budget_type
    description: Type of budget allocation
    tests:
    - not_null
    - accepted_values:
        values:
        - DAILY_BUDGET
    cocoon_meta:
      future_accepted_values:
      - LIFETIME_BUDGET
      - MONTHLY_BUDGET
      - ANNUAL_BUDGET
      - QUARTERLY_BUDGET
      - PROJECT_BUDGET
      - CAMPAIGN_BUDGET
      data_type:
        current_data_type: VARCHAR
  - name: clicks
    description: Number of clicks received
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: cost
    description: Total cost of clicks
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: impressions
    description: Number of ad impressions
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: keyword_bid
    description: Bid amount for the keyword
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: keyword_type
    description: Type of keyword (e.g., exact, broad)
    tests:
    - not_null
    - accepted_values:
        values:
        - EXACT
    cocoon_meta:
      future_accepted_values:
      - PHRASE
      - BROAD
      - NEGATIVE
      data_type:
        current_data_type: VARCHAR
  - name: match_type
    description: Match type for the keyword
    tests:
    - not_null
    - accepted_values:
        values:
        - EXACT
    cocoon_meta:
      future_accepted_values:
      - PHRASE
      - BROAD
      - NEGATIVE
      data_type:
        current_data_type: VARCHAR
  - name: target_keyword
    description: Targeted keyword phrase
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: performance_date
    description: Date of the performance data
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:35:21.494921+00:00
WITH 
"portfolio_history_data_projected" AS (
    -- Projection: Selecting 13 out of 14 columns
    -- Columns projected out: ['id', 'last_updated_date', '_fivetran_synced', 'budget_amount', 'budget_currency_code', 'budget_end_date', 'budget_policy', 'budget_start_date', 'creation_date', 'in_budget', 'name', 'profile_id', 'serving_status', 'state']
    SELECT 
        "id",
        "last_updated_date",
        "budget_amount",
        "budget_currency_code",
        "budget_end_date",
        "budget_policy",
        "budget_start_date",
        "creation_date",
        "in_budget",
        "name",
        "profile_id",
        "serving_status",
        "state"
    FROM "memory"."main"."portfolio_history_data"
),

"portfolio_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> entry_id
    -- budget_currency_code -> currency_code
    -- in_budget -> is_within_budget
    -- name -> portfolio_name
    -- serving_status -> operational_status
    -- state -> portfolio_state
    SELECT 
        "id" AS "entry_id",
        "last_updated_date",
        "budget_amount",
        "budget_currency_code" AS "currency_code",
        "budget_end_date",
        "budget_policy",
        "budget_start_date",
        "creation_date",
        "in_budget" AS "is_within_budget",
        "name" AS "portfolio_name",
        "profile_id",
        "serving_status" AS "operational_status",
        "state" AS "portfolio_state"
    FROM "portfolio_history_data_projected"
),

"portfolio_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- budget_amount: from INT to DECIMAL
    -- budget_end_date: from VARCHAR to DATE
    -- budget_start_date: from VARCHAR to DATE
    -- creation_date: from VARCHAR to TIMESTAMP
    -- last_updated_date: from VARCHAR to TIMESTAMP
    SELECT
        "entry_id",
        "currency_code",
        "budget_policy",
        "is_within_budget",
        "portfolio_name",
        "profile_id",
        "operational_status",
        "portfolio_state",
        CAST("budget_amount" AS DECIMAL) 
        AS "budget_amount",
        CAST("budget_end_date" AS DATE) 
        AS "budget_end_date",
        CAST("budget_start_date" AS DATE) 
        AS "budget_start_date",
        CAST("creation_date" AS TIMESTAMP) 
        AS "creation_date",
        CAST("last_updated_date" AS TIMESTAMP) 
        AS "last_updated_date"
    FROM "portfolio_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "portfolio_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_portfolio_history_data
  description: The table is about portfolio history data. It contains details of various
    portfolios including budget amounts, currencies, date ranges, and statuses. Each
    row represents a snapshot of a portfolio at a specific time. Key fields include
    budget information, portfolio name, profile ID, and serving status. The table
    tracks changes to portfolios over time, allowing for historical analysis of budget
    adjustments and status changes.
  columns:
  - name: entry_id
    description: Unique identifier for the portfolio entry
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: currency_code
    description: Currency code for the budget amount
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: budget_policy
    description: Type of budgeting policy applied
    tests:
    - not_null
    - accepted_values:
        values:
        - dateRange
        - monthlyRecurring
    cocoon_meta:
      future_accepted_values:
      - annualRecurring
      - quarterlyRecurring
      - oneTime
      - projectBased
      - rollingForecast
      - zeroBasedBudgeting
      - activityBasedBudgeting
      - flexibleBudgeting
      - incrementalBudgeting
      - performanceBasedBudgeting
      data_type:
        current_data_type: VARCHAR
  - name: is_within_budget
    description: Boolean indicating if portfolio is within budget
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: BOOLEAN
  - name: portfolio_name
    description: Name of the portfolio
    tests:
    - not_null
    - accepted_values:
        values:
        - Empire
        - First Order
        - New Republic
        - Rebel Alliance
        - Resistance
    cocoon_meta:
      future_accepted_values:
      - Galactic Republic
      - Confederacy of Independent Systems (CIS)
      - Jedi Order
      - Sith Empire
      - Mandalorians
      - Hutt Cartel
      - Black Sun
      - Trade Federation
      data_type:
        current_data_type: VARCHAR
  - name: profile_id
    description: Unique identifier for the associated profile
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: operational_status
    description: Current operational status of the portfolio
    tests:
    - not_null
    - accepted_values:
        values:
        - PORTFOLIO_STATUS_ENABLED
        - PORTFOLIO_STATUS_PAUSED
    cocoon_meta:
      future_accepted_values:
      - PORTFOLIO_STATUS_DISABLED
      data_type:
        current_data_type: VARCHAR
  - name: portfolio_state
    description: Current state of the portfolio (enabled/paused)
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
        - paused
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: budget_amount
    description: Numeric value of the portfolio budget
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: budget_end_date
    description: End date of the budget period
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  - name: budget_start_date
    description: Start date of the budget period
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  - name: creation_date
    description: Timestamp when the portfolio was initially created
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: last_updated_date
    description: Timestamp of the last update to the portfolio
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:33:52.591635+00:00
WITH 
"keyword_history_data_projected" AS (
    -- Projection: Selecting 12 out of 13 columns
    -- Columns projected out: ['id', 'last_updated_date', '_fivetran_synced', 'ad_group_id', 'bid', 'campaign_id', 'creation_date', 'keyword_text', 'match_type', 'native_language_keyword', 'serving_status', 'state', 'native_language_locale']
    SELECT 
        "id",
        "last_updated_date",
        "ad_group_id",
        "bid",
        "campaign_id",
        "creation_date",
        "keyword_text",
        "match_type",
        "native_language_keyword",
        "serving_status",
        "state",
        "native_language_locale"
    FROM "memory"."main"."keyword_history_data"
),

"keyword_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> keyword_id
    -- bid -> bid_amount
    -- state -> keyword_state
    SELECT 
        "id" AS "keyword_id",
        "last_updated_date",
        "ad_group_id",
        "bid" AS "bid_amount",
        "campaign_id",
        "creation_date",
        "keyword_text",
        "match_type",
        "native_language_keyword",
        "serving_status",
        "state" AS "keyword_state",
        "native_language_locale"
    FROM "keyword_history_data_projected"
),

"keyword_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- creation_date: from VARCHAR to TIMESTAMP
    -- last_updated_date: from VARCHAR to TIMESTAMP
    -- native_language_keyword: from DECIMAL to VARCHAR
    -- native_language_locale: from DECIMAL to VARCHAR
    SELECT
        "keyword_id",
        "ad_group_id",
        "bid_amount",
        "campaign_id",
        "keyword_text",
        "match_type",
        "serving_status",
        "keyword_state",
        CAST("creation_date" AS TIMESTAMP) 
        AS "creation_date",
        CAST("last_updated_date" AS TIMESTAMP) 
        AS "last_updated_date",
        CAST("native_language_keyword" AS VARCHAR) 
        AS "native_language_keyword",
        CAST("native_language_locale" AS VARCHAR) 
        AS "native_language_locale"
    FROM "keyword_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "keyword_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_keyword_history_data
  description: The table is about keyword history data for advertising campaigns.
    It contains details such as keyword ID, ad group ID, campaign ID, bid amount,
    keyword text, match type, serving status, and state. Each keyword can have multiple
    entries with different last updated dates, showing changes in bid amounts or serving
    status over time. The table tracks the performance and status of keywords used
    in advertising campaigns.
  columns:
  - name: keyword_id
    description: Unique identifier for the keyword
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: ad_group_id
    description: Identifier for the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: bid_amount
    description: Bid amount for the keyword
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: campaign_id
    description: Identifier for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: keyword_text
    description: The actual keyword text
    tests:
    - not_null
    - accepted_values:
        values:
        - x wing parts
        - b wing parts
        - y wing parts
    cocoon_meta:
      future_accepted_values:
      - a wing parts
      - tie fighter parts
      - tie interceptor parts
      - tie bomber parts
      - tie defender parts
      - snowspeeder parts
      - u wing parts
      - z-95 headhunter parts
      - arc-170 parts
      - v wing parts
      data_type:
        current_data_type: VARCHAR
  - name: match_type
    description: Type of keyword match (e.g., exact)
    tests:
    - not_null
    - accepted_values:
        values:
        - exact
    cocoon_meta:
      future_accepted_values:
      - phrase
      - broad
      - modified broad
      data_type:
        current_data_type: VARCHAR
  - name: serving_status
    description: Current status of the keyword for serving ads
    tests:
    - not_null
    - accepted_values:
        values:
        - TARGETING_CLAUSE_STATUS_LIVE
        - CAMPAIGN_PAUSED
    cocoon_meta:
      future_accepted_values:
      - TARGETING_CLAUSE_STATUS_PAUSED
      - TARGETING_CLAUSE_STATUS_ARCHIVED
      - CAMPAIGN_ARCHIVED
      - TARGETING_CLAUSE_STATUS_PENDING
      - TARGETING_CLAUSE_STATUS_REJECTED
      data_type:
        current_data_type: VARCHAR
  - name: keyword_state
    description: State of the keyword (e.g., enabled, paused)
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
    cocoon_meta:
      future_accepted_values:
      - paused
      - removed
      data_type:
        current_data_type: VARCHAR
  - name: creation_date
    description: Date and time when the keyword was created
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: last_updated_date
    description: Date and time of the last update
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: native_language_keyword
    description: Keyword in native language (if applicable)
    cocoon_meta:
      missing_reason: Not applicable for keywords already in native language
      data_type:
        current_data_type: VARCHAR
  - name: native_language_locale
    description: Locale for the native language keyword
    cocoon_meta:
      missing_reason: Not applicable when keyword is in default language
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:32:44.998118+00:00
WITH 
"campaign_level_report_data_projected" AS (
    -- Projection: Selecting 12 out of 13 columns
    -- Columns projected out: ['campaign_id', 'date_', '_fivetran_synced', 'campaign_applicable_budget_rule_id', 'campaign_applicable_budget_rule_name', 'campaign_bidding_strategy', 'campaign_budget_amount', 'campaign_budget_currency_code', 'campaign_budget_type', 'clicks', 'cost', 'impressions', 'campaign_rule_based_budget_amount']
    SELECT 
        "campaign_id",
        "date_",
        "campaign_applicable_budget_rule_id",
        "campaign_applicable_budget_rule_name",
        "campaign_bidding_strategy",
        "campaign_budget_amount",
        "campaign_budget_currency_code",
        "campaign_budget_type",
        "clicks",
        "cost",
        "impressions",
        "campaign_rule_based_budget_amount"
    FROM "memory"."main"."campaign_level_report_data"
),

"campaign_level_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> report_date
    -- campaign_applicable_budget_rule_id -> budget_rule_id
    -- campaign_applicable_budget_rule_name -> budget_rule_name
    -- campaign_bidding_strategy -> bidding_strategy
    -- campaign_budget_amount -> daily_budget_amount
    -- campaign_budget_currency_code -> budget_currency
    -- campaign_budget_type -> budget_type
    -- campaign_rule_based_budget_amount -> rule_based_budget_amount
    SELECT 
        "campaign_id",
        "date_" AS "report_date",
        "campaign_applicable_budget_rule_id" AS "budget_rule_id",
        "campaign_applicable_budget_rule_name" AS "budget_rule_name",
        "campaign_bidding_strategy" AS "bidding_strategy",
        "campaign_budget_amount" AS "daily_budget_amount",
        "campaign_budget_currency_code" AS "budget_currency",
        "campaign_budget_type" AS "budget_type",
        "clicks",
        "cost",
        "impressions",
        "campaign_rule_based_budget_amount" AS "rule_based_budget_amount"
    FROM "campaign_level_report_data_projected"
),

"campaign_level_report_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- budget_type: The problem is that 'DAILY_BUDGET0.0' appears to be a redundant version of 'DAILY_BUDGET' with an unnecessary '0.0' appended to it. This is likely due to a data entry error or a glitch in the system that recorded the budget types. The correct value should be 'DAILY_BUDGET' for all entries, as this is the most frequent and logically correct representation of a daily budget type.
    SELECT
        "campaign_id",
        "report_date",
        "budget_rule_id",
        "budget_rule_name",
        "bidding_strategy",
        "daily_budget_amount",
        "budget_currency",
        CASE
            WHEN "budget_type" = 'DAILY_BUDGET0.0' THEN 'DAILY_BUDGET'
            ELSE "budget_type"
        END AS "budget_type",
        "clicks",
        "cost",
        "impressions",
        "rule_based_budget_amount"
    FROM "campaign_level_report_data_projected_renamed"
),

"campaign_level_report_data_projected_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- budget_rule_id: from DECIMAL to VARCHAR
    -- budget_rule_name: from DECIMAL to VARCHAR
    -- impressions: from DECIMAL to VARCHAR
    -- report_date: from VARCHAR to DATE
    -- rule_based_budget_amount: from DECIMAL to VARCHAR
    SELECT
        "campaign_id",
        "bidding_strategy",
        "daily_budget_amount",
        "budget_currency",
        "budget_type",
        "clicks",
        "cost",
        CAST("budget_rule_id" AS VARCHAR) 
        AS "budget_rule_id",
        CAST("budget_rule_name" AS VARCHAR) 
        AS "budget_rule_name",
        CAST("impressions" AS VARCHAR) 
        AS "impressions",
        CAST("report_date" AS DATE) 
        AS "report_date",
        CAST("rule_based_budget_amount" AS VARCHAR) 
        AS "rule_based_budget_amount"
    FROM "campaign_level_report_data_projected_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT *
FROM "campaign_level_report_data_projected_renamed_cleaned_casted"

version: 2
models:
- name: stg_campaign_level_report_data
  description: The table is about campaign performance data. It includes details such
    as campaign ID, date, budget rules, bidding strategy, budget amount and type,
    currency code, and performance metrics like clicks, cost, and impressions. Each
    row represents a daily report for a specific campaign, showing its configuration
    and performance on that day.
  columns:
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: bidding_strategy
    description: Strategy used for bidding in the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - optimizeForSales
    cocoon_meta:
      future_accepted_values:
      - optimizeForConversions
      - targetCPA
      - targetROAS
      - maximizeClicks
      - manualCPC
      - enhancedCPC
      - maximizeConversions
      - maximizeConversionValue
      data_type:
        current_data_type: VARCHAR
  - name: daily_budget_amount
    description: Daily budget amount set for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: budget_currency
    description: Currency code for the campaign budget
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: budget_type
    description: Type of budget allocation for the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - DAILY_BUDGET
    cocoon_meta:
      future_accepted_values:
      - LIFETIME_BUDGET
      - CAMPAIGN_BUDGET
      data_type:
        current_data_type: VARCHAR
  - name: clicks
    description: Number of clicks received by the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: cost
    description: Total cost incurred by the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: budget_rule_id
    description: ID of the budget rule applied to the campaign
    cocoon_meta:
      missing_reason: No budget rule applied to these campaigns.
      data_type:
        current_data_type: VARCHAR
  - name: budget_rule_name
    description: Name of the budget rule applied to the campaign
    cocoon_meta:
      missing_reason: No budget rule applied to these campaigns.
      data_type:
        current_data_type: VARCHAR
  - name: impressions
    description: Number of times the campaign was displayed
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: report_date
    description: Date of the campaign performance data
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  - name: rule_based_budget_amount
    description: Budget amount determined by applied rules
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:38:31.035705+00:00
WITH 
"search_term_ad_keyword_report_data_projected" AS (
    -- Projection: Selecting 13 out of 14 columns
    -- Columns projected out: ['ad_group_id', 'campaign_id', 'date_', 'keyword_id', '_fivetran_synced', 'campaign_budget_amount', 'campaign_budget_currency_code', 'campaign_budget_type', 'clicks', 'cost', 'impressions', 'keyword_bid', 'search_term', 'targeting']
    SELECT 
        "ad_group_id",
        "campaign_id",
        "date_",
        "keyword_id",
        "campaign_budget_amount",
        "campaign_budget_currency_code",
        "campaign_budget_type",
        "clicks",
        "cost",
        "impressions",
        "keyword_bid",
        "search_term",
        "targeting"
    FROM "memory"."main"."search_term_ad_keyword_report_data"
),

"search_term_ad_keyword_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> performance_date
    -- campaign_budget_amount -> daily_budget_amount
    -- campaign_budget_currency_code -> budget_currency
    -- campaign_budget_type -> budget_type
    -- cost -> daily_ad_cost
    -- keyword_bid -> keyword_bid_amount
    -- targeting -> targeting_criteria
    SELECT 
        "ad_group_id",
        "campaign_id",
        "date_" AS "performance_date",
        "keyword_id",
        "campaign_budget_amount" AS "daily_budget_amount",
        "campaign_budget_currency_code" AS "budget_currency",
        "campaign_budget_type" AS "budget_type",
        "clicks",
        "cost" AS "daily_ad_cost",
        "impressions",
        "keyword_bid" AS "keyword_bid_amount",
        "search_term",
        "targeting" AS "targeting_criteria"
    FROM "search_term_ad_keyword_report_data_projected"
),

"search_term_ad_keyword_report_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- performance_date: from VARCHAR to DATE
    SELECT
        "ad_group_id",
        "campaign_id",
        "keyword_id",
        "daily_budget_amount",
        "budget_currency",
        "budget_type",
        "clicks",
        "daily_ad_cost",
        "impressions",
        "keyword_bid_amount",
        "search_term",
        "targeting_criteria",
        CAST("performance_date" AS DATE) 
        AS "performance_date"
    FROM "search_term_ad_keyword_report_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "search_term_ad_keyword_report_data_projected_renamed_casted"

version: 2
models:
- name: stg_search_term_ad_keyword_report_data
  description: The table is about search term and ad keyword performance data. It
    includes details such as ad group ID, campaign ID, date, keyword ID, campaign
    budget information, clicks, cost, impressions, keyword bid, search term, and targeting.
    Each row represents a daily performance report for a specific keyword and search
    term combination within an ad campaign.
  columns:
  - name: ad_group_id
    description: Identifier for the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: campaign_id
    description: Identifier for the advertising campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: keyword_id
    description: Identifier for the keyword
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: daily_budget_amount
    description: Daily budget amount for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: budget_currency
    description: Currency code for the campaign budget
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: budget_type
    description: Type of budget for the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - DAILY_BUDGET
    cocoon_meta:
      future_accepted_values:
      - LIFETIME_BUDGET
      - TOTAL_BUDGET
      - MONTHLY_BUDGET
      - WEEKLY_BUDGET
      - FLEXIBLE_BUDGET
      data_type:
        current_data_type: VARCHAR
  - name: clicks
    description: Number of clicks received
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: daily_ad_cost
    description: Cost of the advertising for the day
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: impressions
    description: Number of times the ad was shown
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: keyword_bid_amount
    description: Bid amount for the keyword
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: search_term
    description: Actual search term used by the user
    tests:
    - not_null
    - accepted_values:
        values:
        - b wing parts
        - y wing parts
        - x wing parts
    cocoon_meta:
      future_accepted_values:
      - a wing parts
      - u wing parts
      - v wing parts
      - tie fighter parts
      - millennium falcon parts
      - death star parts
      - star destroyer parts
      - lambda shuttle parts
      - naboo starfighter parts
      - jedi starfighter parts
      data_type:
        current_data_type: VARCHAR
  - name: targeting_criteria
    description: Targeting criteria for the ad
    tests:
    - not_null
    - accepted_values:
        values:
        - b wing parts
        - y wing parts
        - x wing parts
    cocoon_meta:
      future_accepted_values:
      - A-wing parts
      - B-wing parts
      - X-wing parts
      - Y-wing parts
      - TIE Fighter parts
      - TIE Interceptor parts
      - TIE Bomber parts
      - U-wing parts
      - V-wing parts
      - Z-95 Headhunter parts
      - Jedi Starfighter parts
      - Naboo Starfighter parts
      data_type:
        current_data_type: VARCHAR
  - name: performance_date
    description: Date of the performance data
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:30:07.435997+00:00
WITH 
"ad_group_level_report_data_projected" AS (
    -- Projection: Selecting 6 out of 7 columns
    -- Columns projected out: ['ad_group_id', 'date_', '_fivetran_synced', 'campaign_bidding_strategy', 'clicks', 'cost', 'impressions']
    SELECT 
        "ad_group_id",
        "date_",
        "campaign_bidding_strategy",
        "clicks",
        "cost",
        "impressions"
    FROM "memory"."main"."ad_group_level_report_data"
),

"ad_group_level_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> performance_date
    -- campaign_bidding_strategy -> bidding_strategy
    SELECT 
        "ad_group_id",
        "date_" AS "performance_date",
        "campaign_bidding_strategy" AS "bidding_strategy",
        "clicks",
        "cost",
        "impressions"
    FROM "ad_group_level_report_data_projected"
),

"ad_group_level_report_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- performance_date: from VARCHAR to DATE
    SELECT
        "ad_group_id",
        "bidding_strategy",
        "clicks",
        "cost",
        "impressions",
        CAST("performance_date" AS DATE) 
        AS "performance_date"
    FROM "ad_group_level_report_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "ad_group_level_report_data_projected_renamed_casted"

version: 2
models:
- name: stg_ad_group_level_report_data
  description: The table is about ad group performance data. It includes daily metrics
    for an ad group. The data shows the ad group ID, date, campaign bidding strategy,
    clicks, cost, and impressions. Each row represents a single day's performance
    for the ad group. The table tracks the ad group's performance over time, allowing
    for analysis of its advertising effectiveness.
  columns:
  - name: ad_group_id
    description: Unique identifier for the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: bidding_strategy
    description: Bidding strategy used for the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - legacy
    cocoon_meta:
      future_accepted_values:
      - manual_cpc
      - manual_cpv
      - manual_cpm
      - target_cpa
      - target_roas
      - maximize_conversions
      - maximize_conversion_value
      - target_impression_share
      - viewable_cpm
      - ecpc
      data_type:
        current_data_type: VARCHAR
  - name: clicks
    description: Number of clicks received on the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: cost
    description: Total cost spent on the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: impressions
    description: Number of times the ad was displayed
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: performance_date
    description: Date of the performance data
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: This column represents the date of the performance data. For
        this table, each row represents a single day's performance for an ad group.
        The performance_date is unique across rows for a specific ad group, as there
        should be only one entry per day for each ad group.
      data_type:
        current_data_type: DATE
  tests: []

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
-- Generated at 2024-08-25 17:28:43.711244+00:00
WITH 
"advertised_product_report_data_projected" AS (
    -- Projection: Selecting 12 out of 13 columns
    -- Columns projected out: ['ad_group_id', 'ad_id', 'campaign_id', 'date_', '_fivetran_synced', 'campaign_budget_amount', 'campaign_budget_currency_code', 'campaign_budget_type', 'clicks', 'cost', 'impressions', '__advertised_asin', '__advertised_sku']
    SELECT 
        "ad_group_id",
        "ad_id",
        "campaign_id",
        "date_",
        "campaign_budget_amount",
        "campaign_budget_currency_code",
        "campaign_budget_type",
        "clicks",
        "cost",
        "impressions",
        "__advertised_asin",
        "__advertised_sku"
    FROM "memory"."main"."advertised_product_report_data"
),

"advertised_product_report_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- date_ -> report_date
    -- campaign_budget_amount -> daily_budget_amount
    -- campaign_budget_currency_code -> budget_currency
    -- campaign_budget_type -> budget_type
    -- cost -> ad_spend
    -- __advertised_asin -> product_asin
    -- __advertised_sku -> product_sku
    SELECT 
        "ad_group_id",
        "ad_id",
        "campaign_id",
        "date_" AS "report_date",
        "campaign_budget_amount" AS "daily_budget_amount",
        "campaign_budget_currency_code" AS "budget_currency",
        "campaign_budget_type" AS "budget_type",
        "clicks",
        "cost" AS "ad_spend",
        "impressions",
        "__advertised_asin" AS "product_asin",
        "__advertised_sku" AS "product_sku"
    FROM "advertised_product_report_data_projected"
),

"advertised_product_report_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- product_asin: from DECIMAL to VARCHAR
    -- product_sku: from DECIMAL to VARCHAR
    -- report_date: from VARCHAR to DATE
    SELECT
        "ad_group_id",
        "ad_id",
        "campaign_id",
        "daily_budget_amount",
        "budget_currency",
        "budget_type",
        "clicks",
        "ad_spend",
        "impressions",
        CAST("product_asin" AS VARCHAR) 
        AS "product_asin",
        CAST("product_sku" AS VARCHAR) 
        AS "product_sku",
        CAST("report_date" AS DATE) 
        AS "report_date"
    FROM "advertised_product_report_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT *
FROM "advertised_product_report_data_projected_renamed_casted"

version: 2
models:
- name: stg_advertised_product_report_data
  description: The table is about advertised product reports. It includes data on
    ad groups, ads, and campaigns. Each row represents daily performance metrics for
    a specific ad. Key details include campaign budget information, clicks, cost,
    and impressions. The table also has fields for advertised ASIN and SKU, though
    these appear empty in the samples. This data likely helps analyze and optimize
    advertising performance across different campaigns and ad groups.
  columns:
  - name: ad_group_id
    description: Identifier for the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: ad_id
    description: Unique identifier for the advertisement
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: campaign_id
    description: Identifier for the advertising campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: daily_budget_amount
    description: Daily budget amount for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: budget_currency
    description: Currency code for the budget
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: budget_type
    description: Type of budget allocation
    tests:
    - not_null
    - accepted_values:
        values:
        - DAILY_BUDGET
    cocoon_meta:
      future_accepted_values:
      - LIFETIME_BUDGET
      - MONTHLY_BUDGET
      - ANNUAL_BUDGET
      - QUARTERLY_BUDGET
      - PROJECT_BUDGET
      - CAMPAIGN_BUDGET
      data_type:
        current_data_type: VARCHAR
  - name: clicks
    description: Number of clicks on the ad
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: ad_spend
    description: Total cost of the advertising
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: impressions
    description: Number of times the ad was displayed
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: product_asin
    description: Amazon Standard Identification Number of advertised product
    cocoon_meta:
      missing_reason: Ad may not be associated with a specific product.
      data_type:
        current_data_type: VARCHAR
  - name: product_sku
    description: Stock Keeping Unit of advertised product
    cocoon_meta:
      missing_reason: Ad may not be associated with a specific product.
      data_type:
        current_data_type: VARCHAR
  - name: report_date
    description: Date of the recorded data
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  tests: []

-- Slowly Changing Dimension: Dimension keys are "portfolio_name", "profile_id"
-- Version columns are creation_date, last_updated_date
-- We will create Type 1 SCD (latest snapshot)

SELECT 
    "entry_id",
    "currency_code",
    "budget_policy",
    "is_within_budget",
    "portfolio_name",
    "profile_id",
    "operational_status",
    "portfolio_state",
    "budget_amount",
    "budget_end_date",
    "budget_start_date"
FROM "stg_portfolio_history_data"
QUALIFY ROW_NUMBER() OVER ( 
    PARTITION BY "portfolio_name", "profile_id"
    ORDER BY
        last_updated_date DESC,
        creation_date DESC
) = 1

version: 2
models:
- name: snapshot_portfolio_history_data
  description: The table is about current portfolio data. It tracks the most recent
    version of each portfolio, including its budget details, operational status, and
    currency. Each row represents a unique portfolio identified by its name and profile
    ID. The table provides up-to-date information on budget amounts, date ranges,
    and compliance status for various portfolios across different currencies and regions.
  columns:
  - name: entry_id
    description: Unique identifier for the portfolio entry
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: currency_code
    description: Currency code for the budget amount
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: budget_policy
    description: Type of budgeting policy applied
    tests:
    - not_null
    - accepted_values:
        values:
        - dateRange
        - monthlyRecurring
    cocoon_meta:
      future_accepted_values:
      - annualRecurring
      - quarterlyRecurring
      - oneTime
      - projectBased
      - rollingForecast
      - zeroBasedBudgeting
      - activityBasedBudgeting
      - flexibleBudgeting
      - incrementalBudgeting
      - performanceBasedBudgeting
      data_type:
        current_data_type: VARCHAR
  - name: is_within_budget
    description: Boolean indicating if portfolio is within budget
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: BOOLEAN
  - name: portfolio_name
    description: Name of the portfolio
    tests:
    - not_null
    - accepted_values:
        values:
        - Empire
        - First Order
        - New Republic
        - Rebel Alliance
        - Resistance
    cocoon_meta:
      future_accepted_values:
      - Galactic Republic
      - Confederacy of Independent Systems (CIS)
      - Jedi Order
      - Sith Empire
      - Mandalorians
      - Hutt Cartel
      - Black Sun
      - Trade Federation
      data_type:
        current_data_type: VARCHAR
  - name: profile_id
    description: Unique identifier for the associated profile
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: operational_status
    description: Current operational status of the portfolio
    tests:
    - not_null
    - accepted_values:
        values:
        - PORTFOLIO_STATUS_ENABLED
        - PORTFOLIO_STATUS_PAUSED
    cocoon_meta:
      future_accepted_values:
      - PORTFOLIO_STATUS_DISABLED
      data_type:
        current_data_type: VARCHAR
  - name: portfolio_state
    description: Current state of the portfolio (enabled/paused)
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
        - paused
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: budget_amount
    description: Numeric value of the portfolio budget
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: budget_end_date
    description: End date of the budget period
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  - name: budget_start_date
    description: Start date of the budget period
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  tests: []
cocoon_meta:
  scd_base_table: stg_portfolio_history_data
  scd_columns:
  - creation_date
  - last_updated_date

-- Slowly Changing Dimension: Dimension keys are "ad_id"
-- Version columns are last_updated_date, ad_serving_status, ad_state
-- We will create Type 1 SCD (latest snapshot)

SELECT 
    "ad_id",
    "ad_group_id",
    "product_asin",
    "campaign_id",
    "creation_date",
    "product_sku"
FROM "stg_product_ad_history_data"
QUALIFY ROW_NUMBER() OVER ( 
    PARTITION BY "ad_id"
    ORDER BY
        last_updated_date DESC,
        CASE 
            WHEN ad_serving_status = 'AD_STATUS_LIVE' THEN 1
            WHEN ad_serving_status = 'CAMPAIGN_PAUSED' THEN 2
            WHEN ad_serving_status = 'AD_STATUS_PAUSED' THEN 3
            ELSE 4
        END,
        CASE 
            WHEN ad_state = 'enabled' THEN 1
            WHEN ad_state = 'disabled' THEN 2
            ELSE 3
        END
) = 1

version: 2
models:
- name: snapshot_product_ad_history_data
  description: The table is about current product ad status. It tracks the most recent
    version of each ad, identified by a unique ad ID. The table includes details like
    ad group ID, ASIN, campaign ID, creation date, and current serving status. Each
    row represents the latest state of an ad, showing its most up-to-date information
    without historical changes.
  columns:
  - name: ad_id
    description: Unique identifier for the ad
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: Unique dimension key, derived from the slowly changing dimension
      data_type:
        current_data_type: INT
  - name: ad_group_id
    description: Identifier for the group the ad belongs to
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: product_asin
    description: Amazon Standard Identification Number for the product
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: campaign_id
    description: Identifier for the campaign the ad is part of
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: creation_date
    description: Timestamp when the ad was initially created
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: TIMESTAMP
  - name: product_sku
    description: Stock Keeping Unit, appears to be unused
    cocoon_meta:
      missing_reason: Not applicable when product is identified by ASIN instead.
      data_type:
        current_data_type: VARCHAR
  tests: []
cocoon_meta:
  scd_base_table: stg_product_ad_history_data
  scd_columns:
  - last_updated_date
  - ad_serving_status
  - ad_state

-- Slowly Changing Dimension: Dimension keys are "ad_group_id"
-- Version columns are last_updated_date, creation_date
-- We will create Type 1 SCD (latest snapshot)

SELECT 
    "ad_group_id",
    "campaign_id",
    "default_bid",
    "ad_group_name",
    "serving_status",
    "ad_group_state"
FROM "stg_ad_group_history_data"
QUALIFY ROW_NUMBER() OVER ( 
    PARTITION BY "ad_group_id"
    ORDER BY
        last_updated_date DESC,
        creation_date DESC
) = 1

version: 2
models:
- name: snapshot_ad_group_history_data
  description: The table is about current ad group data. It tracks the most recent
    version of each ad group. It includes details such as ad group ID, campaign ID,
    default bid, name, serving status, and state. Each row represents the latest snapshot
    of an ad group's state. The table provides a current view of all ad groups without
    historical changes.
  columns:
  - name: ad_group_id
    description: Unique identifier for the ad group
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: Unique dimension key, derived from the slowly changing dimension
      data_type:
        current_data_type: INT
  - name: campaign_id
    description: Identifier of the campaign the ad group belongs to
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: default_bid
    description: Default bid amount for the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: ad_group_name
    description: Name of the ad group
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: serving_status
    description: Current serving status of the ad group
    tests:
    - not_null
    - accepted_values:
        values:
        - AD_STATUS_LIVE
        - CAMPAIGN_PAUSED
    cocoon_meta:
      future_accepted_values:
      - AD_GROUP_PAUSED
      - AD_STATUS_PAUSED
      - REMOVED
      - PENDING_REVIEW
      - DISAPPROVED
      - ENDED
      - SUSPENDED
      data_type:
        current_data_type: VARCHAR
  - name: ad_group_state
    description: Current state of the ad group
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
        - disabled
        - paused
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  tests: []
cocoon_meta:
  scd_base_table: stg_ad_group_history_data
  scd_columns:
  - last_updated_date
  - creation_date

-- Slowly Changing Dimension: Dimension keys are "campaign_id"
-- Version columns are last_updated_date, creation_date
-- We will create Type 1 SCD (latest snapshot)

SELECT 
    "campaign_id",
    "bidding_strategy",
    "campaign_name",
    "portfolio_id",
    "profile_id",
    "serving_status",
    "campaign_state",
    "targeting_type",
    "budget_type",
    "budget_amount",
    "effective_budget",
    "end_date",
    "start_date"
FROM "stg_campaign_history_data"
QUALIFY ROW_NUMBER() OVER ( 
    PARTITION BY "campaign_id"
    ORDER BY
        last_updated_date DESC,
        creation_date DESC
) = 1

version: 2
models:
- name: snapshot_campaign_history_data
  description: The table is about current campaign data. It tracks the most recent
    version of each campaign. Each row represents a unique campaign with its latest
    attributes. Key information includes campaign ID, name, budget, bidding strategy,
    and status. The table provides a snapshot of campaign settings as of the last
    update, without historical changes.
  columns:
  - name: campaign_id
    description: Unique identifier for the campaign
    tests:
    - not_null
    - unique
    cocoon_meta:
      unique_reason: Unique dimension key, derived from the slowly changing dimension
      data_type:
        current_data_type: INT
  - name: bidding_strategy
    description: Approach used for bidding in the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - autoForSales
    cocoon_meta:
      future_accepted_values:
      - Manual CPC
      - Enhanced CPC
      - Maximize Clicks
      - Maximize Conversions
      - Target CPA
      - Target ROAS
      - Target Impression Share
      - Maximize Conversion Value
      - CPM
      - vCPM
      - CPA
      - Cost Per View
      - Cost Per Completed View
      - First Page Bid
      - Top of Page Bid
      - Automated Bidding
      - Portfolio Bidding
      - Dayparting
      - Geo-bidding
      - Device-specific Bidding
      data_type:
        current_data_type: VARCHAR
  - name: campaign_name
    description: Name of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: VARCHAR
  - name: portfolio_id
    description: Identifier for the associated portfolio
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: profile_id
    description: Identifier for the associated profile
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: INT
  - name: serving_status
    description: Current status of campaign delivery
    tests:
    - not_null
    - accepted_values:
        values:
        - CAMPAIGN_STATUS_ENABLED
        - CAMPAIGN_PAUSED
    cocoon_meta:
      future_accepted_values:
      - CAMPAIGN_ARCHIVED
      - CAMPAIGN_PENDING
      data_type:
        current_data_type: VARCHAR
  - name: campaign_state
    description: Current operational state of the campaign
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
        - paused
    cocoon_meta:
      future_accepted_values:
      - archived
      data_type:
        current_data_type: VARCHAR
  - name: targeting_type
    description: Method used for targeting audience
    tests:
    - not_null
    - accepted_values:
        values:
        - manual
    cocoon_meta:
      future_accepted_values:
      - automated
      - demographic
      - behavioral
      - contextual
      - geographic
      - interest-based
      - lookalike
      - retargeting
      - custom audience
      - keyword
      - device
      - dayparting
      - affinity
      - in-market
      - life events
      - b2b
      data_type:
        current_data_type: VARCHAR
  - name: budget_type
    description: Type of budget allocation (e.g., daily)
    tests:
    - not_null
    - accepted_values:
        values:
        - daily
    cocoon_meta:
      future_accepted_values:
      - weekly
      - monthly
      - quarterly
      - annual
      - biannual
      - custom
      data_type:
        current_data_type: VARCHAR
  - name: budget_amount
    description: Amount allocated for the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DECIMAL
  - name: effective_budget
    description: Actual budget in effect
    cocoon_meta:
      missing_reason: Unknown
      data_type:
        current_data_type: VARCHAR
  - name: end_date
    description: Scheduled end date of the campaign
    cocoon_meta:
      missing_reason: Not applicable for campaigns without a set end date.
      data_type:
        current_data_type: DATE
  - name: start_date
    description: Scheduled start date of the campaign
    tests:
    - not_null
    cocoon_meta:
      data_type:
        current_data_type: DATE
  tests: []
cocoon_meta:
  scd_base_table: stg_campaign_history_data
  scd_columns:
  - last_updated_date
  - creation_date