Skip to content

Instantly share code, notes, and snippets.

@blackawa
Last active August 2, 2019 06:01
Show Gist options
  • Save blackawa/686898da03617a629a03ac8aa9a74c66 to your computer and use it in GitHub Desktop.
Save blackawa/686898da03617a629a03ac8aa9a74c66 to your computer and use it in GitHub Desktop.
Useful view to define in Segment warehouse schemes
WITH
sessions AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY anonymous_id ORDER BY sent_at ASC) AS rn
FROM
`your-project.your_dataset.pages` ),
first_page AS (
SELECT
*
FROM
sessions
WHERE
rn = 1 )
SELECT
*,
CASE
WHEN context_campaign_source = 'yahoo' THEN 'ad/yahoo'
WHEN context_campaign_source = 'google' THEN 'ad/google'
WHEN referrer = 'https://search.yahoo.co.jp/' THEN 'organic/yahoo'
WHEN referrer LIKE '%www.google.%' THEN 'organic/google'
WHEN referrer LIKE 'https://blogs.example.com%' THEN 'organic/example'
ELSE
NULL
END referrer_type
FROM
first_page;
SELECT
identifies.user_id user_id,
FORMAT_TIMESTAMP('%F', landing_pages.timestamp) date
FROM
`your-project.your_dataset.landing_pages_view` landing_pages
JOIN
`your-project.your_dataset.identifies` identifies
ON
landing_pages.anonymous_id = identifies.anonymous_id
WHERE
identifies.user_id IS NOT NULL
GROUP BY
identifies.user_id,
date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment