Performance data isn't cleared out on rerun

In most cases, performance data is cleared out correctly on a rerun. For example, if Yahoo does some click fraud scrubbing and reduces impressions or clicks from 5 to 4, when we rerun, the warehouse (or ABU) will use the updated value (4).

In one scenario, where Yahoo removes all impressions and clicks for a keyword, the data is not cleared out. Changed data for other keywords will be correctly reflected in the warehouse, but the old data for the keyword which had impressions taken away (so it was completely removed from the report) will still remain in the warehouse (and Yahoo Keyword table).

This bug can be reproduced for a new account that doens't have any data at all (since we're inserting fake data). To reproduce, deploy an account. insert fake data in to the performance tables: yahooJPerformanceReports, yahooJDailyCheck, yahooJAdMax, and run the corresponding reporting script (see below for the corresponding scripts). The row for the yahooJPerformanceReports will need to come from the sources table for a keyword that doesn't have any data for that date. The campaignID and campaign name can be found in searchEngineAccounts.searchEngineIdentifier. The adgroupID can be found in sources.searchEngineGroupIdentifier. After running the report you will see that the fake data has not been cleared out.

Use something like this query to find the information from sources to insert into yahooJPerformanceReports:

select c.searchEngineIdentifier 'campaignID', searchEngineGroupIdentifier 'adGroupID', s.searchEngineIdentifier 'keywordID' from `st-tracker`.sources s join tsacommon.searchEngineAccounts c on s.accountID=c.accountID where c.description like '<MERCHANT UUID>%';

Insert statements and reporting scripts to reproduce the bug:

yahooJPerformanceReports (yahoojapanreport.sh run with -rt performance)

insert into staging.yahooJPerformanceReports (startTime, campaignID, adGroupID, keywordid, kwType, avgCpc, imps, clicks, cost, pos, qualityScore) values (DATE, CAMPAIGNID, ADGROUPID, KEYWORDID, KWTYPE, 5.0, 20, 10, 5000, 3.5, 4.2);

example:
insert into staging.yahooJPerformanceReports (startTime, campaignID, adGroupID, keywordid, kwType, avgCpc, imps, clicks, cost, pos, qualityScore) values ('2014-11-24', 944584, 333986158, 740172688, 'Broad', 5.0, 20, 10, 5000, 3.5, 4.2);

Sample command:
./usr/local/tsa/bidmgr/yahoojapanreport.sh -T 2014-11-24 -rt performance --force -U145

yahooJAdMax (yahoojapanreport.sh run with -rt admax)

INSERT INTO staging.yahooJAdMax VALUES(DATE, CAMPAIGNID, 5, 500, 5);

example:
INSERT INTO staging.yahooJAdMax VALUES('2014-11-24', 944584, 5, 500, 5);

Sample command:
./usr/local/tsa/bidmgr/yahoojapanreport.sh -T 2014-11-24 -rt admax --force -U145

yahooJDailyCheck (sedatachecker.sh)

INSERT INTO staging.yahooJDailyCheck VALUES (DATE, CAMPAIGNID, CAMPAIGNNAME, 500, 5, 500);

example (insert a couple of rows of data since sedatacheck runs for multiple days):
INSERT INTO staging.yahooJDailyCheck VALUES ('2014-11-24', 944584, 'a142_150499_400039_581_GM', 500, 5, 500);
INSERT INTO staging.yahooJDailyCheck VALUES ('2014-11-22', 944584, 'a142_150499_400039_581_GM', 500, 5, 500);

Sample command:
./usr/local/tsa/bidmgr/sedatacheck.sh -T 2014-11-20 -E 2014-11-24 -d3 --distribution 178 --threads 5 -U145