1. Go into eSAT and pull a report for the last 3 years of sales for the shop.

2. Export the data to an excel file

   1. Change Title to TFID - Shop Name - Date

   2. Import file: File>Import>Upload>Replace current Sheet

   3. Delete first 2 rows

   4. Split date Data>Split text to columns> Custom >/

   5. Change the first 3 columns headings to Month Day Year

   6. Select all > CTRL A

   7. Insert Pivot table> New sheet>Create

3. Making the pivot table

   1. Add Year and Month to Rows

   2. Add Visitors, Orders, Total Sales, Average Order Value, Conversions

   3. Change SUM to AVERAGE on AOV an COnversions

   4. Add MONTH to the filter, and filter out the months that do not have data for the current year.

The Pivot Table will be created now.

• Make sure to check Data Month Over Month to identify possible drops in performance for holidays or spot 

4. Copy the chart, paste it in the following prompt and run the prompt through Chat GPT 

   1. Replace the chart with the pivot table data that we just created.

   2. Edit the first paragraph in the prompt to match your information (edit underlined parts)

   3. Paste the prompt in Chat GPT, don’t run it yet. 

5. Generate a GA4 report, copy the picture and add it to the CHat GPT prompt. Now your prompt is ready to run. 

   1. The output will give us the YOY breakdowns

   2. We can copy these YOY breakdowns and insert it into our Presentation.

   3. Take snippets of our Pivot Tables and other supporting assets and place them into the Presentation.

Change all of the Pink Data for the prompt. 

ChatGPT Prompt

Prompt:

I have the following website performance metrics and sales data for the years 2021 to 2024, year over year excluding the months of November until December so the timeline is comparable for all of the years in the same time period. I am also adding a GA4 performance metrics with traffic source data to analyze further traffic gain and loss insights. 

Add YOY table

 Additionally, I am providing MOM data for the last 2 years. Identify any trends and important information to notice. 

Add MOM table

Add snapshot of GA4

Please provide a performance snapshot and analysis for these metrics, including:

1. Summary of visitors and orders with year-over-year (YOY) changes.

2. Summary of conversion rates and total sales with YOY changes.

3. Summary of average order value (AOV) with YOY changes.

4. Key observations.

5. Analyze source traffic data. 

6. Contextual comparison with industry trends.

7. Summary of observations of MOM data from the last 2 years.

8. Recommendations for improvement.

Format the output similarly to the example below:

Performance Snapshot (2021-2024)

Visitors and Orders

• 2021: 30,761 visitors, 3,521 orders

• 2022: 26,410 visitors, 3,472 orders

• 2023: 25,135 visitors, 2,871 orders

• 2024: 11,519 visitors (partial year), 1,272 orders (partial year)

Year-over-Year (YOY) Changes:

• 2022 vs 2021: Visitors down -14.2%, Orders down -1.4%

• 2023 vs 2022: Visitors down -4.8%, Orders down -17.3%

• 2024 vs 2023: Data is partial, but current figures show a significant decline.

Conversions and Total Sales

• 2021: 11.36% conversion rate, $296,704.23 total sales

• 2022: 12.80% conversion rate, $309,693.63 total sales

• 2023: 11.53% conversion rate, $271,538.62 total sales

• 2024: 10.96% conversion rate, $123,582.52 total sales (partial year)

YOY Changes:

• 2022 vs 2021: Conversion rate up +1.44 percentage points, Total sales up +4.4%

• 2023 vs 2022: Conversion rate down -1.27 percentage points, Total sales down -12.3%

• 2024 vs 2023: Data is partial, but conversion rate and total sales show a declining trend.

Average Order Value (AOV)

• 2021: $84.51

• 2022: $88.11

• 2023: $93.95

• 2024: $93.28

YOY Changes:

• 2022 vs 2021: AOV up +4.3%

• 2023 vs 2022: AOV up +6.6%

• 2024 vs 2023: AOV down -0.7% (partial year)

MOM Trends and Key Observations:

[Add analysis of MOM data of last 2 years]

Key Observations:

1. Declining Traffic: There is a consistent decline in the number of visitors each year, indicating a need for improved marketing and customer acquisition strategies.

2. Fluctuating Orders: Orders remained relatively stable in 2022 compared to 2021 but saw a significant drop in 2023 and 2024, suggesting potential issues in attracting and retaining customers.

3. Conversion Rate Variability: The conversion rate increased in 2022 but decreased in subsequent years, indicating potential issues in the customer conversion process.

4. Total Sales Growth and Decline: Total sales grew in 2022, but decreased significantly in 2023 and 2024, reflecting the decline in orders.

5. Average Order Value Increase: AOV has been increasing, except for a slight decrease in 2024, showing that customers are spending more per order, which is a positive trend.

6. Traffic source Data: Organic search seems to be the driver for the performance fluctuations indicating there might be a Search visibility issue or keyword ranking affecting the website. 

Contextual Comparison:

• In 2023, total visits and orders showed a downward trend, with a notable decline in visitors and orders. The trend in 2024 is even more pronounced, though the data is partial.

• Compared to industry averages, where eFlorist websites experienced flat traffic YOY, the visitor trend for your website showed a more significant decline.

Recommendations:

1. Revamp Marketing Strategies: Enhance marketing efforts to attract more visitors, particularly focusing on months with traditionally lower traffic.

2. Improve Conversion Funnel: Optimize the website's user experience and conversion funnel to increase the conversion rate.

3. Engage and Retain Customers: Implement customer engagement strategies and loyalty programs to retain existing customers and encourage repeat purchases.

4. Investigate Declining Trends: Analyze the reasons behind the decline in visitors and orders in 2023 and 2024, addressing any underlying issues.

5. Leverage AOV Growth: Continue to offer value-added products or services to capitalize on the increasing AOV.

If you need a more detailed analysis or specific action plans, feel free to ask!

6. Copy and paste your generated answer in a word document where you can edit and copy and paste the information needed to complete the presentation.