0 Replies Latest reply: Dec 3, 2015 9:46 AM by Paolo Ferri RSS

    Count If in the data load editor / aggregate by email address

      Hi Guys,

       

      Question:

       

      I have a website where people have to answer to a simple question:
      Let’s say: “What’s the total of 2+2?” . They then have to leave their email address.
      Someone may make a mistake at the first attempt and try again. In that case he will have to resubmit the answer and insert his email address again.

      Now, as you can see in the script below, I have a list of email (Email Address) and a list of answers (your_answer)

      I am interested in understanding the email address that answered right at the first attempt, but due to  the way the data are structured I cannot do it. In fact, I can track correct answers, wrong answers, but not correct first answers.

      Therefore I was thinking about creating a new column in the script called “Correct First Answers”.
      To do so, I was thinking about using the count if in the load script and create a logic like:

      If count [your_answer ] > 1 (aggregated by email address), then
      Recode  all your_answer associated to that email address to 19 (which is a wrong answer to 2+2 = )

      This way, I will be able to separate email addresses that answered right from the email addresses that answered wrong, no matter if they answered right at the second, third attempt and so on.

      So, it’s kind of a pass / fail with just one attempt available.

      Here is the script related to the excel file I am uploading.
      Any help will be very appreciated.

      P.s. since I am quite new, please do not use abbreviations or naming conventions or I may not be able to understand

      Many Thanks

      Paolo

       

       

      // All Javascript Leads

       

       

      [thisismyTable]:

       

          LOAD

          date_submitted as ddate,

          time_submitted,

          ip_address,

          variant as "Landing Page Variant", //renaming

          page_uuid,

          name,

          email as "Email Address", //renaming to link two sheets

          your_answer,

        utm_source,

          utm_medium,

          utm_campaign,

          untitled,

          page_name,

          page_url,

          page_variant_name,

          untitled_menu,

          utm_content

          FROM [lib://Documents/myclient\All Javascript Leads (p).csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);