How To Easily Spy On Your Competitor’s Order Volume With Magento
With a little bit of detective work, Magento can give you some really useful insight into your competitor's (or anyone else) order volume. The majority of store owners don't change their default order settings and that makes it easy to get an estimate of how many orders they are doing per day. Add in some estimates on average order value (AOV) and you can even make a rough stab at their gross sales.
Here is how it works...
Magento assigns a unique order number to every new order. By default this order number is built by the following method:
{STORE ID} + {SOME NUMBER OF ZEROS} + {ORDER INCREMENT}
So the 20th order for store id 3 would look something like this: 300000020
Magento also assigns a different unique id to every order that can be seen in the url when viewing the order in the admin (or your customer account! <this is what we use to get data from our competitors)
Here is an example from one of our Magento stores, as you can see we have done 17,740 orders so far:
Their is a catch though. Magento generates a new quote during the checkout process. So if a customer does not fully complete the checkout process it will generate an order id but obviously the company never made the sale. This is where abandoned carts come from. Here is an example:
Order id: 30000001 (Entity id: 1) - Customer completed the order
Order id: 30000002 (Entity id: 2) - Customer did not complete the order
Order id: 30000003 (Entity id: 3) - Customer completed the order
In this example it would appear that we have 3 sales when in reality we only had 2. So this method does not give us an exact number of sales, but it does give us a max number of potential sales.
Here is how you can easily get some info on your competitors
- Place a small order with your competitor and create a customer account during checkout, make note of your order ID and when you view the order from My Account, make note of the order id in the URL
- Wait a week or two and place another order, again note the order ID and ID in the URL
- Subtract the second order id (the URL one) from the first and divide by the number of days you waited. That will tell you approximately how many orders per day your competitor is receiving. Take that number times estimated average order value, times 365 and you can (very) roughly estimate yearly sales.
- EXTRA CREDIT: If you want a more accurate number, do this every couple months and you can identify their sales trends and potentially estimate sales better. You can also do competitive research about their industry to determine what their AOV might be.
Real World Example
I am currently in the process of starting a new company and we will be using Magento as our shopping cart solution. Here is my live example of this research and what it told me...
Order #31521, Placed 5/26/2011
Order #31756, Placed 6/9/2011
If I subtract the two URL ids from each other (556-509) I get 47.
47 orders / 14 days = 3.35 orders per day!
I estimated this company's AOV to be around$50 so 3.35 orders per day * $50 AOV * 365 days in a year = $61,137 in sales per year!
As I mentioned before, Magento generates a new order id before the order is actually completed, so this number is probably higher than reality. I am also guessing at their AOV but this at least gives me a good estimate to start working from. This is a very new company and they are already doing around $50k/year in sales from one product so I am now more confident about my move into this market.
Now if you have been paying close attention, you will notice that my order IDs had a much larger increase than my order entity IDs (from the URL). My assumption is that they are using this handy extension (FREE): Set Start Order Number extension
Off topic a bit...
There is another extension that will do custom order numbers, if you are the type who likes spending money on overpriced extensions and ridiculous licensing structures, click here. But seriously, see if the Free one from Ashley Schroder works for you, the guy is an amazing Magento developer and is extremely helpful. Support him, <rant>don't support developers who will automatically upgrade your previous extensions when you install a new one and then inform you that you are violating their licensing structure and must purchase an upgrade which is, for some unknown reason, based on the number of products you have and now costs $399 when you initially purchased it for $59. Do you get an extra features? Nope. More support time? Nope. The only difference is the price. Seriously, it's absurd.</rant>
Back on topic...
Ok so the Order numbers for these two orders had a difference of 235, but as we can see from the entity IDs, they could not have had more than 47 orders. It's a simple explanation, 235 / 47 = 5. They are using an increment of 5 instead of 1. Their first order will be #3000001 and their next order will be #3000006 but the entity IDs can never lie.
EXTRA CREDIT
If you run a Magento store, how do you stop your competitors from using this technique to find out your sales numbers?
Unfortunately that order entity id is hard to mess with. The only solution I have come up with is to manually run a bunch of orders yourself using Check/Money Order or Purchase Order and then cancel them. If anyone has a better method please let me know. This technique works extremely well on Magento sites and is therefore hard to protect yourself against.
Update from one of our developers: a very SIMPLE way to hide the order id is to extend the sales_order extension and base64 encode the order id. i actually don't understand why magento doesn't already hide this.
Now go out and do some research on your competitors! Let me know in the comments if this helps you out.
Create Custom Category Attribute – The Unofficial Step-By-Step Guide
The Problem
Magento does not allow the creation of category attributes. For whatever reason, there is no way to do this in the Admin. Sometimes it would be nice to have the ability to display additional information on a per category basis. Or even use it for calculations or what have you.
The Solution
This simple solution will help you create an attribute for a category and display it on your category view page. Pay close attention, as your Magento setup is most likely different than what I show here. Also be sure to back up your database prior to making ANY changes.
Step 1: Creating A New Attribute
First off, we need to create a new attribute. Simply do so in the Admin under Catalog > Attributes > Manage Attributes. I'll call my attribute category_test but you can call it anything you like. Keep in mind that it must be unique and can't contain spaces or non alphanumeric characters. Set the Scope to what you need. If you have a multi-store setup and want it to be different for each store, leave it at Store View. Otherwise set it to Website or Global depending on your needs. Set Apply To to All Product Types. Specify all the Attribute Properties but set all Frontend Properties except Allow HTML Tags on Frontend to "No".
Specify at least the Admin label under the Manage Label / Options tab. This is what will be displayed as the title in the backend as well as each store view on the frontend.
Please note that you will not be able to make any changes to this attribute through the backend after you continue with step 2. Double check to make sure your settings are correct.
Save the attribute and write down the attribute_id. You can find the attribute's id by hovering over the row in the grid and reading it from the link which is displayed in the tool tip. My attribute_id is 954. (Yours will be different, of course)
Step 2: Looking Up the Correct Attribute Entity Type ID, Attribute Set ID and Attribute Group ID In the Database
First we need to open the Magento database in a database management tool, like phpMyAdmin, and open the table eav_entity_type. Search for the row containing catalog_category in the field entity_type_code and write down the entity_type_id.
SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category';
In my case it is 9. Yours will most likely be different, depending on what version of Magento you started your store with.
Now open the table eav_entity_attribute and find a row that has the entity_type_id set to the entity_type_id that we just looked up.
SELECT attribute_set_id, attribute_group_id FROM eav_entity_attribute WHERE entity_type_id = [YOUR entity_type_id here];
Write down the attribute_set_id and the attribute_group_id values. Look at several rows and make sure they contain the same attribute_group_id values, as you could have multiple category attribute groups in your setup.
My setup has three different attribute_group_id's: 7, 166 and 167. These group id's are actually the tabs the attributes appear under when you edit a category. If you have several ID's, pick the lowest attribute_group_id, as this is most likely the "General Information" tab. In my case this is 7.
Step 3: Changing the entity_type_id of the Attribute
Next open the table eav_attribute. Now that we know what entity_type_id our new attributes needs to have, we must change it to the entity_type_id we looked up above.
UPDATE eav_attribute SET entity_type_id = [YOUR entity_type_id] WHERE attribute_id = [YOUR attribute_id];
For me, the SQL statement looks like this:
UPDATE eav_attribute SET entity_type_id = 9 WHERE attribute_id = 954;
Step 4: Creating an Entry In the eav_entity_attribute Table
Now we need to create a new entry in the eav_entity_attribute table. To do so, use your database management tool's editor or use the following SQL statement:
INSERT INTO eav_entity_attribute (entity_type_id, attribute_set_id, attribute_group_id, attribute_id, sort_order) VALUES ([YOUR entity_type_id], [YOUR attribute_set_id], [YOUR attribute_group_id], [YOUR attribute_id], 0);
Use the entity_type_id, attribute_set_id, attribute_group_id and attribute_id values you looked up earlier instead of the placeholders.
For my setup, the SQL statement looks like this:
INSERT INTO eav_entity_attribute (entity_type_id, attribute_set_id, attribute_group_id, attribute_id, sort_order) VALUES (9, 12, 7, 954, 0);
Step 5: Changing the Sort Order
Because we added this attribute to the database by hand, we need to set the proper sort order so it appears in the right place on the category edit page. Go to Catalog > Manage Categories and edit any category in the Admin and you will find the newly created attribute at the very first position. You can change the sort order for your attribute by changing the sort_order field in the eav_entity_attribute table. Keep in mind that the sort_order can have missing and duplicate values.
Displaying the Attribute On the Category View Page (Optional)
Now that we have our custom category attribute, we can display it on the category view page. First we check if the theme you are using already has the file /app/design/frontend/default/YOUR THEME NAME/template/catalog/category/view.phtml. If you can't find the file you must copy it to your theme's /template/catalog/category directory from /app/design/frontend/base/default/template/catalog/category/view.phtml.
Now open the file in your editor of choice and add these lines anywhere you want to display the attribute's value:
<?php $_my_category_attribute = $_category->getData('category_test'); if($_my_category_attribute): echo $_my_category_attribute; endif; ?>
Of course you need to change 'category_test' to whatever you set your attribute code to.
As allways, refresh your cache or disable it while you make changes to ensure you are actually looking at the real thing and not some phantom.
That's it. If you have any trouble, post a comment and I'll try to help you figure it all out.
Filter Magento product collection using addAttributeToFilter() to create SQL OR(s)
/** Add attribute filter to collection
*
* If $attribute is an array will add OR condition with following format:
* array(
* array('attribute'=>'firstname', 'like'=>'test%'),
* array('attribute'=>'lastname', 'like'=>'test%'),
* )
*/
// Correct application looks like this
$_productCollection->addAttributeToFilter(
array(
array('attribute'=>'name', 'like'=>'%productname%'),
array('attribute'=>'sku', 'like'=>'%productsku%')
)
);
Remove Custom Option data from catalogsearch_fulltext table in the Magento database
When you Rebuild Search Index you may notice Custom Option data in that catalogsearch_fulltext table. This can be turned off by editing the file app/code/core/Mage/CatalogSearch/Model/Mysql4/Fulltext.php. Around line 524 comment out the following 3 lines:
if ($data = $typeInstance->getSearchableData($product)) {
$index = array_merge($index, $data);
}
Quickly & Easily Backup Your Entire Magento MySQL Database
We have used this command to successfully and quickly duplicate a Magento production site before upgrading it.
IMPORTANT:
- You must run this command via SSH as a user with permission to access your MySQL databases.
- The destination database must exist
Here is the command to run via SSH:
mysqldump -u mysqlusername --password=mysqlpassword sourcedatabase | mysql -u mysqlusername --password=mysqlpassword destinationdatabase
