%3

1. model.pinterest.pinterest_ads__ad_group_report

This SQL query integrates data from multiple Pinterest Ads-related tables (ad group report, advertisers, campaigns, and ad groups) to create a comprehensive view of ad performance. It joins these tables based on relevant IDs and source relations, filters for the most recent records in some tables, and aggregates spend, clicks, and impressions data at the ad group level. The result is a detailed report that includes information about advertisers, campaigns, ad groups, and their performance metrics.

FilteringIntegrationAggregation

SQL Query

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__ad_group_report
), advertisers AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_history
  WHERE
    is_most_recent_record = TRUE
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), ad_groups AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__ad_group_history
  WHERE
    is_most_recent_record = TRUE
), fields AS (
  SELECT
    report.source_relation,
    report.date_day,
    advertisers.advertiser_name,
    advertisers.advertiser_id,
    campaigns.campaign_name,
    campaigns.campaign_status,
    campaigns.campaign_id,
    ad_groups.ad_group_name,
    report.ad_group_id,
    ad_groups.created_at,
    ad_groups.start_time,
    ad_groups.end_time,
    ad_groups.ad_group_status,
    SUM(report.spend) AS spend,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions
  FROM report
  LEFT JOIN ad_groups
    ON report.ad_group_id = ad_groups.ad_group_id
    AND report.source_relation = ad_groups.source_relation
  LEFT JOIN campaigns
    ON ad_groups.campaign_id = campaigns.campaign_id
    AND ad_groups.source_relation = campaigns.source_relation
  LEFT JOIN advertisers
    ON campaigns.advertiser_id = advertisers.advertiser_id
    AND campaigns.source_relation = advertisers.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
advertiser_id	None	The ID of the related Advertiser.
advertiser_name	None	Name of the advertiser.
campaign_id	None	The ID of the related Campaign.
campaign_name	None	The name of the related Campaign.
campaign_status	None	Status of the campaign.
ad_group_id	None	The ID of the related Ad group.
ad_group_name	None	The name of the related Ad group.
ad_group_status	None	Status of the ad group.
created_at	None	Ad group creation time.
start_time	None	Ad group start time.
end_time	None	Ad group end time.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[source_relation]
- Direct The column is directly copied without transformation
- Integration Used as part of the join condition with other tables
Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[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: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly selected in the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

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

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration used as the key to join with the campaigns table
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- Integration used as the key to join with ad_groups table
- Direct directly selected in the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct directly selected in the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_status]
- Direct directly selected in the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_id]
- Direct The column is directly copied without transformation
- Integration Used as the key to join with the report table
Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[ad_group_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the ad_groups table
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_name]
- Direct The column is directly copied without transformation

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_status]
- Direct The column is directly copied without transformation

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[created_at]
- Direct The column is directly copied without transformation

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[start_time]
- Direct The column is directly copied without transformation

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[end_time]
- Direct The column is directly copied without transformation

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[impressions]
- Aggregation Summed up in the SELECT statement

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[clicks]
- Aggregation Summed up in the SELECT statement

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[spend]
- Aggregation Summed up in the SELECT statement

2. model.pinterest.pinterest_ads__advertiser_report

This SQL query combines advertiser report data with advertiser history data, joining them on advertiser_id and source_relation. It filters the advertiser history to only include the most recent records. The query then aggregates spend, clicks, and impressions by date, advertiser, currency, and country, providing a comprehensive summary of advertising performance for each advertiser.

FilteringIntegrationAggregation

SQL Query

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_report
), advertisers AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_history
  WHERE
    is_most_recent_record = TRUE
), fields AS (
  SELECT
    report.source_relation,
    report.date_day,
    advertisers.advertiser_name,
    report.advertiser_id,
    advertisers.currency_code,
    advertisers.country,
    SUM(report.spend) AS spend,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions
  FROM report
  LEFT JOIN advertisers
    ON report.advertiser_id = advertisers.advertiser_id
    AND report.source_relation = advertisers.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
advertiser_name	None	Name of the advertiser.
advertiser_id	None	The ID of the related Advertiser.
currency_code	None	The currency code which the advertiser is set up using.
country	None	The country code where the advertiser is located.
spend	None	The amount of spend that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the key to join with the report table
- Aggregation Included in the GROUP BY clause
Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with the advertisers table
- Aggregation Used in the GROUP BY clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

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

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct The column is directly copied without any transformation
- Aggregation Included in the GROUP BY clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the report table
- Aggregation Included in the GROUP BY clause
Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[advertiser_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the advertisers table
- Aggregation Used in the GROUP BY clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[currency_code]
- Direct The column is directly copied without any transformation
- Aggregation Included in the GROUP BY clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[country]
- Direct The column is directly copied without any transformation
- Aggregation Included in the GROUP BY clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[spend]
- Aggregation The column is summed up in the SELECT clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[clicks]
- Aggregation The column is summed up in the SELECT clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[impressions]
- Aggregation The column is summed up in the SELECT clause

3. model.pinterest.pinterest_ads__campaign_report

This SQL query combines data from three tables (campaign report, campaign history, and advertiser history) to create a comprehensive report on Pinterest ad campaigns. It joins the most recent campaign and advertiser data with daily report data, aggregates spend, clicks, and impressions, and includes relevant information such as advertiser name, campaign name, and status.

FilteringIntegrationAggregation

SQL Query

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__campaign_report
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), advertisers AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_history
  WHERE
    is_most_recent_record = TRUE
), fields AS (
  SELECT
    report.source_relation,
    report.date_day,
    advertisers.advertiser_name,
    advertisers.advertiser_id,
    campaigns.campaign_name,
    report.campaign_id,
    campaigns.campaign_status,
    SUM(report.spend) AS spend,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions
  FROM report
  LEFT JOIN campaigns
    ON report.campaign_id = campaigns.campaign_id
    AND report.source_relation = campaigns.source_relation
  LEFT JOIN advertisers
    ON campaigns.advertiser_id = advertisers.advertiser_id
    AND campaigns.source_relation = advertisers.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
advertiser_id	None	The ID of the related Advertiser.
advertiser_name	None	Name of the advertiser.
campaign_id	None	The ID of the related Campaign.
campaign_name	None	The name of the related Campaign.
campaign_status	None	Status of the campaign.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Integration Used as part of the join condition with campaigns table
- Direct Directly included in the output
Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[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 result
Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[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

3. model.pinterest.pinterest_ads__campaign_report

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

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration Used as the key to join with campaigns table
- Direct Directly included in the output

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct Directly included in the output

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- 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 result
Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[campaign_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the campaigns table
- Aggregation Used in the GROUP BY clause

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct The column is directly copied without any transformation
- Aggregation The column is grouped by in the final result

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_status]
- Direct The column is directly copied without any transformation
- Aggregation The column is grouped by in the final result

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[impressions]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[clicks]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[spend]
- Aggregation The column is summed up in the SELECT statement

4. model.pinterest.pinterest_ads__keyword_report

This SQL query combines data from multiple Pinterest Ads-related tables to create a comprehensive keyword report. It joins keyword performance data with advertiser, campaign, ad group, and keyword information. The query filters for the most recent records in the history tables, aggregates spend, clicks, and impressions data, and presents a detailed view of keyword performance across the advertising hierarchy.

FilteringIntegrationAggregation

SQL Query

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__keyword_report
), advertisers AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_history
  WHERE
    is_most_recent_record = TRUE
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), ad_groups AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__ad_group_history
  WHERE
    is_most_recent_record = TRUE
), keywords AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__keyword_history
  WHERE
    is_most_recent_record = TRUE
), fields AS (
  SELECT
    report.source_relation,
    report.date_day,
    advertisers.advertiser_name,
    advertisers.advertiser_id,
    campaigns.campaign_name,
    campaigns.campaign_id,
    ad_groups.ad_group_name,
    ad_groups.ad_group_id,
    report.keyword_id,
    keywords.match_type,
    keywords.parent_type,
    keywords.keyword_value,
    SUM(report.spend) AS spend,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions
  FROM report
  LEFT JOIN keywords
    ON report.keyword_id = keywords.keyword_id
    AND report.source_relation = keywords.source_relation
  LEFT JOIN ad_groups
    ON keywords.ad_group_id = ad_groups.ad_group_id
    AND keywords.source_relation = ad_groups.source_relation
  LEFT JOIN campaigns
    ON ad_groups.campaign_id = campaigns.campaign_id
    AND ad_groups.source_relation = campaigns.source_relation
  LEFT JOIN advertisers
    ON campaigns.advertiser_id = advertisers.advertiser_id
    AND campaigns.source_relation = advertisers.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12
)
SELECT
  *
FROM fields

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
advertiser_name	None	Name of the advertiser.
advertiser_id	None	The ID of the related Advertiser.
campaign_name	None	Name of the campaign.
campaign_id	None	The ID of the related Campaign.
ad_group_name	None	Name of the ad group.
ad_group_id	None	The ID of the related Ad group.
keyword_id	None	Unique identifier of the keyword.
match_type	None	Type of match the keyword is tied to. Either Exact or Broad.
parent_type	None	Identifier of what grain the parent type is. Ad group or campaign.
keyword_value	None	The text value that makes upd the keyword.
spend	None	The amount of spend that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[source_relation]
- Integration used as a key to join with other tables
- Direct directly copied to the output
Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join conditions with other tables
- Aggregation Used in the GROUP BY clause

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

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

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct directly copied to the output

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration used as the key to join with the campaigns table
- Direct directly copied to the output

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct directly copied to the output

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- Integration used as the key to join with ad_groups table
- Direct directly copied to the output

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_name]
- Direct directly copied to the output

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_id]
- Integration used as a key to join with other tables
- Direct directly copied to the output

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[keyword_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the report table
Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[keyword_id]
- Direct The column is directly copied without any transformation
- Integration Used to join with the keywords table
- Aggregation Used in the GROUP BY clause

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[match_type]
- Direct The column is directly copied without any transformation

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[parent_type]
- Direct The column is directly copied without any transformation

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[keyword_value]
- Direct The column is directly copied without any transformation

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[spend]
- Aggregation The spend is summed up in the GROUP BY clause

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[clicks]
- Aggregation The clicks are summed up in the GROUP BY clause

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[impressions]
- Aggregation The column is summed up in the SELECT statement

5. model.pinterest.pinterest_ads__pin_promotion_report

This SQL query integrates data from multiple Pinterest Ads-related tables (pin promotion report, pin promotion history, ad group history, campaign history, and advertiser history) to create a comprehensive report. It joins these tables based on various IDs and source relations, filters for the most recent records in the history tables, and aggregates metrics such as clicks, impressions, and spend. The result is a detailed view of Pinterest ad performance across different levels of the advertising hierarchy (advertiser, campaign, ad group, and pin).

FilteringIntegrationAggregation

SQL Query

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__pin_promotion_report
), pins AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__pin_promotion_history
  WHERE
    is_most_recent_record = TRUE
), ad_groups AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__ad_group_history
  WHERE
    is_most_recent_record = TRUE
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), advertisers AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_history
  WHERE
    is_most_recent_record = TRUE
), joined AS (
  SELECT
    report.source_relation,
    report.date_day,
    campaigns.advertiser_id,
    advertisers.advertiser_name,
    report.campaign_id,
    campaigns.campaign_name,
    campaigns.campaign_status,
    report.ad_group_id,
    ad_groups.ad_group_name,
    ad_groups.ad_group_status,
    pins.creative_type,
    report.pin_promotion_id,
    pins.pin_name,
    pins.pin_status,
    pins.destination_url,
    pins.base_url,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.spend) AS spend
  FROM report
  LEFT JOIN pins
    ON report.pin_promotion_id = pins.pin_promotion_id
    AND report.source_relation = pins.source_relation
  LEFT JOIN ad_groups
    ON report.ad_group_id = ad_groups.ad_group_id
    AND report.source_relation = ad_groups.source_relation
  LEFT JOIN campaigns
    ON report.campaign_id = campaigns.campaign_id
    AND report.source_relation = campaigns.source_relation
  LEFT JOIN advertisers
    ON campaigns.advertiser_id = advertisers.advertiser_id
    AND campaigns.source_relation = advertisers.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16
)
SELECT
  *
FROM joined

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
advertiser_id	None	The ID of the related Advertiser.
advertiser_name	None	Name of the advertiser.
campaign_id	None	The ID of the related Campaign.
campaign_name	None	Name of the campaign.
campaign_status	None	Status of the campaign.
ad_group_id	None	The ID of the related Ad group.
ad_group_name	None	Name of the ad group.
ad_group_status	None	Status of the ad group.
creative_type	None	The creative type. One of "APP", "APP_VIDEO", "BOARD", "CAROUSEL", "CINEMATIC", "COMMERCE", "MAX_VIDEO", "NATIVE_VIDEO", "REGULAR", "SEARCH_PROMINENCE", "SEARCH_PROMINENCE_CAROUSEL", "SHOPPING", "SHOP_THE_PIN", "THIRD_PARTY", or "VIDEO".
pin_promotion_id	None	The ID of the related Pin promotion.
pin_name	None	Name of the pin.
pin_status	None	Status of the pin.
destination_url	None	Pin destination URL.
base_url	None	The base URL of the ad, extracted from the `destination_url`.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Integration used to join with other tables
- Direct directly copied to the output
Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[source_relation]
- Integration used as a key to join with other tables
- Direct directly copied to the output
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables
- Aggregation Included in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[date_day]
- Direct The column is directly copied without any transformation
- Aggregation Included in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration used to join with the campaigns table
- Direct directly copied to the output
Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[advertiser_id]
- Integration used as a key to join with the advertisers table
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- Integration used as a key to join with other tables
- Direct directly copied to the output
Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[campaign_id]
- Integration Used as the key to join with the campaigns table
- Aggregation Included in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_status]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output
Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[ad_group_id]
- Integration Used as the key to join with the ad_groups table
- Aggregation Included in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_name]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_status]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[creative_type]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_promotion_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output
Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[pin_promotion_id]
- Integration Used as the key to join with the pins table
- Aggregation Included in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_name]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_status]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Featurization used to extract base_url

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Featurization used to extract base_url
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[base_url]
- Direct This column is directly selected in the final output without transformation

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[clicks]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[impressions]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[spend]
- Aggregation The column is summed up in the SELECT statement

6. model.pinterest.pinterest_ads__url_report

This SQL query integrates data from multiple Pinterest advertising tables (pin promotion report, pin promotion history, ad group history, campaign history, and advertiser history) to create a comprehensive URL report. It joins these tables, filters for the most recent records in the history tables, and aggregates metrics like clicks, impressions, and spend. The query provides a detailed view of advertising performance across various dimensions such as advertiser, campaign, ad group, and pin levels, including URL-related information and UTM parameters.

IntegrationFilteringAggregation

SQL Query

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__pin_promotion_report
), pins AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__pin_promotion_history
  WHERE
    is_most_recent_record = TRUE
), ad_groups AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__ad_group_history
  WHERE
    is_most_recent_record = TRUE
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), advertisers AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_history
  WHERE
    is_most_recent_record = TRUE
), joined AS (
  SELECT
    report.source_relation,
    report.date_day,
    campaigns.advertiser_id,
    advertisers.advertiser_name,
    report.campaign_id,
    campaigns.campaign_name,
    campaigns.campaign_status,
    report.ad_group_id,
    ad_groups.ad_group_name,
    ad_groups.ad_group_status,
    pins.destination_url,
    pins.creative_type,
    report.pin_promotion_id,
    pins.pin_name,
    pins.pin_status,
    pins.base_url,
    pins.url_host,
    pins.url_path,
    pins.utm_source,
    pins.utm_medium,
    pins.utm_campaign,
    pins.utm_content,
    pins.utm_term,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.spend) AS spend
  FROM report
  LEFT JOIN pins
    ON report.pin_promotion_id = pins.pin_promotion_id
    AND report.source_relation = pins.source_relation
  LEFT JOIN ad_groups
    ON report.ad_group_id = ad_groups.ad_group_id
    AND report.source_relation = ad_groups.source_relation
  LEFT JOIN campaigns
    ON report.campaign_id = campaigns.campaign_id
    AND report.source_relation = campaigns.source_relation
  LEFT JOIN advertisers
    ON campaigns.advertiser_id = advertisers.advertiser_id
    AND campaigns.source_relation = advertisers.source_relation
  WHERE
    NOT pins.destination_url IS NULL
  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
)
SELECT
  *
FROM joined

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
advertiser_id	None	The ID of the related Advertiser.
advertiser_name	None	Name of the advertiser.
campaign_status	None	Status of the campaign.
ad_group_status	None	Status of the ad group.
destination_url	None	Pin destination URL.
pin_promotion_id	None	The ID of the related Pin promotion.
pin_name	None	Pin promotion name.
pin_status	None	The status of the Pin promotion. One of "ACTIVE", "ARCHIVED", "PAUSED"
creative_type	None	The creative type. One of "APP", "APP_VIDEO", "BOARD", "CAROUSEL", "CINEMATIC", "COMMERCE", "MAX_VIDEO", "NATIVE_VIDEO", "REGULAR", "SEARCH_PROMINENCE", "SEARCH_PROMINENCE_CAROUSEL", "SHOPPING", "SHOP_THE_PIN", "THIRD_PARTY", or "VIDEO".
base_url	None	The base URL of the ad, extracted from the `destination_url`.
url_host	None	The URL host of the ad, extracted from the `destination_url`.
url_path	None	The URL path of the ad, extracted from the `destination_url`.
utm_source	None	The utm_source parameter of the ad, extracted from the `destination_url`.
utm_medium	None	The utm_medium parameter of the ad, extracted from the `destination_url`.
utm_campaign	None	The utm_campaign parameter of the ad, extracted from the `destination_url`.
utm_content	None	The utm_content parameter of the ad, extracted from the `destination_url`.
utm_term	None	The utm_term parameter of the ad, extracted from the `destination_url`.
campaign_id	None	The ID of the related Campaign.
campaign_name	None	The name of the related Campaign.
ad_group_id	None	The ID of the related Ad group.
ad_group_name	None	The name of the related Ad group.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[source_relation]
- Integration used as part of the join condition with the report table
- Direct directly copied to the output
Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Integration used as part of the join condition with campaigns table
- Direct copied directly to the output
Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly copied to the output
Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[source_relation]
- Direct The column is directly selected in the output
- Integration Used to join with other tables

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[date_day]
- Direct The column is directly selected in the output
- Aggregation Used in the GROUP BY clause

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration used as the key to join with campaigns table
- Direct copied directly to the output
Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[advertiser_id]
- Direct directly copied to the output
- Integration used to join with the advertisers table

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct copied directly to the output

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_status]
- Direct directly copied to the output

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_status]
- Direct directly copied to the output

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_promotion_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output
Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[pin_promotion_id]
- Integration Used to join with the pins table
- Aggregation Used in the GROUP BY clause

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_name]
- Direct directly copied to the output

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_status]
- Direct directly copied to the output

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[creative_type]
- Direct directly copied to the output

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[base_url]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[url_host]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[url_path]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_source]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_medium]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_campaign]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_content]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters
Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_term]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output
Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[campaign_id]
- Integration Used to join with the campaigns table
- Aggregation Used in the GROUP BY clause

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct directly copied to the output

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output
Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[ad_group_id]
- Integration Used to join with the ad_groups table
- Aggregation Used in the GROUP BY clause

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_name]
- Direct directly copied to the output

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[impressions]
- Aggregation Summed up in the SELECT clause

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[clicks]
- Aggregation Summed up in the SELECT clause

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[spend]
- Aggregation Summed up in the SELECT clause

7. model.pinterest_source.stg_pinterest_ads__ad_group_history

This SQL query performs a series of transformations on data from the 'pinterest_source.stg_pinterest_ads__ad_group_history' table. It starts by casting columns to specific data types, renames some columns, and adds a 'source_relation' column. The query then selects and reorganizes these fields, adding a 'is_most_recent_record' flag based on the most recent '_fivetran_synced' timestamp for each unique combination of 'source_relation' and 'id'.

CleaningDeduplicationOther

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__ad_group_history_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS campaign_id,
    CAST(NULL AS TIMESTAMP) AS created_time,
    CAST(NULL AS TIMESTAMP) AS end_time,
    CAST(NULL AS TEXT) AS id,
    CAST(NULL AS TEXT) AS ad_account_id,
    CAST(NULL AS TEXT) AS name,
    CAST(NULL AS TEXT) AS pacing_delivery_type,
    CAST(NULL AS TEXT) AS placement_group,
    CAST(NULL AS TIMESTAMP) AS start_time,
    CAST(NULL AS TEXT) AS status,
    CAST(NULL AS TEXT) AS summary_status,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    id AS ad_group_id,
    name AS ad_group_name,
    status AS ad_group_status,
    ad_account_id AS advertiser_id,
    _fivetran_synced,
    campaign_id,
    created_time AS created_at,
    end_time,
    pacing_delivery_type,
    placement_group,
    start_time,
    summary_status,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY _fivetran_synced DESC) = 1 AS is_most_recent_record
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
ad_group_id	None	Ad group ID.
campaign_id	None	Parent Campaign ID.
created_at	None	Ad group creation time.
ad_group_name	None	Ad group name.
ad_group_status	None	The status of the Ad group. One of "ACTIVE", "ARCHIVED", "PAUSED"
advertiser_id	None	The ID of the related Advertiser.
start_time	None	Ad group start time.
end_time	None	Ad group end time.
pacing_delivery_type	None	Ad group pacing delivery type. With ACCELERATED, an ad group budget is spent as fast as possible. With STANDARD, an ad group budget is spent smoothly over a day. When using CBO, only the STANDARD pacing delivery type is allowed.
placement_group	None	The placement group. "ALL", "SEARCH", "BROWSE", or "OTHER"
summary_status	None	Summary status. "RUNNING", "PAUSED", "NOT_STARTED", "COMPLETED", "ADVERTISER_DISABLED", "ARCHIVED"
_fivetran_synced	None	Timestamp of when a record was last synced.
is_most_recent_record	None	Boolean representing whether the record is the most recent version of the object.

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[source_relation]
- Direct The column is directly copied without transformation
- Integration Used as part of the join condition with other tables

4. model.pinterest.pinterest_ads__keyword_report

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

5. model.pinterest.pinterest_ads__pin_promotion_report

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

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[source_relation]
- Integration used as part of the join condition with the report table
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_id]
- Direct The column is directly copied without transformation
- Integration Used as the key to join with the report table

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_id]
- Integration used as a key to join with other tables
- Direct directly copied to the output

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[campaign_id]
- Integration Used as the key to join with the campaigns table

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[campaign_id]
- Integration used as a key to join with the campaigns table

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[created_at]
- Direct The column is directly copied without transformation

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_name]
- Direct The column is directly copied without transformation

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_name]
- Direct directly copied to the output

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_name]
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_name]
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_status]
- Direct The column is directly copied without transformation

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_status]
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[ad_group_status]
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[start_time]
- Direct The column is directly copied without transformation

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[end_time]
- Direct The column is directly copied without transformation

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the ad_groups CTE

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the ad_groups CTE

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the ad_groups CTE

6. model.pinterest.pinterest_ads__url_report

Input Column: 7. model.pinterest_source.stg_pinterest_ads__ad_group_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the ad_groups CTE

8. model.pinterest_source.stg_pinterest_ads__ad_group_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

9. model.pinterest_source.stg_pinterest_ads__ad_group_report

This SQL query transforms and cleans data from a Pinterest ads ad group report. It casts various fields to specific data types, combines impression and clickthrough data, calculates spend in dollars from micro-dollars, and truncates the date to day-level granularity. The query also adds a source_relation field and renames some columns for clarity.

CleaningFeaturization

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__ad_group_report_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS ad_group_id,
    CAST(NULL AS TEXT) AS ad_group_name,
    CAST(NULL AS TEXT) AS ad_group_status,
    CAST(NULL AS TEXT) AS advertiser_id,
    CAST(NULL AS INT) AS campaign_id,
    CAST(NULL AS INT) AS clickthrough_1,
    CAST(NULL AS INT) AS clickthrough_2,
    CAST(NULL AS TIMESTAMP) AS date,
    CAST(NULL AS INT) AS impression_1,
    CAST(NULL AS INT) AS impression_2,
    CAST(NULL AS INT) AS spend_in_micro_dollar,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    DATE_TRUNC('DAY', date) AS date_day,
    ad_group_id,
    ad_group_name,
    ad_group_status,
    campaign_id,
    advertiser_id,
    COALESCE(impression_1, 0) + COALESCE(impression_2, 0) AS impressions,
    COALESCE(clickthrough_1, 0) + COALESCE(clickthrough_2, 0) AS clicks,
    spend_in_micro_dollar / 1000000.0 AS spend
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
ad_group_id	None	The ID of the related Ad group.
ad_group_name	None	Name of the ad group.
ad_group_status	None	Status of the ad group.
campaign_id	None	The ID of the related Campaign.
advertiser_id	None	The ID of the related Advertiser.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[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

1. model.pinterest.pinterest_ads__ad_group_report

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

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[ad_group_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the ad_groups table
- Aggregation Used in the GROUP BY clause

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[impressions]
- Aggregation Summed up in the SELECT statement

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[clicks]
- Aggregation Summed up in the SELECT statement

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 9. model.pinterest_source.stg_pinterest_ads__ad_group_report[spend]
- Aggregation Summed up in the SELECT statement

10. model.pinterest_source.stg_pinterest_ads__ad_group_report_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type, which is set to NULL. The query is limited to 0 rows, effectively returning no data. This type of query is often used as a placeholder or template in data modeling tools like dbt (data build tool) to define the structure of a staging table without actually populating it with data.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment

11. model.pinterest_source.stg_pinterest_ads__advertiser_history

This SQL query stages data from a Pinterest ads advertiser history table. It casts columns to specific data types, renames some columns, and adds a flag to identify the most recent record for each advertiser. The query also adds a source_relation column and selects specific fields from the base table.

CleaningDeduplicationOther

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_history_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS country,
    CAST(NULL AS TIMESTAMP) AS created_time,
    CAST(NULL AS TEXT) AS currency,
    CAST(NULL AS TEXT) AS id,
    CAST(NULL AS TEXT) AS owner_user_id,
    CAST(NULL AS TEXT) AS owner_username,
    CAST(NULL AS TEXT) AS name,
    CAST(NULL AS TEXT) AS advertiser_permissions,
    CAST(NULL AS TIMESTAMP) AS updated_time,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    id AS advertiser_id,
    name AS advertiser_name,
    country,
    created_time AS created_at,
    currency AS currency_code,
    owner_user_id,
    owner_username,
    advertiser_permissions, /* permissions was renamed in macro */
    updated_time AS updated_at,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY updated_time DESC) = 1 AS is_most_recent_record
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
advertiser_id	None	The ID of the related Advertiser.
advertiser_name	None	Name of the advertiser.
country	None	The country code where the advertiser is located.
created_at	None	Timestamp of when a record was created.
currency_code	None	The currency code which the advertiser is set up using.
updated_at	None	Timestamp of when a record was last updated.
owner_username	None	Advertiser's username.
owner_user_id	None	Unique identifier of the owner user.
advertiser_permissions	None	The permissions associated with this account.
is_most_recent_record	None	Boolean representing whether the record is the most recent version of the object.

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

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

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the key to join with the report table
- Aggregation Included in the GROUP BY clause

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Integration Used as part of the join condition with campaigns table
- Direct Directly included in the output

4. model.pinterest.pinterest_ads__keyword_report

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

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Integration used to join with other tables
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[source_relation]
- Integration used as part of the join condition with campaigns table
- Direct copied directly to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration used as the key to join with the campaigns table
- Direct directly copied to the output

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the report table
- Aggregation Included in the GROUP BY clause

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration Used as the key to join with campaigns table
- Direct Directly included in the output

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration used as the key to join with the campaigns table
- Direct directly copied to the output

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration used to join with the campaigns table
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_id]
- Integration used as the key to join with campaigns table
- Direct copied directly to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct directly copied to the output

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct The column is directly copied without any transformation
- Aggregation Included in the GROUP BY clause

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct Directly included in the output

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct directly copied to the output

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[advertiser_name]
- Direct copied directly to the output

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[country]
- Direct The column is directly copied without any transformation
- Aggregation Included in the GROUP BY clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[currency_code]
- Direct The column is directly copied without any transformation
- Aggregation Included in the GROUP BY clause

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[is_most_recent_record]
- Filtering This column is used to filter the records in the advertisers CTE to only include the most recent version of each advertiser record.

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[is_most_recent_record]
- Filtering This column is used to filter the advertiser history records to only include the most recent ones (WHERE is_most_recent_record = TRUE)

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[is_most_recent_record]
- Filtering This column is used to filter the advertiser history records to only include the most recent version of each advertiser's data.

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[is_most_recent_record]
- Filtering This column is used to filter the records in the advertisers CTE to only include the most recent version of each advertiser record.

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[is_most_recent_record]
- Filtering This column is used to filter the advertiser history table to include only the most recent records for each advertiser.

6. model.pinterest.pinterest_ads__url_report

Input Column: 11. model.pinterest_source.stg_pinterest_ads__advertiser_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the advertiser_history table

12. model.pinterest_source.stg_pinterest_ads__advertiser_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 query, possibly for testing or initializing a structure without actual data.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment

13. model.pinterest_source.stg_pinterest_ads__advertiser_report

This SQL query performs several transformations on data from a Pinterest ads advertiser report. It starts by casting columns to specific data types, then combines impression and clickthrough data, converts spend from micro-dollars to dollars, and truncates the date to day level. The query also adds a source_relation column and renames some fields for clarity.

CleaningFeaturizationOther

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__advertiser_report_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS advertiser_id,
    CAST(NULL AS INT) AS clickthrough_1,
    CAST(NULL AS INT) AS clickthrough_2,
    CAST(NULL AS TIMESTAMP) AS date,
    CAST(NULL AS INT) AS impression_1,
    CAST(NULL AS INT) AS impression_2,
    CAST(NULL AS INT) AS spend_in_micro_dollar,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    DATE_TRUNC('DAY', date) AS date_day,
    advertiser_id,
    COALESCE(impression_1, 0) + COALESCE(impression_2, 0) AS impressions,
    COALESCE(clickthrough_1, 0) + COALESCE(clickthrough_2, 0) AS clicks,
    spend_in_micro_dollar / 1000000.0 AS spend
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
advertiser_id	None	The ID of the related Advertiser.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

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

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

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

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[advertiser_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the advertisers table
- Aggregation Used in the GROUP BY clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[impressions]
- Aggregation The column is summed up in the SELECT clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[clicks]
- Aggregation The column is summed up in the SELECT clause

Column Lineage

2. model.pinterest.pinterest_ads__advertiser_report

Input Column: 13. model.pinterest_source.stg_pinterest_ads__advertiser_report[spend]
- Aggregation The column is summed up in the SELECT clause

14. model.pinterest_source.stg_pinterest_ads__advertiser_report_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 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

15. model.pinterest_source.stg_pinterest_ads__campaign_history

This SQL query stages data from a temporary Pinterest ads campaign history table. It casts columns to specific data types, renames some columns, and adds a flag to identify the most recent record for each campaign. The query prepares the data for further processing or analysis by structuring it in a consistent format.

CleaningDeduplication

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__campaign_history_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TIMESTAMP) AS created_time,
    CAST(NULL AS INT) AS default_ad_group_budget_in_micro_currency,
    CAST(NULL AS BOOLEAN) AS is_automated_campaign,
    CAST(NULL AS BOOLEAN) AS is_campaign_budget_optimization,
    CAST(NULL AS BOOLEAN) AS is_flexible_daily_budgets,
    CAST(NULL AS TEXT) AS id,
    CAST(NULL AS TEXT) AS advertiser_id,
    CAST(NULL AS TEXT) AS name,
    CAST(NULL AS TEXT) AS status,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    id AS campaign_id,
    name AS campaign_name,
    advertiser_id,
    default_ad_group_budget_in_micro_currency,
    is_automated_campaign,
    is_campaign_budget_optimization,
    is_flexible_daily_budgets,
    status AS campaign_status,
    _fivetran_synced,
    created_time AS created_at,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY _fivetran_synced DESC) = 1 AS is_most_recent_record
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
campaign_id	None	The ID of the related Campaign.
created_at	None	Campaign creation time.
advertiser_id	None	The ID of the related Advertiser.
campaign_name	None	Campaign name.
campaign_status	None	The status of the Campaign. One of "ACTIVE", "ARCHIVED", "PAUSED"
default_ad_group_budget_in_micro_currency	None	When transitioning from campaign budget optimization to non-campaign budget optimization, the default_ad_group_budget_in_micro_currency will propagate to each child ad groups daily budget. Unit is micro currency of the associated advertiser account.
is_automated_campaign	None	Specifies whether the campaign was created in the automated campaign flow
is_campaign_budget_optimization	None	Determines if a campaign automatically generate ad-group level budgets given a campaign budget to maximize campaign outcome. When transitioning from non-cbo to cbo, all previous child ad group budget will be cleared.
is_flexible_daily_budgets	None	Determine if a campaign has flexible daily budgets setup.
_fivetran_synced	None	Timestamp of when a record was last synced.
is_most_recent_record	None	Boolean representing whether the record is the most recent version of the object.

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[source_relation]
- Integration used as part of the join condition with other tables
- Direct directly selected in the output

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[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 result

4. model.pinterest.pinterest_ads__keyword_report

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

5. model.pinterest.pinterest_ads__pin_promotion_report

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

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_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.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- Integration used as the key to join with ad_groups table
- Direct directly selected in the output

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- 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 result

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- Integration used as the key to join with ad_groups table
- Direct directly copied to the output

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- Integration used as a key to join with other tables
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[advertiser_id]
- Integration used as the key to join with advertisers table

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[advertiser_id]
- Integration The column is used as a key to join with the advertisers table

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[advertiser_id]
- Integration used as the key to join with advertisers table

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[advertiser_id]
- Integration used as a key to join with the advertisers table
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[advertiser_id]
- Direct directly copied to the output
- Integration used to join with the advertisers table

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct directly selected in the output

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct The column is directly copied without any transformation
- Aggregation The column is grouped by in the final result

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct directly copied to the output

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_name]
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_status]
- Direct directly selected in the output

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_status]
- Direct The column is directly copied without any transformation
- Aggregation The column is grouped by in the final result

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_status]
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[campaign_status]
- Direct directly copied to the output

Column Lineage

1. model.pinterest.pinterest_ads__ad_group_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the campaigns CTE

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[is_most_recent_record]
- Filtering This column is used to filter the most recent records from the campaign_history table

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the campaign history table

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the campaign history table

6. model.pinterest.pinterest_ads__url_report

Input Column: 15. model.pinterest_source.stg_pinterest_ads__campaign_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the campaign history table

16. model.pinterest_source.stg_pinterest_ads__campaign_history_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. The query doesn't select any actual data and limits the output to 0 rows, effectively creating a template or placeholder for the table structure.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment

17. model.pinterest_source.stg_pinterest_ads__campaign_report

This SQL query performs several operations on the Pinterest ads campaign report data. It starts by casting fields to specific data types, then combines impression and clickthrough data, calculates spend in dollars from micro-dollars, and truncates the date to day level. The query also renames some columns and selects specific fields for the final output.

CleaningFeaturizationOther

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__campaign_report_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS advertiser_id,
    CAST(NULL AS TEXT) AS campaign_id,
    CAST(NULL AS TEXT) AS campaign_name,
    CAST(NULL AS TEXT) AS campaign_status,
    CAST(NULL AS INT) AS clickthrough_1,
    CAST(NULL AS INT) AS clickthrough_2,
    CAST(NULL AS TIMESTAMP) AS date,
    CAST(NULL AS INT) AS impression_1,
    CAST(NULL AS INT) AS impression_2,
    CAST(NULL AS INT) AS spend_in_micro_dollar,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    DATE_TRUNC('DAY', date) AS date_day,
    campaign_id,
    campaign_name,
    campaign_status,
    advertiser_id,
    COALESCE(impression_1, 0) + COALESCE(impression_2, 0) AS impressions,
    COALESCE(clickthrough_1, 0) + COALESCE(clickthrough_2, 0) AS clicks,
    spend_in_micro_dollar / 1000000.0 AS spend
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
campaign_id	None	The ID of the related Campaign.
campaign_name	None	Name of the campaign.
campaign_status	None	Status of the campaign.
advertiser_id	None	The ID of the related Advertiser.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[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

3. model.pinterest.pinterest_ads__campaign_report

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

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[campaign_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the campaigns table
- Aggregation Used in the GROUP BY clause

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[impressions]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[clicks]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

3. model.pinterest.pinterest_ads__campaign_report

Input Column: 17. model.pinterest_source.stg_pinterest_ads__campaign_report[spend]
- Aggregation The column is summed up in the SELECT statement

18. model.pinterest_source.stg_pinterest_ads__campaign_report_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 is likely used as a template or placeholder for a staging table in a dbt (data build tool) project.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment

19. model.pinterest_source.stg_pinterest_ads__keyword_history

This SQL query performs a series of transformations on data from a Pinterest ads keyword history table. It starts by casting all fields to specific data types, then renames and reorganizes some columns. Finally, it adds a flag to identify the most recent record for each keyword using a window function.

CleaningDeduplicationOther

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__keyword_history_tmp
), fields AS (
  SELECT
    CAST(NULL AS TEXT) AS _fivetran_id,
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS ad_group_id,
    CAST(NULL AS TEXT) AS advertiser_id,
    CAST(NULL AS BOOLEAN) AS archived,
    CAST(NULL AS INT) AS bid,
    CAST(NULL AS TEXT) AS campaign_id,
    CAST(NULL AS TEXT) AS id,
    CAST(NULL AS TEXT) AS match_type,
    CAST(NULL AS TEXT) AS parent_type,
    CAST(NULL AS TEXT) AS value,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    id AS keyword_id,
    value AS keyword_value,
    _fivetran_id,
    _fivetran_synced,
    ad_group_id,
    advertiser_id,
    archived,
    bid,
    campaign_id,
    match_type,
    parent_type,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY _fivetran_synced DESC) = 1 AS is_most_recent_record
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
keyword_id	None	Unique identifier of the keyword.
keyword_value	None	The text value that makes upd the keyword.
_fivetran_id	None	The unique identifier of the record within the Fivetran synced table.
_fivetran_synced	None	Timestamp of when a record was last synced.
ad_group_id	None	The ID of the related Ad group.
advertiser_id	None	The ID of the related Advertiser.
archived	None	Boolean indicating if the keyword is archived.
bid	None	Bid amount set for the keyword.
campaign_id	None	The ID of the related Campaign.
match_type	None	Type of match the keyword is tied to. Either Exact or Broad.
parent_type	None	Identifier of what grain the parent type is. Ad group or campaign.
is_most_recent_record	None	Boolean representing whether the record is the most recent version of the object.

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[source_relation]
- Direct The column is directly copied without any transformation
- Integration Used as part of the join condition with other tables

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[keyword_id]
- Direct The column is directly copied without any transformation
- Integration Used as the key to join with the report table

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[keyword_value]
- Direct The column is directly copied without any transformation

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[ad_group_id]
- Integration Used as the key to join with the ad_groups table

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[match_type]
- Direct The column is directly copied without any transformation

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[parent_type]
- Direct The column is directly copied without any transformation

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 19. model.pinterest_source.stg_pinterest_ads__keyword_history[is_most_recent_record]
- Filtering The column is used to filter for the most recent records (WHERE is_most_recent_record = TRUE)

20. model.pinterest_source.stg_pinterest_ads__keyword_history_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of type TEXT, initialized to NULL. The query is limited to 0 rows, effectively creating a schema-only representation of the table without any data.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment

21. model.pinterest_source.stg_pinterest_ads__keyword_report

This SQL query stages data from a Pinterest ads keyword report. It performs type casting on various fields, combines impression and clickthrough data, calculates spend in dollars from micro-dollars, and truncates the date to day level. The query also adds a source_relation field and renames some columns for clarity.

CleaningFeaturization

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__keyword_report_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS ad_group_id,
    CAST(NULL AS TEXT) AS ad_group_name,
    CAST(NULL AS TEXT) AS ad_group_status,
    CAST(NULL AS TEXT) AS advertiser_id,
    CAST(NULL AS TEXT) AS campaign_id,
    CAST(NULL AS INT) AS clickthrough_1,
    CAST(NULL AS INT) AS clickthrough_2,
    CAST(NULL AS TIMESTAMP) AS date,
    CAST(NULL AS INT) AS impression_1,
    CAST(NULL AS INT) AS impression_2,
    CAST(NULL AS TEXT) AS keyword_id,
    CAST(NULL AS TEXT) AS pin_promotion_id,
    CAST(NULL AS INT) AS spend_in_micro_dollar,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    DATE_TRUNC('DAY', date) AS date_day,
    keyword_id,
    pin_promotion_id,
    ad_group_id,
    ad_group_name,
    ad_group_status,
    campaign_id,
    advertiser_id,
    COALESCE(impression_1, 0) + COALESCE(impression_2, 0) AS impressions,
    COALESCE(clickthrough_1, 0) + COALESCE(clickthrough_2, 0) AS clicks,
    spend_in_micro_dollar / 1000000.0 AS spend
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
keyword_id	None	Unique identifier of the keyword.
pin_promotion_id	None	The ID of the related Pin promotion.
ad_group_id	None	The ID of the related Ad group.
ad_group_name	None	Name of the ad group.
ad_group_status	None	Status of the ad group.
campaign_id	None	The ID of the related Campaign.
advertiser_id	None	The ID of the related Advertiser.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

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

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

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

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[keyword_id]
- Direct The column is directly copied without any transformation
- Integration Used to join with the keywords table
- Aggregation Used in the GROUP BY clause

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[impressions]
- Aggregation The column is summed up in the SELECT statement

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[clicks]
- Aggregation The clicks are summed up in the GROUP BY clause

Column Lineage

4. model.pinterest.pinterest_ads__keyword_report

Input Column: 21. model.pinterest_source.stg_pinterest_ads__keyword_report[spend]
- Aggregation The spend is summed up in the GROUP BY clause

22. model.pinterest_source.stg_pinterest_ads__keyword_report_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. It's likely used as a placeholder or template for a staging table in a dbt (data build tool) project, specifically for Pinterest ads keyword report data.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment

23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history

This SQL query processes data from a Pinterest ads pin promotion history table. It performs several transformations on the data, including casting data types, extracting URL components, parsing UTM parameters, and creating a flag for the most recent record. The query also renames some columns and creates new features from existing data.

CleaningFeaturizationDeduplication

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__pin_promotion_history_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS ad_group_id,
    CAST(NULL AS TEXT) AS ad_account_id,
    CAST(NULL AS TEXT) AS android_deep_link,
    CAST(NULL AS TEXT) AS click_tracking_url,
    CAST(NULL AS TIMESTAMP) AS created_time,
    CAST(NULL AS TEXT) AS creative_type,
    CAST(NULL AS TEXT) AS destination_url,
    CAST(NULL AS TEXT) AS id,
    CAST(NULL AS TEXT) AS ios_deep_link,
    CAST(NULL AS BOOLEAN) AS is_pin_deleted,
    CAST(NULL AS BOOLEAN) AS is_removable,
    CAST(NULL AS TEXT) AS name,
    CAST(NULL AS TEXT) AS pin_id,
    CAST(NULL AS TEXT) AS review_status,
    CAST(NULL AS TEXT) AS status,
    CAST(NULL AS TIMESTAMP) AS updated_time,
    CAST(NULL AS TEXT) AS view_tracking_url,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    id AS pin_promotion_id,
    ad_account_id AS advertiser_id,
    ad_group_id,
    created_time AS created_at,
    destination_url,
    SPLIT_PART(destination_url, '?', 1) AS base_url,
    TRY_CAST(SPLIT_PART(
      SPLIT_PART(
        REPLACE(
          REPLACE(REPLACE(destination_url, 'android-app://', ''), 'http://', ''),
          'https://',
          ''
        ),
        '/',
        1
      ),
      '?',
      1
    ) AS TEXT) AS url_host,
    '/' || TRY_CAST(SPLIT_PART(
      CASE
        WHEN LENGTH(REPLACE(REPLACE(destination_url, 'http://', ''), 'https://', '')) - COALESCE(
          NULLIF(
            STR_POSITION(REPLACE(REPLACE(destination_url, 'http://', ''), 'https://', ''), '/'),
            0
          ),
          STR_POSITION(REPLACE(REPLACE(destination_url, 'http://', ''), 'https://', ''), '?') - 1
        ) = 0
        THEN ''
        ELSE RIGHT(
          REPLACE(REPLACE(destination_url, 'http://', ''), 'https://', ''),
          LENGTH(REPLACE(REPLACE(destination_url, 'http://', ''), 'https://', '')) - COALESCE(
            NULLIF(
              STR_POSITION(REPLACE(REPLACE(destination_url, 'http://', ''), 'https://', ''), '/'),
              0
            ),
            STR_POSITION(REPLACE(REPLACE(destination_url, 'http://', ''), 'https://', ''), '?') - 1
          )
        )
      END,
      '?',
      1
    ) AS TEXT) AS url_path,
    NULLIF(SPLIT_PART(SPLIT_PART(destination_url, 'utm_source=', 2), '&', 1), '') AS utm_source,
    NULLIF(SPLIT_PART(SPLIT_PART(destination_url, 'utm_medium=', 2), '&', 1), '') AS utm_medium,
    NULLIF(SPLIT_PART(SPLIT_PART(destination_url, 'utm_campaign=', 2), '&', 1), '') AS utm_campaign,
    NULLIF(SPLIT_PART(SPLIT_PART(destination_url, 'utm_content=', 2), '&', 1), '') AS utm_content,
    NULLIF(SPLIT_PART(SPLIT_PART(destination_url, 'utm_term=', 2), '&', 1), '') AS utm_term,
    name AS pin_name,
    pin_id,
    status AS pin_status,
    creative_type,
    _fivetran_synced,
    ROW_NUMBER() OVER (PARTITION BY source_relation, id ORDER BY _fivetran_synced DESC) = 1 AS is_most_recent_record
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
pin_promotion_id	None	Pin promotion ID.
advertiser_id	None	The ID of the related Advertiser.
ad_group_id	None	Pin promotion ad group ID.
created_at	None	Pin creation time.
destination_url	None	Pin destination URL.
pin_name	None	Pin promotion name.
pin_id	None	Original pin ID.
pin_status	None	The status of the Pin promotion. One of "ACTIVE", "ARCHIVED", "PAUSED"
creative_type	None	The creative type. One of "APP", "APP_VIDEO", "BOARD", "CAROUSEL", "CINEMATIC", "COMMERCE", "MAX_VIDEO", "NATIVE_VIDEO", "REGULAR", "SEARCH_PROMINENCE", "SEARCH_PROMINENCE_CAROUSEL", "SHOPPING", "SHOP_THE_PIN", "THIRD_PARTY", or "VIDEO".
_fivetran_synced	None	Timestamp of when a record was last synced.
is_most_recent_record	None	Boolean representing whether the record is the most recent version of the object.
base_url	None	The base URL of the ad, extracted from the `destination_url`.
url_host	None	The URL host of the ad, extracted from the `destination_url`.
url_path	None	The URL path of the ad, extracted from the `destination_url`.
utm_source	None	The utm_source parameter of the ad, extracted from the `destination_url`.
utm_medium	None	The utm_medium parameter of the ad, extracted from the `destination_url`.
utm_campaign	None	The utm_campaign parameter of the ad, extracted from the `destination_url`.
utm_content	None	The utm_content parameter of the ad, extracted from the `destination_url`.
utm_term	None	The utm_term parameter of the ad, extracted from the `destination_url`.

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

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

6. model.pinterest.pinterest_ads__url_report

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

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_promotion_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_promotion_id]
- Integration used as the key to join with the report table
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Featurization used to extract base_url

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[destination_url]
- Direct directly copied to the output
- Filtering used in the WHERE clause to filter out null values
- Featurization base for extracting URL components and UTM parameters

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_name]
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_name]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_status]
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[pin_status]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[creative_type]
- Direct directly copied to the output

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[creative_type]
- Direct directly copied to the output

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the pins CTE

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[is_most_recent_record]
- Filtering This column is used to filter for the most recent records in the pins CTE

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[base_url]
- Direct This column is directly selected in the final output without transformation

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[base_url]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[url_host]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[url_path]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_source]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_medium]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_campaign]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_content]
- Direct This column is directly selected in the final output without transformation

Column Lineage

6. model.pinterest.pinterest_ads__url_report

Input Column: 23. model.pinterest_source.stg_pinterest_ads__pin_promotion_history[utm_term]
- Direct This column is directly selected in the final output without transformation

24. model.pinterest_source.stg_pinterest_ads__pin_promotion_history_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type. The query doesn't fetch any actual data; 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

25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report

This SQL query performs data transformation on a Pinterest ads pin promotion report. It starts by casting columns to specific data types, then calculates total impressions and clicks by combining two separate impression and clickthrough fields. It also converts the spend from micro-dollars to dollars. The query truncates the date to day level and selects specific fields for the final output.

CleaningFeaturization

SQL Query

WITH base AS (
  SELECT
    *
  FROM TEST.PUBLIC_pinterest_source.stg_pinterest_ads__pin_promotion_report_tmp
), fields AS (
  SELECT
    CAST(NULL AS TIMESTAMP) AS _fivetran_synced,
    CAST(NULL AS TEXT) AS ad_group_id,
    CAST(NULL AS TEXT) AS advertiser_id,
    CAST(NULL AS TEXT) AS campaign_id,
    CAST(NULL AS DECIMAL(28, 6)) AS clickthrough_1,
    CAST(NULL AS DECIMAL(28, 6)) AS clickthrough_2,
    CAST(NULL AS TIMESTAMP) AS date,
    CAST(NULL AS DECIMAL(28, 6)) AS impression_1,
    CAST(NULL AS DECIMAL(28, 6)) AS impression_2,
    CAST(NULL AS TEXT) AS pin_promotion_id,
    CAST(NULL AS DECIMAL(28, 6)) AS spend_in_micro_dollar,
    CAST('' AS TEXT) AS source_relation
  FROM base
), final AS (
  SELECT
    source_relation,
    DATE_TRUNC('DAY', date) AS date_day,
    pin_promotion_id,
    ad_group_id,
    campaign_id,
    advertiser_id,
    COALESCE(impression_1, 0) + COALESCE(impression_2, 0) AS impressions,
    COALESCE(clickthrough_1, 0) + COALESCE(clickthrough_2, 0) AS clicks,
    spend_in_micro_dollar / 1000000.0 AS spend
  FROM fields
)
SELECT
  *
FROM final

Name	Type	Comment
source_relation	None	The source of the record if the unioning functionality is being used. If not this field will be empty.
date_day	None	The performance date of the record.
pin_promotion_id	None	The ID of the related Pin promotion.
ad_group_id	None	The ID of the related Ad group.
campaign_id	None	The ID of the related Campaign.
advertiser_id	None	The ID of the related Advertiser.
impressions	None	The number of paid and earned impressions that occurred on the day of the record.
clicks	None	The number of paid and earned clicks that occurred on the day of the record.
spend	None	The amount of spend that occurred on the day of the record.

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

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

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[source_relation]
- Direct The column is directly selected in the output
- Integration Used to join with other tables

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[date_day]
- Direct The column is directly copied without any transformation
- Aggregation Included in the GROUP BY clause

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[date_day]
- Direct The column is directly selected in the output
- Aggregation Used in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[pin_promotion_id]
- Integration Used as the key to join with the pins table
- Aggregation Included in the GROUP BY clause

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[pin_promotion_id]
- Integration Used to join with the pins table
- Aggregation Used in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[ad_group_id]
- Integration Used as the key to join with the ad_groups table
- Aggregation Included in the GROUP BY clause

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[ad_group_id]
- Integration Used to join with the ad_groups table
- Aggregation Used in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[campaign_id]
- Integration Used as the key to join with the campaigns table
- Aggregation Included in the GROUP BY clause

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[campaign_id]
- Integration Used to join with the campaigns table
- Aggregation Used in the GROUP BY clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[impressions]
- Aggregation The column is summed up in the SELECT statement

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[impressions]
- Aggregation Summed up in the SELECT clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[clicks]
- Aggregation The column is summed up in the SELECT statement

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[clicks]
- Aggregation Summed up in the SELECT clause

Column Lineage

5. model.pinterest.pinterest_ads__pin_promotion_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[spend]
- Aggregation The column is summed up in the SELECT statement

6. model.pinterest.pinterest_ads__url_report

Input Column: 25. model.pinterest_source.stg_pinterest_ads__pin_promotion_report[spend]
- Aggregation Summed up in the SELECT clause

26. model.pinterest_source.stg_pinterest_ads__pin_promotion_report_tmp

This SQL query creates an empty result set with a single column named '_dbt_source_relation' of TEXT data type, initialized as NULL. It's likely used as a placeholder or template for a staging table in a dbt (data build tool) project, specifically for Pinterest ads data related to pin promotion reports.

Other

SQL Query

SELECT
  CAST(NULL AS TEXT) AS _dbt_source_relation
LIMIT 0

Name	Type	Comment

27. source.pinterest_source.pinterest_ads.ad_group_history

The model details are not provided

28. source.pinterest_source.pinterest_ads.ad_group_report

The model details are not provided

29. source.pinterest_source.pinterest_ads.advertiser_history

The model details are not provided

30. source.pinterest_source.pinterest_ads.advertiser_report

The model details are not provided

31. source.pinterest_source.pinterest_ads.campaign_history

The model details are not provided

32. source.pinterest_source.pinterest_ads.campaign_report

The model details are not provided

33. source.pinterest_source.pinterest_ads.keyword_history

The model details are not provided

34. source.pinterest_source.pinterest_ads.keyword_report

The model details are not provided

35. source.pinterest_source.pinterest_ads.pin_promotion_history

The model details are not provided

36. source.pinterest_source.pinterest_ads.pin_promotion_report

The model details are not provided

Cocoon Semantic Lineage

dbt_pinterest