Jul

Quick Tip: Magento Catalog Price Rules Don’t Work

In this post you’ll find a list of likely reasons to why your catalog price rules do not have any effect on prices of products they are supposed to control. The problems listed below are most likely to occur without being caused by a custom module, i.e. it is assumed that catalog price rule functionality has not been changed. The reasons are not mutually exclusive and it is possible that more than one applies to your case. If you, however, find a reason not listed here, please post it in the comment section. This would be much appreciated. So, to the list:

Is the rule active?

Symptoms: none of the targeted products is affected by the rule.

Reason: Rules can be disabled by setting their property status to inactive. This can be easily checked in the back-end. To resolve this, set status to active and click the Save and Apply button.

Has the rule been applied?

Symptoms: the rule is active, but still none of the targeted products is affected by it.

Reason: Calculation of rule-adjusted prices depends on rule-product relationship data. A rule must be applied in order to generate these relationships. The table where they are stored is called catalogrule_product. The system writes data into this table in three cases:

When all active rules are applied manually to all products in the catalog rule management in the back-end. Even though rules can be applied automatically for a limited range of products (as you’ll see in the next two cases), the rule of thumb, however, is to manually apply every newly created rule (no pun intended).

After a product is saved. The catalog_product_save_after event is dispatched that triggers the Mage_CatalogRule module’s observer method applyAllRulesOnProduct, which goes through all active rules and generates their relationships with the given product:

public function applyAllRulesOnProduct($observer)
{
    $product = $observer->getEvent()->getProduct();
    if ($product->getIsMassupdate()) {
        return;
    }

    $productWebsiteIds = $product->getWebsiteIds();

    $rules = Mage::getModel('catalogrule/rule')->getCollection()
        ->addFieldToFilter('is_active', 1);

    foreach ($rules as $rule) {
        $websiteIds = array_intersect($productWebsiteIds, $rule->getWebsiteIds());
        $rule->applyToProduct($product, $websiteIds);
    }

    return $this;
}

Listing 1. Applying rules to a product after saving the product model, /app/code/core/Mage/CatalogRule/Model/Observer.php, line 46.

In the rules’ loop the call to rule model’s method applyToProduct writes data into the catalogrule_product table.

After products are imported via the Mage_ImportExport module. In this case the system dispatches the catalog_product_import_finish_before event, which triggers the catalog rule observer’s createCatalogRulesRelations method. This method also goes through all the active rules and applies them. The difference is that it is done not to a single product but to a list of the imported ones, which is passed on by the event.

   public function createCatalogRulesRelations(Varien_Event_Observer $observer)
{
    $adapter = $observer->getEvent()->getAdapter();
    $affectedEntityIds = $adapter->getAffectedEntityIds();

    if (empty($affectedEntityIds)) {
        return;
    }

    $rules = Mage::getModel('catalogrule/rule')->getCollection()
        ->addFieldToFilter('is_active', 1);

    foreach ($rules as $rule) {
        $rule->setProductsFilter($affectedEntityIds);
        Mage::getResourceSingleton('catalogrule/rule')->updateRuleProductData($rule);
    }
}

Listing 2. Generating product-rule relationships after a product import. /app/code/core/Mage/CatalogRule/Model/Observer.php. Line 385.

You can quickly check if your catalog rule has any products assigned by running this SQL query (supply your rule ID):

SELECT
        product_id
FROM
        catalogrule_product
WHERE
        rule_id = 1

Listing 3. Checking rule-product relationships.

Is the rule active on this day?

Symptoms: the rule used to work fine once, but all of a sudden stopped working.

Reasons: it is possible to limit a rule to a date range, which is achieved by setting values to “From Date” (rule_from_date) and “To Date” (rule_to_date) properties. If no value is set, the rule can have effect on any day. You should, however, check if there is such date limit. Look at the rule’s settings in the back-end or run a SQL query in the database. The SQL from the Listing 3 is extended in the following way:

SELECT
        *
FROM
        catalogrule_product
WHERE
        (from_time = 0
                OR from_time <= unix_timestamp())
                AND (to_time = 0
                OR to_time >= unix_timestamp())
                AND rule_id = 1

Listing 4. Checking the date range applicability of a rule with ID = 1.

Note that the data in columns catalogrule_product.from_time and catalogrule_product.to_time are timestamps. The function unix_timestamp() returns the current timestamp.

Does the rule you created actually include the targeted products?

Symptoms: price is not changed by one or more of the products supposed to be affected by the rule.

Reasons: sometimes the problem is embarrassingly simple. The conditions set for the rule simply do not include the desired products. Do check them again.

Unfortunately there is no simple SQL to validate rule conditions. This is because each condition of the rule is evaluated in a complex procedure implemented in the Mage_Rule module. This module’s class Mage_Rule_Model_Condition_Product_Abstract represents a condition object and uses its method validate() to check if a product matches the current condition.

Are there any rule-adjusted prices in the price index?

Symptoms: despite all the settings done right, rule-product relationships present, and date range conditions met, prices are still the same.

Reasons: system fetches product prices from price index table catalog_product_index_price. These prices are supposed to be updated by the Mage_CatalogRule module. This happens when the prepare_catalog_product_price_index_table event is dispatched during the product price indexing. The catalog rule observer responds to it by collecting prices calculated by every rule for every affected product and by replacing the regular prices with adjusted ones. Important is that rule prices must be calculated for the current day and before the catalog price re-indexing starts. Otherwise the observer will not get any active rule prices to augment the price index. In a standard setting this is achieved by scheduling the rule price calculation cronjob one hour before the catalog product price indexing. This schedule is set in the respective config.xml files:

<jobs>
    <catalogrule_apply_all>
        <schedule>
            <cron_expr>0 1 * * *</cron_expr>
        </schedule>
        <run>
            <model>catalogrule/observer::dailyCatalogUpdate</model>
        </run>
    </catalogrule_apply_all>
</jobs>

Listing 5. Daily catalog rule price update cronjob: 1AM. /app/code/core/Mage/CatalogRule/etc/config.xml. Line 197.

<jobs>
    <catalog_product_index_price_reindex_all>
        <schedule>
            <cron_expr>0 2 * * *</cron_expr>
        </schedule>
        <run>
            <model>catalog/observer::reindexProductPrices</model>
        </run>
    </catalog_product_index_price_reindex_all>
</jobs>

Listing 6. Daily catalog product price indexing update cronjob: 2AM. /app/code/core/Mage/Catalog/etc/config.xml. Line 816.

Final advice: rule product price table

Check out the catalogrule_product_price table. It contains rule prices for affected products as well as date ranges for which these prices are valid. So if you have a product ID whose price is not adjusted despite having a rule for it run the following query to find out if there actually is a rule price for this product:

SELECT
        *
FROM
        catalogrule_product_price
WHERE
        product_id = 'YOUR_PRODUCT_ID' AND website_id = 1

Listing 7. Checking the catalogrule_product_price table.

Note the website_id parameter. If you run a multi-website environment – do supply a respective website ID. Value 1 stands for Main website and can be used for any single website installation (to make sure that 1 is indeed Main website look into table core_website). A sample output from such query (assuming there is a price for the product) looks like this:

rule_product_price_id	rule_date	customer_group_id	product_id	rule_price	website_id	latest_start_date	earliest_end_date
5105	2013-07-01	0	29	15.9900	1	2013-07-01	2013-08-01
5106	2013-07-02	0	29	15.9900	1	2013-07-01	2013-08-01

Table 1. Sample entries in table catalogrule_product_price.

What to check here?

rule_date: if there current date is not present, no price has been calculated for this rule on this day. Check the rule’s date range and if it is correct, apply the rule manually. Re-index the catalog_product_price index.
customer_group_id: make sure that prices are available for the customers groups you selected in the rule settings in the back-end.
rule_price: check if the price value is the same as expected.
latest_start_date, earliest_end_date: if these columns are null, then the rule is not limited by a date range. Otherwise, latest_start_date must be earlier or the same as the current date and earliest_end_date must be later or the same as the current day.

If the query produces no result for the product, check the rule’s settings and apply it again. If after that a valid entry appears there, re-index the catalog_product_price index and check the product price in the front-end.

Hope these tips will help you resolve your issues with catalog price rules. For their detailed description please read my earlier post Magento Catalog Price Rules.

29 thoughts on “Quick Tip: Magento Catalog Price Rules Don’t Work”

Michela on July 6, 2013 at 4:26 pm said:

Hi. Thanks a lot for your article. In my site I have a strange issue. Have a catalogue rule: “set product as new from date” must be equal or lower than 30 april 2013 to apply a discount.
Noticed that it doesn’t work on Magento. The system applies a discount to any product showing a date in this field even if it is prior to april 30, 2013. This dies not affect products with this field empty.
No way to make it work.
Ciao.
Michela . Rome. Italy

Reply ↓
- Michela on July 6, 2013 at 4:46 pm said:
  
  Better explained, the system should not apply a discount to products where the field “set product as new from date” is higher than 30 april. Instead it does’nt apply the discount even to those products that show a date prior to 30 april. It works fine with products that have no date in this field.
  Sorry for messing it up a bit.
  Michela
  
  Reply ↓
  - Oleg Ishenko on July 7, 2013 at 7:19 pm said:
    
    Hi Michela,
    
    I tried to reproduce your situation: creating a rule with condition “Set Product as New from Date equals or greater than 2013-04-30” and having products with the attribute “news_from_date” with values both before and after that date as well as without any value at all. I did not succeed.
    
    The problem in my case was that the rule did not have any matched product at all. A query to table catalogrule_product with my rule’s Id returned nothing. I went to debugging and saw a peculiar thing. While the date in the rule was saved as a timestamp, i.e. 1367272800, the code that tried to match it to my products used string values (‘2013-04-30 00:00:00′) for the products’ news_from_date attribute. Matching failed.
    
    I can’t tell if that is Magento’s native problem, or if it was I doing something wrong. Could you check your table catalogrule_product if it has any matches for your products at all?
    
    I was testing on Magento ver. 1.7.0.0, what is yours?
    
    Reply ↓
Josh Ribakoff on August 23, 2013 at 1:18 am said:

Magento replaces (deletes & re-inserts) these rows with a cron. This gives new IDs. Eventually they hit the maximum size allowed for a mysql integer & it stops working.

Reply ↓
- Oleg Ishenko on August 26, 2013 at 7:27 pm said:
  
  Thank you, Josh, for sharing this tip. Fortunately I haven’t had this problem before, but I’ll be looking out for it now.
  
  Reply ↓
Ben on September 13, 2013 at 11:29 pm said:

I have a little different issue. The rules apply when I have only one product in the cart and not when i have multiple products with different rules for each.

The sub totals and totals don’t get calculated correctly when there are more than one products in the cart.

Does anybody have a clue.

Reply ↓
Miguel on January 21, 2014 at 12:59 am said:

My situation is slightly different, I can see the rules applied on the catalog but on the Shopping Cart page the discounts are not longer applied. Any ideas?
Thanks

Reply ↓
- romain on April 30, 2014 at 10:38 am said:
  
  I had the same problem. The answer is right here : http://stackoverflow.com/questions/7908513/magento-shopping-cart-price-rule-gets-applied-but-discount-not-subtracted-from
  
  For short :
  – Log in to Magento admin area
  – Navigate to Catalog > Attributes > Manage Attributes
  – Locate the Atrtibute Code “url_key” and click to edit it
  – Change “Use for Promo Rule Conditions” to Yes
  – Save the changes
  – you may need to reindex the Product Flat Data index at this point
  
  Works fine with Magento 1.7
  
  Reply ↓
  - tony g on June 10, 2015 at 1:43 pm said:
    
    Many thanks, I was looking all over after trying for hours to get the promo discounts working and your response fixed it in version 1.9.0.1 CE.
    
    Thanks again
    
    Tony
    
    Reply ↓
- Yair on November 14, 2014 at 9:58 am said:
  
  I have a similar problem, but on the contrary case: I can see discount applied in cart and checkout but not in catalog
  
  Reply ↓
Flemming on February 13, 2014 at 11:59 am said:

Really REALLY helpful info, especially the SQL queries which gave me a clue as to what was going wrong and now my catalog price rules are working! Thanks so much for posting this excellent info!

Reply ↓
- Oleg Ishenko on February 13, 2014 at 12:37 pm said:
  
  Hi Flemming,
  
  I’m glad this post was helpful for you. Thank you for your comment!
  
  Reply ↓
Miguel Tristao on May 5, 2014 at 10:32 am said:

My rules do stop working between midnight and 1 am..

I have tried to “narrow the gap” by editing both the config.xml files

5 0 * * *

and

10 0 * * *

but i only got the rules to stop working completely…
now, they only restart by manually apply all rules inside promotions>catalog price rules

what may i be missing here?
i’m using CE 1.81

Thanks

Reply ↓
- Miguel Tristao on May 16, 2014 at 9:41 am said:
  
  UPDATE:
  I figure out why it was not working, hope this comment helps someone else out there 🙂
  The Shop i´m working on is in Portugal (GMT), and i say this because we have a summer time and a winter time (like the UK BST), this makes our timezone GMT = UTC in winter time and in summer time we get UTC+1
  
  When i found out i could change the timings of the catalogrule_apply_all in here and i did so, i changed the rule application from 1:00 am to 0:05 am, in reality what i was telling the database was to run this job at 23:05 because magento does not consider this time change (for magento UTC always = GMT) (this problem does not happen in winter time because then GMT = UTC)
  
  The main problem is that catalog price rule are created daily, and expire at midnight, and when i just changed the cron job (to 5 0 * * *) what i was really telling the database was to refresh the rule at 23:05 so it stopped my catalog rules from working at 0:00, and instead of just one hour gap i got it to stop working completely.
  
  I got this solved following this tutorial http://husseycoding.blogspot.pt/2011/10/magento-working-with-bst.html
  
  Hope this big text helps someone else out there, from me a BIG THANK YOU to Solving Magento and all the other wonderful people that share info like you do.
  
  Cheers
  
  Reply ↓
  - Oleg Ishenko on May 16, 2014 at 9:45 am said:
    
    Hi Miguel,
    
    I’m happy you’ve figured out your problem; sorry I wasn’t much of assistance there.
    
    Thank you for sharing your solution here! I hope, it will help others who have the same difficulty you had.
    
    Reply ↓
  - Louis Bataillard on August 15, 2014 at 9:38 am said:
    
    I think I have the same problem here. Due to our local time zone, the “catalogrule_apply_all” job is executed at 23:00 (11pm), while the “catalog_product_index_price_reindex_all” job is executed at midnight, one hour later but already the next day… So most likely the “catalog_product_index_price_reindex_all” doesn’t see any valid catalog rules for that day, since “catalogrule_apply_all” created them with the date from one day earlier?
    
    I have updated the cron schedule accordingly and will update this comment if it works, but thanks in advanced for the solution to this very tricky issue. 🙂
    
    Reply ↓
    - Louis Bataillard on August 19, 2014 at 1:04 pm said:
      
      Update: Yes, that fixed it. Thanks so much for posting your solution here because otherwise I probably would’ve had to do a lot more debugging before finding it – awesome!
      
      Reply ↓
zouc on May 29, 2014 at 10:34 am said:

I have a problem with a magento website and the catalog rules.
The re index product price apparently works only with new products OR old product that have been changed (title, description etc…).
I checked the catalogrule_product_price table and yes only that kind of a product is indexed.
Is anyone here as an idea about how to solve it please ?

Reply ↓
John on October 7, 2014 at 7:13 am said:

I am using the catalog rules based

1. User must be logged in (students of approved coaches).
2. Rules applied based on product manufacturing type.

The rules works fine for some time after click on ‘Save and Apply’ rules in back-end , but after some times it does not works, no discount shows to the users.

Reply ↓
John on October 23, 2014 at 7:21 pm said:

Hi

I am experiencing an issue with a clients site where once a discount rule is applied on checkout the VAT is still calculated on the full price as below

Subtotal £20.00
Discount -£2.00
Shipping & Handling (Shipping – Standard UK Delivery) £0.00
Grand Total Excl. Tax £14.67
Tax £3.33
Grand Total Incl. Tax £18.00

All sales process normally and have tried just about every setting in the tax configuration… just wondering if anyone has experienced this?

Reply ↓
Nathan Green on February 19, 2015 at 12:25 am said:

Hi Oleg, I just wanted to reach out and say that this page is a fantastic resource.

After reading and re-reading your post I was able to determine that my catalog rules issues were at the catalogrules_product_price level. Magento wasn’t creating the rules for the current day in our timezone.

I’ll preface the next bit here by saying we already had our timezone in Magento settings set to E. Australian Standard Time.

So our issue was due to Magento being UTC-based in its core coding and our store being in Australia. That means we’re 10 hours ahead of UTC, thus between midnight and 10AM AEST our rules weren’t set for the current day.

So if we tried to manually apply rules before 10AM Magento would apply them for the “day before” because it runs its DB entires on UTC time.

Anyway, the fix in the end for us was to set our GLOBAL timezone scope in Settings > Configuration > General > Locale > Timezone to E. Australian Standard Time and then set our WEBSITE SCOPE timezone to be GMT Standard Time, which is equal to UTC.

So setting the GLOBAL to AEST means all of our orders are logged in Magento admin at the correct time & date for us. Our cron jobs are still firing at the correct times, etc. It is running at the correct time for us: GMT+10.

And setting the WEBSITES to use GMT is tricking the website scopes to thinking they are on UTC time (same as Magento). So, for example, even though Magento has only calculated rules for 2015-02-18, and its 2015-02-19 in AEST, the website is running on 2015-02-18 and therefore the rules still apply.

It’s a bit hacky, and won’t be an ideal solution for everyone, but it works for us.

The only pitfall of this approach is if you have a catalog rule that is meant to finish at midnight AEST it will actually keep running until 10AM AEST that day unless you manually deactivate it. But for us that’s a better outcome than the alternative.

Thanks again and I hope this can help someone as your post has helped me.

Nathan

Reply ↓
- Oleg Ishenko on February 19, 2015 at 11:11 am said:
  
  Hi Nathan,
  
  thanks for sharing this!
  
  Reply ↓
Teresa on October 12, 2015 at 9:24 pm said:

Hi
I have read your article with interest, my problem is the add new rule button. Once pressed this is what it shows. Here is a link http://i.stack.imgur.com/UWJLQ.jpg

Would you be able to suggest a place to find the correct files associated with promotion/new rules, or any ideas. Where to look.

Reply ↓
Tariq on October 28, 2015 at 7:11 am said:

Hi,
I really have a wired and i followed all the tips here in page, I can see the rule in database, product price are there, total 60 product only 2 product got updated rule price rest are not appearing with discounted amount.
Further, when I apply rule after spinning for a while it gives error. Run the script to apply rule manually but no luck so far.
any clue?

Reply ↓
Inge Kruikmans on January 12, 2016 at 2:52 pm said:

We simply did not use “is one of” as a rule for several sku’s but just “is”… Arrrgh stupid human me…

Reply ↓
Nishant on February 4, 2016 at 11:18 am said:

SELECT
product_id
FROM
catalogrule_product
WHERE
rule_id = 1 Gives Empty Result Set

Reply ↓
simon on May 3, 2016 at 9:42 pm said:

Hi i wonder if you could help my issue. I have a price £39.99 and a special price of £23.99 and a group price of £24.99

my issue is that i want to charge the group price to members of the group and the special price to anyone else but because the special price is lower it defaults to this for the group price. is there a way we can charge the group price to members of the group irrespective of the price being the cheapest or not?

Reply ↓
giorg on May 31, 2017 at 10:53 am said:

hi,
maybe you can help me. I have a sitge I am developing which behaves weird: I have a very simple rule to test, if product belongs to a certain category apply 50% discount. In fronted, I see the right price only in cart, mini-cart and checakout pages. Category view and product detail view show full price. I’ve checked all the tables u mentioned, and it seems all good.

Any other hint?

Thanks a lot

Reply ↓
Kaia on November 30, 2018 at 8:13 am said:

Thank YOU SO much!
it gave lots of stuff to check on and try out.
Allthought I didn’t get it working. Not sure why, is is the connection with directo or sumething, bu at least I had some kind on a chance to do it.

Reply ↓