Project Detail

Description

Proposed automated reporting LM

Please make sure to review our requirements and respond based on these requirements or other potential solutions you believe suitable.

Overview:
LM is a third party affiliate program. We have agreements with other affiliate programs and are sourcing traffic from affiliates which will be tracked through our hosted affiliate tracking software called Direct Track (DT). In order for us to track the performance of our affiliates on our campaigns with our Merchants we receive data from the merchant in excel format. The files we receive need to be imported via a manual process into DT, this process is labour intensive and requires manual human intervention. Direct Track does support SOAP commands however the merchants will not let DT directly interface into their data. We therefore have envisaged an intermediary solution whereby we will automatically import the received excel files into our own separate database (LM_SQL) which is separate to DT and the merchants. The excel data will be mailed to us on a 4 hour interval. The idea is then to implement SOAP between LM_SQL Db and DT. The benefit of this is that we have all our data consolidated into our own Db which gives us the ability to design our own custom reports for internal purposes, reconciliation, data mining etc as well as, and most importantly it automates the process of importing the data into DT which our affiliates will then be able to view through the DT frontend.
Objective:

1.Produce near real time reports for LM on the Direct Track Platform
2.Automate the process of reporting
3.Avoid human intervention
4.Ability to map Client fields to the required DT reporting fields
5.Create a platform that can be used for all future clients of LM so as to avoid integration issues and overcome security concerns of merchants
6.Ability to write and extract more detailed reports than accessible from DT system

Process:
1.Merchant emails LM reports in a excel format on predefined intervals, such as 4 hours
2.Email arrives into a predefined mailbox at LM
3.An Outlook “Add-On” strips the excel attachment and stores it in a Windows directory on the SQL server. LM has a hosted Windows 2003 server onto which we can install, Outlook and SQL or MySQL
4.A script on LM_ SQL automatically opens the excel file and imports the data into the correct fields
5.The LM_SQL has the required SOAP commands for DT
6.DT has access to call the data from LM_SQL

Results and Benefits

1.This solution is clean and allows us to sign up any Merchants without them having to allow us access to their databases, all they have to do is email the reports in excel format to the reports email address. This means much faster setup and go to market
2.No integration issues with clients
3.Near real time reporting
4.Can prep the data exactly to DT requirements so when imported the fields match up
5.The entire process is automated which means no human intervention is required
6.We are able to write our own reports and automate these for ourselves.

Other criteria:
1.The entire solution must be documented
2.We require all source code
3.We will require some level of training
4.Easy to maintain
5.Availability to a GUI reports builder

For more detailed information, a diagram and sample of the SOAP commands for DT please review the attached file.


Additional files submitted:
Proposed automated reporting for LM.docx