Help needed for Mautic tracking code

[hubert]

Hello,

I'm in the process of implementing Mautic which is a PHPList on steroids for sending emails. Mautic has a tracking code similar to Google Analytics or Piwik. Both have pulgins for ZC.
The Mautic tracking code has two cases, one if the user is not logged and one when he's logged in :

Guest tracking code :

<script>
    (function(w,d,t,u,n,a,m){w['MauticTrackingObject']=n;
        w[n]=w[n]||function(){(w[n].q=w[n].q||[]).push(arguments)},a=d.createElement(t),
        m=d.getElementsByTagName(t)[0];a.async=1;a.src=u;m.parentNode.insertBefore(a,m)
    })(window,document,'script','http(s)://votreinstancemautic.com/mtc.js','mt');

    mt('send', 'pageview');
</script>

Logged in user tracking code :

<script>
    (function(w,d,t,u,n,a,m){w['MauticTrackingObject']=n;
        w[n]=w[n]||function(){(w[n].q=w[n].q||[]).push(arguments)},a=d.createElement(t),
        m=d.getElementsByTagName(t)[0];a.async=1;a.src=u;m.parentNode.insertBefore(a,m)
    })(window,document,'script','http(s)://votreinstancemautic.com/mtc.js','mt');

   mt('send', 'pageview', {email: 'jean.dupont######################', firstname: 'Jean'});

</script>

Can someone point me in the right direction on how to know if the user is logged in or not and how to extract user firstname, lastname and email address.

The most dirty manner seems to me to insert the code in footer but would'nt it be cleaner to mimic the Google Analytics code (https://www.zen-cart.com/downloads.php?do=file&id=1997) ? If so that's beyond my skills ?

A bit of help would be appreciated

Thanks for your help

Regards

[DrByte]

Probably this:
/includes/templates/YOUR_TEMPLATE_DIRECTORY/jscript/jscript_mautic.php

<script>
    (function(w,d,t,u,n,a,m){w['MauticTrackingObject']=n;
        w[n]=w[n]||function(){(w[n].q=w[n].q||[]).push(arguments)},a=d.createElement(t),
        m=d.getElementsByTagName(t)[0];a.async=1;a.src=u;m.parentNode.insertBefore(a,m)
    })(window,document,'script','http(s)://votreinstancemautic.com/mtc.js','mt');

   mt('send', 'pageview' 
<?php 
if (!empty($_SESSION['customers_email_address)) {
  echo ", {email: '" . $_SESSION['customers_email_address'] . "', firstname: '" . $_SESSION['customer_first_name'] . "'}";
}
?>);
</script>

[mc12345678]

Probably this:
/includes/templates/YOUR_TEMPLATE_DIRECTORY/jscript/jscript_mautic.php

<script>
    (function(w,d,t,u,n,a,m){w['MauticTrackingObject']=n;
        w[n]=w[n]||function(){(w[n].q=w[n].q||[]).push(arguments)},a=d.createElement(t),
        m=d.getElementsByTagName(t)[0];a.async=1;a.src=u;m.parentNode.insertBefore(a,m)
    })(window,document,'script','http(s)://votreinstancemautic.com/mtc.js','mt');

   mt('send', 'pageview' 
<?php 
if (!empty($_SESSION['customers_email_address)) {
  echo ", {email: '" . $_SESSION['customers_email_address'] . "', firstname: '" . $_SESSION['customer_first_name'] . "'}";
}
?>);
</script>

I haven't looked at the session assignments, but shouldn't they possibly be treated in some way in this display section so that there isn't some sort of extra single quote incorporated in the output?

[DrByte]

If your target audience is highly likely to have apostrophes in their first-names, then wrap it in add_slashes().

[lat9]

If your target audience is highly likely to have apostrophes in their first-names, then wrap it in add_slashes().

Noting that the same is true for the last-name and email-address.

[hubert]

Thanks for your help, I succeeded in implementing the tracking code. For the record, Mautic needs the fields which will be feeded by the code setup as 'Publicly updatable'.

Here what I did :

<script>    (function(w,d,t,u,n,a,m){w['MauticTrackingObject']=n;
        w[n]=w[n]||function(){(w[n].q=w[n].q||[]).push(arguments)},a=d.createElement(t),
        m=d.getElementsByTagName(t)[0];a.async=1;a.src=u;m.parentNode.insertBefore(a,m)
    })(window,document,'script','https://MAUTIC_INSTANCE_DOMAIN/mtc.js','mt');


   mt('send', 'pageview'<?php //do not put new line here befor php
    // If customer is logged in get infos from database
    if ($_SESSION['customer_id'] ){
        $MAUTIC_query = "SELECT
        customers.customers_id, 
        customers.customers_firstname, 
        customers.customers_lastname, 
        customers.customers_email_address, 
        customers_info.customers_info_date_of_last_logon, 
        customers_info.customers_info_number_of_logons, 
        customers_info.customers_info_date_account_last_modified, 
        customers_info.customers_info_date_account_created, 
        orders.order_total, 
        SUM(orders.order_total) AS zc_orders_tot, 
        SUBSTRING_INDEX(orders.ip_address,' - ',1) AS ip_address1, 
        SUBSTRING_INDEX(orders.ip_address,' - ',-1) AS ip_address2, 
        reward_customer_points.reward_points, 
        reward_customer_points.pending_points,
        customers.customers_newsletter, 
        customers.customers_dob, 
        customers.customers_gender, 
        customers.customers_email_format
    FROM
        customers
        INNER JOIN
        customers_info
        ON 
            customers.customers_id = customers_info.customers_info_id
        INNER JOIN
        orders
        ON 
            customers.customers_id = orders.customers_id
        INNER JOIN
        reward_customer_points
        ON 
            customers.customers_id = reward_customer_points.customers_id
    WHERE
        customers.customers_id = " . $_SESSION['customer_id'] . "
    GROUP BY
        customers_info.customers_info_id
    ORDER BY
        reward_customer_points.reward_points DESC limit 1";
    
    $MAUTIC_answer = $db->Execute($MAUTIC_query);
    
    $MAUTIC_cust_id = $MAUTIC_answer->fields['customers_id']; // zc_cust_id
    $MAUTIC_firstname = $MAUTIC_answer->fields['customers_firstname']; // firstname
    $MAUTIC_lastname = $MAUTIC_answer->fields['customers_lastname']; // lastname
    $MAUTIC_email = $MAUTIC_answer->fields['customers_email_address']; //email
    $MAUTIC_last_logon = $MAUTIC_answer->fields['customers_info_date_of_last_logon']; // zc_last_logon
    $MAUTIC_number_logons = $MAUTIC_answer->fields['customers_info_number_of_logons']; // zc_number_of_logons
    $MAUTIC_last_modif = $MAUTIC_answer->fields['customers_info_date_account_last_modified']; // zc_date_account_last_modi
    $MAUTIC_account_created = $MAUTIC_answer->fields['customers_info_date_account_created']; // zc_date_account_created
    $MAUTIC_last_order_total = $MAUTIC_answer->fields['order_total']; // zc_last_order_total
    $MAUTIC_all_orders_total = $MAUTIC_answer->fields['zc_orders_tot']; // zc_orders_total
    $MAUTIC_ipaddress1 = $MAUTIC_answer->fields['ip_address1']; // zc_ip_address
    $MAUTIC_ipaddress2 = $MAUTIC_answer->fields['ip_address2']; // zc_ip_address1
    $MAUTIC_reward_points = $MAUTIC_answer->fields['reward_points']; // zc_reward_points
    $MAUTIC_pending_points = $MAUTIC_answer->fields['pending_points']; // zc_pending_points
    $MAUTIC_email_format = $MAUTIC_answer->fields['customers_email_format']; //  zc_email_format
    $MAUTIC_newsletter = $MAUTIC_answer->fields['customers_newsletter']; // zc_newsletter
    $MAUTIC_date_ob = $MAUTIC_answer->fields['customers_dob']; //  zc_date_ob
    $MAUTIC_gender = $MAUTIC_answer->fields['customers_gender']; // zc_gender
    
    // build JS syntax to feed Mautic
    echo ", {email: '" . $MAUTIC_email . 
         "', firstname: '" . $_SESSION['customer_first_name'] . 
         "', lastname: '" . $MAUTIC_lastname .         
         "', zc_cust_id: '" . $MAUTIC_cust_id .
         "', zc_last_logon: '" . $MAUTIC_last_logon .         
         "', zc_number_of_logons: '" . $MAUTIC_number_logons .         
         "', zc_date_account_last_modi: '" . $MAUTIC_last_modif .         
         "', zc_date_account_created: '" . $MAUTIC_account_created .         
         "', zc_last_order_total: '" . $MAUTIC_last_order_total .         
         "', zc_orders_total: '" . $MAUTIC_all_orders_total .         
         "', zc_ip_address: '" . $MAUTIC_ipaddress1 .         
         "', zc_ip_address1: '" . $MAUTIC_ipaddress2 .         
         "', zc_reward_points: '" . $MAUTIC_reward_points .         
         "', zc_pending_points: '" . $MAUTIC_pending_points .         
         "', zc_email_format: '" . $MAUTIC_email_format .         
         "', zc_newsletter: '" . $MAUTIC_newsletter .         
         "', zc_date_ob: '" . $MAUTIC_date_ob .         
         "', zc_gender: '" . $MAUTIC_gender .         
         "'}";
}
?>);
</script>

1) I know it could be simplified
2) Be carefull to the line

   mt('send', 'pageview'<?php //do not put new line here befor php

if you write it as

   mt('send', 'pageview'
<?php //do not put new line here befor php

It doesn't work
3) This code is a bit specific to our website, but I thought it could be usefull for others to let it in its complete version.

I didn't face any problem yet. Let see when a lot of website users will come and browse our website.

Thanks

P.S. : If anyone needs a mailing list software Mautic seems a bit tricky but incredibly powerfull (I'm only begining).

[mc12345678]

May not really see this one in "results", but the portion of the query addressing orders doesn't limit to the last/most recent order but instead provides a sum of all orders. This is absolutely fine if a customer only ever places one order and never comes back, but that of course is not necessarily desirable for a business. (I haven't yet identified how to modify the query to support pulling only the most recent order or if instead that request should be made in a separate query.)

I make the comment because:

"', zc_last_order_total: '" . $MAUTIC_last_order_total .         
         "', zc_orders_total: '" . $MAUTIC_all_orders_total .

appears to try to capture the last order total. Now, there may be a "unique" condition based on the overall design that causes the first returned value to be the last order, but I would recommend ensuring that you get the result desired through some sort of modification.

Otherwise, would recommend casting $_SESSION['customer_id'] to an integer in support of this query. Thus changing:

WHERE
        customers.customers_id = " . $_SESSION['customer_id'] . "

to:

WHERE
        customers.customers_id = " . (int)$_SESSION['customer_id'] . "

For more recent/stricter versions of MySQL, the field(s) identified in the GROUP BY section should be included in the SELECT portion.

There are a number of other things that could be done to improve the overall code structure (use of arrays, foreach loops, table aliasing, etc.), but operation is important first.

[hubert]

May not really see this one in "results", but the portion of the query addressing orders doesn't limit to the last/most recent order but instead provides a sum of all orders. This is absolutely fine if a customer only ever places one order and never comes back, but that of course is not necessarily desirable for a business. (I haven't yet identified how to modify the query to support pulling only the most recent order or if instead that request should be made in a separate query.)

I make the comment because:

"', zc_last_order_total: '" . $MAUTIC_last_order_total .         
         "', zc_orders_total: '" . $MAUTIC_all_orders_total .

appears to try to capture the last order total. Now, there may be a "unique" condition based on the overall design that causes the first returned value to be the last order, but I would recommend ensuring that you get the result desired through some sort of modification.

While you were writing this I was banging my head on it. I tried a lot of queries and don't succeed in getting the last order amount.
Perhaps it is in the session data, if so it could easier to pull it from session but I don't know how to get it.

Otherwise if an SQL expert could help that would save my day.

[hubert]

While you were writing this I was banging my head on it. I tried a lot of queries and don't succeed in getting the last order amount.
Perhaps it is in the session data, if so it could easier to pull it from session but I don't know how to get it.

Otherwise if an SQL expert could help that would save my day.

Would it make sens to use
$_SESSION['order_summary']['order_total'] ?

[hubert]

Would it make sens to use
$_SESSION['order_summary']['order_total'] ?

Just tried...
$_SESSION['order_summary']['order_total']
returns customer first order amount not the last... Grrrr Definetly needing SQL expert help.