<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Malek Damiri</title>
    <description>The latest articles on DEV Community by Malek Damiri (@malek_damiri_68c6fecee5b7).</description>
    <link>https://dev.to/malek_damiri_68c6fecee5b7</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4169722%2Ffc761bc6-38cf-45da-ad64-b510fb047331.png</url>
      <title>DEV Community: Malek Damiri</title>
      <link>https://dev.to/malek_damiri_68c6fecee5b7</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/malek_damiri_68c6fecee5b7"/>
    <language>en</language>
    <item>
      <title>Volume went up and revenue went down. Here is the pandas to explain it</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Thu, 19 Nov 2026 13:51:31 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/volume-went-up-and-revenue-went-down-here-is-the-pandas-to-explain-it-5d5p</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/volume-went-up-and-revenue-went-down-here-is-the-pandas-to-explain-it-5d5p</guid>
      <description>&lt;p&gt;Trading volume rose 5.4% last month. Fee revenue fell 4.0%.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Where the \$79k went&lt;/strong&gt;&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Effect&lt;/th&gt;
&lt;th&gt;Amount&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Volume&lt;/td&gt;
&lt;td&gt;+\$106k&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Mix&lt;/td&gt;
&lt;td&gt;-\$48k&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Rate&lt;/td&gt;
&lt;td&gt;-\$136k&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;&lt;strong&gt;Total change&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;-\$79k&lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;If you work on revenue analytics in any business that earns a small percentage of a large flow, you have been in the meeting that follows. Someone says the big clients were busy. Someone else says pricing must have slipped. A third person suspects the data. Everyone has a theory and no one has a number.&lt;/p&gt;

&lt;p&gt;There is a standard way to replace the theories with arithmetic. Finance teams call it price, volume, and mix analysis. It splits a change in revenue into three parts that add up exactly to the total, and it takes very little pandas. This article walks through it on a synthetic dataset, so you can run every line.&lt;/p&gt;

&lt;h2&gt;
  
  
  The setup
&lt;/h2&gt;

&lt;p&gt;The example is a brokerage-style business with three client segments that pay very different fees:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Segment&lt;/th&gt;
&lt;th&gt;Share of clients&lt;/th&gt;
&lt;th&gt;Fee&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Retail&lt;/td&gt;
&lt;td&gt;70%&lt;/td&gt;
&lt;td&gt;35 bps&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Professional&lt;/td&gt;
&lt;td&gt;25%&lt;/td&gt;
&lt;td&gt;12 bps&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Institutional&lt;/td&gt;
&lt;td&gt;5%&lt;/td&gt;
&lt;td&gt;4 bps&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Volumes are lognormal, so a handful of clients account for most of the flow, which is how these businesses look in practice. I planted three changes in September: retail volume drops, institutional volume jumps, and the professional and institutional segments each get a small fee cut. Planting the answer matters. It lets you check that the method finds what you put there.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;numpy&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;np&lt;/span&gt;
&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;pandas&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;pd&lt;/span&gt;

&lt;span class="n"&gt;rng&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;np&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;random&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;default_rng&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;7&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="n"&gt;segments&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;            &lt;span class="c1"&gt;# share of clients, typical monthly volume, fee in bps&lt;/span&gt;
    &lt;span class="s2"&gt;"Retail"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;        &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mf"&gt;0.70&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mf"&gt;2e5&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="mi"&gt;35&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="s2"&gt;"Professional"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;  &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mf"&gt;0.25&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mf"&gt;4e6&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="mi"&gt;12&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="s2"&gt;"Institutional"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mf"&gt;0.05&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mf"&gt;9e7&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;   &lt;span class="mi"&gt;4&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="n"&gt;growth&lt;/span&gt;    &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="s2"&gt;"Retail"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;0.92&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Professional"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;1.05&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Institutional"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;1.45&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="n"&gt;fee_shift&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="s2"&gt;"Retail"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;0.0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;  &lt;span class="s2"&gt;"Professional"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt;&lt;span class="mf"&gt;0.5&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Institutional"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt;&lt;span class="mf"&gt;0.4&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;

&lt;span class="n"&gt;rows&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;share&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;typical&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;bps&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;segments&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;items&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;cid&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="nb"&gt;range&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kt"&gt;int&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;400&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="n"&gt;share&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;month&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"2026-08"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"2026-09"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;sept&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;month&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="s2"&gt;"2026-09"&lt;/span&gt;
            &lt;span class="n"&gt;vol&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;rng&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;lognormal&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;np&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;log&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;typical&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mf"&gt;0.9&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;growth&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;sept&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="mf"&gt;1.0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
            &lt;span class="n"&gt;fee&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;bps&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;fee_shift&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;sept&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="mf"&gt;0.0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
            &lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;append&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{seg[:3]}-{cid:03d}"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;month&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;vol&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;fee&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="n"&gt;df&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;pd&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;DataFrame&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;columns&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"client"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"segment"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"month"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"volume"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"fee_bps"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;df&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"revenue"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;df&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"volume"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="n"&gt;df&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"fee_bps"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mf"&gt;1e4&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Run it and you get the headline numbers at the top of this article. Volume is up, revenue is down, and the blended rate fell by about half a basis point. The blended rate alone can’t tell you why. It falls when you cut prices, and it falls just the same when a low-fee client has a busy month.&lt;/p&gt;

&lt;h2&gt;
  
  
  The idea
&lt;/h2&gt;

&lt;p&gt;Revenue is volume times rate, summed over segments. Write total volume as &lt;em&gt;V&lt;/em&gt;, each segment’s share of that volume as &lt;em&gt;s&lt;/em&gt;, each segment’s rate as &lt;em&gt;r&lt;/em&gt;, and the blended rate as &lt;em&gt;b&lt;/em&gt;, the share-weighted average of the segment rates. Revenue is then &lt;em&gt;V × b&lt;/em&gt;, and the change from period 0 to period 1 splits into three effects:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Volume effect:&lt;/strong&gt; &lt;code&gt;(V1 - V0) * b0&lt;/code&gt;. What you would have gained if only the total moved and everything else stayed as it was.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Mix effect:&lt;/strong&gt; &lt;code&gt;V1 * sum((s1 - s0) * (r0 - b0))&lt;/code&gt;. What you gained or lost because volume shifted between segments that earn different rates.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Rate effect:&lt;/strong&gt; &lt;code&gt;V1 * sum(s1 * (r1 - r0))&lt;/code&gt;. What you gained or lost because rates changed inside segments.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Add them up and the terms cancel to &lt;code&gt;V1 * b1 - V0 * b0&lt;/code&gt;, which is the revenue change. There is no residual to explain away.&lt;/p&gt;

&lt;p&gt;One detail in the mix term is worth a sentence. Measuring each segment’s rate against the blended rate, &lt;code&gt;(r0 - b0)&lt;/code&gt;, gives the same total as using &lt;code&gt;r0&lt;/code&gt; alone, because the share changes sum to zero. It makes the per-segment numbers readable, though. Gaining share in a segment that earns less than average comes out negative, which is what your intuition expects.&lt;/p&gt;

&lt;h2&gt;
  
  
  The decomposition
&lt;/h2&gt;

&lt;p&gt;Aggregate to one row per segment per month, compute share and rate, and the rest is a few lines.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="n"&gt;seg&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;df&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;groupby&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"month"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"segment"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;as_index&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="kc"&gt;False&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
         &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;agg&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;volume&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"volume"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"sum"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;revenue&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"revenue"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"sum"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"rate"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;  &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"revenue"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"volume"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"share"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"volume"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;groupby&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"month"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"volume"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;transform&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"sum"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="n"&gt;p0&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"month"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="s2"&gt;"2026-08"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;set_index&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"segment"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;p1&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;seg&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"month"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="s2"&gt;"2026-09"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;set_index&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"segment"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="no"&gt;V0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;V1&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;p0&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"volume"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;p1&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"volume"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="no"&gt;R0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;R1&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;p0&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"revenue"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;p1&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"revenue"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;b0&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="no"&gt;R0&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="no"&gt;V0&lt;/span&gt;

&lt;span class="n"&gt;volume_effect&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;V1&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="no"&gt;V0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="n"&gt;b0&lt;/span&gt;
&lt;span class="n"&gt;mix_by_seg&lt;/span&gt;    &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="no"&gt;V1&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;p1&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"share"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;p0&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"share"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;p0&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"rate"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;b0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;rate_by_seg&lt;/span&gt;   &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="no"&gt;V1&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="n"&gt;p1&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"share"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;p1&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"rate"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;p0&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"rate"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="k"&gt;assert&lt;/span&gt; &lt;span class="n"&gt;np&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;isclose&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;volume_effect&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;mix_by_seg&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;rate_by_seg&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;R1&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="no"&gt;R0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Keep the &lt;code&gt;assert&lt;/code&gt;. It is the line that makes other people trust the method, and it will catch you the day someone changes a join upstream.&lt;/p&gt;

&lt;h2&gt;
  
  
  The result
&lt;/h2&gt;

&lt;p&gt;The three effects are the chart at the top of this article: volume added \$106k, mix cost \$48k, and rate cost \$136k, for a net change of -\$79k. The by-segment terms show where each one came from.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Which segment did it&lt;/strong&gt;&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Segment&lt;/th&gt;
&lt;th&gt;Mix&lt;/th&gt;
&lt;th&gt;Rate&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Retail&lt;/td&gt;
&lt;td&gt;-\$46k&lt;/td&gt;
&lt;td&gt;\$0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Professional&lt;/td&gt;
&lt;td&gt;+\$1k&lt;/td&gt;
&lt;td&gt;-\$32k&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Institutional&lt;/td&gt;
&lt;td&gt;-\$3k&lt;/td&gt;
&lt;td&gt;-\$105k&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Now the meeting has something to work with. The extra volume was worth \$106k at August’s blended rate. Retail, the highest-fee segment, lost share, and that cost \$46k. The fee cuts cost \$136k, most of it institutional, because a small cut applied to the largest volume is the biggest number on the page.&lt;/p&gt;

&lt;p&gt;All three planted changes came back out, each in the right place. The headline that volume grew 5% was true and beside the point.&lt;/p&gt;

&lt;h2&gt;
  
  
  The chart
&lt;/h2&gt;

&lt;p&gt;One bar per effect is enough. Bars that added revenue grow right from zero, bars that cost revenue grow left, and the net change sits underneath.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;matplotlib&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;pyplot&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;plt&lt;/span&gt;

&lt;span class="n"&gt;plt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;rcParams&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"font.family"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"Helvetica Neue"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Arial"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"sans-serif"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;

&lt;span class="n"&gt;mix_effect&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;rate_effect&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;mix_by_seg&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;rate_by_seg&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;effects&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="s2"&gt;"Volume"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;volume_effect&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Mix"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;mix_effect&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Rate"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;rate_effect&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Net change"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="no"&gt;R1&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="no"&gt;R0&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="no"&gt;BLUE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;RED&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;GRAY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="no"&gt;INK&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s2"&gt;"#2a78d6"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"#e34948"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"#8b857b"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"#1d1b18"&lt;/span&gt;

&lt;span class="n"&gt;fig&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ax&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;plt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;subplots&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;figsize&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;8&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mf"&gt;2.7&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;names&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="kt"&gt;list&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;effects&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="o"&gt;::-&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;                       &lt;span class="c1"&gt;# barh draws bottom-up&lt;/span&gt;
&lt;span class="n"&gt;values&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;effects&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;n&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;names&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="n"&gt;colors&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="no"&gt;GRAY&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="s2"&gt;"Net change"&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;BLUE&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="no"&gt;RED&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;n&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="nb"&gt;zip&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;names&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="n"&gt;ax&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;barh&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;names&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;color&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="n"&gt;colors&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;height&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mf"&gt;0.52&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="n"&gt;pad&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nb"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;abs&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="mf"&gt;0.03&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;y&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="nb"&gt;enumerate&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;                    &lt;span class="c1"&gt;# value at the end of each bar&lt;/span&gt;
    &lt;span class="n"&gt;ax&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;text&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;pad&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt;&lt;span class="n"&gt;pad&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;y&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{v/1e3:+,.0f}k"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
            &lt;span class="n"&gt;va&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"center"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ha&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"left"&lt;/span&gt; &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;v&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="k"&gt;else&lt;/span&gt; &lt;span class="s2"&gt;"right"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;fontsize&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;11&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;color&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="no"&gt;INK&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="n"&gt;ax&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;axvline&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;color&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="no"&gt;INK&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;linewidth&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;ax&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;set_xlim&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nb"&gt;min&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="mf"&gt;1.28&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;values&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="mf"&gt;1.28&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;ax&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;xaxis&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;set_visible&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kc"&gt;False&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;ax&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;tick_params&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;axis&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="s2"&gt;"y"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;length&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;labelsize&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="mi"&gt;11&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;labelcolor&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="no"&gt;INK&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;side&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;ax&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;spines&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;values&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;side&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;set_visible&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kc"&gt;False&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;fig&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;tight_layout&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzh9k2p21vbcvgh8qsqm5.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fzh9k2p21vbcvgh8qsqm5.png" alt="Bar chart by Malek Damiri: volume, mix, and rate effects and the net change in fee revenue from August to September" width="800" height="270"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;What the code above draws: the three effects and the net change, in thousands of dollars.&lt;/em&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Four things to decide before you ship it
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;The order is a choice.&lt;/strong&gt; This version values the volume change at the old blended rate and the rate change at the new shares. Other orderings move a small amount between the effects. None is more correct. Pick one, write it down, and use the same one every month, or your trend will reflect your method changing rather than the business.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;The level of aggregation changes the answer.&lt;/strong&gt; Decompose by segment, and a pricing change for one client shows up as rate. Decompose by client, and a client who trades more shows up as mix. Both are right. They answer different questions, so say which one you ran.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;New and lost clients have no rate in one of the periods.&lt;/strong&gt; At client level you have to decide what to do with them. I treat them as mix, a share moving to or from zero, and report them on their own line so they don’t hide inside the total.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Rate here means revenue divided by volume.&lt;/strong&gt; Anything that changes revenue without changing volume, such as rebates, credits, and minimum fees, lands in the rate effect. If those are large, split them out before you decompose.&lt;/p&gt;

&lt;h2&gt;
  
  
  What to take away
&lt;/h2&gt;

&lt;p&gt;A revenue change is explained when you can say it in one sentence: revenue moved by X, of which volume was A, mix was B, and rate was C. If the write-up can’t be put in that form, the analysis isn’t finished. The code to get there is short enough that there is no reason to guess.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://malekdamiri.com/writing/revenue-decomposition-volume-mix-rate-pandas/volume-mix-rate-decomposition-malek-damiri.pdf" rel="noopener noreferrer"&gt;Download this article as a PDF&lt;/a&gt;&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/revenue-decomposition-volume-mix-rate-pandas/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>python</category>
      <category>pandas</category>
      <category>analytics</category>
      <category>datascience</category>
    </item>
    <item>
      <title>How to build a RevOps metric dictionary</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Tue, 17 Nov 2026 13:50:25 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/how-to-build-a-revops-metric-dictionary-28eo</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/how-to-build-a-revops-metric-dictionary-28eo</guid>
      <description>&lt;p&gt;Every company has a metric dictionary. In most of them it lives in people’s heads, in the SQL behind the dashboards, and in the slide where a number was last explained. Writing it down is not documentation for its own sake. It means the next argument about a number can be settled by reading instead of by meeting.&lt;/p&gt;

&lt;h2&gt;
  
  
  What a metric dictionary is
&lt;/h2&gt;

&lt;p&gt;One entry per metric, in plain language, readable by someone in finance, sales, or data without translation. It is not a data catalog, which lists tables and columns. It is not a dashboard inventory. It sits above both and answers three questions: what does this metric mean, who decides, and where does the number come from.&lt;/p&gt;

&lt;p&gt;If a metric is not in the dictionary, it is not a metric yet. It is a query someone ran.&lt;/p&gt;

&lt;h2&gt;
  
  
  The fields each entry needs
&lt;/h2&gt;

&lt;p&gt;Eight fields cover almost everything.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt; &lt;strong&gt;Name.&lt;/strong&gt; One canonical name, plus the aliases people actually use so the entry can be found.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Definition.&lt;/strong&gt; One or two sentences on what it measures and what it excludes.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Formula in words.&lt;/strong&gt; Not SQL. A sentence a non-technical reader can check against the SQL.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Owner.&lt;/strong&gt; A person, the one who decides what the metric means and signs off on changes.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Source of truth.&lt;/strong&gt; Which system, and which table or report inside it, wins when sources disagree.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Grain.&lt;/strong&gt; What one row represents: per customer per month, per deal, per invoice line.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Refresh.&lt;/strong&gt; How often the number updates and how to tell when it last did.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Known gaps.&lt;/strong&gt; What the number does not capture, which cases are handled by hand, and where it is known to drift.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The last field is the most useful and the least often written. A metric with its gaps listed is one people trust more, not less, because they can see that someone has looked.&lt;/p&gt;

&lt;h2&gt;
  
  
  A few example entries
&lt;/h2&gt;

&lt;p&gt;Bookings. Definition: contract value of opportunities closed-won in the period. Formula: sum of the amount on closed-won opportunities by close date, net of discounts. Owner: sales operations lead. Source of truth: the CRM opportunity object. Grain: one row per opportunity. Refresh: nightly. Known gaps: amendments after close are not reflected unless the opportunity is reopened.&lt;/p&gt;

&lt;p&gt;Recognized revenue. Definition: revenue earned in the period under the company’s recognition policy. Formula: as published by finance at close. Owner: the controller. Source of truth: the general ledger, fed by billing. Grain: one row per customer per month. Refresh: monthly, after close. Known gaps: not available before close, and any pre-close estimate is labeled as an estimate.&lt;/p&gt;

&lt;p&gt;Active customer. Definition: a customer with at least one paid invoice in the trailing window. Formula: count of distinct billing customer IDs with a paid invoice in the trailing window. Owner: RevOps. Source of truth: billing, joined to the account mapping table. Grain: one row per customer per day. Refresh: daily. Known gaps: customers on unpaid pilots are excluded, and parent and child accounts count once.&lt;/p&gt;

&lt;p&gt;These are generic on purpose. Yours will differ in every detail. The shape is the point.&lt;/p&gt;

&lt;h2&gt;
  
  
  Source-of-truth rules
&lt;/h2&gt;

&lt;p&gt;A metric has exactly one source of truth. Other systems can hold copies, and the dictionary should say they are copies. The rule I use is that the system where a fact is created owns it. Deals are created in the CRM, so the CRM owns bookings. Invoices are created in billing, so billing owns billings and revenue. The warehouse creates nothing. It joins.&lt;/p&gt;

&lt;p&gt;That makes the warehouse the source of truth only for derived metrics, the ones that need both systems to exist. Even then, the entry names which upstream system wins when the two disagree, so a conflict has an answer before it happens.&lt;/p&gt;

&lt;h2&gt;
  
  
  How to keep it alive
&lt;/h2&gt;

&lt;p&gt;A dictionary dies the day it stops matching the dashboards. Three habits keep it current. Every dashboard tile links to its dictionary entry, and a tile without an entry does not ship. Every change to a definition goes through the owner and leaves a note in the entry saying what changed and from which period. Once a quarter, each owner confirms their entries are still right or fixes them. An entry nobody will own gets deleted, because if nobody owns it, nobody should be reporting it.&lt;/p&gt;

&lt;p&gt;Keep it somewhere with history. Plain text in a repository works well. A wiki works if someone tends it. A spreadsheet works until there are two copies of it.&lt;/p&gt;

&lt;p&gt;The dictionary will not end disagreements. It changes what they are about. Instead of arguing over whose number is right, people argue over whether the definition is right, and that is an argument you can actually finish.&lt;/p&gt;

&lt;p&gt;The slides for this essay are on Speaker Deck: &lt;a href="https://speakerdeck.com/malekdamiri/the-revops-metric-dictionary-by-malek-damiri" rel="noopener noreferrer"&gt;The RevOps metric dictionary&lt;/a&gt;.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/revops-metric-dictionary/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. More from Malek Damiri on revenue operations at &lt;a href="https://malekdamiri.com/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>analytics</category>
      <category>sql</category>
      <category>management</category>
      <category>productivity</category>
    </item>
    <item>
      <title>A status file for every project</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Thu, 12 Nov 2026 13:50:26 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/a-status-file-for-every-project-20j7</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/a-status-file-for-every-project-20j7</guid>
      <description>&lt;p&gt;I usually have a dozen or more analytics projects open at once. Most of them are waiting on something: a decision, a credential, a stakeholder’s answer. The cost isn’t the work. It’s remembering where each one stands when I come back to it.&lt;/p&gt;

&lt;p&gt;What fixed it for me is small. Every project gets a status file, and the file has the same five parts.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt; &lt;strong&gt;Current status.&lt;/strong&gt; A few sentences on what is true now, written for someone with no context. Usually that’s me in three weeks.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Open questions.&lt;/strong&gt; What I don’t know yet, and who can answer it.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Next actions.&lt;/strong&gt; Concrete steps, in order. “Review the output with the team,” not “continue analysis.”&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Blockers.&lt;/strong&gt; Anything that stops progress regardless of effort, kept separate so it doesn’t hide in the to-do list.&lt;/li&gt;
&lt;li&gt; &lt;strong&gt;Key files.&lt;/strong&gt; Where the work lives, so I don’t have to search for it.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Update it when you stop, not when you start
&lt;/h2&gt;

&lt;p&gt;The file is only useful if it’s current, and the only reliable moment to update it is the end of a working session, while the details are still in your head. Two minutes then saves twenty later.&lt;/p&gt;

&lt;h2&gt;
  
  
  Let it tell you what’s stale
&lt;/h2&gt;

&lt;p&gt;Because the files share a format, a short script can read all of them and print a briefing: what’s blocked, what’s waiting on me, and what hasn’t been touched in a week. I start the day with that list. It’s more accurate than my memory, and less optimistic.&lt;/p&gt;

&lt;p&gt;The same habit works in a notebook. The format matters less than the rule: no project gets put down without a note about where to pick it up.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/a-status-file-for-every-project/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. More from Malek Damiri on revenue operations at &lt;a href="https://malekdamiri.com/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>productivity</category>
      <category>analytics</category>
      <category>management</category>
      <category>career</category>
    </item>
    <item>
      <title>How to define revenue metrics that finance and sales both accept</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Tue, 10 Nov 2026 13:51:19 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/how-to-define-revenue-metrics-that-finance-and-sales-both-accept-4lnd</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/how-to-define-revenue-metrics-that-finance-and-sales-both-accept-4lnd</guid>
      <description>&lt;p&gt;Ask finance and sales for last quarter’s revenue and you will get two numbers. Neither team is wrong. They are answering different questions with the same word, and until someone writes down what the word means, every quarterly review starts with a reconciliation nobody planned for.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why the two teams disagree
&lt;/h2&gt;

&lt;p&gt;Sales counts what was sold, when it was sold. Finance counts what was earned, when it was earned. A contract signed in March for a service delivered over the next twelve months is March revenue to a sales leader and one twelfth of that per month to a controller. Add discounts, multi-year terms, usage pricing, and refunds, and the gap gets wide enough that each side starts to suspect the other’s number.&lt;/p&gt;

&lt;p&gt;The disagreement is not about accuracy. It is about definition. That means it can be settled on paper before the quarter, instead of in a meeting after it.&lt;/p&gt;

&lt;h2&gt;
  
  
  One definition per metric, one owner
&lt;/h2&gt;

&lt;p&gt;Every metric that appears in a deck gets one written definition, one formula in plain words, and one owner, a person who can be asked what it means. If two teams need different versions, they are two metrics with two names. “Revenue” and “bookings” are not competing versions of the same thing. They are different things, and the slide should say which one it is showing.&lt;/p&gt;

&lt;p&gt;The owner is not whoever runs the query. It is the person who decides what the metric means and signs off when the definition changes. Finance owns revenue. Sales operations owns bookings. RevOps usually ends up owning the bridge between them, which is where most of the confusion lives.&lt;/p&gt;

&lt;h2&gt;
  
  
  Bookings, billings, and revenue
&lt;/h2&gt;

&lt;p&gt;Bookings are the value of contracts signed in the period. They measure selling, and they belong to sales. Billings are what was invoiced in the period. They are what cash collection follows, and they belong to finance operations. Revenue is what was earned in the period under whatever recognition policy finance applies. It belongs to the controller.&lt;/p&gt;

&lt;p&gt;In any given month the three can be far apart and all be correct. An annual prepaid contract is one booking, one billing, and twelve months of revenue. A monthly usage contract is twelve of each. None of that is a problem. The problem is a slide labeled “revenue” that is actually bookings, shown to someone who will compare it to the income statement.&lt;/p&gt;

&lt;h2&gt;
  
  
  Decide when to recognize
&lt;/h2&gt;

&lt;p&gt;Recognition is finance’s call, and the metric definitions should defer to it. What RevOps has to decide is everything upstream of that: what date goes on a booking, signature or start date; what counts as closed-won in the CRM, a signed contract, a verbal yes, or a purchase order; and what happens to a booking when the deal changes after signing.&lt;/p&gt;

&lt;p&gt;Pick one answer for each and write it down. The classic fight is a deal signed on the last day of a quarter that starts on the first day of the next. With definitions in place, it is a booking in one quarter and revenue in the next, and nobody has to argue about it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Edge cases decided in advance
&lt;/h2&gt;

&lt;p&gt;The list of edge cases is finite and mostly predictable: discounts and credits, refunds and clawbacks, multi-year contracts, mid-term upgrades and downgrades, churn before the contract ends, foreign currency, pilots that convert, and deals that come through a partner.&lt;/p&gt;

&lt;p&gt;For each one, write what happens to bookings, billings, and revenue. One line, not a paragraph. When a new case shows up, the owners decide it within the week and the decision joins the list. The goal is that nobody has to invent an answer during a close.&lt;/p&gt;

&lt;h2&gt;
  
  
  Change control
&lt;/h2&gt;

&lt;p&gt;Definitions will change. Pricing changes, a new product line launches, an accounting standard gets updated. The rule is that a definition changes only with a short written note: what changed, why, from which period, and whether history is restated. The dashboard carries a version number. A trend line that crosses a definition change gets a marker on it.&lt;/p&gt;

&lt;p&gt;Without that, every change becomes a silent break in the series, and six months later nobody can explain why the number jumped.&lt;/p&gt;

&lt;p&gt;Metrics that finance and sales both accept are not a compromise between their two numbers. They are both numbers, each with a name and an owner, and a written explanation of the gap between them. The agreement comes from the definition existing before anyone needs it.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/how-to-define-revenue-metrics-finance-and-sales-accept/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. More from Malek Damiri on revenue operations at &lt;a href="https://malekdamiri.com/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>finance</category>
      <category>sales</category>
      <category>analytics</category>
      <category>management</category>
    </item>
    <item>
      <title>Revenue reconciliation: a practical operating model for finance and sales</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Thu, 05 Nov 2026 22:18:34 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/revenue-reconciliation-a-practical-operating-model-for-finance-and-sales-3kho</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/revenue-reconciliation-a-practical-operating-model-for-finance-and-sales-3kho</guid>
      <description>&lt;p&gt;Finance closes the month from the billing system. Sales reports from the CRM. The analytics team builds its dashboards from the warehouse. Three systems, three revenue numbers, and a recurring argument about which one is right.&lt;/p&gt;

&lt;p&gt;Usually none of them is wrong. They measure different things at different moments. The problem is that nobody has written down which number is for what, so every gap looks like an error. This is the operating model I’d put in place to make reconciliation a routine instead of a fight.&lt;/p&gt;

&lt;h2&gt;
  
  
  Three systems, three jobs
&lt;/h2&gt;

&lt;p&gt;The billing system is the record of what was invoiced and collected. The CRM is the record of what was sold and what is expected to close. The warehouse joins the two, keeps history, and is where the analysis happens.&lt;/p&gt;

&lt;p&gt;Each is authoritative for something and none is authoritative for everything. Billing owns invoiced and recognized revenue. The CRM owns bookings and pipeline. The warehouse owns nothing on its own. It owns the reconciliation between the other two and the reporting built on top. When the warehouse disagrees with billing about revenue, billing wins. When it disagrees with the CRM about what was booked, the CRM wins. Saying that out loud once saves a lot of meetings.&lt;/p&gt;

&lt;h2&gt;
  
  
  Who owns which number
&lt;/h2&gt;

&lt;p&gt;Every number that reaches an executive deck needs one owner, a person rather than a team. Finance owns revenue. Sales operations owns bookings. RevOps owns the mapping between them: which closed-won deal became which invoice, and which CRM account is which billing customer.&lt;/p&gt;

&lt;p&gt;That mapping is the real work. Deals and invoices rarely line up one to one. A deal becomes several invoices, an invoice covers several deals, an account gets renamed in one system and not the other. Keep the mapping as its own table in the warehouse, owned by one person, and treat every unmapped record as a task with a due date.&lt;/p&gt;

&lt;h2&gt;
  
  
  A monthly cadence
&lt;/h2&gt;

&lt;p&gt;Reconciliation that happens when someone notices a gap is too late. Put it on the calendar and tie it to the close.&lt;/p&gt;

&lt;p&gt;In the first few business days after month end, finance closes billing and publishes recognized revenue by customer. Over the next few days, RevOps pulls bookings from the CRM and invoices from billing, joins them in the warehouse, and produces the variance report. By the end of the following week, the open items have been reviewed, the disputes settled, and the report signed off by both owners.&lt;/p&gt;

&lt;p&gt;Monthly is the minimum. For large deals I’d also check bookings against invoicing weekly, because a big contract that never gets invoiced is the kind of gap you want to find in week one, not at close.&lt;/p&gt;

&lt;h2&gt;
  
  
  Tolerance thresholds
&lt;/h2&gt;

&lt;p&gt;Not every gap deserves a ticket. Decide in advance what counts as a difference worth chasing, and set it two ways: an absolute amount and a percentage, at the customer level and at the total level. A small timing difference on one invoice is noise. The same difference repeated across dozens of customers is a pattern, and the total-level threshold is what catches it.&lt;/p&gt;

&lt;p&gt;Both thresholds get written down, agreed by finance and sales operations, and revisited once a year. Anything inside tolerance is logged and left alone. Anything outside gets an owner and a date.&lt;/p&gt;

&lt;h2&gt;
  
  
  How disputes get settled
&lt;/h2&gt;

&lt;p&gt;Most disputes fall into three categories. Timing: the deal closed in one month and the invoice went out in the next. Scope: the CRM says one amount and the signed contract says another. Identity: the same customer exists under two names, or two customers share one.&lt;/p&gt;

&lt;p&gt;Each category gets a default answer. Timing: the invoice date drives revenue and the close date drives bookings, and both are correct. Scope: the signed contract wins and the CRM is corrected to match. Identity: the billing customer ID is the master and the CRM carries it as a field. When a dispute fits none of the categories, the two owners meet, decide, and the decision becomes a new rule. The goal is that the same argument never happens twice.&lt;/p&gt;

&lt;h2&gt;
  
  
  What gets written down
&lt;/h2&gt;

&lt;p&gt;Four things. The variance report itself, every month, archived where anyone can find it. The exception log: each item outside tolerance, its root cause, who fixed it, and how. The rule book: the default answers above plus every rule added since. And the mapping table, with history, so you can see what changed and when.&lt;/p&gt;

&lt;p&gt;After a few months the exception log tells you where the process leaks. If most exceptions are identity problems, fix how accounts get created. If most are scope, the CRM amount field needs a control. The log turns reconciliation from cleanup into diagnosis.&lt;/p&gt;

&lt;p&gt;None of this is sophisticated, and most of it is not SQL. The hard part is getting two teams to accept that the number each of them produces is right for its purpose and wrong for the other’s. Once that is agreed, reconciliation stops being a monthly argument and becomes a monthly checklist.&lt;/p&gt;

&lt;p&gt;The slides for this essay are on Speaker Deck: &lt;a href="https://speakerdeck.com/malekdamiri/revenue-reconciliation-a-practical-operating-model-for-finance-and-sales-by-malek-damiri" rel="noopener noreferrer"&gt;Revenue reconciliation: a practical operating model&lt;/a&gt;.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/revenue-reconciliation-operating-model/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. More from Malek Damiri on revenue operations at &lt;a href="https://malekdamiri.com/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>finance</category>
      <category>sales</category>
      <category>analytics</category>
      <category>management</category>
    </item>
    <item>
      <title>When pipeline doesn’t start with a form fill</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Tue, 03 Nov 2026 13:50:36 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/when-pipeline-doesnt-start-with-a-form-fill-2odh</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/when-pipeline-doesnt-start-with-a-form-fill-2odh</guid>
      <description>&lt;p&gt;Most attribution models assume a tidy sequence. Someone clicks an ad, fills in a form, becomes a lead, and gets routed to a rep. The data exists because the form created it.&lt;/p&gt;

&lt;p&gt;A lot of B2B selling doesn’t work that way. Reps find people on LinkedIn, trade a few messages, and book a meeting directly. There is no form, no UTM, and no lead record until someone remembers to create one. The deal is real, but the paper trail isn’t.&lt;/p&gt;

&lt;p&gt;If your rep scorecard is built on form-driven data, three things happen. Outbound looks weaker than it is. Marketing gets credit by default for whatever it touched last. And reps learn that the scorecard doesn’t describe their work, so they stop trusting it. That last one costs the most, because a scorecard nobody trusts changes nothing.&lt;/p&gt;

&lt;p&gt;Here is where I’ve landed.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Anchor on the meeting, not the lead.&lt;/strong&gt; The first meeting is the earliest event that reliably exists in both the calendar and the CRM. Start there and work backwards.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Ask the rep, in one field.&lt;/strong&gt; “How did this start?” with five options beats any inferred model. Require it when the meeting is created, not at close, when memory has faded.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Separate sourcing from influence.&lt;/strong&gt; Who started the conversation and what helped it along are different questions. One number can’t answer both.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Publish the rules.&lt;/strong&gt; A scorecard is a negotiated definition of good. Write down how each number is calculated and let reps challenge it.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Accept some imprecision.&lt;/strong&gt; Directionally right and trusted beats precise and ignored.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;None of this is sophisticated. The hard part is agreeing on definitions and then holding to them long enough for the numbers to mean something.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/when-pipeline-does-not-start-with-a-form/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. More from Malek Damiri on revenue operations at &lt;a href="https://malekdamiri.com/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sales</category>
      <category>marketing</category>
      <category>analytics</category>
      <category>productivity</category>
    </item>
    <item>
      <title>Teaching yourself SQL when your job is ops</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Thu, 29 Oct 2026 13:00:33 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/teaching-yourself-sql-when-your-job-is-ops-bml</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/teaching-yourself-sql-when-your-job-is-ops-bml</guid>
      <description>&lt;p&gt;I taught myself SQL. No bootcamp and no data degree. I took an online course, did a lot of practice problems, and then started answering real questions at work. If you’re in sales ops, RevOps, or finance and thinking about learning it, here is what I’d pass on.&lt;/p&gt;

&lt;h2&gt;
  
  
  Start with questions, not syntax
&lt;/h2&gt;

&lt;p&gt;Pick three questions you get asked every month. Which reps are creating pipeline? Where do deals stall? Which accounts have gone quiet? Learn only what you need to answer those. &lt;code&gt;SELECT&lt;/code&gt;, &lt;code&gt;WHERE&lt;/code&gt;, &lt;code&gt;GROUP BY&lt;/code&gt;, and one &lt;code&gt;JOIN&lt;/code&gt; cover more ground than you’d expect.&lt;/p&gt;

&lt;h2&gt;
  
  
  Do the practice problems anyway
&lt;/h2&gt;

&lt;p&gt;LeetCode-style problems feel artificial, and they are. They still build the habit of breaking a question into steps, and they put window functions and CTEs in front of you long before your day job would. Twenty minutes a day beats a weekend binge.&lt;/p&gt;

&lt;h2&gt;
  
  
  Learn window functions earlier than feels reasonable
&lt;/h2&gt;

&lt;p&gt;Most operations questions are really “compared to what?” This month against last month. This rep against the team. First touch against latest touch. &lt;code&gt;ROW_NUMBER&lt;/code&gt;, &lt;code&gt;LAG&lt;/code&gt;, and a running &lt;code&gt;SUM&lt;/code&gt; answer a large share of them.&lt;/p&gt;

&lt;h2&gt;
  
  
  Distrust your first answer
&lt;/h2&gt;

&lt;p&gt;A query that runs is not a query that’s right. Joins quietly duplicate rows, and filters quietly drop them. Count rows before and after every join, and reconcile one number against a source you trust before you send anything to anyone.&lt;/p&gt;

&lt;h2&gt;
  
  
  Write it so the next person can read it
&lt;/h2&gt;

&lt;p&gt;The next person is usually you, three months from now. Use CTEs with plain names, and leave a comment on anything that isn’t obvious.&lt;/p&gt;

&lt;p&gt;The payoff is speed. The gap between having a question and having an answer shrinks from days to minutes, and that changes which questions you bother to ask.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/teaching-yourself-sql-in-ops/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. More from Malek Damiri on revenue operations at &lt;a href="https://malekdamiri.com/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>career</category>
      <category>beginners</category>
      <category>productivity</category>
    </item>
    <item>
      <title>What revenue operations should own, and what it should hand off</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Thu, 22 Oct 2026 15:42:04 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/what-revenue-operations-should-own-and-what-it-should-hand-off-e4i</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/what-revenue-operations-should-own-and-what-it-should-hand-off-e4i</guid>
      <description>&lt;p&gt;Revenue operations collects work. Anything that touches revenue data and has no obvious owner lands there: a report request, a broken field, a territory dispute, a question about a commission check. Each one is reasonable on its own. Together they turn the team into a help desk for the whole go-to-market org, and the work only RevOps can do gets done last.&lt;/p&gt;

&lt;p&gt;The fix is to decide on purpose what the team owns, and to hand the rest to a named owner on the other side. Here is where I’d draw the line.&lt;/p&gt;

&lt;h2&gt;
  
  
  Own the definitions
&lt;/h2&gt;

&lt;p&gt;Someone has to decide what counts as a qualified opportunity, a booking, an active customer, and churn, and where each stage starts and ends. Those definitions need one owner and one written home, and every report should read from them.&lt;/p&gt;

&lt;p&gt;If marketing, sales, and finance each calculate pipeline their own way, the company doesn’t have a pipeline number. It has three opinions. This is the one job RevOps can’t hand off, because no other team sits across all of them.&lt;/p&gt;

&lt;h2&gt;
  
  
  Own the handoffs between teams
&lt;/h2&gt;

&lt;p&gt;Each team owns its own stage of the funnel. Nobody owns the gap between two stages unless someone is assigned to it. That gap is where leads sit unworked, closed deals reach onboarding without context, and renewals surprise the account manager.&lt;/p&gt;

&lt;p&gt;Routing rules, response-time targets, and the fields that carry context from one team to the next belong to RevOps. So does measuring them. A handoff nobody measures slows down quietly.&lt;/p&gt;

&lt;h2&gt;
  
  
  Own the system design, not every request
&lt;/h2&gt;

&lt;p&gt;The CRM’s structure needs one owner: objects, stages, required fields, validation rules, and who can change them. Routine admin work can be shared. Design decisions can’t, because a field added for one team changes the reports of every other team.&lt;/p&gt;

&lt;p&gt;Report requests are different. A team that builds every one-off pull never gets to the analysis that changes a decision. Build a small set of dashboards people trust, make self-serve the default, and spend the time saved on questions nobody else can answer.&lt;/p&gt;

&lt;h2&gt;
  
  
  Hand off the decisions
&lt;/h2&gt;

&lt;p&gt;Quotas, territories, comp plans, and pricing belong to the leaders accountable for the results, usually with finance in the room. RevOps should build the models behind them: capacity, coverage, funnel math, and the trade-offs between options.&lt;/p&gt;

&lt;p&gt;When RevOps makes the call itself, it inherits every argument about it, and its analysis stops looking neutral. Commissions work the same way. RevOps can supply the attainment data, but the payout and the controls around it belong with finance.&lt;/p&gt;

&lt;h2&gt;
  
  
  Keep the logic, share the plumbing
&lt;/h2&gt;

&lt;p&gt;Where a data engineering team exists, ingestion, the warehouse, and job scheduling belong there. RevOps keeps the business logic: how a booking is defined in SQL, how accounts map across systems, and what counts as a match in a reconciliation. In a smaller company RevOps often runs both, which works as long as the logic is written down well enough to move later.&lt;/p&gt;

&lt;p&gt;A simple test helps with each new request: would it break if every team ran its own version? If yes, it’s RevOps work. If it has a natural home somewhere else, hand it over cleanly, with the data and the documentation, and send the next request to its new owner.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/what-revenue-operations-should-own/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. More from Malek Damiri on revenue operations at &lt;a href="https://malekdamiri.com/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sales</category>
      <category>management</category>
      <category>productivity</category>
      <category>career</category>
    </item>
    <item>
      <title>Find the funnel constraint before you automate</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Thu, 22 Oct 2026 12:51:56 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/find-the-funnel-constraint-before-you-automate-1e41</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/find-the-funnel-constraint-before-you-automate-1e41</guid>
      <description>&lt;p&gt;When a team is behind its number, the first instinct is to do more of everything: more outreach, more leads, more tooling. Before any of that, I like to ask a plainer question. What would it take to hit the number, step by step?&lt;/p&gt;

&lt;h2&gt;
  
  
  Work backwards
&lt;/h2&gt;

&lt;p&gt;Start with the target and divide. Deals needed, at your real win rate, means this many qualified opportunities. At your real conversion, that means this many first meetings, which means this many conversations started. Use measured rates, not hoped-for ones. The arithmetic takes ten minutes and it’s usually sobering.&lt;/p&gt;

&lt;h2&gt;
  
  
  Then look for the ceiling
&lt;/h2&gt;

&lt;p&gt;Every funnel has a step that can’t go faster no matter how much you pour in above it. It might be the size of the market you can credibly reach. It might be how many deals a rep can carry, or how many new customers onboarding can process in a month. That step sets your output. Everything upstream of it is inventory.&lt;/p&gt;

&lt;p&gt;This is where automation goes wrong. Automating the top of the funnel feels productive because the counts go up immediately. If the constraint sits downstream, you’ve built a faster way to create a backlog.&lt;/p&gt;

&lt;h2&gt;
  
  
  Rank fixes by payoff
&lt;/h2&gt;

&lt;p&gt;Once the constraint is named, the list of projects sorts itself. The cheap fixes that widen the narrow step come first. Volume at the top comes last, and only with a way to switch it off if quality drops.&lt;/p&gt;

&lt;p&gt;It’s also worth separating what sales can influence from what it can’t. Some lines in a plan are met by self-serve demand, some by partners, and some by the sales team. Only the last one is a sales problem. Treating all three as one target is how a team ends up working hard on the wrong line.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/find-the-constraint-before-you-automate/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. More from Malek Damiri on revenue operations at &lt;a href="https://malekdamiri.com/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>productivity</category>
      <category>sales</category>
      <category>automation</category>
      <category>career</category>
    </item>
    <item>
      <title>Your average customer doesn't exist</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Tue, 20 Oct 2026 12:51:56 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/your-average-customer-doesnt-exist-4iho</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/your-average-customer-doesnt-exist-4iho</guid>
      <description>&lt;p&gt;Most revenue plans contain a line like this: new accounts times average revenue per account. It looks harmless. It’s usually the weakest number in the model.&lt;/p&gt;

&lt;p&gt;Revenue per customer is almost never bell-shaped. A handful of accounts produce most of it and a long tail produces very little. In a distribution like that, the average describes nobody. It sits far above the typical account and far below the big ones.&lt;/p&gt;

&lt;h2&gt;
  
  
  Plan on the median and size the tail separately
&lt;/h2&gt;

&lt;p&gt;If you want to know what the next ordinary customer is worth, look at the median. In skewed data it can be a small fraction of the mean. A plan that assumes the mean is quietly assuming you’ll land more very large accounts, without saying so.&lt;/p&gt;

&lt;p&gt;So split it. Model the typical account on the median, and treat large accounts as their own line, with their own count, their own sales cycle, and their own odds.&lt;/p&gt;

&lt;h2&gt;
  
  
  Look at concentration before anything else
&lt;/h2&gt;

&lt;p&gt;Before trusting any total, sort the accounts and ask what share comes from the top ten. If it’s most of it, your “trend” is really a few customers’ behavior, and a single departure can pass for a market shift.&lt;/p&gt;

&lt;h2&gt;
  
  
  Report the spread
&lt;/h2&gt;

&lt;p&gt;One number invites one conclusion. I’d rather show the median, the 90th percentile, and the count behind them. It takes one more line on a slide, and it prevents the worst meetings, the ones where everyone argues about an average none of them could find in the data.&lt;/p&gt;

&lt;p&gt;The habit is cheap. Every time you’re handed an average, ask to see the distribution it came from. If nobody has looked, you’ve found the first thing to do.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/your-average-customer-does-not-exist/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. I'm Malek Damiri, Head of Revenue Operations in Atlanta; more of my writing is at &lt;a href="https://malekdamiri.com" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>analytics</category>
      <category>datascience</category>
      <category>statistics</category>
      <category>sales</category>
    </item>
    <item>
      <title>How to audit a revenue dashboard before executives use it</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Thu, 15 Oct 2026 12:50:10 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/how-to-audit-a-revenue-dashboard-before-executives-use-it-2kl7</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/how-to-audit-a-revenue-dashboard-before-executives-use-it-2kl7</guid>
      <description>&lt;p&gt;A revenue dashboard gets one first impression. If an executive finds a number in the first week that doesn’t match the board deck, every number after that is suspect, and the trust takes months to earn back. So before anything goes in front of leadership, I run the same audit. Here is the checklist, with the reasoning behind each item, because the reasoning is what tells you how hard to look.&lt;/p&gt;

&lt;h2&gt;
  
  
  Tie it out to the ledger
&lt;/h2&gt;

&lt;p&gt;Start with the total. Whatever the dashboard calls revenue should tie to what finance recognized for the same period, or you should be able to explain every dollar of the gap. Pull the period total from the dashboard’s own query, pull the same period from the ledger, and reconcile them line by line. Timing differences, refunds, and credits are acceptable reasons to differ. “We’re not sure” is not.&lt;/p&gt;

&lt;p&gt;Do this for more than one period. A single month can tie by accident.&lt;/p&gt;

&lt;h2&gt;
  
  
  Check the date boundaries and time zones
&lt;/h2&gt;

&lt;p&gt;A large share of revenue disputes come down to what day something happened. The source system stamps events in UTC, the warehouse truncates to a date, the BI tool applies the viewer’s local time zone, and the month closes at a different moment in each layer. Pick one reporting time zone, write it down, and make sure every layer agrees. Then test the edges: the last hour of the month, the first hour of the next, and the weekend daylight saving changes.&lt;/p&gt;

&lt;p&gt;While you’re there, confirm which date the dashboard keys on. Order date, invoice date, payment date, and recognition date are all defensible. Mixing them on one page is not.&lt;/p&gt;

&lt;h2&gt;
  
  
  Follow the currency and the fees
&lt;/h2&gt;

&lt;p&gt;If revenue arrives in more than one currency, find out where the conversion happens, which rate it uses, and whether that rate matches what finance uses to close. Then find out whether the dashboard shows gross or net. Processor fees, discounts, taxes, and refunds each move the number, and each has a camp that considers its version the real one. The dashboard has to pick one, label it, and use it everywhere. A tile that says “Revenue” with no qualifier is a question waiting to be asked in a meeting.&lt;/p&gt;

&lt;h2&gt;
  
  
  Look for duplicates and late arrivals
&lt;/h2&gt;

&lt;p&gt;Count distinct identifiers against raw row counts at every step of the pipeline. Joins duplicate rows quietly, and a retry in the source system can land the same record twice. Then look in the other direction: records that arrive after the period they belong to. A dashboard that shows last month as final on the first and then revises it for a week teaches readers to wait before believing anything. Decide how long a period stays open, show that status on the tile, and build the dedupe into the query instead of trusting the source to be clean.&lt;/p&gt;

&lt;h2&gt;
  
  
  Find the filters that silently exclude
&lt;/h2&gt;

&lt;p&gt;Every dashboard carries filters that someone added for a good reason and then forgot. A status filter that drops a new order type. A date filter hard-coded to a fiscal year that has since ended. A segment filter meant for one tab that leaked into the shared dataset. Read every &lt;code&gt;WHERE&lt;/code&gt; clause and every filter the BI tool applies, and for each one ask what it excludes and whether the readers know. The dangerous ones make the number smaller in a way that still looks plausible.&lt;/p&gt;

&lt;h2&gt;
  
  
  Freshness, and a name on the sign-off
&lt;/h2&gt;

&lt;p&gt;Put a “data as of” timestamp on the page, computed from the data itself rather than from when the page loaded. Decide how stale is too stale, and make the dashboard say so when it crosses that line.&lt;/p&gt;

&lt;p&gt;Finally, someone has to sign off, and it shouldn’t be the person who built it. The owner of the number, usually in finance or the business, reviews the reconciliation, the definitions, and the list of known gaps, and agrees in writing that this is the version executives see. Signing off doesn’t mean the dashboard is perfect. It means someone besides the builder has looked and is willing to defend it.&lt;/p&gt;

&lt;p&gt;An audit like this always takes longer than anyone wants. It is still cheaper than relaunching a dashboard that was wrong on day one.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/how-to-audit-a-revenue-dashboard/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. I'm Malek Damiri, Head of Revenue Operations. Drafted with AI assistance. More of my writing is at &lt;a href="https://malekdamiri.com" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>analytics</category>
      <category>management</category>
      <category>saas</category>
    </item>
    <item>
      <title>Snowflake SQL patterns for revenue reporting</title>
      <dc:creator>Malek Damiri</dc:creator>
      <pubDate>Tue, 13 Oct 2026 17:48:44 +0000</pubDate>
      <link>https://dev.to/malek_damiri_68c6fecee5b7/snowflake-sql-patterns-for-revenue-reporting-573l</link>
      <guid>https://dev.to/malek_damiri_68c6fecee5b7/snowflake-sql-patterns-for-revenue-reporting-573l</guid>
      <description>&lt;p&gt;Most revenue reporting problems are not hard SQL. They’re the same handful of problems showing up in different tables: gaps in the calendar, days cut at the wrong midnight, duplicate records, totals that need a comparison, data that quietly stopped arriving, and two systems that disagree about the same month. I’ve settled on a small set of Snowflake patterns for them. Here they are, with generic table names. Swap in your own.&lt;/p&gt;

&lt;h2&gt;
  
  
  Start from a date spine
&lt;/h2&gt;

&lt;p&gt;If a day had no orders, a plain &lt;code&gt;GROUP BY&lt;/code&gt; leaves it out and the chart skips the day as if it never happened. Generate the calendar first and left join the facts onto it, so a quiet day shows up as zero instead of vanishing. If you already have a &lt;code&gt;dim_date&lt;/code&gt; table, join to that instead of the generator. &lt;code&gt;SEQ4&lt;/code&gt; can have gaps, so use &lt;code&gt;ROW_NUMBER&lt;/code&gt; for the offset.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;spine&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;dateadd&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;day&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;row_number&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;seq4&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'2025-01-01'&lt;/span&gt;&lt;span class="p"&gt;::&lt;/span&gt;&lt;span class="nb"&gt;date&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;report_date&lt;/span&gt;
  &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="k"&gt;table&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;generator&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;rowcount&lt;/span&gt; &lt;span class="o"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;1000&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;report_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;amount_usd&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;revenue_usd&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;spine&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;
&lt;span class="k"&gt;left&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;
  &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;order_date&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;report_date&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;report_date&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="k"&gt;current_date&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Cut days in the reporting time zone
&lt;/h2&gt;

&lt;p&gt;Timestamps usually arrive in UTC, and a month that closes at midnight UTC is a different month from one that closes at midnight where the business operates. Pick one reporting time zone, convert before you truncate, and do it in one shared view rather than in every dashboard. If &lt;code&gt;created_at&lt;/code&gt; is a &lt;code&gt;TIMESTAMP_NTZ&lt;/code&gt; holding UTC, the three-argument form of &lt;code&gt;CONVERT_TIMEZONE&lt;/code&gt; does the conversion.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;convert_timezone&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'UTC'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'America/New_York'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;created_at&lt;/span&gt;&lt;span class="p"&gt;)::&lt;/span&gt;&lt;span class="nb"&gt;date&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;order_day&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;count&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;amount_usd&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;revenue_usd&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Dedupe to the latest record with QUALIFY
&lt;/h2&gt;

&lt;p&gt;Systems that sync by upsert or replay often land several versions of the same invoice, and a &lt;code&gt;SUM&lt;/code&gt; over all of them overstates revenue by exactly the amount nobody can explain. Keep the latest version per key and drop the rest. &lt;code&gt;QUALIFY&lt;/code&gt; filters on the window function directly, with no wrapping subquery, which keeps the intent visible to the next reader.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;invoices&lt;/span&gt;
&lt;span class="n"&gt;qualify&lt;/span&gt; &lt;span class="n"&gt;row_number&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;invoice_id&lt;/span&gt;
  &lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;updated_at&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;loaded_at&lt;/span&gt; &lt;span class="k"&gt;desc&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Running totals and period-over-period
&lt;/h2&gt;

&lt;p&gt;Most executive questions are “compared to what?” This month against last month. This year to date against the same point last year. Aggregate to the grain you report at first, then let window functions do the comparing. One caveat: &lt;code&gt;LAG&lt;/code&gt; counts rows, not months. If a month can be missing, run this on top of the spine above or the comparison will shift by a row.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;monthly&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;date_trunc&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'month'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;trade_date&lt;/span&gt;&lt;span class="p"&gt;)::&lt;/span&gt;&lt;span class="nb"&gt;date&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
         &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;fee_usd&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;fees_usd&lt;/span&gt;
  &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;trades&lt;/span&gt;
  &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;fees_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;fees_usd&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
         &lt;span class="k"&gt;partition&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;date_trunc&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'year'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
         &lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;
       &lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;fees_ytd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;lag&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;fees_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;prior_month_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;lag&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;fees_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;12&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;over&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;prior_year_usd&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;monthly&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  A freshness check the dashboard can show
&lt;/h2&gt;

&lt;p&gt;A dashboard that shows yesterday’s numbers with full confidence is worse than one that admits it’s stale. Compute freshness from the data itself, not from when the page loaded, and put the result where readers see it. Watch the time zones here too. &lt;code&gt;CURRENT_TIMESTAMP&lt;/code&gt; follows the session time zone, so if &lt;code&gt;loaded_at&lt;/code&gt; is stored as UTC, compare against &lt;code&gt;SYSDATE&lt;/code&gt; instead, or convert one side before the subtraction.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;latest&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="k"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;loaded_at&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;last_loaded_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
         &lt;span class="n"&gt;datediff&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'hour'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;max&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;loaded_at&lt;/span&gt;&lt;span class="p"&gt;),&lt;/span&gt; &lt;span class="k"&gt;current_timestamp&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;hours_since_load&lt;/span&gt;
  &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;last_loaded_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;hours_since_load&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;iff&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;hours_since_load&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;24&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'stale'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'ok'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;latest&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Reconcile two systems with a FULL OUTER JOIN
&lt;/h2&gt;

&lt;p&gt;When the warehouse and the ledger disagree, you want every period from both sides, including the months that exist in only one of them. An inner join hides exactly the rows you’re looking for. Use a &lt;code&gt;FULL OUTER JOIN&lt;/code&gt;, &lt;code&gt;COALESCE&lt;/code&gt; the keys and the amounts, and add a tolerance so you’re not chasing rounding. Anything outside the tolerance gets a written explanation.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;warehouse&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;date_trunc&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'month'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;invoice_date&lt;/span&gt;&lt;span class="p"&gt;)::&lt;/span&gt;&lt;span class="nb"&gt;date&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
         &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;amount_usd&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;warehouse_usd&lt;/span&gt;
  &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;invoices&lt;/span&gt;
  &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="p"&gt;),&lt;/span&gt;
&lt;span class="n"&gt;ledger&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;date_trunc&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'month'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;posting_date&lt;/span&gt;&lt;span class="p"&gt;)::&lt;/span&gt;&lt;span class="nb"&gt;date&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
         &lt;span class="k"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;amount_usd&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;ledger_usd&lt;/span&gt;
  &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;ledger_entries&lt;/span&gt;
  &lt;span class="k"&gt;group&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;select&lt;/span&gt; &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;l&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;report_month&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;warehouse_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;warehouse_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;l&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;ledger_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;ledger_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;warehouse_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;l&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;ledger_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;variance_usd&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;warehouse&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;
&lt;span class="k"&gt;full&lt;/span&gt; &lt;span class="k"&gt;outer&lt;/span&gt; &lt;span class="k"&gt;join&lt;/span&gt; &lt;span class="n"&gt;ledger&lt;/span&gt; &lt;span class="n"&gt;l&lt;/span&gt;
  &lt;span class="k"&gt;on&lt;/span&gt; &lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;report_month&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;l&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;report_month&lt;/span&gt;
&lt;span class="k"&gt;where&lt;/span&gt; &lt;span class="k"&gt;abs&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;w&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;warehouse_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;l&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;ledger_usd&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="c1"&gt;-- tolerance in dollars&lt;/span&gt;
&lt;span class="k"&gt;order&lt;/span&gt; &lt;span class="k"&gt;by&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;None of these are clever, and that’s the point. The queries behind an executive dashboard should be boring enough that the next person can read them in one sitting. Put them in version control, name the CTEs plainly, and reuse the same few shapes until people stop asking how a number was built.&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://malekdamiri.com/writing/snowflake-sql-patterns-for-revenue-reporting/" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;. I'm Malek Damiri, Head of Revenue Operations. More of my writing is at &lt;a href="https://malekdamiri.com" rel="noopener noreferrer"&gt;malekdamiri.com&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>analytics</category>
      <category>data</category>
      <category>database</category>
      <category>sql</category>
    </item>
  </channel>
</rss>
