5. Twitter

Use the SQL code below and change {{projectId}} and {{datasetName}} to your own.

WITH
  dates AS
    (
      SELECT
        '{{start_suffix}}' AS s,
        '{{end_suffix}}' AS e,
        '{{main_currency}}' AS cur
    ),
  AdCostData AS
    (
      SELECT
        --UTM
          IFNULL(NULLIF(TRIM(c.source), ''), 'twitter.com') AS source,
          IFNULL(NULLIF(TRIM(c.medium), ''), 'social') AS medium,
          IFNULL(NULLIF(TRIM(c.campaign), ''), '(not set)') AS campaign,
          IFNULL(NULLIF(TRIM(c.keyword), ''), '(not set)') AS keyword,
          IFNULL(NULLIF(TRIM(c.adContent), ''), '(not set)') AS adContent,
        CONCAT('twitter via OWOX (', c.adAccount, ')') AS adGroup,
        c.date AS date,
        --Metrics
          SUM(c.adCost) AS adCost,
          c.currency AS currency,
          SUM(c.impressions) AS impressions,
          SUM(c.adClicks) AS adClicks,
        c.adAccount AS adAccount,
        'R01' AS dataSource,
        --Campaign
          CAST(null AS STRING) AS campaignType,
          CAST(null AS STRING) AS campaignId,
          CAST(null AS STRING) AS campaignName,
        --AdGroup
          CAST(null AS STRING) AS adGroupId,
          CAST(null AS STRING) AS adGroupName,
        'Twitter Ads' AS adSource,
        c.debugInfo_pipelineId AS pipelineId
      FROM
        dates AS d, `{{projectId}}.{{datasetName}}.twitter_OWOXAdCostData` AS c
      WHERE
        c.date BETWEEN PARSE_DATE('%Y%m%d', d.s) AND PARSE_DATE('%Y%m%d', d.e)
      GROUP BY
        source, medium, campaign, keyword, adContent,
        adGroup, date, currency, adAccount, dataSource,
        campaignType, campaignId, campaignName,
        adGroupId, adGroupName, adSource, pipelineId
    ),
  Currencies AS
    (
      SELECT
        c.date,
        c.currency,
        c.rate,
        MAX(IF(c.currency=d.cur, c.rate, null)) OVER (PARTITION BY c.date) AS localRate
      FROM
        dates AS d, `{{projectId}}.{{datasetName}}.Currencies` AS c
      WHERE
        c.date BETWEEN PARSE_DATE('%Y%m%d', d.s) AND PARSE_DATE('%Y%m%d', d.e)
        AND
        currency IN ('UAH','EUR','RUB')
    ),
  CurrencyConversion AS
    (
      SELECT
        --UTM
          a.source,
          a.medium,
          a.campaign,
          a.keyword,
          a.adContent,
        a.adGroup,
        a.date,
        --Metrics
          IF(c.rate IS null OR c.localRate IS null, null, a.adCost/c.rate*c.localRate) AS adCost,
          a.impressions,
          a.adClicks,
        a.adAccount,
        a.dataSource,
        --Campaign
          a.campaignType,
          a.campaignId,
          a.campaignName,
        --AdGroup
          a.adGroupId,
          a.adGroupName,
        a.adSource,
        STRUCT(a.pipelineId) AS debugInfo
      FROM
        AdCostData AS a
        LEFT JOIN
        Currencies AS c
        ON a.date=c.date AND a.currency=c.currency
    )
SELECT
  *
FROM
  CurrencyConversion

 

You will get the data in the table 'CostData' with the schema below:

Field name Type
source STRING
medium STRING
campaign STRING
keyword STRING
adContent STRING
adGroup STRING
date STRING
adCost FLOAT
impressions INTEGER
adClicks INTEGER
currency STRING
adAccount STRING
dataSource STRING
campaignType STRING
campaignId STRING
campaignName STRING
adGroupId STRING
adGroupName STRING
adSource STRING
pipelineId STRING
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.