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.utm_source), ''), 'linkedin.com') AS source,
IFNULL(NULLIF(TRIM(c.utm_medium), ''), 'referral') AS medium,
IFNULL(NULLIF(TRIM(c.utm_campaign), ''), '(not set)') AS campaign,
IFNULL(NULLIF(TRIM(c.utm_term), ''), '(not set)') AS keyword,
IFNULL(NULLIF(TRIM(c.utm_content), ''), '(not set)') AS adContent,
CONCAT('linkedin via OWOX (', c.ad_account, ')') AS adGroup,
c.date AS date,
--Metrics
SUM(cost_in_local_currency) AS adCost,
local_currency AS currency,
SUM(impressions) AS impressions,
SUM(clicks) AS adClicks,
c.ad_account AS adAccount,
'A01' AS dataSource,
--Campaign
'WEB' AS campaignType,
IF(c.campaign_group IS NOT NULL, REPLACE(c.campaign_group, 'urn:li:sponsoredCampaignGroup:', ''), NULL) AS campaignId,
CAST(null AS STRING) AS campaignName,
--AdGroup
CAST(c.campaign_id AS STRING) AS adGroupId,
c.campaign_name AS adGroupName,
'LinkedIn Ads' AS adSource,
c.debug_info.PipelineId AS pipelineId
FROM
dates AS d, `{{projectId}}.{{datasetName}}.linkedin_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 |
0 Comments