From: Mike Squadrito

Sent: Wednesday, January 09, 2013 1:50 PM

To: Jennifer Clarke

Subject: carry over AML-877

Attachments: campaign_carry_over.txt; account-level-carryover-data-2013-01-08.csv

Aquidneck - Core (Dev)  build 121

1) for any given merchant uuid, it is a total of all campaign carry overs in the account

2) the status is:

    Active if there is at least one active campaign

    Paused if there is at least one paused campaign, and no active ones

    Deleted otherwise, i.e., no active or paused accounts, all are deleted

The following are SQL commands you can use to validate the report.

Below that I describe the campaign_carry_over.txt and how it can be used instead if you wish.

This will get all merchant campaigns

SELECT DISTINCT `tsacommon`.accounts.description, `st-tracker`.admaxCampaigns.id, status FROM `st-tracker`.admaxCampaigns INNER JOIN `tsacommon`.accounts ON (`tsacommon`.accounts.id=`st-tracker`.admaxCampaigns.accountID) ORDER BY `tsacommon`.accounts.description;

This is the query that gets the correct carry over for a campaign:

two places  admaxCampaignID=125 where '125' is the campaign id from above result

SELECT `tsacommon`.accounts.description, SUM(IFNULL(`st-tracker`.admaxCarryovers.dedicatedCarryover, 0) + IFNULL(`st-tracker`.admaxCarryovers.commonCarryover, 0)) AS 'Carryover' FROM `st-tracker`.admaxCarryovers INNER JOIN `st-tracker`.admaxCampaigns on (admaxCarryovers.admaxCampaignID=admaxCampaigns.id) INNER JOIN `tsacommon`.accounts on (`tsacommon`.accounts.id=`st-tracker`.admaxCampaigns.accountID) WHERE admaxCampaignID=125 AND admaxCarryovers.type='boosted' AND admaxCarryovers.date = (SELECT MAX(date) FROM `st-tracker`.admaxCarryovers where date <= '2013-01-08' and admaxCampaignID=125);

pick a merchant uuid that has a carry over value not equal to zero AND in the list you got from above, has more than one campaign - to test adding total carry over. Only one I could find was: adc30d63-1e93-4094-ac43-0150d8ff1be4


I have attached a file campaign_carry_over.txt that the report output (for testing). You can verify total carry overs by adding these values for a particular uuid. adc30d63-1e93-4094-ac43-0150d8ff1be4 is only one that seems to have 3 non zero carry overs.

IN_REPORT is the values put into the report.

all other lines are new campaings. If carry over = NULL, it isn't in carry over table, and is given a value of 0.

Example:

IN_REPORT d11fc283-3a9e-4102-ac45-9ad7f66a98ee 0 Paused
d23a9254-cd25-41c6-8a57-8b61c26c9a50 campaingID= 255 carry_over= NULL Deleted
d23a9254-cd25-41c6-8a57-8b61c26c9a50 campaingID= 254 carry_over= NULL Active
d23a9254-cd25-41c6-8a57-8b61c26c9a50 campaingID= 256 carry_over= NULL Deleted
IN_REPORT d23a9254-cd25-41c6-8a57-8b61c26c9a50 0 Active

every line above a IN_REPORT line was used to determine what goes into the report. The example output above shows carry over - NULL, so report will get 0, and since there is at least one 'Active', that goes into the report