Improved keyword searching in NopCommerce

27. October 2014 14:15 by John Dillon | 0 Comments

27. October 2014 14:15 by John Dillon, 0 Comments

Keyword searching in NopCommerce by default didn’t seem to work as well as we would have expected. Our main issues were:

  1. It doesn’t handle plurals (e.g. searching for "dinosaurs" doesn’t find anything with "dinosaur" in it)
  2. Results aren’t ranked in order of relevance

We’ve made some relatively simple modifications which in our opinion make it work much more effectively. All the necessary code is in the stored procedure ProductLoadAllPaged.

1. Ranking results

SQL fulltextsearch has a Rank feature which creates a score for a match, enabling you to order results by this value. The higher the rank the more relevant it is (in theory). Its a complicated algorithm which I wont... OK cant explain, but the point is it seems to work pretty well.

In order for this to work you need to use CONTAINSTABLE rather than CONTAINS as NopCommerce does by default. I expect the reason it uses CONTAINS is that CONTAINSTABLE can only be used on single tables, you cant as easily join queries to search on multiple tables. Because NopCommerce is multi lingual, it needs to also search on the LocalizedProperty table for multi language variations. We don’t need this in our current setup so for us it was simple enough, just remove this lookup. For those of you who do need multiple joins, this is possible using a schema bound indexed view with a fulltextindex created on that. This can be a bit fiddly to setup, and its slightly annoying moving forward because if you ever make any schema changes to the tables involved you need to recreate the index each time.... however it does work.

All you then need to do is add in the fields you want to search on and it should work just fine.

One other point on our code is you'll notice we've not included the default NOP logic to build the fields to search on based on variables like "IF @SearchDescriptions = 1". This is simply because we don’t need this functionality to be CMS driven, we're happy to hard code this. It should be easy enough to modify the code should you need this.

2. Plurals

In order to handle plurals you need to use for example FORMSOF (INFLECTIONAL, "dinosaurs").

One additional requirement we had was for it to also match on partial strings, so for example if I enter "dino" it needs to find "dinosaurs". This is because we use an auto complete search in the header.
What this meant was we needed to build up 2 separate strings to search on, and use an OR operator to split these:
i.e.
1. FORMSOF (INFLECTIONAL, action) AND FORMSOF (INFLECTIONAL, dino)
2. "dino*" AND "action*"
Resulting in (FORMSOF (INFLECTIONAL, action) AND FORMSOF (INFLECTIONAL, dino)) OR ("dino*" AND "action*")

Because we use the rank feature to order results it naturally means the more relevant results come first.

You can see the resulting code in action here: http://www.mulberrybush.co.uk. The client certainly considered it to be an improvement.

I’ve included the code below in case it’s of use to anyone else.



ALTER PROCEDURE [dbo].[ProductLoadAllPaged]
(
    @CategoryIds        nvarchar(MAX) = null,    --a list of category IDs (comma-separated list). e.g. 1,2,3
    @ManufacturerId        int = 0,
    @StoreId            int = 0,
    @VendorId            int = 0,
    @WarehouseId        int = 0,
    @ParentGroupedProductId    int = 0,
    @ProductTypeId        int = null, --product type identifier, null - load all products
    @VisibleIndividuallyOnly bit = 0,     --0 - load all products , 1 - "visible indivially" only
    @ProductTagId        int = 0,
    @FeaturedProducts    bit = null,    --0 featured only , 1 not featured only, null - load all products
    @PriceMin            decimal(18, 4) = null,
    @PriceMax            decimal(18, 4) = null,
    @Keywords            nvarchar(4000) = null,
    @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions
    @SearchSku            bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU
    @SearchProductTags  bit = 0, --a value indicating whether to search by a specified "keyword" in product tags
    @UseFullTextSearch  bit = 0,
    @FullTextMode        int = 0, --0 - using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
    @FilteredSpecs        nvarchar(MAX) = null,    --filter by attributes (comma-separated list). e.g. 14,15,16
    @LanguageId            int = 0,
    @OrderBy            int = 0, --0 - position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date
    @AllowedCustomerRoleIds    nvarchar(MAX) = null,    --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
    @PageIndex            int = 0,
    @PageSize            int = 2147483644,
    @ShowHidden            bit = 0,
    @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
    @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
    @TotalRecords        int = null OUTPUT
)
AS
BEGIN
   
    /* Products that filtered by keywords */
    CREATE TABLE #KeywordProducts
    (
        [ProductId] int NOT NULL,
        Rank int
    )

    DECLARE
        @SearchKeywords bit,
        @sql nvarchar(max),
        @sql_orderby nvarchar(max)

    SET NOCOUNT ON
   
    --filter by keywords
    SET @Keywords = isnull(@Keywords, '')
    SET @Keywords = rtrim(ltrim(@Keywords))
    IF ISNULL(@Keywords, '') != ''
    BEGIN
        SET @SearchKeywords = 1
       
        IF @UseFullTextSearch = 1
        BEGIN
            --remove wrong chars (' ")
            SET @Keywords = REPLACE(@Keywords, '''', '')
            SET @Keywords = REPLACE(@Keywords, '"', '')
           
            --full-text search
            IF @FullTextMode = 0
            BEGIN
                --0 - using CONTAINS with <prefix_term>
                SET @Keywords = ' "' + @Keywords + '*" '
            END
            ELSE
            BEGIN
                --5 - using CONTAINS and OR with <prefix_term>
                --10 - using CONTAINS and AND with <prefix_term>

                --clean multiple spaces
                WHILE CHARINDEX('  ', @Keywords) > 0
                    SET @Keywords = REPLACE(@Keywords, '  ', ' ')

                DECLARE @concat_term nvarchar(100)               
                IF @FullTextMode = 5 --5 - using CONTAINS and OR with <prefix_term>
                BEGIN
                    SET @concat_term = 'OR'
                END
                IF @FullTextMode = 10 --10 - using CONTAINS and AND with <prefix_term>
                BEGIN
                    SET @concat_term = 'AND'
                END

                --now let's build search string
                declare @fulltext_keywords nvarchar(4000)
                set @fulltext_keywords = N''
                declare @fulltext_inflex nvarchar(4000)
                set @fulltext_inflex = N''
                declare @index int       
       
                set @index = CHARINDEX(' ', @Keywords, 0)

                -- if index = 0, then only one field was passed
                IF(@index = 0)
                    BEGIN
                        set @fulltext_keywords = ' "' + @Keywords + '*" '
                        set @fulltext_inflex = ' FORMSOF (INFLECTIONAL, "' + @Keywords + '") '
                    END
                ELSE
                BEGIN       
                    DECLARE @first BIT
                    SET  @first = 1           
                    WHILE @index > 0
                    BEGIN
                        IF (@first = 0)
                            BEGIN
                                SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' '
                                SET @fulltext_inflex = @fulltext_inflex + ' ' + @concat_term + ' '
                            END
                        ELSE
                            SET @first = 0

                        SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"'
                        set @fulltext_inflex = @fulltext_inflex + 'FORMSOF (INFLECTIONAL, "' + SUBSTRING(@Keywords, 1, @index - 1) + '")'           
                        SET @Keywords = SUBSTRING(@Keywords, @index + 1, LEN(@Keywords) - @index)                       
                        SET @index = CHARINDEX(' ', @Keywords, 0)
                    end
                   
                    -- add the last field
                    IF LEN(@fulltext_keywords) > 0
                        SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' + '"' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '*"'
                    IF LEN(@fulltext_inflex) > 0
                        SET @fulltext_inflex = @fulltext_inflex + ' ' + @concat_term + ' ' + 'FORMSOF (INFLECTIONAL, "' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '")'   
                END
                --PRINT @fulltext_keywords
                --PRINT @fulltext_inflex
                -- NOTE we dont use @concat_term here because it means we cannot get partial matching from either /or option
                SET @Keywords = '(' + @fulltext_inflex + ') OR (' + @fulltext_keywords + ')'
            END
        END
        ELSE
        BEGIN
            --usual search by PATINDEX
            SET @Keywords = '%' + @Keywords + '%'
        END
        --PRINT @Keywords

        --product name
        SET @sql = '
        INSERT INTO #KeywordProducts ([ProductId],Rank)       
        SELECT p.Id, Rank
        FROM Product p with (NOLOCK)
        INNER JOIN CONTAINSTABLE(Product, (Name,Sku,ShortDescription,FullDescription), @Keywords) As Ftt ON Ftt.[KEY]=p.id
        '

        ----localized product name
        --SET @sql = @sql + '
        --UNION
        --SELECT lp.EntityId
        --FROM LocalizedProperty lp with (NOLOCK)
        --WHERE
        --    lp.LocaleKeyGroup = N''Product''
        --    AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        --    AND lp.LocaleKey = N''Name'''
        --IF @UseFullTextSearch = 1
        --    SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
        --ELSE
        --    SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
   

        --IF @SearchDescriptions = 1
        --BEGIN
        --    --product short description
        --    SET @sql = @sql + '
        --    UNION
        --    SELECT p.Id
        --    FROM Product p with (NOLOCK)
        --    WHERE '
        --    IF @UseFullTextSearch = 1
        --        SET @sql = @sql + 'CONTAINS(p.[ShortDescription], @Keywords) '
        --    ELSE
        --        SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 '


        --    --product full description
        --    SET @sql = @sql + '
        --    UNION
        --    SELECT p.Id
        --    FROM Product p with (NOLOCK)
        --    WHERE '
        --    IF @UseFullTextSearch = 1
        --        SET @sql = @sql + 'CONTAINS(p.[FullDescription], @Keywords) '
        --    ELSE
        --        SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 '



        --    --localized product short description
        --    SET @sql = @sql + '
        --    UNION
        --    SELECT lp.EntityId
        --    FROM LocalizedProperty lp with (NOLOCK)
        --    WHERE
        --        lp.LocaleKeyGroup = N''Product''
        --        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        --        AND lp.LocaleKey = N''ShortDescription'''
        --    IF @UseFullTextSearch = 1
        --        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
        --    ELSE
        --        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
               

        --    --localized product full description
        --    SET @sql = @sql + '
        --    UNION
        --    SELECT lp.EntityId
        --    FROM LocalizedProperty lp with (NOLOCK)
        --    WHERE
        --        lp.LocaleKeyGroup = N''Product''
        --        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        --        AND lp.LocaleKey = N''FullDescription'''
        --    IF @UseFullTextSearch = 1
        --        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
        --    ELSE
        --        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
        --END

        ----SKU
        --IF @SearchSku = 1
        --BEGIN
        --    SET @sql = @sql + '
        --    UNION
        --    SELECT p.Id
        --    FROM Product p with (NOLOCK)
        --    WHERE '
        --    IF @UseFullTextSearch = 1
        --        SET @sql = @sql + 'CONTAINS(p.[Sku], @Keywords) '
        --    ELSE
        --        SET @sql = @sql + 'PATINDEX(@Keywords, p.[Sku]) > 0 '
        --END

        --IF @SearchProductTags = 1
        --BEGIN
        --    --product tag
        --    SET @sql = @sql + '
        --    UNION
        --    SELECT pptm.Product_Id
        --    FROM Product_ProductTag_Mapping pptm with(NOLOCK) INNER JOIN ProductTag pt with(NOLOCK) ON pt.Id = pptm.ProductTag_Id
        --    WHERE '
        --    IF @UseFullTextSearch = 1
        --        SET @sql = @sql + 'CONTAINS(pt.[Name], @Keywords) '
        --    ELSE
        --        SET @sql = @sql + 'PATINDEX(@Keywords, pt.[Name]) > 0 '

        --    --localized product tag
        --    SET @sql = @sql + '
        --    UNION
        --    SELECT pptm.Product_Id
        --    FROM LocalizedProperty lp with (NOLOCK) INNER JOIN Product_ProductTag_Mapping pptm with(NOLOCK) ON lp.EntityId = pptm.ProductTag_Id
        --    WHERE
        --        lp.LocaleKeyGroup = N''ProductTag''
        --        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        --        AND lp.LocaleKey = N''Name'''
        --    IF @UseFullTextSearch = 1
        --        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
        --    ELSE
        --        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
        --END

        PRINT (@sql)
        EXEC sp_executesql @sql, N'@Keywords nvarchar(4000)', @Keywords

    END
    ELSE
    BEGIN
        SET @SearchKeywords = 0
    END

    --filter by category IDs
    SET @CategoryIds = isnull(@CategoryIds, '')   
    CREATE TABLE #FilteredCategoryIds
    (
        CategoryId int not null
    )
    INSERT INTO #FilteredCategoryIds (CategoryId)
    SELECT CAST(data as int) FROM [nop_splitstring_to_table](@CategoryIds, ',')   
    DECLARE @CategoryIdsCount int   
    SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)

    --filter by attributes
    SET @FilteredSpecs = isnull(@FilteredSpecs, '')   
    CREATE TABLE #FilteredSpecs
    (
        SpecificationAttributeOptionId int not null
    )
    INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
    SELECT CAST(data as int) FROM [nop_splitstring_to_table](@FilteredSpecs, ',')
    DECLARE @SpecAttributesCount int   
    SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecs)

    --filter by customer role IDs (access control list)
    SET @AllowedCustomerRoleIds = isnull(@AllowedCustomerRoleIds, '')   
    CREATE TABLE #FilteredCustomerRoleIds
    (
        CustomerRoleId int not null
    )
    INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
    SELECT CAST(data as int) FROM [nop_splitstring_to_table](@AllowedCustomerRoleIds, ',')
   
    --paging
    DECLARE @PageLowerBound int
    DECLARE @PageUpperBound int
    DECLARE @RowsToReturn int
    SET @RowsToReturn = @PageSize * (@PageIndex + 1)   
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageLowerBound + @PageSize + 1
   
    CREATE TABLE #DisplayOrderTmp
    (
        [Id] int IDENTITY (1, 1) NOT NULL,
        [ProductId] int NOT NULL
    )

    SET @sql = '
    INSERT INTO #DisplayOrderTmp ([ProductId])
    SELECT p.Id
    FROM
        Product p with (NOLOCK)'
   
    IF @CategoryIdsCount > 0
    BEGIN
        SET @sql = @sql + '
        LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
            ON p.Id = pcm.ProductId'
    END
   
    IF @ManufacturerId > 0
    BEGIN
        SET @sql = @sql + '
        LEFT JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
            ON p.Id = pmm.ProductId'
    END
   
    IF ISNULL(@ProductTagId, 0) != 0
    BEGIN
        SET @sql = @sql + '
        LEFT JOIN Product_ProductTag_Mapping pptm with (NOLOCK)
            ON p.Id = pptm.Product_Id'
    END
   
    --searching by keywords
    IF @SearchKeywords = 1
    BEGIN
        SET @sql = @sql + '
        JOIN #KeywordProducts kp
            ON  p.Id = kp.ProductId'
    END
   
    SET @sql = @sql + '
    WHERE
        p.Deleted = 0'
   
    --filter by category
    IF @CategoryIdsCount > 0
    BEGIN
        SET @sql = @sql + '
        AND pcm.CategoryId IN (SELECT CategoryId FROM #FilteredCategoryIds)'
       
        IF @FeaturedProducts IS NOT NULL
        BEGIN
            SET @sql = @sql + '
        AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
        END
    END
   
    --filter by manufacturer
    IF @ManufacturerId > 0
    BEGIN
        SET @sql = @sql + '
        AND pmm.ManufacturerId = ' + CAST(@ManufacturerId AS nvarchar(max))
       
        IF @FeaturedProducts IS NOT NULL
        BEGIN
            SET @sql = @sql + '
        AND pmm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
        END
    END
   
    --filter by vendor
    IF @VendorId > 0
    BEGIN
        SET @sql = @sql + '
        AND p.VendorId = ' + CAST(@VendorId AS nvarchar(max))
    END
   
    --filter by warehouse
    IF @WarehouseId > 0
    BEGIN
        SET @sql = @sql + '
        AND p.WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max))
    END
   
    --filter by parent grouped product identifer
    IF @ParentGroupedProductId > 0
    BEGIN
        SET @sql = @sql + '
        AND p.ParentGroupedProductId = ' + CAST(@ParentGroupedProductId AS nvarchar(max))
    END
   
    --filter by product type
    IF @ProductTypeId is not null
    BEGIN
        SET @sql = @sql + '
        AND p.ProductTypeId = ' + CAST(@ProductTypeId AS nvarchar(max))
    END
   
    --filter by parent product identifer
    IF @VisibleIndividuallyOnly = 1
    BEGIN
        SET @sql = @sql + '
        AND p.VisibleIndividually = 1'
    END
   
    --filter by product tag
    IF ISNULL(@ProductTagId, 0) != 0
    BEGIN
        SET @sql = @sql + '
        AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
    END
   
    --show hidden
    IF @ShowHidden = 0
    BEGIN
        SET @sql = @sql + '
        AND p.Published = 1
        AND p.Deleted = 0
        AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999''))'
    END
   
    --min price
    IF @PriceMin > 0
    BEGIN
        SET @sql = @sql + '
        AND (
                (
                    --special price (specified price and valid date range)
                    (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
                    AND
                    (p.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
                )
                OR
                (
                    --regular price (price isnt specified or date range isnt valid)
                    (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
                    AND
                    (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
                )
            )'
    END
   
    --max price
    IF @PriceMax > 0
    BEGIN
        SET @sql = @sql + '
        AND (
                (
                    --special price (specified price and valid date range)
                    (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
                    AND
                    (p.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
                )
                OR
                (
                    --regular price (price isnt specified or date range isnt valid)
                    (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
                    AND
                    (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
                )
            )'
    END
   
    --show hidden and ACL
    IF @ShowHidden = 0
    BEGIN
        SET @sql = @sql + '
        AND (p.SubjectToAcl = 0 OR EXISTS (
            SELECT 1 FROM #FilteredCustomerRoleIds [fcr]
            WHERE
                [fcr].CustomerRoleId IN (
                    SELECT [acl].CustomerRoleId
                    FROM [AclRecord] acl with (NOLOCK)
                    WHERE [acl].EntityId = p.Id AND [acl].EntityName = ''Product''
                )
            ))'
    END
   
    --show hidden and filter by store
    IF @StoreId > 0
    BEGIN
        SET @sql = @sql + '
        AND (p.LimitedToStores = 0 OR EXISTS (
            SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
            WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + '
            ))'
    END
   
    --filter by specs
    IF @SpecAttributesCount > 0
    BEGIN
        SET @sql = @sql + '
        AND NOT EXISTS (
            SELECT 1 FROM #FilteredSpecs [fs]
            WHERE
                [fs].SpecificationAttributeOptionId NOT IN (
                    SELECT psam.SpecificationAttributeOptionId
                    FROM Product_SpecificationAttribute_Mapping psam with (NOLOCK)
                    WHERE psam.AllowFiltering = 1 AND psam.ProductId = p.Id
                )
            )'
    END
   
    --sorting
    SET @sql_orderby = ''
    IF LEN(@Keywords) > 0 /* Keyword searches ordered by rank */
        SET @sql_orderby = ' kp.Rank DESC'
    ELSE IF @OrderBy = 5 /* Name: A to Z */
        SET @sql_orderby = ' p.[Name] ASC'
    ELSE IF @OrderBy = 6 /* Name: Z to A */
        SET @sql_orderby = ' p.[Name] DESC'
    ELSE IF @OrderBy = 10 /* Price: Low to High */
        SET @sql_orderby = ' p.[Price] ASC'
    ELSE IF @OrderBy = 11 /* Price: High to Low */
        SET @sql_orderby = ' p.[Price] DESC'
    ELSE IF @OrderBy = 15 /* creation date */
        SET @sql_orderby = ' p.[CreatedOnUtc] DESC'
    ELSE /* default sorting, 0 (position) */
    BEGIN
        --category position (display order)
        IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.DisplayOrder ASC'
       
        --manufacturer position (display order)
        IF @ManufacturerId > 0
        BEGIN
            IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
            SET @sql_orderby = @sql_orderby + ' pmm.DisplayOrder ASC'
        END
       
        --parent grouped product specified (sort associated products)
        IF @ParentGroupedProductId > 0
        BEGIN
            IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
            SET @sql_orderby = @sql_orderby + ' p.[DisplayOrder] ASC'
        END
       
        --name
        IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
        SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
    END
   
    SET @sql = @sql + '
    ORDER BY' + @sql_orderby
   
    --PRINT (@sql)
    EXEC sp_executesql @sql

    DROP TABLE #FilteredCategoryIds
    DROP TABLE #FilteredSpecs
    DROP TABLE #FilteredCustomerRoleIds
    DROP TABLE #KeywordProducts

    CREATE TABLE #PageIndex
    (
        [IndexId] int IDENTITY (1, 1) NOT NULL,
        [ProductId] int NOT NULL
    )
    INSERT INTO #PageIndex ([ProductId])
    SELECT ProductId
    FROM #DisplayOrderTmp
    GROUP BY ProductId
    ORDER BY min([Id])

    --total records
    SET @TotalRecords = @@rowcount
   
    DROP TABLE #DisplayOrderTmp

    --prepare filterable specification attribute option identifier (if requested)
    IF @LoadFilterableSpecificationAttributeOptionIds = 1
    BEGIN       
        CREATE TABLE #FilterableSpecs
        (
            [SpecificationAttributeOptionId] int NOT NULL
        )
        INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
        SELECT DISTINCT [psam].SpecificationAttributeOptionId
        FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
        WHERE [psam].[AllowFiltering] = 1
        AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])

        --build comma separated list of filterable identifiers
        SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
        FROM #FilterableSpecs

        DROP TABLE #FilterableSpecs
     END

    --return products
    SELECT TOP (@RowsToReturn)
        p.*
    FROM
        #PageIndex [pi]
        INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
    WHERE
        [pi].IndexId > @PageLowerBound AND
        [pi].IndexId < @PageUpperBound
    ORDER BY
        [pi].IndexId
   
    DROP TABLE #PageIndex
END

Multi lingual multi currency eCommerce with NopCommerce

10. October 2014 15:23 by John Dillon | 0 Comments

10. October 2014 15:23 by John Dillon, 0 Comments

One of the best things about modern eCommerce platforms such as NopCommerce is the range of features you get out of the box. 5 years ago, if a client came to us wanting a multi language, multi currency site, set automatically by IP, we would have quoted them a figure that would have made them think twice. These days we can do most of it right out the box, and this is exactly what the requirements were for http://www.goforitgames.com/.

Multi-lingual websites are complicated things to build, but its done well in NopCommerce. Once setup in the CMS, a client can control pretty much anything they want to using the localisation settings. This ranges from text on buttons, to about us pages, to product descriptions and much more.

Multi currency is also built into NopCommerce. Currencies can either be set automatically using a web service, or set manually.

The one limitation we encountered here is that prices cannot be set specifically per language per product (i.e. make a product £3.99 but then $5.99 for the US). Prices are set by converting the default currency to the active currency. The problem here is that it doesn’t always give clean prices, so for example £4.99 becomes $8.04. A client may want to make this $7.99 so its less obviously a converted price.

Fortunately this is a feature which is being built into NopCommerce for the next release (https://nopcommerce.codeplex.com/workitem/9291), so for future builds it won’t be an issue. In our case however we explored 2 workarounds.

  1. The simpler solution is by being creative with manual exchange rates. This generally works best with a small range of products but its a very simple solution.
  2. The second solution is to use tier pricing in a multi store environment. To clarify NopCommerce is a multi store application, meaning you can have multiple stores (websites) from the same product base. Tier pricing on the other hand is used for volume discounts. So for example for X product you say that if the user purchases 10 items they get them for £3.99 each instead of £4.99. Tier pricing can be set per language, so the trick is to set product prices per language using this method, and set the quantity to 1. This makes it slightly trickier to set prices but it works.

Other than this there were 2 main other customisations we needed to make for this project:

  1. Automatically selecting language/country based on IP address
    When a user first comes to http://www.goforitgames.com/ we need to select which language/currency to show them. NopCommerce by default selects this based on the language settings in a users browser. This wasn’t the best solution to us because more often than not (as was the case with the client) these default to US, as its not something the user always configures. Therefore whilst it has its faults, selecting this based on IP was our preferred route. So we changed it to select based on IP, and if that fails (i.e. it cant match based on IP) we default to browser settings.
  2. Selecting language/country also needs to select currency
    By default NopCommerce allows you to select language and currency independently. In our case however we wanted currency to be linked specifically to the language/country (i.e. if the user is US it shows $). We therefore modified the code to automatically assign a currency when a language was set, either automatically when the user first arrives, or by changing language using the flags.

Now whilst these changes weren’t 5 minutes work by any means, they are certainly a world away from what would have been the case 5 years ago.

Are you looking for an eCommerce website? Call us on 01273 603995, or email us at info@accu-web.co.uk.

Responsive eCommerce design using NopCommerce themes

7. October 2014 11:58 by John Dillon | 0 Comments

7. October 2014 11:58 by John Dillon, 0 Comments

In 2014 an ecommerce website simply must work equally well on a phone and tablet, as it does on a larger screen. Conversion rates are still typically lower on smaller devices; however research suggests that users are significantly more likely to buy on a mobile where the site is specifically optimized. There is also a lot of research which suggests that people who view sites on smaller devices, often go on to complete the purchase on devices with larger screens.

The most commonly used method to achieve this is through responsive design (making a single set of content adapt to the different screen sizes it might be used on).
This might sound relatively simple however I can assure you it’s not. Laying out a complex screen in the space available on a mobile device is completely different to doing the same on a widescreen laptop. The real challenge here is that responsive design is about getting pretty much the same set of content and images to work on both!

I won’t go into full details here but effectively it means the design and build process is potentially 3 or 4 times more complicated than it used to be. The impact of this is it means a project takes 3 or 4 times longer, meaning it costs 3 or 4 times more. This is a problem, both in terms of costs and timescales. Not all projects have massive budgets and long lead in times, and we as developers have to adapt to these needs.

The result is that these days when it comes to ecommerce layouts, and most other builds for that matter, clients have a basic decision to make about design. Do you go bespoke, or so you use an off the shelf template or framework?

Sticking mainly to ecommerce for now there are 2 main approaches:

  1. We create you your own bespoke responsive design from scratch. This is for clients with a suitable budget who want absolute control over their product. We’re big fans of bespoke because when we started development some 13 years ago now there was no other choice, and we’ve personally seen how investing in bespoke systems can pay off in the long term. A website is a long term investment and a carefully thought out front end should last a minimum of 3-4 years, and more than pay for itself in that time.
  2. For other clients, who may not have the same budget, timescales or design requirements, there are the prebuilt themes. These are out of the box designs which can be installed relatively simply on eCommerce and other CMS platforms. They can typically be purchased for less than £100 so make very good financial sense. Here is a NopCommerce theme for example: http://themes.lavella.nop-templates.com/.

The problem here is that it leaves no middle ground, and in our experience most clients fit into a middle ground. They don’t need or cant afford a completely bespoke solution, yet they don’t want something completely out of the box.

This is where we have developed our own 3rd solution of customizing themes. This customization can range from simple logo and color changes, to a complete reworking to something which barely resembles the original theme.
The major benefit of this approach is that a high degree of customization is possible, with less work than an entirely bespoke approach.

As an example take: http://www.mulberrybush.co.uk (specialist retailers of children's toys and games). Believe it or not this is based on this theme http://themes.motion.nop-templates.com/

Mulberrybush are a client we have worked with for many years. They were used to a bespoke design but realized the need to go responsive necessitated a shift in thinking, and so were open to this 3rd approach.
From a client perspective this takes a leap of faith, because its very hard as developers to reassure a client that we can turn http://themes.motion.nop-templates.com/ into http://www.mulberrybush.co.uk, and explain exactly where the boundaries of possibilities lie.

Fortunately because we’d always delivered over the years they trusted us. They also trusted us because they knew we had a very skilled designer on board (Anna Celeste Watson), who was able to translate their ideas for a new site into something we could build on top of this theme. Now this wasn’t always a simple process, but the result is a completely unique site, built around proven techniques, yet that didn’t take the same build time as creating one entirely from scratch.

The result has been a site everyone is very happy with. Not only has the client received positive comments from customers, but sales figures back up a good increase in conversion rates, meaning all this hard work and investment will over the course of time pay for itself.

For those of you who are interested in my next post I’ll go into more details about the actual design process and how one takes a theme like this and turns it into a bespoke design.

NopCommerce plugin: Relex remote payment integration

16. September 2014 10:24 by John Dillon | 0 Comments

16. September 2014 10:24 by John Dillon, 0 Comments

For a recent project we created a NopCommerce plugin which integrates with the Realex remote payment system (Global Iris RealMPI 3D Secure Remote). We were surprised something didnt already exist out there for this, hence we're writing this post in case it can be of use to anyone else.

If anyone is interested please contact us and we can send you the base code. This would be sent as is and unsupported.

Launch: Mulberrybush.co.uk eCommerce website

12. August 2014 13:32 by John Dillon | 0 Comments

12. August 2014 13:32 by John Dillon, 0 Comments

We're very pleased to announce that after weeks months of hard work, a new version of http://www.mulberrybush.co.uk has just been launched.

Mulberry bush are one of our oldest and most valued clients; we’ve worked with them for well over 10 years now, and built at least 4 incarnations of their popular eCommerce website selling traditional and innovative toys for children.

This latest version was a replacement for their previous site which was built around 3 years ago now. Its shelf life was cut a little shorter than one might usually expect from a website due largely to the explosion seen in responsive web design. It’s pretty much a given in 2014 that an eCommerce website is optimised for the full range of devices out there (mobiles, tablets etc...), and hence the decision to rebuild was taken.

The new site was built using our favourite eCommerce platform NopCommerce, which was as ever great to work with. The project wasn’t without its challenges, very few projects are, and to be honest it’s what makes them interesting. I’m not going to go into any detail right now about these challenges as we have some interesting posts planned over the coming weeks on these subjects (such how do you turn this into this to save weeks of work). If you’re interested then in NopCommerce builds then please do keep an eye out for these posts.

Responsive design with NopCommerce: A bespoke responsive design

19. December 2013 16:05 by John Dillon | 0 Comments

19. December 2013 16:05 by John Dillon, 0 Comments

Making sure your new eCommerce website has a responsive design is a pretty important component of building an eCommerce website these days. More and more people are using smartphones and tablets to view websites on these days, and the range of different devices and operating systems is growing rapidly. There is still a valid debate about native Apps Vs Html5 Hybrids, but in our opinion the latter is the clear winner when it comes to cost effectiveness.

Once the decision to go responsive has been made this still leaves a bit of a conundrum, because bespoke responsive web design can be a very time consuming and therefore costly exercise. The choice to make is whether or not to go for an off the shelf theme, a modified off the shelf theme, or have a completely bespoke design created from scratch.

Option 1 of an off the shelf theme can mean you are up and running for a relatively low cost in a few days, where as the opposite end of the scale option 3 of a completely bespoke design can mean many weeks of work for designers & developers. The decision will be a complicated one factoring in things like: the needs of the project (e.g. how important is it to be unique), the budget available, timescales for development, what themes are available for the platform in question etc....

Let’s take a recent build we did for example using NopCommerce: http://www.butterfliesanddragons.co.uk/. Whilst there are certainly some good out of the box responsive themes out there for NopCommerce: (e.g. NOP Template, the needs of this project were such that a completely unique site was required, and none of the themes we found came close to what the client wanted. Therefore option 3 was the only logical choice.

We therefore started down our usual build process path and at the relevant juncture begin wireframing. The decision had already been made to create the design on top of the twitter bootstrap responsive framework in order to give us a head start. This gave us 5 different viewports to create a design for: Large display, default, portrait tablets, phones to tablets, and phones (see here for more info).

Very quickly we and the client realised what a task this would be. For wireframing purposes we were doing 4 key screens (home, category listing, product details and cart), which meant 20 separate wireframes needed to be created and tested (i.e. 5 variations of each of the 4 screens). We then needed 15 pages designed by Anna our graphic designer, again each of which needed 5 variations... that made 75 individual designs eeeek.

As anyone who’s done responsive design knows, creating these variations is not simply a case of squishing things and moving a few boxes around, each screen has to be individually designed. What you can logically fit onto a widescreen TV is completely different to a phones screen, and it requires a completely differently layout and sometimes content. It also needs to be designed with development efficiency in mind. What we don’t want is to have to repeat content and code all over the place so as to achieve what we want (i.e. different code and content for each viewport). In an ideal world we want 1 set of HTML and content which is altered by CSS media queries to achieve what we need to across the different viewports.

A very long month later we had all our designs signed off and ready to be built, easy days then I hear you say.... Our developers would most certainly not agree. In the end it took nearly 4 weeks to get the site coded up into static HTML files, sometimes with 3 guys working on it simultaneously. At the end of it they could hardly bare to look at the designs any more.

The final step was then creating a new custom theme for NopCommerce: and ripping out all the existing UI code. Whilst this wasn’t a simple job by any means, compared to the previous stages it felt like a breeze. Coupled with this the great way in which layout is separated in Nops architecture made it pretty easy. We had to make very few source code modifications to get this all working as we needed it to, which is a testament to how well built and flexible NopCommerce: is as an eCommerce platform.

The end result after months of hard work was http://www.butterfliesanddragons.co.uk/. Take a bit of time to resize pages in your browser and you will see the different layouts. Now it’s not pixel perfect across all viewports by any means, but that’s because of the realities of budgets and what we wanted to achieve with the site. Suffice to say testing results of the site were very positive, as has been the clients reaction and anyone else who’s looked at the site. This is down to the hard work of everyone involved and the creativity of Anna who designed the site.

Ecommerce with NopCommerce: Customising NopTemplates Ajax filters

29. October 2013 14:17 by John Dillon | 0 Comments

29. October 2013 14:17 by John Dillon, 0 Comments

NopTemplates Ajax filters

In a recent eCommerce website we created using NopCommerce (www.butterfliesanddragons.co.uk), we were faced with an interesting challenge; we had to create a custom search control which integrated with NopTemplates Ajax filters.

These filters were important as they allow users to easily filter search results on the fly, and this kind of faceted search filtering is pretty standard fare in eCommerce sites these days (our wireframe testing backs up this is generally expected).

In addition to this a very important part of the clients brief was to create a simple method of searching for toys using the full range of attributes we setup for the product range (e.g. colour, price, age range etc...). On top of this it needed to be more creative than a simple search, so we came up with the idea of making it character driven, and trying to use this character to highlight why particular toys in users searches may be more applicable than others. So for example if you've searched for "blue pirates for your son Jimmy", at any stage during your visit if you come across such a toy, "Dave Dragon" will appear and highlight to you that this toy may be more suitable than others.

Butterflies and Dragons eCommerce website

Unfortunately NopCommerce doesn’t do this out of the box (why would it, this is a unique feature), but fortunately like any good open source eCommerce platform it was flexible enough for us to create this custom functionality ourselves.

So if you look at the eCommerce website in question, you will see the custom "Find A Gift" search we created. This allows users to search on all attributes created in the Content Management System (CMS), as well as enter optional personalised information like the child’s name. So for example we might search for toys for: Boys, Theme=Pirates, Colour=Blue, Childs name=Jimmy, and Relationship=Son.

eCommerce website - Search

Now the first interesting point (which is a bit of a digression but worth highlighting) is how the search displays a live count of how many toys a search will return. So for example this search returns 4 toys. This is interesting not only because its a very useful feature much underused on the web we believe, but because it shows the value of user testing a site. We tested this site before its release using real users both locally and with www.usertesting.com. One of the main things we noticed in the videos of people using the site was how often they reached a no results page, because there were no toys matching their search criteria. Whilst this is a big site, its fairly specialised so the product range of around 1000 toys can produce no results page fairly easily given the specific range of filters we have. The people we tested also seemed compelled to enter all fields in the form, so if a user enters a search for "pink frogs between £5-10 that help children with their numeracy skills", its fairly likely they will encounter a no results pages. Now whilst users didn’t seem to mind seeing these pages (they just hit back and amended it), it felt very clunky to us. We all discussed possible solutions like more explanatory text, but the clear winner was this solution which helps inform the user how many results they will get before they actually search. Crucially if the search doesn’t product any results, it doesn’t allow the user to proceed to the results page. Testing result of this were much more successful and as I say really do highlight the value of user testing a site.

Anyway back to the main point, which is to discuss how we integrated this custom "Find a gift" search with the NopTemplates Ajax filters. The issue for us was how we created a results page from our custom "Find a gift" search, with these filters preloaded. It was obviously crucial this was the case for them to be of any use.

Our solution was to map the # parameters used by NopTemplates to create the filters, and match these to our search filters. So for example in a url like http://www.butterfliesanddragons.co.uk/all-toys#/specFilters=7m!#-!46!-#!12m!#-!70!-#!6m!#-!20 its about analysing this section "specFilters=7m!#-!46!-#!12m!#-!70!-#!6m!#-!20". Then in our search, based on what a user selects in the form, we build up a string of parameters to pass across to the results page. As long as we do this correctly, the NopAjax filters then do the rest using a listener which checks for pages with # values and filters if it finds some.

The result is a custom search, which integrates with the NopTemplates Ajax filters out of the box. Admittedly from a developers point of view it isn’t a perfect solution because we do need to make an Ajax call after the page loads to filter the products, but both the client and customers haven’t seen this as any sort of an issue, so in terms of the brief its job done.

Pleasingly NopTemplates hadn't ever seen this approach used before and praised our inventiveness... and praise from fellow developers is always nice to receive. If there are any NopCommerce developers out there interested using this approach please do get in touch as we'd be more than happy to share our code.

If you are interested in any of the eCommerce web design solutions mentioned above please do get in touch with us today at the Skiff in Brighton. We can very quickly help you determine which option might be best for you, and in most cases we have demo sites of platforms setup which you can have a play around on to see what you might be getting. You can either call us on 01273 603995, or email us at info@accu-web.co.uk.

Bespoke Vs open source CMS web design: Which content management system is best?

17. October 2013 12:18 by John Dillon | 0 Comments

17. October 2013 12:18 by John Dillon, 0 Comments

Whether or not we use a bespoke content management system (CMS) or an off the shelf open source Content Management System (CMS) to build a project has always been a key question clients ask us. This is a decision we always make with the client after careful consideration of the options, based around their individual needs.

In our opinion whilst there is no right or wrong answer here (it’s about evaluating what’s right for the individual web design project/client), the answer to this question is increasingly becoming an open source content management system such as Orchard, Word Press, Umbraco, or Drupal.

Typically in the past the decision would be made to use our own CMS which has been developed over many years. This hooks into industry standard tools like the Telerik ASP.NET Ajax toolkit and is a very robust system. The decision to opt for this as opposed to an off the shelf option was generally taken because most businesses/organisations are very different from one another, making the applications and websites they require quite bespoke. Creating this very personal website around a generic system that provides hundreds of modules like blogs, forums, events calendars or eCommerce modules, none of which they would ever use, didn’t make sense for a client. It made much more sense to create their own bespoke website using proven building blocks of our own CMS. This creates an application that does exactly what the client’s needs it to do, no more no less, with no bloated code.

So why is this changing?

  • Content management systems and their ecosystems have evolved and matured

    In the past there were less obvious market leaders when it came to CMS systems, and the ecosystems around them were less established. This made developers nervous because it meant investing large amounts of time in something which may or may not succeed. Development was also more time consuming because of the lack of other people doing similar work on these systems. These days we can be pretty sure that if we invest time in learning a CMS such as Word Press, that this knowledge is still likely to be relevant in years to come. We can also be relatively sure that if we do need help its out there and easily accessible. Equally as a business owner I know that there is not going to be a shortage of talented programmers out there to work on the projects we need.

    The systems themselves are also now very slick, they are quick to setup, easy to configure, and don’t suffer from the same performance issues they did a few years back. There is also generally a wealth of professionally developed modules, plugins and themes out there which in some cases can saves weeks of development and therefore cost for the client.

  • Client expectations and knowledge of systems has evolved

    Its very regular these days for clients to come to us with a clear idea of which CMS platform they feel their new website should be built in. This is typically because they have heard good things from other people about a system, or because they have spent time researching the market themselves.

    This research will point them towards open source, mature platforms, with plugins for everything, and large ecosystems to help support them. Compared to the unknown of a bespoke system developed by a company they more than likely haven’t worked with before, they are understandably led to feeling more comfortable with an open source platform.

    As a company we relish this kind of knowledge in a client, because it means they are interested in the technical side of things which means working with them will be infinitely easier. Over the years we have found the more a client is willing to take on board in a technical sense, the better and longer the working relationship is for both parties.

    That’s not to say this kind of knowledge cant often be challenging as a developer, because whilst the intentions are correct, sometimes the actual choice might not be. For example we have had clients come to us adamant they want to build an eCommerce website using Word Press. Now whilst this is possible, Word Press is not really designed as an out and out eCommerce platform. If you are doing eCommerce you would be much better building it in something like NopCommerce or Magento. Thankfully most clients are usually to open to new ideas here and when its properly explained to them they quicky adapt their decisions.

  • Economies of scale

    One of the key phases in our web design process is a scoping phase we go through with clients. This is where we take the full list of what they want to achieve, and match it up against their budget. Its very rare they match exactly, and so compromises need to be made. This is exactly why we have this process, so that the client rather then us decides where compromises are made.

    Often this is easy, for example there may be a particular feature the client thought was simple but didn’t realise it would take up 10% of the budget, so its shelved for the time being.

    At other times its not so simple, and this is where picking the right choice of CMS platform can help. For example are their plugins that already do much of what the client needs? Maybe they dont do it in exactly the way the client had in mind but with some creative thinking we can often make things fit, and if something saves a client thousands of £ you would be amazed how quickly they can adapt to a different way of doing things!

    Another key area is often responsive web design (making the design adapt to mobile devices). For reasons I discuss here this can often add significantly to a projects costs, and so something we always consider is whether or not an off the shelf theme such as these for example can be used. These can either be used as is, or adapted for use by developers at a significantly reduced costs compared to creating a responsive web design from scratch. Often having a completely bespoke and unique design isn’t a top priority for a client, especially when it can save them thousands of £ and speed up development time by weeks.

To summarise then there really is no right or wrong answer here; you may think from the list above that we will always recommend an open source platform from now on to new clients, but I know that won’t be true. So many systems we build are completely unique, and because of the needs of the project (e.g. efficiency, complicated functionality etc...) a bespoke solution will still be the right choice. The key point is that all options are open, and we make this choice with our clients based on the needs of a project.

If you are interested in any of the content management systems mentioned above please do get in touch with us. We can very quickly help you determine which option might be suitable for you, and in most cases we have demo sites of platforms setup which you can have a play around on to see what you might be getting. You can either call us on 01273 603995, or email us at info@accu-web.co.uk.

Is it time for a new web design for your company website?

24. September 2013 14:40 by John Dillon | 0 Comments

24. September 2013 14:40 by John Dillon, 0 Comments

Technology is an ever moving target, what’s new today can be old fashioned in a year’s time. Website design is no different to this, in fact if anything it's a perfect example of this.

So why can’t us website designers build something that stands the test of time, and what is "a long time" in web design terms? How long for example is it reasonable to expect a website design to remain current?

Well its worth remembering how young web design is relative to other industries. Despite how advanced us web designers like to think the tools we have are, in reality when it comes to designing websites we are still living in caves (some developers actually do to be fair), and using stone age tools.

Take one of the current big things in web development which is responsive web design. This means building a site which provides the optimal user experience regardless of whether a user is accessing it via desktop, laptop, mobile, iPad or tablet. Its very unlikely we would build a website these days that didn’t take this into account in some shape or form.
Take our latest ecommerce website for example http://www.butterfliesanddragons.co.uk/. This was built on top of the twitter bootstrap responsive framework and a lot of effort was put in to making the site equally usable across all viewports. Whilst this was a completely new ecommerce website replacing an old one developed by other web designers (not in Brighton), one of our existing clients with an ecommerce website built say 4 years ago might ask "why wasn’t my site future proofed for this?"

Well the answer is because things move so quickly in ways its impossible to predict. For example the iPhone has only been around for about 6 years, and believe it or not the iPad for only 3. This means as little as 3 years ago smartphones and tablets weren’t even in the thought process of web designers when building a website, let alone at the forefront. I remember even 2 years ago looking at web stats of clients current websites, and with mobile/tables usage at 5-10%, coupled with the expense needed to build a site using responsive web design techniques, deciding together with the client that it wasn’t commercially viable at this stage. These same clients are now coming to us with usage stats much higher (we've seen as high as 40-45% mobile/table usage on a site), asking for new responsive web designs to be created for their site.

So getting back to the original question then, how do you as a website owner decide when a new design for your website is required?

Here are some fairly simple tips:

  • Has technology has changed significantly since the site was built. The growth of smartphone use and making a site responsive to these devices is a good example of this.
  • Are there important features missing? For example social media integration with websites has grown dramatically over the past few years and most sites are now designed with this in mind. It’s unlikely a 2-3 year old site would have been built in such a way.
  • Do you have enough control of your site, for example does the site have a CMS (Content Management System) that is flexible enough for your needs?
  • Is your web analytics software showing low conversation rates, a decrease in website traffic, a high bounce rate, or that visitors are leaving the site too quickly? This doesn’t necessarily point to a design related issue but it certainly warrants a full investigation of your current setup.
  • Have you significantly changed what you do, your services, or your product range since the site was built?
  • Has your company rebranded since the site was developed?

When doing an appraisal of your current website its often useful to get a range of different opinions on this. Whilst a client may think their site is still current and looks good, their target market may think differently. When doing this you should get as many of the following different viewpoints as possible:

  • A professional opinion from a web designer. We do free appraisals of all websites.
  • Impressions from friends and family, ideally ones who you know will be honest and up front.
  • 3rd party user testing sites such as http://www.usertesting.com/. We use sites like this to test our new websites before launch, to make sure they meet the brief and the business requirements. Doing this on an existing website can help determine if that website needs updating, but also provides useful guidance on what your target markets are looking for in a website. This can all be done for a relatively low cost.
  • Previous customers or services users.

Before embarking on such an exercise its worth creating a script or some basic questions to guide the users towards the questions you want answered. So for example you might ask:

  • Do you like the design?
  • Does the design look contemporary?
  • Is the design something you would expect from a website in this sector?

Again we can help you come up with this script.

If you are thinking about re-designing your website why not get in touch with us today for an informal chat. We can very quickly help you determine if this is the case, and if so give you a range of options to consider for next steps. You can either call us on 01273 603995, or email us at info@accu-web.co.uk.