Tracking email opens with Google Analytics - without using __utm.gif or javascript

Click in e-mail

When sending e-mails out it's allready common practice to make sure every click in your newsletter is tracked by Google analytics

Google has a fine online tool for this which explains all the possible parameters so on analytics you can see the clicks under Conversions / Campaigns
example: http://yourwebsite.com?utm_source=Newsletter&utm_medium=email&utm_campaign=DecemberEdition

E-mail is opened

But what is more difficult is to track if an e-mail is opened. When you install a Google tracker on your website you install a javascript which generates html which renders a __utm.gif on the google servers. While passing through every parameter Google analytics sees which pages are opened

Since it is impossible to run Javascript in a e-mail it is not possibe to use this method.
For some years we included a self generated version of the utm.gif but since August 2014 we've noticed that this method doesn't work anymore

Offcourse this only works if users load the images of the e-mail

No longer using utm.gif

After some hours of debugging I read that Google actually (finally) created a fully documented way to solve this issue. There is now something called the "Measurement Protocol"

The Google Analytics Measurement Protocol allows developers to make HTTP requests to send raw user interaction data directly to Google Analytics servers.

Normally you can only do a HTTP post but HTTP GET is also possible.
So I've put a little PHP code together which generates a img src like this and I can see the events in my Google analytics.

<img src="https://www.google-analytics.com/collect?v=1&tid=UA-XXXX-Y&cid=1234&t=event&ec=Newsletter&ea=EmailOpen&el=DecemberEdition&cs=Newsletter&cm=email&cn=DecemberEdition">

Both pageview as event tracking is very easy now. Check this page for more details

Missing from this list are
cs= utm source
cm= utm medium
cn= utm campaign

BIC opvullen in Dynamics NAV met vbs

Op de website van http://www.ibanbic.be/ vind je een zeer handige tool om de IBAN nummer en Swift code (BIC) op te zoeken van een leverancier of klant

Omdat er in ons boekhoudpakket geen automatische manier is om dit op te vullen schreef ik een VBscript dat deze automatisch opvult. Deze gebruikt de webservice van ibanbic.

Ik heb eerst een periode getest voor ik de toestemming heb gevraagd. Vanaf dat ik deze teveel gebruikte kreeg ik automatische deze melding als antwoord :-)

MAIL ME TO UNLOCK - You have reached our fair-use daylimit of 2000 conversion. No problem if you want to use more. Just send me an email at info@ebcs.be or mds@ebcs.be, I will unblock your IP address


Ik roep dit script op met de parameter Klant of Leverancier

cscript D:\Scripts\Nav_BIC_opvullen.vbs Klant > "D:\Scripts\Log\%Date:~6,4%%Date:~3,2%%Date:~0,2%_%Time:~0,2%u%Time:~3,2%_IBANBIC_http_post.log"

Nav_BIC_opvullen.vbs


'Tabelnaam bepalen adhv parameter
Select Case Wscript.Arguments.Item(0)
    Case "Leverancier"
        BIC_invullen "XXXXXXX$Vendor Bank Account","Vendor"
    Case "Klant"
        BIC_invullen "XXXXXXX$Customer Bank Account","Customer"
End Select


'BBAN opzoeken om een IBAN + BIC request te doen.

Function BIC_invullen(Tabelnaam,Soort)
set Cn = createobject ("ADODB.connection")
Cn.Connectionstring = "Driver={SQL Server};Server=XXXXX;Database=XXXXX;Uid=XXXXX;Pwd=XXXXX;"
Cn.open
set rs = createobject("ADODB.Recordset")
set rs.activeconnection = cn

Set HTTP = CreateObject("MSXML2.XMLHTTP")


    sql = "select [Country_region Code], ["& Soort &" No_],Code, Name,[Bank Account No_], IBAN, [SWIFT Code],  " & _
            "RIGHT(Replace(Replace( coalesce(NULLIF(IBAN,''), [Bank Account No_]),'-',''),' ',''),12) as Rekening " & _
            "from ["& Tabelnaam &"]  " & _
            "where ([Bank Account No_] <> '' or IBAN <> '') and [SWIFT Code] = '' "

    wscript.echo sql
    rs.open(sql)

    If not rs.eof and not rs.bof then
        i = 0
        do while not Rs.EOF
            wscript.echo "[Start record "&i&"]"
            i = i + 1
            Bericht = "Value=" & rs("Rekening")
            wscript.echo Bericht
           
            Url = "http://www.ibanbic.be/IBANBIC.asmx/BBANtoIBANandBIC"
            HTTP.Open "POST", Url, False
           
            HTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded;charset=UTF-8"
            HTTP.setRequestHeader "Content-Length", Len(Bericht)
           
            HTTP.send(Bericht)
            'WScript.Echo HTTP.status
           
            If HTTP.status = 200 Then
                WScript.Echo HTTP.Statustext
                'WScript.Echo "OK en antwoord is: "
                'WScript.Echo HTTP.responseBody
                'WScript.Echo HTTP.responseText
                Antwoord = HTTP.responseText

               
                IBAN = Mid(Antwoord, instr(Antwoord,"<string xmlns=""http://tempuri.org/"">")+36, instr(Antwoord,"#")-instr(Antwoord,"<string xmlns=""http://tempuri.org/"">")-36)
                IBAN = Replace(IBAN," ","")
               
                BIC = Mid(Antwoord, instr(Antwoord,"#")+1, instr(Antwoord,"</string>")-instr(Antwoord,"#")-1)
                BIC = Replace(BIC," ","")
                wscript.echo "IBAN=" & IBAN & " - BIC=" & BIC
               
                If BIC <> "" and IBAN <> "" Then
                sql = "update ["& Tabelnaam &"] " & _
                        "set [IBAN] = '" & IBAN & "',  [SWIFT Code] = '" & BIC & "' ,[Country_region Code] = 'BE' " & _
                        "where ["& Soort &" No_] = '" & rs(Soort &" No_")& "' " & _
                        "and [Code] = '" & rs("Code")& "'"
                       
                WScript.Echo sql
                Cn.execute(sql)
                End if
           
                WScript.Echo
               
                'WScript.Echo HTTP.getAllResponseHeaders
                'WScript.Echo = "OK"
            Else
                WScript.Echo "FOUT"
                WScript.Echo HTTP.Statustext
                'WScript.Echo "An error (" & LTrim(Str(oHttp.status)) & ") has occurred connecting to the server."
            End If

           
            Rs.movenext
        Loop
        HTTP = nothing

    Else
        WScript.Echo "Geen Foute IBAN - BIC gevonden"
    end if

    rs.Close
Set rs = Nothing

Set objMail = Nothing 

End Function