Monthly Total Running Average Rates

Simon

Hi,

I have a sum function within a beast mode, that calculates a simple rate and I am trying to create a total running average rate, but up until now, I am only succeeding in getting the right values for each month. When I use the running total line chart, the percentage gets bigger than 100 %.

 

However, I want to sum up the values from each previous month and divide them accordingly, so that I can get the average rate from the beginning of the particular activity up until now. Example (assuming equal numbers in January and February):

Data now: 
January: 100% conversion
February: 50% conversion

Desirable result: (Monthly Total Running Average):
January: 100% conversion
February: 75%

Is this possible within the Beast mode or could you provide us with an example of a SQL code, that could bring us the desirable results? 

zcameron

In that case, you could modify the second transform to look like this:

 

SELECT
    `MONTH`
    , (SELECT

            SUM(yt.`conversions`) / SUM(yt.`opportunities`)

       FROM 

           your_table yt

       WHERE

           LAST_DAY(yt.`start`) <= m.`Month`

           AND LAST_DAY(yt.`start`) >= DATE_SUB(m.`Month`, INTERVAL 6 MONTH)

) AS `Running Average

FROM
months m

 

 

This will include the records that are within 6 months of the date in question.

 

I hope that helps!

[Deleted User]

We are looking into this and will get back to you shortly.

Regards,

 

zcameron

Unfortunately, the desired result isn't possible in beast mode because each record will fall into exactly one category, which in this case appears to be the month. If a record is used in the January line point, it isn't also part of the group of records used to calculate the February line point. 

 

You could create a dataset that behaves how you'd like using a dataflow, however. You might try something like this:

 

Transform 1: months

/* Selects distinct month values from the data for use in the next transform */

 

SELECT

    DISTINCT LAST_DAY(`date`) AS `Month`

FROM

    your_table

ORDER BY

    `Month`

 

 

Transform 2: running_averages

/* Uses a sub query to calculate averages from the beginning of the year through the month of each record in the months table */

 

SELECT
    `MONTH`
    , (SELECT

            SUM(yt.`conversions`) / SUM(yt.`opportunities`)

       FROM 

           your_table yt

       WHERE

           LAST_DAY(yt.`start`) <= m.`Month`

           AND LAST_DAY(yt.`start`) >= STR_TO_DATE(CONCAT(YEAR(m.`Month`),'-01-01'),'%Y-%m-%d')) AS `Running Average

FROM
months m

 

 

You'll need to swap out your own column and table names, of course, but hopefully this helps you get started.

 

Simon

@zcameron, thank you for the solution.

I implemented it and it works fine, but when a new year begins, the rolling average starts from January and is not cumulated with December from the last year.

What could be the issue? 

zcameron

This code is set up to give you Jan through Dec metrics. Can you clarify what your criteria are for what period should be included in the rolling average, e.g. instead of for the year, do you want to see the last 6 months?

Simon

Last 6 months would be great. 

zcameron

In that case, you could modify the second transform to look like this:

 

SELECT
    `MONTH`
    , (SELECT

            SUM(yt.`conversions`) / SUM(yt.`opportunities`)

       FROM 

           your_table yt

       WHERE

           LAST_DAY(yt.`start`) <= m.`Month`

           AND LAST_DAY(yt.`start`) >= DATE_SUB(m.`Month`, INTERVAL 6 MONTH)

) AS `Running Average

FROM
months m

 

 

This will include the records that are within 6 months of the date in question.

 

I hope that helps!

kshah008

@Simon, tagging you to check out zcameron's latest reply.