mowshowitz mowshowitz - 13 days ago 5
SQL Question

Select max value for which condition x is fulfilled in SQL Server 2012

I'm trying to build a flexible attribution model for site traffic. I've started out by making a last-click model, where a conversion is attributed to a channel of traffic based on the last visit a visitor made prior to converting. So:

Visitor Visit Channel ConversionId
visitor a visit 1 email 123
visitor a visit 2 email 123
visitor a visit 3 direct 123
visitor b visit 1 seo 123
visitor b visit 2 direct 123
visitor b visit 3 email 123
visitor c visit 1 seo 123
visitor c visit 2 direct 123
visitor c visit 3 direct 123


In the above example, I would count each visitor's final visit and attribute the conversion to that channel. So, visitor a and visitor c's conversions would be attributed to direct, while visitor b's would be attributed to email.

Now I want to be able to exclude direct, and thus be able to attribute the conversion to the last non-direct channel. In this scenario, visitor a and visitor b's conversions would be attributed to email (visitor a's 3rd visit would be excluded), while visitor c's conversion would be attributed to se (as c's second and third visits would be excluded).

The way I have this set up so far is (and some of this looks a little goofy because I stripped some joins and identifying info out for simplicity):

WITH test (visitor,
visit, --a number unique for each visitor row but not necessarily unique across all visits
channel,
conversionid,
rn) AS
(
SELECT visitorid AS visitor,
visitid AS visit,
channel AS channel,
conversionid AS conversionid
rn = ROW_NUMBER() OVER (PARTITION BY conversionid ORDER BY visit DESC)
FROM db
GROUP BY visitorid,
visitid,
channel,
conversionid)

SELECT visitor,
MAX(visit) AS maxvisit,
channel,
conversionid
FROM test
WHERE rn = 1
GROUP BY visitor,
channel
ORDER BY visitor;


This gives me last-click attribution. (There was an easier way for me to do this but that way didn't look as flexible--I want to be able to easily change the query to exclude channels.) My question is, what do I do so I can exclude direct and attribute the conversion to the last non-direct channel? I'd also like to be able to exclude additional channels if needed so I can build different attribution models.

Thanks a million, guys.

Answer

It is a little unclear what you want so here are some statistics examples that hopefully will make this a little easier for you to understand. This section is geared toward Conditional Aggregation which allows you to Mix and Match such as take the MAX VisitId regardless of conversion that is associated and get the last channel that isn't direct.

DECLARE @Table AS TABLE (visitor CHAR(1), VisitId INT, ConversionId INT, Channel VARCHAR(15))
INSERT INTO @Table VALUES  ('a',1, 11,'email'),('a',2, 12,'email'),('a',3, 13,'direct')
,('b',4, 14,'seo'),('b',5, 15,'direct'),('b',6, 16,'email'),('c',7, 17,'seo')
,('c',8, 18,'direct'),('c',9, 19,'direct')

    ;WITH cte AS (
        SELECT
           visitor
           ,VisitId
           ,Channel
           ,ConversionId
           ,DirectConversion = CASE WHEN Channel = 'direct' THEN ConversionId END
           ,ChannelRowNumber = ROW_NUMBER() OVER (PARTITION BY visitor
              ORDER BY
                 CASE WHEN Channel = 'direct' THEN 1 ELSE 0 END
                 ,ConversionId DESC)

        FROM
           @Table
    )

    SELECT
        visitor
        ,MaxVisitId = MAX(VisitId)
        ,MaxVisitIdOfNonDirect = MAX(CASE WHEN DirectConversion IS NULL THEN VisitId END)
        ,NumOfVisits = COUNT(DISTINCT VisitId)
        ,Channel = MAX(CASE WHEN ChannelRowNumber = 1 THEN Channel END)
        ,NumOfConversions = COUNT(DISTINCT ConversionId)
        ,NumOfDirectConversions = COUNT(DISTINCT DirectConversion)
        ,NumOfNonDirectConversions = COUNT(DISTINCT ConversionId) - COUNT(DISTINCT DirectConversion)
    FROM
        cte
    GROUP BY
        visitor

If you just want to rule out Channel all together then using ROW_NUMBER and WHERE <> 'direct' should do the trick for you.