Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 1.3.0
-
Fix Version/s: 1.5.0
-
Component/s: Data Summarization
-
Labels:None
-
Environment:
Optus -production
Description
I am looking into an issue in Optus production where the data in the warehouse is not matching up the with data eg for costs in the googleMccAdmax. All the googleMcc tables (googleMccDailyCheck, googleMcc, googleMccAdMax) agree with each other.
So what I decided to do, was a pick a day where the spend information was not lining up (clearly greater than 2% difference) and look at all the accounts spend for that day in the googleMccAdMax and the warehouse.
The day I picked was: 2012-11-17 (17th of November)
Spend in googleMccAdmax: 80.09
Spend in the warehouse: 80.09
Spend in the actual production MCC: 80.09
It looks like the spend in the googleMccAdmax is lining up with what is in the MCC.
I broke this down into more detail to figure out which account information was not lining up between the googleMccAdmax and the warehouse.
Warehouse spend on the 17th: 76.01
mysql> select now(), startTime, sum(seAdCost), accountID from whDailyGroupPerf where startTime='2012-11-17' group by accountID;
-----------------------------------------------------+
| now() | startTime | sum(seAdCost) | accountID |
-----------------------------------------------------+
| 2012-11-20 17:16:31 | 2012-11-17 | 0.86 | 4 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.88 | 313 |
| 2012-11-20 17:16:31 | 2012-11-17 | 4.66 | 322 |
| 2012-11-20 17:16:31 | 2012-11-17 | 8.93 | 338 |
| 2012-11-20 17:16:31 | 2012-11-17 | 2.2 | 343 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.3 | 370 |
| 2012-11-20 17:16:31 | 2012-11-17 | 4.46 | 380 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.16 | 437 |
| 2012-11-20 17:16:31 | 2012-11-17 | 4 | 447 |
| 2012-11-20 17:16:31 | 2012-11-17 | 3.22 | 501 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.49 | 516 |
| 2012-11-20 17:16:31 | 2012-11-17 | 7.33 | 581 |
| 2012-11-20 17:16:31 | 2012-11-17 | 4.35 | 594 |
| 2012-11-20 17:16:31 | 2012-11-17 | 2.08 | 599 |
| 2012-11-20 17:16:31 | 2012-11-17 | 3.07 | 634 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.05 | 643 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.08 | 654 |
| 2012-11-20 17:16:31 | 2012-11-17 | 5.88 | 672 |
| 2012-11-20 17:16:31 | 2012-11-17 | 2.94 | 678 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.46 | 680 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.82 | 692 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0 | 694 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.18 | 701 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.78 | 704 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.18 | 708 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.71 | 712 |
| 2012-11-20 17:16:31 | 2012-11-17 | 6.19 | 713 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.41 | 719 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.35 | 729 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.15 | 731 |
| 2012-11-20 17:16:31 | 2012-11-17 | 3.84 | 736 |
googleMcc Admax spend on the 17th: 80.09
mysql> select sum(gma.cost/1000000), sea.accountID from staging.googleMccAdMax gma join tsacommon.searchEngineAccounts sea on sea.searchEngineIdentifier = gma.campaignid where date='2012-11-17' group by sea.accountID;
--------------------------------+
| sum(gma.cost/1000000) | accountID |
--------------------------------+
| 0.86 | 4 |
| 0.88 | 313 |
| 4.66 | 322 |
| 8.93 | 338 |
| 2.2 | 343 |
| 1.2 | 370 |
| 4.46 | 380 |
| 1.16 | 437 |
| 4 | 447 |
| 3.22 | 501 |
| 0.49 | 516 |
| 7.33 | 581 |
| 4.35 | 594 |
| 2.08 | 599 |
| 3.07 | 634 |
| 1.05 | 643 |
| 1.08 | 654 |
| 5.88 | 672 |
| 2.94 | 678 |
| 0.46 | 680 |
| 0.82 | 692 |
| 3.18 | 694 |
| 1.18 | 701 |
| 1.78 | 704 |
| 0.18 | 708 |
| 1.71 | 712 |
| 6.19 | 713 |
| 0.41 | 719 |
| 0.35 | 729 |
| 0.15 | 731 |
| 3.84 | 736 |
--------------------------------+
31 rows in set (0.02 sec)
As you can see, there were two accounts with the issue. Account: 370, and Account: 694.
Account 370:
Warehouse spend: 0.3
googleMccAdmax spend: 1.2
Actual spend from the production MCC: 1.2
Account 694:
Warehouse spend: 0
googleMccAdmax spend: 3.18
Actual spend from the production MCC: 3.18
Going through the warehouse summerizer code, it looks like the whsumm.sh summerizes the data into whDailyGroupPerf by getting the information from the whDailySourcePerf tables (Please correct me if that is an in-correct assumption). whDailySourcePerf tables also had the incorrect information for spend for accounts 370 and 694 for 17th of November.
Drilling down further, I worked out that the warehouse eventually ends up getting the information from the searchEngineActivity tables store in the accounts database in the tmp database on port 3307.
mysql> select * from searchEngineActivitySummary201211 where accountID=694 and startTime='2012-11-17';
------------------------------------------------------------------------------------------------------------------------------------------------------------
| distributionID | accountID | searchEngineUserID | sourceID | searchEngineListingID | siteID | startTime | count | impressions | cost | avgPosition | avgCTR | conversions | value | qualityScore |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | 694 | 84 | 219455 | 16916 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 9.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 694 | 84 | 219585 | 16925 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 1.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 219585 | 16926 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 1.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 219585 | 16927 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 2.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 219589 | 16927 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 219697 | 16931 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 219973 | 16942 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 1.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 219973 | 16943 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 1.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 220937 | 17011 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 2.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 221511 | 17046 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 6.000 |
| 3 | 694 | 84 | 221559 | 17051 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 694 | 84 | 221561 | 17049 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 11.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 694 | 84 | 222307 | 17109 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 1.000 | 0.000000 | NULL | 0 | 4.000 |
------------------------------------------------------------------------------------------------------------------------------------------------------------
13 rows in set (0.00 sec)
Account 694 also did not have any spend information in the searchEngineActivitySummary201211 table. This matches the warehouse and not the googleMccAdmax.
mysql> select * from searchEngineActivitySummary201211 where accountID=370 and startTime='2012-11-17';
------------------------------------------------------------------------------------------------------------------------------------------------------------
| distributionID | accountID | searchEngineUserID | sourceID | searchEngineListingID | siteID | startTime | count | impressions | cost | avgPosition | avgCTR | conversions | value | qualityScore |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | 370 | 72 | 190821 | 14818 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 1.000 | 0.000000 | NULL | 0 | 5.000 |
| 3 | 370 | 72 | 190821 | 14822 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 5.000 |
| 3 | 370 | 72 | 190821 | 14823 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 5.000 |
| 3 | 370 | 72 | 190821 | 14836 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 5.000 |
| 3 | 370 | 72 | 190821 | 14840 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 5.000 |
| 3 | 370 | 72 | 190821 | 14841 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 5.000 |
| 3 | 370 | 72 | 210268 | 16178 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 3.000 | 0.000000 | NULL | 0 | 10.000 |
| 3 | 370 | 72 | 210294 | 16182 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 3.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210294 | 16183 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 8.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210294 | 16184 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 6.330 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210294 | 16185 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 13.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210296 | 16183 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210296 | 16184 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210298 | 16182 | 0 | 2012-11-17 | 0 | 13 | 0.000 | 3.620 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210298 | 16183 | 0 | 2012-11-17 | 0 | 6 | 0.000 | 3.830 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210298 | 16184 | 0 | 2012-11-17 | 0 | 71 | 0.000 | 4.060 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210298 | 16185 | 0 | 2012-11-17 | 0 | 6 | 0.000 | 4.500 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210300 | 16183 | 0 | 2012-11-17 | 0 | 5 | 0.000 | 4.200 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210300 | 16184 | 0 | 2012-11-17 | 0 | 13 | 0.000 | 5.080 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210300 | 16185 | 0 | 2012-11-17 | 0 | 11 | 0.000 | 3.820 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210302 | 16185 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210304 | 16182 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 9.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210304 | 16184 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210306 | 16182 | 0 | 2012-11-17 | 0 | 16 | 0.000 | 3.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210306 | 16183 | 0 | 2012-11-17 | 0 | 4 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210306 | 16184 | 0 | 2012-11-17 | 0 | 56 | 0.000 | 3.610 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210306 | 16185 | 0 | 2012-11-17 | 0 | 5 | 0.000 | 4.800 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210308 | 16185 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210310 | 16182 | 0 | 2012-11-17 | 0 | 11 | 0.000 | 4.180 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210310 | 16183 | 0 | 2012-11-17 | 0 | 10 | 0.000 | 3.900 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210310 | 16184 | 0 | 2012-11-17 | 0 | 34 | 0.000 | 4.410 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210310 | 16185 | 0 | 2012-11-17 | 0 | 18 | 0.000 | 4.940 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210312 | 16182 | 0 | 2012-11-17 | 0 | 4 | 0.000 | 3.750 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210312 | 16183 | 0 | 2012-11-17 | 0 | 6 | 0.000 | 4.670 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210312 | 16184 | 0 | 2012-11-17 | 0 | 29 | 0.000 | 4.590 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210312 | 16185 | 0 | 2012-11-17 | 0 | 5 | 0.000 | 4.800 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210314 | 16182 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210314 | 16184 | 0 | 2012-11-17 | 0 | 8 | 0.000 | 4.250 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210316 | 16182 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210316 | 16184 | 0 | 2012-11-17 | 1 | 16 | 0.300 | 4.000 | 0.062500 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210316 | 16185 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 4.500 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210320 | 16182 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210320 | 16184 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210323 | 16184 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210323 | 16185 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 7.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210331 | 16184 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210335 | 16182 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210335 | 16184 | 0 | 2012-11-17 | 0 | 24 | 0.000 | 4.330 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210335 | 16185 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210338 | 16182 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210338 | 16183 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 9.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210338 | 16184 | 0 | 2012-11-17 | 0 | 4 | 0.000 | 3.750 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210338 | 16185 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 11.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210343 | 16184 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210346 | 16186 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210348 | 16182 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 2.500 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210348 | 16183 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210348 | 16184 | 0 | 2012-11-17 | 0 | 16 | 0.000 | 2.560 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210352 | 16184 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210364 | 16187 | 0 | 2012-11-17 | 0 | 4 | 0.000 | 2.500 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210364 | 16188 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 1.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210364 | 16189 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 1.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210492 | 16198 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 4.670 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210494 | 16200 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 7.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210496 | 16199 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 5.500 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210496 | 16201 | 0 | 2012-11-17 | 0 | 5 | 0.000 | 4.600 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210502 | 16198 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 3.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210502 | 16199 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210502 | 16200 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210518 | 16198 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210536 | 16202 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210540 | 16204 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210552 | 16203 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 11.000 | 0.000000 | NULL | 0 | 5.000 |
| 3 | 370 | 72 | 210573 | 16210 | 0 | 2012-11-17 | 0 | 5 | 0.000 | 4.800 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210573 | 16211 | 0 | 2012-11-17 | 0 | 4 | 0.000 | 4.500 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210573 | 16212 | 0 | 2012-11-17 | 0 | 5 | 0.000 | 3.200 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210573 | 16213 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 5.330 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210575 | 16210 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 11.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210575 | 16211 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 11.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210575 | 16213 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 10.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210587 | 16210 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 15.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210587 | 16211 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 11.500 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210587 | 16212 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 8.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210587 | 16213 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 12.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210589 | 16210 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 9.500 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210589 | 16211 | 0 | 2012-11-17 | 0 | 11 | 0.000 | 4.450 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210589 | 16212 | 0 | 2012-11-17 | 0 | 6 | 0.000 | 7.500 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210589 | 16213 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 12.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210593 | 16211 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 14.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210593 | 16212 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 11.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210595 | 16210 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 12.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210595 | 16212 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 6.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210597 | 16210 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 12.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210597 | 16211 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210597 | 16212 | 0 | 2012-11-17 | 0 | 7 | 0.000 | 5.570 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210597 | 16213 | 0 | 2012-11-17 | 0 | 3 | 0.000 | 8.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210599 | 16210 | 0 | 2012-11-17 | 0 | 23 | 0.000 | 4.480 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210599 | 16211 | 0 | 2012-11-17 | 0 | 29 | 0.000 | 3.380 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210599 | 16212 | 0 | 2012-11-17 | 0 | 9 | 0.000 | 3.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210599 | 16213 | 0 | 2012-11-17 | 0 | 8 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210603 | 16210 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 4.500 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210603 | 16211 | 0 | 2012-11-17 | 0 | 5 | 0.000 | 3.800 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210603 | 16212 | 0 | 2012-11-17 | 0 | 6 | 0.000 | 3.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210603 | 16213 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210607 | 16212 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 14.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210615 | 16211 | 0 | 2012-11-17 | 0 | 2 | 0.000 | 5.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210615 | 16213 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 2.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210617 | 16211 | 0 | 2012-11-17 | 0 | 4 | 0.000 | 3.500 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210617 | 16213 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 11.000 | 0.000000 | NULL | 0 | 3.000 |
| 3 | 370 | 72 | 210637 | 16211 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 3.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210641 | 16211 | 0 | 2012-11-17 | 0 | 11 | 0.000 | 2.820 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210641 | 16212 | 0 | 2012-11-17 | 0 | 8 | 0.000 | 2.750 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210643 | 16210 | 0 | 2012-11-17 | 0 | 4 | 0.000 | 4.000 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210643 | 16211 | 0 | 2012-11-17 | 0 | 21 | 0.000 | 3.190 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210643 | 16212 | 0 | 2012-11-17 | 0 | 16 | 0.000 | 2.560 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210643 | 16213 | 0 | 2012-11-17 | 0 | 4 | 0.000 | 3.500 | 0.000000 | NULL | 0 | 4.000 |
| 3 | 370 | 72 | 210645 | 16211 | 0 | 2012-11-17 | 0 | 1 | 0.000 | 2.000 | 0.000000 | NULL | 0 | 4.000 |
------------------------------------------------------------------------------------------------------------------------------------------------------------
117 rows in set (0.00 sec)
Account 370 also did not have the correct spend information in the searchEngineActivitySummary201211 table. This matches the warehouse and not the googleMccAdmax.
I am not quite sure where to go from here. The question I have is where does the searchEngineActivitySummary table gets its spend information from? Ie., which database etc.
I am going to keep looking but any help or thoughts would be greatly appreciated.
I want to pin-point the exact place where the data does not line up, so that I can figure out what part of the process the issue is happening
I think I worked out why we are not getting the data.
I worked out that the data from the searchEngineActivitySummary table gets generated by the searchEngineSummerizer script and traced through the script.
The search engine summerizer script is getting the data from the google MCC and google MCC has the data for the 17th of November for account 694.
mysql> select (gm.cost/1000000), sea.accountID, campaignID, adwordsType, kwType from staging.googleMcc gm join tsacommon.searchEngineAccounts sea on sea.searchEngineIdentifier = gm.campaignid where startTime='2012-11-17' and sea.accountID=694;
---------------------------------------------------------
| (gm.cost/1000000) | accountID | campaignID | adwordsType | kwType |
---------------------------------------------------------
| 0 | 694 | 94787953 | Content Only | Content |
| 0 | 694 | 94787953 | Content Only | Content |
| 0 | 694 | 94787953 | Content Only | Content |
| 0 | 694 | 94787953 | Content Only | Content |
| 0 | 694 | 94788193 | Search Only | Broad |
| 0 | 694 | 94788193 | Content Only | Content |
| 0 | 694 | 94788193 | Content Only | Content |
| 0 | 694 | 94788313 | Search Only | Broad |
| 0 | 694 | 94788313 | Search Only | Broad |
| 0 | 694 | 94788313 | Search Only | Broad |
| 0 | 694 | 94788313 | Search Only | Broad |
| 0 | 694 | 94788313 | Content Only | Content |
| 2.22 | 694 | 94788313 | Content Only | Content |
| 0 | 694 | 94788313 | Content Only | Content |
| 0.96 | 694 | 94788313 | Content Only | Content |
| 0 | 694 | 94788433 | Search Only | Exact |
| 0 | 694 | 94788673 | Search Only | Broad |
| 0 | 694 | 94788673 | Search Only | Broad |
| 0 | 694 | 94789393 | Search Only | Broad |
| 0 | 694 | 94789633 | Search Only | Broad |
| 0 | 694 | 94789633 | Search Only | Broad |
| 0 | 694 | 94790113 | Search Only | Broad |
| 0 | 694 | 94790713 | Search Only | Broad |
---------------------------------------------------------
23 rows in set (0.00 sec)
The query that extracts the data from the googleMcc to the searchEngineActivitySummary table is only looking for adwordsType = SearchOnly.
Here is the query as I extracted from the logs:
select * from `staging`.`googleMcc` where ((`staging`.`googleMcc`.`campaignid` in (94794313,94794073,94794193,94793353,94793473,94793113,94793233,94881793,94793833,94793953,94793593,94793713,94790233,94790353,94790473,94790593,94790713,94790833,94790953,94791073,94789273,94789393,94789513,94789633,94789753,94789873,94789993,94790113,94792273,94792153,94792513,94792393,94792753,94792633,94792993,94792873,94791313,94791193,94791553,94791433,94791793,94791673,94792033,94791913,94788073,94788193,94787953,94789033,94789153,94788793,94788913,94788553,94788673,94788313,94788433)) and (`staging`.`googleMcc`.`startTime`="2012-11-17 00:00:00") and (`staging`.`googleMcc`.`adwordsType`="Search Only") and (`staging`.`googleMcc`.`kwType` in ("Broad","BroadMatch","Exact","Phrase")));
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| startTime | campaignid | adgroupid | keywordid | keyword | kwType | creativeid | creativeDestUrl | kwDestUrl | adwordsType | maxCpc | imps | clicks | cost | pos | conv | transactions | purchases | purchaseValue | leads | leadValue | signups | signupValue | pageviews | pageviewValue | defaultConv | defaultConvValue | qualityScore | firstPageCPC |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2012-11-17 | 94788193 | 5354982913 | 17744110 | Broad | 22866585193 | Search Only | 0 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6.000 | 3000000 | |||
| 2012-11-17 | 94788313 | 5354983033 | 11490821 | Broad | 22866583633 | Search Only | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 2000000 | |||
| 2012-11-17 | 94788313 | 5354983033 | 11490821 | Broad | 22866583753 | Search Only | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 2000000 | |||
| 2012-11-17 | 94788313 | 5354983033 | 11490821 | Broad | 22866583873 | Search Only | 0 | 1 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 2000000 | |||
| 2012-11-17 | 94788313 | 5354983033 | 14628573 | Broad | 22866583873 | Search Only | 0 | 2 | 0 | 0 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 3250000 | |||
| 2012-11-17 | 94788433 | 5354983153 | 45120431233 | Exact | 22866585553 | Search Only | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4.000 | 550000 | |||
| 2012-11-17 | 94788673 | 5354983393 | 13202360 | Broad | 22866586513 | Search Only | 0 | 1 | 0 | 0 | 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4.000 | 3250000 | |||
| 2012-11-17 | 94788673 | 5354983393 | 13166880 | Broad | 22866586753 | Search Only | 0 | 1 | 0 | 0 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 2500000 | |||
| 2012-11-17 | 94789393 | 5354984113 | 11490821 | Broad | 22866589633 | Search Only | 0 | 1 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 2000000 | |||
| 2012-11-17 | 94789633 | 5354984353 | 11490821 | Broad | 22866589993 | Search Only | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 2000000 | |||
| 2012-11-17 | 94789633 | 5354984353 | 11490821 | Broad | 22866590113 | Search Only | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 2000000 | |||
| 2012-11-17 | 94790113 | 5354984833 | 13201760 | Broad | 22866592273 | Search Only | 0 | 1 | 0 | 0 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4.000 | 900000 | |||
| 2012-11-17 | 94790713 | 5354985433 | 11490821 | Broad | 22866594553 | Search Only | 0 | 1 | 0 | 0 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3.000 | 2000000 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
13 rows in set (0.00 sec)
The place where we spent in this instance on the 17th is for adwords Type = Content only.
This is why the data is not lining up between the googleMcc and the warehouse.
Sorry this may be a stupid question but is there a reason why we are not selecting data with adwords Type = Content only?
What would you recommend is the best way to move forward with this issue? Should the query be changed to include the data from adwords Type = Content only or is it ok that the warehouse data only has the data for search as long as we can explain away on a case by case basis?
Warehouse spend on the 17th: 76.01
mysql> select now(), startTime, sum(seAdCost), accountID from whDailyGroupPerf where startTime='2012-11-17' group by accountID;
-----------------------------------------------------+
| now() | startTime | sum(seAdCost) | accountID |
-----------------------------------------------------+
| 2012-11-20 17:16:31 | 2012-11-17 | 0.86 | 4 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.88 | 313 |
| 2012-11-20 17:16:31 | 2012-11-17 | 4.66 | 322 |
| 2012-11-20 17:16:31 | 2012-11-17 | 8.93 | 338 |
| 2012-11-20 17:16:31 | 2012-11-17 | 2.2 | 343 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.3 | 370 |
| 2012-11-20 17:16:31 | 2012-11-17 | 4.46 | 380 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.16 | 437 |
| 2012-11-20 17:16:31 | 2012-11-17 | 4 | 447 |
| 2012-11-20 17:16:31 | 2012-11-17 | 3.22 | 501 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.49 | 516 |
| 2012-11-20 17:16:31 | 2012-11-17 | 7.33 | 581 |
| 2012-11-20 17:16:31 | 2012-11-17 | 4.35 | 594 |
| 2012-11-20 17:16:31 | 2012-11-17 | 2.08 | 599 |
| 2012-11-20 17:16:31 | 2012-11-17 | 3.07 | 634 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.05 | 643 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.08 | 654 |
| 2012-11-20 17:16:31 | 2012-11-17 | 5.88 | 672 |
| 2012-11-20 17:16:31 | 2012-11-17 | 2.94 | 678 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.46 | 680 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.82 | 692 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0 | 694 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.18 | 701 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.78 | 704 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.18 | 708 |
| 2012-11-20 17:16:31 | 2012-11-17 | 1.71 | 712 |
| 2012-11-20 17:16:31 | 2012-11-17 | 6.19 | 713 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.41 | 719 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.35 | 729 |
| 2012-11-20 17:16:31 | 2012-11-17 | 0.15 | 731 |
| 2012-11-20 17:16:31 | 2012-11-17 | 3.84 | 736 |
googleMcc Admax spend on the 17th: 80.09
mysql> select sum(gma.cost/1000000), sea.accountID from staging.googleMccAdMax gma join tsacommon.searchEngineAccounts sea on sea.searchEngineIdentifier = gma.campaignid where date='2012-11-17' group by sea.accountID;
--------------------------------+
| sum(gma.cost/1000000) | accountID |
--------------------------------+
| 0.86 | 4 |
| 0.88 | 313 |
| 4.66 | 322 |
| 8.93 | 338 |
| 2.2 | 343 |
| 1.2 | 370 |
| 4.46 | 380 |
| 1.16 | 437 |
| 4 | 447 |
| 3.22 | 501 |
| 0.49 | 516 |
| 7.33 | 581 |
| 4.35 | 594 |
| 2.08 | 599 |
| 3.07 | 634 |
| 1.05 | 643 |
| 1.08 | 654 |
| 5.88 | 672 |
| 2.94 | 678 |
| 0.46 | 680 |
| 0.82 | 692 |
| 3.18 | 694 |
| 1.18 | 701 |
| 1.78 | 704 |
| 0.18 | 708 |
| 1.71 | 712 |
| 6.19 | 713 |
| 0.41 | 719 |
| 0.35 | 729 |
| 0.15 | 731 |
| 3.84 | 736 |
--------------------------------+
31 rows in set (0.02 sec)
For account 694, which is missing the spend information from the display network, the campaign that actually spent the money is: 82190643000197-04116611664_289_29050
The search engine identifier for this campaign is: 94788313
The search engine account id for this campaign is: 4216
I used the search engine account id (4216) and checked the st-tracker.sources table to make sure there were sources present for this account. My assumption here is, this is the sources entry you were referring to in your previous email for this the seupdate creates sources for. Please advise if my assumption is in-correct.
mysql> select * from searchEngineAccounts where id=4216;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | accountID | distributionID | searchEngineUserID | description | shortCode | paused | searchEngineIdentifier | searchEngineScope | inContent | enabled | useConversionData | searchEngineStatus | searchEngineStatusText | budgetType | budgetAmount | lastChecked | networkType | contentType | biddingStrategy |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 4216 | 694 | 3 | 84 | 82190643000197-0411661664_2000409_289_29050_GT | NULL | false | 94788313 | true | true | false | ok | ACTIVE | dailyDivide | 2.75 | 2012-11-28 09:11:53 | CONTENT_NETWORK,GOOGLE_SEARCH,SEARCH_NETWORK | CONTENT_NETWORK | BudgetOptimizer |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--
mysql> select * from sources where searchEngineAccountID=4216 and searchEngineMatchType='content';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | altKey | accountID | siteID | searchEngineAccountID | keywordID | distributionID | searchEngineIdentifier | searchEngineGroupIdentifier | searchEngineMatchType | searchEngineGroupID | isBiddable | campaignID | description | type | keyword | sourceURL | cost | maxCost | waypointID | landingURL | passParams | trackPerformance | searchEngineStatus | searchEngineStatusText | lastChecked |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 219640 | go4729_136c | 694 | 0 | 4216 | 0 | 3 | 2859278492 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [environmental recycling services] content | 1 | environmental recycling services | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_136c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219588 | go4729_142c | 694 | 0 | 4216 | 0 | 3 | 11559301 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle bin] content | 1 | recycle bin | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_142c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219612 | go4729_141c | 694 | 0 | 4216 | 0 | 3 | 117601271 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle bin] content | 1 | recycle bin | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_141c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219594 | go4729_144c | 694 | 0 | 4216 | 0 | 3 | 25120480 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle bins] content | 1 | recycle bins | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_144c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219614 | go4729_143c | 694 | 0 | 4216 | 0 | 3 | 142620364 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle bins] content | 1 | recycle bins | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_143c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219602 | go4729_145c | 694 | 0 | 4216 | 0 | 3 | 43299203 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle companies] content | 1 | recycle companies | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_145c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219690 | go4729_147c | 694 | 0 | 4216 | 0 | 3 | 45120504433 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle curb pick ups] content | 1 | recycle curb pick ups | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_147c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219692 | go4729_146c | 694 | 0 | 4216 | 0 | 3 | 45120504553 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle curb pick up] content | 1 | recycle curb pick up | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_146c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219670 | go4729_148c | 694 | 0 | 4216 | 0 | 3 | 45120503233 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle curb pickup] content | 1 | recycle curb pickup | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_148c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219678 | go4729_149c | 694 | 0 | 4216 | 0 | 3 | 45120503713 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle curb pickups] content | 1 | recycle curb pickups | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_149c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219666 | go4729_151c | 694 | 0 | 4216 | 0 | 3 | 45120502993 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle kerb pick ups] content | 1 | recycle kerb pick ups | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_151c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219668 | go4729_150c | 694 | 0 | 4216 | 0 | 3 | 45120503113 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle kerb pick up] content | 1 | recycle kerb pick up | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_150c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219682 | go4729_152c | 694 | 0 | 4216 | 0 | 3 | 45120503953 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle kerb pickup] content | 1 | recycle kerb pickup | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_152c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219686 | go4729_153c | 694 | 0 | 4216 | 0 | 3 | 45120504193 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle kerb pickups] content | 1 | recycle kerb pickups | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_153c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219628 | go4729_154c | 694 | 0 | 4216 | 0 | 3 | 1138802349 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle service] content | 1 | recycle service | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_154c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219630 | go4729_155c | 694 | 0 | 4216 | 0 | 3 | 1316661106 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle services] content | 1 | recycle services | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_155c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219654 | go4729_156c | 694 | 0 | 4216 | 0 | 3 | 6776565942 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle waste services] content | 1 | recycle waste services | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_156c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219622 | go4729_158c | 694 | 0 | 4216 | 0 | 3 | 415156554 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle yard] content | 1 | recycle yard | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_158c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219656 | go4729_157c | 694 | 0 | 4216 | 0 | 3 | 7440356936 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle yard] content | 1 | recycle yard | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_157c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219650 | go4729_160c | 694 | 0 | 4216 | 0 | 3 | 5636343647 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle yards] content | 1 | recycle yards | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_160c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219652 | go4729_159c | 694 | 0 | 4216 | 0 | 3 | 5882219851 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycle yards] content | 1 | recycle yards | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_159c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219586 | go4729_162c | 694 | 0 | 4216 | 0 | 3 | 11490821 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling] content | 1 | recycling | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_162c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219608 | go4729_161c | 694 | 0 | 4216 | 0 | 3 | 51880190 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling] content | 1 | recycling | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_161c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219648 | go4729_163c | 694 | 0 | 4216 | 0 | 3 | 5539587744 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling and disposal services] content | 1 | recycling and disposal services | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_163c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219590 | go4729_165c | 694 | 0 | 4216 | 0 | 3 | 14628573 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling bin] content | 1 | recycling bin | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_165c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219604 | go4729_164c | 694 | 0 | 4216 | 0 | 3 | 44674312 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling bin] content | 1 | recycling bin | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_164c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219592 | go4729_167c | 694 | 0 | 4216 | 0 | 3 | 19371223 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling bins] content | 1 | recycling bins | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_167c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219610 | go4729_166c | 694 | 0 | 4216 | 0 | 3 | 113942429 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling bins] content | 1 | recycling bins | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_166c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219634 | go4729_168c | 694 | 0 | 4216 | 0 | 3 | 1597898528 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling collection services] content | 1 | recycling collection services | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_168c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219600 | go4729_170c | 694 | 0 | 4216 | 0 | 3 | 43298183 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling companies] content | 1 | recycling companies | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_170c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219626 | go4729_169c | 694 | 0 | 4216 | 0 | 3 | 1013546889 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling companies] content | 1 | recycling companies | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_169c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219694 | go4729_172c | 694 | 0 | 4216 | 0 | 3 | 45120504673 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling curb pick ups] content | 1 | recycling curb pick ups | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_172c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219696 | go4729_171c | 694 | 0 | 4216 | 0 | 3 | 45120504793 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling curb pick up] content | 1 | recycling curb pick up | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_171c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219676 | go4729_173c | 694 | 0 | 4216 | 0 | 3 | 45120503593 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling curb pickup] content | 1 | recycling curb pickup | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_173c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219680 | go4729_174c | 694 | 0 | 4216 | 0 | 3 | 45120503833 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling curb pickups] content | 1 | recycling curb pickups | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_174c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219636 | go4729_175c | 694 | 0 | 4216 | 0 | 3 | 1597899128 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling disposal services] content | 1 | recycling disposal services | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_175c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219672 | go4729_177c | 694 | 0 | 4216 | 0 | 3 | 45120503353 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling kerb pick ups] content | 1 | recycling kerb pick ups | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_177c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219674 | go4729_176c | 694 | 0 | 4216 | 0 | 3 | 45120503473 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling kerb pick up] content | 1 | recycling kerb pick up | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_176c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219662 | go4729_178c | 694 | 0 | 4216 | 0 | 3 | 45119248873 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling kerb pickup] content | 1 | recycling kerb pickup | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_178c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219664 | go4729_180c | 694 | 0 | 4216 | 0 | 3 | 45119248993 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling kerb pickups] content | 1 | recycling kerb pickups | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_180c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219684 | go4729_179c | 694 | 0 | 4216 | 0 | 3 | 45120504073 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling kerb pickup] content | 1 | recycling kerb pickup | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_179c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219688 | go4729_181c | 694 | 0 | 4216 | 0 | 3 | 45120504313 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling kerb pickups] content | 1 | recycling kerb pickups | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_181c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219646 | go4729_182c | 694 | 0 | 4216 | 0 | 3 | 4591110428 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling pick up service] content | 1 | recycling pick up service | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_182c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219606 | go4729_184c | 694 | 0 | 4216 | 0 | 3 | 50657953 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling service] content | 1 | recycling service | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_184c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219618 | go4729_183c | 694 | 0 | 4216 | 0 | 3 | 377985027 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling service] content | 1 | recycling service | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_183c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219596 | go4729_186c | 694 | 0 | 4216 | 0 | 3 | 35546941 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling services] content | 1 | recycling services | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_186c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219620 | go4729_185c | 694 | 0 | 4216 | 0 | 3 | 377985147 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling services] content | 1 | recycling services | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_185c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219624 | go4729_188c | 694 | 0 | 4216 | 0 | 3 | 839352163 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling yard] content | 1 | recycling yard | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_188c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219660 | go4729_187c | 694 | 0 | 4216 | 0 | 3 | 12774613197 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling yard] content | 1 | recycling yard | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_187c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219616 | go4729_190c | 694 | 0 | 4216 | 0 | 3 | 252101139 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling yards] content | 1 | recycling yards | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_190c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219658 | go4729_189c | 694 | 0 | 4216 | 0 | 3 | 8013926870 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [recycling yards] content | 1 | recycling yards | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_189c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219632 | go4729_191c | 694 | 0 | 4216 | 0 | 3 | 1419506395 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [service recycling] content | 1 | service recycling | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_191c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219642 | go4729_193c | 694 | 0 | 4216 | 0 | 3 | 3432642490 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [waste and recycling services] content | 1 | waste and recycling services | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_193c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219644 | go4729_192c | 694 | 0 | 4216 | 0 | 3 | 4590978068 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [waste and recycling companies] content | 1 | waste and recycling companies | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_192c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 | ||
| 219598 | go4729_194c | 694 | 0 | 4216 | 0 | 3 | 37170476 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [waste recycling companies] content | 1 | waste recycling companies | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_194c&tsacr=go
{creative} | | true | ok | ACTIVE | 0000-00-00 00:00:00 | | 219638 | go4729_195c | 694 | 0 | 4216 | 0 | 3 | 2393097205 | 5354983033 | content | 4219 | false | 0 | BiddableAdGroupCriterion: [waste recycling services] content | 1 | waste recycling services | | 0 | 0 | NULL | http://www.cleanitoffice.com.au/sem-recyclingservicesyarra.html?refcd=go4729_195c&tsacr=go{creative}
|
true | ok | ACTIVE | 0000-00-00 00:00:00 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
56 rows in set (0.00 sec)
It looks like even though there are sources for this account, some how sesummerize script is not putting the spend data for the display network in the searchEngineActivitySummary table for November.
