Laravel 8Content operationsWordPress + TrelloPayment logic

Writer payments that calculate correctly, even when the systems do not talk to each other

This content-operations dashboard connects Google Sheets, Trello, Google Docs, WordPress, analytics, and payment rules into one traceable workflow. It replaces manual cross-checking with a record that shows where every article and amount came from.

The key protection: old months are not silently recalculated after a writer rate changes.

See the workflow Discuss a project like this

At a glance

Built to reconcile, not just report

The system connects source data, human workflow, payment rules, publishing, and performance history in one operating record.

configured web properties

The analytics work spans five configured view and property pairs.

reporting windows

Traffic is kept for 30, 90, 180, and 365-day windows.

Hourly

editorial refresh

Trello work is refreshed on a regular schedule rather than only when someone remembers.

1 min

publishing check

The scheduler selects no more than one eligible WordPress publication at a time.

The problem

A payment decision can depend on four disconnected places.

One name variation, missing document, changed rate, or half-finished publication can turn ordinary editorial work into manual reconciliation and payment risk.

The approach

Build one record that can explain itself.

The dashboard builds a local audit trail from the tools people already use, automates the repetitive joins, and leaves room for human judgment where it matters.

How it works

From editorial source to payment, publication, and performance

Each stage has an input, a saved result, and a clear exception path.

Start with the records the team already uses

Starts when

The scheduled Sheets refresh reads the configured current, prior-month, and historical source tabs.

What happens

It finds the author, submission date, title, and linked Trello card needed to connect the rest of the work.

You get

A traceable candidate record that can be matched to an article instead of treated as an isolated spreadsheet row.

If it fails

A missing or unrecognized card is recorded for follow-up while the rest of the payment run continues.

Find the document and count the work correctly

Starts when

A valid Trello card is available for the article.

What happens

The system finds the linked Google Doc, reads its content, and calculates the word count from the document itself.

You get

A local article record stores the author, card, document, title, date, and computed word count together.

If it fails

If the document cannot be found, the item becomes an operations notification instead of an invented pay record.

Apply the pay rule that was true for that month

Starts when

Article records and Trello labels are refreshed, or an administrator starts a refresh.

What happens

The dashboard connects labels, article-specific exceptions, monthly rates, salary snapshots, and qualifying premiums.

You get

A payout view can explain the agreed rate, count, salary, and bonus behind each amount.

If it fails

Duplicate bonus records are prevented and an authorized administrator can correct a specific article rate.

Publish one ready article at a time

Starts when

Trello refreshes identify eligible work, then the one-minute publishing scheduler selects one record that is ready.

What happens

The publisher checks credentials, converts the Google Doc, processes images, uploads media, creates the WordPress post, and maps the author.

You get

The record keeps the external post ID, status, timestamp, notes, and an operation log for that publishing session.

If it fails

Known errors are categorized, image uploads retry, stuck work recovers, and repeated alerts are suppressed.

Connect published work to its performance history

Starts when

Daily analytics refreshes run at noon Eastern and post-tracker refreshes run every three hours.

What happens

The dashboard collects website totals and post measures, then attaches newly found URLs to the matching WordPress post and author.

You get

A team can compare content performance over the 30-, 90-, 180-, and 365-day views.

If it fails

Analytics errors retry on a limit, and bad post URLs are classified rather than quietly accepted as real articles.

The business result

The team does not have to choose between trusting a spreadsheet and manually opening every outside system. A payment or publishing decision can point back to the source data and the rule that produced it.

How the reconciliation finds the right articleshow

The daily flow connects Google Sheets, Trello cards, Google Docs content, and local UserArticle records. It reads configured Raw Data and historical tabs, applies author aliases, finds the Trello card, and uses three HTTP retry attempts plus a delayed second lookup when a card is not returned initially.

The importer searches Trello card actions and attachments for a Google Docs URL. It recursively reads paragraphs, tables, and table-of-contents content before storing the computed word count.

It first-or-new upserts a UserArticle using the author and Trello card as identity, saving the document identifier, title, submitted date, and word count. Missing or unknown cards are recorded as errors; missing Docs are collected for an operations notification.

How pay stays correct

The rate for an old month stays the rate for that old month.

That matters whenever compensation changes, someone earns a premium, or an individual article needs an exception.

Application core

Laravel 8, PHP, Eloquent, controllers, Blade

One application holds the workflow records, protected payment views, and operational routes.

Editorial intake

Google Sheets, Trello boards, cards, labels, members

The automation works with the planning tools editors already use.

Document source

Google Docs API

The actual document supplies both the word count and the source for publishing.

Publishing target

WordPress REST API and protected AJAX endpoints

The system can create posts, upload media, map authors, refresh status, and verify credentials.

Measurement

Analytics Reporting API, GA4 Data API, local traffic history

Both analytics paths keep historical and newer reporting data available during a platform change.

State and operations

MySQL, Redis, Laravel Scheduler, queued jobs, schedule monitoring

Saved facts, visible progress, background work, and monitored schedules turn connected tools into an operating system.

How monthly compensation is preservedshow

The payment logic supports monthly per-word and fixed-salary snapshots. An article-level rate override can replace the monthly rate for a specific post, while stored salary and bonus records protect a past period from the current profile changes.

Trello labels are mirrored to local tables and reconciled with article-label links. The rules include configured $10 and $20 article bonuses, a separate hourly job that can add a $0.02 premium for qualifying labels, and monthly consistency and double-quota bonus records.

The bonus command uses first-or-create checks before inserting a monthly record. Authorized administrators can correct a single post rate through a protected route.

Why this is different from a live spreadsheet formula

A current rate change should help calculate future work, not quietly alter what the business owed someone last month. Saving the monthly agreement makes each payout reviewable and defensible later.

Automations and safeguards

Automation with an operational memory

The important work is not merely making calls between tools. It is knowing what has happened, recovering when work stalls, and notifying people about exceptions.

Old months stay true

Monthly per-word rates and fixed salaries are saved as snapshots. A post-specific rate can override the general monthly rate without rewriting history.

A stalled run does not stay invisible

The Sheets refresh records progress in Redis, and a five-minute watcher can clear work that exceeded the configured stuck threshold.

External calls are paced

Trello has three retry attempts, analytics retries are capped, and document or media calls include waits rather than becoming an uncontrolled request storm.

Publishing does not race itself

The one-minute publisher selects a single record, keeps a heartbeat while working, and resets scheduled records left active for more than 60 minutes.

Exceptions reach the right person

Missing documents, revoked access, bad credentials, and publisher errors become saved alerts and notifications, with 24-hour duplicate suppression for recurring failures.

How publishing avoids overlap and recovers from hangsshow

Content operations include hourly Trello refreshes and one-at-a-time publishing runs every minute. The publisher validates Google Docs and WordPress access, converts the document to HTML, processes images, uploads media, creates the post through the WordPress REST API, and attempts to set the mapped author.

It retains post ID, posted status, timestamp, publisher notes, and an operation log. Known errors include missing document, bad WordPress credentials, and API error; image-upload loops retry up to five times.

A selected record is marked as active and receives a publish heartbeat. Scheduled records left processing for more than 60 minutes are reset, while repeat alert messages are suppressed for 24 hours.

How analytics and job progress are kept recoverableshow

Traffic data comes from both the Google Analytics Reporting API and the GA4 Data API across five configured properties. The system stores website totals, daily history, page views, unique views, engagement time, and rate data over 30, 90, 180, and 365 days.

Analytics wrappers catch exceptions, wait 30 seconds, and retry up to two times. A WordPress lookup fills cached post and author data when a new URL appears, while invalid post URLs are explicitly classified.

Scheduled work has overlap controls, retry behavior, missing-document notifications, and Redis tracking for sync progress and stalled-job recovery. The Sheets refresh sets in-progress and timestamp keys; a five-minute watcher clears a process beyond its stuck threshold.

What protects the team on a busy day

The system does not treat a scheduled job as magic. It remembers active work, limits duplicate publishing, retries selected problems, names the error when it cannot continue, and gives someone a way to investigate before the issue spreads.

What it replaced

From cross-tool detective work to a controlled operating record

No time-saved claim is invented here. The gain is fewer manual joins, clearer exception ownership, and a stronger basis for payment and publishing decisions.

Before

Sheets, Trello cards, Docs, and WordPress records must be manually compared before paying or publishing.

With this approach

One article record joins the author, card, document, date, word count, labels, rate rules, and publishing status.

Before

A later rate change can blur what was agreed for earlier work.

With this approach

Monthly rate and salary snapshots plus stored bonuses give every reporting period a defensible baseline.

Before

A publishing problem leaves people guessing whether the document, image, credentials, or target site failed.

With this approach

Status, error records, heartbeat recovery, notes, logs, and notifications make the exception visible and diagnosable.

Before

Traffic reporting lives apart from editorial history.

With this approach

Post identity, author, publication timing, and analytics history are kept together for follow-up.

What this means for your business

Reconcile, preserve, and act.

Your tools may not be Trello and WordPress. This pattern still applies whenever a human workflow, a financial rule, and several outside systems must agree on the same record.

Reconcile work orders, deliverables, and compensation across an agency or contractor network.
Build a controlled publishing or approval process that connects a planning board to the content system your team already uses.
Preserve historical commission, fee, or rate agreements when operating rules change over time.
Make recurring external-system failures visible through status and targeted notifications instead of spreadsheet detective work.

Plain-language glossary

Every technical term used on this page, explained without jargon.

API: A controlled way for software systems to request and exchange information.
GA4 Data API: The Google Analytics 4 connection used to request current analytics data.
Google Analytics Reporting API: A legacy Google Analytics connection used for historical reporting data.
Redis: A fast temporary data store used here to track job progress and recovery state.
REST API: A common web interface that lets software create, read, or update information.
Snapshot: A saved version of a value that protects what was true at a particular time.
Trello: A visual work-management tool built around boards, lists, cards, and labels.
Upsert: Save a record by creating it when absent or updating it when it already exists.
WordPress CLI: A command-line tool for checking and managing WordPress installations.

Need your operations tools to agree with each other?

We can map the handoffs creating manual work, payment risk, or publishing uncertainty and design the right level of automation around them.

Discuss a project like this