Customer Search Log

Hi,

I wanted to find out what my customers were searching for and whether or not they were finding anything. I looked at the search log module that was released some time ago but it didn't do what I needed and wasn't a very clean implementation. I've therefore written a new Search Log module, you can find it in the attached zip file. I will also upload it to the Contributions download section soon.

Each time a customer does a search on your site the search term (keywords entered), date/time of the search and the number of results returned will be recorded. To see a list of the searches performed, log into Admin and use Reports... Search Log.

Installation Instructions

1. Unpack the zip file to a temporary directory

2. Using the Admin->Tools->Install SQL Patch, run the included SQL file (search_log.sql)

The search_log.sql file has instructions to add a new table (called search_log) to your database. This table must be present before the module can run.

3. All of the files in this package are stored in the same directory structure as the default Zen Cart package. Upload the files into the appropriate directories on your server.

4. Installation complete!

Always take a backup of your site (files and database) before installing any add-ons. Always test new add-ons in your test environment and ensure you are fully familiar with them before installing them on a live site.

The mod has been designed to work with v1.3.7 (and v1.3.7.1) of Zen Cart. It may work with earlier v1.3 versions but has not been tested with them. It is unlikely to work with v1.2 or earlier versions.

If you are using the old Search Log module then you should uninstall it before installing this one (amongst other things they both use a table called search_log but the table structures are different).

This is the first release so it is possible that there are bugs in it. Please let me know what you think, if you would like to see any extra features etc.

I hope you find this mod useful.

Regards,
Christian.

*MODERATOR EDIT: Download section link: *http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=702

Wow...it so happens that I was modifying the original Search Log 0.4 the same day as you realease this and ran across your post. I like what you've done with it, so I changed over to your code...but I couldn't leave it alone with what I'd already learned :smile:

So here are my "improvements"

A.) A lighter weight method of getting the data into the table is to use the tpl_advanced_search_result_default.php file to get the search results. If you use my method, you should not use the files (delete them) in the "includes" directory of the distribution. (Basically, the class file and the auto_loader)

Edit or copy the default file to: includes/templates/[YOUR TEMPLATE NAME]\templates\tpl_advanced_search_result_default.php

Find the line around 48:

require($template->get_template_dir('tpl_modules_product_listing.php', DIR_WS_TEMPLATE, $current_page_base,'templates'). '/' . 'tpl_modules_product_listing.php');

And append this code after it

// GDS add 8-23-07 for search_log
// Modified to return number of rows returned
$keyword = (strip_tags(trim($_GET['keyword'])));
$searchsql = "insert into " .DB_PREFIX. "search_log (search_term, search_time, search_results) values ('". $keyword .
"',now(),'". $listing_split->number_of_rows ."')";
$result = $db->Execute($searchsql);
if (!$result) {
$messageStack->add_session('search', ENTRY_ADV_SEARCH_INSERTION_ERROR);
}

// End of GDS add 8-23-07 for search_log

This saves a database call.

**B.) I also modified the admin page to show the top 5 search terms. This requires adding code to admin/index.php. Find the end of the </div> around line 160.

Some code right before this point is:**

echo '              <div class="row"><span class="left">' . $counter_startdate_formatted . '</span><span class="rigth"> ' . $counter->fields['session_counter'] . ' - ' . $counter->fields['counter'] . '</span>   </div>' . "\n";
$counter->MoveNext();

}
?>

Append the following code after it:
<!- GDS Added 8-3-07 for search_log -->

 $sql = "SELECT search_term, count(*) AS Number
              FROM " .  TABLE_SEARCH_LOG . "
                   WHERE search_term is not null and search_term not like  ''
                    GROUP BY search_term
                          ORDER BY Number desc limit 0,5";

$result = mysql_query($sql);

if (!$result) {
$messageStack->add(ENTRY_SEARCHLOG_ID_NOT_FOUND, 'error');
} ###### End of if statement.

while ($row = @mysql_fetch_array($result)) {
$searchterm = (isset($row["search_term"]) && !empty($row["search_term"])) ? trim($row["search_term"]) : "";
$number = (isset($row["Number"]) && $row["Number"] != 0) ? intval(trim($row["Number"])) : 0;

/* Now we do this small line which is basically going to tell
PHP to alternate the colors between the two colors we defined above. */

$row_color = ($row_count % 2) ? trim($color1) : trim($color2);

// Echo your table row and table data that you want to be looped over and over here.

echo "<tr>
<td width=\"110\" bgcolor=\"$row_color\" nowrap>" . $searchterm . "</td>
<td bgcolor=\"".$row_color."\">" . $number . "</td>
</tr>";

// Add 1 to the row count

$row_count++;

}

// Close out your table.

echo "</table>";
echo '<a href=' . zen_href_link(FILENAME_STATS_SEARCH_LOG, '', 'NONSSL'). '>' . BOX_REPORTS_SEARCH_LOG . '</a>';
?>

<!- End of GDS Add 8-23-07 for search_log -->

Next add the following lines to admin/includes/languages/english/extra_definitions/search_log.php
**
// Added by GDS on 8/23/07 to handle Top5 info on admin page
define('SEARCHLOG_TERM', '<b>Search Term</b>');
define('SEARCHLOG_TOP_5', 'Search Log Top 5');
define('SEARCHLOG_COUNT', '<b>count</b>');
define('VIEW_SEARCH_LOG', 'View Search Log');
define('ENTRY_SEARCHLOG_ID_NOT_FOUND', 'Error: Search ID could not be found.');
define('ENTRY_SEARCHLOG_EMPTY_ROWS', 'There are currently no search logs entries in your ' . DB_PREFIX. TABLE_SEARCH_LOG . ' table. ');
**
That's all there is to it.

**Hope this helps someone,

Gregg Short
GShort.com Web Marketing and Design
**

Hi Gregg

Thanks for the suggestions. Glad you like the mod.

I looked at hooking into tpl_advanced_search_result_default.php when I put the mod together but I didn't want people having to edit files to get the mod to work. It is quite a simple mod and I wanted to keep the installation as simple as possible. I weighed up the options and thought an extra SQL query was worth it for a no-edit install.

Regards,
Christian.

Hi,

Great mod mate! Works great apart from 1 error I'm getting.

When searching with anything that includes an apostrophe in it I get the following error:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's&inc_subcat=0&sort=20a', exit_page_name='Advanced Search', num_clicks=num_click' at line 1
in:
[UPDATE zen_supertracker SET last_click = NOW(), exit_page='/index.php?main_page=advanced_search&search_in_description=1&keyword=topcat's&inc_subcat=0&sort=20a', exit_page_name='Advanced Search', num_clicks=num_clicks+1, added_cart='false', categories_viewed='a:1:{i:16;i:1;}', products_viewed='', customer_id='0', completed_purchase='false', cart_contents='', cart_total = '0', order_id = '0' WHERE tracking_id='31195']

Any ideas on whats the problem?

Thanks mate!

Grimmy2007:

Hi,

Great mod mate! Works great apart from 1 error I'm getting.

When searching with anything that includes an apostrophe in it I get the following error:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's&inc_subcat=0&sort=20a', exit_page_name='Advanced Search', num_clicks=num_click' at line 1
in:
[UPDATE zen_supertracker SET last_click = NOW(), exit_page='/index.php?main_page=advanced_search&search_in_description=1&keyword=topcat's&inc_subcat=0&sort=20a', exit_page_name='Advanced Search', num_clicks=num_clicks+1, added_cart='false', categories_viewed='a:1:{i:16;i:1;}', products_viewed='', customer_id='0', completed_purchase='false', cart_contents='', cart_total = '0', order_id = '0' WHERE tracking_id='31195']

The error says it is trying to update the zen_supertracker table which is not part of the search log mod. I'm guessing it is part of the Super Tracker mod. So it looks like the problem is with the Super Tracker mod rather than the Search Log. I think you need to report the problem in the Super Tracker support thread.

Regards,
Christian.

Just a quick note to let everyone know that v2.0 of the Search Log mod is now available for download in the contribs section...
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=702

Customer Search Log v2.0 has the following new features...

• Now fully compatible with Zen Cart v1.3.8 as well as v1.3.7
• Duplicate records no longer created in search log
• Added ability to sort columns in search log report
• Added CSV export to search log admin

I hope you find it useful in increasing sales. Please report any problems or provide feedback in this thread.

Regards,
Christian.

Is it possible to bring back the feature from version 1 where the keywords are group together. That was a nice feature to be able to see a grouping of keyword to see the top 10 searches on the site. Just displaying each search keyword per day is not as useful a tool as being able to group them and rank them by total searches as in version 1.

Thanks for this great mod.

BlessIsaacola:

Is it possible to bring back the feature from version 1 where the keywords are group together. That was a nice feature to be able to see a grouping of keyword to see the top 10 searches on the site. Just displaying each search keyword per day is not as useful a tool as being able to group them and rank them by total searches as in version 1.

Someone else added the grouping of the keywords to the report. I wasn't around much over the Xmas period so wasn't aware that someone else had uploaded a modified version. v2.0 is from my own development branch, it has just taken me awhile to upload it.

There was a bug in v1 which meant that if someone did a search that went on to multiple pages each time the user moved to the next page it record an additional search. Same if they clicked on a product in the search and then went back to the search page. This meant that you would get multiple identical entries for searches. Grouping searches was probably to work around this. Grouping to determine the top 10 would have given a false figure because of the bug.

You can export the data in v2.0 and then load it in a spreadsheet and analyze it any way you can think of.

I'll have a look at the grouping though and see if I can work something into the report that will give top 10 searches etc.

Funny thing is, there has been hardly any posts about the mod on the forum so I assumed no one was using it. Guess I just made it too easy to install :smile:

Many thanks for the feedback.

Regards,
Christian.

For now I uploaded all file from version 2.0 and replaced your admin\stats_search_log.php with the one from Paul's version 1.1.1 and that provided the report that I am looking for. I am not sure if this is the right approach but it works.

Hi installed and it is very straightforward. Very nice mod. :smile:

HeathenMagic:

Hi installed and it is very straightforward. Very nice mod. :smile:

It's absolutely fabulous mod and with the integration of added features from 1.1.1 it's even a better mod :)

BlessIsaacola:

For now I uploaded all file from version 2.0 and replaced your admin\stats_search_log.php with the one from Paul's version 1.1.1 and that provided the report that I am looking for. I am not sure if this is the right approach but it works.

I can't see any problems with doing that. Should work fine.

Regards,
Christian.

Yeah, this mod is cool...

More features would be nice...keeping a Marketing/SEO point of view in mind.

I think Google Analytics offers this type of tool for your site,
I'm sure there's some good ideas to be copped from their version.

Some people don't believe in offering Google their entire business model, advertising strategy and sales information on a silver platter...
so those people have to do without the handy little features like search stats...this Search Log mod helps overcome that problem!

Thanks man, good work!

Hello everyone,

Does anyone have Search Log v2.0 working with v1.3.8a of the Time Zone Offset mod by Gilby?
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=145

Whilst the Time Zone Offset mod offsets all other times correctly, the Search Log still shows the incorrect time.
In an unsuccessful attempt to resolve this, the following change was made, however, new searches still do not show the offset time.

\includes\classes\observers\class.search_log.php

Line 47 - Changed from:

$sql = 'insert into ' .DB_PREFIX. 'search_log (search_term, search_time, search_results) values (:searchTerm,now(),:searchResults)'; 

To:

$sql = 'insert into ' .DB_PREFIX. 'search_log (search_term, search_time, search_results) values (:searchTerm,date_add(now(), INTERVAL " . TIME_ZONE_OFFSET . " HOUR),:searchResults)'; 

Can anyone advise what other changes in addition to, or instead of the above, are required to fix this?
If relevant, Zen Cart is v1.38a, PHP is v5.2.6 and MySQL is v5.0.51a-community

Testing on an otherwise clean installed Zen Cart with correct locale setting, and only the Search Log and Time Zone Offset mods installed shows the problem remains, and is seemingly not related to any incompatibility with other mods.

Many thanks,
Edward McDougle.

You used double-quotes when inserting the constant value into the query, but the rest of the syntax is using single-quotes. Thus the name of your constant, along with its quotes, is being sent literally to the query. I'm surprised MySQL didn't croak on it.

Thanks DrByte, your suggested fix resolved that problem.

Just one more issue remains, that being the date format, which is shown in the wrong format:
For example, 2008-08-10 01:02:52, where it should be 01:02:52 10-08-2008

Elsewhere, the date format is correct as per the locale setting.

Many thanks,
Edward McDougle.

The date must be stored in the database in YYYY-MM-DD HH:MM:SS format. That's what MySQL requires.

If you're not happy with the way the search log report shows the output, you'll have to alter the code of the contribution to have it render the display to your liking. You can probably use the changes introduced by other files in your timezone-offset addon as an example for how to change other things such as your report.

Thank you for your help DrByte.

Hi,

Good job! Combining using this add-on with Google Analytics, we would get what keywords do well and what to develop!

Thanks for your job,:smile:

I have installed this to Zen 1.3.9 c, and it is not showing up under the admin>reports, how do I add this , thanks for any help