<?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 Abhranil Pal on Medium]]></title>
        <description><![CDATA[Stories by Abhranil Pal on Medium]]></description>
        <link>https://medium.com/@abhranilpaul?source=rss-bb38fa91d100------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*PNZeWbqNxTLSL2EH.</url>
            <title>Stories by Abhranil Pal on Medium</title>
            <link>https://medium.com/@abhranilpaul?source=rss-bb38fa91d100------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 23:10:32 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@abhranilpaul/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[Silence Isn’t Churn, Part 3: Building It in Python]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://blog.devgenius.io/silence-isnt-churn-part-3-building-it-in-python-1ba958cd7d6c?source=rss-bb38fa91d100------2"><img src="https://cdn-images-1.medium.com/max/1408/1*eP4uIZpU7DZMhUN4-qf5Zg.jpeg" width="1408"></a></p><p class="medium-feed-snippet">A 5-part series on predicting customer value with probabilistic models, starting with the problem, not the math.</p><p class="medium-feed-link"><a href="https://blog.devgenius.io/silence-isnt-churn-part-3-building-it-in-python-1ba958cd7d6c?source=rss-bb38fa91d100------2">Continue reading on Dev Genius »</a></p></div>]]></description>
            <link>https://blog.devgenius.io/silence-isnt-churn-part-3-building-it-in-python-1ba958cd7d6c?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/1ba958cd7d6c</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[customer-lifetime-value]]></category>
            <category><![CDATA[customer-analytics]]></category>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Thu, 01 Oct 2026 12:59:10 GMT</pubDate>
            <atom:updated>2026-10-08T10:38:13.718Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Silence Isn’t Churn, Part 5: The CLTV Interview Cheat Sheet]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://blog.devgenius.io/silence-isnt-churn-part-5-the-cltv-interview-cheat-sheet-b2aae309ddf4?source=rss-bb38fa91d100------2"><img src="https://cdn-images-1.medium.com/max/1408/1*eP4uIZpU7DZMhUN4-qf5Zg.jpeg" width="1408"></a></p><p class="medium-feed-snippet">A 5-part series on predicting customer value with probabilistic models, starting with the problem, not the math.</p><p class="medium-feed-link"><a href="https://blog.devgenius.io/silence-isnt-churn-part-5-the-cltv-interview-cheat-sheet-b2aae309ddf4?source=rss-bb38fa91d100------2">Continue reading on Dev Genius »</a></p></div>]]></description>
            <link>https://blog.devgenius.io/silence-isnt-churn-part-5-the-cltv-interview-cheat-sheet-b2aae309ddf4?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/b2aae309ddf4</guid>
            <category><![CDATA[customer-analytics]]></category>
            <category><![CDATA[customer-lifetime-value]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Thu, 01 Oct 2026 12:23:10 GMT</pubDate>
            <atom:updated>2026-10-08T10:38:18.157Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Silence Isn’t Churn,Part 2: The mathematical intuition of BG/NBD]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://blog.devgenius.io/silence-isnt-churn-customer-lifetime-value-with-bg-nbd-part-2-c9f2219754f5?source=rss-bb38fa91d100------2"><img src="https://cdn-images-1.medium.com/max/1408/1*eP4uIZpU7DZMhUN4-qf5Zg.jpeg" width="1408"></a></p><p class="medium-feed-snippet">A 5-part series on predicting customer value with probabilistic models, starting with the problem, not the math.</p><p class="medium-feed-link"><a href="https://blog.devgenius.io/silence-isnt-churn-customer-lifetime-value-with-bg-nbd-part-2-c9f2219754f5?source=rss-bb38fa91d100------2">Continue reading on Dev Genius »</a></p></div>]]></description>
            <link>https://blog.devgenius.io/silence-isnt-churn-customer-lifetime-value-with-bg-nbd-part-2-c9f2219754f5?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/c9f2219754f5</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[statistical-analysis]]></category>
            <category><![CDATA[customer-analytics]]></category>
            <category><![CDATA[business-analytics]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Wed, 30 Sep 2026 07:59:53 GMT</pubDate>
            <atom:updated>2026-10-06T12:49:23.842Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Silence Isn’t Churn, Part 4: Putting the Model to Work]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://blog.devgenius.io/silence-isnt-churn-part-4-putting-the-model-to-work-110b988f7241?source=rss-bb38fa91d100------2"><img src="https://cdn-images-1.medium.com/max/1408/1*eP4uIZpU7DZMhUN4-qf5Zg.jpeg" width="1408"></a></p><p class="medium-feed-snippet">A 5-part series on predicting customer value with probabilistic models, starting with the problem, not the math.</p><p class="medium-feed-link"><a href="https://blog.devgenius.io/silence-isnt-churn-part-4-putting-the-model-to-work-110b988f7241?source=rss-bb38fa91d100------2">Continue reading on Dev Genius »</a></p></div>]]></description>
            <link>https://blog.devgenius.io/silence-isnt-churn-part-4-putting-the-model-to-work-110b988f7241?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/110b988f7241</guid>
            <category><![CDATA[customer-analytics]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[customer-lifetime-value]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Wed, 30 Sep 2026 05:44:46 GMT</pubDate>
            <atom:updated>2026-10-08T10:38:21.281Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Silence Isn’t Churn,Part 1: Introduction to Customer Lifetime Value with BG/NBD]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://blog.devgenius.io/silence-isnt-churn-customer-lifetime-value-with-bg-nbd-part-1-96d84a8384b7?source=rss-bb38fa91d100------2"><img src="https://cdn-images-1.medium.com/max/1408/1*eP4uIZpU7DZMhUN4-qf5Zg.jpeg" width="1408"></a></p><p class="medium-feed-snippet">A 5-part series on predicting customer value with probabilistic models, starting with the problem, not the math.</p><p class="medium-feed-link"><a href="https://blog.devgenius.io/silence-isnt-churn-customer-lifetime-value-with-bg-nbd-part-1-96d84a8384b7?source=rss-bb38fa91d100------2">Continue reading on Dev Genius »</a></p></div>]]></description>
            <link>https://blog.devgenius.io/silence-isnt-churn-customer-lifetime-value-with-bg-nbd-part-1-96d84a8384b7?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/96d84a8384b7</guid>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[customer-lifetime-value]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[customer-analytics]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Wed, 23 Sep 2026 15:46:16 GMT</pubDate>
            <atom:updated>2026-10-06T12:49:10.852Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[How we are implementing a Data driven Multi touch attribution model for our organization.(Part 1)]]></title>
            <link>https://blog.devgenius.io/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-1-7474d0d5c19a?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/7474d0d5c19a</guid>
            <category><![CDATA[multi-touch-attribution]]></category>
            <category><![CDATA[web-analytics]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[digital-analytics]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Tue, 11 Nov 2025 11:02:02 GMT</pubDate>
            <atom:updated>2025-11-27T10:59:21.058Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zqCHKk9SUbhB8kvjaRdpqQ.png" /></figure><p>Part 1: How do we do things now?</p><p><em>This is part 1 of a 3 part series where I am going to share what we are doing as an organization about implementing Data driven attribution. The parts are:</em></p><ol><li><strong>How do we do things now?</strong></li><li><a href="https://medium.com/dev-genius/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-2-89763db51726">How do we get the data?</a></li><li><a href="https://medium.com/dev-genius/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-3-5b20aa4fb11c">How do we build the model?</a></li></ol><h4>Intro:</h4><p>As our business has started to mature in the Digital Analytics stack one thing we felt as a team we can do better is improve the quality of Marketing channel attribution for the business teams. This would help them get a better understanding of how to best plan and measure their marketing efforts and what worked and what did not.</p><p>While tools like Market Mix Model and Matched market testing are efficient in giving the big picture representation of how Marketing works for us , it would be worth having something which is more tactical. Something which the Marketing team for example would be able to use day to day to measure and plan their strategies with.</p><p>In this article I am going to share 2 important building blocks of what defines how we do Multi touch attribution as of today.</p><h4>Part 1: Cookie based journey stitching:</h4><p>Without getting too much into what cookies are(in case you are interested to read about cookies please check <a href="http://What Are Internet Cookies and What Do They Do?  Kaspersky https://www.kaspersky.com › resource-center › definitions">this</a> article), I will just point out that this is the most basic way any web tracking tool(Google/Adobe/Mix-panel etc.) goes about identifying a visitor on the website. This helps the tools to stitch visitor journeys across multiple days, sessions etc. E.g: If you were to visit a website today and accept the terms of Cookie settings(that banner which pops up when you first go to a website) then no matter when you visit the website later from your device on the same browser the tracking tool would be able to say that it is you and not someone else who is visiting the website. This in turn will help a Digital Analyst like me to see the data and say “<em>hey user xyz came to the website did some browsing and then returned 5 hours later to buy the same product”</em>. The cookie dropped on your website is a unique alpha numeric value which in Google Analytics is called user pseudo id and the series of actions you do are called events. A bunch of events done at a relatively cohesive time period constitutes a session. More on these concepts <a href="https://www.lovesdata.com/blog/sessions-users/">here</a>.</p><p>Point being as of now we are using cookies to identify a user and whatever we can record in terms of events tied to a cookie gives us a view of how a visitor journey looks like.</p><p>While this is a fairly simple way of doing things and spares us any additional effort of user journey stitching, in recent times this has led to major tracking issues with all kinds of user data privacy protections acts that have recently been placed. These laws have made it extremely difficult to persist cookies.(Safari for example automatically expires cookies after 7 days). Thus it makes things very difficult for us, since now even if we have the same user coming to the website multiple times, because the earlier cookie gets replaced by a new cookie we cannot stitch the user’s journey.</p><p>And even before all of this happened cookies were historically non persistent as things like changing a user’s device or browser or clearing caches would replace the cookies.</p><p>Thus all in all if our aim is to do simple analysis like how many times someone clicks on the Add to cart button the cookie based implementation does not affect us however if we want to do realistic cross device customer journey tracking to build data driven models it becomes very important that we leverage something other than cookie id to track the users.</p><h4>Part 2: Last Non- Direct Click Attribution(LNDC):</h4><p>Like a lot of businesses who are early in their Digital Analytics maturity cycle we have been using Last non-direct click rule to attribute our revenues and orders to the Marketing Channels. This is a fairly simple model where in(as the name itself suggests) for every transaction we look at the last touch point leading to the transaction and give the credit of the transaction to the marketing channel recorded against this touch point. If <em>Direct</em> as a channel happens to be the last touch point we ignore that touch point and <em>look-back</em> to see where we can get a <em>non-direct </em>channel. If such a touchpoint exists we credit the transaction to the channel against this touch point. If no such touch point exists we attribute the order to Direct.</p><p>Consider this example. where a user visits the website 4 times before actually making the purchase.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2-r4W3w1tSYAY9PWwSo51Q.png" /><figcaption>LNDC exists</figcaption></figure><p>In this case since we have a value other than Direct in the sequence of events we attribute this transaction to Organic. Where as for this example</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bEah7409QN9ZZZrL7MmE0Q.png" /><figcaption>LNDC does not exist</figcaption></figure><p>we do not have any other value apart from Direct so the order gets attributed to Direct as a channel.</p><p>Now why do we not want to attribute orders to Direct?</p><p>Before that what does Direct as a channel mean → Its when users are typing our website link on the browser or clicking a bookmark with our website link and landing on the page. Thus</p><ol><li>Direct as a channel can be argued is activated by some other Marketing channel effort. E.g: Only when the user sees a Display ad of our website do they become familiar with our website thus the fact that they recalled our website and came back via Direct actually should be credited to the impression our Display campaign was able to make on the user.</li><li>More importantly Direct is a user driven touchpoint and as a Marketing team even if I know that a large number of users come via Direct I cannot do much about this information itself unless I know what actually made the users come to the website via Direct. After all as a Marketer, I can invest money on building awareness like via a Display ad which in turn can direct users to come to our website via Direct . Thus my role is to create a sticky display ad, whether user will come back via Direct is not really in my control.</li></ol><p>One last thing about this mechanism(or any other attribution mechanism) we need to have some sort of a look back window which should remain constant for all calculations. In simple terms this look-back window is the number of days I should <em>look back </em>from the day of transaction to stitch the journeys and calculate my last non direct channel.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*llXH8xEla_I7iTItKwAJ7A.png" /><figcaption>Look back window</figcaption></figure><p>Say in this example if I have decided my look back window to be 30 days for all calculations- for the transaction on D12 I can look back upto D0 to see which is my Last non direct channel(in this case Organic). Applying the same look back window to the transaction on D50 I can only look back upto D20 thus my LNDC in this case would be Direct(not Display).</p><p>How do we decide our look back windows? →You can find more about it <a href="https://segwise.ai/blog/understanding-lookback-windows-marketing-attribution">here</a>.</p><p>The LNDC attribution process is fairly simple to calculate thus quite easy to implement in a data pipeline. On top of that as long as we are using non-persistent cookies to track users — where in the user journeys themselves are not that well-stitched(maximum 1 or 2 touch-points prior to a conversion) LNDC will give similar results when compared to Data driven attribution or any complex method(since the data itself is not meaningful).</p><p>However this is a rule based/heuristic approach. Which is to say it is a one size fits all approach to understanding how our users actually decide whether they will buy on our website or not. More over this will always be oblivious to channels which are top of the funnel since the name itself suggests that this is <strong>L</strong>NDC. In our example for the first journey the credit for the order will go to Organic, but what about the role Display played in probably creating awareness about the brand? We totally overlook that.</p><p>A relatively straight forward(implementation wise) alternative to this would be using something like <a href="https://experienceleague.adobe.com/en/docs/analytics/analyze/analysis-workspace/attribution/models"><strong>Participation</strong></a>(aka frequency based approach). However, what if we have a channel like Email- where in we randomly re-target people and even though it gets recorded quite often in the journeys it actually plays little to no role in nudging the user towards conversion? Better than LNDC for sure at giving us some insight on the remaining funnel but not the best yet.</p><blockquote>Think of it this way, that in a football team I keep playing the same players but since I do not shuffle my team very often all my players get equal credit for winning and losing. But which player actually contributes most to the team?</blockquote><p>There are more complex rule based algorithms like Logarithmic and Time Decay but yet again these still fall within the guard rails of being rule based, thus even though it may give better visibility to the channels it may not able to capture the nuances of the customer journeys specific to our website.</p><p>Instead what if we can actually find those channels(or football players) who give us a win/conversion?</p><p>In other words what we are trying to find out is given email appears in a customer journey what can I say about the customer’s intent to buy before and after they came across this touch point? Did it increase/decrease/ stay the same? — What role did email play (actually) in driving the conversion/non-conversion?</p><p>This is what we try to achieve by implementing a data driven attribution model.</p><p>Our objective is to look at our own data(not rely on static rules) and model our attributions accordingly.</p><blockquote>With this thought in mid let’s move to the next article — where we find a fix to these problems.</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7474d0d5c19a" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-1-7474d0d5c19a">How we are implementing a Data driven Multi touch attribution model for our organization.(Part 1)</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How we are implementing a data-driven multi-touch attribution model for our organization (Part 3)]]></title>
            <link>https://blog.devgenius.io/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-3-5b20aa4fb11c?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/5b20aa4fb11c</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[data-driven-attribution]]></category>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[web-analytics]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Tue, 11 Nov 2025 11:01:34 GMT</pubDate>
            <atom:updated>2025-11-28T11:04:43.308Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zqCHKk9SUbhB8kvjaRdpqQ.png" /></figure><p>Part 3: How do we build the model?</p><p><em>This is part 3 of a 3 part series where I am going to share what we are doing as an organization about implementing Data driven attribution. The parts are:</em></p><ol><li><a href="https://medium.com/dev-genius/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-1-7474d0d5c19a">How do we do things now?</a></li><li><a href="https://medium.com/dev-genius/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-2-89763db51726">How do we get the data?</a></li><li><strong>How do we build the model?</strong></li></ol><h4>Intro:</h4><p>As our business has started to mature in the Digital Analytics stack one thing we felt as a team we can do better is improve the quality of Marketing channel attribution for the business teams. This would help them get a better understanding of how to best plan and measure their marketing efforts and what worked and what did not.</p><p>While tools like Market Mix Model and Matched market testing are efficient in giving the big picture representation of how Marketing works for us , it would be worth having something which is more tactical. Something which the Marketing team for example would be able to use day to day to measure and plan their strategies with.</p><p>In this article we discuss about how do we go about building the actual Data driven model.</p><p>Before we start there are several popular algorithms/models that are used by businesses to do this. In my research I found the 3 most common ones are Shapley value, Markov chain and Fractribution. Fractribution is the algorithm that is used by Google Analytics to power its data driven models however the workings of this(from whatever literature I could find online) seemed to be a mix of Shapley/Markov and then some other rule based calculations which I was not very clear with. So I did not try to mimic this.</p><p>Now, between Shapley and Markov I found one particular aspect of Markov chains which gives it an upper hand over Shapley:</p><blockquote>Markov chain attribution factors in the sequence in which the channel occurs. We will see this later in the calculations but broadly speaking Markov is able to say how does Channel B impacts conversions when it comes right after Channel A in a customer journey where as Shapley tells you when B is added to A in a journey what is the increase in Conversion rate. It does not take into account whether B has this impact by coming before or after A. This seemed to be a slightly more causal way of looking at things.</blockquote><p>Now, then how does Markov chain work.</p><p>Markov chains is a stochastic algorithm in which the events at current(nth) state depends on the previous(n-1) state. A simple example would be given that it was rainy yesterday what is the probability that today will be sunny?</p><p>Now the nth state may depend on the n-1 th or n-2 th or even more of the predecessors. For our discussion we will work with the hypothesis that our Customer journey touch points mimic a 1st order Markov Chain i.e the nth state depends on n-1th state. There is a statistical test to test this hypothesis called <a href="http://Statistical Inference about Markov Chains">Anderson- Goodman test</a>.</p><p>Back to the application.</p><p>Let’s go back to our <a href="https://medium.com/p/89763db51726/edit">dataset</a> and look at some data. Let’s say it looks something like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/495/1*Wai6eoVMWdXA283EM9P8ag.png" /><figcaption>Training data</figcaption></figure><p>Let’s say this is our observation data(or historical data). We have 4 journeys here 2 of which convert and 2 do not. A,B,C and D are the channels and they appear in the sequence as shown.</p><p>If we were to frame this as a Markov chain, each point in the journey would be a state.</p><p>There are 2 types of states in a chain:</p><ol><li>Transitional: We can move out of these states(Start, A,B,C,D)</li><li>Absorption: Once in this state we cannot move to another state(1 and 0). Think of this as an end state.</li></ol><p>Using this framework we will try to build what is called a Transition Probability Matrix(TPM). In other words given that I am in state A what are the probabilities of me going to say state B or C etc. We will do this for all the states and we will stop the calculation once we reach our absorption state.</p><p>Diagrammatically it will look something like this</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/763/1*D8PlhB39nmNp8OrfqRItPQ.png" /><figcaption>Base diagram</figcaption></figure><p>Now this diagram also gives me what is called the total conversion probability. In terms of this diagram the interpretation of that would be how many ways can I reach from Start to Conversion. Say one of the paths we can take is Start&gt;A&gt;B&gt;C&gt;Conv another could be Start&gt;B&gt;D&gt;Conv and so forth. Say for path Start&gt;A&gt;B&gt;C&gt;Conv this would the product of paths Start&gt;A =3/4, A&gt;B=1/3,B&gt;C=2/3 and C&gt;Conv=1/3. We have to find the products of each of these paths(Start&gt;A&gt;B&gt;C&gt;Conv,Start&gt;B&gt;D&gt;Conv etc.) and then we sum all these values to get the net conversion probability of this path.</p><p>The answer to this calculation should be .5 since as per our data we have 2 conversions out of 4 paths.More on how to calculate Markov paths <a href="https://adequate.digital/en/markov-chain-attribution-modeling-complete-guide/">here</a>.</p><p>Now here is the interesting bit, what I do next is I remove one of the states in the path (say A) and see what happens to my total conversion probability.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/817/1*gI5jsy_A1bkdEaZ-EckJfA.png" /><figcaption>Removal effect</figcaption></figure><p>Since A has been removed all the paths associated with it which led to the conversion path are also gone, thus this will bring down our conversion probability.</p><p>In other words this is what A brings to the table- this is A’s contribution to my website’s conversion rate.</p><p>So, we calculate the weightage or efficacy of A in achieving conversion as</p><pre>Efficacy of A =<br>1-(Conversion probability without A/Conversion probability with A)</pre><p>Similarly we repeat this for all other nodes and we end up with a set of coefficients for each channel</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/352/1*_mPErNXRP2Tr2CzWVqwBoQ.png" /><figcaption>Markov coefficients for channels</figcaption></figure><p>Now that we have these coefficients we can apply them to any future conversion journey. Say we have some data like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/462/1*LAKm3BJNLzL2A2Wp-8bSKw.png" /><figcaption>Future journeys</figcaption></figure><p>For the first order we know the coefficients of B and C, we just need to normalize them(round to 1) and we will get the contribution of B and C. We do this for all orders.</p><p>This is what the net contribution to orders for each channel will look like when compared with how LNDC or Participation would attribute:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/586/1*ox-e-bjg3TDCDUYTCPRTng.png" /><figcaption>Comparative view of LNDC and Markov</figcaption></figure><p>A couple of observations here</p><ol><li>A starts to get credit for orders. In LNDC since we are not concerned with upper funnel channels we never credited A for the conversions but Markov identifies the role A plays in bringing in B and C to the chain who eventually close the conversions.</li><li>D’s contribution decreases. In LNDC we just understand D appeared before conversion thus gave it the whole credit of the order but Markov understands that even though D did appear before the conversion, it in itself may not be a strong converter as there are cases where in it also appears as the last touch point but does not convert.</li></ol><p>T<a href="https://github.com/abhraliteyaars/multitouchattribution/blob/main/Markov_chain.ipynb"><strong>he code to implement the logic for this can be found here.</strong></a></p><p>We ran this on some of our test data and compared this with the output of LNDC and this is how it stacks:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*izTL11j-V6yVJ6Ws23lu6A.png" /><figcaption>LNDC vs Markov</figcaption></figure><p>As we can see a lot of credit gets shifted from Direct and Display and gets allocated to Email and Brand/Shopping channels. This makes sense, especially for emails as we do send a lot of targeting emails encouraged in nudging users to come back and buy. This would mean we were giving less credit to those emails than we should(even though emails are free to send but they do play a greater role than we thought they did).</p><p>Well that’s about it. I hope I was able to give you an intuition of the how whole process works and how it can change the whole attribution game for an organization.</p><p>What did I leave out? — Well several aspects.</p><ol><li>We do not have offline data in this whole exercise(Call center, Mail orders etc.) . In order for us to compare the results of this model with other models like Market Mix or Match market test we will need to bring those in and recalibrate.</li><li>One of the suggestions from our stakeholders were should we treat returning customers and new customers seperately? Fair point, maybe we should evaluate.</li><li>We need to ideate and see how to productionalize this(since this was a POC). How frequently do we re-calibrate our coefficients?</li><li>Should we also include other coefficients like what was the position of the channel(top/middle/bottom)in the funnel? which device was the touchpoint interacted with etc.</li></ol><p>Well, I am off trying to solve these challenges. In the meantime, hope this helps someone in need.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5b20aa4fb11c" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-3-5b20aa4fb11c">How we are implementing a data-driven multi-touch attribution model for our organization (Part 3)</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How we are implementing a Data driven Multi touch attribution model for our organization(Part 2)]]></title>
            <link>https://blog.devgenius.io/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-2-89763db51726?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/89763db51726</guid>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[data-driven-attribution]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[web-analytics]]></category>
            <category><![CDATA[marketing-attribution]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Tue, 11 Nov 2025 10:57:09 GMT</pubDate>
            <atom:updated>2025-11-28T11:04:26.795Z</atom:updated>
            <content:encoded><![CDATA[<h3>How we are implementing a data-driven multi-touch attribution model for our organisation (Part 2)</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zqCHKk9SUbhB8kvjaRdpqQ.png" /></figure><p>Part 2: What do we want to do ?</p><p><em>This is part 2 of a 3 part series where I am going to share what we are doing as an organization about implementing Data driven attribution. The parts are:</em></p><ol><li><a href="https://medium.com/dev-genius/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-1-7474d0d5c19a">How do we do things now?</a></li><li><strong>How do we get the data?</strong></li><li><a href="https://medium.com/dev-genius/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-3-5b20aa4fb11c">How do we build the model?</a></li></ol><h4>Intro:</h4><p>As our business has started to mature in the Digital Analytics stack one thing we felt as a team we can do better is improve the quality of Marketing channel attribution for the business teams. This would help them get a better understanding of how to best plan and measure their marketing efforts and what worked and what did not.</p><p>While tools like Market Mix Model and Matched market testing are efficient in giving the big picture representation of how Marketing works for us , it would be worth having something which is more tactical. Something which the Marketing team for example would be able to use day to day to measure and plan their strategies with.</p><p>In this article we are going to talk about how do we build the basic dataset which will help us build our data driven model.</p><p>To recap; the broader objective here for us is to build a probabilistic model- one which looks at our past customer journeys, understands what role a channel played in nudging a customer towards conversion and gives us a fair way to allocate a conversion to a marketing channel in the future.</p><p>And in order for us to build such a model we need a dataset which represents a very realistic customer journey both when they convert and when they do not. So how do we go about this?</p><p><strong>Part 1: First we need to get the right data</strong></p><p>The first problem we need to solve to get to the model is getting our data right. If we do not have a fair representation of an actual customer journey the model will not be able to learn much and in turn will give us uninformed or even worse- biased outputs.</p><p>As we have already discussed the limitations of a cookie based tracking here we need to find an alternative. We will need to use whatever resources we have at hand. In our case fortunately we have 2 such tools:</p><ol><li>Login id: Even though login rates are not that great for websites(especially if it is a B2B website like ours) — the login ids are way more persistent i.e a user is more likely to be sticking to a login id than a cookie id. Thus, say if a user changes device between 2 sessions but is logged in both the sessions we can still say its the same user(even though this would mean 2 unique cookie ids).</li><li>Customer number: We record a unique customer number in our transaction database when an order is placed. This is not the same as login id. In our case a user can do a guest checkout(thus no login id) but they will still have a customer number. I beleive we use a mix of shipping and billing address to generate this. So this is a very handy piece of information indeed.</li></ol><p>So using these 2 we can start to build a much more meaningful dataset.</p><p>Consider this example:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5w17W6FP1e_v9xTAnNKrMw.png" /><figcaption>Journey stitching</figcaption></figure><p>Using a combination of Customer number and Login id we are able to stitch 3 separate journeys as a single journey.</p><p><strong>Part 2: Now let’s build a reasonable definition of a user journey:</strong></p><p>Time for some assumptions. We now, have data which looks very similar to the image above. Now we need to build some customer journeys from these data. These journeys should have 2 important elements:</p><ol><li>A starting point — Which should represent or mimic an actual journey start. This could mean 1 of 2 things. The 1st touch point of a New user to the website or the 1st touchpoint of a returning dormant user.</li><li>An end point — This could be the point where the user places an order. If no order is placed we have to find the last touchpoint in the user’s journey which should actually represent the end point.The question here is how do we know what actually is the last touchpoint? How can we say that the user does not convert further down the road?</li></ol><p>Ok, so let us solve this one by one.</p><p>One data point or analysis which comes in handy here is<em> “What is the typical engagement window(also called treatment window) of a user”</em></p><p>One way of getting some idea of this is checking the number of sessions users have over multiple time periods. In our case we see that the number of sessions over 30,60 and 90 days for a user medians around 4,7 and 8 respectively. Thereafter it hovers around 8. So it is a fair assumption that most purposeful user journeys would be less than 90 days. Or in other words if a user comes to the website on D0 then he or she would have either converted or not converted within the next 90 days.</p><p>This should help us get a starting point. We can say that if a user records a session on Day D0 and has not recorded any session for the last 90 days the user is starting a new journey. If they were to have another session in the last 90 days we would assume that the session we are seeing on D0 is not the beginning but the continuation of an already initiated journey. This data thus will not help in our aim of getting a full view of a user journey.</p><p>In other words we want to analyse data which gives us the full view of a funnel where we record the actual journey start and not assume an arbitrary point in the journey as the journey start</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*0vIK_PnsujkzMmYtmhIPfQ.png" /><figcaption>Correct journey map</figcaption></figure><p>and not this which gives us only part of the funnel</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ybZSTJ5DWyfmvBxEhYOORw.png" /><figcaption>Incorrect journey map</figcaption></figure><p>The engagement window(of 90 days) also gives us a way of getting the end point for non-conversion journeys . We can look forward 90 days from the starting point and make a fair assumption that the last recorded touchpoint in this window would be the end point of the journey. This is called the <em>observation window.</em></p><p><strong>Part 3: Now let’s build the user journeys:</strong></p><p>Now with this in mind we can start to build our customer journeys, which would look like these:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/758/1*XkGaezGEmyHmv6Ytms0HHg.png" /><figcaption>Sample journeys</figcaption></figure><p>The green bubbles are the ones where a user purchases. Thus we can say these are the Conversion endpoints while for the 2nd journey since there are no green bubbles this is a non-converted journey with the last touch point being S2(aka the<em> point of churn</em>).</p><p>Now we have a base to stitch our journeys with a well defined start and end point.</p><p>Since a conversion/non-conversion is the end state of a journey for us , we can stitch the journeys backwards from these states to see what led to these states. We can take an end-point say for the first journey this would be S4 where a conversion has happened and look back 90 days to create one journey. Similarly for the 2nd path since S2 is the endpoint of this journey we can look back 90 days from this point to create one non-conversion journey and so forth.</p><p>In a table this will look something like this</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/599/1*dFQ3dg0zQ60xqMQkf0QDaA.png" /><figcaption>Customer journey table</figcaption></figure><p>Here the journey id is transaction id if the journey led to a conversion else the last session id for a non-conversion one. The path array represents the channels from start to end(i.e the touchpoint closest to the transaction appears last). The Converted flag tells us if this journey led to a conversion or not.</p><p>One thing to note here is if you see the last journey we can see there are multiple transactions within 90 days so how do we frame the paths?</p><p>For me S3 is an endpoint from where I look back 90 days and create one path and I create another path starting from S4 and look back 90 days. In this case the paths will overlap(Display, Direct,Organic will appear for both TX-20002 and TX-30003) but that is fine because our assumption is whatever path led to a conversion in S3 would have also influenced the purchase at S4.</p><p>Now that we have the data in place in the next article we are going to talk about how do we build a data driven model on top of this data.</p><p>Over to article 3 of this series.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=89763db51726" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/how-we-are-implementing-a-data-driven-multi-touch-attribution-model-for-our-organization-part-2-89763db51726">How we are implementing a Data driven Multi touch attribution model for our organization(Part 2)</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a sql chatbot in Snowflake using Cortex API]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://blog.devgenius.io/building-a-sql-chatbot-in-snowflake-using-cortex-api-135213ec2812?source=rss-bb38fa91d100------2"><img src="https://cdn-images-1.medium.com/max/2304/1*WO-GfhUUtM-ZkZGZFamtwA.jpeg" width="2304"></a></p><p class="medium-feed-snippet">Sharing my notes on a recent project where I built a Chat-GPT like chatbot which can allow a business user to get information from a&#x2026;</p><p class="medium-feed-link"><a href="https://blog.devgenius.io/building-a-sql-chatbot-in-snowflake-using-cortex-api-135213ec2812?source=rss-bb38fa91d100------2">Continue reading on Dev Genius »</a></p></div>]]></description>
            <link>https://blog.devgenius.io/building-a-sql-chatbot-in-snowflake-using-cortex-api-135213ec2812?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/135213ec2812</guid>
            <category><![CDATA[ai-agent]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[naturallanguageprocessing]]></category>
            <category><![CDATA[cortex]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Wed, 27 Aug 2025 11:25:00 GMT</pubDate>
            <atom:updated>2025-09-09T17:02:24.474Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Triggering an alert email on job failure using Pub/sub and Cloud functions]]></title>
            <link>https://blog.devgenius.io/my-first-shot-at-using-gcp-services-as-a-data-engineer-8354823d905b?source=rss-bb38fa91d100------2</link>
            <guid isPermaLink="false">https://medium.com/p/8354823d905b</guid>
            <category><![CDATA[gcp]]></category>
            <category><![CDATA[event-driven-architecture]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[serverless]]></category>
            <dc:creator><![CDATA[Abhranil Pal]]></dc:creator>
            <pubDate>Mon, 14 Jul 2025 09:22:59 GMT</pubDate>
            <atom:updated>2025-09-09T07:32:52.487Z</atom:updated>
            <content:encoded><![CDATA[<p>Ok, so this is a brief article about how we can go about setting up an email alert system completely on GCP using the event driven serverless capabilities it provides.</p><h3>Objective</h3><p>We have multiple Scheduled Queries running on Big-query which are essentially data pipelines. We want to be able to get an alert if one of these pipelines failed so that we can <br>a. re run them manually to avoid loss of data<br>b. fix any bugs in the code in near real time</p><p>However, unlike Snowflake which has a built in email alert functionality, BQ does not have anything similar. So, we have to work our way around this. Thankfully BQ provides a bunch of other services which we can leverage. Let’s start first by creating a mental map of what we can build to enable this functionality. We will connect our Scheduled queries to pub/sub topics such that whenever a query fails pub/sub should record a message. This event in turn should trigger our cloud function which will read the event data like the error message/time of error etc and deliver this using an email service.</p><h3>Architecture:</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vGSRIaEHQTd49bW39uPdRQ.png" /><figcaption>Architecture</figcaption></figure><h3>PART 0(Pre-requisite):</h3><p>I have written a sample scheduled query like this:</p><pre>DECLARE DATE_TODAY DATETIME;<br>DECLARE ERROR_MSG STRING;<br>BEGIN<br>  SET DATE_TODAY = (SELECT CURRENT_DATE());<br>  SELECT 1/0;<br>EXCEPTION WHEN ERROR THEN<br>  SET ERROR_MSG = &#39;YES&#39;;<br>  RAISE;<br>END</pre><p>This will fail every time it is run because select 1/0 will always give an error. We have specified a <strong>RAISE</strong> statement without which we will not get the ‘FAIL’ flag in pub-sub.</p><p>I have also set it up to send any notification to a pub-sub topic which I have created(we can see the below form when we select Schedule on the top nav)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/769/1*9hDI6z5D9WsqNf4d78nOnA.png" /></figure><p>You can see the Notifications option right at the bottom:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*QuvIawF-R-iCZsQ8DbPoZg.png" /></figure><p>This will trigger a message to my pub-sub topic every time this proc runs. To see this go to the subscriptions(<a href="https://www.geeksforgeeks.org/devops/how-to-create-a-pub-sub-topic-on-gcp/">you have to create a subscription if you are using a new topic</a>) of the pub-sub topic and check the messages tab:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*J6ufvT5SWRfEGwL_awnYFA.png" /></figure><p>Once the message is published we can now focus on reading this message into a cloud function.</p><p>Next we will create a cloud run/cloud function(server-less and event-driven) from our cloud services. We will ensure the trigger for this function is our pub sub topic. Such that when ever the topic receives a failure message our function should trigger. The name of this event is: (google.cloud.pubsub.topic.v1.messagePublished).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/233/1*zQoUU1vnWUYiWvCK_jMHtw.png" /></figure><blockquote>Since we are reading a pub/sub notification we will need to use <strong>functions framework</strong>- which will help us handle events(https, pub-sub or any other kind of trigger event)under the hood. More on this later.</blockquote><p>Also, please make sure that the service account you are using to run the Cloud function should have access to the pub-sub topic as a Cloud Run Service Agent. To check this go to “Triggers” in your cloud function and check the Service account mentioned there. Then go to Pub-sub topic and see in “Permissions” if this same service account is mentioned under Cloud Run Service Agent. If not make necessary modifications. For our case it is: <em>nationalpen@appspot.gserviceaccount.com.</em></p><p>Next we will start writing the code for our function. To do this we go to the Source tab on the top and then open the editor:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rra1p1Rc9jNjj-ZyaI2EOQ.png" /></figure><p>This will give us a cloud shell which will work as our IDE to build and test our code.</p><h3>PART 1(Reading the data):</h3><p>In our code should be able to read the message from pub/sub notification and extract the necessary info: Name of the procedure, time of execution and the error message logged if any.</p><p>Since we will need to test our function while building we would need a sample input which should mimic the pub/sub notification. It’s tricky to get this payLoad from pub-sub directly into the cloud function, since that would require running our procedure- recording the data in pub/sub and letting pub/sub trigger a message.Instead, we can create a temporary function to simulate a dummy payLoad message for testing. You can also copy a message from pub-sub and apply base64 encoding</p><pre>base64.b64encode(json.dumps(sample_message).encode(&quot;utf-8&quot;)).decode(&quot;utf-8&quot;)</pre><p>this will give you an encoded string which we can use to test our function.</p><p>We will be using functions framework to handle the data, which is the go to option for event driven server-less functions . The syntax for an event for this framework is shown below:</p><pre>event = SimpleNamespace(<br>    data={<br>        &quot;message&quot;: {<br>            &quot;data&quot;: &quot;&lt;&lt;your encoded message&gt;&quot;<br>        }<br>    }</pre><p>SimpleNamespace is a python library which will convert event into an object whose attributes(in this case data) can be accessed using a .</p><pre>person = SimpleNamespace(name=&quot;Alice&quot;, age=30)<br>person.name #gives us Alice</pre><p>This step is essentially for debugging purpose and we will not require this when we deploy the function.</p><p>But first we need to decode the data from the event.data dictionary, to do this we can fetch the data from the event_data dict and then convert it to a python dictionary from a json object</p><pre>    #decode the pubsub message into utf-8(json) from bytes<br>    payload_bytes = base64.b64decode(event.data[&quot;message&quot;][&quot;data&quot;])<br>    #convert json to python data dictionary<br>    payload = json.loads(payload_bytes.decode(&quot;utf-8&quot;))</pre><p>Now we can get all attributes of the data by using payload.get.&lt;attr&gt;. E.g:</p><pre>    # Extract the error message,time_of_error and config id which gives the error<br>    error_message = payload.get(&quot;errorStatus&quot;, {}).get(&quot;message&quot;, &quot;No error message found.&quot;)<br>    runtime_utc = payload.get(&quot;runTime&quot;, &quot;No runTime found&quot;)<br>    #this will contain the config id<br>    payload_request_url = payload.get(&quot;name&quot;, &quot;&quot;)</pre><p>This way we can get all the necessary information from the pub-sub message whenever our function is triggered by a pub-sub topic.</p><p>While we got most of the information about the failed scheduled query via the payload, we still do not have the actual name of it(we do have the config id within the payload request url). For ease of debugging we need the name of the procedure. To get this value we can use the <strong>big query data transfer service</strong> by simply passing the name attribute from our payload and splitting it to get the required query args like project_id, location, transfer_config_id.</p><pre>#helper function to get name of scheduled query<br>def get_transfer_display_name(name):<br>    try:<br>        parts = name.split(&quot;/&quot;)<br>        project_id = parts[1]<br>        location = parts[3]<br>        #transfer config id is in the 5th split<br>        transfer_config_id = parts[5]<br>        <br>        #get default bearer tokens associated with the session<br>        credentials, _ = default()<br>        authed_session = AuthorizedSession(credentials)<br>        #bearer tokens will get automatically attached to this endpoint<br>        url = f&quot;https://bigquerydatatransfer.googleapis.com/v1/projects/<br>              {project_id}/locations/{location}/<br>              transferConfigs/{transfer_config_id}&quot;<br><br>        <br><br>        response = authed_session.get(url)<br>        if response.status_code == 200:<br>            return response.json().get(&quot;displayName&quot;, None)<br>        else:<br>            print(f&quot;Failed to fetch display name. Status: {response.status_code}, Body: {response.text}&quot;)<br>            return None<br>    except Exception as e:<br>        print(f&quot;Error fetching display name: {e}&quot;)<br>        return None<br><br>#invoke the function by passing the name attribute to the helper function<br>query_name = get_transfer_display_name(payload_request_url)</pre><h3>PART 2(Emailing the data):</h3><p>Next we want to send all of this information via an email. To do this we have an email api service hosted on our company portal(in case you do not have one you will have to create an email service on outlook/gmail etc and get an end-point). In our system we can design a template for our email. This will consist of the layout of the body, the recipient list, subject etc. This can contain dynamic values as well. We need the template id from this application to connect to our app.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yl0FziGY3eZQYo1H1OqpDw.png" /></figure><p>To invoke this service we need to make a ‘POST’ call to this endpoint and pass the relevant information and it will trigger email to relevant senders.</p><p>However this service is hosted in our Open API Environment(for “Cimpress” users to use such services). If we go <a href="https://mcpapis.cimpress.io/api/puremail/openapi?env=open+api#/PureMail">here</a> we can see the endpoint and how we can invoke them. For e.g for this service we can see the base url and the format of the post request:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GHgIPzPig1ghFJHIrSP4BA.png" /></figure><p>However when we send the request we also need to include the Authorization tokens(since this is a gated environment). E.g:</p><pre>    url = f&quot;https://puremail.trdlnk.cimpress.io/v1/send/{PUREMAIL_TEMPLATE_ID}&quot;<br>    headers = {<br>        &quot;Authorization&quot;: token,<br>        &quot;Content-Type&quot;: &quot;application/json&quot;<br>    }<br><br>    try:<br>        response = requests.post(url, headers=headers, data=json.dumps({&quot;item&quot;: message_body}))<br>        logging.info(f&quot;Email API responded with status: {response.status_code}&quot;)<br>        logging.debug(f&quot;Email response: {response.text}&quot;)<br>    except Exception as e:<br>        logging.exception(&quot;Error occurred while sending email.&quot;)</pre><p>Our application which is the client in this case needs to get this token from our O auth server. But how does oauth know that this application which is asking for the token should be given a Token or not? To bridge this gap the email service is already registered on gcp with a client name and a client secret. If we can get hold of these and pass it as a variable to the oauth server when making the request, oauth knows this is a valid client and gives us a token. Recap:</p><ol><li>Google cloud function is registered on Oauth as a legitimate client.</li><li>The validation of this registration is a Client Secret</li><li>This client secret is saved in Secret Manager in GCP</li><li>We fetch the secret from this Secret Manager</li><li>Using this secret we generate a token from Oauth</li><li>Using the token we access the Email template service to send emails</li></ol><p>This I found a little complicated so I have created a mental map to explain this better.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RIP31Rvh1RWDKdr41Z5Gww.png" /></figure><p>In simple words the cloud function service has been registered on our O auth server with a certain id and secret such it can access all API services. Think of this as you registering to Facebook(or any other app) such that you can access all of their services. You essentially need 2 things</p><ol><li>The url of the app(in our case this is: ‘https://api.cimpress.io/&#39;)</li><li>The user id and password(this we replace by a ‘Bearer token’)</li></ol><p>Now to get this token specific to our client we need to know the client id(which is a fixed value assigned when we have registered our app) and a secret (which also was generated one time). However, as a best practice we store the Client Secret in another service called Secret Manager. This helps us in protecting sensitive information as well as this service keeps rotating this value to protect it from leakage. To fetch this client secret we just need to make a request to the service using our secret manager. So if we break this down there are 3 sub processes taking place here</p><ol><li>Get secret key from Secret Manager. To do this we have written a helper function like this:</li></ol><pre>def secret_fetcher(secret_name):<br>    client = secretmanager.SecretManagerServiceClient()<br>    name = f&quot;projects/261374685312/secrets/{secret_name}/versions/1&quot;<br>    response = client.access_secret_version(name=name)<br>    # Decode the payload<br>    payload = response.payload.data.decode(&quot;UTF-8&quot;).strip()<br>    return payload</pre><p>we need to know our secret name to generate the secret as a payload.</p><p>2. Using this secret we can generate an o auth token to access any Cimpress service. The function for this:</p><pre>def generate_token(client_id, client_secret):<br>    try:<br>        response = requests.post(<br>            &#39;https://oauth.cimpress.io/v2/token&#39;,<br>            data={<br>                &#39;grant_type&#39;: &#39;client_credentials&#39;,<br>                &#39;client_id&#39;: client_id,<br>                &#39;client_secret&#39;: client_secret,<br>                &#39;audience&#39;: &#39;https://api.cimpress.io/&#39;<br>            }<br>        )<br>        json_data = response.json()<br>        # print (&#39;Response from cimpress endpoint:&#39;,json_data)-- &gt; {&#39;access_token&#39;: &#39;eyJhbGci....&#39;expires_in&#39;: 86400, &#39;token_type&#39;: &#39;Bearer&#39;}<br>        return f&quot;Bearer {json_data[&#39;access_token&#39;]}&quot; if &#39;access_token&#39; in json_data else None<br>    except requests.exceptions.RequestException as err:<br>        print(f&quot;Request Exception: {err}&quot;)<br>        return None</pre><p>3. Using this token we can now use the specific email service called PUREMAIL TEMPLATE. The ID for this can be generated from the email service itself(more on this later):</p><pre>def send_email_alert(message_body, cred):<br>    token = generate_token(cred[&#39;client_id&#39;], cred[&#39;client_secret&#39;])<br>    if not token:<br>        print(&quot;Cannot send email: Failed to generate access token.&quot;)<br>        return<br>    PUREMAIL_TEMPLATE_ID = os.getenv(&quot;PUREMAIL_TEMPLATE_ID&quot;)<br>    url = f&quot;https://puremail.trdlnk.cimpress.io/v1/send/{PUREMAIL_TEMPLATE_ID}&quot;<br>    headers = {<br>        &quot;Authorization&quot;: token,<br>        &quot;Content-Type&quot;: &quot;application/json&quot;<br>    }<br><br>    email_payload = {<br>        # &quot;to&quot;: &lt;&lt;.........&gt;&gt;<br>        # &quot;subject&quot;: &lt;&lt;............&gt;&gt;<br>        &quot;item&quot;: message_body<br>    }<br><br>    try:<br>        response = requests.post(url, headers=headers, data=json.dumps(email_payload))<br>        print(f&quot;Email sent. Status: {response.status_code}&quot;)<br>        print(f&quot;Response: {response.text}&quot;)<br>    except Exception as e:<br>        print(f&quot;Error sending email: {e}&quot;)</pre><p>This establishes the connection. We can chose how to send the data to this service. For now we will just send value as message body, which in our case will be the name/time and error message of the procedure. Sample message body:</p><pre>        message_body = {<br>            &quot;procedure_name&quot;: proc_name,<br>            &quot;message&quot;: error_message,<br>            &quot;timestamp&quot;: runtime,<br>            &quot;contacts&quot;: {<br>                &quot;primary&quot;: &quot;abc@xyz.com&quot;,<br>                &quot;secondary&quot;: &quot;xyz@xyz.com&quot;<br>            }<br>        }</pre><p>Our message body can literally be anything as long as it is in a json format.</p><p>This will send our message to the emailing service from our cloud function every time there is a failed scheduled query in the queue. In the future if we setup any scheduled query all we need to do is</p><ol><li>ensure we have a RAISE statement within our exception block</li><li>ensure we chose the pub sub topic in the notification options and apply the proper topic name which we have connected to our cloud function.</li></ol><p>The full code for this can be found here:</p><p><a href="https://bitbucket.org/national-pen/e-comm-notebook/commits/182d26f14a9ec086cf7817b13bb0a213a4ff5ce5">https://bitbucket.org/national-pen/e-comm-notebook/commits/182d26f14a9ec086cf7817b13bb0a213a4ff5ce5</a></p><p>(only for company users)</p><h3>Annexure:</h3><p>(This part is related to the email template service and does not involve info regarding the cloud function)</p><p>We can either use one of the existing templates or create a new one. The link for this : <a href="https://templatedesigner.cimpress.io/">https://templatedesigner.cimpress.io/</a></p><p>You can either create a new template or use an existing one.</p><p>In either case here are some pointers:</p><ol><li>We can access any information passed to the message_body(item) by referencing them with . notation. E.g to get error message: item.message etc.</li><li>Ensure that you give proper access to team members who will either edit/modify it. If someone needs to use this template as is then no need to grant access. Knowing the template id is enough. Also, they should know the payload structure of sending the message_body(item) from the function.</li></ol><p>Sample template:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7ua_R49UYw7ZCZZRdKm67w.png" /></figure><h3>Conclusion</h3><p>We have thus set up an end to end process using Scheduled query, Pub/sub and cloud function to send an email basis a failure event. We can expand on this to brainstorm other use cases using the serverless/event driven architecture provided by GCP.</p><p>Hope this helps!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8354823d905b" width="1" height="1" alt=""><hr><p><a href="https://blog.devgenius.io/my-first-shot-at-using-gcp-services-as-a-data-engineer-8354823d905b">Triggering an alert email on job failure using Pub/sub and Cloud functions</a> was originally published in <a href="https://blog.devgenius.io/">Dev Genius</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>