magento report_event table analysis

magento

Solution

TL;DR:

A `report_event.subject_id == 0` usually is the result of either an ignored user agent, or an explicitely excluded route triggering an event which normally would have been logged by Magento.

See `app/code/core/Mage/Log/etc/config.xml`:

<config>
    <global>
        <!-- : -->
        <ignoredModules>
            <entities>
                <install/>
                <adminhtml/>
                <admin/>
            </entities>
        </ignoredModules>
        <ignore_user_agents>
            <google1>Googlebot/1.0 (googlebot@googlebot.com http://googlebot.com/)</google1>
            <google2>Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)</google2>
            <google3>Googlebot/2.1 (+http://www.googlebot.com/bot.html)</google3>
        </ignore_user_agents>
        <!-- : -->
    </global>
</config>

Long answer

To explain why this happens, I need to go into greater detail.

When any Magento controller action is about to be dispatched, a `controller_action_predispatch` event will be triggered, right before the dispatching of the action actually happens.

Having another look at `app/code/core/Mage/Log/etc/config.xml`:

<config>
    <frontend>
        <events>
            <controller_action_predispatch>
                <observers>
                    <log>
                        <class>log/visitor</class>
                        <method>initByRequest</method>
                    </log>
                </observers>
            </controller_action_predispatch>
            <!-- : -->
        </events>
    </frontend>
</config>

shows, that the `Mage_Log` module defines an observer for the `controller_action_predispatch` event, represented by the method `Mage_Log_Model_Visitor::initByRequest()`:

public function initByRequest($observer)
{
    if ($this->_skipRequestLogging || $this->isModuleIgnored($observer)) {
        return $this;
    }

    $this->setData($this->_getSession()->getVisitorData());
    $this->initServerData();

    if (!$this->getId()) {
        $this->setFirstVisitAt(now());
        $this->setIsNewVisitor(true);
        $this->save();
    }
    return $this;
}

The property `$this->_skipRequestLogging` will be `true`, when the user agent sending the current request matches one of the `<ignored_user_agents />` (GoogleBot).

The method `$this->isModuleIgnored()` returns only `true`, if the request route matches one of the `<ignoredModules />` (that is `install/`, `adminhtml/` or `admin/`; in EE also `api/`).

The point is, if one of these both returns `true`, then the observer immediately exits, that is, the visitor will not be created/saved at all.

Now, to further explain using your `catalog_product_view` case as example, have a look at another config file, `app/code/core/Mage/Reports/etc/config.xml` this time:

<config>
    <frontend>
        <events>
            <catalog_controller_product_view>
                <observers>
                    <reports>
                        <class>reports/event_observer</class>
                        <method>catalogProductView</method>
                    </reports>
                </observers>
            </catalog_controller_product_view>
            <!-- : -->
        </events>
    </frontend>
</config>

which defines an observer for `catalog_controller_product_view` events, represented by the `Mage_Reports_Model_Event_Observer::catalogProductView()` method:

public function catalogProductView(Varien_Event_Observer $observer)
{
    $productId = $observer->getEvent()->getProduct()->getId();

    Mage::getModel('reports/product_index_viewed')
        ->setProductId($productId)
        ->save()
        ->calculate();

    return $this->_event(Mage_Reports_Model_Event::EVENT_PRODUCT_VIEW, $productId);
}

The last line of this observer calls the `_event()` method:

protected function _event($eventTypeId, $objectId, $subjectId = null, $subtype = 0)
{
    if (is_null($subjectId)) {
        if (Mage::getSingleton('customer/session')->isLoggedIn()) {
            $customer = Mage::getSingleton('customer/session')->getCustomer();
            $subjectId = $customer->getId();
        }
        else {
            $subjectId = Mage::getSingleton('log/visitor')->getId();
            $subtype = 1;
        }
    }

    $eventModel = Mage::getModel('reports/event');
    $storeId    = Mage::app()->getStore()->getId();
    $eventModel
        ->setEventTypeId($eventTypeId)
        ->setObjectId($objectId)
        ->setSubjectId($subjectId)
        ->setSubtype($subtype)
        ->setStoreId($storeId);
    $eventModel->save();

    return $this;
}

Have a look at the `else` block, especially this line:

$subjectId = Mage::getSingleton('log/visitor')->getId();

When a visitor doesn't gets created/saved at all, because of an ignored user agent, or an excluded route, then `$subjectId` will be `null`.

Since the table column `subject_id` is defined as NOT NULL (see `app/code/core/Mage/Reports/sql/reports_setup` SQL scripts), `0` will finally be saved.

Thats because of the Data Type Default Values of MySQL.

Problem

I have a requirement to create a number of custom reports on the magento data. The first job I have to do is to improve on the ‘Products Viewed’ report integrated into Magento. I have managed to work out that the data for this report is taken from the ‘report_event’ table, however there are some peculiarities with this data I need to understand. Firstly, I assume that where event_type_id is 1 (i.e. ‘catalog_product_view’ according to the reference table), the columns object_id and subject_id store the ID of the product and the ID of the visitor respectively. However, there are a large number of entries where subject_id is zero, but object_id holds the id of a valid product. What I need to know is how this can occur… i.e. how can we get an entry in the report_event table where the following are all true: event_type_id = 1 object_id = [valid product id] subject_id = 0 Many thanks in anticipation Si

Original source