Monthly Total Running Average Rates

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? 

Best Answer

  • zcameron
    zcameron Admin
    Answer ✓

    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!

Answers

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

    Regards,

     

  • 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.

     

  • @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? 

  • 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?

  • Last 6 months would be great. 

  • zcameron
    zcameron Admin
    Answer ✓

    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
    kshah008 Contributor

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

This discussion has been closed.