Update Retail Reporting Weekly

Update CURRENT 20201108 Retail Beverage Operations Sales Analysis Data - Threes (data delivery shipping pickup tips) in the folder a. Retail Beverage Operations Data Analysis for our retail reporting and marketing and Akshay

See the how to video for more: "Update Retail Reporting Weekly"

Full Week Update

1. If you haven't already, make a copy as a backup of the current report

   • Name the copy the same thing with the word "BACKUP" replacing the word "CURRENT" as a historical record

2. Update the current reporting template.

   • Change the date in the title to the new Sunday date

   • Change the date in the yellow cell on sheet Nav and Instructions for the week end date of the Sunday.

3. Click the button Clear Old Data in the sheet Nav and Instructions to clear the source data and formulas in various sheets (including the sheets Shopify Log Manual and Tips Shopify Log Manual)

4. Import the sales source data from Data Export app in Shopify

   • Go to the app by logging in to Shopify, going to Apps and clicking on Data Export

   • Select the Sales Report - Used for Reporting (allow for the report to load)

     • Make sure dates are for the previous week (start of Monday to end of Sunday)

     • Select Export CSV

   • Move the file to the staging folder

   • Open the file with Google Sheets or MS Excel, copy and paste Values (Cmd + Shift + V) into the cell A6 of sheet Shopify Log from Download

5. Import the inventory source data from Data Export app in Shopify

   • Go to the app: https://app.dataexport.io/v2/reports

   • Select the Inventory Location Report (allow for the report to load)

     • Supposedly when you run the report, no matter what date range you put in, it gives you the inventory at the moment you run the report

     • Select Export CSV

   • Move the file to the staging folder

   • Open the file with Google Sheets or MS Excel, copy and paste Values (Cmd + Shift + V) into the cell A6 of sheet Inventory from Shopify

6. Import Square

   • Download Items Detail Report from Square for

     • All locations (Gowanus, Greenpoint, Governors Island)

     • 7 Days for last Monday to Sunday week (4am-4am) (for 7 days, for example: May 1, 2023–May 7, 2023)

     • Click Export => Items Detail Report

   • Move the file to the staging folder

   • Open the file with Google Sheets or MS Excel, copy and paste Values (Cmd + Shift + V) into the cell A6 of sheet Square Log

7. Fill down manually the formulas in the sheets Shopify Log from Download and Square Log

   • The formulas are at the right of the first gray column in the first row of data (row 7)

8. Prepare the data by clicking the button: Prep Data in the sheet Nav and Instructions (let the file save before starting this step). Clicking that button will do these things:

   • Format the column Shipping Zip Text in sheet Shopify Log from Download as text

   • Tips Shopify Log Manual: This is the source for Tips that are technical tips in shopify and not a product from TipJar, which is all POS tips and all other tips when we have that feature enabled

     • Transfer the data we need from Shopify Log Manual to Tips Shopify Log Manual:

       • Sort Shopify Log Manual by Tip Flag column desc by clicking Sort by Tip Flag

       • For the rows with the 1 for Tip Flag, copy them to the sheet Tips Shopify Log Manual

   • Create sheet Orders by Business Line

     • • Check Columns M:N and fill in the current week's date in Column M and fill down the formula in Column N

9. Update Beer List in Sales Summary here

   • Copy the list of unique Brands (not including the header) from the source of truth for brands in file Full Beer Portfolio (v3), sheet UB FullBeerPortfolio, column AT. The header for this row is "Unique List for Weekly Reporting"

   • Into file Retail Weekly Reporting, sheet Sales Summary, cell C34

   • Make sure no substrings match to avoid double counting

   • Make sure formulas go to the bottom of that section, especially in row 4

10. On By Shipping Method By Day from Download sheet, paste values for current week on top of formulas

    • Cmd + C then Cmd + Shift + V for every column for that week

11. Update sheet By Shipping Method By Week by adding one more row

    • Highlight the current entire last row, then highlight one more row, then press Cmd + D to fill down (same as copying down)

12. Checks

    • Make sure all the checks in Sales Summary are GOOD

    • Make sure sheet Orders by Business Line has the orders from the recent week

      • And if you had to run it twice on Monday, remove the duplicates, this is for Shopify only

    • Check the sheet Square Summary Check

13. Create the sheet Inventory Manual Count by clicking the Create Sheet button to clear and update that sheet

14. Update PNL for each location with weekly totals - data is in By Shipping Method By Week sheet in columns AH:AP