%3

Cocoon Semantic Lineage

dbt_linkedin

1. model.linkedin.linkedin_ads__account_report

This SQL query integrates data from three LinkedIn Ads tables: account history, campaign history, and ad analytics by campaign. It filters for the latest versions of account and campaign data, joins these with the analytics data, and then aggregates metrics (clicks, impressions, cost) at the account level. The result is a comprehensive account-level report that includes account details and summarized ad performance metrics.

FilteringIntegrationAggregation

SQL Query

WITH account AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__account_history
  WHERE
    is_latest_version
), campaign AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_history
  WHERE
    is_latest_version
), report AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__ad_analytics_by_campaign
), final AS (
  SELECT
    report.source_relation,
    report.date_day,
    account.account_id,
    account.account_name,
    account.version_tag,
    account.currency,
    account.status,
    account.type,
    account.last_modified_at,
    account.created_at,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.cost) AS cost
  FROM report
  LEFT JOIN campaign
    ON report.campaign_id = campaign.campaign_id
    AND report.source_relation = campaign.source_relation
  LEFT JOIN account
    ON campaign.account_id = account.account_id
    AND campaign.source_relation = account.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
account_name	text	A label for the account.
account_id	integer	Unique internal ID representing the account.
date_day	timestamp without time zone	The day of the activity.
clicks	bigint	The count of chargeable clicks.
impressions	bigint	This is the count of "impressions" for Direct Ads and Sponsored Updates and "sends" for InMails.
cost	numeric	The cost of the ads in the local currency or USD.
last_modified_at	timestamp without time zone	The timestamp of when the version was updated.
created_at	timestamp without time zone	The timestamp of when the account was created.
currency	text	The ISO 4217 currency code of the account, such as 'USD' for United States Dollar (default).
version_tag	numeric	An integer representation of the version of the account.
status	text	ACTIVE - Account is active; this is the default state CANCELLED - Account has been permanently cancelled DRAFT - Account is in draft status, meaning it's not yet fully set up and it is not serving PENDING_DELETION - Denotes that the account has been requested to be deleted that is currently pending REMOVED - Denotes that the account was deleted, but must remain fetchable due to the existence of performance data.
type	text	BUSINESS - This is the only value allowed when creating accounts through the API. ENTERPRISE - This value cannot be used to create accounts through the API and is reserved for accounts created by LinkedIn's internal ad operations systems.

Column Lineage

6. model.linkedin_source.stg_linkedin_ads__account_history

Input Column: 7. model.linkedin_source.stg_linkedin_ads__account_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as part of the source information

8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign

Input Column: 9. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cast as TEXT type in the macro CTE

14. model.linkedin_source.stg_linkedin_ads__campaign_history

Input Column: 15. model.linkedin_source.stg_linkedin_ads__campaign_history_tmp[_dbt_source_relation]
- Cleaning The column is cleaned by casting it to TEXT type
- Direct The column is directly copied to the output

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with campaign table
- Aggregation Used in the GROUP BY clause
Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as a key to join with other tables
- Aggregation The column is included in the GROUP BY clause
Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration This column is used as a key to join the campaign table with the report and account tables
- Direct This column is directly copied to the output

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with campaign table
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[date_day]
- Direct The column is directly copied without any transformation
- Aggregation The column is included in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[clicks]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[impressions]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[cost]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[last_modified_at]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[created_at]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[version_tag]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[status]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[type]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

This SQL query generates a report for LinkedIn ad campaign groups. It joins data from campaign groups, campaigns, accounts, and ad analytics, filtering for the latest versions of historical data. The query then aggregates metrics like clicks, impressions, and cost at the campaign group level, while including various attributes such as account information, campaign group status, and date information.

FilteringIntegrationAggregation

SQL Query

WITH campaign_group AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_group_history
  WHERE
    is_latest_version
), campaign AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_history
  WHERE
    is_latest_version
), account AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__account_history
  WHERE
    is_latest_version
), report AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__ad_analytics_by_campaign
), final AS (
  SELECT
    report.source_relation,
    report.date_day,
    campaign_group.campaign_group_id,
    campaign_group.campaign_group_name,
    account.account_id,
    account.account_name,
    campaign_group.status,
    account.currency,
    campaign_group.is_backfilled,
    campaign_group.run_schedule_start_at,
    campaign_group.run_schedule_end_at,
    campaign_group.last_modified_at,
    campaign_group.created_at,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.cost) AS cost
  FROM report
  LEFT JOIN campaign
    ON report.campaign_id = campaign.campaign_id
    AND report.source_relation = campaign.source_relation
  LEFT JOIN campaign_group
    ON campaign.campaign_group_id = campaign_group.campaign_group_id
    AND campaign.source_relation = campaign_group.source_relation
  LEFT JOIN account
    ON campaign.account_id = account.account_id
    AND campaign.source_relation = account.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
account_name	text	A label for the account.
account_id	integer	Unique internal ID representing the account.
date_day	timestamp without time zone	The day of the activity.
clicks	bigint	The count of chargeable clicks.
impressions	bigint	This is the count of "impressions" for Direct Ads and Sponsored Updates and "sends" for InMails.
cost	numeric	The cost of the ads in the local currency or USD.
campaign_group_name	text	A label for the campaign group.
campaign_group_id	integer	Unique internal ID representing the campaign group.
last_modified_at	timestamp without time zone	The timestamp of when the version was updated.
created_at	timestamp without time zone	The timestamp of when the campaign group was created.
is_backfilled	boolean	Flag that denotes whether the campaign group was created organically or to backfill existing campaigns. This is a read-only field set by the system. Default = false.
run_schedule_start_at	timestamp without time zone	Represents the inclusive (greater than or equal to) date when to start running the associated campaigns under this campaign group. This field is required.
run_schedule_end_at	timestamp without time zone	Represents the exclusive (strictly less than) date when to stop running the associated campaigns under this campaign group. If this field is unset, it indicates an open range with no end date.
status	text	Status of campaign group. Possible values are: ACTIVE - Denotes that the campaign group is capable of serving ads, subject to run date and budget limitations (as well as any other limitations at the account or campaign level). ARCHIVED - Denotes that the campaign group is presently inactive, and should mostly be hidden in the UI until un-archived. CANCELLED - Denotes that the campaign group has been permanently canceled and cannot be reactivated. Not a settable status. DRAFT - Denotes that the campaign group is in a preliminary state and should temporarily not be served. PAUSED - Denotes that the campaign group meets all requirements to be served, but temporarily should not be. PENDING_DELETION - Denotes that the campaign group has been requested to be deleted that is currently pending. REMOVED - Denoted that the campaign group was deleted, but must remain fetchable due to the existence of performance data.
currency	text	The ISO 4217 currency code of the account, such as 'USD' for United States Dollar (default).

Column Lineage

6. model.linkedin_source.stg_linkedin_ads__account_history

Input Column: 7. model.linkedin_source.stg_linkedin_ads__account_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as part of the source information

8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign

Input Column: 9. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cast as TEXT type in the macro CTE

12. model.linkedin_source.stg_linkedin_ads__campaign_group_history

Input Column: 13. model.linkedin_source.stg_linkedin_ads__campaign_group_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cleaned by casting it as TEXT

14. model.linkedin_source.stg_linkedin_ads__campaign_history

Input Column: 15. model.linkedin_source.stg_linkedin_ads__campaign_history_tmp[_dbt_source_relation]
- Cleaning The column is cleaned by casting it to TEXT type
- Direct The column is directly copied to the output

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause
Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct directly copied to the output

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Integration used as the key to join with other tables
- Direct directly copied to the output

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[date_day]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[clicks]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[impressions]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[cost]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_name]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_id]
- Integration used as part of the join condition with the campaign table
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[last_modified_at]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[created_at]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[is_backfilled]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[run_schedule_start_at]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause
Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[run_schedule_start_at]
- Integration This column is used to join the campaign table with the campaign_group table

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[run_schedule_end_at]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[status]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

This SQL query integrates data from multiple LinkedIn Ads-related tables (campaign, campaign group, account, and ad analytics) to create a comprehensive campaign report. It joins these tables based on common identifiers and source relations, aggregates metrics like clicks, impressions, and cost, and includes various campaign and account details. The query filters for the latest versions of historical data and groups the results by multiple dimensions.

FilteringIntegrationAggregation

SQL Query

WITH campaign AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_history
  WHERE
    is_latest_version
), campaign_group AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_group_history
  WHERE
    is_latest_version
), account AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__account_history
  WHERE
    is_latest_version
), report AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__ad_analytics_by_campaign
), final AS (
  SELECT
    report.source_relation,
    report.date_day,
    report.campaign_id,
    campaign.campaign_name,
    campaign.version_tag,
    campaign_group.campaign_group_id,
    campaign_group.campaign_group_name,
    account.account_id,
    account.account_name,
    campaign.status AS campaign_status,
    campaign_group.status AS campaign_group_status,
    campaign.type,
    campaign.cost_type,
    campaign.creative_selection,
    campaign.daily_budget_amount,
    campaign.daily_budget_currency_code,
    campaign.unit_cost_amount,
    campaign.unit_cost_currency_code,
    account.currency,
    campaign.format,
    campaign.locale_country,
    campaign.locale_language,
    campaign.objective_type,
    campaign.optimization_target_type,
    campaign.is_audience_expansion_enabled,
    campaign.is_offsite_delivery_enabled,
    campaign.run_schedule_start_at,
    campaign.run_schedule_end_at,
    campaign.last_modified_at,
    campaign.created_at,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.cost) AS cost
  FROM report
  LEFT JOIN campaign
    ON report.campaign_id = campaign.campaign_id
    AND report.source_relation = campaign.source_relation
  LEFT JOIN campaign_group
    ON campaign.campaign_group_id = campaign_group.campaign_group_id
    AND campaign.source_relation = campaign_group.source_relation
  LEFT JOIN account
    ON campaign.account_id = account.account_id
    AND campaign.source_relation = account.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16,
    17,
    18,
    19,
    20,
    21,
    22,
    23,
    24,
    25,
    26,
    27,
    28,
    29,
    30
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
account_name	text	A label for the account.
account_id	integer	Unique internal ID representing the account.
date_day	timestamp without time zone	The day of the activity.
clicks	bigint	The count of chargeable clicks.
impressions	bigint	This is the count of "impressions" for Direct Ads and Sponsored Updates and "sends" for InMails.
cost	numeric	The cost of the ads in the local currency or USD.
campaign_group_name	text	A label for the campaign group.
campaign_group_id	integer	Unique internal ID representing the campaign group.
campaign_name	text	A label for the campaign.
campaign_id	integer	Unique internal ID representing the campaign.
campaign_group_status	text	Status of campaign's campaign group. Possible values are: ACTIVE - Denotes that the campaign group is capable of serving ads, subject to run date and budget limitations (as well as any other limitations at the account or campaign level). ARCHIVED - Denotes that the campaign group is presently inactive, and should mostly be hidden in the UI until un-archived. CANCELLED - Denotes that the campaign group has been permanently canceled and cannot be reactivated. Not a settable status. DRAFT - Denotes that the campaign group is in a preliminary state and should temporarily not be served. PAUSED - Denotes that the campaign group meets all requirements to be served, but temporarily should not be. PENDING_DELETION - Denotes that the campaign group has been requested to be deleted that is currently pending. REMOVED - Denoted that the campaign group was deleted, but must remain fetchable due to the existence of performance data.
last_modified_at	timestamp without time zone	The timestamp of when the version was updated.
created_at	timestamp without time zone	The timestamp of when the campaign was created.
version_tag	numeric	An integer representation of the version of the campaign.
is_audience_expansion_enabled	boolean	Boolean (default=false) representing whether Audience Expansion is enabled for the campaign provides query expansion for certain targeting criteria.
cost_type	text	CPM - Cost per thousand advertising impressions. If type=SPONSORED_INMAILS; cost per send(CPS) is measured as CPM x 1000. CPC- Cost per individual click on the associated link. CPV- Cost per view for video ads.
creative_selection	text	ROUND_ROBIN - Rotate through available creatives to serve them as evenly as possible. OPTIMIZED (default) - Bias selection taking into account such as expected performance. Not available for Message and Conversation Ads (type=SPONSORED_INMAILS).
daily_budget_amount	double precision	Maximum amount to spend per day UTC. The amount of money as a real number string.
daily_budget_currency_code	text	ISO currency code. The currency must match that of the parent account.
format	text	The ad format on the campaign level. See https://docs.microsoft.com/en-us/linkedin/marketing/integrations/ads/campaign-formats?view=li-lms-2022-07
locale_country	text	Locale of the campaign. An uppercase two-letter country code as defined by ISO-3166.
locale_language	text	Locale of the campaign. A lowercase two-letter language code as defined by ISO-639.
objective_type	text	Campaign Objective type values. Click [here](https://docs.microsoft.com/en-us/linkedin/marketing/integrations/ads-reporting/ad-budget-pricing-type-combinations?view=li-lms-2022-07) for Campaign Objective descriptions. One of: - BRAND_AWARENESS - ENGAGEMENT - JOB_APPLICANTS - LEAD_GENERATION - WEBSITE_CONVERSIONS - WEBSITE_VISITS - VIDEO_VIEWS
is_offsite_delivery_enabled	boolean	Boolean indicating if this campaign to allowed to be served on the LinkedIn Audience Network to extend the reach of your campaign by delivering ads beyond the LinkedIn feed to members on third-party apps and sites. (default will be "True" from June 30, 2022)
optimization_target_type	text	Determines how this campaign is optimized for spending. If this is not set, there is no optimization. Refer to the documentation here. Default = 'NONE'.
run_schedule_start_at	timestamp without time zone	Scheduled date range to run associated creatives. The start date must be non-null. Represents the inclusive (greater than or equal to) value in which to start the range.
run_schedule_end_at	timestamp without time zone	Scheduled date range to run associated creatives. The start date must be non-null. Represents the exclusive (strictly less than) value in which to end the range, if provided.
campaign_status	text	ACTIVE - Denotes that the campaign is fully servable. PAUSED - Denotes that the campaign meets all requirements to be served, but temporarily should not be. ARCHIVED - Denotes that the campaign is presently inactive, and should mostly be hidden in the UI until un-archived. COMPLETED - Denotes that the campaign has reached a specified budgetary or chronological limit. CANCELED - Denotes that the campaign has been permanently canceled, such as when an advertising account is permanently closed. DRAFT - Denotes that the campaign is still being edited and not eligible for serving. Some validation will be postponed until the campaign is activated. PENDING_DELETION - Denotes that the campaign has been requested to be deleted that is currently pending. REMOVED - Denotes that the campaign was deleted, but must remain fetchable due to the existence of performance data.
type	text	TEXT_AD - Text-based ads that show up in the right column or top of the page on LinkedIn. SPONSORED_UPDATES - Native ads that promote a company's content updates in the LinkedIn feed. SPONSORED_INMAILS - Personalized messages with a call-to-action button delivered to a LinkedIn's member inbox. DYNAMIC - Ads that are dynamically personalized.
unit_cost_amount	double precision	This value is used as one of the following: amount to bid (for manual bidding), amount which is the target cost (for target cost bidding) per click, impression, or other event depending on the pricing model, or cost cap (for cost cap bidding). The amount of money as a real number string. The amount should be non-negative if the bidding strategy is manual, target cost, or cost cap bidding. The default is 0 with the currency code set to match that of the associated account.
unit_cost_currency_code	text	Amount to bid per click, impression, or other event depending on the pricing model. The default is 0 with the currency code set to match that of the associated account. ISO currency code.
currency	text	The ISO 4217 currency code of the account, such as 'USD' for United States Dollar (default).

Column Lineage

6. model.linkedin_source.stg_linkedin_ads__account_history

Input Column: 7. model.linkedin_source.stg_linkedin_ads__account_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as part of the source information

8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign

Input Column: 9. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cast as TEXT type in the macro CTE

12. model.linkedin_source.stg_linkedin_ads__campaign_group_history

Input Column: 13. model.linkedin_source.stg_linkedin_ads__campaign_group_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cleaned by casting it as TEXT

14. model.linkedin_source.stg_linkedin_ads__campaign_history

Input Column: 15. model.linkedin_source.stg_linkedin_ads__campaign_history_tmp[_dbt_source_relation]
- Cleaning The column is cleaned by casting it to TEXT type
- Direct The column is directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct copied directly to the output
Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as a key to join with other tables
- Aggregation The column is grouped by in the final SELECT
Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct copied directly to the output
Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct copied directly to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Integration used as the key to join with other tables
- Direct copied directly to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[date_day]
- Direct The column is directly copied without any transformation
- Aggregation The column is grouped by in the final SELECT

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[clicks]
- Aggregation The column is summed up in the final SELECT

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[impressions]
- Aggregation The column is summed up in the final SELECT

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[cost]
- Aggregation The column is summed up in the final SELECT

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_name]
- Direct copied directly to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_id]
- Integration used as the key to join with the campaign table
- Direct copied directly to the output
Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_group_id]
- Integration used as the key to join with the campaign_group table

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_name]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[campaign_id]
- Direct The column is directly copied without any transformation
- Integration The column is used as a key to join with the campaign table
- Aggregation The column is grouped by in the final SELECT
Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[status]
- Direct copied directly to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[last_modified_at]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[created_at]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[version_tag]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_audience_expansion_enabled]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[cost_type]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[creative_selection]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[daily_budget_amount]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[daily_budget_currency_code]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[format]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[locale_country]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[locale_language]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[objective_type]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_offsite_delivery_enabled]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[optimization_target_type]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[run_schedule_start_at]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[run_schedule_end_at]
- Direct This column is directly copied to the output without transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[status]
- Direct This column is directly copied to the output
- Filtering Used in the WHERE clause to filter for the latest version

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[type]
- Direct This column is directly copied to the output without transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[unit_cost_amount]
- Direct This column is directly copied to the output without transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[unit_cost_currency_code]
- Direct This column is directly copied to the output without transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

This SQL query integrates data from multiple LinkedIn Ads-related tables, including creative history, campaign history, campaign group history, account history, and ad analytics. It joins these tables based on various IDs and source relations, filtering for the latest versions of historical data. The query then aggregates metrics such as clicks, impressions, and cost, grouping by various dimensions like creative ID, campaign ID, account ID, and date. The result is a comprehensive report that combines creative performance data with associated campaign, campaign group, and account information.

FilteringIntegrationAggregation

SQL Query

WITH creative AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__creative_history
  WHERE
    is_latest_version
), campaign AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_history
  WHERE
    is_latest_version
), campaign_group AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_group_history
  WHERE
    is_latest_version
), account AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__account_history
  WHERE
    is_latest_version
), report AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__ad_analytics_by_creative
), final AS (
  SELECT
    report.source_relation,
    report.date_day,
    report.creative_id,
    campaign.campaign_id,
    campaign.campaign_name,
    campaign_group.campaign_group_id,
    campaign_group.campaign_group_name,
    account.account_id,
    account.account_name,
    creative.click_uri,
    creative.status AS creative_status,
    campaign.status AS campaign_status,
    campaign_group.status AS campaign_group_status,
    account.currency,
    creative.last_modified_at,
    creative.created_at,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.cost) AS cost
  FROM report
  LEFT JOIN creative
    ON report.creative_id = creative.creative_id
    AND report.source_relation = creative.source_relation
  LEFT JOIN campaign
    ON creative.campaign_id = campaign.campaign_id
    AND creative.source_relation = campaign.source_relation
  LEFT JOIN campaign_group
    ON campaign.campaign_group_id = campaign_group.campaign_group_id
    AND campaign.source_relation = campaign_group.source_relation
  LEFT JOIN account
    ON campaign.account_id = account.account_id
    AND campaign.source_relation = account.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
account_name	text	A label for the account.
account_id	integer	Unique internal ID representing the account.
date_day	timestamp without time zone	The day of the activity.
clicks	bigint	The count of chargeable clicks.
impressions	bigint	This is the count of "impressions" for Direct Ads and Sponsored Updates and "sends" for InMails.
cost	numeric	The cost of the ads in the local currency or USD.
campaign_group_name	text	A label for the campaign group.
campaign_group_id	integer	Unique internal ID representing the campaign group.
campaign_name	text	A label for the creative's campaign.
campaign_id	integer	Unique internal ID representing the creative's campaign.
creative_id	integer	Unique internal ID representing the creative.
last_modified_at	timestamp without time zone	The timestamp of when the version was updated.
created_at	timestamp without time zone	The timestamp of when the creative was created.
creative_status	text	ACTIVE - Creative creation is complete and creative is available for review and serve. PAUSED - Creative creation is complete and creative is current, but should temporarily not be served. This status is not valid upon creation. DRAFT - Creative creation is incomplete and may still be edited. ARCHIVED - Creative creation is complete, but creative should not be served and should be separated from non-archived creatives in any UI. CANCELED - Creative is permanently canceled.
click_uri	text	Required when creative type is TEXT_AD. Maximum character limit of 500 after URL encoding all special characters. Limit does not include the "https://" prefix.
campaign_group_status	text	Status of campaign's campaign group. Possible values are: ACTIVE - Denotes that the campaign group is capable of serving ads, subject to run date and budget limitations (as well as any other limitations at the account or campaign level). ARCHIVED - Denotes that the campaign group is presently inactive, and should mostly be hidden in the UI until un-archived. CANCELLED - Denotes that the campaign group has been permanently canceled and cannot be reactivated. Not a settable status. DRAFT - Denotes that the campaign group is in a preliminary state and should temporarily not be served. PAUSED - Denotes that the campaign group meets all requirements to be served, but temporarily should not be. PENDING_DELETION - Denotes that the campaign group has been requested to be deleted that is currently pending. REMOVED - Denoted that the campaign group was deleted, but must remain fetchable due to the existence of performance data.
campaign_status	text	ACTIVE - Denotes that the campaign is fully servable. PAUSED - Denotes that the campaign meets all requirements to be served, but temporarily should not be. ARCHIVED - Denotes that the campaign is presently inactive, and should mostly be hidden in the UI until un-archived. COMPLETED - Denotes that the campaign has reached a specified budgetary or chronological limit. CANCELED - Denotes that the campaign has been permanently canceled, such as when an advertising account is permanently closed. DRAFT - Denotes that the campaign is still being edited and not eligible for serving. Some validation will be postponed until the campaign is activated. PENDING_DELETION - Denotes that the campaign has been requested to be deleted that is currently pending. REMOVED - Denotes that the campaign was deleted, but must remain fetchable due to the existence of performance data.
currency	text	The ISO 4217 currency code of the account, such as 'USD' for United States Dollar (default).

Column Lineage

6. model.linkedin_source.stg_linkedin_ads__account_history

Input Column: 7. model.linkedin_source.stg_linkedin_ads__account_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as part of the source information

10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative

Input Column: 11. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is renamed to 'source_relation'

12. model.linkedin_source.stg_linkedin_ads__campaign_group_history

Input Column: 13. model.linkedin_source.stg_linkedin_ads__campaign_group_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cleaned by casting it as TEXT

14. model.linkedin_source.stg_linkedin_ads__campaign_history

Input Column: 15. model.linkedin_source.stg_linkedin_ads__campaign_history_tmp[_dbt_source_relation]
- Cleaning The column is cleaned by casting it to TEXT type
- Direct The column is directly copied to the output

16. model.linkedin_source.stg_linkedin_ads__creative_history

Input Column: 17. model.linkedin_source.stg_linkedin_ads__creative_history_tmp[_dbt_source_relation]
- Direct The column is directly copied to the 'source_relation' output column.
- Cleaning The column is cleaned by being cast as TEXT and coalesced with an empty string.

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause
Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as a key to join with other tables
- Direct directly copied to the output
Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[source_relation]
- Integration Used as part of the join condition with other tables
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Integration used as the join key with other tables
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[date_day]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[clicks]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[impressions]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[cost]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_name]
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_id]
- Integration used as a key to join with the campaign table
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_name]
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_id]
- Integration used as join key with creative table
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[creative_id]
- Direct The column is directly copied without any transformation
- Integration Used as a key to join with the creative table
- Aggregation Used in the GROUP BY clause
Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[creative_id]
- Integration Used as the key to join with the report table
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[last_modified_at]
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[created_at]
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[status]
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[click_uri]
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[status]
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[status]
- Direct The status is directly copied to the output
- Filtering Only the latest version of the campaign is used (is_latest_version = true)

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

This query integrates data from multiple LinkedIn Ads-related tables (creative, campaign, campaign group, account, and ad analytics) to create a comprehensive URL report. It joins these tables based on various ID fields and source relations, filters out records with null click URIs, and aggregates metrics such as clicks, impressions, and cost. The result is a detailed report that includes URL-related information, campaign details, and performance metrics grouped by various dimensions.

IntegrationFilteringAggregation

SQL Query

WITH creative AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__creative_history
  WHERE
    is_latest_version
), campaign AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_history
  WHERE
    is_latest_version
), campaign_group AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_group_history
  WHERE
    is_latest_version
), account AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__account_history
  WHERE
    is_latest_version
), report AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__ad_analytics_by_creative
), final AS (
  SELECT
    report.source_relation,
    report.date_day,
    creative.click_uri,
    creative.base_url,
    creative.url_host,
    creative.url_path,
    creative.utm_source,
    creative.utm_medium,
    creative.utm_campaign,
    creative.utm_content,
    creative.utm_term,
    report.creative_id,
    campaign.campaign_id,
    campaign.campaign_name,
    campaign_group.campaign_group_id,
    campaign_group.campaign_group_name,
    account.account_id,
    account.account_name,
    account.currency,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.cost) AS cost
  FROM report
  LEFT JOIN creative
    ON report.creative_id = creative.creative_id
    AND report.source_relation = creative.source_relation
  LEFT JOIN campaign
    ON creative.campaign_id = campaign.campaign_id
    AND creative.source_relation = campaign.source_relation
  LEFT JOIN campaign_group
    ON campaign.campaign_group_id = campaign_group.campaign_group_id
    AND campaign.source_relation = campaign_group.source_relation
  LEFT JOIN account
    ON campaign.account_id = account.account_id
    AND campaign.source_relation = account.source_relation
  WHERE
    NOT creative.click_uri IS NULL
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16,
    17,
    18,
    19
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
account_name	text	A label for the account.
account_id	integer	Unique internal ID representing the account.
date_day	timestamp without time zone	The day of the activity.
clicks	bigint	The count of chargeable clicks.
impressions	bigint	This is the count of "impressions" for Direct Ads and Sponsored Updates and "sends" for InMails.
cost	numeric	The cost of the ads in the local currency or USD.
campaign_group_name	text	A label for the campaign group.
campaign_group_id	integer	Unique internal ID representing the campaign group.
creative_id	integer	Unique internal ID representing the creative.
click_uri	text	Required when creative type is TEXT_AD. Maximum character limit of 500 after URL encoding all special characters. Limit does not include the "https://" prefix.
base_url	text	The base URL of the ad, extracted from the `click_uri`.
url_host	text	The URL host of the ad, extracted from the `click_uri`.
url_path	text	The URL path of the ad, extracted from the `click_uri`.
utm_source	text	The utm_source parameter of the ad, extracted from the `click_uri`.
utm_medium	text	The utm_medium parameter of the ad, extracted from the `click_uri`.
utm_campaign	text	The utm_campaign parameter of the ad, extracted from the `click_uri`.
utm_content	text	The utm_content parameter of the ad, extracted from the `click_uri`.
utm_term	text	The utm_term parameter of the ad, extracted from the `click_uri`.
currency	text	The ISO 4217 currency code of the account, such as 'USD' for United States Dollar (default).
campaign_name	text	A label for the creative's campaign.
campaign_id	integer	Unique internal ID representing the creative's campaign.

Column Lineage

6. model.linkedin_source.stg_linkedin_ads__account_history

Input Column: 7. model.linkedin_source.stg_linkedin_ads__account_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as part of the source information

10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative

Input Column: 11. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is renamed to 'source_relation'

12. model.linkedin_source.stg_linkedin_ads__campaign_group_history

Input Column: 13. model.linkedin_source.stg_linkedin_ads__campaign_group_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cleaned by casting it as TEXT

14. model.linkedin_source.stg_linkedin_ads__campaign_history

Input Column: 15. model.linkedin_source.stg_linkedin_ads__campaign_history_tmp[_dbt_source_relation]
- Cleaning The column is cleaned by casting it to TEXT type
- Direct The column is directly copied to the output

16. model.linkedin_source.stg_linkedin_ads__creative_history

Input Column: 17. model.linkedin_source.stg_linkedin_ads__creative_history_tmp[_dbt_source_relation]
- Direct The column is directly copied to the 'source_relation' output column.
- Cleaning The column is cleaned by being cast as TEXT and coalesced with an empty string.

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause
Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Integration used as the key to join with other tables
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[date_day]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[clicks]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[impressions]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[cost]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_name]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_id]
- Integration used as the key to join with the campaign table
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[creative_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the creative table
- Aggregation Used in the GROUP BY clause
Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[creative_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[click_uri]
- Filtering used in the WHERE clause to filter out null values
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[base_url]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[url_host]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[url_path]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_source]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_medium]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_campaign]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_content]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_term]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_name]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_id]
- Integration used as the key to join with the creative table
- Direct directly copied to the output

6. model.linkedin_source.stg_linkedin_ads__account_history

This SQL query performs several operations on LinkedIn ads account history data. It starts by creating a base CTE from a temporary staging table. Then, it defines a macro CTE with null or empty values for all columns. The fields CTE then selects and casts various columns from the macro CTE, renames some columns, and adds an is_latest_version column using a window function. Finally, it selects all columns from the fields CTE.

CleaningDeduplicationOther

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__account_history_tmp
), macro AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS created_time,
    CAST(NULL AS TEXT) AS currency,
    CAST(NULL AS INT) AS id,
    CAST(NULL AS TIMESTAMP) AS last_modified_time,
    CAST(NULL AS TEXT) AS name,
    CAST(NULL AS TEXT) AS status,
    CAST(NULL AS TEXT) AS type,
    CAST(NULL AS TEXT) AS version_tag,
    CAST('' AS TEXT) AS source_relation
  FROM base
), fields AS (
  SELECT
    source_relation,
    id AS account_id,
    name AS account_name,
    currency,
    CAST(version_tag AS DECIMAL) AS version_tag,
    status,
    type,
    CAST(last_modified_time AS TIMESTAMP) AS last_modified_at,
    CAST(created_time AS TIMESTAMP) AS created_at,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY last_modified_time DESC) = 1 AS is_latest_version
  FROM macro
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
account_id	integer	Unique internal ID representing the account.
last_modified_at	timestamp without time zone	The timestamp of when the version was updated.
created_at	timestamp without time zone	The timestamp of when the account was created.
account_name	text	A label for the account.
currency	text	The ISO 4217 currency code of the account, such as 'USD' for United States Dollar (default).
version_tag	numeric	An integer representation of the version of the account.
status	text	ACTIVE - Account is active; this is the default state CANCELLED - Account has been permanently cancelled DRAFT - Account is in draft status, meaning it's not yet fully set up and it is not serving PENDING_DELETION - Denotes that the account has been requested to be deleted that is currently pending REMOVED - Denotes that the account was deleted, but must remain fetchable due to the existence of performance data.
type	text	BUSINESS - This is the only value allowed when creating accounts through the API. ENTERPRISE - This value cannot be used to create accounts through the API and is reserved for accounts created by LinkedIn's internal ad operations systems.
is_latest_version	boolean	Boolean of whether the record is the latest version of the account.

Column Lineage

6. model.linkedin_source.stg_linkedin_ads__account_history

Input Column: 7. model.linkedin_source.stg_linkedin_ads__account_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as part of the source information

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with campaign table
- Aggregation Used in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with campaign table
- Aggregation Used in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Integration used as the key to join with other tables
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Integration used as the key to join with other tables
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Integration used as the join key with other tables
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_id]
- Integration used as the key to join with other tables
- Direct directly copied to the output

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[last_modified_at]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[created_at]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[account_name]
- Direct directly copied to the output

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[currency]
- Direct directly copied to the output

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[version_tag]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[status]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[type]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[is_latest_version]
- Filtering The column is used to filter for the latest version of account data

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[is_latest_version]
- Filtering used to filter for the latest version of the account

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[is_latest_version]
- Filtering used to filter for the latest version of account data

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[is_latest_version]
- Filtering used to filter for the latest version of account data

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[is_latest_version]
- Filtering used to filter for the latest version of the account

7. model.linkedin_source.stg_linkedin_ads__account_history_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type, set to NULL. The query is likely used as a placeholder or template for further development, or to initialize a temporary table structure without populating it with data.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment
_dbt_source_relation	text	None

Column Lineage

6. model.linkedin_source.stg_linkedin_ads__account_history

Input Column: 7. model.linkedin_source.stg_linkedin_ads__account_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as part of the source information

1. model.linkedin.linkedin_ads__account_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with campaign table
- Aggregation Used in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 6. model.linkedin_source.stg_linkedin_ads__account_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign

This SQL query appears to be a staging transformation for LinkedIn ads analytics data. It starts with a base CTE that selects all columns from a temporary table. Then, it defines a macro CTE that casts NULL values to specific data types for each column. Finally, it selects and renames specific columns from the macro CTE, including truncating the 'day' column to create a 'date_day' column. The query doesn't perform any filtering, deduplication, or aggregation.

CleaningFeaturization

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__ad_analytics_by_campaign_tmp
), macro AS (
  SELECT
    CAST(NULL AS INT) AS campaign_id,
    CAST(NULL AS INT) AS clicks,
    CAST(NULL AS DECIMAL(28, 6)) AS cost_in_local_currency,
    CAST(NULL AS DECIMAL(28, 6)) AS cost_in_usd,
    CAST(NULL AS TIMESTAMP) AS day,
    CAST(NULL AS INT) AS impressions,
    CAST('' AS TEXT) AS source_relation
  FROM base
), fields AS (
  SELECT
    source_relation,
    DATE_TRUNC('DAY', day) AS date_day,
    campaign_id,
    clicks,
    impressions,
    cost_in_usd AS cost
  FROM macro
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
campaign_id	integer	The ID of the related creative.
date_day	timestamp without time zone	The day of the activity.
clicks	integer	The count of chargeable clicks.
impressions	integer	This is the count of "impressions" for Direct Ads and Sponsored Updates and "sends" for InMails.
cost	numeric(28,6)	The cost of the ads in the local currency or USD.

Column Lineage

8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign

Input Column: 9. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cast as TEXT type in the macro CTE

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as a key to join with other tables
- Aggregation The column is included in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as a key to join with other tables
- Aggregation The column is grouped by in the final SELECT

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[campaign_id]
- Integration The column is used as a key to join with the campaign table

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[campaign_id]
- Integration Used as the key to join with the campaign table

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[campaign_id]
- Direct The column is directly copied without any transformation
- Integration The column is used as a key to join with the campaign table
- Aggregation The column is grouped by in the final SELECT

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[date_day]
- Direct The column is directly copied without any transformation
- Aggregation The column is included in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[date_day]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[date_day]
- Direct The column is directly copied without any transformation
- Aggregation The column is grouped by in the final SELECT

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[clicks]
- Aggregation The column is summed up in the final SELECT statement

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[clicks]
- Aggregation The column is summed up in the final SELECT statement

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[clicks]
- Aggregation The column is summed up in the final SELECT

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[impressions]
- Aggregation The column is summed up in the final SELECT statement

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[impressions]
- Aggregation The column is summed up in the final SELECT statement

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[impressions]
- Aggregation The column is summed up in the final SELECT

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[cost]
- Aggregation The column is summed up in the final SELECT statement

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[cost]
- Aggregation The column is summed up in the final SELECT statement

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[cost]
- Aggregation The column is summed up in the final SELECT

9. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of type TEXT, which is set to NULL. The LIMIT 0 clause ensures no rows are returned. This appears to be a template or placeholder query, possibly used for schema definition or testing purposes in a dbt (data build tool) project.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment
_dbt_source_relation	text	None

Column Lineage

8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign

Input Column: 9. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cast as TEXT type in the macro CTE

1. model.linkedin.linkedin_ads__account_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as a key to join with other tables
- Aggregation The column is included in the GROUP BY clause

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 8. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_campaign[source_relation]
- Direct The column is directly copied without any transformation
- Integration The column is used as a key to join with other tables
- Aggregation The column is grouped by in the final SELECT

10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative

This SQL query creates a staging table for LinkedIn ad analytics data. It starts with a base table, then defines a macro with null or empty values for each column to establish the structure. Finally, it selects and renames specific columns, truncating the day to remove time information. The query primarily focuses on data structure and basic cleaning.

CleaningFeaturization

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__ad_analytics_by_creative_tmp
), macro AS (
  SELECT
    CAST(NULL AS INT) AS clicks,
    CAST(NULL AS DECIMAL(28, 6)) AS cost_in_local_currency,
    CAST(NULL AS DECIMAL(28, 6)) AS cost_in_usd,
    CAST(NULL AS INT) AS creative_id,
    CAST(NULL AS TIMESTAMP) AS day,
    CAST(NULL AS INT) AS impressions,
    CAST('' AS TEXT) AS source_relation
  FROM base
), fields AS (
  SELECT
    source_relation,
    DATE_TRUNC('DAY', day) AS date_day,
    creative_id,
    clicks,
    impressions,
    cost_in_usd AS cost
  FROM macro
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
creative_id	integer	The ID of the related creative.
date_day	timestamp without time zone	The day of the activity.
clicks	integer	The count of chargeable clicks.
impressions	integer	This is the count of "impressions" for Direct Ads and Sponsored Updates and "sends" for InMails.
cost	numeric(28,6)	The cost of the ads in the local currency or USD.

Column Lineage

10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative

Input Column: 11. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is renamed to 'source_relation'

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[creative_id]
- Direct The column is directly copied without any transformation
- Integration Used as a key to join with the creative table
- Aggregation Used in the GROUP BY clause

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[creative_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the creative table
- Aggregation Used in the GROUP BY clause

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[date_day]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[date_day]
- Direct The column is directly copied without any transformation
- Aggregation Used in the GROUP BY clause

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[clicks]
- Aggregation The column is summed up in the SELECT statement

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[clicks]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[impressions]
- Aggregation The column is summed up in the SELECT statement

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[impressions]
- Aggregation The column is summed up in the final SELECT statement

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[cost]
- Aggregation The column is summed up in the SELECT statement

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[cost]
- Aggregation The column is summed up in the final SELECT statement

11. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of type TEXT, which is set to NULL. The LIMIT 0 clause ensures that no rows are returned. This appears to be a template or placeholder query, possibly used for schema definition or testing purposes in a dbt (data build tool) project.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment
_dbt_source_relation	text	None

Column Lineage

10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative

Input Column: 11. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is renamed to 'source_relation'

4. model.linkedin.linkedin_ads__creative_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause

5. model.linkedin.linkedin_ads__url_report

Input Column: 10. model.linkedin_source.stg_linkedin_ads__ad_analytics_by_creative[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Used in the GROUP BY clause

12. model.linkedin_source.stg_linkedin_ads__campaign_group_history

This SQL query stages data from a LinkedIn Ads campaign group history source. It casts and renames columns, converts timestamp fields, adds a flag for the latest version of each campaign group, and prepares the data for further processing or analysis.

CleaningDeduplicationFeaturization

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_group_history_tmp
), macro AS (
  SELECT
    CAST(NULL AS INT) AS account_id,
    CAST(NULL AS BOOLEAN) AS backfilled,
    CAST(NULL AS TIMESTAMP) AS created_time,
    CAST(NULL AS INT) AS id,
    CAST(NULL AS TIMESTAMP) AS last_modified_time,
    CAST(NULL AS TEXT) AS name,
    CAST(NULL AS TIMESTAMP) AS run_schedule_end,
    CAST(NULL AS TIMESTAMP) AS run_schedule_start,
    CAST(NULL AS TEXT) AS status,
    CAST('' AS TEXT) AS source_relation
  FROM base
), fields AS (
  SELECT
    source_relation,
    id AS campaign_group_id,
    name AS campaign_group_name,
    account_id,
    status,
    backfilled AS is_backfilled,
    CAST(run_schedule_start AS TIMESTAMP) AS run_schedule_start_at,
    CAST(run_schedule_end AS TIMESTAMP) AS run_schedule_end_at,
    CAST(last_modified_time AS TIMESTAMP) AS last_modified_at,
    CAST(created_time AS TIMESTAMP) AS created_at,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY last_modified_time DESC) = 1 AS is_latest_version
  FROM macro
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
campaign_group_id	integer	Unique internal ID representing the campaign group.
last_modified_at	timestamp without time zone	The timestamp of when the version was updated.
account_id	integer	The ID of the account the campaign group belongs to.
created_at	timestamp without time zone	The timestamp of when the campaign group was created.
campaign_group_name	text	A label for the campaign group.
is_backfilled	boolean	Flag that denotes whether the campaign group was created organically or to backfill existing campaigns. This is a read-only field set by the system. Default = false.
run_schedule_start_at	timestamp without time zone	Represents the inclusive (greater than or equal to) date when to start running the associated campaigns under this campaign group. This field is required.
run_schedule_end_at	timestamp without time zone	Represents the exclusive (strictly less than) date when to stop running the associated campaigns under this campaign group. If this field is unset, it indicates an open range with no end date.
status	text	Status of campaign group. Possible values are: ACTIVE - Denotes that the campaign group is capable of serving ads, subject to run date and budget limitations (as well as any other limitations at the account or campaign level). ARCHIVED - Denotes that the campaign group is presently inactive, and should mostly be hidden in the UI until un-archived. CANCELLED - Denotes that the campaign group has been permanently canceled and cannot be reactivated. Not a settable status. DRAFT - Denotes that the campaign group is in a preliminary state and should temporarily not be served. PAUSED - Denotes that the campaign group meets all requirements to be served, but temporarily should not be. PENDING_DELETION - Denotes that the campaign group has been requested to be deleted that is currently pending. REMOVED - Denoted that the campaign group was deleted, but must remain fetchable due to the existence of performance data.
is_latest_version	boolean	Boolean of whether the record is the latest version of the campaign group.

Column Lineage

12. model.linkedin_source.stg_linkedin_ads__campaign_group_history

Input Column: 13. model.linkedin_source.stg_linkedin_ads__campaign_group_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cleaned by casting it as TEXT

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as a key to join with other tables
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_id]
- Integration used as part of the join condition with the campaign table
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_id]
- Integration used as the key to join with the campaign table
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_id]
- Integration used as a key to join with the campaign table
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_id]
- Integration used as the key to join with the campaign table
- Direct directly copied to the output

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[last_modified_at]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[created_at]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_name]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_name]
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_name]
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[campaign_group_name]
- Direct directly copied to the output

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[is_backfilled]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[run_schedule_start_at]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[run_schedule_end_at]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[status]
- Direct directly copied to the output
- Aggregation used in the GROUP BY clause

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[status]
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[status]
- Direct directly copied to the output

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[is_latest_version]
- Filtering This column is used to filter the records in the campaign_group CTE to only include the latest version of each campaign group.

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[is_latest_version]
- Filtering This column is used to filter the campaign group history to only include the latest version of each campaign group.

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[is_latest_version]
- Filtering This column is used to filter the campaign group history records to only include the latest version of each campaign group.

5. model.linkedin.linkedin_ads__url_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[is_latest_version]
- Filtering This column is used to filter the campaign group history to only include the latest version of each campaign group.

13. model.linkedin_source.stg_linkedin_ads__campaign_group_history_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type, set to NULL. The query is likely used as a placeholder or template for a staging table in a dbt (data build tool) project, specifically for LinkedIn ads campaign group history data.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment
_dbt_source_relation	text	None

Column Lineage

12. model.linkedin_source.stg_linkedin_ads__campaign_group_history

Input Column: 13. model.linkedin_source.stg_linkedin_ads__campaign_group_history_tmp[_dbt_source_relation]
- Direct The column is directly copied without any transformation
- Cleaning The column is cleaned by casting it as TEXT

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct copied directly to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as a key to join with other tables
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 12. model.linkedin_source.stg_linkedin_ads__campaign_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

14. model.linkedin_source.stg_linkedin_ads__campaign_history

This SQL query stages data from a LinkedIn Ads campaign history source. It begins by selecting all columns from a temporary staging table, then defines a set of columns with specific data types using a CTE named 'macro'. The 'fields' CTE then selects and renames columns, casts some to appropriate data types, and adds a flag to identify the latest version of each campaign. The final SELECT statement returns all columns from the 'fields' CTE.

CleaningDeduplicationOther

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__campaign_history_tmp
), macro AS (
  SELECT
    CAST(NULL AS INT) AS account_id,
    CAST(NULL AS BOOLEAN) AS audience_expansion_enabled,
    CAST(NULL AS INT) AS campaign_group_id,
    CAST(NULL AS TEXT) AS cost_type,
    CAST(NULL AS TIMESTAMP) AS created_time,
    CAST(NULL AS TEXT) AS creative_selection,
    CAST(NULL AS FLOAT) AS daily_budget_amount,
    CAST(NULL AS TEXT) AS daily_budget_currency_code,
    CAST(NULL AS TEXT) AS format,
    CAST(NULL AS INT) AS id,
    CAST(NULL AS TIMESTAMP) AS last_modified_time,
    CAST(NULL AS TEXT) AS locale_country,
    CAST(NULL AS TEXT) AS locale_language,
    CAST(NULL AS TEXT) AS name,
    CAST(NULL AS TEXT) AS objective_type,
    CAST(NULL AS BOOLEAN) AS offsite_delivery_enabled,
    CAST(NULL AS TEXT) AS optimization_target_type,
    CAST(NULL AS TIMESTAMP) AS run_schedule_end,
    CAST(NULL AS TIMESTAMP) AS run_schedule_start,
    CAST(NULL AS TEXT) AS status,
    CAST(NULL AS TEXT) AS type,
    CAST(NULL AS FLOAT) AS unit_cost_amount,
    CAST(NULL AS TEXT) AS unit_cost_currency_code,
    CAST(NULL AS TEXT) AS version_tag,
    CAST('' AS TEXT) AS source_relation
  FROM base
), fields AS (
  SELECT
    source_relation,
    id AS campaign_id,
    name AS campaign_name,
    CAST(version_tag AS DECIMAL) AS version_tag,
    campaign_group_id,
    account_id,
    status,
    type,
    cost_type,
    creative_selection,
    daily_budget_amount,
    daily_budget_currency_code,
    unit_cost_amount,
    unit_cost_currency_code,
    format,
    locale_country,
    locale_language,
    objective_type,
    optimization_target_type,
    audience_expansion_enabled AS is_audience_expansion_enabled,
    offsite_delivery_enabled AS is_offsite_delivery_enabled,
    CAST(run_schedule_start AS TIMESTAMP) AS run_schedule_start_at,
    CAST(run_schedule_end AS TIMESTAMP) AS run_schedule_end_at,
    CAST(last_modified_time AS TIMESTAMP) AS last_modified_at,
    CAST(created_time AS TIMESTAMP) AS created_at,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY last_modified_time DESC) = 1 AS is_latest_version
  FROM macro
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
campaign_id	integer	Unique internal ID representing the campaign.
last_modified_at	timestamp without time zone	The timestamp of when the version was updated.
account_id	integer	The ID of the account the campaign belongs to.
campaign_group_id	integer	The ID of the campaign group the campaign belongs to.
created_at	timestamp without time zone	The timestamp of when the campaign was created.
campaign_name	text	A label for the campaign.
version_tag	numeric	An integer representation of the version of the campaign.
is_audience_expansion_enabled	boolean	Boolean (default=false) representing whether Audience Expansion is enabled for the campaign provides query expansion for certain targeting criteria.
cost_type	text	CPM - Cost per thousand advertising impressions. If type=SPONSORED_INMAILS; cost per send(CPS) is measured as CPM x 1000. CPC- Cost per individual click on the associated link. CPV- Cost per view for video ads.
creative_selection	text	ROUND_ROBIN - Rotate through available creatives to serve them as evenly as possible. OPTIMIZED (default) - Bias selection taking into account such as expected performance. Not available for Message and Conversation Ads (type=SPONSORED_INMAILS).
daily_budget_amount	double precision	Maximum amount to spend per day UTC. The amount of money as a real number string.
daily_budget_currency_code	text	ISO currency code. The currency must match that of the parent account.
format	text	The ad format on the campaign level. See https://docs.microsoft.com/en-us/linkedin/marketing/integrations/ads/campaign-formats?view=li-lms-2022-07
locale_country	text	Locale of the campaign. An uppercase two-letter country code as defined by ISO-3166.
locale_language	text	Locale of the campaign. A lowercase two-letter language code as defined by ISO-639.
objective_type	text	Campaign Objective type values. Click [here](https://docs.microsoft.com/en-us/linkedin/marketing/integrations/ads-reporting/ad-budget-pricing-type-combinations?view=li-lms-2022-07) for Campaign Objective descriptions. One of: - BRAND_AWARENESS - ENGAGEMENT - JOB_APPLICANTS - LEAD_GENERATION - WEBSITE_CONVERSIONS - WEBSITE_VISITS - VIDEO_VIEWS
is_offsite_delivery_enabled	boolean	Boolean indicating if this campaign to allowed to be served on the LinkedIn Audience Network to extend the reach of your campaign by delivering ads beyond the LinkedIn feed to members on third-party apps and sites. (default will be "True" from June 30, 2022)
optimization_target_type	text	Determines how this campaign is optimized for spending. If this is not set, there is no optimization. Refer to the documentation here. Default = 'NONE'.
run_schedule_start_at	timestamp without time zone	Scheduled date range to run associated creatives. The start date must be non-null. Represents the inclusive (greater than or equal to) value in which to start the range.
run_schedule_end_at	timestamp without time zone	Scheduled date range to run associated creatives. The start date must be non-null. Represents the exclusive (strictly less than) value in which to end the range, if provided.
status	text	ACTIVE - Denotes that the campaign is fully servable. PAUSED - Denotes that the campaign meets all requirements to be served, but temporarily should not be. ARCHIVED - Denotes that the campaign is presently inactive, and should mostly be hidden in the UI until un-archived. COMPLETED - Denotes that the campaign has reached a specified budgetary or chronological limit. CANCELED - Denotes that the campaign has been permanently canceled, such as when an advertising account is permanently closed. DRAFT - Denotes that the campaign is still being edited and not eligible for serving. Some validation will be postponed until the campaign is activated. PENDING_DELETION - Denotes that the campaign has been requested to be deleted that is currently pending. REMOVED - Denotes that the campaign was deleted, but must remain fetchable due to the existence of performance data.
type	text	TEXT_AD - Text-based ads that show up in the right column or top of the page on LinkedIn. SPONSORED_UPDATES - Native ads that promote a company's content updates in the LinkedIn feed. SPONSORED_INMAILS - Personalized messages with a call-to-action button delivered to a LinkedIn's member inbox. DYNAMIC - Ads that are dynamically personalized.
unit_cost_amount	double precision	This value is used as one of the following: amount to bid (for manual bidding), amount which is the target cost (for target cost bidding) per click, impression, or other event depending on the pricing model, or cost cap (for cost cap bidding). The amount of money as a real number string. The amount should be non-negative if the bidding strategy is manual, target cost, or cost cap bidding. The default is 0 with the currency code set to match that of the associated account.
unit_cost_currency_code	text	Amount to bid per click, impression, or other event depending on the pricing model. The default is 0 with the currency code set to match that of the associated account. ISO currency code.
is_latest_version	boolean	Boolean of whether the record is the latest version of the campaign.

Column Lineage

14. model.linkedin_source.stg_linkedin_ads__campaign_history

Input Column: 15. model.linkedin_source.stg_linkedin_ads__campaign_history_tmp[_dbt_source_relation]
- Cleaning The column is cleaned by casting it to TEXT type
- Direct The column is directly copied to the output

1. model.linkedin.linkedin_ads__account_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration This column is used as a key to join the campaign table with the report and account tables
- Direct This column is directly copied to the output

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_id]
- Integration This column is used as a key to join the campaign table with the report table

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_id]
- Integration used as the key to join with the report table

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_id]
- Integration used as join key with creative table
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_id]
- Integration used as the key to join with the creative table
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[last_modified_at]
- Direct directly copied to the output

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[account_id]
- Integration This column is used as a key to join the campaign table with the account table

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[account_id]
- Integration used to join with the account table

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[account_id]
- Integration used as the key to join with the account table

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[account_id]
- Integration used as join key with account table

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[account_id]
- Integration used to join with the account table

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_group_id]
- Integration used to join with the campaign_group table

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_group_id]
- Integration used as the key to join with the campaign_group table

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_group_id]
- Integration used as join key with campaign_group table

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_group_id]
- Integration used to join with the campaign_group table

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[created_at]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_name]
- Direct directly copied to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_name]
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[campaign_name]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[version_tag]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_audience_expansion_enabled]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[cost_type]
- Direct directly copied to the output

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[creative_selection]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[daily_budget_amount]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[daily_budget_currency_code]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[format]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[locale_country]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[locale_language]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[objective_type]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_offsite_delivery_enabled]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[optimization_target_type]
- Direct This column is directly copied without any transformation

Column Lineage

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[run_schedule_start_at]
- Integration This column is used to join the campaign table with the campaign_group table

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[run_schedule_start_at]
- Direct This column is directly copied without any transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[run_schedule_end_at]
- Direct This column is directly copied to the output without transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[status]
- Direct This column is directly copied to the output
- Filtering Used in the WHERE clause to filter for the latest version

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[status]
- Direct The status is directly copied to the output
- Filtering Only the latest version of the campaign is used (is_latest_version = true)

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[type]
- Direct This column is directly copied to the output without transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[unit_cost_amount]
- Direct This column is directly copied to the output without transformation

Column Lineage

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[unit_cost_currency_code]
- Direct This column is directly copied to the output without transformation

Column Lineage

1. model.linkedin.linkedin_ads__account_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_latest_version]
- Filtering This column is used to filter for the latest version of campaign data

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_latest_version]
- Filtering This column is used to filter for the latest version of campaign history records

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_latest_version]
- Filtering Used in the WHERE clause to filter for the latest version of the campaign

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_latest_version]
- Filtering Only records where is_latest_version is true are included in the CTE

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[is_latest_version]
- Filtering This column is used to filter for the latest version of each campaign

15. model.linkedin_source.stg_linkedin_ads__campaign_history_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of type TEXT. It's likely used as a placeholder or template for further development or testing purposes.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment
_dbt_source_relation	text	None

Column Lineage

14. model.linkedin_source.stg_linkedin_ads__campaign_history

Input Column: 15. model.linkedin_source.stg_linkedin_ads__campaign_history_tmp[_dbt_source_relation]
- Cleaning The column is cleaned by casting it to TEXT type
- Direct The column is directly copied to the output

1. model.linkedin.linkedin_ads__account_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration This column is used as a key to join the campaign table with the report and account tables
- Direct This column is directly copied to the output

2. model.linkedin.linkedin_ads__campaign_group_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

3. model.linkedin.linkedin_ads__campaign_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

4. model.linkedin.linkedin_ads__creative_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 14. model.linkedin_source.stg_linkedin_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

16. model.linkedin_source.stg_linkedin_ads__creative_history

This SQL query processes data from a LinkedIn Ads creative history table. It performs several operations: 1. Casts and renames columns for consistency 2. Selects the latest version of each creative based on last modified time 3. Extracts various URL components and UTM parameters from the click_uri field 4. Cleans and standardizes data formats

CleaningDeduplicationFeaturization

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_linkedin_ads_source.stg_linkedin_ads__creative_history_tmp
), macro AS (
  SELECT
    CAST(NULL AS INT) AS campaign_id,
    CAST(NULL AS TEXT) AS click_uri,
    CAST(NULL AS TIMESTAMP) AS created_time,
    CAST(NULL AS TIMESTAMP) AS created_at,
    CAST(NULL AS INT) AS id,
    CAST(NULL AS TIMESTAMP) AS last_modified_time,
    CAST(NULL AS TIMESTAMP) AS last_modified_at,
    CAST(NULL AS TEXT) AS intended_status,
    CAST(NULL AS TEXT) AS status,
    CAST('' AS TEXT) AS source_relation
  FROM base
), fields AS (
  SELECT
    source_relation,
    id AS creative_id,
    campaign_id,
    COALESCE(status, intended_status) AS status,
    click_uri,
    CAST(COALESCE(last_modified_time, last_modified_at) AS TIMESTAMP) AS last_modified_at,
    CAST(COALESCE(created_time, created_at) AS TIMESTAMP) AS created_at,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY COALESCE(last_modified_time, last_modified_at) DESC) = 1 AS is_latest_version
  FROM macro
), url_fields AS (
  SELECT
    *,
    SPLIT_PART(click_uri, '?', 1) AS base_url,
    TRY_CAST(SPLIT_PART(
      SPLIT_PART(
        REPLACE(REPLACE(REPLACE(click_uri, 'android-app://', ''), 'http://', ''), 'https://', ''),
        '/',
        1
      ),
      '?',
      1
    ) AS TEXT) AS url_host,
    '/' || TRY_CAST(SPLIT_PART(
      CASE
        WHEN LENGTH(REPLACE(REPLACE(click_uri, 'http://', ''), 'https://', '')) - COALESCE(
          NULLIF(STR_POSITION(REPLACE(REPLACE(click_uri, 'http://', ''), 'https://', ''), '/'), 0),
          STR_POSITION(REPLACE(REPLACE(click_uri, 'http://', ''), 'https://', ''), '?') - 1
        ) = 0
        THEN ''
        ELSE RIGHT(
          REPLACE(REPLACE(click_uri, 'http://', ''), 'https://', ''),
          LENGTH(REPLACE(REPLACE(click_uri, 'http://', ''), 'https://', '')) - COALESCE(
            NULLIF(STR_POSITION(REPLACE(REPLACE(click_uri, 'http://', ''), 'https://', ''), '/'), 0),
            STR_POSITION(REPLACE(REPLACE(click_uri, 'http://', ''), 'https://', ''), '?') - 1
          )
        )
      END,
      '?',
      1
    ) AS TEXT) AS url_path,
    NULLIF(SPLIT_PART(SPLIT_PART(click_uri, 'utm_source=', 2), '&', 1), '') AS utm_source,
    NULLIF(SPLIT_PART(SPLIT_PART(click_uri, 'utm_medium=', 2), '&', 1), '') AS utm_medium,
    NULLIF(SPLIT_PART(SPLIT_PART(click_uri, 'utm_campaign=', 2), '&', 1), '') AS utm_campaign,
    NULLIF(SPLIT_PART(SPLIT_PART(click_uri, 'utm_content=', 2), '&', 1), '') AS utm_content,
    NULLIF(SPLIT_PART(SPLIT_PART(click_uri, 'utm_term=', 2), '&', 1), '') AS utm_term
  FROM fields
)
SELECT
  *
FROM url_fields

Name	Type	Comment
source_relation	text	The source of the record if the unioning functionality is being used. If not this field will be empty.
creative_id	integer	Unique internal ID representing the creative.
last_modified_at	timestamp without time zone	The timestamp of when the version was updated.
created_at	timestamp without time zone	The timestamp of when the creative was created.
campaign_id	integer	The ID of the campaign the creative belongs to.
status	text	ACTIVE - Creative creation is complete and creative is available for review and serve. PAUSED - Creative creation is complete and creative is current, but should temporarily not be served. This status is not valid upon creation. DRAFT - Creative creation is incomplete and may still be edited. ARCHIVED - Creative creation is complete, but creative should not be served and should be separated from non-archived creatives in any UI. CANCELED - Creative is permanently canceled.
click_uri	text	Required when creative type is TEXT_AD. Maximum character limit of 500 after URL encoding all special characters. Limit does not include the "https://" prefix.
base_url	text	The base URL of the ad, extracted from the `click_uri`.
url_host	text	The URL host of the ad, extracted from the `click_uri`.
url_path	text	The URL path of the ad, extracted from the `click_uri`.
utm_source	text	The utm_source parameter of the ad, extracted from the `click_uri`.
utm_medium	text	The utm_medium parameter of the ad, extracted from the `click_uri`.
utm_campaign	text	The utm_campaign parameter of the ad, extracted from the `click_uri`.
utm_content	text	The utm_content parameter of the ad, extracted from the `click_uri`.
utm_term	text	The utm_term parameter of the ad, extracted from the `click_uri`.
is_latest_version	boolean	Boolean of whether the record is the latest version of the creative.

Column Lineage

16. model.linkedin_source.stg_linkedin_ads__creative_history

Input Column: 17. model.linkedin_source.stg_linkedin_ads__creative_history_tmp[_dbt_source_relation]
- Direct The column is directly copied to the 'source_relation' output column.
- Cleaning The column is cleaned by being cast as TEXT and coalesced with an empty string.

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[source_relation]
- Integration Used as part of the join condition with other tables
- Direct Directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[creative_id]
- Integration Used as the key to join with the report table
- Direct Directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[creative_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[last_modified_at]
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[created_at]
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[campaign_id]
- Integration Used to join with the campaign table

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[campaign_id]
- Integration used as the key to join with the campaign table

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[status]
- Direct Directly copied to the output

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[click_uri]
- Direct Directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[click_uri]
- Filtering used in the WHERE clause to filter out null values
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[base_url]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[url_host]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[url_path]
- Direct directly copied to the output

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_source]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_medium]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_campaign]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_content]
- Direct The column is directly copied without any transformation

Column Lineage

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[utm_term]
- Direct The column is directly copied without any transformation

Column Lineage

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[is_latest_version]
- Filtering This column is used to filter for the latest version of the creative in the CTE

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[is_latest_version]
- Filtering The column is used to filter only the latest version of creatives

17. model.linkedin_source.stg_linkedin_ads__creative_history_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of type TEXT. It's likely used as a placeholder or template for further operations in dbt (data build tool).

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment
_dbt_source_relation	text	None

Column Lineage

16. model.linkedin_source.stg_linkedin_ads__creative_history

Input Column: 17. model.linkedin_source.stg_linkedin_ads__creative_history_tmp[_dbt_source_relation]
- Direct The column is directly copied to the 'source_relation' output column.
- Cleaning The column is cleaned by being cast as TEXT and coalesced with an empty string.

4. model.linkedin.linkedin_ads__creative_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[source_relation]
- Integration Used as part of the join condition with other tables
- Direct Directly copied to the output

5. model.linkedin.linkedin_ads__url_report

Input Column: 16. model.linkedin_source.stg_linkedin_ads__creative_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output

18. source.linkedin_source.linkedin_ads.account_history

The model details are not provided

19. source.linkedin_source.linkedin_ads.ad_analytics_by_campaign

The model details are not provided

20. source.linkedin_source.linkedin_ads.ad_analytics_by_creative

The model details are not provided

21. source.linkedin_source.linkedin_ads.campaign_group_history

The model details are not provided

22. source.linkedin_source.linkedin_ads.campaign_history

The model details are not provided

23. source.linkedin_source.linkedin_ads.creative_history

The model details are not provided