Specifications for UK Pixel Check Page

Use Case:

The TSA UK team will be performing test conversions checking TSA Pixel Implementation and Firing on their client’s web sites. They need to see same day results on whether the pixel loads where captured into our system. Because this data is un-summarized, they will look based on timestamps of the conversions in results.

Risks:

This requires access to the Database (specifically the shard the account is on) to query the results.

Solution:

Create a secured page that allows the UK Team to specify one of their accounts. This will query the appropriate database shard and return the results for the current day as a flat file (csv).

Sample Case:

Small Luxury Hotels
Site ID 808
DB Shard: acctdb-05-write.dc1bos.thesearchagency.com

Query:

SELECT FROM_UNIXTIME(unixTimestamp) as Date,refererURL as Conversion_Page,remoteAddr as IPAddress,backRef as orderID,name as ProductID,count,value as revenue
FROM spike_v2.waypointLog wl
join spike_v2.waypointConversionLog wcl using (siteID,unixTimestamp,microseconds,rowNum)
where
wl.siteID = 808 and
wl.unixTimestamp > unix_timestamp('2012-10-10')

Variables of Query:

DB shard to query, siteID and date (unix_timestamp).

DB Shard and SiteID would be determined by account selected. The menu of available accounts would be determined by looking at a predetermined user (users table from tsacommon) and grabbing the accounts from the userAccountMap table (similar to how the account pulldown is generated in Admax). DB Shard and SiteID can be pulled from the accounts and sites tables (joined).