Níže je příkladové query, které z “campaigns total” tabulek v GBQ, separovaných pro Meta, Google Ads a Adform, udělá jednu společnou tabulku se základními unifikovanými metrikami. (pozor, počítá že mají všechny stejnou měnu).
CREATE OR REPLACE VIEW CAMPAIGNDASH.DashSampleCampaignALL1074 AS
— Meta (DashSampleMeta1074_2)
SELECT
campaign_name,
SAFE_CAST(impressions AS INT64) AS impressions,
SAFE_CAST(clicks AS INT64) AS clicks,
SAFE_CAST(spend AS NUMERIC) AS spend
FROM CAMPAIGNDASH.DashSampleMeta1074_2
UNION ALL
— Google Ads
SELECT
campaign_name,
SAFE_CAST(metrics_impressions AS INT64) AS impressions,
SAFE_CAST(metrics_clicks AS INT64) AS clicks,
SAFE_CAST(metrics_costmicros AS NUMERIC) / 1000000 AS spend
FROM CAMPAIGNDASH.DashSampleGAdsCampaignTotal1074
UNION ALL
— Adform
SELECT
campaign AS campaign_name,
SAFE_CAST(impressions AS INT64) AS impressions,
SAFE_CAST(clicks AS INT64) AS clicks,
SAFE_CAST(cost AS NUMERIC) AS spend
FROM CAMPAIGNDASH.DashSampleAdform1074;
A níže je prompt pro Chat GPT, který to vygeneruje, můžete si ho případně upravit pokud jste líní nebo nemáte znalosti si upravovat to query.
You are a Google Big Query expert. Your task is to create a query for creating / updating a new table view in GBQ, that will contain the values from other tables.
The view name will be CAMPAIGNDASH.DashSampleCampaignALL1074 with columns: campaign_name impressions clicks spend
and it will consist of the following tables. Each table has several columns to display, they all are in the same order, but in the query I need you to name them, because I will work on that query further CAMPAIGNDASH.DashSampleMeta1074 campaign_name impressions clicks spend
CAMPAIGNDASH.DashSampleGAdsCampaignTotal1074 campaign_name metrics_impressions metrics_clicks metrics_costmicros – please divide this by 1000000 before inserting the value into the view
CAMPAIGNDASH.DashSampleAdform1074 campaign impressions clicks cost
please output the query for the view