12 Replies Latest reply on Feb 13, 2018 3:01 PM by Gigazelle

    Distinct Dimension count with metric => x

    mubarakd57262327 Level 3

      I've noted the function 'Approximate Count Distinct' which could be used for a count of the distinct values for a dimension however I'd like to extend this further and only count the distinct count where the value has had => specified metric value.

       

      eg.

      Distinct Headline count that have had 10 or more Page Views.

       

      I'm able to achieve this if the table breakdown (rows) is headline but not without.  

        • 1. Re: Distinct Dimension count with metric => x
          PratheepArunRaj Level 4

          Dear Mubarak,

           

          Thanks for your idea to count the unique items of a variable using Calculated Metric. It was a new technique to me!

           

          For the above, adding Segment to the function will help to solve?

          What i have done is,

          Created a segment with Page Views >= 10 in a Visit and used that in Calculated Metric.

          First Segment Container and then Function for distinct count.

          Will this help?

           

          Thank You!

          Arun

          • 2. Re: Distinct Dimension count with metric => x
            mubarakd57262327 Level 3

            Arun,

             

            Thanks for the suggestion, let me clarify further..

            I'd like to report on articles that have been viewed more than x no. of times in a specified time frame and not by visitor/visit container.

            so I could then report on ..

            1. No of articles published (distinct count)

            2. No of articles published with 10 or more page views

            .. to then produce KPI % of articles published with more/less than 10 page views.

             

            as your segment is visit based it wouldn't do what's required.

            • 3. Re: Distinct Dimension count with metric => x
              Gigazelle Adobe Employee

              Viewed X number of times per individual, or total across your site?

              • 4. Re: Distinct Dimension count with metric => x
                mubarakd57262327 Level 3

                Across site/network

                 

                Take the following as an example ..

                We've deemed individual articles being viewed 100+ times as being a 'quality' content measurement, So the above highlights 76% of are articles met this requirement/KPI and generating the significant proportion of the overall page views.

                The above has been produced using row count and a calculated metric which is reliant on row count.

                The above is a workaround and does not do/allow ..

                 

                1. Have this trended (each month would require separate table)

                2. Have segment applied to 'headlines' dimension and it only show corresponding articles eg. articles that were viewed less than 100 times individually)

                 

                ps. this concept could apply to other market places too eg. retail show unpopular products that have been viewed/purchased less than x times

                • 5. Re: Distinct Dimension count with metric => x
                  Gigazelle Adobe Employee

                  I know that conditional (if/then) calculated metrics can accomplish this. Basically say that if a given line item has less than X, drop it to 0 so it's excluded from the report entirely. Then count the number of distinct line items and use that in your report.

                  • 6. Re: Distinct Dimension count with metric => x
                    mubarakd57262327 Level 3

                    That's what I've done to achieve dashboard above.. but this is limited and does not allow for what I mentioned in the previous post

                    • 7. Re: Distinct Dimension count with metric => x
                      Gigazelle Adobe Employee

                      Can you provide more info on why this data would not be able to be trended? I'm also unsure what you mean by headlines and it only show corresponding articles. If you could give more details on your observed limitations, we can explore further whether it is available in Workspace or not.

                      • 8. Re: Distinct Dimension count with metric => x
                        mubarakd57262327 Level 3

                        eg ...

                         

                        The highlighted metrics was produced relying on row count with calculated metric on a table for specified time frame. ie to produce this metric for different timeframes (for trended) would require separate tables ... and you can only trend data in a single table rather than across multiple tables.

                        So in order to produce a trended visualization  I need to be able to create the required metric which can work on date dimension in a single table

                        • 9. Re: Distinct Dimension count with metric => x
                          marion33678522 Level 2

                          You can still apply diferent time ranges on top of your metric if this one is on the rows. I usually do this and works pretty well. I have a time range for each month.

                           

                          So what I do is putting many times the same metric, and addind diferent time ranges on top. Then you can trend each ROW in the same plot.

                           

                          I'm interested on your calculated metric ! could you share ?!

                           

                          Kind regards,

                          • 10. Re: Distinct Dimension count with metric => x
                            mubarakd57262327 Level 3

                            Marion ..

                             

                            One of the calculated metrics below, this sums up the page views for articles that had 100+ PVs in the specified time frame.  There is a similar which does a distinct count of the rows too

                            With regards being able to trend I'm not sure I know what you mean.  As you'll note from the calculated metric it sums up the rows (or count of distinct rows in the other metric) .. therefore I have to have a separate table for each month/duration and thus can't trend.

                             

                            ps. I'm able to trend (bar chart) a basic metric using the APPROXIMATE COUNT DISTINCT function, however the above metrics are doing a little more than just counting distinct values .. ie distinct value where pvs for value > 100+

                            • 11. Re: Distinct Dimension count with metric => x
                              marion33678522 Level 2

                              I was talking about putting a Time Range on top of your metric, but yeah, I'm not sure it will work with the column sum, since it will probably count the columns of the current time :

                              • 12. Re: Distinct Dimension count with metric => x
                                Gigazelle Adobe Employee

                                I don't think there's a solid answer here within workspace, as there is a paradox of requirements:

                                 

                                • The ability to trend articles such as this would necessitate a segment, as separating it by month would require the 100+ page view requirement to be met every month, not just across the reporting period.
                                • Segments are based on users to your site (hits/visits/visitors), not variable values. A segment cannot be defined as "only use articles where there were more than 100 page views across the reporting range". That definition couldn't fit into a hit/visit/visitor container. This is why a calculated metric works.

                                 

                                The only way I anticipate you'd be able to do this is by using report builder to create a separate request for each date range, then create a line chart in excel that takes the table values and trends them.