2 Part Consultation
Get a quick assessment and overview of the account information and the account's performance from SharePoint, GA4 Report, and Ahrefs (10 - 15 min)
Ask the florist about the nature of the inquiry to see why they requested an audit and the florist's perception of performance.
Why are you requesting a review?
What are you concerned about?
What specific metrics are you looking for?
Answer any specific immediate questions they might have.
Inform the florist it may take 5 - 8 business days and make the next appointment.
Go into eSAT and pull a report for the last 3 years of sales for the shop.
Export the data to an excel file
Change Title to TFID - Shop Name - Date
Import file: File>Import>Upload>Replace current Sheet
Delete first 2 rows
Split date Data>Split text to columns> Custom >/
Change the first 3 columns headings to Month Day Year
Select all > CTRL A
Insert Pivot table> New sheet>Create
Making the pivot table
Add Year and Month to Rows
Add Visitors, Orders, Total Sales, Average Order Value, Conversions
Change SUM to AVERAGE on AOV an COnversions
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
Copy the chart, paste it in the following prompt and run the prompt through Chat GPT
Replace the chart with the pivot table data that we just created.
Edit the first paragraph in the prompt to match your information (edit underlined parts)
Paste the prompt in Chat GPT, don’t run it yet.
Generate a GA4 report, copy the picture and add it to the CHat GPT prompt. Now your prompt is ready to run.
The output will give us the YOY breakdowns
We can copy these YOY breakdowns and insert it into our Presentation.
Take snippets of our Pivot Tables and other supporting assets and place them into the Presentation.
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:
Summary of visitors and orders with year-over-year (YOY) changes.
Summary of conversion rates and total sales with YOY changes.
Summary of average order value (AOV) with YOY changes.
Key observations.
Analyze source traffic data.
Contextual comparison with industry trends.
Summary of observations of MOM data from the last 2 years.
Recommendations for improvement.
Format the output similarly to the example below:
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.
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.
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]
Declining Traffic: There is a consistent decline in the number of visitors each year, indicating a need for improved marketing and customer acquisition strategies.
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.
Conversion Rate Variability: The conversion rate increased in 2022 but decreased in subsequent years, indicating potential issues in the customer conversion process.
Total Sales Growth and Decline: Total sales grew in 2022, but decreased significantly in 2023 and 2024, reflecting the decline in orders.
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.
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.
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.
Revamp Marketing Strategies: Enhance marketing efforts to attract more visitors, particularly focusing on months with traditionally lower traffic.
Improve Conversion Funnel: Optimize the website's user experience and conversion funnel to increase the conversion rate.
Engage and Retain Customers: Implement customer engagement strategies and loyalty programs to retain existing customers and encourage repeat purchases.
Investigate Declining Trends: Analyze the reasons behind the decline in visitors and orders in 2023 and 2024, addressing any underlying issues.
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.
https://docs.google.com/presentation/d/11Bo_PbbcD4A5-gzjzvKDoFSTo6M8Cvbg0Pw0ABubXDg/edit?usp=sharing
Make your own copy of the presentation. File > Make a copy
Follow the prompt comments on the presentation.