YPG - Campaign Structure Report

YPG has requested a campaign structure report to be delivered daily to an SFTP site.

This report must:

• Be delivered daily via SFTP (SFTP portion to be addressed in different task)
• File is in csv format
• File is UTF8 encoded
• First record must contain the column headers
• File name: YYYYMMDD_HHMMSS_YPG_AdMaxLocal_Campaign_Structure_Report_LL.csv where the prefix is the creation timestamp of the file and LL is the language code of the taxonomy (en/fr). In case we need to produce this report for different clients in the future, please include an option that allows the user to specify the file name, even if the default is in this format.

The file contains the following data:

• Snapshot date (YYY-MM-DD) - We are required to produce this report every single day. If we miss a day we will have to rerun the report and manually verify the data for fields that are not historical such as SE campaign status and quote name (which is based on the most recently deployed order). In this scenario one file would contain data for two dates. Example: It is March 30 and for some reason we cannot deliver reports for March 29. On the next day, March 31 we would deliver a report that contains data for both March 30 and March 29.
• Merchant UUID
• Merchant ID*
• Merchant Name*
• Quote Name* (YPG will be using this field to store their Asset ID)
• Campaign ID (AML campaign ID: st-tracker.admaxCampaigns.id)
• SE Campaign Identifier
• SE Campaign Name (format: merchantUUID_BAID_locID_advertID_SECampaignType(GT/GM))
• Distribution (Google, Bing)
• SE Campaign Start Date (day budget is set for the campaign)
• SE Campaign End Date
• SE Campaign Status (OK = Active, Disabled = Paused and Deleted = Removed)
• BA_ID
• BA*
• Loc_ID
• BL*
• Actual Daily Budget ( ABU value for that day)
• Nominal Monthly Budget (Monthly budget minus markup: st-tracker.admaxNominalMonthlyBudgets.budget, it is understood that this value is unique to the AdMax campaign and not the search engine campaign)
• Destination URL - since ads can have slightly different destination URLs they are only looking for the primary destination URL of the order (mms_common.campaigns.customerDestinationURL - this is a better place to get the destination URL since the smb.orders table will not be able to provide destination URLs for deleted campaigns)

* indicates column should be double quoted ("example"). While it is very unlikely we will have double quotes in any of these columns, YPG has requested we use the standard backslash () to escape any double quotes. Example if the merchant's name was The Almond "Milk" Company it would show up as "The Almond \"Milk\" Company" in the report.

Once the report is complete please document it in Confluence: https://admaxlocal.atlassian.net/wiki/display/OPS/YPG+Specific+Reports

See attached Excel sheet for column names and more details. SE Campaign Structure tab.

Need to be able to produce this report for stacks with non English taxonomies - specifically a French taxonomy. The BA IDs are not the same but there is a constant offset that can be subtracted to get the English BA ID.