How to calculate the total Avg Position for a campaign during a given period?

120 views
Skip to first unread message

Li Tan

unread,
Jul 8, 2016, 5:58:59 PM7/8/16
to AdWords API Forum

Hello,


How to calculate the total Avg Position for a campaign during a given period?


How to calculate the total Avg Position for campaign **XX GPP - Real Estate - A? Thank you


Sample CSV Data:


CAMPAIGN_PERFORMANCE_REPORT (Jun 1, 2016-Jun 14, 2016)

Customer ID Day Network Cost Clicks Impressions Avg. position Conversions Campaign ID Campaign Budget

xxxxxxxxxxx 2016-06-01 Search Network 18160000 17 260 4.1 1 280257197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-02 Display Network 3190000 3 1060 1 0 280171157 **XX GPP - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-07 Search Network 24320000 24 232 3.8 0 280257197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-08 Search Network 18160000 16 190 3.6 0 280257197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-02 Search Network 19730000 16 203 3.7 1 280257197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-08 Display Network 3400000 4 605 1 0 280171157 **XX GPP - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-05 Search Network 26330000 22 318 3.2 0 280257197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-10 Search Network 14710000 4 304 1.1 0 280251197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-05 Display Network 1800000 2 1274 1 0 280171157 **XX GPP - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-09 Display Network 1360000 2 334 1 0 280171157 **XX GPP - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-12 Display Network 3370000 4 890 1 0 280171157 **XX GPP - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-06 Search Network 14010000 4 363 1.1 0 280251197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-13 Search Network 18990000 18 252 3.6 1 280257197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-03 Search Network 31760000 25 212 3.7 0 280257197 **XX GPP - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-01 Search Network 4070000 2 398 1.1 0 280251197 **XX GPP - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-09 Search Network 53780000 11 304 1 0 280251197 **XX GPP - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-10 Display Network 3540000 4 495 1 0 280171157 **XX GPP - Real Estate - B 3000000

xxxxxxxxxxx 2016-06-14 Search Network 20880000 18 230 3.5 0 280257197 **XX GPP - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-12 Search Network 20520000 17 148 3.3 0 280257197 **XX GPP - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-01 Display Network 2070000 2 1551 1 0 280171157 **XX GPP - Real Estate - B 3000000

xxxxxxxxxxx 2016-06-02 Search Network 15140000 4 374 1.1 0 280251197 **XX GPP - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-03 Search Network 2140000 2 6 1.3 0 280256957 **XX GPP - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-11 Search Network 26570000 53 429 2 0 280248797 **XX GPP - Real Estate - B 40430000

xxxxxxxxxxx 2016-06-13 Search Network 3840000 3 10 1 0 280256957 **XX GPP - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-14 Search Network 1480000 3 8 1 0 280257077 **XX GPP - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-03 Search Network 18970000 26 3301 2.3 0 280250597 **XX GPP - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-03 Search Network 27240000 52 485 2 0 280248797 **XX GPP - Real Estate - B 40430000

xxxxxxxxxxx 2016-06-04 Search Network 30260000 41 2998 2.2 2 280250597 **XX GPP - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-13 Search Network 14880000 24 4518 2.8 0 280250597 **XX GPP - Rentals - A 75000000

xxxxxxxxxxx 2016-06-08 Search Network 760000 1 6 1 0 280257077 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-09 Search Network 20450000 8 97 1.1 0 280250717 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-12 Search Network 12110000 5 61 1.1 0 280250717 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-02 Search Network 1740000 1 11 1.2 0 280256957 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-01 Search Network 1550000 1 8 1.1 0 280256957 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-06 Search Network 28170000 50 431 2 0 280248797 **XX GPP - Rentals - A 40430000

xxxxxxxxxxx 2016-06-02 Search Network 1790000 2 6 1 0 280257077 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-14 Search Network 28530000 38 4186 2.5 0 280250597 **XX GPP - Rentals - A 75000000

xxxxxxxxxxx 2016-06-11 Search Network 820000 2 3 1 0 280257077 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-04 Search Network 0 0 4 1.5 0 280256957 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-10 Search Network 0 0 7 1 0 280256957 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-06 Search Network 0 0 7 1.3 0 280256957 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-01 Search Network 980000 2 4 1 0 280257077 **XX GPP - Rentals - A 25000000

xxxxxxxxxxx 2016-06-01 Search Network 14700000 7 55 1.1 0 280250717 **XX GPP - Rentals - B 25000000

xxxxxxxxxxx 2016-06-09 Search Network 16130000 23 2866 2 0 280250597 **XX GPP - Rentals - B 75000000

xxxxxxxxxxx 2016-06-10 Search Network 26680000 36 2853 2.1 0 280250597 **XX GPP - Rentals - B 75000000

xxxxxxxxxxx 2016-06-12 Search Network 26840000 54 469 2.1 0 280248797 **XX GPP - Rentals - B 40430000

xxxxxxxxxxx 2016-06-14 Search Network 2130000 1 2 1 0 280256837 **XX GPP - Rentals - B 75000000

xxxxxxxxxxx 2016-06-13 Search Network 27750000 58 596 1.8 0 280248797 **XX GPP - Rentals - B 40430000

xxxxxxxxxxx 2016-06-05 Search Network 1100000 1 11 1.4 0 280256957 **XX GPP - Rentals - B 25000000

xxxxxxxxxxx 2016-06-07 Search Network 12150000 5 154 1.1 0 280250717 **XX GPP - Rentals - B 25000000

xxxxxxxxxxx 2016-06-13 Search Network 7570000 5 81 1.1 0 280250717 **XX GPP - Rentals - B 25000000

xxxxxxxxxxx 2016-06-10 Search Network 26990000 54 529 1.9 0 280248797 **XX GPP - Rentals - B 40430000

xxxxxxxxxxx 2016-06-03 Search Network 26440000 9 124 1.1 0 280250717 **XX GPP - Rentals - B 25000000

xxxxxxxxxxx 2016-06-05 Search Network 1620000 4 6 1 0 280257077 **XX GPP - Rentals - B 25000000

xxxxxxxxxxx 2016-06-07 Search Network 2220000 1 5 1 0 280256957 **XX GPP - Rentals - B 25000000

xxxxxxxxxxx 2016-06-10 Search Network 0 0 2 1 0 280257077 **XX GPP - Rentals - B 25000000

xxxxxxxxxxx 2016-06-09 Search Network 0 0 11 1.3 0 280256957 **XX Remarketing 25000000

xxxxxxxxxxx 2016-06-11 Search Network 0 0 10 1.4 0 280256957 **XX Remarketing 25000000

xxxxxxxxxxx 2016-06-02 Search Network 0 0 2 1 0 280256837 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-13 Search Network 0 0 7 1 0 280257077 **XX Remarketing 25000000

xxxxxxxxxxx 2016-06-13 Search Network 0 0 1 1 0 280256837 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-14 Search Network 29940000 10 523 1.1 0 280251197 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-04 Search Network 14790000 13 170 3.6 0 280257197 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-12 Search Network 42400000 15 370 1.1 0 280251197 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-07 Search Network 18200000 6 342 1.1 0 280251197 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-10 Search Network 21290000 15 196 3.1 0 280257197 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-03 Search Network 24840000 7 282 1.1 0 280251197 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-11 Search Network 34040000 25 216 3 0 280257197 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-13 Display Network 3970000 5 1192 1 0 280171157 **XX Remarketing 3000000

xxxxxxxxxxx 2016-06-08 Search Network 4930000 2 322 1 0 280251197 **XX Remarketing 75000000

xxxxxxxxxxx 2016-06-07 Display Network 3050000 3 1612 1 0 280171157 **XX Sconset - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-04 Search Network 20490000 5 317 1.1 0 280251197 **XX Sconset - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-13 Search Network 0 0 369 1.1 0 280251197 **XX Sconset - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-05 Search Network 19910000 7 326 1.1 0 280251197 **XX Sconset - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-06 Search Network 28760000 26 269 3.8 0 280257197 **XX Sconset - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-11 Search Network 17430000 6 338 1 0 280251197 **XX Sconset - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-04 Display Network 850000 2 742 1 0 280171157 **XX Sconset - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-03 Display Network 3050000 3 852 1 0 280171157 **XX Sconset - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-11 Display Network 3070000 3 898 1 0 280171157 **XX Sconset - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-14 Display Network 860000 1 1355 1 0 280171157 **XX Sconset - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-06 Display Network 3270000 3 893 1 0 280171157 **XX Sconset - Real Estate - A 3000000

xxxxxxxxxxx 2016-06-09 Search Network 9980000 8 163 3.9 0 280257197 **XX Sconset - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-02 Search Network 0 0 28 1 0 280250717 **XX Sconset - Real Estate - A 25000000

xxxxxxxxxxx 2016-06-07 Search Network 19650000 28 4065 2.1 2 280250597 **XX Sconset - Real Estate - A 75000000

xxxxxxxxxxx 2016-06-02 Search Network 25510000 35 4253 2.1 0 280250597 **XX Sconset - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-06 Search Network 9750000 2 160 1.1 0 280250717 **XX Sconset - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-07 Search Network 26690000 57 525 2 0 280248797 **XX Sconset - Real Estate - B 40430000

xxxxxxxxxxx 2016-06-11 Search Network 19990000 8 81 1 0 280250717 **XX Sconset - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-11 Search Network 23890000 31 3130 2.4 2 280250597 **XX Sconset - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-08 Search Network 19880000 27 3735 2.2 0 280250597 **XX Sconset - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-10 Search Network 8580000 4 96 1.1 0 280250717 **XX Sconset - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-12 Search Network 16010000 5 12 1.1 0 280256957 **XX Sconset - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-05 Search Network 24660000 44 490 2.2 0 280248797 **XX Sconset - Real Estate - B 40430000

xxxxxxxxxxx 2016-06-08 Search Network 3930000 3 130 1 0 280250717 **XX Sconset - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-09 Search Network 3800000 1 1 1 0 280256837 **XX Sconset - Real Estate - B 75000000

xxxxxxxxxxx 2016-06-12 Search Network 1840000 4 6 1 0 280257077 **XX Sconset - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-14 Search Network 1840000 2 11 1.4 0 280256957 **XX Sconset - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-04 Search Network 0 0 27 1.2 0 280250717 **XX Sconset - Real Estate - B 25000000

xxxxxxxxxxx 2016-06-12 Search Network 0 0 1 1 0 280256837 **XX Sconset - Rentals - A 75000000

xxxxxxxxxxx 2016-06-04 Search Network 0 0 2 1 0 280257077 **XX Sconset - Rentals - A 25000000

xxxxxxxxxxx 2016-06-01 Search Network 12010000 18 4503 2.2 0 280250597 **XX Sconset - Rentals - A 75000000

xxxxxxxxxxx 2016-06-09 Search Network 26890000 51 485 1.9 0 280248797 **XX Sconset - Rentals - A 40430000

xxxxxxxxxxx 2016-06-01 Search Network 27440000 58 537 2.1 0 280248797 **XX Sconset - Rentals - A 40430000

xxxxxxxxxxx 2016-06-08 Search Network 4660000 2 3 1 0 280256837 **XX Sconset - Rentals - A 75000000

xxxxxxxxxxx 2016-06-07 Search Network 610000 2 5 1 0 280257077 **XX Sconset - Rentals - A 25000000

xxxxxxxxxxx 2016-06-01 Search Network 4260000 1 3 1 0 280256837 **XX Sconset - Rentals - A 75000000

xxxxxxxxxxx 2016-06-04 Search Network 26570000 56 443 2.1 1 280248797 **XX Sconset - Rentals - A 40430000

xxxxxxxxxxx 2016-06-12 Search Network 20820000 28 2524 2.7 1 280250597 **XX Sconset - Rentals - A 75000000

xxxxxxxxxxx 2016-06-14 Search Network 23740000 49 513 2 0 280248797 **XX Sconset - Rentals - B 40430000

xxxxxxxxxxx 2016-06-03 Search Network 980000 2 4 1 0 280257077 **XX Sconset - Rentals - B 25000000

xxxxxxxxxxx 2016-06-05 Search Network 7710000 5 205 1.1 0 280250717 **XX Sconset - Rentals - B 25000000

xxxxxxxxxxx 2016-06-06 Search Network 13290000 23 4461 2.3 2 280250597 **XX Sconset - Rentals - B 75000000

xxxxxxxxxxx 2016-06-08 Search Network 28190000 57 469 2 0 280248797 **XX Sconset - Rentals - B 40430000

xxxxxxxxxxx 2016-06-09 Search Network 2420000 2 7 1 0 280257077 **XX Sconset - Rentals - B 25000000

xxxxxxxxxxx 2016-06-05 Search Network 27390000 40 4531 2.1 0 280250597 **XX Sconset - Rentals - B 75000000

xxxxxxxxxxx 2016-06-06 Search Network 2080000 4 9 1 0 280257077 **XX Sconset - Rentals - B 25000000

xxxxxxxxxxx 2016-06-02 Search Network 21800000 40 492 2.2 0 280248797 **XX Sconset - Rentals - B 40430000

xxxxxxxxxxx 2016-06-08 Search Network 3570000 2 8 1.3 0 280256957 **XX Sconset - Rentals - B 25000000

xxxxxxxxxxx 2016-06-03 Search Network 1620000 1 2 1 0 280256837 **XX Sconset - Rentals - B 75000000

xxxxxxxxxxx 2016-06-14 Search Network 0 0 54 1.1 0 280250717 **XX Sconset - Rentals - B 25000000

xxxxxxxxxxx 2016-06-06 Search Network 0 0 2 1 0 280256837 **XX Sconset - Rentals - B 75000000

xxxxxxxxxxx 2016-06-04 Search Network 0 0 1 1 0 280256837 **XX Sconset - Rentals - B 75000000

Total -- -- 1501080000 1648 82128 2 13 -- -- 143430000



Anthony Madrigal

unread,
Jul 8, 2016, 6:18:49 PM7/8/16
to AdWords API Forum
Hi,

This question seems better suited for the AdWords Help Center to answer. You can contact them through this link.

Cheers,
Anthony
AdWords API Team

Li Tan

unread,
Jul 11, 2016, 9:36:49 AM7/11/16
to AdWords API Forum
I already contact the AdWords Help Center, and they told me to post the question in the forum.

Anthony Madrigal

unread,
Jul 11, 2016, 2:22:26 PM7/11/16
to AdWords API Forum
Hello,

Ok, we will try to help you as best we can. Could you please provide more details on what you are trying to find out? Do you want to know the formula in which total average position is calculated?

Thanks,
Anthony
AdWords API Team

Li Tan

unread,
Jul 11, 2016, 4:31:46 PM7/11/16
to AdWords API Forum
I am trying to find the total Avg Position for each campaign using the API.

What is the formula? Thanks.
adwords_avg.png

Anthony Madrigal

unread,
Jul 11, 2016, 5:17:42 PM7/11/16
to AdWords API Forum
Hi,

You can calculate the average position for all of your campaigns by setting skipReportSummary = false. If you are still not getting the total average position in your reports, could you please send me via reply privately to author your report definition that you used and your client customer Id?

Zweitze

unread,
Jul 12, 2016, 8:21:35 AM7/12/16
to AdWords API Forum
>What is the formula? Thanks.

In SQL, on a table where each row has the results of a campaign:
SUM(Impressions * AveragePosition) / SUM(Impressions)

Li Tan

unread,
Jul 12, 2016, 11:33:04 AM7/12/16
to AdWords API Forum
I know that I can get the Avg Position for all campaigns using the API, but I need to calculate Avg Position for each campaign as well. Thank you.

Li Tan

unread,
Jul 12, 2016, 11:35:44 AM7/12/16
to AdWords API Forum
When I used all the campaigns Impression and Avg Position data with this formula (SUM(Impressions * AveragePosition) / SUM(Impressions)), I got the right number.
However, I got a different number when I selected one specific campaign with this formula. SUM(Impressions * AveragePosition) / SUM(Impressions)

Thanks

Li Tan

unread,
Jul 12, 2016, 11:54:25 AM7/12/16
to AdWords API Forum
I attached an excel file for testing.
adwords-report-avg-position.xls

Thanet Knack Praneenararat (AdWords API Team)

unread,
Jul 12, 2016, 11:57:27 PM7/12/16
to adwor...@googlegroups.com
Hi Li,

It seems your excel file contains something that I'm not sure it's correct.
What I understand is you're trying to get average position for each campaign over the time period (e.g., 5 days), right?
The file seems to not sort rows based on campaign IDs, so I'm not sure how you get the value in colored cell (AdWords real).

What seems strange to me is that your campaign IDs and campaign names are not equivalent.
For example, campaign ID 280171157 has the name as Jane - Rentals - B in one row but has the name as ** John - Rentals - A in another row.
Could you please confirm if this's correct? If you need more help, please send you report definition to me via Reply privately to author.

Hope this helps.

Best,
Thanet, AdWords API Team

Zweitze

unread,
Jul 13, 2016, 8:06:09 AM7/13/16
to AdWords API Forum
I think that your Excel file got corrupt somehow, for instance:
The first calculation of cell N3 is about rows 3-9, each having a campaign named "** John - Real Estate - A". But... those rows have different campaign IDs. The dates are between July 4 and July 10, but July 5 is missing and July 6 is listed twice (although with different cost, impressions etc.)
I don't think this corresponds to the original download.

Before I forget: when retrieving impressions, make sure you do not include segment 'clicktype' as well.

Li Tan

unread,
Jul 13, 2016, 2:26:40 PM7/13/16
to AdWords API Forum
Thanks for the help. @Thanet

Formula: =SUM(L17:L23)/SUM(F17:F23)
Reply all
Reply to author
Forward
0 new messages