استخدام البيانات الأولية في Google Analytics عمليًا

SELECT
CORR(culture,transac) AS corr_culture,
CORR(opinion,transac) AS corr_opinion,
CORR(lifestyle,transac) AS corr_lifestyle,
CORR(sport,transac) AS corr_sport
FROM(
SELECT
  SUM(IF(hit.page.pagePath LIKE'/culture%',
      1,
      0)) AS culture,
  SUM(IF(hit.page.pagePath LIKE'/opinion%',
      1,
      0)) AS opinion,
  SUM(IF(hit.page.pagePath LIKE'/lifestyle%',
      1,
      0)) AS lifestyle,-
  SUM(IF(hit.page.pagePath LIKE'/sport%',
      1,
      0)) AS sport,      
  COUNT(hit.transaction.transactionId) AS transac
FROM
  `mydatabase.view_id.ga_sessions_*`,
  UNNEST(hits) AS hit
WHERE
  _TABLE_SUFFIX BETWEEN '20191201' AND '20200301'
GROUP BY
  fullVisitorId
ORDER BY
  transac DESC
)

# change my-app.analytics_123456789 to your ID
WITH cohorte_september
AS
(
WITH
user_september AS
(
SELECT DISTINCT user_pseudo_id AS user
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20190901' AND '20190930'
AND
event_name="first_open"
)
SELECT sessions, month
FROM
(
(SELECT COUNT(DISTINCT user_pseudo_id) AS sessions,"201909" AS month
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20190901' AND '20190930'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_september)
)
UNION ALL
(SELECT COUNT(DISTINCT user_pseudo_id),"201910"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191001' AND '20191031'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_september)
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"201911"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191101' AND '20191130'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_september)
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"201912"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191201' AND '20191231'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_september)
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202001"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200101' AND '20200131'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_september)
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202002"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200201' AND '20200229'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_september)
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202003"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200301' AND '20200331'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_september)
)
)
ORDER BY month ASC
),

#october

cohorte_october AS
(
WITH
user_october AS
(
SELECT DISTINCT user_pseudo_id AS user
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191001' AND '20191031'
AND
event_name="first_open"
)
SELECT sessions, month
FROM
(
(SELECT COUNT(DISTINCT user_pseudo_id) AS sessions,"201909" AS month
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20190901' AND '20190930'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_october )
)
UNION ALL
(SELECT COUNT(DISTINCT user_pseudo_id),"201910"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191001' AND '20191031'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_october )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"201911"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191101' AND '20191130'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_october )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"201912"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191201' AND '20191231'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_october )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202001"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200101' AND '20200131'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_october )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202002"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200201' AND '20200229'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_october )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202003"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200301' AND '20200331'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_october)
)
)
ORDER BY month ASC
),

#november

cohorte_november AS
(
WITH
user_november AS
(
SELECT DISTINCT user_pseudo_id AS user
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191101' AND '20191130'
AND
event_name="first_open"
)
SELECT sessions, month
FROM
(
(SELECT COUNT(DISTINCT user_pseudo_id) AS sessions,"201909" AS month
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20190901' AND '20190930'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_november )
)
UNION ALL
(SELECT COUNT(DISTINCT user_pseudo_id),"201910"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191001' AND '20191031'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_november )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"201911"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191101' AND '20191130'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_november )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"201912"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191201' AND '20191231'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_november )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202001"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200101' AND '20200131'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_november )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202002"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200201' AND '20200229'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_november )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202003"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200301' AND '20200331'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_november )
)
)
ORDER BY month ASC
),

#decembre


cohorte_decembre AS
(
WITH
user_decembre AS
(
SELECT DISTINCT user_pseudo_id AS user
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191201' AND '20191231'
AND
event_name="first_open"
)
SELECT sessions, month
FROM
(
(SELECT COUNT(DISTINCT user_pseudo_id) AS sessions,"201909" AS month
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20190901' AND '20190930'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_decembre )
)
UNION ALL
(SELECT COUNT(DISTINCT user_pseudo_id),"201910"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191001' AND '20191031'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_decembre  )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"201911"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191101' AND '20191130'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_decembre  )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"201912"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20191201' AND '20191231'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_decembre  )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202001"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200101' AND '20200131'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_decembre  )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202002"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200201' AND '20200229'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_decembre  )
)
UNION ALL
(
SELECT COUNT(DISTINCT user_pseudo_id),"202003"
FROM `my-app.analytics_123456789.events_*`
WHERE
_table_suffix between '20200301' AND '20200331'
AND event_name="session_start"
AND user_pseudo_id IN (SELECT user FROM user_decembre  )
)
)
ORDER BY month ASC
)

SELECT cohorte_september.sessions AS september_cohort,  
 cohorte_october.sessions AS october_cohort,  
 cohorte_november.sessions AS november_cohort,  
 cohorte_decembre.sessions AS december_cohort,  
 month

FROM cohorte_september
JOIN  cohorte_october USING (month)
JOIN  cohorte_november USING (month)
JOIN  cohorte_decembre USING (month)
ORDER BY month ASC