Customer Search Log

[CJPinder]

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?ma...roducts_id=702

[GreggShort]

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

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();
}
?>

</div>

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

<div class="reportBox">
<div class="header"><?php echo SEARCHLOG_TOP_5; ?></div>
<?php
echo "<table width=\"100%\" border=\"0\" cellpadding=\"4\" cellspacing=\"0\">
<tr>
<td width=\"110\"><b>" . SEARCHLOG_TERM . "</b></td>
<td><b>" . SEARCHLOG_COUNT . "</b></td>
</tr>";
$color1 = "#F8E7F9";
$color2 = "#BFD8BC";
$row_count = 0;


$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>';
?>
</div>

<!- 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

[CJPinder]

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.

[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'] 

Any ideas on whats the problem?

Thanks mate!

[CJPinder]

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?

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.

[CJPinder]

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?ma...roducts_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.

[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.

Thanks for this great mod.

[CJPinder]

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

Many thanks for the feedback.

Regards,
Christian.

[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.

[HeathenMagic]

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