Processing Automated Client Reports (Mid-Terms & Renewals)

Purpose

To define the complete end-to-end data processing workflow for generating both Mid-Term Client Reports and Client Renewal Reports. This SOP standardizes how data flows from WordPress via WP Sheet Editor, native WordPress post screens, Meta Business Suite, and the Master Social Schedule directly into the dedicated Gemini Report Gems (Mid-Term Report Gem and Renewal Report Gem).

Step 1: Establish Report Parameters & Date Windows

Open the client's record in your CRM or renewal tracker to confirm the baseline parameters before querying data:

• Client Business Name: (e.g., King Locksmiths, Forrest Funeral Services, Northern Arena)
• Client Plan Type: (Standard, Deluxe, or Premium)
• Reporting Window:
  • Mid-Term Report: Start Date to 3-Month / 6-Month Mid-Point Date (e.g., 20 Nov 2025 to 20 Feb 2026)
  • Renewal Report: Full 12-Month Contract Term (e.g., 10 March 2025 to 10 April 2026)
• Donation Programme Group: Beneficiary school, club, or charity (if applicable)

Step 2: Query Live Links via WP Sheet Editor

WP Sheet Editor isolates dynamic client URLs in seconds without manual front-end site browsing:

1. Blog Posts:
   • Navigate to WP Sheet Editor > Edit Posts.
   • Click Search $\rightarrow$ Check Enable advanced filters.
   • Set search query:
     • Keyword / Title contains: [Client Name]
     • Date filter: post_date $\ge$ [Start Date] AND $\le$ [End Date]
   • Copy the headline(s) and URL(s) from the View column.
2. Classified Adverts:
   • Navigate to WP Sheet Editor > Edit Classifieds.
   • Run the same search query (Client Name + Date Window).
   • Copy the classified headline(s) and URL(s) from the View column.
3. Directory Listings & Category Pages:
   • Navigate to WP Sheet Editor > Edit Business Listings (or Edit Pages).
   • Search [Client Name] and copy their primary listing URL from the View column (e.g., [https://linku2hibiscuscoast.co.nz/business_listings/](https://linku2hibiscuscoast.co.nz/business_listings/)...).
   • Copy the URLs for the specific directory category pages they appear in (e.g., Property | Security, Business | Business Supplies and Services).
4. Features & Donation Programme (For Renewal Reports):
   • In Edit Pages, copy the links for:
     • Local Business Donation Support Page & Acknowledgement listing
     • Themed Feature hub pages they participated in (e.g., Home Sweet Home, Who You Gonna Call?)

Step 3: Record Post Hits & Site Views

1. Blog Posts & Classifieds Hit Counts (Native WordPress):
   • In the standard WordPress left admin menu, open Posts > All Posts and Classifieds > All Classifieds.
   • In the top-right search box on each screen, type [Client Name].
   • Transcribe the hit counts shown in the native Views column (powered by Post Views Counter) for each client item.
2. Onsite Listing & Category Hits (Analytics / Counter):
   • Look up the reporting period view counts from your analytics/counter tool for:
     • Client main listing page
     • Active directory category pages
     • Logo advert display pages / Sticky top-of-page adverts
     • Donation Programme map listing
     • Direct website clicks (if tracked)

Step 4: Extract Social Media Promotions & Reach

1. Query the Master Sharing Schedule:

• Open [Year] Master Sharing Schedule.xlsx.
• Locate the client’s promotional columns across the relevant monthly tabs by checking Row 4 (Business or Title).
• Extract:
  • Date: from Row 2
  • Platform / Code: from Row 7 (Page posted on)
  • Live URL: from Row 6 (Post Link)

2. Crossover Post Integrity:

• For posts continuing into a new month, pull reach metrics solely from the originating tab (refer to the Managing Monthly Social Schedules & Crossover Posts micro-guide).

3. Retrieve Reach Metrics via Meta Business Suite:

• Go to Meta Business Suite > Insights > Content and export the performance CSV for the designated reporting window.
• Populate Row 7 (Reach / Impressions) in the schedule for each client post.
• Calculate the total social reach figure to feed into the Gem prompt or spreadsheet template.

Step 5: Check Google Keyword Rankings & Site Totals

1. Run an incognito browser search or open your rank tracker for the client's agreed target phrases.
2. Record their current organic positions (e.g., Hibiscus Coast security services – No. 1, Hibiscus Coast locksmiths – No. 2).
3. For Renewal Reports, record site-wide totals for the period (Site Visitors, Page Views, Google Search Impressions, and Clicks).

Step 6: Process via Custom Gemini Report Gems

Open Gemini and select the dedicated Gem matching the report type:

Option A: Running a Mid-Term Report

1. Select the Mid-Term Report Gem.
2. Attach the client's gathered stats/spreadsheet and prompt: "Please generate the Mid-Term Report for [Client Name] based on the attached spreadsheet data. Write out the full report directly in this chat, embed all links into the text, and generate customized Insights & Recommendations for each section, along with a Looking Ahead strategy focused on the second half of their current term."

Option B: Running a Renewal Report

1. Select the Renewal Report Gem.
2. Attach the client's gathered stats/spreadsheet and prompt:"Please generate the Client Renewal Report for [Client Name] based on the attached spreadsheet data. Embed all direct links into the wording, calculate the full Hits Summary, provide customized Insights & Recommendations for each section, and draft the Looking Ahead strategy for their upcoming renewal term."

Step 7: Final Assembly, Review & Dispatch

1. Verify Report Content:
   • Ensure arithmetic in the Hits Summary accurately matches the itemized breakdowns.
   • Confirm all URLs are functioning markdown links.
   • Confirm the closing text matches the report type:
     • Mid-Term: Requests keyword/content feedback for the remainder of their active term.
     • Renewal: Notes that their renewal invoice will issue under separate cover.
2. Insert Online Advert Proofs:
   • Paste the report text into your final client document or email template.
   • Under YOUR ONLINE ADVERTS, paste current screenshots of their active banners/logos (e.g., Sticky Top-of-Page Advert, Side Scrolling Logo).
3. Dispatch via Fluent CRM:
   • Send the report using the corresponding email template (Mid-Term Report or Renewal Notification).
   • If Renewal, issue the invoice under separate cover.
4. Schedule 7-Day Follow-Up:
   • Set a diary reminder for 7 days to review client feedback, confirm renewal terms, and initiate the 3-Monthly Schedule Procedure.

Old Procedure below to be deleted once above confirmed -

This reporting process relies entirely on accurate, up-to-date client history. For instructions on how to properly log daily client links and statistics into the CRM to prepare for these reports, please review the Linku2 Weekly Scheduling & Daily Execution procedure.

The Core Rule: Boards vs. CRM

Before processing any reports, you must remember the fundamental rule of our system:

• Boards are for Action: If it is a task to do today, it lives on the Board.
• The CRM is for History: If it is a record of what has been done, it lives in FluentCRM.

1. Processing Mid-Term Reports

When a client reaches their mid-term milestone, the CRM automatically creates a brand new, temporary task directly in your "Mid-Term Report Due" column.

• Step 1: Spot the Task. You will see the newly spawned task in your "Due" column. The client's original Master Card sits untouched as a permanent visual roster in your Live and Scheduled column. You do not need to touch the Master Card.
• Step 2: Gather the History. Open the client's profile in FluentCRM. Read through their logged notes and tags to see exactly what activities and campaigns have been completed for them. You will have every single piece of data you need to write a Mid-Term or Renewal report in one single window.
• Step 3: Process the Report. Perform the report work using the CRM data, and send it to the client.
• Step 4: Clear the Board. Because the Master Card is safely sitting in your active column, you simply mark this temporary, spawned task as "Completed" (or delete it to keep the board clean).

2. Processing Renewal Reports

Renewals follow the exact same automated process but require one vital final step to restart the client's contract for the following year.

• Step 1: Spot the Task. The CRM automatically creates a brand new, temporary task directly in your "Renewal Due" column.
• Step 2: Gather the History. Review their FluentCRM profile for their complete annual history.
• Step 3: Process the Report. Perform the report work and send the renewal details to the client.
• Step 4: Clear the Board. Mark this temporary, spawned task as "Completed" (or delete it to keep the board clean).
• Step 5: Reset the Clock (Crucial). Once the client officially signs on for another year, go into FluentCRM and re-apply the Status: Automation Ready tag. This resets their automated clock to zero for their next contract cycle.

Quick Reference Summary

Milestone	Where Task Appears	Action Required	Post-Report Step
Mid-Term	Mid-Term Report Due stage	Write report using FluentCRM history	Complete or delete the spawned task
Renewal	Renewal Due stage	Write report using FluentCRM history	Complete task AND re-apply Status: Automation Ready tag in CRM