Top List Refreshing

At the end of every month, we need to update the Bestseller web pages. These pages are the top tabs (Books, Audio, Music, etc.) on the website.

Use the same excel specific_list file to populate BookManager lists.  At the end of each month rename specific_list.xls to specific_list_yymmdd.xls and start a new clean file.

PREPARE DATA FILES
In HiPoint run Reports > Sales > Sales Analysis > Item Best Seller Report to file.
IMPORTANT: Deselect List Only If Inventory.
Select last two months e.g. 05/01/09 to 06/30/09
Destination: File > Save as TopLists_yymmdd.txt (date of creation)
Make column 2 the same as column 1

Note: Fiscal year is August to July. If looking for July to August stats, make August the new year (e.g., 2015 below)

In HiPoint run TRN (click on Transaction Inquiry tab)
Under Customer Selections:
Customer = 6751962
Under General Selections:
Period = last two months e.g. 05/01/09 to 06/30/09
Save to file as Indigo_090630.txt (date of creation)

In HiPoint run TRN
Customer = 6621879
Period = last two months e.g. 05/01/09 to 06/30/09
Save as TenThousand_090630.txt (date of creation)

You now have files which show ALL sales for the period as well as Indigo and Ten Thousand Villages sales for the same period. Indigo and Ten Thousand Villages sales are removed from the analysis when determining Top Lists.

Pull them from C:\tdsfiles and put all text files into G:\DATA DEPARTMENT\Top List Analysis and work from there.

REVIEW DATA
In G:\DATA DEPARTMENT\Top List Analysis\TopListAnalysis.mdb run:
• qry_1_DELETE_Indigo_YTD
• qry_1_DELETE_TenThousand
• qry_1_DELETE_TopLists

Import the TopList file to the table TopLists:

Click Tables > click TopLists > click External Data Tab> Text File (smaller icon) > Append a copy of the records to the table. Choose TopLists from the dropdown menu> Browse to the TopLists file in C-TDSFiles > OK > Delimited > Next > Click First Row Contains Field Names > Next > Import to Table > Finish

Import the Indigo file to the table Indigo_YTD.
Import the Ten Thousand Villages file to the table TenThousand.

Click Queries. Run qry_2_TopBooks. Books are generally the area most likely to be affected by special event quantities.
• Double check a couple of key titles against Item in HiPoint for sales quantities in the relevant period. For numbers that are off, check trans/ord to see if there haven’t been orders added after your data export
• For those titles that look like they were special quantities for events, etc discuss review sales and discuss with pH or BJD to see if they should be Adjusted Out. Adjustments are entered directly into the TopLists table as –ve numbers in the AdjustOut field.

REFRESH WEB PAGES
Copy the data from the first two columns ProductCode and SortOrder into the first worksheet in specific_list. Add the sort order 1, 2, 3, etc. (If you don't the titles will be sorted alphabetically.) Save.

In dempseycanada.com, click on the Books tab up top. This brings you to the web page Bestselling Books. Note the number of books and a few of the top titles and their covers.

In DempseyCommerce, click List Pop'n. In the pop-up box, enter the name of the webpage that you're populating (e.g., Bestselling Books) and click Load Items. Go back to the web page, refresh and make sure the titles have changed.

Export these queries to an XL file at G:\DATA DEPARTMENT\Top List Analysis called TopLists_05-07.xls (for period shown above). They will all be exported as separate worksheets to the same file.

Run these queries and repeat the steps above to refresh the web pages:
• qry_2_TopAudio
• qry_2_TopBooks
• qry_2_TopDecks_Combined
• qry_2_TopDecks_SAFF_SAFS
• qry_2_TopDecks_STAD_STAS
• qry_2_TopCrystalBooks
• qry_2_TopDVD
• qry_2_TopMusic – DO NOT USE – TAKES TOO LONG! Export to Excel file instead (RIGHT CLICK > EXPORT) - Copy the BookManager column in a separate worksheet. When uploading this list to BookManager, you might have to replace the SKU with UPC or ISBN.
• qry_2_TopSidelines
• qry_2_TopCrystals
• For Top Chimes, use qry_2_TopChimes_Bells - Copy the UPC column in a separate worksheet for BookManager
• For qry_2_TopRecentTitles, go to Design View and change the monthyear to the past two months before running query. Choose the top 20 titles
• Run qry_2_TopCalendars once there’s enough data to go with, usually by the end of August
• Top Forthcoming and Recent Acquisitions (wait right til the end of the month to run this so all the titles are captured) have their own button in Dempsey Tools.
• See also Front Lists for Future Months - What's New

Review each tab and make sure there is an odd number of rows (even number of titles) on each tab. Delete the overall YTD lowest title if necessary (not always the bottom one in the XL file).

 

FEATURED ITEM REFRESH
If you have time, refresh the lists under Featured Items (click through from homepage sidebar):

Bible Series - qry_2_TopList_Bibles
Children's Books - qry_2_TopList_Children
Crystal Books - qry_2_TopCrystalBooks

Etc.

Note: The Crystals - Woodstock list gets automatically pulled so you don't need to do this one.