Averaging multiple Percentile rankings

Options
VisheshB
VisheshB Member
in Magic ETL

Hi Everybody.

I am stuck with a problem. First of all, thanks to Jae Wilson for the percentile rank video. It help me solve one part of the problem. My data looks lik attached

Domo (2).PNG

The data is pulled from Google Analytics and modified using Magic ETL. It contains page title, Month, and other metrics. The task on hand is to rank Page views, Organic search and new user rate for every content published in last six months. the ranks need to work for all content grouped by page title and also page titles + month/ so if somebody choose to see a particular month content rank they can see it. Now this is resolved, at least I think it is. the current output looks like this.

Domo (3).PNG

I have used following formula to calculate rank through beast mode

(sum(MIN(1)) over (partition by `Month_ text` order by (sum(`New Users`) / SUM(`Users`)) asc)

/

sum(MIN(1)) over (partition by `Month_ text`)) * 100

Now, another task is to find an average of all three of these segmented by month. So when filtered by month they can see the score of that filtered period. something like:

( avg ( new user rate percentile rank) + avg ( organic search percentile rank) + avg ( pageview percentile rank) ) / 3.

Any suggestions are welcom. Much appreciate your help with this.

Kind regards,

Vishesh

Answers

  • user027926
    user027926 Member
    Options

    If I'm understanding your issue correctly, the issue is that you want to compute a beast mode of a beast mode. My recommendation is to use Magic ETL for (at least) your first computation and then you can compute the second formula either in another Magic ETL tile or in a Beast Mode. If you use the formula tile in Magic ETL beta, this should be pretty straightforward. Hope this helps.

Categories