LinkedIn

Twitter

GitHub


<p>By&nbsp;Leeor Engel</p>



<p>Analytics are critical to success in business today. Businesses can’t expect to understand their customers or the best path forward without constantly leveraging and gaining insights from their data. Just like business leaders use analytics to be more effective, so too should engineering leaders!</p>



<p>Leveraging analytics in your daily work as an engineering manager will help you make better decisions and increase your confidence in those decisions. Simply put, if you build the skills up and use them — it’s like a <em>superpower</em>. It will open up a whole new world of opportunities to make you and your team more effective.</p>



<h2 class="wp-block-heading"><strong>Why it matters</strong></h2>



<p>One of the things that comes up a lot in my job as an engineering manager are subjective arguments about things that are actually <em>quite measurable and objective</em>. What’s even more absurd is that very often the thing in question <em>is </em>actually measured and recorded somewhere. It’s just that nobody bothered to pull it into a consumable form so it could be used. Producing reports using data squashes subjective arguments with actual numbers.</p>



<h2 class="wp-block-heading"><strong>Keep up your tech chops</strong></h2>



<p>If you’re like me, you miss writing code every day. You like the opportunity to move the needle at a higher level, but still crave technical work. Weaving analytics into your work can help quench that thirst. It provides you problem solving work that is key to your job, but isn’t on the critical path for projects. It’s definitely one way to help keep your tech skills from getting rusty.</p>



<p>And SQL is fun! Accessing data requires no complex deployments. It’s serverless! And today it’s easier than ever.</p>



<h2 class="wp-block-heading"><strong>Modern Tools</strong></h2>



<p>You might be thinking you would have to write a bunch of code to ETL data from various places — but you can actually do quite <em>a lot without ever writing a single line of code</em>. Really! The days of writing your own ETL scripts for common data sources are a thing of the past.</p>



<p>Today there is a whole space dedicated to fully managed ETL processes with pre-built connectors to 100’s of popular SaaS tools and products like JIRA, Github &amp; Zendesk — all into your data warehouse of choice.</p>



<p>Just sign up, setup some connectors, point the tool at your data warehouse, initiate a sync, and <em>boom </em>— get super useful data at your fingertips.</p>



<p>Of course, If you are looking to perform analytics on your own application’s data, you might still need to write some code. But there’s also a good chance your company already has some ETL processes sending this data to a data warehouse or data lake. If they don’t, this might be a good time to consider championing that effort, because in this day and age it’s hard to imagine an effective organization that doesn’t constantly collect and analyze their own data.</p>



<p>Once data is in a data warehouse, you need a tool to provide you an environment to query the data and produce dashboards. I use a number of dashboards on a weekly basis for many different tasks (more on use cases later). A current favourite of mine is <a href="https://www.sisense.com/product/data-teams/">Periscope Data</a>, but there are many vendors in this space such as <a href="https://mode.com/" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">Mode</a> and <a href="https://www.tableau.com/" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">Tableau</a>.</p>



<p>Once you have data synced into your data warehouse and access to an analytics tool, it’s time to write some SQL.</p>



<h2 class="wp-block-heading"><strong>Leveling Up Your SQL Chops</strong></h2>



<p>Is your SQL a little rusty? Most engineers have a lot of experience working with SQL in a <a href="https://www.sisense.com/glossary/oltp/">OLTP</a> context — but writing analytics queries requires some other SQL constructs you might not be as familiar with. The article <a rel="noreferrer noopener" aria-label=" (opens in a new tab)" href="https://blog.jooq.org/2016/04/25/10-sql-tricks-that-you-didnt-think-were-possible/" target="_blank">Top 10 SQL Tips and Tricks that You Didn’t Know were Possible</a> is a great place to start and covers the powerful SQL features you need for writing analytics queries. There are a few in particular that are really important that I use all the time. The article above explains them extremely well, so I will only summarize them briefly here.</p>



<h2 class="wp-block-heading"><strong>Common Table Expressions</strong></h2>



<p>Common Table Expressions (CTE’s for short) are a way to re-factor your sub-queries into their own separate named queries. Since <em>everything is a table</em> in SQL, you can capture those results and reference them in other subsequent queries or even other CTE’s. CTE’s are defined using the <a href="https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">WITH clause</a>. The other really cool thing about CTE’s is that they are <em>recursive</em>. This makes SQL a <a href="https://stackoverflow.com/questions/900055/is-sql-or-even-tsql-turing-complete/7580013#7580013" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">turing complete</a> language!</p>



<h2 class="wp-block-heading"><strong>Window Functions</strong></h2>



<p>Window functions are one of those things that end up being the secret sauce behind some of my most powerful reports. They let you look <em>ahead</em> or <em>behind </em>within your result set from the perspective of the current row in that result set.</p>



<p>In particular <em>LEAD</em> and LAG are super useful. Lead will look ahead a configurable number of rows in your result set, while lag will look behind.</p>



<h2 class="wp-block-heading"><strong>Treat writing analytics reports like a software engineering problem</strong></h2>



<p>Once you start writing a lot of reports, you will find that you really need to make sure your SQL is <em>readable and maintainable</em>. It’s very easy to end up with long and incomprehensible SQL. Treat the SQL code in your reports like any other code you would write. Use source code control to version your reports. Some analytics tools support integration with source code control natively, such as PeriscopeData’s <a href="https://dtdocs.sisense.com/article/git-integration">git integration</a>.</p>



<p>Extract sub-queries into common table expressions and give them clear names that convey their purpose. Re-use common SQL code with CTE’s or views. Avoid overly fancy SQL tricks or optimizations if there is a simpler way that is easier to understand. Write comments to explain any unavoidable tricky bits to aide the reader (and your future self).</p>



<p>As an example, take a look at the following before and after pieces of code based on a contrived <em>employee</em> schema:</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/sql-code-block-1.png" alt="SQL Code block 1" class="wp-image-81739"/><figcaption>SQL Before</figcaption></figure>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/sql-code-block-2.png" alt="SQL Code block 2" class="wp-image-81733"/><figcaption> SQL After </figcaption></figure>



<p>You can see the full gist <a href="https://gist.github.com/leeorengel/8ea6bb583c579bced356bb25aa3a1c06" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">here</a>.</p>



<p>The first uses no special SQL features. The second makes use of common table expressions. The difference is pretty pronounced. In the first example, notice how quickly you get lost in the subqueries — and there’s only 2!</p>



<p>In the second example the indentation never gets deeper than one level. The indentation also remains linear with the addition of more sub-queries. It always baffles me when I see walls of SQL in reports that are almost completely unreadable. It’s almost as if it they were never intended to be maintained!</p>



<h2 class="wp-block-heading"><strong>Some Use Cases</strong></h2>



<p>Let’s take a look at a few real world use cases.</p>



<h4 class="wp-block-heading"><strong>Project Forecasting</strong></h4>



<p>I make pretty heavy use of project forecasts on projects I manage. The main idea is to use <a href="https://lookforwardconsulting.com/2016/12/12/yesterdays-weather-an-extreme-programming-planning-technique-using-story-points/" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">yesterday’s weather</a> to help predict how a project’s progress is tracking against a target date. I use these reports directly with my engineers to track progress and make course corrections when things are looking bad (which is usually much sooner than they realize). Note the use of colours in the report as <a href="http://www.perceptualedge.com/articles/ie/visual_perception.pdf" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">immediate indicators</a> of project health.</p>



<p>JIRA reporting capabilities are <em>very</em> limited. Using an ETL tool with JIRA you can have a live updated project scoreboard easily.</p>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/baz-service.png" alt="BAZ service" class="wp-image-81745"/></figure>



<p>A project milestone forecast live synced from JIRA</p>



<p>You can see the project forecast dashboard I’ve created on <a href="https://github.com/leeorengel/jira_project_forecast" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">github</a>.</p>



<h4 class="wp-block-heading"><strong>Project Scoreboards</strong></h4>



<figure class="wp-block-image size-full fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/scoreboard.png" alt="Scoreboard" class="wp-image-81751"/></figure>



<p><a href="https://resources.franklincovey.com/franklincovey-blog/people-play-differently-when-they-are-keeping-score-2" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">People play differently when they are keeping score</a>. Having simple dashboards which capture this kind of data can be really powerful. They help motivate a team to see something through to completion and provide visibility and transparency to upper management and stakeholders.</p>



<h2 class="wp-block-heading"><strong>Other Use Cases</strong></h2>



<p>I use reports for many other things too. Here are just a few other use cases to give you a sense of the breadth of utility analytics can be to your daily work as a manager.</p>



<h4 class="wp-block-heading"><strong>Pulse Reports</strong></h4>



<p>I call these pulse reports because they are excellent at business level monitoring. Any good analytics and dashboarding software has the ability to setup subscriptions to send you runs of your reports on a configurable cadence. Set these up and have reports land directly into your inbox for a quick look.</p>



<p>One example where I use this is with in progress ticket times. As I find JIRA’s kanban boards hard to read (they also don’t account for many things like holidays). I setup a simple report to show me with traffic lights if any tickets have been in-flight for more than a certain number of days. This provides an early warning sign of someone being blocked on a ticket. This is also really useful when you miss daily standups.</p>



<h4 class="wp-block-heading"><strong>Ad-hoc Reporting</strong></h4>



<p>Sometimes bugs pop up that warrant some deeper investigation based on historical data or trends. Producing ad-hoc reports for forensic purposes can be really useful. Another advantage of this sort of report is you can send it to any stakeholders and clearly articulate the patterns you saw in the data. You can also re-use them if the issue re-surfaces.</p>



<h2 class="wp-block-heading"><strong>Tips &amp; Advice</strong></h2>



<h4 class="wp-block-heading"><strong>Measure twice, measure again</strong></h4>



<p>Scrutinize the results you get. See if they match your intuition. Measure things in different ways to see if you arrive at different results.</p>



<h4 class="wp-block-heading"><strong>Use these tools ethically</strong></h4>



<p>Don’t blindly trust numbers. The numbers tell a story, but be careful confusing precision with accuracy. Much can go untracked and thus is unrepresented in the data. A good example is using JIRA data to track engineer productivity. I would recommend you avoid doing this. It will <em>lie to you</em>. Engineer output and value cannot be solely measured in tickets shipped or ticket cycle times.</p>



<h4 class="wp-block-heading"><strong>High margins of error can be OK</strong></h4>



<p>In his book <a href="https://www.audible.ca/pd/How-to-Measure-Anything-Audiobook/B071VQMKJL?source_code=GDGGBSH0822170007&amp;ds_rl=1250324&amp;gclid=CjwKCAjwk7rmBRAaEiwAhDGhxN_bYsPjIEW0npaof6Y3mmr8xtFJXvwYszdyUXoPl0XMp9c3cz-AyxoCwmAQAvD_BwE&amp;gclsrc=aw.ds" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">How to Measure Anything</a>, Douglas W. Hubbard talks about the value of <em>reductions</em> of uncertainty. He describes the common misconception that measurements must <em>eliminate</em> uncertainty rather than simply reduce it in order to be useful. This couldn’t be further from the truth.</p>



<p>Think about it: if you could reduce uncertainty on an investment representing weeks or months of work by 10% for just a few hours of work, wouldn’t that be worth it?</p>



<p>Expect high margin of error in some cases. The insights you get and the fact that you are looking at <em>relative comparisons</em> still make even relatively high error-margin data super valuable. Remember, if it helps you reduce uncertainty for a relatively low investment — it’s a no-brainer!</p>



<p>Project forecasting is a good example of this. My objective with a project forecast is not to be 90% right — it’s to <em>avoid being 100% wrong</em>. A 20–25% error is totally acceptable.</p>



<h2 class="wp-block-heading"><strong>Final Thoughts</strong></h2>



<p>Using analytics in your job can be a major boost to your effectiveness as an engineering manager. You will likely find many more great use cases specific to your team or organization.</p>



<p>So go out and see what data your organization already has. See what you can do with it. If you haven’t hooked up your SaaS tools to an ETL tool for data extraction, start that initiative in your company. Finally, see what team-specific metrics you are missing and might help you drive towards the outcomes you want for your team.</p>



<p>So what are you waiting for? Start poking around your data warehouse and get some answers!</p>



<p><em>Originally published at </em><a href="http://leeorengel.com/analytics-for-the-engineering-manager/" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)"><em>https://leeorengel.com</em></a><em> on May 29, 2019.</em></p>


Analytics for the Engineering Manager