Update .js for sales tracking to save data to Google Spreadsheet. More work to follow.

*This is a test project. My commercial software (started in 1995) regularly has .js programming tests that I'd like to outsource.* I'd like you to modify some code (in the details section, adding the following: 1. Add several extra parameters. 2. Store the tracked data in a Google Spreadsheet rather than a database. If you can suggest another easy to use database that you can easily to install on our Windows Server (shared developer hosting account @ [login to view URL]) and that also has an easy to use free interface, please do suggest that. **Testing** Make sure that all three sources of clicks are tested (Pay Per Click, search engine, direct link) and that each writes the correct data to the Google Spreedsheet. 1. Pass these values in to a "landing page" and then go to a mocked up "purchase" page and confirm they get entered correctly into the Google Spreadsheet. **Adwords** ?utm_source=AdWords&utm_medium=utm_medium_TESTVALUE&utm_term=utm_term_TESTVALUE+TESTVALUE2&utm_content=utm_content_TESTVALUE&Network=SEARCH_TESTVALUE&SiteTarget=SiteTarget_TEST&utm_campaign=utm_campaign_TEST&AdGroup=AdGroup **OrganicSearch** 2. ?<[login to view URL]:en-US:official&client=firefox-a> **Direct Link** url: [login to view URL] ## Deliverables 1. **The incoming links will be from serveral sources with several formats:** 1. **Pay Per click Could be from Adwords, Bing or maybe even another source. ** **Adwords** ?utm_source=AdWords&utm_medium=PPC&utm_term={keyword}&utm_content={creative}&Network={ifContent:Content}{ifSearch:Search}&SiteTarget={placement}&utm_campaign=&AdGroup= 2. **natural (organic) search**. If so then set the *utm_source*=OrganicSearch and the utm_campaign= or **Google Search**: <[login to view URL]:en-US:official&client=firefox-a> or **Bing Search**: [[login to view URL]**q**=**term1**+**term2**+term3&qs=n&form=QBRE&pq=term1+term2+term3&sc=0-5&sp=-1&sk=][1] 3. **Referral link (link from another website).** Just set utm_source=referencing url 2. The cookies could be set and read from several different domains (we have several domains and our shopping cart is hosted on another domain). The only way I know to make that work all (or most) of the time is to use an **iFrame** displaying a page from our main site. You'll need to mirror all of the ?Parameters from the container page to the iFrame so that the page inside of it gets that data. The page that reads the cookie will also need to run in an iFrame which will include on our third party shopping cart checkout. 3. **Additional requirements** 4. Additional optional parameters: **gs ** (google spreadsheet name). It's ideal if you can use the NAME of the Google Spreadhseet ("AnonymousPPC") not the long cryptic Google Doc link. **q** (search terms from search engine) **DateFirstContact** (date the cookie is generated. That may be automatically included when you create a cookie, if not you'll need to generate this and save it with the cookie. **SubGoal ** (this is just a secondary conversion value for future use) **App** **Param1**, **Param2** (again, just an additional value for future use) 1. Default values (if no parameter passed via the url) *utm_source* : The website it came from (or, if it's a search engine: the search *engine url*: [login to view URL], [login to view URL], etc.) 2. Note: It's very possible that _not_ all the above parameters will be passed via the URL. If so, just store the ones that are passed. Partial data is OK. 3. There may be domain variations of the above, etc. ([login to view URL] or whatever) so make sure you're using pattern matching (recognizing [login to view URL] as google, or whatever). 4. Store the incoming parameters in a cookie (per *Landing Page* Code, below) 5. Conversion value tracking Use the **Conversion Code** (below) but _change_ it to store it in a Google Spreadsheet (ideal option) . That Conversion Code writes to a database, but I want you to change that code to write to a spreadsheet or CSV file. If you can't do that let me know and _maybe_ we can use a flat CSV file (comma separated file). 1. If no gs value (google spreadsheet) value is passed in then just store it in a default google Spreadsheet. 2. if a gs Paramater is included in the cookie, then match that to a list of names for several Google Spreadshets. =0 gs values could be: 1. **Install ** (so this is the name of a Google Spreadsheet that we might store cookies about installation in. Example: if LowerCase(gs)="install" then GoogleDocLink=[login to view URL] 3. Make sure you include the date as a column. Each parameter will be one column. I'd like to be able to **Re-Arrange the columns**, so check the first line for Column Headings when saving data. (I.e., if "Source" is column 1 today but changes to Column 2 tomorrow then save the Source data into the 2nd Column (Source) [login to view URL] that will take more than an extra hour, let me know and we'll NOT do this. 4. **Modifications to the Conversion Code** ConversionValue (this isn't in the ConversionCode now b/c the person who sells it only have one product at one price, so he was just counting the number of occurences. I'll want to be able to call that Conversion Code from several pages and just pass in a ConversionValue. So I'll want that script to be off the page, and we'll just call the script (it'll be stored on the same domain). Something like SaveAdwordsCookieToDatabase( Value) Need pass in a ConversionValue, which coudl be either numeric ($ amount) or a text value for an event "Downloaded a trial". so if that sale were worth $100 then Value=100. Sometimes that Value will come from the shopping cart and sometimes it'll be an estimated value or maybe even just non-numeric text ( "downloaded Trial". Wow, you've actually read the whole spec. Thanks! I worked hard on it and appreciate you taking the time to read it. You must be *very* detail oriented. Mention that in your bid or a message and you're more likely to get this job. **Landing Page code** function index() // when a page loads it calls this method { // confirm that the URL contains the required GET request parameters if($this->hasAdwordsRequestParameters()) $this->setAdwordsCookie(); $this->load->view('landingPage_view'); } function hasAdwordsRequestParameters() { return isset($_GET['k']) && isset($_GET['t']) && isset($_GET['a']); } function setAdwordsCookie() { // Only set it once, don't reset it if they click again on the Adwords ad. Not perfect but good enough. That and we // want to know what the sales cycle length is. Otherwise they could just re-find the website through the ad. if(!get_cookie('adwords')) { // type, keyword, adCode $type = $_GET['t']; $keyword = $_GET['k']; $ad = $_GET['a']; $cookie = array( 'name' => 'adwords', 'value' => $type . '.' . $keyword . '.' . $ad . '.' . date('Y-m-d'), 'expire' => '15778463', ); set_cookie($cookie); } } **Conversion Code for "shopping cart" Page** function saveAdwordsCookieToDatabase() { $cookieData = get_cookie('adwords'); // No need to save anything if there isn't a cookie to be saved. if(!$cookieData) return; list($type, $keyword, $ad, $date) = explode('.', $cookieData); // some additional security/safety code if(!isset($type) || !isset($keyword) || !isset($ad) || !isset($date)) return; $sqlData = array( 'type' => $type, 'keyword' => $keyword, 'ad' => $ad, 'dateClicked' => $date, 'datePurchased' => date('Y-m-d') ); // the CodeIgniter classes includes all the sql injection protection code and so on. $this->db->insert('adwords', $sqlData); } * * *This broadcast message was sent to all bidders on Friday Oct 5, 2012 5:12:35 PM: I added one more requirement: it needs to work cross-domain (so, leaving a cookie at [login to view URL] and reading it at [login to view URL]) using an iFrame.