Optimizing advertising spend with the Blended Same-Month Return metric

Published on May 6, 2019 by Eric Benjamin Seufert

Systematic growth through programmatic mobile marketing analysis.

One of the difficult aspects of managing large marketing budgets is reconciling the urge to measure everything on the basis of a cohort’s lifetime with the very practical need of the business to manage cash flow and plan around monthly expenses. Companies can go bankrupt buying advertising on an LTV-profitable basis; it’s important for advertising managers to be able to predict how much money any given cohort will recoup against a timeline of regular, predictable monthly expenses like payroll, rent, and even advertising media itself.

One interesting way to think about this is: if I start buying traffic on the first of the month and run that campaign for some pre-determined timeline, when will that spend be recovered (recouped)? The shorter that number is, all other things held equal, the higher the “velocity” of revenue for the product is and the faster profit accumulates into growth. If media spend could be recouped same-month — that is, I spend $100 in May and generate $100+ in revenue on that spend in May — then it’s likely that expenses could be managed in such a way that advertising spend never reduces the company’s cash balance.

How would one go about measuring that same-month return? We can start with a theoretical, linear cumulative ARPU curve:

This curve shows that any given cohort produces $0.05 in revenue each day, and by the end of Day 7, $0.35 has been generated.

If we were to start buying traffic against this curve, and buy it for seven days, then we’d theoretically see an ARPDAU schedule that looks like this:

The Timeline x Cohort matrix here just shows that we have seven cohorts that passed through the Day 1 value of the cumulative ARPU curve above and produced $0.05 for a total of $0.35 (7x$0.05 = $.35), we have six cohorts that passed through the Day 2 value and produced $0.05 for a total of $0.30, we have five cohorts that passed through the Day 3 value and produced $0.05 for a total of $0.25, etc. If we sum those Day X cumulative ARPU totals up, we get $1.40, which means that $1.40 is the expected aggregate revenue generated from seven of these hypothetical users introduced to the product over seven days (ie. if we acquired one user per day for seven days and they all exhibited this cumulative ARPU curve).

If we don’t want to use a spreadsheet to calculate this, we can do it two other ways. The first is to use linear algebra to calculate the value of this ARPU matrix with a dot product, where the number of cohorts that have reached each LTV day is multiplied by the ARPU value at that day. Programmatically, that looks like this:

#assumptions
timeline = 7
CPI = 3
DNU = 100

#linear cumulative ARPU equation: y = .05x

ARPDAU = [ ( .05 * x ) - ( .05 * ( x - 1 ) ) if x > 1 
    else ( .05 * ( x ) ) for x in np.arange( 1, ( timeline + 1 ) ) ]
blended_ARPU = np.dot( list( range( timeline, 0, -1 ) ), ARPDAU )
print( blended_ARPU )
#this is the revenue generated by all cohorts over the timeline (=7 days in this example)

This produces the following output:

The other approach is to use a mathematical sum on the LTV equation over the timeline. We can do this with Wolfram Alpha to get the same result:

(If you are wondering why a sum is used here instead of an integral, this article provides a useful explanation.)

Once we have this per-user revenue number, we can calculate the total return from those cohorts within that timeline by multiplying the weighted average DNU over the period by the user-level revenue and dividing that number by the total cost (which is CPI times the timeline times weighted average DNU).

We can also do this programmatically:

total_recoup = ( blended_ARPU * DNU )
total_cost = CPI * DNU * timeline
total_recoup = total_recoup / total_cost
print( str( round( total_recoup * 100, 2 ) ) + '%' )

In both cases, we get 6.67% total blended return from these seven cohorts in the same seven days: we generated $7,000 in total revenue from the 35,000 acquired users, and we spent $105,000 on user acquisition.

We can now look at a more realistic example with a log-shaped LTV curve:

In this case, using the same DNU and CPI assumptions, we generated $4.40 in per-user revenue over those seven days, which produces 21% return over the period:

It’s easy to see why this analysis is inconvenient to manage in a spreadsheet: in order to calculate these values for the month, we’d need a 30×30 matrix of cells that wouldn’t fit in one screen. So we can implement this programmatically by changing the timeline:

timeline = 30

#logarithmic cumulative ARPU equation: y = 0.25*ln(x)+0.02
ARPDAU = [ ( .5 * np.log( x ) + .02) - ( .5 * np.log( x - 1 ) + .02 ) if x > 1 
    else ( .5 * np.log( x ) + .02 ) for x in np.arange( 1, ( timeline + 1 ) ) ]
blended_ARPU = np.dot( list( range( timeline, 0, -1 ) ), ARPDAU )
print( blended_ARPU )
plt.plot( np.arange( 1, ( timeline + 1 ) ), [ ( .5 * np.log( x ) + .02) - ( .5 * np.log( x - 1 ) + .02 ) if x > 1 
    else ( .5 * np.log( x ) + .02 ) for x in np.arange( 1, ( timeline + 1 ) ) ] )
# ^ ARPDAU
plt.plot( np.arange( 1, ( timeline + 1 ) ), [ ( .5 * np.log( x ) + .02 ) for x in np.arange( 1, ( timeline + 1 ) ) ] )
# ^ Cumulative ARPDAU
plt.show()

#again, the revenue generated by all cohorts over the timeline

Here we have calculated a blended per-user revenue value of $38, which is a 42% same-month return:

If we want to know how many days it will take to recoup the money spent on those 30 cohorts, we can write a simple function that just iterates through the LTV curves of those cohorts and calculates how much cumulative revenue has been accrued from them and stops when the total cost is equal to the total revenue:

def get_total_return_date( timeline, CPI, DNU, equation ):
    #equation contains a and c for the formula y = a*ln(x)+c
    
    ARPDAU = [ equation[ 'a' ] * np.log( x ) + equation[ 'c' ] for x in np.arange( 1, ( timeline + 1 ) ) ]
    blended_ARPU = sum( ARPDAU )

    total_recoup = ( blended_ARPU * DNU )
    total_cost = CPI * DNU * timeline

    total_recoup = total_recoup / total_cost

    i = 1
    while total_recoup < 1:
        ARPDAU = [ equation[ 'a' ] * np.log( x ) + equation[ 'c' ] for x in np.arange( 1, ( timeline + 1 + i ) ) ]
        blended_ARPU = sum( ARPDAU )

        total_recoup = ( blended_ARPU * DNU )
        total_cost = CPI * DNU * timeline

        total_recoup = total_recoup / total_cost
        i += 1
        if i > 100:
            return False
    return i + timeline

In this case, our 30 cohorts recoup fully by day 59, which means that we’ll get paid in month three for the money we spent in month one.

The more interesting question to answer is: how can I ensure that my Blended Same-Month Return is 1? That is, how can I manage my spend in such a way that I’m only spending in-month what I’m also recouping in-month? If that can be accomplished, then payment terms and timelines can roughly be adjusted to ensure that I’m carefully managing my cash balance.

In order to do this, we can alter the very simple function above to start at 30 (or whatever the number of days in the current month is) and work backwards until some number of cohorts fully recoups within that same month. The new function is:

def get_cohort_return_timeline( CPI, DNU, equation ):
    #equation contains a and c for the formula y = a*ln(x)+c
    
    #month length is the timeline that we calculate blended return across
    month_length = 30
    timeline = month_length
    
    ARPDAU = [ equation[ 'a' ] * np.log( x ) + equation[ 'c' ] for x in np.arange( 1, ( month_length + 1 ) ) ]
    blended_ARPU = sum( ARPDAU )

    total_recoup = ( blended_ARPU * DNU )
    total_cost = CPI * DNU * timeline

    total_recoup = total_recoup / total_cost
    
    if total_recoup >= 1:
        return True

    i = 1
    while total_recoup < 1:
        ARPDAU = [ equation[ 'a' ] * np.log( x ) + equation[ 'c' ] for x in np.arange( 1, ( month_length + 1 ) ) ]
        blended_ARPU = sum( ARPDAU )

        total_recoup = ( blended_ARPU * DNU )
        total_cost = CPI * DNU * ( timeline - i )

        total_recoup = total_recoup / total_cost
        i += 1
        if i > month_length:
            return False
    return ( timeline - i ), total_recoup

What this function does is start by calculating the Blended Same-Month Return (BSMR) for a full set of 30 cohorts in the month. If that BSMR metric is >= 1, it returns True, meaning that the cohorts fully recoup within the month; if the BSMR is less than 1, it iterates backwards, trying 29 cohorts, 28 cohorts, 27 cohorts etc. until it finds some number of cohorts that can be purchased in the month that will fully recoup in that same month.

For the values in the example above, the number of cohorts that can be acquired and fully recoup same-month is 11:

Thinking about acquisition costs and recoup from the perspective of calendar months (or quarters, or years, etc.) as opposed to individual cohort timelines (eg. the cohort we acquired yesterday will pay for itself in 120 days) is helpful in planning and accounting for media spend. The code above is crude, but a more robust approach can be integrated into an advertising workflow to assist the marketing team and the broader organization in making better decisions around media buying.

The code used in this article can be found on GitHub. The Google Doc used in the article can be found here.

Photo by Scott Blake on Unsplash

Modern Mobile Marketing at Scale is a four-hour online course that provides a comprehensive overview of the modern mobile marketing landscape, from creative production to analytics & reporting to Facebook and Google UAC media buying tactics.