14 Replies Latest reply: Mar 28, 2018 7:19 PM by Roger Bielicke RSS

    Aggregation Help

    Roger Bielicke

      Problem to solve.  Ad Groups rows are like "All Star Podiatrist Near Me" and "Podiatrist St. Louis" are showing in both tables.  This is due to to formula currently looking at each row of data, where I prefer to aggregate the all the rows for each Ad Group and assign to only one table.  Here's the current formula for CTR.  How do I update to aggregate for the total CTR for each Ad Group rather than each individual row of data?  As a bonus, how do I make the "CTR=(<"0.96")" into the Aggregate Avg CTR so automatically updates with data refresh.  Bonus items is lower priority.

       

      =sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Clicks])

      /sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Impressions])

      Screen Shot 2018-03-20 at 9.20.44 PM.pngScreen Shot 2018-03-20 at 9.21.00 PM.png

        • Re: Aggregation Help
          Sunny Talwar

          Not sure I understand, what is the expected output here? Which table should "All Star Podiatrist Near Me" appear in?

          • Re: Aggregation Help
            David Forest

            I think you're wanting to use Aggr() to roll up the measures by Ad Group and have an overall Average?

             

            Sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>} Aggr(sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Clicks]),[Ad Group])

            /Sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>} Aggr(sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Impressions]),[Ad Group])

              • Re: Aggregation Help
                Roger Bielicke

                Yes, David, trying to use the Aggr() function.  Now when I tried to copy the entire formula you proposed I get an error expression.  When I take just the part starting from Aggr it states OK but returns no values.  First, is it supposed to be the entire expression or just the portion starting from Aggr?  Second, any thoughts what may be throwing it off?

                 

                Much appreciate the help.

                  • Re: Aggregation Help
                    David Forest

                    sorry missing a paren at the end of each

                    Sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>} Aggr(sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Clicks]),[Ad Group]))

                    /Sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>} Aggr(sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Impressions]),[Ad Group]))

                     

                    As I dont have your data, hard to say. Assuming the original expression was correct and the the field to group by is called [Ad Group].

                    I usually break things down and solve one formula at a time, gradually adding/evaluating each set expression one at a time to see where an error might be. Syntax checker doesn't always catch things in set expressions.

                      • Re: Aggregation Help
                        Roger Bielicke

                        Much appreciate the help and the step by step approach David.  Okay, I was able to get aggregation step to work with

                        =Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])

                         

                        resulting in the following which is accurate.

                        Screen Shot 2018-03-24 at 1.57.21 PM.png

                        Now I want to take the next step and only include those where the Aggregate CTR is >=5%, thus the final result would only be the 3 yellow highlighted rows below.  Note below is pivot table aggregation of many rows of data for each.  Assuming need another Aggr() formula but I'm guessing it would not be simply on CTR but sum version of Aggr(Clicks,AdGroup) /Aggr(Impressions,AdGroup) >=.05%.

                        Screen Shot 2018-03-24 at 2.05.29 PM.png

                        Any thoughts or suggestion much appreciated.

                          • Re: Aggregation Help
                            Sasidhar Parupudi

                            May be use an if?

                            If (

                            Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=.05,

                            Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])

                            0

                            )

                              • Re: Aggregation Help
                                Roger Bielicke

                                Thanks Asisdhar.   I tried the following - tweaked a bit to simplify - but am getting "error in expressions" message.  Can you tell what's off in the formula?

                                =If (Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=10,000,

                                Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])0)

                                 

                                I would expect the result to pull the below with only the top 2 rows with impressions > 10,000.  If I can get this view to work, I can then apply at more complex level for the CTR > 5%.  Thanks for your help.Screen Shot 2018-03-28 at 12.01.41 PM.png

                                  • Re: Aggregation Help
                                    David Forest

                                    Looks like you're missing a comma before the 0 at the end:

                                    =If (Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=10,000,

                                    Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group]),0)

                                      • Re: Aggregation Help
                                        Roger Bielicke

                                        Yes, that fixes the expression.  Now that provides the following result, which is almost there.  How do I know show the only the aggregate rows that meet the IF = True?  Rather than listing with the other rows with = 0, goal is to simply exclude them from the view and only the top 2 rows would show.

                                         

                                        Here's current formula  and current result.

                                        =If (Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=10000,

                                        Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group]),0)Screen Shot 2018-03-28 at 12.47.17 PM.png

                                          • Re: Aggregation Help
                                            David Forest

                                            remove the 0 from the if, don't provide the false and it will return null.

                                              • Re: Aggregation Help
                                                Roger Bielicke

                                                David - Thanks for the quick reply.  That changes the 0 to a "--" and the rows still there.  How do I approach so that those rows do show in the visual?   Thanks,

                                                 

                                                Screen Shot 2018-03-28 at 12.56.50 PM.png

                                                  • Re: Aggregation Help
                                                    David Forest

                                                    drat. so, need to put the "if" into the set expression Something like:

                                                     

                                                    variable vImpressions =

                                                    If (Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=10000,

                                                    Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group]),0)

                                                     

                                                    variable vTopImpressions =

                                                    Sum({$<[Ad group-Sheet1-2.Ad group]={"=$(vImpressions)>=10000"}>} $(vImpressions))

                                                     

                                                    might need

                                                    Sum({$<[Ad group-Sheet1-2.Ad group]={"=$(=$(vImpressions))>=10000"}>} $(vImpressions))

                                                      • Re: Aggregation Help
                                                        Sunny Talwar

                                                        unchecking 'Include Zero Value' under Add-ons -> Data Handling should remove 0s

                                                          • Re: Aggregation Help
                                                            Roger Bielicke

                                                            Thank you all very much.  You helped me reach the end game.  Here is example of formula and final output where only showed those Ad Groups with Aggregate CTR > 5% and Conv Rate > 9.6%.   Much appreciate the coaching.

                                                             

                                                            =if(Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Clicks]),[Ad group-Sheet1-2.Ad group])

                                                            /Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=.05,

                                                            if(Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Conversions]),[Ad group-Sheet1-2.Ad group])

                                                            /Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Clicks]),[Ad group-Sheet1-2.Ad group])>=.096,

                                                            Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])/

                                                            Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Total Impressions]),[Ad group-Sheet1-2.Ad group]),0))

                                                            Screen Shot 2018-03-28 at 6.15.58 PM.png