<?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[Narrator - Medium]]></title>
        <description><![CDATA[Narrator’s solution to data modeling is an engine to transform all your data into a single 11-column data model and a platform that can generate any table for BI, reporting, and analysis using JUST that data model. - Medium]]></description>
        <link>https://medium.com/narrator-ai?source=rss----c40f0c134c80---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>Narrator - Medium</title>
            <link>https://medium.com/narrator-ai?source=rss----c40f0c134c80---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 03:36:21 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/narrator-ai" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[The best way to calculate Customer Lifetime Value for Ecommerce]]></title>
            <link>https://medium.com/narrator-ai/the-best-way-to-calculate-customer-lifetime-value-for-ecommerce-68dae683d5d8?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/68dae683d5d8</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[customer-lifetime-value]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Cedric Dussud]]></dc:creator>
            <pubDate>Tue, 09 Feb 2021 20:35:22 GMT</pubDate>
            <atom:updated>2021-02-09T20:37:46.775Z</atom:updated>
            <content:encoded><![CDATA[<p>Understanding what Customer Lifetime Value is, why it’s a useful metric, and how to compute it.</p><p><em>Originally published at </em><a href="https://blog.narrator.ai/how-to-calculate-ltv/"><em>https://blog.narrator.ai</em></a><em> on February 2, 2021</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*taZjtu0a3GOYNnPx.jpg" /><figcaption><em>Photo by </em><a href="https://unsplash.com/@jplenio"><em>Johannes Plenio</em></a><em> on </em><a href="https://unsplash.com/?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText"><em>Unsplash</em></a></figcaption></figure><h3>Why is LTV useful?</h3><p>Lifetime Value (LTV) allows us to make decisions about our customers by considering their value today and their value in the future.</p><p>LTV allows us to have a common metric that represents what someone is worth today and what we expect them to be worth in the future so we can make decisions to influence future revenue.</p><blockquote><em>Note that this is LTV for retail e-commerce businesses with multiple single purchases. For subscriptions / SaaS products LTV is calculated differently and will be the topic of a future post.</em></blockquote><h3>LTV Explained</h3><p>LTV is a modeled value that gives us a simple way to reason about a customer’s changing behavior (purchase amount, retention, and cadence) using a simple metric.</p><p>It’s a prediction of future value generated from each customer and it’s made up of the following components:</p><ol><li><strong>Retention</strong> Customers become more valuable as they have more orders</li><li><strong>Average Order Value</strong> Customers who spend more are more valuable</li><li><strong>Time Between Orders</strong> The time it takes for customers to come back will drive the time horizon over which LTV is realized</li></ol><h3>Calculating LTV</h3><blockquote><em>Before we dive in: Narrator can provide this analysis and many others in minutes using your data. </em><a href="https://calendly.com/narrator-team/demo?utm_source=blog"><em>Chat</em></a><em> with us and we’ll show you how!</em></blockquote><p>LTV uses retention patterns to understand a customer’s likelihood to reach the nth order, then multiplies it with the revenue value in order to come up with the expected revenue from the nth order. Finally, the expected revenue is summed up across all orders. From there we’ll use the time it takes for customers to order again to estimate the amount of time it will take to realize LTV.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*MFjGp5Ffi4x9U8uf.png" /></figure><p><strong>LTV is a converged value.</strong> LTV is based on the <em>expected</em> revenue that a single customer will generate. We look at the chance for customers overall to make subsequent orders. Eventually the likelihood to reach order number 4, for example, becomes so small that the expected revenue gains from another order stop mattering (diminishing returns). This is the point when LTV converges.</p><p>For example:</p><p>The LTV of a Narrator Demo customer converges at <strong>$154.25</strong>. It takes <strong>180</strong> days for a customer to reach <strong>95%</strong> of this value.</p><h3>What drives LTV</h3><p>LTV is a single number that represents a complex relationship between multiple variables, so we can reason about the combination customers’ time, average order value, and retention and focus on the most valuable customers.</p><p>Behavioral drivers of LTV:</p><h3>Modeling Customer Retention</h3><p><strong>Retention is the biggest driver of LTV because the retention rate is carried over to subsequent orders</strong></p><p>Recall that the equation of LTV is a function of <strong>likelihood to reach the Nth order</strong>. And because the likelihood to reach the Nth order is dependent on the likelihood to reach the (N-1)th order, even small changes in retention are amplified throughout the calculation. This is what makes retention a significant driver of LTV.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/881/0*Zb5bDt4PEZVATm2K.png" /></figure><p>Overall the calculation goes like this</p><ol><li>Remove the most recent X days of data.</li><li>For each order occurrence (1st, 2nd, etc) look at the conversion rate to the next order</li><li>Sum the conversion rates to get the likelihood to reach Nth order</li></ol><p><strong>Ignore Recent Data</strong></p><p>First we need to ignore the most recent days of data — we need to give a chance for customers to order multiple times.</p><p>We can do this by looking at the number of days between completed orders for each customer. Of all the total orders, some were completed 1 day after the last one. Some were done 2 days after, and so on. When we add them all up, the number at which we hit 85% of all orders is a good stopping point. In our sample data that’s 119 days.</p><p><strong>Likelihood to Reach Nth Order</strong></p><p>Generally (though not always) a returning customer is more likely to buy again than a brand new customer.</p><p>The problem is that any given customer isn’t likely to have many orders — at each step a significant number of customers are dropping off along the way.</p><p>In our example, we see that 9.6% of customers purchase again after the first order, and of those 36% purchase after the second.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hEfv4B3W1SWpE5Mh.png" /></figure><p>To calculate the likelihood to reach the Nth order we just need to multiply the conversion rate of all the previous orders (note that we’re only looking at customers who have ordered already, so the likelihood to reach the first order is 1).</p><p>Likelihood to reach 2nd order: 9.62%</p><p>Likelihood to reach 3rd order: 9.62% * 35.7% = 3.43%</p><p>… and so on</p><p>When plotted it looks like this</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*iv4v7GgDUn2Rt5H3.png" /></figure><p>This will be the first part of our LTV calculation.</p><p>There’s one interesting thing to note here: the fastest way to increase LTV is to increase the conversion to multiple orders. Here the drop off is fairly drastic, so any improvement will have an important effect.</p><blockquote><em>In our example, most customers are lost after the first purchase, so improving first order retention will have a huge impact on LTV.</em></blockquote><p>Now for the next part of the LTV calculation.</p><h3>Modeling Average Order Value</h3><p>Customers may change their spending habits as they continue to purchase.</p><p>When valuing a customer, it’s important to understand if they are going to spend more or less over time. For example, a customer who spends a lot up front but a little for each returning purchase might be less valuable than a customer spending less up front up a lot on each recurring purchase.</p><p>In short, spending behavior is an important input to the LTV calculation. LTV is the sum of the expected revenue gained at each order. Since we already know the likelihood that a customer will reach the Nth order, we just need to multiply that with the revenue value to get the expected revenue for that order.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/882/0*C5Wkjns9CE_Rz5gY.png" /></figure><p>Here we’re predicting, based on the order number, how much money a customer will spend.</p><p>So we just need to compute the average order value by occurrence, which is fairly straightforward.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*3Z8TRpmuzYeXXH90.png" /></figure><h3>Putting it Together</h3><p>With both our pieces in place we can now compute LTV. So here’s the equation one last time:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*_z6aMiVMvQLwHRHc.png" /></figure><p>Recall that for each occurrence the likelihood to reach the Nth order is the product of all the ones before it. So we just multiply that by our average order value to get the expected order value by Nth order. Adding all those together gives us LTV!</p><p>The sum after the 4th order is $156. But what’s the value after <strong>all </strong>the orders? At each step the expected value is going down fairly rapidly. It’s obviously converging.</p><p>So where do we stop? When the LTV changes by no more than 1%, which will sum up to 95% of its total value.</p><p>For our example, the expected value of the seventh order is $1.22. This is less than 1% of the sum to that point, so we can stop there. That sum is $160.66, so dividing it by 0.95 brings us to a converged value of $169.11</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*GoygqL3waXlINwNM.png" /></figure><h3>Modeling Time to Accrue Revenue</h3><p>Now that we know that most of the LTV is accrued in the first 7 orders, we can compute how long it takes a customer to reach this point — we need to understand how much time passes between each order.</p><blockquote><strong><em>A common error in LTV timing calculations</em></strong></blockquote><blockquote><em>A common mistake is to use historical data to see how long it took customers to reach the converged number of orders (in our case 7).</em></blockquote><blockquote><strong><em>Why it’s dangerous</em></strong></blockquote><blockquote><em>The sample size of customers who reached 7 orders will very small, so using them to calculate the time to reach LTV will introduce a lot of error.</em></blockquote><blockquote><strong><em>A better way</em></strong></blockquote><blockquote><em>Instead, we’ll use the median time between orders to calculate a better estimate of the time it takes to reach a given order ocurrence.</em></blockquote><p>This is fairly straightforward. We’ll just look at the days between orders and sum them all together.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*O8E0bam7PsPBfNM_.png" /></figure><p>Summing the days between orders up to the 7th order gives us a total of <strong>266 </strong>days. In other words, we’re predicting that it takes 266 days from their first order for customers to reach 95% of their LTV.</p><p>This number can be a very useful timeframe for thinking about return on investment. For example, this can be used to compute the time to recover customer acquisition costs.</p><h3>LTV and Beyond</h3><p>And there it is. Our complete LTV.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-ObK9zKPIpSHqckX.png" /></figure><p>So where do we go from here?</p><p><strong>Use LTV instead of Average Order Value to represent the value of a customer</strong></p><p>Use LTV for analyses when you need to represent the value of a customer because LTV allows you to reason about their spending, retention, and timing simply using a single metric.</p><ul><li>Ensure operating and acquisition costs stay below LTV to ensure a positive business ROI</li><li>Ensure Product teams prioritize improvements that cater towards high LTV users</li></ul><p><strong>Work on improving order retention and other drivers of LTV</strong></p><p>Conduct a follow up analysis to dive into each of the behaviors that drive LTV. This will identify the areas to focus that will have the biggest impact.</p><h3>Is there an easier way?</h3><p>Doing a full LTV analysis like this is clearly a lot of work. Luckily we’ve already done it.</p><p>Our company, <a href="https://www.narrator.ai/?utm_source=blog">Narrator</a>, provides these kinds of in-depth analyses (what we call <a href="https://www.narrator.ai/narratives?utm_source=blog">Narratives</a>) in minutes using your own data. Our library of Narratives includes this exact LTV analysis, along with many others. We’ll also write about our optimizing LTV Narrative in a future blog post.</p><p>If you’re interested <a href="https://calendly.com/narrator-team/demo?utm_source=blog">book a demo</a> or <a href="/service/mailto:contact@narrator.ai">reach out</a> and we can chat about it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/368/0*O_OPu8Wc3mQkXKfb.png" /></figure><p><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=68dae683d5d8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/the-best-way-to-calculate-customer-lifetime-value-for-ecommerce-68dae683d5d8">The best way to calculate Customer Lifetime Value for Ecommerce</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[One question to make your data project 10x more valuable]]></title>
            <link>https://medium.com/narrator-ai/one-question-to-make-your-data-project-10x-more-valuable-462d7f62795b?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/462d7f62795b</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Brittany Davis]]></dc:creator>
            <pubDate>Thu, 14 Jan 2021 22:32:17 GMT</pubDate>
            <atom:updated>2021-01-14T22:32:17.015Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*1ppUbiMgdYtKUEQh.jpg" /></figure><p>Data folks are in the business of helping people make decisions. Whether it’s a quick and dirty ad-hoc query or a super sophisticated statistical model, at the end of the day we’re informing a decision.</p><p>How many people are viewing our website on mobile?</p><p>What discount should I offer to this customer?</p><p>Who is likely to churn?</p><p>Decisions are so core to our work, that we sometimes forget to acknowledge them before starting a data project. We assume it’s baked into everything we do, so when the product team asks a question like: “Which products are commonly purchased together?” it’s natural to jump straight into figuring out what data we need, which approach is best, etc, etc. It’s a straightforward ask and the data work could be fun. How should we could visualize the results? What if the patterns have changed over time? We dive right into the data without taking the time to really (I mean really) understand the decision we’re trying to inform.</p><p>The devil is in the details- and details are what matter here. Without an understanding of how the data will be used, we’re not in a position to give the right answers.</p><blockquote><em>“If I had one hour to save the world, I would spend fifty-five minutes defining the problem and only five minutes finding the solution.”​<br>- Albert Einstein</em></blockquote><h3>The magic question</h3><p>Luckily, we don’t need to spend all of our time defining the problem. Here is the one simple question that will get to the heart of any data request within minutes:</p><h3>“What decision are you trying to make?”</h3><p><em>Subtext: What action will you take once you have the answers?</em></p><p>If there is no action, then there will be no impact. This question will cut through all of the clutter and get straight to the action.</p><p>And the answer can be VERY telling! That’s why it’s so powerful.</p><p><strong>A good response is specific! </strong>Almost immediately, you should be able to picture what they’ll do once they see the data. If not, then that’s a signal to pause and scope out the question before even thinking about the data.</p><p>A good response sounds like:</p><ul><li>“I need to know which customers are likely to churn because I want to send them $10 in loyalty credits to save them”</li><li>“I need to know how to segment our customers because I want to build a look-alike audience based on our highest value customers”</li><li>“I need to know the day of week with the highest open rates, because I want to know when we should be sending emails to get the most engagement”</li></ul><p>These responses are specific, and have a clear action in mind. It’s clear that the action will change based on what the data says. Unfortunately, not all responses start out as good responses.</p><p>Here are a few example red flags to look out for:</p><ol><li><strong>Responses that sound specific but aren’t<br></strong>Beware of responses that sound specific, but aren’t. “I need to segment our customers, so I can personalize their emails.” What does that mean? How will they personalize the email? We might learn that the email team just wants to update the subject lines to improve open rates. That information can change the entire analysis.</li><li><strong>“I’ll know what to do once I see the data” <br></strong>This is usually a sign that there’s no plan for action. These responses usually lead to the ever-so-common situation where an analysis is interesting but not actionable. <br>If you hear a response like this one, I recommend playing out a few hypotheticals with your colleague: “What will you do if the data says X? What if it says Y?” I love this exercise because it allows you both to peek into the future where you have the answers, but still don’t know what to do about it. Most people have a tendency to romanticize what the data can do for them, which is why it can be disappointing when you get the analysis but it doesn’t solve any of your problems. The game of hypotheticals helps bring everyone back to reality and be practical about what can be accomplished with the data.</li><li><strong>“Once I know the answer, I’ll be able to ask </strong>{insert follow up question}<strong>”<br></strong>More questions are great, as long as they eventually lead to an action. Endless questions just lead to more learning, and learning is cool but it’s not what runs a company — decisions do. For responses like this, follow the question trail to see if you can eventually get to an action: “What do you plan to do once you answer the follow up question?”</li><li><strong>“I don’t know”</strong><br>A skilled data analyst can usually spot a data question that’s not going to be actionable, but it can be difficult to help someone else see it the same way. Asking the stakeholder how the data will change their decision is a great way to help them see that their data question won’t give them the results they’re looking for. Ask the magic question often enough and you’re bound to get a few “I don’t know”s. When this happens, it’s a great opportunity to reset. It’s so much easier to start with a decision and figure out the data you’ll need to inform it, than the other way around.</li></ol><p>Next time someone comes to you with a data request, remember the magic question. And remember, a bad response doesn’t mean game over. It should be a starting point to get you to the final, better data question. A skilled data analyst will always tease out the real problem and understand the real decision before starting any project. This makes the difference between a data project that is just “interesting” and a data project that drives an impact.</p><p><em>Originally published at </em><a href="https://blog.narrator.ai/one-question-to-make-your-data-project-10x-more-valuable/"><em>https://blog.narrator.ai</em></a><em> on January 13, 2021.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=462d7f62795b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/one-question-to-make-your-data-project-10x-more-valuable-462d7f62795b">One question to make your data project 10x more valuable</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Why you should be using your data warehouse to sync your SaaS tools]]></title>
            <link>https://medium.com/narrator-ai/why-you-should-be-using-your-data-warehouse-to-sync-your-saas-tools-3984334f6af7?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/3984334f6af7</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[saas]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[digital-marketing]]></category>
            <dc:creator><![CDATA[Cedric Dussud]]></dc:creator>
            <pubDate>Mon, 04 Jan 2021 21:09:10 GMT</pubDate>
            <atom:updated>2021-01-25T21:15:25.381Z</atom:updated>
            <content:encoded><![CDATA[<h4>The most robust and simple way to sync between production systems is to use your warehouse as the single source of truth</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fxwScvt7vsLcEJEV.jpeg" /></figure><p>Syncing business data between 3rd party tools has become fairly common. The marketing team wants to see customer support tickets, the customer support team should be able see previous orders, the sales team needs to see all previous interactions with an account, etc.</p><p>Consequently in the past few years nearly every SaaS tool has started offering their own set of integrations to other services.</p><p>At Narrator we use dozens of third-party SaaS products and have some kind of integration with nearly all of them. We send lists of customers to email from our internal database to Sendgrid, sync calendar data to our CRM, send leads from our website to Salesforce, etc.</p><p>Given dozens of systems each supporting hundreds of integrations, most companies do a point to point integration like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*Wa0Ng-c4-2peXqsIbtFkmA.png" /><figcaption>Integrating six systems point to point</figcaption></figure><p>At Narrator we’ve been using a hub and spoke model with our data warehouse as the single source.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*KCJMuJrosNun_xQrhTq5xg.png" /><figcaption>A warehouse as the source of the integration</figcaption></figure><p>Our warehouse already has all our source data. Using it as the single source of truth for integration means that everything matches and is self consistent.</p><p>Though this is a much simpler diagram, it’s less common in practice. Tools like Narrator that facilitate syncing from the warehouse are less-widely known and relatively new.</p><p>I’ll spend the rest of this post showing why a warehouse-first approach is far better in the long run.</p><h3>Quick note on real-time data synchronization</h3><p>All the strategies below are for systems that can handle a sync delay of a few minutes to a few hours</p><blockquote><em>This post is not about syncing real-time production data within your own systems!</em></blockquote><p>In other words, a warehouse isn’t a great solution for synchronizing internal data between your own (micro)services. Those have very different constraints (like an API call to System B should return up to date info, even if part of it is owned by System A).</p><h3>What’s the problem?</h3><p>Let’s start by assuming point to point integrations are consistent and up to date — I’ll save data quality and consistency concerns for the end. There are a lot of reasons why these types of integrations can be problematic, but I’ll focus on one.</p><p>Point-to-point integrations <em>as a strategy</em> is super inflexible. Sometimes you’re lucky enough to use a fully-supported integration that matches your use case perfectly. But many times you’re not.</p><p>For example, let’s say you’re working in Customer Support using Zendesk. You’d like to see which email campaigns that customers have received. If your marketing team uses Mailchimp <a href="https://www.zendesk.com/apps/support/mailchimp-activity/">you’re in luck</a>. Zendesk supports it.</p><p>What about going the other way? What if marketing wants to send an email to customers who have recently submitted a ticket? Unfortunately that’s not supported. Or at least I didn’t find it.</p><p>Ok, so the marketing team will have to find a workaround.</p><p>So what about all customer orders? Seems reasonable that each time an agent is chatting with a customer they can pull up order information. If you’re using Stripe you’re out of luck. There’s no such integration. There’s one for Shopify, but it’s no longer supported by Mailchimp. They recommend another third-party <a href="https://shopsync.io/">service</a> that literally does only that <a href="https://shopsync.io/about">one integration</a>.</p><p>The problem is that all the different ways you’re going to want to integrate data far exceeds the prebuilt integrations out there. And of course you don’t know ahead of time what’s possible. You’ll need to look to see if it’s natively supported; if not is it supported by a service you already use, like Zapier; and if not, is there some simple other workaround? Multiply this times the hundreds of integrations you’ll do.</p><p>In practice things quickly get even more complicated. Our customer service team doesn’t just want to see a customer’s orders and support tickets. They want to see a list of at-risk customers. Oh, and so does the marketing team.</p><p>Let’s say (for the sake of example) that we define an at-risk customer as someone who hasn’t ordered in the last 30 days but has submitted a support ticket in that time. You can’t sync that list to Zendesk or Mailchimp. It doesn’t exist. You’ve got to define it somewhere.</p><p>These types of computed values pop all over the place. The sales team wants to score leads to identify which ones to focus on (total marketing site pages viewed before the lead was submitted). The marketing team wants to send a coupon to customers who put something in their cart but didn’t check out last week.</p><p>These start to sound like data questions don’t they? And since these types of questions use data across systems, it’s likely your data warehouse is the only place that can answer them.</p><p>So far we’ve identified two problems</p><ol><li>the integration you’ll need between two systems may or may not exist</li><li>some integrations require combining data from multiple systems</li></ol><p>A warehouse is clearly an excellent solution to problem 2: it has all the relevant data, we know how to query it, and it’s (or at least it should be) straightforward to combine data from various systems.</p><p>So what about problem 1? A warehouse cuts that problem down drastically. Once you’re able to get data from the warehouse into one destination (using webhooks or an intermediary like Zapier) then you can repeat the same approach for most other destinations.</p><p>So what does a proper integration with a warehouse look like?</p><h3>Avoiding integration</h3><p>Before we dive into how to integrate using a warehouse, I want to point out one additional advantage: often you don’t have to integrate at all.</p><p>Ask yourself: will the target system use the data you’re sending in a fundamental way? Or is it just additional information you want to see at the same time?</p><p>For example, building a Mailchimp email list is fundamental — it wouldn’t know what to email otherwise.</p><p>Viewing additional information about a customer in Zendesk (recent orders, marketing emails received) isn’t really fundamental to Zendesk. In fact, there’s no obvious place to show this info (you’ll have to define a <a href="https://support.zendesk.com/hc/en-us/articles/203662066">custom field</a>).</p><p>Rule of thumb: <strong>it’s fundamental if it’s a predefined concept that exists by default.</strong> Tickets for Zendesk, leads for Salesforce, orders for Stripe, email lists for Mailchimp, etc.</p><p>If the data you’re sending isn’t one of the service’s core concepts then <strong>you can probably avoid sending it in the first place.</strong></p><p>And nothing is easier than an integration that doesn’t have to happen.</p><p>Broadly there are two ways to avoid syncing: embedding and linking.</p><p><strong>Option 1: Embed</strong></p><p>If you have control over the app used by the end user, then you can embed the data they need right there.</p><p>Let’s go back to the Zendesk example. We’d love to see which customers are at risk, and for each one see their recent orders and marketing emails.</p><p>If you have a tool to build charts / dashboards you can show them directly in an app by embedding them (typically in an iframe).</p><p>Many BI tools support this kind of <a href="https://docs.looker.com/sharing-and-publishing/embedding">embedding</a> automatically, so this can be a a fairly straightforward approach.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*ZdoYTnnAL9t4gxDqPBwaqQ.png" /><figcaption>Looker embedded into Salesforce (Credit: Looker)</figcaption></figure><p>Not all third-party tools allow this, but <a href="https://www.zendesk.com/apps/support/iframe/">Zendesk</a> does, as does Salesforce.</p><p>We did this exact approach at a previous company — we used our warehouse to show additional metrics to our customer support team.</p><p>Pros: Data always up to date, can come from anything in the data warehouse, and is shown right where the user is working</p><p>Cons: sometimes requires some engineering work; only applies to apps where embedding is an option.</p><p><strong>Option 2: Link to the data</strong></p><p>If you can’t embed the data directly into an app’s UI the next best thing is to show a link to a data tool with the right information.</p><p>This is fairly simple to implement. Most BI tools allow <a href="https://docs.looker.com/sharing-and-publishing/sharing-urls">linking</a> directly to dashboards, with configurable parameters like user email.</p><p>Getting the link into the destination system isn’t always straightforward. If the target system doesn’t have a way to generate a url then you might have to send one in as a custom field (Zapier can listen to a user created in Zendesk and update a custom field on that user with the url ). And sure, at that point it might be worth just sending the actual data — but a link will point to much more data, from many systems, that is always up to date. And the link itself presumably hardly (if ever) changes, so it won’t suffer from sync issues.</p><p>Over the last year, more of our customers have started adding link directly to Narrator’s customer journey in Salesforce. This gives the sales person a clear timeline of the customer’s interaction with all systems, nicely cleaned and organized.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*98kOQEwh6PH43iGbTOlZDQ.png" /></figure><p>Pros: Data is always correct and up to date</p><p>Cons: User has to open another interface; getting the link into the destination system could require a little work</p><h3>Warehouse Integration</h3><p>So what about when you really do have to send data to a destination?</p><p>Let’s go back to building an email list in Mailchimp.</p><p>You need to do two things</p><ol><li>Collect and transform data into the proper format for the destination system</li><li>Send the transformed data to the destination system</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*oKHZyNg6QUuJDOgD-RdbGA.png" /><figcaption>Using Narrator to transform and send data from a warehouse to Mailchimp</figcaption></figure><p><strong>Transform</strong></p><p>A warehouse is ideal for transforming data — it will have access to the raw data from all source systems and can be easily queried in SQL.</p><p>To set up on ongoing integration you’ll need to build a SQL transformation that runs on a regular basis. Tools like <a href="https://docs.narrator.ai/docs/defining-a-dataset-grammar">Narrator</a> and <a href="https://getdbt.com/">dbt</a> are excellent for this. Both can create <a href="https://docs.narrator.ai/docs/materialize-a-dataset-2">new tables</a> (materialized views) in the warehouse with transformed data and keep them up to date.</p><p>Here’s Narrator’s Dataset tool creating a list of customers along with their risk level</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*jY9w6gkrgzbYGLmkbY3G7Q.png" /><figcaption>Narrator’s Dataset tool</figcaption></figure><p><strong>Send</strong></p><p>Sending transformed data to a destination system is really straightforward.</p><p>One of the primary reasons companies build point to point integrations in the first place is because transformation is difficult. The Shopify to Mailchimp integration has to understand Shopify’s data format and transform it for Mailchimp. If you’ve already transformed it yourself you only have to send it.</p><p>If you already have data in the right format most destinations will accept it through webhooks. And if not, tools like <a href="https://zapier.com/page/webhooks/">Zapier</a> will listen to a webhook and send the data to the destination</p><p>Luckily for our example Mailchimp <a href="https://mailchimp.com/developer/api/marketing/list-webhooks/add-webhook/">support</a>s using webhooks to manage email lists.</p><p>Narrator can <a href="https://docs.narrator.ai/docs/exports">export</a> any transformed data from the warehouse as a <a href="https://docs.narrator.ai/docs/exports#webhook">webhook</a>, so the transformation and integration steps can be done together. You don’t even have to write the table back to the warehouse as a materialized view.</p><p>Each dataset in Narrator has its own set of configurable integrations. Here’s a webhook to Mailchimp.</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*ksn-vAaxshcIetkW5ZSkwA.png" /><figcaption>Daily webhook integration with Mailchimp</figcaption></figure><h3>Further Advantages</h3><p>I skipped some other real problems with point-to-point integrations for the sake of clarity.</p><p>But in case you’re not convinced here are additional advantages of the warehouse model</p><p><strong>Data consistency</strong>: the data warehouse has complete, up to date, and cleaned data (otherwise it’s not a very useful warehouse). If you send the same thing to Salesforce and Zendesk you know that your sales team will have the same view as your customer support team. Any issues found can be fixed in a single spot rather than in multiple integrations.</p><p><strong>Unidirectional sync</strong>: sending data from the warehouse means that it has to be unidirectional almost by definition. In other words, if I sync something with Salesforce there’s really no way to edit it and send it back.</p><p>This drastically simplifies syncing. Everything should have a single source of truth. This includes the business logic around when it can and can’t be modified, and in what ways. Allowing other apps to edit a value either complicates syncing (the owning system might refuse an update, requiring error-handling) or requires duplicating business logic. In the worst case the values in different systems will diverge without anyone knowing.</p><p><strong>Maintenance: </strong>as with anything, the fewer moving pieces the better. Once you’re stuck with the point-to-point model you’re going to a.) have more integration points and b.) a much wider variety of sync mechanisms. Some might be through native integrations, some by third-party integrations, some through webhooks, some through custom-built APIs (by you of course).</p><p><strong>Extensibility: </strong>because the warehouse can transform any data in arbitrary ways you can basically sync with everything. There’s a clear path for how to support the next thing your business might want. Sure, it’ll be a bit of work to come up with (say) a list of customers who called support and then used a coupon, but you know how to go about it. From there sending it to Mailchimp is easy.</p><p><strong>Data Advocacy: </strong>I’m not sure what to call this one — but the more people looking at the same data, the more they’re all on the same page. What I mean by that is if every system is using the same view of an ‘at risk’ customer, then the company will all be speaking the same language. Any issues with it will be caught sooner. Has anyone else worked at a company where the CEO had their own private dashboard and would ask about it? No fun.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3984334f6af7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/why-you-should-be-using-your-data-warehouse-to-sync-your-saas-tools-3984334f6af7">Why you should be using your data warehouse to sync your SaaS tools</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to choose an attribution model, advice from a data scientist]]></title>
            <link>https://medium.com/narrator-ai/how-to-choose-an-attribution-model-advice-from-a-data-scientist-1f6a8e27896f?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/1f6a8e27896f</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[attribution]]></category>
            <category><![CDATA[marketing]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[attribution-modeling]]></category>
            <dc:creator><![CDATA[Brittany Davis]]></dc:creator>
            <pubDate>Mon, 21 Dec 2020 22:43:16 GMT</pubDate>
            <atom:updated>2021-01-14T22:31:15.961Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S26KlpdyqcIH8iAXjANzrg.png" /><figcaption>Image by: <a href="https://pixabay.com/images/search/user:mohamed_hassan%20brain/">Mohamed Hassan</a></figcaption></figure><p>First Touch, <br>Last Touch, <br>Last Non-Direct Click, <br>Time Decay, <br>Linear, <br>Any Touch 🙀, <br>Last Paid Touch, <br>U-shaped, <br>W-shaped, <br>Algorithmic….</p><p>I’ve spent many years on the data-side of marketing teams. One thing I’ve learned is that so many companies fall into the same trap when choosing an attribution model.</p><p>Every company wants to be data-driven so they assume they <em>need</em> that fancy multi-touch attribution model to capture all the complex nuances of their customer’s behavior and what led them to purchase.</p><p>…and this is exactly what the $130B/yr ad-tech industry wants us to think.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/620/0*IzAfe594fcupAizD.jpeg" /></figure><p>After seeing these same patterns play out at large enterprises like IBM, unicorn startups like WeWork, and tons of small startups… I’ve come to realize one thing:</p><p><strong>Fancy multi-touch attribution models are overhyped. Most likely, you don’t need one.</strong> There, I said it.</p><p>The truth is, I’ve looked at the data so I can objectively say that the vast majority of companies do not need a fancy multi-touch model (especially if you’re an e-commerce / D2C brand).</p><p>Even worse, when we opt for a fancy multi-touch algorithmic model unwarranted, we’re inviting a whole slew of problems that could have been avoided entirely if we had just taken the time to choose an appropriate attribution model up front.</p><h4>The downside of complex attribution models</h4><p>An overly-complex model can mean a lag in reporting while you wait for customers to convert — or alternatively, you’ll have metrics that retroactively change as more data is acquired. Of course, there’s the added maintenance for your data team and the lost budget on said fancy-attribution product you didn’t need.</p><p>Finally, let’s not forget the added confusion for the marketing practitioners who need to act the results.</p><p>All of this can be avoided by taking the time to look at the data, understand what your customers are doing, and choose an appropriate attribution model up front.</p><p>Ok, so let’s talk about choosing an attribution model…</p><h3>DOs and DON’Ts of Choosing an Attribution Model</h3><h3>DON’T</h3><ol><li><strong>Choose a model based on the specific campaign that you’re evaluating</strong> Ex. upper funnel campaigns get first-touch attribution. This is cheating.</li><li><strong>Rely on</strong> <strong>the models used by your Ad Platforms</strong> First off they don’t have access to all of the data they need to properly attribute conversions. Secondly, they’re notoriously bad at math and their “mistakes” usually make their platform seem more favorable (see Facebook in <a href="https://money.cnn.com/2016/09/27/technology/facebook-apology-tour/?iid=EL">2016</a>, <a href="https://www.wsj.com/articles/facebook-refunds-some-advertisers-after-finding-new-measurement-bug-1494954000">2017</a>, <a href="https://www.wsj.com/articles/advertisers-allege-facebook-failed-to-disclose-key-metric-error-for-more-than-a-year-1539720524?mod=article_inline">2018</a>, <a href="https://www.wsj.com/articles/facebooks-latest-error-shakes-advertisers-confidence-11606346927">2020</a> 😒).</li><li><strong>Implement a handful of models and see which “makes the most sense”</strong> This is a big headache and often leads to bias and conditional attribution (see point #1).</li></ol><h3>DO 👏</h3><ol><li><strong>Choose your attribution model objectively, with data!</strong> This is data-driven marketing. We’ll get into the approach in a sec.</li><li><strong>Use the data in your data warehouse so you can use the source-of-truth for conversion activity (not pixels) and combine data from all your ad sources</strong> In order to understand things like ROI, you need the full picture. How much was spent across ALL of your ad sources to acquire that customer?</li></ol><h3>How to choose your attribution model, with data!</h3><p>At the end of the day, a good attribution model is <strong>objective</strong> and <strong>universally applied</strong> across <strong>all ad sources</strong>. And our goal is to choose the model that <strong>minimizes complexity</strong> while <strong>capturing the nuances </strong>of each customer’s unique behavior.</p><p>To do this we need to understand how customers are behaving, so we start by looking at the data and systematically run through a series of checks that guide us to the right answer.</p><p>Here’s the approach we use at <a href="http://www.narrator.ai/narratives">Narrator</a> to decide on an attribution model:</p><h4>1. Do most customers convert on their first visit?</h4><p><strong>If YES → Use a First Touch model.</strong>Easy choice. There’s no question about how to assign credit when customers only have one session before converting. By the way, this behavior is pretty common. We’ve done this analysis for many companies and you’d be surprised by how many of them fall into this group (especially e-commerce brands).</p><p><strong>If NO → We need more info to choose a model, move to the next question.</strong>We need to understand if there’s a media mix before we can choose a model.</p><h4>2. Do customers usually engage with different ad sources before converting? (Is there a media mix?)</h4><p><strong>If YES (not common) → Use a multi-touch model. </strong>Since customers are engaging with multiple ad sources, we need to use a model that distributes credit across each source. We’ll need a multi-touch model. There are many different kinds of multi-touch models and the choice of model is a tradeoff between resources/complexity vs accuracy. The most straight-forward multi-touch approach is a linear model, but if you have the resources to implement a more advanced model we recommend using a logistic regression approach to assign partial credit.</p><p><strong>If NO → We can use a single-touch model, the next question will help us determine which one.</strong>We don’t need to distribute credit amongst the paid touches because there isn’t a media mix. This means we can use a single-touch approach (last touch/first touch/last-paid touch).</p><h4>3. Do customers who initially visit via organic, come back via a paid source?</h4><p><strong>If YES → Use a Last Touch or Last Paid Touch model.</strong>If we were to use a first-touch model we’d be masking the contribution of paid touches later on in the customer’s journey. This is why we’ll need a last touch or last-paid touch model to assign credit accordingly. Use last-paid touch if you’d like to ensure credit is always given to paid marketing.</p><p><strong>If NO → Use a First Touch model.</strong>Since organic visitors won’t come back via paid sources, we can confidently use a first-touch model without the risk of masking credit from subsequent paid touches. Essentially, when people have a paid touch in their journey, it’s the first touch. That’s why we can use a first-touch model.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ZTL2I-5bBWT5pyVK.jpg" /></figure><h3>Attribution models in the wild</h3><p>At Narrator, we’ve had the opportunity to battle-test the approach with many different companies: subscription, e-commerce, direct to consumer, B2B, retail, etc. Every time, it’s yielded some surprising insights. Mostly, because we all assume that our customers are re-visiting the site multiple times and engaging with lots of different ads along the way. But when we look at the data, we learn that this behavior very unusual.</p><p>And, with any analysis, it’s important to re-check these questions periodically to see if your customers’ behavior have changed. This will ensure you’re always using the right attribution model. At Narrator, we use <a href="https://www.narrator.ai/narratives">Narratives</a> to automatically re-run our analyses on a regular basis and monitor changes in customer behavior (ex. we run our attribution analysis weekly just to be safe).</p><p>If your team is considering different attribution models, I highly recommend looking at your data to answer these questions before getting started. It may help you save your time and resources for the more important questions that <em>actually</em> require those fancy data science models 😉</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1f6a8e27896f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/how-to-choose-an-attribution-model-advice-from-a-data-scientist-1f6a8e27896f">How to choose an attribution model, advice from a data scientist</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using customer behavior to drive better product decisions]]></title>
            <link>https://medium.com/narrator-ai/using-customer-behavior-to-drive-better-product-decisions-75e44145fc79?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/75e44145fc79</guid>
            <category><![CDATA[analysis]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[ab-testing]]></category>
            <category><![CDATA[product]]></category>
            <category><![CDATA[questions]]></category>
            <dc:creator><![CDATA[Ahmed Elsamadisi]]></dc:creator>
            <pubDate>Tue, 15 Dec 2020 15:17:58 GMT</pubDate>
            <atom:updated>2020-12-15T16:10:00.289Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6zgWoQdk2ieliISkOgrnGg.jpeg" /></figure><h3>Understanding which customer behaviors lead to conversion is the fastest way to know which product changes to make.</h3><p>As a data person, I frequently get asked for help from product teams. Over the years, I spent quite a bit of time explaining the difference between good and bad questions.</p><p>Typically product teams want to improve something (say conversion). They’ll come to me for advice on what to measure —and nearly all the time they want to discover which<strong> product </strong>changes<strong> </strong>will have an impact<strong> </strong>on their metric.</p><p>“I want to know whether X matters so I’ll split my audience, test the product change, and see if it makes an impact”.</p><p>Notice that this question is from a product perspective: ‘did a product change have an impact?’, instead of a customer perspective: ‘which customer behavior leads to conversion?’</p><p>I know this sounds like such a small nuance, but it’s often the root cause why product teams launch features that don’t increase conversion.</p><p><strong>Here are some common questions from the product perspective:</strong></p><ul><li>I want to see if <strong>when we launch a class</strong> affects the number of people who sign up. We’ll launch the same class to half our audience at 4 pm and the other half at 5 pm. Then we’ll see how the different cohorts react.</li><li>I want to test <strong>which email messaging </strong>leads to more sales. I’ll split the audience in half, send each a different email, and see how many of them purchase.</li></ul><p>The problem with these that there is no causality. If we want to increase a customer metric (like conversion), we have to ask a question about customer behavior.</p><p>Let’s look at the first example to illustrate.</p><p>“I want to see if <strong>when we launch a class</strong> affects the number of people who sign up.</p><p>Launching a class doesn’t affect customers at all. Why not? Because nothing has happened to the customer. Do they know you launched a class? If you sent a notification did they see that notification? There’s no direct connection between ‘launching’ and user behavior.</p><p>If there’s no direct connection it’s not causal — in other words, if you ‘improve’ the time at which you launch a class time you will probably not increase conversion.</p><p>Let’s try something more direct: “How does the time of day of <strong>viewing a class </strong>affect the customer likelihood to sign up for that class?”</p><p>‘Viewed a class’ is definitely a customer action. And this is a far better question for two reasons</p><ol><li>we don’t need to run an A/B test at all to answer this question. We already have this data (because customers have already viewed classes at different times) and can start answering it immediately</li><li>if we conclude that the time to view a class matters, we now have a concrete action to take (try to get more customers to view a class at X time).</li></ol><p>The question of HOW to get customers to view classes at a more optimal time is a great product question, and its progress is very easy to measure.</p><p>The approach now is straightforward. Once you know the best time for customers to view a class, you can measure the change in the percent of customers viewing the class at this time.</p><ol><li>Create a metric by dividing customers into buckets based on the time they’ve viewed a class. (20% of customers viewed at 10:00 AM, 15% at 11:00 AM …)</li><li>Make some product changes to shift the percentage of customers in each bucket to the desired one.</li><li>As the percentages change, look at the overall conversion rate again. It should increase.</li></ol><p>What about the other question?</p><p>“I want to test which email messaging leads to more sales. I’ll split the audience in half, send each a different email, and see how many of them purchase.”</p><p>The customer doesn’t care about <strong>receiving</strong> an email. It could be marked as spam or bounce, or not be noticed in many other ways.</p><p>So what’s a good customer action?</p><p>“How does <strong>opening</strong> email A vs opening email B affect the likelihood to purchase within 30 minutes?”</p><p>One primary reason I get these types of questions is that product facing things are easier to control. You can’t control exactly when a customer views a class, but you can control when you launch it to the website.</p><h3>The entire point of product questions is to understand which product changes will increase conversion. By re-framing questions from the customer’s perspective, you’ll understand which customer behaviors matter. You’ll now be solving a much simpler problem: how can I change the product to affect a specific customer behavior?</h3><p>What can I do about it?</p><p>These mistakes are natural in a data-driven org. Part of the reason I co-founded Narrator was to counteract this — our data system is designed to only ask questions from the customer’s perspective.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=75e44145fc79" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/using-customer-behavior-to-drive-better-product-decisions-75e44145fc79">Using customer behavior to drive better product decisions</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[When good data analyses fail to deliver the results you expect]]></title>
            <link>https://medium.com/narrator-ai/when-good-data-analyses-fail-to-deliver-the-results-you-expect-34361ed2936b?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/34361ed2936b</guid>
            <category><![CDATA[decision-making]]></category>
            <category><![CDATA[analysis]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Brittany Davis]]></dc:creator>
            <pubDate>Fri, 09 Oct 2020 15:39:03 GMT</pubDate>
            <atom:updated>2021-01-15T21:02:32.272Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vmTYiHRH0KqqrOlC3kH8nA.png" /></figure><h3>A cautionary tale of a data analysis, a dashboard, and a huge waste of resources</h3><p>Any good data analyst will spend their days asking “What action (X) impacts my bottom line (Y)?” We explore the data and rigorously test each hypothesis. When we finally discover that X influences Y, we run to our team and convince them to focus on X.</p><p>“Get more customers to join our loyalty program to improve LTV by 30%!” we say.</p><p>Finding this insight and convincing the team to act upon it is a feat in itself — so we go home feeling proud of a hard day’s work. Maybe we even set up a nice dashboard to help them track their progress against this new target. Each week they’ll track the % of customers in the loyalty program and devise new tactics to improve that number. So data-driven!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/350/1*-Ueqd03X4lNUT_-PwwSGJA.gif" /></figure><p>This is the dream. We found an insight and we made a meaningful impact… didn’t we? LTV remains relatively unchanged. How is that possible? What happened?</p><p>The analysis was solid. Our KPI was a good metric to track, but this is where things fell apart. We tracked our progress against the KPI, <strong>but failed to track all the assumptions that led us to focus on that KPI in the first place.</strong> The problem was that we didn’t keep an eye on the assumptions. There are a few ways to solve this problem, which we’ll talk about later on.</p><h3>Time to First Response: A Cautionary Tale</h3><p>In my last role, I spent a lot of time analyzing support tickets. Our support team was laser-focused on “time to first response”. It was so ingrained in the operational model that we even had leaderboards to celebrate the reps with the fastest response times.</p><p>I’m not kidding — some people would take their laptops into the bathroom so they could respond right away if a request came in.</p><p>This goal was born out of great intentions. We looked at the data and saw that tickets with shorter response times had higher satisfaction scores.</p><p>So… “Answer tickets faster to improve customer satisfaction rates,” we said! And they did.</p><p>But satisfaction rates didn’t improve — they actually declined.</p><p>“Time to first response” used to be a good indicator for customer satisfaction, but once it became a target, the relationship between “time to respond” and “customer satisfaction” fell apart.</p><p><strong>This happens all the time! </strong>The popular book <a href="https://www.amazon.com/Tyranny-Metrics-Jerry-Z-Muller/dp/0691174954"><em>Tyranny of Metrics</em></a> is full of stories just like mine.</p><p>We do an analysis to understand how a customer behaves. A metric becomes a KPI, it goes into a dashboard, and we completely forget how we got there. We put on our blinders and we focus on optimizing that metric at any cost, forgetting all the assumptions that got us there.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*vXJGRcNYUtIxJuYTfpC1ow.png" /></figure><blockquote><strong><em>“We put on our blinders and we focus on optimizing that metric at any cost, forgetting all the assumptions that got us there.”</em></strong></blockquote><p>When we change our behavior, we also change our customer’s behavior, so the data powering the entire analysis starts shifting. The assumptions that were once true are now wrong. And as a result, we don’t see the outcomes we want.</p><h3>Goodhart’s Law</h3><p>There’s actually a term for this phenomenon: Goodhart’s Law:</p><h3>“When a measure becomes a target, it ceases to be a good measure.”</h3><p>Meaning that when you start to optimize for a metric, you run the risk of fundamentally changing the value it represents.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/760/1*MWpjVHS1QJXbzWTE7l2F1Q.jpeg" /></figure><p>Sadly, this means that most teams will continue their days blissfully unaware that their KPI (which was at one point meaningful) has now become a vanity metric. 😱</p><p>Don’t fall into this trap.</p><h3>What can we do about it?</h3><p>Track the context that led to each decision!</p><p>This means monitoring the KPI <strong><em>and</em></strong> the context of how you came to that conclusion.</p><p>With each analysis, there is a set of criteria that needs to be met before you can claim that the findings are reliable:</p><ul><li>Does X relate to a change in Y? Yes <em>…maybe you do a hypothesis test to check for statistical significance</em></li><li>Has that relationship been consistent over time? Yes.</li></ul><p>Ok great! It’s reasonable to assume that if we change X then it will result in a change in Y.</p><p>But if any of the answers to these questions change, then you need to update your recommendation.</p><p>How do we do this?</p><h3>Create live analyses (not dashboards) to capture the context</h3><p>As data folks, our go-to option for monitoring data over time is a dashboard. Create the relevant plots and put them in a dashboard. Set it and forget it.</p><p><strong>To capture context, we need to create more than just a collection of dashboards</strong>. No one knows what to do with that. <br>What happens when 6 months from now, you’re focused on a totally different project, the underlying assumptions have changed but your team has forgotten how each plot influences the recommendation?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mbRBo2PkO3g7tHi2-TOQlA.png" /></figure><p>We love dashboards because they’re live.</p><p>But we <em>need</em> analyses because the plots are already interpreted and the takeaways are actionable.</p><p>So what if we could have the benefits of both? We would have live analyses.</p><p>Live analyses preserve the context of our decision-making criteria while keeping the insights fresh and timely, so our team can reliably and confidently reach the same conclusions as if we were there interpreting the plots for them.</p><h4>Time to First Response (as a live analysis)</h4><p>A live analysis would have kept us from falling into the trap of optimizing for a vanity metric like Time to First Response. As soon as the underlying assumptions changed, we would have the context alongside each plot to know exactly what happened and why we shouldn’t focus on time to first response anymore.</p><p>The Customer Support analysis would tell us:</p><ul><li>How did we arrive at “Time to First Response” as our KPI?</li><li>What assumptions did we check?</li><li>Are those assumptions still true?</li></ul><p>And when those assumptions failed, the recommendation would have updated to “Don’t focus on decreasing first response times, it is not impactful because XYZ is no longer true”</p><p>We’d be able to quickly see this change and respond quickly! No more “secret” vanity metrics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i__IJUVM7KHaI3mOpiWFFQ.png" /><figcaption>At Narrator, we use “<a href="https://www.narrator.ai/narratives">Narratives</a>” to share live analyses in a story-like format</figcaption></figure><p>At <a href="http://www.narrator.ai/narratives">Narrator</a>, every analysis we create is a live analysis. Each one captures our recommendations and the steps we took to get there. And we include context alongside each plot of its interpretation and why it matters. We’ve even figured out a way to condition the interpretations based on the data from the plot using variables and if-statements under the hood.</p><p>So if the data changes, the recommendations will update, making it impossible for a metric to become a vanity metric without us realizing it.</p><p>Since we adopted this approach of creating live-analyses in a story-like format, we’ve never been blindsided by secret shifts in data. We’ve created analyses that deliver value year after year and will tell us a new story as our customers’ behaviors change.</p><p>Teams that are serious about making impactful decisions with data need live insights to ensure they’re always optimizing for the right thing. Don’t repeat the same mistakes I did.</p><p>Monitor the data, provide the context, and validate each assumption over time to ensure your team is always focused on something impactful.</p><p><em>Originally published at </em><a href="https://blog.narrator.ai/when-good-data-analyses-fail-to-deliver-the-results-you-expect/"><em>https://blog.narrator.ai </em></a><em>on October 9, 2020</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=34361ed2936b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/when-good-data-analyses-fail-to-deliver-the-results-you-expect-34361ed2936b">When good data analyses fail to deliver the results you expect</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Hack: How to join tables where the key is in a blob of text?]]></title>
            <link>https://medium.com/narrator-ai/sql-hack-how-to-join-tables-where-the-key-is-in-a-blob-of-text-4c74fcc2cb39?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/4c74fcc2cb39</guid>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[hacks]]></category>
            <dc:creator><![CDATA[Ahmed Elsamadisi]]></dc:creator>
            <pubDate>Wed, 16 Sep 2020 18:28:27 GMT</pubDate>
            <atom:updated>2021-03-22T19:48:54.206Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MoJB44t81Y8EY_KQpmcTwg.png" /></figure><h3>SQL Hack: How to join tables when the key is buried in a blob of text</h3><h3>The Situation</h3><p>Text blobs are a pain, but they are commonly used with data from email platforms, Salesforce, or dynamic UTMs. In these cases, the JOIN key is probably hidden in a blob of text.</p><p>For example, say you want to join your users table using the user_id from the Salesforce notes column. But the notes data looks like this…</p><p><strong>Notes Column:<br></strong>Ahmed (user_id: 1f65e898-0f67-4d34-8ac3-ae223bb71a79) called us today</p><h3>First Attempt: Regex Parsing</h3><p>Initially, you can try to use regex to parse the user_id out of the value column. This is always annoying but can get close.</p><p>If it works, then a simple subquery and join should do the trick. However, like any free text column, the regex may not work perfectly.</p><p>Regex can be risky when the structure of the data changes:</p><p><strong>Notes Column:<br></strong>Ahmed (user: 1f65e898-0f67-4d34-8ac3-ae223bb71a79)<br>(r<em>eferences “user” not “user_id”)</em></p><p>When regex doesn’t work, use this hack…</p><h3>Solution: Use the &quot;ilike&quot; SQL hack</h3><p>Use an ilike to JOIN data by creating a dynamic key:</p><pre>SELECT <br>	...</pre><pre>FROM some_table a.    -- structured data</pre><pre>JOIN other_table b    -- data with the text blob<br>	ON ( a.column ilike &#39;%&#39; || b.other_column || &#39;%&#39;)</pre><p>This can be a handy trick if you’re in a pinch, but it can also be slow, so use it with caution. And watch out for unwanted duplication in your JOIN if the “ilike” matches more than once!</p><p>Good Luck!</p><p><em>Originally published at </em><a href="https://www.narrator.ai/blog/sql-hack-how-to-join-tables-when-the-key-is-buried-in-a-blob-of-text/"><em>https://www.narrator.ai/blog</em></a><em> on September 16, 2020</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4c74fcc2cb39" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/sql-hack-how-to-join-tables-where-the-key-is-in-a-blob-of-text-4c74fcc2cb39">SQL Hack: How to join tables where the key is in a blob of text?</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Hack: How to get first value based on a time column without JOIN or Window Function?]]></title>
            <link>https://medium.com/narrator-ai/sql-hack-how-to-get-first-value-based-on-a-time-column-without-join-or-window-function-41eae3708c19?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/41eae3708c19</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Ahmed Elsamadisi]]></dc:creator>
            <pubDate>Wed, 09 Sep 2020 17:06:55 GMT</pubDate>
            <atom:updated>2020-09-09T17:06:55.248Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L4ToBCSWJNA4A8EnrFk0VQ.png" /></figure><h3>SQL Hack: How to get first value based on time without a JOIN or a Window Function?</h3><p>This is my favorite trick in SQL!</p><p>If you are like me and have been working with data for a while, you have googled “how to get the first value without a join or window function” multiple times in your life. We often hate having to write complex queries to get a seemingly simple answer.</p><h3>For example, you want to answer questions like…</h3><ol><li>Give me the first coupon code the customer used</li><li>Give me the last page a customer visited</li><li>Give me the first invoice amount for the customer</li></ol><h3>Common ways of answering these questions</h3><ul><li>self JOIN on the MIN(timestamp)</li><li>use a ROW_NUMBER() window function</li><li>use FIRST_VALUE() window function</li></ul><p>…Yuck. These approaches are slow and messy. 😩</p><p><strong>Problems with these approaches…</strong></p><ul><li>SQL query gets really long with multiple sub-queries or CTEs (Common Table Expressions)</li><li>self JOINs and window functions are slow</li></ul><h3>The BETTER and FASTER WAY</h3><p>In my day job at <a href="http://www.narrator.ai/">Narrator</a>, I have to auto-generate complex queries, meaning that I have to get the first value of multiple columns at the same time.</p><p><strong>If the columns were timestamps then this would be EASY</strong></p><p>If you had a list of timestamps and you want the first one (based on the timestamp), this would not be a problem since you can just use MIN.</p><p>You can do a bunch of these in the same query SELECT and be done.</p><pre>SELECT</pre><pre>   [Group Columns],<br>   <br>   MIN( [Time Column]) as first_value,<br>   MAX( [Time Column]) as last_value</pre><pre>FROM table<br>GROUP by [Group Columns]</pre><p><strong>What if it’s not a timestamp?</strong></p><p>We still take the MIN (or MAX) of the timestamp column, but we do a neat trick to bring along the actual column value we want with it.</p><pre>SELECT</pre><pre>[Group Columns],</pre><pre>LTRIM(MIN(CONCAT([TIME COLUMN]),{value_column})), [TIME COLUMN]) AS first_value</pre><pre>FROM table<br>GROUP by [Group Columns]</pre><p>This takes advantage of MIN/MAX and GROUP BY so you don’t have to JOIN or use a window function! I think this is faster, simpler, and cleaner to read than other methods.</p><p><strong>Why does this work?</strong></p><p>We concatenate the timestamp with the value of the column we’re looking for. After the warehouse does a normal MIN / MAX on that string we can remove the timestamp, leaving us with the data we want.</p><p><strong>PROS</strong></p><ol><li>Fast!</li><li>Works inline (doesn’t require CTEs or self JOINs)</li><li>Works with GROUP BY</li></ol><p><strong>CONS</strong></p><ol><li>Kind of annoying to type</li></ol><h3>How it works</h3><p>Let’s assume you have an orders table and you want to find the timestamp of the first order based on category.</p><pre>SELECT <br>    <br>   category,<br>   min(completed_at) as first_order_at</pre><pre>FROM orders<br>group by </pre><p>Now, what if you want the name of that order?</p><p>Well, the trick is to Concat the name to the timestamp then compute the MIN then remove the timestamp.</p><p>The SQL:</p><pre>LTRIM(MIN(CONCAT(completed_at), name)),MIN(completed_at))</pre><p>1. Concatenate the timestamp and the column you want to find the first value for</p><p>CONCAT(completed_at, name)</p><p>This returns the following rows:</p><pre>2018-01-01T01:48:02The Best Name</pre><pre>2018-01-01T02:12:06Some Name</pre><p>2. Find the MIN of that string (the timestamp is at the beginning of the string so it will grab the <em>first</em> value)</p><p>MIN(CONCAT(completed_at, name))</p><p>This returns the following rows:</p><pre>2018-01-01T01:48:02The Best Name</pre><p>4. Remove the Timestamp from the result</p><pre>LTRIM(MIN(CONCAT(completed_at, name)), MIN(completed_at))</pre><p>This returns the following rows:</p><pre>The Best Name</pre><p>Note that LTRIM, when given a string as a second argument, will remove that string from the front of its first argument.</p><p>DONE!</p><p>To handle NULL values, use the query below</p><pre>NULLIF(LTRIM(MIN(CONCAT({time_column}), NVL({value_column},&#39;&#39;)), MIN({time_column}) ,&#39;&#39;)</pre><p>Here is the EXACT one I use (at <a href="https://www.narrator.ai/">Narrator</a>). It’s a bit uglier but a bit more efficient (saves an aggregation)</p><pre>NULLIF(SUBSTRING(MIN(CONCAT(DATE_TRUNC(&#39;second&#39;, {time_column}), NVL({value_column},&#39;&#39;))),20, 1000),&#39;&#39;)&quot;</pre><p>I hope you find this as helpful as I did! I have been doing data science for a while and love finding new hacks to make complex data tasks easier. These tricks really come in handy when dealing with event-based data (which is the structure of the data we use at the company I founded — <a href="http://narrator.ai/">Narrator.ai</a>).</p><p><strong>Do you have SQL tricks? Share them here! I’d love to hear them.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=41eae3708c19" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/sql-hack-how-to-get-first-value-based-on-a-time-column-without-join-or-window-function-41eae3708c19">SQL Hack: How to get first value based on a time column without JOIN or Window Function?</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Top 10 Signs you have a Data Modeling problem]]></title>
            <link>https://medium.com/narrator-ai/top-10-signs-you-have-a-data-modeling-problem-bf7a35d83122?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/bf7a35d83122</guid>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Ahmed Elsamadisi]]></dc:creator>
            <pubDate>Tue, 08 Sep 2020 12:25:54 GMT</pubDate>
            <atom:updated>2021-03-22T22:20:58.422Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-eDr6-jHvmk-gFxDj5OHnw.png" /></figure><h3>Top 10 Signs You Have a Data Modeling Problem</h3><p><em>This post was originally published on </em><a href="https://www.narrator.ai/blog/top-10-signs-you-have-a-data-modeling-problem/"><em>Narrator.ai </em></a><em>on September 8, 2020</em></p><h3>1. Your warehouse is really slow</h3><p><strong>Situation</strong></p><blockquote>“I just hit Run Query. I am going to get a cup of coffee and hopefully it’s done when I get back.”<br> — Data Analyst</blockquote><p><strong>Problem</strong></p><ul><li>Every time a transformation runs in your warehouse, it drops and recreates every table, thus processing time only grows.</li><li>Table locks can add up, causing your table to need to be reprocessed before it can be used.</li></ul><h3>2. You have changed your BI tool multiple times</h3><p><strong>Situation</strong></p><blockquote>“We just invested in Looker and it will solve all of our problems. So far everything is really fast and matches, unlike Chartio which was slow and nothing matched.” <br>— BI Team</blockquote><p>6 months later…</p><blockquote>“I don’t know why Looker is super slow and none of the metrics match. I think Tableau Online solves this problem really well” — BI Team</blockquote><p><strong>Problem</strong></p><ul><li>Only a subset of data models make it into your BI tool when it’s first implemented. Then, as time goes on, you require more data models as your business evolves, and soon you’ve recreated all the issues that you experienced with your last BI tool. It’s slow and there are inconsistencies in metrics.</li><li>BI tools have to query your warehouse and cache the results of those queries. If a BI tool is accessing a query with multiple tables, but some of those tables still need to update, then it will cache the wrong results. This will also give you inconsistent metrics across dashboards.</li></ul><h3>3. Your transformations are more than 30 lines</h3><p><strong>Situation</strong></p><blockquote>“I just need to add sales_location to this table but the SQL (and python, sometimes) to generate it is literally 1200 lines. I will just create a new table with a cleaner query.&quot;<br> — Data Engineer</blockquote><p><strong>Problem</strong></p><ul><li>Each query needs to bridge multiple sources — and because foreign keys are often missing — you write insane sub queries to try to get a clean join. This leads to queries that are really long and complicated.</li><li>With complexity comes lack of understanding and so when something needs to change, your team will elect to either: (1) rewrite the data model with only what they need or (2) wrap the entire query in a CTE (Common Table Expression) and add to the query, increasing the length of the query and its complexity.</li></ul><h3>4. Reporting takes too long to set up</h3><p><strong>Situation</strong></p><blockquote>“I just spent my whole day debugging the sales_data table and I’m pretty sure the query is actually wrong.&quot; <br> — Data Analyst</blockquote><blockquote>“Oh yeah, you should use sales_data_v2.” <br> — Data Engineer</blockquote><blockquote>“What? Why do we still have sales_data then?&quot; <br> — Data Analyst</blockquote><blockquote>“Some dashboard depends on it and we can’t deprecate it because it is used by executives”<br> — Data Engineer</blockquote><p><strong>Problem</strong></p><ul><li>Every reporting request requires a long process to find the right data, validate it, and then assemble the visualization.</li><li>Finding, validating, and assembling the data is what takes forever. Your BI Engineer or Analyst has to parse 1000+ line SQL queries and run sanity checks to compare your aggregations with widely used dashboards.</li><li>As your data models evolve, you often don’t delete older data models because some executive (or stubborn person) at your company depends on it for a dashboard and you’re nervous about migrating it. This leads to weeks of wasted work of re-debugging a table that someone already knew was wrong.</li></ul><h3>5. Data engineering has an endless backlog</h3><p><strong>Situation</strong></p><blockquote>“We are currently blocked by Data Engineering.” <br>— Analytics Team</blockquote><p><strong>Problem</strong></p><ul><li>As more requests come in each Data Engineer has to make a very complex decision: build a new data model or update an old model. Creating a new model adds another model to maintain for life. Updating a model means queries get insanely long or introduces a bug that leads to broken dashboards or numbers not matching.</li></ul><h3>6. Dashboards have numbers that don’t match</h3><p><strong>Situation</strong></p><blockquote>“Why does the Sales Dashboard say we have 12,034 sales but when I calculate the sum of sales in Sales by Ad Source dashboard, I see 13,193. The data is wrong.” <br>— Marketing Team</blockquote><p><strong>Problem</strong></p><ul><li>Dashboards visualize your data models. If numbers don’t match in a dashboard, it’s an issue with your data model. If you have multiple data models with similar data then you’ll end up with two dashboards using two different data models but computing the same metrics.</li><li>This leads to two sources of truth for one metric.</li></ul><h3>7. No one knows where to find the data they need</h3><p><strong>Situation</strong></p><blockquote>“Why don’t you use the data dictionary?”<br> — Data Engineer</blockquote><blockquote>“It’s out of date so I just look at the transformation SQL” <br>— Data Analyst.</blockquote><p><strong>Problem</strong></p><ul><li>Data Dictionaries are practically impossible to maintain so they go out of date the moment they are deployed. Automated Data Dictionaries usually just tell you the column names and types, but not the assumptions made.</li><li>As soon as one table’s documentation is out of date, the data team will know that they cannot depend on the documentation so they will go back to the method they can trust — parsing the complex SQL transformations.</li></ul><h3>8. The data team is not trusted</h3><p><strong>Situation</strong></p><blockquote>“The data is always wrong” <br>— Stakeholder</blockquote><p><strong>Problem</strong></p><ul><li>With complex data models, even simple data tasks are complicated, risky, and error-prone. Stakeholders only see the data integrity issues when asking “simple” questions and stop believing in the team’s ability to answer more questions.</li><li>Data integrity issues lead to a mistrust in the data team.</li></ul><h3>9. Other teams are buying their own data tools to do their own analyses</h3><p><strong>Situation</strong></p><blockquote>“We set up Heap so we could do our own analytics.” <br>— Website Team</blockquote><p><strong>Problem</strong></p><ul><li>Engineering teams are constantly told that they don’t need data analysts or to do analyses. They’ve been told that these point and click tools can replace the need for data teams. THIS IS NOT TRUE!</li><li>Data teams now have to do twice the work: re-do the analysis and then educate the team on why the numbers they pulled are different than the numbers the data team got.</li></ul><h3>10. You’re constantly explaining why “simple” questions are more complex than they seem</h3><p><strong>Situation</strong></p><blockquote>“Well, figuring out how many people called us from the website is really hard.” <br>— Data Analyst</blockquote><blockquote>“Why? People come to the site then call us. I just want to know the % of people. Why is this complicated?”<br> — Stakeholder</blockquote><p><strong>Problem</strong></p><ul><li>Simple questions become very complex when they bridge data systems because foreign keys are usually not available.</li><li>Even if we could relate the data, there are many dimensions to each question, so a data analyst needs to consider all the edge cases before answering. Each edge case takes hours to test and, as a stakeholder, it is hard to see the rigor that goes into answering a simple question.</li></ul><p>My talk about answering data questions goes into a bit more depth about this: <a href="https://www.google.com/url?sa=t&amp;rct=j&amp;q=&amp;esrc=s&amp;source=web&amp;cd=&amp;cad=rja&amp;uact=8&amp;ved=2ahUKEwjS7PaPsMPrAhUDVt8KHXs5CTkQwqsBMAF6BAgLEAQ&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DGWPzsVOThWA&amp;usg=AOvVaw3vqXxsQnor8dh5qLhAXi5q">How to Answer Data Questions Without Being Miserable</a></p><h3>So you have a data modeling problem. Where should you go from here?</h3><p>It is not your fault! You’re probably using a <a href="https://medium.com/narrator-ai/star-schema-is-set-up-for-failure-f5e0bdfba189">Star Schema</a> and that is the status quo “best practice” so you think these problems are simply the nature of the beast. We have good news for you — they’re not.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C6fBvMQBjKlq0V0S6NRNiQ.jpeg" /></figure><p>There is a better way.</p><p>Check out Narrator: After suffering through these same problems, we came up with a data model that reflects today’s reality. It is a single, 11-column data model that can be used to assemble any table.</p><p>See for yourself: <a href="https://www.narrator.ai/">Narrator.ai</a></p><p><strong>What are other problems you’ve experienced working with data? Could they be caused by bad data modeling?</strong></p><p>Comment with your problems and we can help you identify the problem.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bf7a35d83122" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/top-10-signs-you-have-a-data-modeling-problem-bf7a35d83122">Top 10 Signs you have a Data Modeling problem</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Star Schema is Set Up for Failure]]></title>
            <link>https://medium.com/narrator-ai/star-schema-is-set-up-for-failure-f5e0bdfba189?source=rss----c40f0c134c80---4</link>
            <guid isPermaLink="false">https://medium.com/p/f5e0bdfba189</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[star-schema]]></category>
            <dc:creator><![CDATA[Brittany Davis]]></dc:creator>
            <pubDate>Thu, 27 Aug 2020 17:13:49 GMT</pubDate>
            <atom:updated>2021-03-22T22:15:56.002Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zYulG983iVUch0sA6c2bVw.png" /></figure><p><em>We interviewed our CEO/co-founder (and ex-Data Engineer) about why star schemas are doomed from the start</em></p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fplayer.vimeo.com%2Fvideo%2F448635577%3Fapp_id%3D122963&amp;dntp=1&amp;display_name=Vimeo&amp;url=https%3A%2F%2Fvimeo.com%2F448635577&amp;image=https%3A%2F%2Fi.vimeocdn.com%2Fvideo%2F941754739_1280.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=vimeo" width="1280" height="720" frameborder="0" scrolling="no"><a href="https://medium.com/media/d74848197016f82b9277f2bf7bbbd8fe/href">https://medium.com/media/d74848197016f82b9277f2bf7bbbd8fe/href</a></iframe><h3>What is star schema?</h3><p><a href="https://en.wikipedia.org/wiki/Star_schema">Star schema</a> is the most common approach to data modeling — primarily made up of fact and dimension tables.</p><p>Why you need data modeling</p><ul><li>Your company has a lot of data across many different data sources: website and marketing interactions, internal databases, Salesforce, Zendesk, etc…</li><li>Querying production data directly is dangerous because these systems weren’t designed for analytics</li><li>Therefore, you build an analytics layer that contains all the data you need for analysis, reporting, and BI</li></ul><h3>How star schema works</h3><p>You break your analytics data up into:</p><ul><li><strong>Facts</strong> or <strong>measures</strong> (what you want to measure, i.e. leads, sales…)</li><li><strong>Dimensions</strong> (how you want to slice your data, i.e. gender, company, contract size…)</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/552/1*Xbs63HnzanZXjYpnQqxNGw.gif" /><figcaption>Example Star Schema Data Model (<a href="http://gkmc.utah.edu/ebis_class/2003s/Oracle/DMB26/A73318/schemas.htm">Source</a>)</figcaption></figure><p>Data Engineers create many of these fact and dimension tables on top of your raw production data in your data warehouse, which enables analysis, reporting, and BI</p><h3>Where did star schema come from?</h3><p>Star schema was created because storage was expensive and fact and dimension tables make it easy to aggregate large amounts of data into smaller chunks. With the advances in data warehousing, storage is now super cheap, BUT we’re still using fact and dimension tables.</p><h3>What’s wrong with star schema if it’s been around for so long?</h3><p>Back in the time of Microsoft stored procedures, it was a great idea! But companies are different now.</p><ul><li>In the past, you had one internal database. Now, on day one of starting your new company, you already have tens, sometimes hundreds of tables across all the different SaaS tools and internal databases.</li><li>In the past, you’d define your sales dashboard and not need to change it for months. Now, with everyone on your team being data-driven, you’re asking many more questions on a daily basis.</li></ul><p>Your data, and your ability to answer questions rapidly and accurately is your competitive advantage. Therefore, for a competitive company, the number of data questions grows exponentially.</p><p>Unfortunately, a star schema is bad at handling change.</p><p>When deploying a star schema, data engineers have to imagine all the questions you want to ask (facts/measures) and all the ways you want to ask those questions (dimensions) and it’s truly impossible to capture all of it.</p><h3>There has to be a correct way to implement star schema, right?</h3><p>The problem is when you look behind the curtain of each fact and dimension table you’re met with a cryptic 1000 line query. This query is full of crucial assumptions that were necessary when the table was generated months, if not years ago.</p><p>And when your CEO asks for a new dimension on the dashboard, no one wants to decode that 1000 line query. It’s too risky. So you end up duplicating the logic to create a similar reporting table with the new dimension and then leave the old table with all the old assumptions intact (no risk). But, now that you have a similar table, you’ve separated the source of truth. There are two tables with similar data, but because of slightly different assumptions dashboards won’t match.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aZSrS4tTJsJZiuZM7FCEEw.png" /><figcaption>Dashboards won’t match</figcaption></figure><p>There is a right way to use a star schema, but you have to spend weeks planning out every change before deploying to production. And this is for EVERY table and EVERY change! Then data teams can’t move fast enough to keep up with all the new data questions being asked on a daily basis.</p><h3>So is it just out of date?</h3><p>That’s right. The assumptions that created star schema are no longer relevant.</p><ul><li>Data Science and analysis are now more important than building dashboards and plots</li><li>Storage and compute power have gotten way cheaper</li><li>There’s a lot more data, and it changes much more frequently</li></ul><h3>I’m a new founder who wants to set up a data system, should I use star schema?</h3><p>Listen, before you do that, go talk to companies that have had a star schema for more than two years. They will tell you the struggles they’ve experienced, and the dangers of this approach: the teams that are being blocked by not being able to get the data they need.</p><h3>Will you ever use a star schema ever again?</h3><p>No! Actually, it’s funny because I’ve thought about what would I do if Narrator doesn’t take off. I’ve seen the alternative. With Narrator, every 1 analyst was supporting 8 companies. When I was at my last company we had 40 people supporting only 1 company and answering much fewer questions using a star schema.</p><p>That’s why I want everyone to try <a href="http://narrator.ai/">Narrator</a>. Once you experience it there’s no going back!</p><p><em>Originally published on </em><a href="https://www.narrator.ai/blog/star-schema-is-set-up-for-failure/"><em>https://www.narrator.ai/blog</em></a><em> on August 27, 2020.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f5e0bdfba189" width="1" height="1" alt=""><hr><p><a href="https://medium.com/narrator-ai/star-schema-is-set-up-for-failure-f5e0bdfba189">Star Schema is Set Up for Failure</a> was originally published in <a href="https://medium.com/narrator-ai">Narrator</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>