Weighted Average Visit Number

[czmudzin]

Has anyone created a calculated metric to get the weighted average of visit number? It's something I use often when examining customer journey, and I often extract the data to Excel. I'm wondering if it can be done in Workspace to save time.

[Jennifer_Dungan]

I don't use metrics like that often, but I believe that some are possible to make... if you share how you calculate your weighted average then maybe we can figure out how to do that in Workspace.

[czmudzin]

Here is my issue. I need to multiply the '1 pct of total' metric by the visit number. But, the visit number is a string. Is there a way around that?

[MandyGeorge]

Unfortunately, this makes it quite difficult. Since your visit number is a dimension/string value, you can't use it as a number in a metric. Are you still using Workspace or are you on CJA? I know in CJA there are options to create new fields based on current dimensions/metrics. I believe there is a way to turn a dimension like that into a metric in CJA, but there's nothing like that in workspace. 

Another possible solution would be if you use report builder. You could replicate that table in report builder, and then you would have the option to edit the data in excel and do the multiplication there. I would suggest just doing an export to excel as another option, but if this is a recurring need and not a one off, then you would have to continuously export the data, so less useful. 

I can't currently think of any other options for making this work.

[czmudzin]

Unfortunately it's not in CJA, and the Report Bulder/Excel option is what I have been using. 

Is there a way to do the calculation somewhere in data collection before it's available in Workspace?

[Jennifer_Dungan]

Well if you were dealing with only a limited number of visit numbers, you could create a calculated metric....

IF [

    Logical_Test [

        Visit Number = 1

            Occurrences

    ]

    Value_If_True [

        Static Number = 1

    ]

    Value_If_False [

        Logical_Test [

            Visit Number = 2

                Occurrences

        ]

        Value_If_True [

            Static Number = 2

        ]

        Value_If_False [

            // more nested ifs....

        ]

    ]

]

But Visit Number is an all time metric, there is no way you could sustainably create a calculated metric to achieve this... unless you do groupings? Like between 1 and 10, between 10 and 20, between 20 and 50, between 50 and 100, and greater than 100 (or something like that) then you could limit the buckets and create a numeric value based on that?

[MandyGeorge]

I've actually been working on making some weighted metrics in workspace recently, it should be possible with the use of the advanced functions. 

There is a function for "column sum" - that will likely be very useful for your weighted metric calculation. For example, a very basic formula, to see how individual items contribute to an entire metric, you can do something like this.

The column sum is great because it adds up all of the rows, and then you can look at an individual rows contribution to the entire sum. The only issue with this is that it doesn't deduplicate data. For example, if I were to use visits instead of page views, it would could all the visits for all the pages in the table, massively inflating the visit count. There might be a way around this by using multiple other functions/calculations, but I haven't solved for it yet. 

Like Jen mentioned, if you share the formula you use in excel for your weighted calculations, we can help replicate it in the metric builder. 

[czmudzin]

This was definitely helpful solving part of of the issue. Thanks for sharing!