Page 1 of 6 123 ... LastLast
Results 1 to 10 of 55
  1. #1
    Join Date
    Apr 2007
    Location
    Herts. UK
    Posts
    890
    Plugin Contributions
    4

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

  2. #2
    Join Date
    Apr 2007
    Posts
    21
    Plugin Contributions
    0

    Default Re: Customer Search Log

    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

  3. #3
    Join Date
    Apr 2007
    Location
    Herts. UK
    Posts
    890
    Plugin Contributions
    4

    Default Re: Customer Search Log

    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.

  4. #4

    Default Re: Customer Search Log

    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:

    PHP Code:
    1064 You have an error in your SQL syntaxcheck 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+1added_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!

  5. #5
    Join Date
    Apr 2007
    Location
    Herts. UK
    Posts
    890
    Plugin Contributions
    4

    Default Re: Customer Search Log

    Quote Originally Posted by Grimmy2007 View Post
    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:

    PHP Code:
    1064 You have an error in your SQL syntaxcheck 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+1added_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.

  6. #6
    Join Date
    Apr 2007
    Location
    Herts. UK
    Posts
    890
    Plugin Contributions
    4

    Default Re: Customer Search Log

    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.

  7. #7
    Join Date
    Feb 2004
    Location
    Georgia, USA
    Posts
    1,948
    Plugin Contributions
    0

    Default Re: Customer Search Log

    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.

  8. #8
    Join Date
    Apr 2007
    Location
    Herts. UK
    Posts
    890
    Plugin Contributions
    4

    Default Re: Customer Search Log

    Quote Originally Posted by BlessIsaacola View Post
    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.

  9. #9
    Join Date
    Feb 2004
    Location
    Georgia, USA
    Posts
    1,948
    Plugin Contributions
    0

    Default Re: Customer Search Log

    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.

  10. #10
    Join Date
    May 2005
    Location
    England
    Posts
    590
    Plugin Contributions
    0

    Default Re: Customer Search Log

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

 

 
Page 1 of 6 123 ... LastLast

Similar Threads

  1. v155 Customer can't log in but site creates customer
    By CandleMan in forum Basic Configuration
    Replies: 1
    Last Post: 8 Apr 2016, 03:37 PM
  2. v150 remove the customer log-in link and customer registration page
    By xr3461 in forum Built-in Shipping and Payment Modules
    Replies: 2
    Last Post: 22 Dec 2012, 04:20 AM
  3. Customize admin customer search query: search alt emails, too?
    By Matt008 in forum All Other Contributions/Addons
    Replies: 3
    Last Post: 15 Apr 2010, 01:10 AM
  4. Problem with Customer Log in/ Log out
    By tequila in forum General Questions
    Replies: 11
    Last Post: 3 Jul 2009, 02:39 AM
  5. External Log In - Log a Customer in from another system
    By designerman in forum General Questions
    Replies: 11
    Last Post: 14 Jul 2008, 02:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR