Useful view to define in Segment warehouse schemes

landing_pages_view.sql

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;

retensions_view.sql

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