Joining KeywordBasicStats table with SearQuery Performance table from Google Adwords data

Question

looking for some help. We pulled in Google Adwords data into BigQuery. I'm using the tables that are created in the transformation of the data from Google Adwords to BigQuery. I'm looking to bring together the KeywordBasicStats table with the SearchQueryPerformance table so we can view keywords and there stats along with the query information as well. I saw you can potential use KeywordTextMatchingQuery however it's not in the SearchQueryPerformance table. Any help around this will be greatly appreciated.

Answer 1

Assuming you're using BigQuery Transfer Services (https://cloud.google.com/bigquery-transfer/docs/adwords-transformation).

You could join 2 table using AdGroupId ANd CriterionId.

WITH
search_term AS (
  SELECT
    AdgroupId,
    CriterionId,
    Query AS search_term,
    SUM(Clicks) AS clicks,
    SUM(Impressions) AS impressions,
    SUM(Cost)/1000000 AS cost,
    -- other metrics
  FROM `<project>.adwords.p_SearchQueryStats_*` t1
  WHERE DATE(_PARTITIONTIME) = '<date>'
  GROUP BY 1, 2, 3
),
kw AS (
  SELECT
    AdGroupId,
    CriterionId,
    Criteria AS keyword,
    -- other fields
  FROM `<project>.adwords.p_Keyword_*`
  WHERE DATE(_PARTITIONTIME) = '<date>'
)
SELECT
    ...
FROM search_term t1
INNER JOIN kw ON t1.AdgroupId = kw.AdgroupId AND t1.CriterionId = kw.CriterionId