使用Sankey Chart在Looker Studio中跟踪用户路径流的SQL查询

我正在使用SQL查询来分析Google Analytics数据中的用户导航路径,特别是用于Looker Studio Sankey图表中的可视化。Looker Studio的Sankey图表要求数据采用“源”和“目标”列结构,并且不能添加超过2个维度。我正试图跟踪从特定引用者开始的路径,然后按照用户导航的页面位置顺序。

以下是我的基本查询:

WITH cleaned_data AS (
  SELECT
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS raw_page_referrer,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS raw_page_location
  FROM
    `<table_id>`
  WHERE
    event_name = 'page_view' AND
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
),
referrer_and_location AS (
  SELECT
    user_pseudo_id,
    CASE
      WHEN raw_page_referrer LIKE '%mydomain.com%' THEN 'raw_page_referrer'
      ELSE 'entrance'
    END AS cleaned_referrer,
    raw_page_location AS cleaned_location
  FROM
    cleaned_data
)

SELECT
  cleaned_referrer AS page_referrer,
  cleaned_location AS page_location,
  COUNT(DISTINCT user_pseudo_id) AS values
FROM
  referrer_and_location
GROUP BY 1, 2
HAVING page_referrer = 'mydomain.com/homepage' AND values > 10

我想修改此查询以查找以特定页面引用者开头的路径。我们的目标是跟踪用户在引用人之后访问的页面位置的顺序,然后将当前页面位置视为新的引用人,以此类推。这将使我们能够从最初的引用页面分析用户流。

挑战在于将这些数据结构化为Looker Studio的Sankey图表所需的源/目的地格式,我正在努力相应地调整我的查询。

有人能就如何修改此查询以实现预期结果提供指导或建议吗?