-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"linkedin_account_history_data_renamed" AS (
    -- Rename: Renaming columns
    -- id -> account_id
    -- last_modified_time -> last_update_time
    -- created_time -> account_creation_time
    -- name -> encrypted_account_name
    -- currency -> account_currency
    -- version_tag -> revision_number
    SELECT 
        "id" AS "account_id",
        "last_modified_time" AS "last_update_time",
        "created_time" AS "account_creation_time",
        "name" AS "encrypted_account_name",
        "currency" AS "account_currency",
        "version_tag" AS "revision_number"
    FROM "linkedin_account_history_data"
),

"linkedin_account_history_data_renamed_casted" AS (
    -- Column Type Casting: 
    -- account_creation_time: from VARCHAR to TIMESTAMP
    -- last_update_time: from VARCHAR to TIMESTAMP
    SELECT
        "account_id",
        "encrypted_account_name",
        "account_currency",
        "revision_number",
        CAST("account_creation_time" AS TIMESTAMP) AS "account_creation_time",
        CAST("last_update_time" AS TIMESTAMP) AS "last_update_time"
    FROM "linkedin_account_history_data_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "linkedin_account_history_data_renamed_casted"

version: 2
models:
- name: stg_linkedin_account_history_data
  description: The table is about LinkedIn account history. It tracks changes to a
    specific account over time. Each row represents a snapshot of the account at a
    particular moment. The table includes the account ID, modification time, creation
    time, encrypted name, currency, and a version tag. The version tag increases with
    each update, indicating the account's revision history.
  columns:
  - name: account_id
    description: Unique identifier for the LinkedIn account
    tests:
    - not_null
  - name: encrypted_account_name
    description: Encrypted name of the account holder
    tests:
    - not_null
  - name: account_currency
    description: Currency associated with the account
    tests:
    - not_null
  - name: revision_number
    description: Incremental number indicating account revision history
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the version number of the account, incrementing
        with each update. For this table, each row is a snapshot of the account at
        a different point in time. The revision_number is unique across rows as it
        increases with each update, making it a good identifier for each specific
        version of the account.
  - name: account_creation_time
    description: Timestamp when the account was initially created
    tests:
    - not_null
  - name: last_update_time
    description: Timestamp of the most recent account modification
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"linkedin_campaign_group_history_data_renamed" AS (
    -- Rename: Renaming columns
    -- id -> campaign_group_id
    -- last_modified_time -> last_modified_timestamp
    -- created_time -> creation_timestamp
    -- name -> encrypted_group_name
    SELECT 
        "id" AS "campaign_group_id",
        "last_modified_time" AS "last_modified_timestamp",
        "account_id",
        "created_time" AS "creation_timestamp",
        "name" AS "encrypted_group_name"
    FROM "linkedin_campaign_group_history_data"
),

"linkedin_campaign_group_history_data_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- last_modified_timestamp: The problem is that one timestamp '2018-09-06 16:52:33.720' includes millisecond precision, while the others do not. The correct values should all follow the same format without milliseconds for consistency. The most frequent format is 'YYYY-MM-DD HH:MM:SS', so we'll map the unusual value to this format. 
    SELECT
        "campaign_group_id",
        CASE
            WHEN "last_modified_timestamp" = '2018-09-06 16:52:33.720' THEN '2018-09-06 16:52:33'
            ELSE "last_modified_timestamp"
        END AS "last_modified_timestamp",
        "account_id",
        "creation_timestamp",
        "encrypted_group_name"
    FROM "linkedin_campaign_group_history_data_renamed"
),

"linkedin_campaign_group_history_data_renamed_cleaned_casted" AS (
    -- Column Type Casting: 
    -- account_id: from INT to VARCHAR
    -- campaign_group_id: from INT to VARCHAR
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- last_modified_timestamp: from VARCHAR to TIMESTAMP
    SELECT
        "encrypted_group_name",
        CAST("account_id" AS VARCHAR) AS "account_id",
        CAST("campaign_group_id" AS VARCHAR) AS "campaign_group_id",
        CAST("creation_timestamp" AS TIMESTAMP) AS "creation_timestamp",
        CAST("last_modified_timestamp" AS TIMESTAMP) AS "last_modified_timestamp"
    FROM "linkedin_campaign_group_history_data_renamed_cleaned"
)

-- COCOON BLOCK END
SELECT * FROM "linkedin_campaign_group_history_data_renamed_cleaned_casted"

version: 2
models:
- name: stg_linkedin_campaign_group_history_data
  description: The table is about LinkedIn campaign groups. It contains historical
    data for each group, including unique ID, modification time, account ID, creation
    time, and encrypted name. Multiple rows can exist for the same group ID, representing
    changes over time. This allows tracking of how campaign groups evolve.
  columns:
  - name: encrypted_group_name
    description: Encrypted name of the campaign group
    tests:
    - not_null
  - name: account_id
    description: LinkedIn account identifier
    tests:
    - not_null
  - name: campaign_group_id
    description: Unique identifier for the campaign group
    tests:
    - not_null
  - name: creation_timestamp
    description: Timestamp of campaign group creation
    tests:
    - not_null
  - name: last_modified_timestamp
    description: Timestamp of last modification to the group
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"linkedin_ad_analytics_by_creative_data_renamed" AS (
    -- Rename: Renaming columns
    -- day_ -> date_
    -- clicks -> click_count
    -- impressions -> impression_count
    -- cost_in_local_currency -> local_currency_cost
    -- cost_in_usd -> usd_cost
    SELECT 
        "creative_id",
        "day_" AS "date_",
        "clicks" AS "click_count",
        "impressions" AS "impression_count",
        "cost_in_local_currency" AS "local_currency_cost",
        "cost_in_usd" AS "usd_cost"
    FROM "linkedin_ad_analytics_by_creative_data"
),

"linkedin_ad_analytics_by_creative_data_renamed_casted" AS (
    -- Column Type Casting: 
    -- click_count: from DECIMAL to INT
    -- creative_id: from INT to VARCHAR
    -- date_: from VARCHAR to TIMESTAMP
    -- impression_count: from DECIMAL to INT
    SELECT
        "local_currency_cost",
        "usd_cost",
        CAST("click_count" AS INT) AS "click_count",
        CAST("creative_id" AS VARCHAR) AS "creative_id",
        CAST("date_" AS TIMESTAMP) AS "date_",
        CAST("impression_count" AS INT) AS "impression_count"
    FROM "linkedin_ad_analytics_by_creative_data_renamed"
),

"linkedin_ad_analytics_by_creative_data_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 3 columns with unacceptable missing values
    -- click_count has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- impression_count has 29.0 percent missing. Strategy: 🔄 Unchanged
    -- usd_cost has 29.0 percent missing. Strategy: 🔄 Unchanged
    SELECT
        "local_currency_cost",
        "usd_cost",
        "creative_id",
        "date_",
        "impression_count"
    FROM "linkedin_ad_analytics_by_creative_data_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "linkedin_ad_analytics_by_creative_data_renamed_casted_missing_handled"

version: 2
models:
- name: stg_linkedin_ad_analytics_by_creative_data
  description: The table is about LinkedIn ad performance metrics. It contains data
    for individual ad creatives, tracked daily. The details include the creative ID,
    date, clicks, impressions, cost in local currency, and cost in USD. However, many
    fields appear to be empty in the sample data, with only creative ID, date, and
    local currency cost populated.
  columns:
  - name: local_currency_cost
    description: Cost of the ad in the local currency
    cocoon_meta:
      missing_acceptable: Zero cost implies no local currency cost applicable.
  - name: usd_cost
    description: Cost of the ad in US dollars
    tests:
    - not_null
  - name: creative_id
    description: Unique identifier for the ad creative
    tests:
    - not_null
  - name: date_
    description: Date of the ad performance data
    tests:
    - not_null
  - name: impression_count
    description: Number of times the ad was displayed
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"linkedin_campaign_history_data_renamed" AS (
    -- Rename: Renaming columns
    -- id -> campaign_id
    -- last_modified_time -> last_updated_timestamp
    -- created_time -> creation_timestamp
    -- name -> encrypted_campaign_name
    -- version_tag -> campaign_version
    SELECT 
        "id" AS "campaign_id",
        "last_modified_time" AS "last_updated_timestamp",
        "account_id",
        "campaign_group_id",
        "created_time" AS "creation_timestamp",
        "name" AS "encrypted_campaign_name",
        "version_tag" AS "campaign_version"
    FROM "linkedin_campaign_history_data"
),

"linkedin_campaign_history_data_renamed_casted" AS (
    -- Column Type Casting: 
    -- account_id: from INT to VARCHAR
    -- campaign_group_id: from INT to VARCHAR
    -- campaign_id: from INT to VARCHAR
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- last_updated_timestamp: from VARCHAR to TIMESTAMP
    SELECT
        "encrypted_campaign_name",
        "campaign_version",
        CAST("account_id" AS VARCHAR) AS "account_id",
        CAST("campaign_group_id" AS VARCHAR) AS "campaign_group_id",
        CAST("campaign_id" AS VARCHAR) AS "campaign_id",
        CAST("creation_timestamp" AS TIMESTAMP) AS "creation_timestamp",
        CAST("last_updated_timestamp" AS TIMESTAMP) AS "last_updated_timestamp"
    FROM "linkedin_campaign_history_data_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "linkedin_campaign_history_data_renamed_casted"

version: 2
models:
- name: stg_linkedin_campaign_history_data
  description: The table is about LinkedIn campaign history data. It includes details
    such as campaign ID, modification time, account ID, campaign group ID, creation
    time, campaign name (encrypted), and version tag. Each row represents a unique
    campaign with its associated information. The table tracks changes and updates
    to LinkedIn advertising campaigns over time.
  columns:
  - name: encrypted_campaign_name
    description: Encrypted name of the campaign
    tests:
    - not_null
  - name: campaign_version
    description: Version number of the campaign
    tests:
    - not_null
  - name: account_id
    description: Unique identifier for the LinkedIn advertising account
    tests:
    - not_null
  - name: campaign_group_id
    description: ID of the campaign group the campaign belongs to
    tests:
    - not_null
  - name: campaign_id
    description: Unique identifier for the specific campaign
    tests:
    - not_null
  - name: creation_timestamp
    description: Timestamp when the campaign was initially created
    tests:
    - not_null
  - name: last_updated_timestamp
    description: Timestamp of the most recent modification to the campaign
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"linkedin_creative_history_data_renamed" AS (
    -- Rename: Renaming columns
    -- id -> creative_id
    -- last_modified_at -> last_modified_timestamp
    -- created_at -> creation_timestamp
    -- intended_status -> content_status
    -- click_uri -> click_through_url
    SELECT 
        "id" AS "creative_id",
        "last_modified_at" AS "last_modified_timestamp",
        "created_at" AS "creation_timestamp",
        "campaign_id",
        "intended_status" AS "content_status",
        "click_uri" AS "click_through_url"
    FROM "linkedin_creative_history_data"
),

"linkedin_creative_history_data_renamed_casted" AS (
    -- Column Type Casting: 
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- last_modified_timestamp: from VARCHAR to TIMESTAMP
    SELECT
        "creative_id",
        "campaign_id",
        "content_status",
        "click_through_url",
        CAST("creation_timestamp" AS TIMESTAMP) AS "creation_timestamp",
        CAST("last_modified_timestamp" AS TIMESTAMP) AS "last_modified_timestamp"
    FROM "linkedin_creative_history_data_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "linkedin_creative_history_data_renamed_casted"

version: 2
models:
- name: stg_linkedin_creative_history_data
  description: The table is about LinkedIn creative content history. It includes details
    like unique ID, modification and creation timestamps, associated campaign ID,
    status (e.g., ACTIVE, CANCELED, DRAFT), and click URI. Each row represents a specific
    creative content item used in LinkedIn advertising campaigns, tracking its lifecycle
    and current status.
  columns:
  - name: creative_id
    description: Unique identifier for the creative content item
    tests:
    - not_null
  - name: campaign_id
    description: Unique identifier for the associated advertising campaign
    tests:
    - not_null
  - name: content_status
    description: Current status of the creative content
    tests:
    - not_null
    - accepted_values:
        values:
        - ACTIVE
        - PAUSED
        - CANCELED
        - ARCHIVED
        - DRAFT
  - name: click_through_url
    description: URL destination for ad clicks
    cocoon_meta:
      missing_acceptable: Not all campaigns require or have a click-through URL.
  - name: creation_timestamp
    description: Timestamp of when the creative content was created
    tests:
    - not_null
  - name: last_modified_timestamp
    description: Timestamp of the most recent modification
    tests:
    - not_null