View a list of transactions in Google Analytics made when served by operator using Zopim (Zendesk Live Chat)

Sometimes you want to see the actual transactions in Google Analytics made during a session where the customer was served by a live chat operator (in this case Zopim – now called Zendesk live Chat).

To create a segment for each person called “Zopim – Served by “:

  • Create a new segment
  • Set conditions to include all sessions where Event Label =

Go to the “Conversions > Ecommerce > Transactions” report

Apply the above segment.

Set the date range (eg. last 30-days).

Google analytics transactions served by operators report

Sneak Peek: Mailchimp ABANDONED_CART merge tags for custom HTML email

You can use ABANDONED_CART blocks similar to PRODUCT_RECOMMENDATIONS

For example, you can display a list of items in the cart:

*|ABANDONED_CART:[$total=3]|*
<a href="*|CART:URL|*">
<img src="*|PRODUCT:IMAGE_URL|*" />
<br />*|PRODUCT:TITLE|*
<br />*|PRODUCT:PRICE|*
</a>
*|END:ABANDONED_CART|*

Or create a button:

*|ABANDONED_CART:[$total=1]|*
<a href="*|CART:URL|*">Complete your order</a>
*|END:ABANDONED_CART|*

Add coupon code form to Magento Onepage Checkout Review Step

Sometimes you want customers to be able to apply the discount at the end of checkout. For example, when you have a mini cart that links customers straight to “checkout” without viewing the “cart” page.

1. Create a new module (with namespace Ronnyew_Checkout)

File: app/etc/modules/Ronnyew_Checkout.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Ronnyew_Checkout>
            <active>true</active>
            <codePool>local</codePool>
        </Ronnyew_Checkout>
    </modules>
</config>

2. Override the core Onepagecontroller

File: app/local/Ronneyw/Checkout/etc/config.xml

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <modules>
        <Ronnyew_Checkout>
            <version>0.2.0</version>
        </Ronnyew_Checkout>
    </modules>  

    <frontend>
        <routers>
            <checkout>
                <args>
                    <modules>
                        <Ronnyew_Checkout before="Mage_Checkout">Ronnyew_Checkout</Ronnyew_Checkout>
                    </modules>
                </args>
            </checkout>
        </routers>
    </frontend>
</config>

3. Create a new method that will process the coupon code

File: app/local/Ronneyw/Checkout/controllers/OnepageController.php

<?php

require_once 'Mage/Checkout/controllers/OnepageController.php';

class Ronnyew_Checkout_OnepageController extends Mage_Checkout_OnepageController
{
    
	public function couponAction() {
		$this->loadLayout('checkout_onepage_review');
		$this->couponCode = (string) $this->getRequest()->getParam('coupon_code');
		Mage::getSingleton('checkout/cart')->getQuote()->getShippingAddress()->setCollectShippingRates(true);
		Mage::getSingleton('checkout/cart')->getQuote()->setCouponCode(strlen($this->couponCode) ? $this->couponCode : ' ')->collectTotals()->save();
		$result['goto_section'] = 'review';
		$result['update_section'] = array( 'name' => 'review', 'html' => $this->_getReviewHtml() );
		$this->getResponse()->setBody(Mage::helper('core')->jsonEncode($result));
	}

}

4. Add this form to the “review” template

File: app/design/frontend/YOURPACKAGE/YOURTHEME/template/checkout/onepage/review/info.phtml

<?php $getCouponCode = Mage::getSingleton('checkout/cart')->getQuote()->getCouponCode(); ?>
<form id="discount-coupon-form" action="<?php echo $this->getUrl('checkout/onepage/coupon')?>" method="post">
<label for="coupon_code"> <?php echo $this->__('Enter your coupon code if you have one.') ?></label><br />
<input id="coupon_code" name="coupon_code" value="<?php echo $getCouponCode; ?>"/>
<?php  if(strlen($getCouponCode)) { ?>
<button type="button" onclick="updateCoupon(2); return false;" value="<?php echo $this->__('Remove Coupon')?>"><span><?php echo $this->__('Remove Coupon') ?></span></button>
<?php } ?>
<button type="button" onclick="updateCoupon(1); return false;" value="<?php echo $this->__('Apply Coupon')?>"><span><?php echo $this->__('Apply Coupon') ?></span></button>
</form>

5. Add this javascript to the onepage.phtml template

File: app/design/frontend/YOURPACKAGE/YOURTHEME/template/checkout/onepage.phtml

<script type="text/javascript">
//<![CDATA[
function updateCoupon(reqid) {
	var reqid;
	if(reqid == 2) {  $('coupon_code').setValue('');  }
	$('discount-coupon-form').request({
		method: 'post',
		onComplete: payment.onComplete,
		onSuccess: payment.onSave,
		onFailure: checkout.ajaxFailure.bind(checkout),
	})
}
//]]>
</script>

6. Style it and add HTML markup to suit your theme

Validate Magento form fields as you type with jQuery


jQuery('.input-text').on('blur', function(){
		Validation.validate(this);
	});
	

Magento/n98-magerun/MAMP

n98-magerun has some useful tools.

Here is how you install if for MAMP so you can use it system wide:

Find and edit your .bash_profile

# Go to your home folder e.g. /Users/[YOURNAME] 
cd ~
cat .bash_profile
vim .bash_profile # or create bash_profile if it does not already exist

Add the following to your .bash_profile

# Use MAMP version of PHP
PHP_VERSION=`ls /Applications/MAMP/bin/php/ | sort -n | tail -1`
export PATH=/Applications/MAMP/bin/php/${PHP_VERSION}/bin:$PATH

# Export MAMP MySQL executables as functions
# Makes them usable from within shell scripts (unlike an alias)
mysql() {
    /Applications/MAMP/Library/bin/mysql "$@"
}
mysqladmin() {
    /Applications/MAMP/Library/bin/mysqladmin "$@"
}
export -f mysql
export -f mysqladmin

Back in terminal, use source ~/.bash_profile after making your changes to make sure they take effect, and each time you run terminal.

Now install n98-magerun:

wget https://raw.githubusercontent.com/netz98/n98-magerun/master/n98-magerun.phar
chmod +x ./n98-magerun.phar
sudo cp ./n98-magerun.phar /usr/local/bin/

References:

Google Analytics Exclude URL Query Parameters

A list of url query parameters to exclude from Google Analytics for various e-commerce apps

Shopify

  • q
  • bis_id
  • checkout_url
  • variant
  • contact_posted
  • cache
  • preview_key

Shopify app “Back in Stock Alerts”

  • bis_id

Magento

  • siteID
  • SiteID
  • SID
  • sid
  • ___SID
  • cat
  • limit
  • sort_by

Mailchimp

  • u
  • id
  • e
  • mc_id
  • mcid
  • goal

Magento stock alerts report queries

Here are some interesting queries to obtain reports from the Magento database:

Total number registered

Last 30 days

select count(*) as totalregistered from product_alert_stock where add_date >= '2014-10-12 00:00:00' and add_date <= '2014-11-10 23:59:59';
Last year
select count(*) as totalregistered from product_alert_stock where add_date >= '2013-11-11 00:00:00' and add_date <= '2014-11-10 23:59:59';
All time
select count(*) as totalregistered from product_alert_stock;
Total number customers registered Last 30 days
select count(*) as totalcustomersregistered from product_alert_stock where add_date >= '2014-10-12 00:00:00' and add_date <= '2014-11-10 23:59:59' group by customer_id;
Last year
select count(*) as totalcustomersregistered from product_alert_stock where add_date >= '2013-11-11 00:00:00' and add_date <= '2014-11-10 23:59:59' group by customer_id;
All time
select count(*) as totalcustomersregistered from product_alert_stock group by customer_id;
Total number sent Last 30 days
select sum(send_count) as totalsent from product_alert_stock where send_date >= '2014-10-12 00:00:00' and send_date <= '2014-11-10 23:59:59' and send_count > 0;

Last year

select sum(send_count) as totalsent from product_alert_stock where send_date >= '2013-11-11 00:00:00' and send_date <= '2014-11-10 23:59:59' and send_count > 0;

All time

select sum(send_count) as totalsent from product_alert_stock where send_count > 0;

Get a list of products for which an alert has been registered but no email sent (to identify inventory shortfalls)


select a.sku
from product_alert_stock
inner join (SELECT entity_id as product_id, sku FROM catalog_product_entity) as a on product_alert_stock.product_id = a.product_id
where
send_count = 0
and add_date >= '2017-01-06 00:00:00' and add_date <= '2017-02-05 23:59:59' group by product_alert_stock.product_id;