<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Robin Patel on Medium]]></title>
        <description><![CDATA[Stories by Robin Patel on Medium]]></description>
        <link>https://medium.com/@robin-patel?source=rss-e2768d1ab6d0------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*HhngbOBUuWDjKzHY.jpg</url>
            <title>Stories by Robin Patel on Medium</title>
            <link>https://medium.com/@robin-patel?source=rss-e2768d1ab6d0------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 04:13:45 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@robin-patel/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Time to sessionise]]></title>
            <link>https://robin-patel.medium.com/time-to-sessionise-b7c324b6b843?source=rss-e2768d1ab6d0------2</link>
            <guid isPermaLink="false">https://medium.com/p/b7c324b6b843</guid>
            <category><![CDATA[sessions]]></category>
            <category><![CDATA[snowplow-analytics]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[event-level-data]]></category>
            <category><![CDATA[snowflake]]></category>
            <dc:creator><![CDATA[Robin Patel]]></dc:creator>
            <pubDate>Tue, 01 Dec 2020 19:44:27 GMT</pubDate>
            <atom:updated>2020-12-01T19:52:35.988Z</atom:updated>
            <content:encoded><![CDATA[<p>Grouping events together to associate traffic sources to user behaviour</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/0*MXHOaUluWX53q10P.png" /></figure><p>The purpose of this article is to explain the basis of sessionisation and how I implemented a business definition of a session using <a href="https://medium.com/u/529d0079c96d">Snowplow Team</a> event level data on <a href="https://www.snowflake.com/">Snowflake</a>.</p><p>I hope you’ve had a chance to read my two previous posts that lead into this one, they basically describe how I’ve modelled data in preparation for sessionisation.</p><h3>What is sessionisation?</h3><blockquote>A session is a group of user interactions with your website that take place within a given time frame. — Google Analytics</blockquote><p>Sessionisation is the process that groups a person’s events together into a session to articulate what a person has done on your website/app in a single sitting. The main point of it is to help identify what sources generated traffic that done something of interest, these ‘things of interest’ can be anything from member sign ups to conversions. This data can then be utilised to optimise traffic based on the type of behaviour it generates or increase efficiency of spend (return on investment - ROI).</p><h3>Sessionisation approaches</h3><p>There are two main approaches on how to create sessions, pre and post data collection.</p><h4>Pre-collection sessionisation</h4><p>Pre-collection sessionisation is where the responsibility is put on the event tracker to define events that belong together in a session. Most good event collector services will have pre-sessionisation out of the box, this basically works by the tracker generating a session id (normally comprises of encrypting a concatenation of the timestamp, browser identifier etc) and assigning this to each event. This session id is preserved in a cookie that is stored on the user’s computer, the cookie has a expiry time (usually 30 minutes) and becomes invalid after this time, if a user visits the site the tracker checks to see if an existing valid cookie exists, if it does it uses the session id stored in the cookie, if no valid cookie exists it generates one.</p><p>Limitations:</p><ul><li>No real flexibility</li><li>Requires user to agree to store cookies</li><li>Vulnerable to client side tracking gaps eg. ad blockers, data transmission errors</li></ul><h4>Post-collection sessionisation</h4><p>Post-collection sessionisation is handled by modelling event level data into sessions. This doesn’t rely on a tracker to define a session and therefore adds flexibility to adjust the notion of a session to meet business requirements.</p><p>Limitations</p><ul><li>Requires advanced data modelling</li><li>Can be costly to compute</li></ul><p>So which one should you use? In most cases the pre-collection sessionisation approach is the way to go, it makes things a lot easier, there’s no need to create complex modelling logic to sessionise your data, no additional compute costs associated to group events into sessions and quicker to implement. If however -like me- the limitations of pre collection sessionisation aren’t something you can afford to compromise on and you have a passion for ensuring your data is the best representation of your business then embark on post-collection sessionisation via data modelling.</p><h3>Define what a session is for your business</h3><p>If you are still on the fence about whether to use pre or post collection defined sessions then this step might help you with your decision. Start out by working out what your company deems as a ‘sitting’ of continuous events for a user, examples of this can be whether things like the duration of inactivity, switching devices or new marketing signals or a combination of various things constitute as a new session.</p><p>In my situation, the business had various marketing initiatives that were generating site/app traffic, many of these were overlapping and therefore it was important that new marketing signals on an event signified the start of a new session. The <a href="https://support.google.com/analytics/answer/2731565?hl=en">Google analytics</a> definition of a session accounts for this, if a person is on a site, goes away and returns to the site within 30 mins but with new marketing parameters this is counted as a new session. However the Snowplow pre-collection defined session doesn’t handle this, and rightly so, the definition for which marketing signals should or shouldn’t mark the start of a new session can be very business specific. We defined our session expiry rules on new marketing parameters, a duration of 30 minutes of inactivity or a change in device.</p><h3>How to sessionise using SQL</h3><p>The general premise of sessionisation implementation is group events together based on a user, order them chronologically and then sub divide these events into sessions based on your business decisions mentioned in the previous section.</p><p>I mentioned that my previous posts helped set some foundational datasets to assist with sessionisation. <a href="https://robin-patel.medium.com/identity-stitching-and-member-resolution-d2fd22519a41">Identity stitching</a> allows us to group events together by assigning the ‘best’ user identifier for traffic based on the unknown identifiers that exist on each event. <a href="https://robin-patel.medium.com/playing-with-url-string-data-75ba14ff1e0">Modelling url strings</a> and categorising them allows us to divide events based on whether or not url parameters or referrer categories have been observed. These combined with event timestamps and device/platform assigned to each event were all the pieces we needed to meet the business requirement.</p><p>My approach to sessionising on these criteria was to break the division logic by computing each criteria separately, then computing sessions if any of the criteria signal a new session.</p><p>SQL window functions are an integral part of this implementation so if aren’t familiar I would advise you read up on them. <a href="https://towardsdatascience.com/sql-window-function-demonstrated-with-real-interview-questions-from-leetcode-e83e28edaabc">Here</a> is a great post by <a href="https://medium.com/u/176e6ade0080">Sherwin Zheng</a> demonstrating how to use them.</p><p>Note the dialect of SQL I’m using for these examples is <a href="https://docs.snowflake.com/en/">Snowflake</a>.</p><h4>Time of inactivity</h4><p>The premise here is quite simple, for every event we want to check if the previous event by the same user was more or less than 30 minutes prior to the timestamp of the current event, if it is more then lets set time_diff_flag = 1 and if it isn’t set time_diff_flag = 0. We can then cumulatively sum the time_diff_flag column and it will group events together based on an expiry of 30 minutes.</p><p>The <strong>LAG</strong> window function (present in most common SQL dialects) allows you to traverse to the column identified for a previous row grouped by a common partition and sorted in your desired order. Here’s some example psuedo code:</p><pre>LAG(event_tstamp) OVER (PARTITION BY attributed_user_id ORDER BY EVENT_TSTAMP) AS previous_tstamp</pre><p>This will produce the last timestamp for each event based on the attributed user id. We can then compare this to the event_tstamp of the current event and test if this is more or less than 30 minutes.</p><pre><em>TIMEDIFF</em>(second, <em>LAG</em>(event_tstamp) <br>       OVER (PARTITION BY attributed_user_id ORDER BY event_tstamp), event_tstamp) &lt; 1800 --30 mins</pre><p><em>Important to note, that if this event is the user’s first event, the lag function will return NULL, so you want to handle this when you assign the time_diff_flag.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/650/1*J0ci9I-vQy8bxW6d69W2tQ.png" /><figcaption>Example time_diff_flag</figcaption></figure><p>We can then use the SUM window function with a sliding window to compute the cumulative sum of the time_diff_flag column to group events together based on lag difference.</p><pre><em>SUM</em>(time_diff_flag)<br>    OVER (PARTITION BY attributed_user_id ORDER BY event_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS time_diff_partition</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/894/1*pymN1qkGKMBX64RwPnfTZw.png" /><figcaption>SUM window function with sliding window</figcaption></figure><h4>Change in marketing signals</h4><p>Change in marketing signals can be quite business specific, this requires knowledge of how your business is using marketing parameters to drive traffic. Snowplow enrichment service does extract utm params from url strings and harmonises them into standard fields mkt_medium, mkt_source etc, if you are not using their enrichment or want to have personal control of what url parameters get harmonised you can simply do this yourself by COALESCE’ing the parameters together, in my implementation I only needed to do this for paid ad click ids.</p><pre>COALESCE(<br>    a.url_parameters:gclid::VARCHAR,<br>    a.url_parameters:msclkid::VARCHAR,<br>    a.url_parameters:dclid::VARCHAR,<br>    a.url_parameters:clickid::VARCHAR,<br>    a.url_parameters:fbclid::VARCHAR<br>) AS click_id</pre><p>I advise discussing with your marketing and other traffic driving departments (eg. CRM) what are the explicit list of url parameters that are in use that would act as signals to identify new traffic. The list we devised was utm_campaign, utm_medium, utm_source, utm_term, utm_content and click_id. Any change in any of the above parameters would signal the start of a new session.</p><p>With each event you also have the referrer url, this is the url for the event that was previous to this event, these referrers can be internal to your website (if the event is not the landing page of a session) or external to your website (if this is the landing page of a session or part of an external redirect like a payment gateway).</p><p>Using the URL hostnames we categorised earlier, these categories can be used to identify the referrer medium of each event. The reason we use these is because the presence of url parameters are not enough, sometimes someone will return to the website without any url parameters, (this is usually from organic search or partner sites) when they do the utm params and the click id will often be null. Note that almost all events will have a referrer, so we utilise the hostname categorisation we done previously to only attach the referrer medium for hostnames that we consider are not part of our user journey within the website, eg. any other referrer medium than internal, payment gateway and oauth.</p><p>So our marketing signal for the initiation of a new session will be a combination of changes of shortlisted url parameters and the referrer medium.</p><p>Similarly to the time diff flag, I first create a field (marketing signals flag) that is a combination of the utm params discussed above and the referrer medium, this can be done using a CONCAT function. So the result will look something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-9VoSaSmNiQyRJHFWILorw.png" /><figcaption>Example marketing_signals_flag</figcaption></figure><p>One of the important features I leverage for the next step is the IGNORE NULLS option within window functions. The window function LAST_VALUE combined with a sliding window and the ignore nulls argument allows us to persist the flag down through the events that aren’t landing pages.</p><pre>LAST_VALUE(marketing_signals_flag) <br>    IGNORE NULLS OVER (PARTITION BY attributed_user_id <br>    ORDER BY event_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS marketing_signals_partition</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gQr0sQ7EB9eNLt9_Ardzzw.png" /><figcaption>LAST_VALUE window function with ignore nulls and sliding window</figcaption></figure><h4>Sessionising</h4><p>Now that we’ve computed the two main partitions for sessionisation to match the business’s needs all that’s left to do is partition based on the combination of preprocessed ‘partitions’.</p><p>I decided to identify my sessions by using the unique id of the first event in the session, this has a few benefits:</p><ol><li>We don’t need to recompute a unique id, each event is unique so the first id for any grouping of them has to be unique.</li><li>It is an easy link back to the original event stream to find event that is considered the landing event for each session for debugging and future enrichment.</li><li>It aids with incremental loading, when incrementally loading sessions you might find that you have to retract and reprocess sessions (because of late arriving or data processing started mid way through a session).</li></ol><p>With that in mind the final step becomes a simple FIRST_VALUE window function that is partitioned by the time_diff_partition, marketing_signals_partition and a device_platform.</p><p><em>Note even if you don’t want to sessionise cross platform events together you should still include the device type/platform in your partitioning logic because sessions can occur on multiple devices at the same time, eg. someone could be browsing on their phone and their computer at the same time.</em></p><pre>FIRST_VALUE(event_id)<br>    OVER (PARTITION BY attributed_user_id,<br>                       time_diff_partition,<br>                       marketing_signals_partition,<br>                       device_platform<br>    ORDER BY e.EVENT_TSTAMP)  AS session_id</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cBCCgIbuBQOjTCyklNGxCQ.png" /><figcaption>FIRST_VALUE window function</figcaption></figure><p>As you can see from the example above it has persisted the the event_id from the landing page event as the session_id for events that are grouped together in a session.</p><p>The output table generated by this process is an event level link table that will work as a way of joining session level tables to event level tables and be used for downstream modelling to aggregate events up to a session. I persist a few key fields that are helpful when switching grain between sessions and events.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S1At7TTaCYjE21r33lzXpg.png" /><figcaption>Example sessionisation table</figcaption></figure><p>Again thank you for taking the time to read this article, I hope these are helpful for anyone trying to solve the same problems. I welcome any feedback. On the next one I’ll talk about making session level attributes to help you categorise each session, which will feed into channelling and then attribution.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b7c324b6b843" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Identity stitching and member resolution]]></title>
            <link>https://robin-patel.medium.com/identity-stitching-and-member-resolution-d2fd22519a41?source=rss-e2768d1ab6d0------2</link>
            <guid isPermaLink="false">https://medium.com/p/d2fd22519a41</guid>
            <category><![CDATA[attribution]]></category>
            <category><![CDATA[sessionize]]></category>
            <category><![CDATA[event-tracking]]></category>
            <category><![CDATA[identity-resolution]]></category>
            <category><![CDATA[data-modeling]]></category>
            <dc:creator><![CDATA[Robin Patel]]></dc:creator>
            <pubDate>Wed, 04 Nov 2020 23:14:15 GMT</pubDate>
            <atom:updated>2020-11-04T23:14:15.901Z</atom:updated>
            <content:encoded><![CDATA[<h4>The bigger picture behind how users are interacting with your site</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jwFv2Aatz7K0RWgmzVauFw.jpeg" /></figure><p>Identity stitching is an a key part of understanding how people frequent your website and what it is they are doing. In this article I am going to discuss the concept of identity stitching, my method of implementation and things to be conscious of given the personally identifiable information (PII) we are handling. Identity stitching can then play an invaluable role in sessionisation and attribution.</p><h4>So what is identity stitching?</h4><p>Identity stitching is a process that enables us to enrich events where we don’t know the identity of the person, with data collected and processed elsewhere or after the fact to associate these unidentifiable events to a person.</p><p><em>I stress at this point that the focus here isn’t to find out what John Smith is doing but to develop a system where we can have a better understanding what people as a group(s) are doing, and to emphasise this point I still recommend implementing identity stitching even if you choose to anonymise to </em><strong><em>all</em></strong><em> PII data.</em></p><p>Lets use a crude example to explain what identity stitching helps us understand:</p><blockquote>John Smith is sitting on the train, starring down at his feet and decides its time for a new pair of trainers. He gets out his phone, starts by using a search engine for inspiration “trainers in fashion” and is returned a set of results. At the top of the results is a paid ad from your company “Trainers in this season by www.bestkicks.com”, and it’s a site he’s never been to before. John proceeds to click your ad, look around your website, finds a pair of trainers he likes but decides he’ll wait to get home and show his partner before he rushes his decision. When John gets home he goes to www.bestkicks.com and shows Jane Smith these new trainers, she also loves them so John proceeds to purchase them.</blockquote><p>Now this is a pretty basic example, but the pattern of events is something that repeats multiple times for Best Kicks, a person is exposed to the brand by an ad presented in a search engine, they then come back directly to the website after a bit of time for consideration and purchase. At the face of it there isn’t a problem, an ad is exposing a person to the brand then they come back and purchase after a bit of time. The user has a cookie id or a device id so we are able to associate the booking to the ad click by attribution. However what happens when instead of making the booking on the same phone he originally browsed on John goes home and opens the site on his laptop or tablet to get a better sized image of the trainers? the data behind this journey then becomes disjoint. The ad session occurred on his phone and the company has no known association between the two devices, the data presents as one session where an ad generated the visit that didn’t convert and another session where a user came directly to the company website and bought trainers with no association between the two. This is where identity stitching comes into action, the process will allow us to associate the phone traffic to John and therefore understand that the ad was the source that drove John to the website to begin with.</p><p>This gives us a better understanding of the complexities behind the modern user journey to a purchase/conversion.</p><h3>Implementation</h3><p>The foundation of my implementation was on the premise that every event will have at least some sort of ambiguous identifier like a cookie id or device id, they <em>may </em>also have a more specific identifiers which we can use in identity stitching.</p><p>These ambiguous identifiers are set in your tracker implementation, many trackers have a default version of this that is fulfilled by a client side cookie initiated on a user’s device that will persist for a certain amount of time, the longer the better.</p><p><em>A colleague of mine was able to implement one better, a browser id, this is an id that is computed and is unique to the each browser on a device and will therefore persist for the length of time the user uses that browser on that device.</em></p><p>The first thing I done was identify all the identifiers we were tracking, these were things like cookie id, browser id, user id, domain user id, idfa, email address etc and sort them into two groups ‘<strong>known identifiers</strong>’ and ‘<strong>unknown identifiers</strong>’.</p><h4>Known identifiers</h4><p>These are identifiers that exist on an event that we can use to associate traffic with an actual person, examples of these are things like email addresses or internal user ids. Known identifiers aren’t always present they normally only occur when the user does something in their journey that requires them to affirm who they are like submit a form, login or purchase etc. In the modern world where the user experience should take precedence on a site these are becoming less and less enforced to create a frictionless user experience but their presence is key in us understanding who the person behind the traffic is.</p><p>At this point its important to note that these identifiers can be PII, with that in mind we need to be conscious of how we collect these - for user safety, and how we store them - for <a href="https://ico.org.uk/for-organisations/guide-to-data-protection/guide-to-the-general-data-protection-regulation-gdpr/">GDPR</a> compliance. Especially things like email addresses, we want to be certain we are encrypting these where ever possible and documenting where they are stored.</p><h4>Unknown Identifiers</h4><p>A form of unknown identifier can be found on each event, examples of these can be cookie id, device id, idfa, unique browser id etc. Although these on their own don’t tell us much about who a user actually is, they allow us to group events by a ‘user’ together and will be used as a mapping key to join known identifiers to events should a mapping be identified.</p><h4><strong>Hierarchy of Identifiers</strong></h4><p>The basis of the hierarchy is that some identifiers are more valuable than others, by valuable I mean they tell us more about a user than others or simply make downstream modelling easier. Generally, known identifiers are more valuable than unknown identifiers, this can then be broken down further within these groups. The importance of specifying this hierarchy influences our implementation, after listing all our identifiers, splitting them into known and unknown identifiers we then note the order of importance of each of these identifiers (eg. user id is more important than email address, and email address is more important than cookie id).</p><p>Having established the foundations of identifiers we can now build a process where we record all distinct combinations of identifiers as they occur, as we record new combinations we continue to add these to this table, lets call this the identity associations table. This allows us to build up a mapping of identifiers as we learn more about our users. At this stage our only concern is to collect distinct combinations, we want to decouple any identity stitching process as actual identity stitching can be an iterative implementation process and it can also be enriched from other data sources.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_Led7_-8LZKoSe7TQY3RGg.png" /><figcaption>Example identity associations table</figcaption></figure><p>In this example you can see that the cookie id ‘<em>a5a87af1-aa62–4b99-ad63–9bb81656da0f’ </em>was first recorded without any other identifier information other than the cookie id. Two days later, this same person comes back to the website but decides to log in, so we now add a new row to this table where we’ve recorded the new combination of cookie id with a user id. You can already see that if we simply look for any previous traffic that has occurred with this cookie id we can now attach a user id. We also want to store the first recorded timestamp of each combination, this will help us de-dupe later on.</p><h4>Identity Stitching</h4><p>With an associations table that has recorded all the unique combinations of identifiers we can now build a simple process referencing our hierarchy we decided earlier, for each unknown identifier we assign the ‘<strong>best</strong>’ identifier based on this hierarchy. I preserved this identifier in a generic field called ‘attributed user id’, this field can have any variation of our different unknown/known identifiers depending on which ever has been assigned. I also created another column ‘stitched identity type’ that records what identifier in the attributed user id represents, this is useful later on for filtering traffic based on how well we can categorise the user.</p><p>This table will be used to join onto any event, as we already know each event will have an unknown identifier of some sort so we can join on the unknown identifier onto our identity stitching table to enrich our events with a ‘better’ identity if evidence of one exists.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ci200gTFoR9onsfkQ_XWRg.png" /><figcaption>Example of identity stitching table</figcaption></figure><p>There may be a couple of outlier situations you need to cater for and these might be more or less apparent based on your business model. The classic example is ‘shared devices’, when two people (users) have logged in on the same device. What this looks like in our data is that your identity associations table will frequently have multiple different known identifiers for the same unknown identifier, we can choose to handle this in different ways but the most sensible approach would be to assess the situations that occur for example you might have a business model where multiple logins on the same device is expected, therefore you might want to take the association that occurs the most. The end goal here is to only ‘stitch’ to one identity otherwise you will end up duplicating out data in downstream modelling so you might need to assess what method best fits your business model.</p><h3><strong>Things to note</strong></h3><p>Couple of things that are worthwhile pointing out:</p><ul><li>If you aren’t able to create a unique browser id, the lifecycle of your client side cookie is quite important. The longer the lifespan the higher level of resolution you will return. If for example your cookie only lasts for 2 weeks, a customer can browse your website without logging in, come back after two weeks -at which stage their cookie would have expired- and will be given a new cookie (with a new cookie id) and you won’t be able to stitch their previous journey.</li><li>Client side tracking itself contains a mass of interesting information about the user, however its well within a user’s rights to restrict your access to recording this information. They are also within their rights to simply block this information being sent to you entirely, with that in mind your ability to stitch their identity can become an extremely difficult task. We used Server Side tracking to help overcome the issues presented by client side tracking limitations.</li><li>This process can evolve to include information from other sources, if for example there’s a large proportion of traffic where we are only able to associate the traffic to an email address, but (in accordance to our hierarchy) we ideally want to get traffic up to an internal user id level. We can simply reference another internal user table (collected elsewhere) that stores an email address with a user id to further update our identity stitched table with user ids.</li></ul><h3>Next Steps</h3><p>A high level of stitching will not be immediate, the nature of the process is simply the more we collect; the better it gets. All it takes is for us to be able to associate a user on a device to a known identifier once for us to be able to reclassify all traffic on that device to that person. With that in mind we want to promote ways within the user journey that can allow us to collect this information as often as possible whilst making sure our user experience with the site isn’t compromised to do so.</p><p>I’ve witnessed some creative ways to intertwine these into the user journey without impacting their experience here are some examples:</p><ul><li>Embed a user identifier into a hyperlink included in email communications, this way when a user clicks on a link within a promotional email the internal user id is embedded into their landing page URL, so even if they don’t log in we know who this user actually is. This is particularly helpful for businesses where the vast majority of their conversions occur on a personal computer, the theory being that a lot of the inspiration towards the conversion occurs on a mobile device if a person never logs in or transacts on their mobile device then there will never be any record to associate the traffic to this mobile device to the user that then transacted on their personal computer.</li><li>Create promotional content on a website that requires user to login using an email address or other social media authentication. The goal here is to keep it to bear minimum where essentially all you need to do as a user is press a button or enter their email address.</li><li>Competition forms, now this isn’t the primary goal of competitions their purpose should still be around PR and customer acquisition but whilst you’re servicing the competition it makes sense to also collect this information to better understand what devices users are using.</li></ul><p><em>If you know/think of anymore I’d love to hear them.</em></p><p>Thank you for taking the time to read this, if you haven’t seen my other pieces this article is part of my implementation approach to modelling raw event data up to useable and scalable data assets for business intelligence level reporting:</p><ul><li><a href="https://robin-patel.medium.com/modelling-snowplow-event-data-e0306d63b491">Modelling Snowplow event data</a></li><li><a href="https://robin-patel.medium.com/playing-with-url-string-data-75ba14ff1e0">Playing with URL string data</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d2fd22519a41" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Playing with URL string data]]></title>
            <link>https://robin-patel.medium.com/playing-with-url-string-data-75ba14ff1e0?source=rss-e2768d1ab6d0------2</link>
            <guid isPermaLink="false">https://medium.com/p/75ba14ff1e0</guid>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data-architecture]]></category>
            <category><![CDATA[url]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[event-tracking]]></category>
            <dc:creator><![CDATA[Robin Patel]]></dc:creator>
            <pubDate>Thu, 22 Oct 2020 23:10:36 GMT</pubDate>
            <atom:updated>2020-10-28T12:49:02.947Z</atom:updated>
            <content:encoded><![CDATA[<h4>Modelling URL strings ready for sessionisation, channelling and even user funnels</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OBLDnIRx7mkaWJjppzYOHw.jpeg" /></figure><p>In my <a href="https://link.medium.com/bnGLW5V8Bab">previous article</a> I briefly touched on the golden chalice of information that lives in URL strings, I wanted to take some time to elaborate on that and how I’ve used URL strings in data modelling for things like sessionisation, channelling, user funnels and ad hoc analysis.</p><p>A good friend of mine read my previous article and was particularly surprised that so much information is in a URL string and in turn how much could be interpreted from them if tracked. So let’s start by going over the basic structure of a URL so we can understand how to read them and then focus on how we can design and build a multi purpose dataset.</p><h3>What is a URL?</h3><h4>Covering the basics</h4><p>URL stands for uniform resource locator but it’s commonly known as a website address. We’ve seen them everywhere, they are conventionally formatted like so ‘www.websiteaddress.com’. When you go to the address bar of your browser and type in a website address hit go to get to a website this is called direct traffic.</p><p>Direct traffic only really happens if you know the brand/company’s website address (or perhaps seen it on a printed ad), this traffic is normally a representation of people who have been to the website before, trust the brand and choose to return directly. With that in mind from the company’s perspective in relation to direct traffic haven’t they already fulfilled their primary marketing objective (secondary being reacquisition)? You’re already going directly to their website, they don’t need to advertise to you and pay to acquire you as a customer. So what about new customers? People who haven’t been exposed to your brand before, this traffic is normally generated by users clicking a link given to them by some other means.</p><p>The more ‘interesting’ traffic and the traffic the company can do something to influence is non direct traffic. This is everything else, it is when you arrive on a website via a link you’ve clicked in an email, the result of a search, a mention you’ve seen on your favourite blog (insert hyperlink here wink wink), an ad you’ve seen on another website you frequent or even a post on social media.</p><p>Now this is where things get fun… (<em>I realise I’m talking about URL strings when I say that</em>) in order for businesses to understand what works in generating non direct traffic they need to be able to quantify the different types of non direct traffic coming to the site. This is where URL strings come in, you may have noticed that when you come into a site via another source the URL in the address bar looks slightly different to when you enter it manually (not always), this is because the URL string itself can be used to carry information about where you came from.</p><p>Let’s talk about the basic URL structure, so we already discussed direct traffic where you enter a URL www.websiteaddress.com, this part of the url is called the domain or hostname. As you probably already know, conventionally a domain takes you to the homepage of the website, but as soon as you start to navigate to other pages within the website (like a search or product page) you will start seeing appenditures to the URL string in your address bar, these are identified by a ‘/’ after the domain eg ‘www.websiteaddress.com/search’ these can continue to append as you traverse through the site’s navigation tree eg. www.siteaddress.com/search/results. As you can identify there’s already a use case for URL strings, you can identify what page was viewed. This forward slash list is called the <strong>page path</strong>.</p><p>In addition to the domain and page path you sometimes you have custom parameters appended to the end of the URL string. These parameters are used for a multitude of things, their main use purposes are to either categorise what was the source that brought you to the site or custom parameters used to adjust what is shown to you on the site. So if you search for a particular product, set filters on what is displayed or insert information in a website form these are normally facilitated using URL parameters. These parameters get appended to the end of the URL and are separated from the page path with a ‘?’, there can be many of these attached and when this happens these are delimited by a ‘&amp;’, the pattern is parameter1=parameter_value1&amp;parameter2=parameter_value2 so for example: <a href="http://www.websiteaddress.com/search/results?products=discount&amp;price_bracket=under_50.">www.websiteaddress.com/search/results?products=discount&amp;price_bracket=under_50.</a> This portion of the URL is called the <strong>query.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*xlQo1V2Ag8Eyf7Ue.jpg" /><figcaption>Image source<em>: https://sitechecker.pro/</em></figcaption></figure><h3>URLs that are tracked on an event</h3><p>In my last post I mentioned that for every page view event there are two URLs tracked (this is the case for most conventional tracking systems), the page URL which is the URL string of the page you are on, and the referrer URL which is the URL for the page that was previous to the current one. The referrer URL for most of your traffic is just going to be another page on your website because every page except the landing page of a session came from a page within your site, if you go from the homepage to the search page the referrer URL for the search page event is going to be the URL of the homepage. This might seem like we don’t need to collect this once a session starts but it helps us identify if this page view event is the first event of a session or not, if there was no referrer url how would we know if a person navigated to this page via links within our website like the search button or arrived on this site by a link posted elsewhere? If it doesn’t contain a URL that includes the site’s own domain then it must have come from elsewhere. We can also use referrer URLs to identify user funnels to see how traffic is navigating through our website.</p><p><em>Small disclaimer at this point, the presence of a referrer URL that is from our own site (determined by the domain in the referrer URL) isn’t enough to constitute whether or not this is a new session, I will tackle that in a sessionisation piece</em>.</p><p>Something I didn’t explain was how entering a website address directs you to the right website, <a href="https://link.medium.com/4nBQ7CXGDab">this is a great article</a> I found that explains the technical side of servicing a request for a website address.</p><h3>Modelling URL strings</h3><p>Right, now the basics have been explained let’s talk about how we use URLs and how we can structure them in a way that is reusable.</p><p>The first thing I done was collate a unique list of URLs that have been tracked on our site, this consisted of page URLs and referrer URLs, this list then becomes the base list for all URL processing.</p><h4>Categorising domains</h4><p>Domain categorisation is the process of bucketing the domains tracked into groups, I identified that there were various instances where categorising the domains you have tracked can assist in downstream modelling (eg. sessionisation and channelling).</p><p>There are some general rules that can be used to bucket external domains (and there will likely be a really long tail) but start by categorising internal domains, these are domains that are <strong>within</strong> your company. Some companies only have one (so that’ll be easy) but others have at least a handful, labelling internal domains means we have a high level grouping to easily identify internal traffic.</p><p>Next I ordered the remaining domains by quantity of occurrence, this helped identify the big groups of external traffic eg www.google.com -search, www.bing.com -search, www.facebook.com -social. I calculated how many domains make up the entire amount of external referrer URL events and set a goal of actively bucketing 80% (determined by flattening of the curve) of them into a category the remaining will go into an ‘Other’ category, aiming for 100% is not a sensible goal as it will be and extremely long tail list for most use cases. I worked down the sorted list categorising domains until I achieved the goal. The resulting code was a CASE WHEN statement that leveraged a combination of explicit and regex conditions. The result was approximately 8 categories plus internal.</p><p><em>Note that these categories are </em><strong><em>not</em></strong><em> channels, we aren’t channelling yet, but they will be used in channelling. Also this categorisation will need occasional attention in the future, these categories change with the times (albeit quite slowly) and your business’ definition of them might also change.</em></p><h4>Extracting URL parameters</h4><p>Extracting parameters from a URL is quite straight forward on modern cloud data storage providers. They have URL parsing functions built into their SQL dialect that can be leveraged, however if your provider doesn’t have one you can simply extract the data programatically using a series of regex functions as long as you understand the pattern of a URL string.</p><p>I am using Snowflake and they have a <a href="https://docs.snowflake.com/en/sql-reference/functions/parse_url.html">PARSE_URL</a> function (here’s one I found on <a href="https://docs.snowflake.com/en/sql-reference/functions/parse_url.html">Google Big Query</a>). The function deconstructs the URL passed in the argument into elements (some of which I explained earlier).</p><p>Let’s see what it does to this example URL: <em>https://www.amazon.co.uk/gp/buyagain?ie=UTF8&amp;ref_=nav_cs_buy_again</em></p><pre>SELECT PARSE_URL(‘https://www.amazon.co.uk/gp/buyagain?ie=UTF8&amp;ref_=nav_cs_buy_again&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/788/1*A04w0CrbCtytAJBkvsijzw.png" /></figure><p>This combined with the Snowflake’s ability to easily <a href="https://docs.snowflake.com/en/user-guide/querying-semistructured.html">query semi structured</a> datasets makes it really easy to extract the query parameters from the url into an object.</p><pre>SELECT <br>PARSE_URL(‘https://www.amazon.co.uk/gp/buyagain?ie=UTF8&amp;ref_=nav_cs_buy_again&#39;):parameters</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/776/1*y5sljj0t-3wGdtW_nhTnZA.png" /></figure><p>Leaving this as an object against the url makes life easier down the line to lift out parameters of interest (eg. utm parameters) later on.</p><pre>SELECT <br>‘https://www.amazon.co.uk/gp/buyagain?ie=UTF8&amp;ref_=nav_cs_buy_again&#39; AS url_column,<br>PARSE_URL(url_column):parameters AS url_parameters,<br>url_parameters:ref_ AS ref__parameter</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dSJ3-0K60ZkBITMdH_cqfg.png" /></figure><p>If you’re interested in Snowflake’s has the ability to query semi structured data, I would recommend investigating their FLATTEN function, it allows you to explode a compound object into multiple rows.</p><h3>The Result</h3><p>These two processes can be combined to output a table that has a distinct row for each URL we’ve tracked, the domain of that URL, the URL domain categorisation for that URL, the page path of that URL and a compound object list of all the parameters within the URL.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aoXbmPGp8-bvUxYwqEog6A.png" /><figcaption>Example row</figcaption></figure><p>I’ve also attached some additional meta data about the URL to assist with future queries eg. url_has_query, url_is_valid, first_tracked_tstamp etc. This table can then be used for a variety of purposes.</p><p>In sessionisation we use this table to identify if an event comes from an external source or an internal one, and we use it to check if a user has come back into the site on a different set of marketing parameters than they originally did.</p><p>After sessions have been defined we again use this table to identify parameters in the ‘landing page’ url of each session which enables us to group sessions into channels.</p><p>URL parameters can also be utilised for a whole host of different ways, a prime example is to use them to identify a particular type of traffic (eg if a person logged in or is in a test group) so having a quick way to query all the parameters available in a URL becomes invaluable.</p><p>As stated earlier we can also use URL strings to identify common conversion funnels and funnels that require attention. We can do this by analysing the page path of each url via a chronological index of events in a session to see what our common funnels are, common conversion paths or even our abandon paths.</p><p>Thanks for taking the time to read this, as I’ve mentioned elsewhere my purpose with these articles is to merely describe my approach to tackling issues/projects, structuring URLs is a pretty basic process but becomes a foundational piece in modelling event data. I appreciate all criticism and feedback.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=75ba14ff1e0" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Modelling Snowplow event data]]></title>
            <link>https://robin-patel.medium.com/modelling-snowplow-event-data-e0306d63b491?source=rss-e2768d1ab6d0------2</link>
            <guid isPermaLink="false">https://medium.com/p/e0306d63b491</guid>
            <category><![CDATA[identity-stitching]]></category>
            <category><![CDATA[data-modelling]]></category>
            <category><![CDATA[attribution]]></category>
            <category><![CDATA[event-tracking]]></category>
            <category><![CDATA[sessionize]]></category>
            <dc:creator><![CDATA[Robin Patel]]></dc:creator>
            <pubDate>Thu, 15 Oct 2020 21:52:56 GMT</pubDate>
            <atom:updated>2020-10-15T22:01:38.489Z</atom:updated>
            <content:encoded><![CDATA[<h4>Turning raw events into useable datasets to provide management level metrics whilst preserving flexibility</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/930/1*4YZO23a1WE3nsfZzfJoWHw.jpeg" /></figure><p>If you are using or have heard about <a href="https://snowplowanalytics.com/">Snowplow</a> tracking then you will have inevitably heard if its advantages compared to ‘out of the box’ solutions from its competitors. You finally own your own data. You have free realm to grab insights whichever way you see fit, pursue ad hoc analysis, identify issues in your user journey, have access to highly customisable tracking, that sounds great right?? Well it is, but to get the data to a stage where it can answer management level metrics or replace an incumbent system takes some work, processing and a lot of data modelling. Snowplow has a lot of ready to use metrics (some you can pay for as a service) but as you expect not all of them are suitable for every business case.</p><p>This was exactly the case for me, we loved being in charge of our data and the freedom we had to interpret it the way we needed but the Snowplow ready-to-use metrics weren’t ready to provide the management level business metrics we needed. There’s plenty of businesses out there that might be absolutely fine with the default metrics Snowplow provides (and in my previous job this was the case) however the ownership of your own data means you have free realm to set it up any way you see fit to answer the questions and support the numbers you need.</p><p>The power to manipulate all of your data is a slippery slope so tread mindfully, you can easily end up building a system that is only fit for ‘current day’ purpose and only really capable of answering the questions you are asking right now. So during our implementation of this modelling process we kept this at the forefront of our design to ensure we weren’t accidentally reintroducing the limitations incumbent systems have and also create something that was truly scalable for our evolving business needs.</p><p>The point of this article is to outline the blueprint I undertook to model Snowplow event data up to usable datasets for management level business metrics whilst preserving the atomic nature and grain required for flexible insight.</p><p>There was a lot of intermediate modelling steps but they can be broadly categorised into these areas:</p><ul><li>Data preparation</li><li>URL domain categorisation and parameter extraction</li><li>Identity stitching</li><li>Sessionisation</li><li>Channelling</li><li>Attribution</li><li>Events of interest</li></ul><p><em>Note for this particular implementation my data stack was Snowplow with both client side and server side tracking invoked across Web and App, Snowflake for our cloud data warehouse, a python data pipeline for execution and Apache Airflow to orchestrate.</em></p><p>I will likely share smaller more succinct articles on the individual steps and their implementation to keep this article as an overall encapsulation of the process.</p><h3>Where do I start?</h3><p>The best place to start is to ask “do I need to do this?”, as with any business case, you should first identify if you need to undergo a project like this. There’s enough in Snowplow’s default metrics to get you some level of insight (quite a substantial amount actually) and in some situations it would play well to be pragmatic and say “we’ve got everything we need”. We quickly realised we didn’t have everything we needed, we wanted to identify members regardless of if they had logged in or not and we had some specific definitions of a session we wanted to invoke. This coupled with a legacy issue of data quality due to incorrect implementation of the snowplow tracker meant our data wasn’t fit for consumption.</p><p>The company committed to using snowplow data as our main source of Web/App analytics so we took steps to modelling the raw event data from Snowplow.</p><h3>Data preparation</h3><p>Before embarking on any modelling tasks its good practice to decouple any data preparation tasks from modelling. This ensures all downstream modelling jobs are working on clean and ready data.</p><p>As mentioned above our first issue was data quality, this was nothing to do with Snowplow and how it tracks, it’s just due to it always being -for us- a secondary data source, it never got the attention it deserved. So historically our trackers weren’t implemented correctly, this resulted in a lot of dirty data. We had to clean this up, this mainly consisted of standardising things, device platforms, implying the correct timestamp for the event, extracting important key values from contexts etc so they can be used with ease and confidence downstream. We called this process ‘hygiene’.</p><p>We also identified a fairly significant amount of duplicated rows, trackers can return the same rows multiple times and this is normally due to multiple collectors implemented to handle the event load. Despite having Snowplow’s deduplication service in place, some duplicate rows were still coming through (we did identify the reason for this but I won’t bore you in this post). These duplications meant it would be difficult down the line to identify a single event, because the snowplow event_id wasn’t actually unique. So our first task was to make a primary key, we identified all the parameters returned by each event that would identify it as being unique (eg. Tstamps, device, event type, user identifiers like cookie id/idfa etc) hashed these together using a SHA2 encryption and voila you have a unique identifier for each row. This meant we could also do our own internal deduplication and reference each individual event if we need to.</p><p>Clean indexed data sets the foundation for the downstream tasks. The output of this step (we named it the event stream) is still event level, but now it has some standardised columns and a unique identifier for each row.</p><h3>URL domain categorisation and parameter extraction</h3><p>There are two forms of url stored against each event, the page url (url of the page you are on) and the referrer url (the url of the page that referred you to this one). I first stacked these on top of one another to create a complete distinct list of urls we’ve tracked, this list then fed into two parallel processing modules.</p><p>The first was to create a categorisation of url domains/hostnames based on a blend of standard tracking and business knowledge. The categories are broad so it helps to keep them simple, eg search, partner, email, internal etc (<a href="https://support.google.com/analytics/answer/3297892?hl=en">here’s GA’s default channels</a>). The purpose of this dataset would be to allow categorisation of traffic particularly for channelling and sessionisation.</p><p>The second process was to extract parameters out of each url string, these exist in the query portion of the url (after a ‘?’, each parameter is denoted by a prefix of ‘&amp;’ and the value of the parameter is prefixed with a ‘=’). My manager at the time pointed out that the url string is usually a plethora of useful information and I’m glad I listened to her. It can contain marketing information for traffic arriving from external sources, it can contain session variables the user has set to refine search results on your site and even tags from A/B tests. So extracting these is essential to categorise the type of traffic or even attach qualitative attributes to a user’s session (eg user made a search).</p><p>I used Snowflakes <a href="https://docs.snowflake.com/en/sql-reference/functions/split.html">SPLIT</a> function to split each query into an array which I could then flatten into rows of keys and values, I then then used <a href="https://docs.snowflake.com/en/sql-reference/functions/object_agg.html">OBJECT_AGG</a> (Aggregation function) to create an object column for each url that listed the parameters of each url into keys and values. This meant we could query any parameter for each url with ease, this is especially important for channelling. Arguably it can be said that we don’t necessarily need <strong>‘all’</strong> the parameters, we could just extract the marketing ones necessary for channelling but as I said before, there’s tons of useful information in there and “you don’t know what you don’t know” so it makes sense to package them all up incase we need to reference them for other things.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Y2D61TVhrHdh7QIoLCJvdQ.png" /><figcaption>Example url_parameters column</figcaption></figure><h3>Identity Stitching</h3><p>Identity stitching is an important part of identifying the complete journey a user had with your site and therefore a key element in sessionisation. When a new user lands on your site, conventionally you have no identifiable way of associating them to a person(s), member, customer etc. However as they continue throughout their session (or on subsequent sessions) there might be a particular event where you might be able to better associate their identity (eg. login page, transaction page) which allows you to categorise them and their traffic better.</p><p>The main components I used to set up identity stitching were known identifiers, unknown identifiers and the hierarchy between each of them.</p><p><strong>Known identifiers</strong> are identifiers that are known internally to your company. Its a way of associating events to an actual member/customer eg. user_id, email_address, booking_id.</p><p><strong>Unknown identifiers</strong> are still ways of associating a set of events to the same person, however the value of the unknown identifier has no specific benefit to you or the company eg cookie_id, unique_browser_id, idfa. One of the key obstacles to better associate traffic is to try and get unknown identifiers to persist on a user for as long as possible. Conventionally this is done via a cookie_id within a long-life snowplow cookie, but this does have its limitations (I advise you to research these limitations and how changes in regulations of tracking effect your ability to identify a person this way). The option we went with was to add a new user context to our snowplow tracker that would return the unique identifier of a browser (ubid). This was the most persistent identifier we could invoke, and it effectively means that if that specific browser (specific to a device) visits our website it will return the same ID every-time.</p><p>The obvious difference between the known and unknown identifiers is that unknown identifiers should be available on almost every event (if not all events). Known identifiers are only available when the user has triggered an event that enables us to capture this known identifier.</p><p><strong>The hierarchy </strong>is the key part here, it allows us to infer who a person is based on our best mapping of unknown identifiers to known identifiers. If a person visits your site but doesn’t do anything to trigger a known identifier, we still can associate their traffic to the unknown identifier (which will allow us to sessionise), but as soon as they do trigger an event that allows us to map an unknown identifier to a known identifier we can then reassign all traffic that was associated to that unknown identifier to this new known identifier. This allows us to build up a picture of what our users done on the site before they gave us any better way of identifying them.</p><p>So I created a table that consisted of each of the known and unknown identifier we have as each column, when events arrive that have a new combination of known and unknown identifiers we add these to the table, and if new events arrive that allow us to map unknown identifiers to known identifiers we update the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f0gg7lD9nI4iDQZ29pEdsg.png" /><figcaption>Example identity_associations table</figcaption></figure><p>Once this has been populated, if we work on the premise that every event should have at least one unknown identifier, we can attach this table to event level data on a COALESCE of <strong>unknown</strong> identifiers and return a COALESCE of <strong>all</strong> identifiers in <strong>order of hierarchy</strong> (known and unknown) to return the ‘best’ stitched identity we have for the unknown identifiers on each event. Note this might be a user id, email address etc but it also might only ever be a cookie_id or unique_browser_id if the person never logs in.</p><p>I also decided to flag what the ‘best’ stitched identity type was, this allows us to segment data later.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zWjedTTTZKGYmozA9vEabw.png" /><figcaption>Example of ‘best’ identity association</figcaption></figure><p>At first identity stitching won’t yield much (in terms of better identifying the user) but as soon as we start adding to this table we begin to build up a historic mapping of users and can associate traffic to a user regardless of if they’ve logged in or not as long as they’ve logged in on that device once.</p><p>Disclaimer, it was noted that this wasn’t a fully complete piece of work, we were aware that if we wanted we could further enrich associations from other data sources to improve our stitching ability however at this stage this was deemed low priority.</p><h3>Sessionisation</h3><blockquote>A session is a group of user interactions with your website that take place within a given time frame. — Google Analytics</blockquote><p>Sessionisation is the term used for processing a group events into a session. Snowplow has a session id returned by the client side tracker to group events together, however it’s quite basic, it has a 30 min window of inactivity before it starts a new session and that’s about it. This time lag condition is better than <a href="https://support.google.com/analytics/answer/2731565?hl=en">Google Analytics definition</a> of 30 mins unless it’s over the midnight threshold, but GA also restarts a session if a user returns to the site via a different source within 30 mins. So we needed to build a happy medium that would accommodate for sessions that have almost an equal possibility of occurring at any time of day (global company) as well as aligning marketing initiatives to ensure the correct channel gets awarded the return on investment (ROI).</p><p>To do this we identified the parameters that would define our session, we group each user’s events together, order them chronologically and then separate them into partitions (sessions) by the following conditions:</p><ol><li>A time lag of 30 mins between two consecutive events</li><li>Returning to the site with new marketing parameters or referrer</li><li>Change of device</li></ol><p>To do this we leverage the three previous datasets, we use the identity stitching module to group events together by user, the url tables to identify the source of traffic and event stream for event timestamps and device type of each event .</p><p>If any of these occur or don’t occur is how we tell if the event is within the current session or constitutes as the start of another session, and we can set the event hash as the session id, as we already know this is unique.</p><p>Using this knowledge and a series of window functions we are able to create two datasets:</p><ul><li>Event to session join table, where we have the primary key for event in the event stream and a session id that each event belongs to</li><li>Session level dataset that is purely session attributes eg session id, landing page url, start timestamp, event timestamp, attributed user id and identity stitch type.</li></ul><h3>Channelling</h3><p>Channelling is the process of assigning a channel to each session, this enables reporting of traffic by its source in categories. These categories can differ from organisation-to-organisation but many of the generic ones should still be relevant eg. Direct, Paid Search Brand, Paid Search Non Brand, Organic Search, Social, Paid Social, Email, Partner/Affiliate etc.</p><p>To do this we leverage the landing page of each session, we combine this with the extracted parameters of the landing page url and the referrer categorisation of the landing page referrer url.</p><p>In my experience the best way to do this is to identify your big sources of data, associate rules for categorising this data, implement it and then move onto the next big source.</p><p>With all the hard processing work done earlier to on urls means that this step just becomes a glorified CASE WHEN statement eg. when no params and no referrer then ‘direct’, when utm_medium = email then ‘email’ etc, we decouple this table from the session attributes table so that the channel can be inferred by the attribution model of choice later on. The result is a table that has a session id, the marketing params of landing url, referrer categorisation from the landing page referrer url and the marketing channel.</p><p>The result of this step is a decoupled dataset with a clear channel for each session that associates with a last click attribution model.</p><h3>Attribution</h3><p>Standard attribution broadly lives within two groups, single touch and multi touch attribution. Single touch simply reattributes the traffic of a session from one channel to another channel, multi touch attributes it to several and assigns a weight to each.</p><p>Reattribution of traffic is basically where you redistribute the success of a channel to another contributing channel(s). Organic social for example has been widely proven to expose customers to a brand, now they might not convert on that first exposure (in-fact its quite unlikely they will) but it exposed the customer to the brand and therefore increased the possibility that that user will convert with said brand. It’s more common that a user will come back directly to the brand at a later date after being exposed and then transact/convert. If we don’t redistribute success of traffic then the contributing channels that are part of the top of the conversion funnel won’t get the credit deserved and this ends up cyclically impacting decisions on which channels we optimise for or spend attention on.</p><p>I won’t go into the specifics of attribution models (I’ll save that for another article) and instead discuss how I implemented the most common, last non direct attribution.</p><p><strong>Last non direct attribution</strong> is where you redistribute direct traffic to the last ‘non direct’ channel a user came in on (if any) within a certain look back window. It’s a form of single touch attribution that simply put says instead of assigning this conversion to direct, let’s assign it to the most recent non direct channel this person made prior to this session within a certain amount of time.</p><p>Having done a lot of the hard modelling work before hand attribution can now be computed by just looking at session level data and each session’s channel. The fact that we decoupled the channelling table from the session attributes table means that the result of this processing step will output a table that is simply a Join table, that will join the session id in the session attributes table to the session id in the channelling table with the additional columns of attribution model and attribution weight. To implement last direct attribution we first identify all direct sessions (because these are the only ones that are going to be reattributed) then using the session’s user we look at sessions prior to this one within a look back window of 6 months. All other session channels should simply link back to the original channel (same session id). We add the a column to specify the attribution model, so this dataset can handle multiple attribution models and add a default weight of 1 because this is single touch attribution.</p><p>With this combination of the session attributes table, the session channelling table and this new attribution table we are able to compute last non direct attribution channel and last click.</p><h3>Events of interest</h3><p>So this is a notion that we kind of just decided to do, it’s not something I’m aware is implemented widely. The need stemmed from the fact that the company uses a certain tracking metric throughout a lot of business reports (this is simply count of page visits by product page), it fed into trading and was included in performance reports sent to suppliers. We had an incumbent MongoDB tracker set up to record page views to these pages but nothing else was tracked (not where they came from, the device etc) other than the product id, a timestamp and a user id if the user had logged in. What we were trying to achieve is still report on metrics the way we used to but now add the additional possibility to split these page view numbers by session level attributes.</p><p>Firstly we identify the page views of interest in the event stream using business logic to filter them, then we attach the session id to each event using the events to sessions table. We persist a few key attributes of the event eg page url, event timestamp, product id and that’s pretty much it. The purpose of this table is that we know that each row in this table is an event of interest and therefore we can still create the same reports the MongoDB tracker outputted but with the additional possibility of attaching session level data to it via the session id. With the session id we can join to channelling, session attributes and attribution tables.</p><p>This architecture proved very useful, the table itself was useful from a product performance stand point but this could also be attached to demand side dimensions via the product id.</p><p>We then decided to create another version of this table for transactions, persisting similar data but at booking level and using the booking id as opposed to the product id.</p><p>This design can be replicated for multiple types of ‘events of interest’ eg, sign up page, search page, blog page etc based on business initiatives to help count business metrics separately from sessionisation but also have the flexibility to incorporate this into session reports eg what were the transactions for these sessions, how many direct channel sessions go to the sign up page etc etc.</p><p>I hope this article is helpful for anyone trying to find their way though modelling the Snowplow Atomic Events. If you do please do share and give this a clap. I welcome all feedback, and will create additional articles explaining the implementation process for each step in a bit more detail.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e0306d63b491" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>