时间戳

首页 > 解决方案 > SQL 来跟踪客户的旅程

sql - SQL 来跟踪客户的旅程

问题描述

我有用户访问网站的数据，然后他们注册、登录并继续。所以当他们登陆页面时，user_id将为空。一旦用户注册，user_id跟踪器就会生成一个。例如。

user_id   IP-address      page_url         domain_id   location
null      192.45.34.23    Landing page     A           ABC
null      192.45.34.23    Welcome page     A           ABC
null      192.45.34.23    FAQ              A           ABC
null      192.45.34.23    Help             A           ABC
null      178.34.12.54    Landing page     A           ABC
12345     192.45.34.23    Sign up          A           ABC
12345     192.45.34.23    Sign in-page1    A           ABC
12345     178.34.12.54    Sign in-page 2   A           ABC

在跟踪客户的旅程时，我需要user_id在分配后将 with null 映射到 one。12345所以在表中，如果 IP 地址和domain_id相同，前 5 个用户 id 必须是。

用户12345有 2 个 IP 地址，因此当 IP 地址或 domain_id 匹配时，必须映射用户 ID。

输出应如下所示

user_id   IP-address      page_url         domain_id     location
12345     192.45.34.23    Landing page     A             ABC
12345     192.45.34.23    Welcome page     A             ABC
12345     192.45.34.23    FAQ              A             ABC
12345     192.45.34.23    Help             A             ABC
12345     178.34.12.54    Landing page     A             ABC
12345     192.45.34.23    Sign up          A             ABC
12345     192.45.34.23    Sing in-page 1   A             ABC
12345     178.34.12.54    Sign in-page 2   A             ABC

标签： sqlanalytics

解决方案

这就是你所追求的吗？这使用 SQL Server 的 T-SQL 语法；虽然应该与大多数其他数据库引擎所需的相似。

declare @example table
(
  Id bigint not null identity(1,1)
  , [user_id] bigint 
  , [IP-address] nvarchar(15) not null
  , page_url nvarchar(1024) not null
  , domain_id  nvarchar(32) not null
  , location nvarchar(32) not null
)

insert @example([user_id],   [IP-address], page_url, domain_id, location)
values (null   , '192.45.34.23', 'Landing page'  , 'A', 'ABC')
     , (null   , '192.45.34.23', 'Welcome page'  , 'A', 'ABC')
     , (null   , '192.45.34.23', 'FAQ'           , 'A', 'ABC')
     , (null   , '192.45.34.23', 'Help'          , 'A', 'ABC')
     , (null   , '178.34.12.54', 'Landing page'  , 'A', 'ABC')
     , (12345  , '192.45.34.23', 'Sign up'       , 'A', 'ABC')
     , (null   , '123.1.2.3'   , 'Help'          , 'B', 'ABC')
     , (12345  , '192.45.34.23', 'Sign in-page1' , 'A', 'ABC')
     , (12345  , '178.34.12.54', 'Sign in-page 2', 'A', 'ABC')
     , (98765  , '123.1.2.3'   , 'Help'          , 'B', 'ABC')

select Id
, coalesce
(
    [user_id]
    ,
    (
        select top 1 [user_id] 
        from @example b
        where [user_id] is not null
        and b.Id > a.Id 
        and
        (
            b.[IP-address] = a.[IP-address]
            or b.domain_id = a.domain_id
        )
        order by Id
    )
)      [user_id]
,      [IP-address]
,      page_url
,      domain_id
,      location
from @example a

即，我们从表中选择所有行，但在未填充用户 ID 的情况下，我们使用您提供的条件（即在域或 IP 上匹配）从后面的条目中派生它。

您没有在标准中指定它，但我冒昧地添加了一个 ID 字段并使用它来确保我们获取的条目是稍后的条目，并且是第一个匹配的条目。这假定空值只会在用户登录之前出现（即用户不会退出并继续访问该站点）。包含此内容而不是进行任何匹配的原因是我假设随着时间的推移，可能会将相同的 IP 重新分配给不同的用户；因此，这可以让您最好地猜测多个匹配中的哪一个最有可能是您所追求的用户。

推荐阅读