MRR

gistfile1.txt

                    for (var year = 2012; year <= EndingYear; year++)
                    {
                        for (var month = 1; month <= 12; month++)
                        {
                            if (year == EndingYear && month > EndingMonth)
                            {
                                continue;
                            }

                            var totalMrr = 0M;

                            var mrrFromRenewingCustomers = 0M;
                            var mrrUpsell = 0M;
                            var mrrDownSell = 0M;

                            var mrrLost = 0M;
                            var mrrNew = 0M;

                            var grossRetention = 0M;
                            var grossRetentionMinusDownsell = 0M;
                            var netRetention = 0M;

                            var logosBeginning = logosTotal;
                            var logosAdded = 0;
                            var logosLost = 0;


                            foreach (var account in accountList)
                            {
                                var mrrNow = statsByMonth.Where(c => c.AccountName == account && c.Year == year && c.Month == month).Sum(c => c.Revenue);
                                var mrrYearAgo = statsByMonth.Where(c => c.AccountName == account && c.Year == (year - 1) && c.Month == month).Sum(c => c.Revenue);

                                var oneMonthAgoMonth = new DateTime(year, month, 1).AddMonths(-1).Month;
                                var oneMonthAgoYear = new DateTime(year, month, 1).AddMonths(-1).Year;

                                var mrrOneMonthAgo = statsByMonth.Where(c => c.AccountName == account && c.Year == oneMonthAgoYear && c.Month == oneMonthAgoMonth).Sum(c => c.Revenue);



                                if (mrrYearAgo > 0 && mrrNow > 0)
                                {
                                    var mrrDifference = mrrNow - mrrYearAgo;

                                    if (mrrDifference > 0)
                                        mrrUpsell += mrrDifference;
                                    else if (mrrDifference < 0)
                                        mrrDownSell += mrrDifference;

                                    mrrFromRenewingCustomers += mrrYearAgo;
                                }
                                else if (mrrYearAgo == 0 && mrrNow > 0)
                                {
                                    mrrNew += mrrNow;
                                }
                                else if (mrrYearAgo > 0 && mrrNow == 0)
                                {
                                    mrrLost -= mrrYearAgo;
                                }

                                if (mrrOneMonthAgo > 0 && mrrNow == 0)
                                {
                                    logosLost++;
                                    logosTotal--;
                                }
                                else if (mrrOneMonthAgo == 0 && mrrNow > 0)
                                {
                                    logosAdded++;
                                    logosTotal++;
                                }

                                totalMrr += mrrNow;
                            }

                            grossRetention = mrrFromRenewingCustomers == 0 ? 0 : (mrrFromRenewingCustomers + mrrDownSell + mrrLost) / mrrFromRenewingCustomers;
                            grossRetentionMinusDownsell = mrrFromRenewingCustomers == 0 ? 0 : (mrrFromRenewingCustomers + mrrLost) / mrrFromRenewingCustomers;
                            netRetention = mrrFromRenewingCustomers == 0 ? 0 : (mrrFromRenewingCustomers + mrrUpsell + mrrDownSell + mrrLost) / mrrFromRenewingCustomers;

                            mrrFromRenewingCustomersRow.Add(mrrFromRenewingCustomers.ToString("0.00"));
                            mrrFromUpsellsRow.Add(mrrUpsell.ToString("0.00"));
                            mrrFromDownChurnRow.Add(mrrDownSell.ToString("0.00"));
                            mrrLostRow.Add(mrrLost.ToString("0.00"));
                            mrrFromNewCustomersRow.Add(mrrNew.ToString("0.00"));

                            grossRetentionRow.Add(grossRetention.ToString("0.00"));
                            grossRetentionMinusDownsellRow.Add(grossRetentionMinusDownsell.ToString("0.00"));
                            netRetentionRow.Add(netRetention.ToString("0.00"));

                            logosBeginningRow.Add(logosBeginning.ToString());
                            logosAddedRow.Add(logosAdded.ToString());
                            logosLostRow.Add(logosLost.ToString());
                            logosTotalRow.Add(logosTotal.ToString());
                        }
                    }