Analytics & the reporting pipeline

The analytics dashboards do not query the CRM live. A scheduled ETL pulls lead / meeting / sale records out of the CRM (through crms-middleware) once a day and writes them into local analytics-mirror tables — analytics_broker_* and analytics_salesman_* — keyed to a shared date-dimension table, dates_dm. The broker, sales-man and sales-manager dashboards then read those mirror tables at request time. The shopper-admin surface is the exception: it reads the shopper platform’s own operational tables plus live Google Analytics (GA4), never the CRM mirror.

Read-side is always local — the CRM is only touched by the nightly sync

None of the four analytics controllers call crms-middleware at request time. The three CRM-backed surfaces query pre-computed local mirror rows; shopper-admin queries local sales/lead tables and GA4. So a dashboard number is only ever as fresh as the last successful sync run (daily) — not the CRM right now. That single fact explains most “the dashboard is behind the CRM” questions.

The four surfaces at a glance

Surface	Endpoint prefix	Reads from	Query layer
Broker	api/broker/analytics/* (routes/api/broker.php:161-185)	AnalyticsBrokerLead / AnalyticsBrokerMeeting / AnalyticsBrokerSale + local SalesInvoice (commissions)	app/Actions/Analytics/{Lead,Sale,Filters}/*
Sales-Manager	api/sales-manager/analytics/* (routes/api/sales-manager.php:22-46)	The same broker mirror — AnalyticsBrokerSale etc. (rollup / rankings view)	the shared app/Actions/Analytics/Sale/* actions
Sales-Man	api/sales-man/analytics/* (routes/api/sales-man.php:230-267)	AnalyticsSalesManLead / AnalyticsSalesManMeeting / AnalyticsSalesManSale / AnalyticsSalesManSaleSplit	app/Actions/SalesMan/Analytics/{Sales,Lead,Meetings,Filters}/*
Shopper-Admin	api/shopper-admin/analytics/* (routes/api/shopper-admin.php:16-126)	No CRM mirror — local UnitSale / UserRequest / Shopper / SearchableLog plus live GA4 for the ggl-* endpoints	app/Actions/Analytics/{UnitSale,UserRequest,User,Unit,GoogleAnalytics}/*

All prefixes are mounted in app/Providers/RouteServiceProvider.php:142-165 (api/broker, api/sales-man, api/sales-manager, api/shopper-admin), each behind user-provider:<persona> + auth:sanctum.

Sales-Manager is a rollup over the broker mirror, not its own dataset

There is no analytics_salesmanager_* table and no sales-manager sync command. The sales-manager controllers live in the SalesManager namespace but call the same App\Actions\Analytics\Sale\* actions the broker uses (e.g. BrokeragesRanking, SalesManagersRanking, BrokersRankingData, GetInternationalSalesStats, CountPerDestination), all of which query AnalyticsBrokerSale. It is a management lens over the broker data, populated by the broker sync.

The whole pipeline, in one diagram

sequenceDiagram
participant SCH as Scheduler (Kernel)
participant Q as Queue worker
participant ETL as ETL actions (Extract / Transform / Load)
participant MW as crms-middleware to CRM
participant DB as Portal DB (analytics_* mirror + dates_dm)
participant GA as Google Analytics (GA4)
actor CRMD as Broker / Sales-Man / Sales-Manager dashboards
actor SA as Shopper-Admin dashboard
Note over SCH,DB: monthly app:seed-dates-dimension pre-seeds dates_dm calendar rows
SCH->>Q: daily dispatch broker-leads / broker-meetings / salesman-* sync
Q->>ETL: run the matching Sync*Crm handle
ETL->>MW: GET /api/portals/leads/sync with OData filter on createdon or modifiedon
MW-->>ETL: raw CRM rows (a broker lead carries a nested sales array)
ETL->>DB: resolve dates via dates_dm plus broker / unit / currency by source_id
ETL->>DB: updateOrCreate keyed by (source_id, country_id) so replays are idempotent
Note over DB: analytics_broker_* and analytics_salesman_* now populated
CRMD->>DB: read the mirror tables at request time (never the live CRM)
SA->>DB: read local unit_sale / user_requests / searchable_logs
SA->>GA: ggl-* endpoints fetch live GA4 page views on every request

CRM → scheduled ETL → analytics mirror + dates_dm → the four dashboards (verified)

The date dimension: dates_dm

Every analytics-mirror table stores its dates as integer foreign keys to dates_dm — columns like submitted_at_dm, verified_at_dm, reserved_at_dm, canceled_at_dm, closed_at_dm, meeting_at_dm. DateDM (app/Models/DateDM.php:10-14, table dates_dm, no timestamps, non-incrementing) is a classic date-dimension: one row per calendar day with id in Ymd form (e.g. 20260701), plus date, year, month, day, month_name.

It is filled ahead of time by the monthly command app:seed-dates-dimension (app/Console/Commands/analytics/SeedDatesDimension.php:29-75), which runs a recursive-CTE INSERT into dates_dm. On an empty table it seeds 2018-01-01 → end of the current year; on subsequent runs it appends the next ~30 days after the last row. The mirror models point at it via belongsTo(DateDM::class, '<column>_dm') (e.g. AnalyticsBrokerSale.php:40-58), and date-range filters run against these integer *_at_dm columns (e.g. AnalyticsBrokerLead::whereBetween($attrDate, [from, to]), app/Actions/Analytics/Lead/Count.php:23-25).

If dates_dm is not seeded far enough, new *_at_dm keys dangle

The *_at_dm values are computed by Util::formatDate(...) into Ymd ints during transform. If the monthly seed hasn’t reached that calendar day yet, the mirror row still stores the int but the DateDM join has nothing to resolve — dimension-joined reports quietly drop or mis-bucket those rows. Confirm app:seed-dates-dimension is on the schedule and has run (Kernel.php:28).

The sync mechanism (Extract → Transform → Load)

1. The scheduler fires the sync commands daily. app/Console/Kernel.php:28-35 schedules app:seed-dates-dimension monthly, then broker-leads:sync, broker-meetings:sync, salesman-leads:sync, salesman-meetings:sync, salesman-sales:sync and salesman-sales:sync-split daily. (Two commands exist but are not scheduled — see Gotchas.)

2. Each command decides backfill vs incremental, then dispatches queued jobs. Take broker-leads:sync (app/Console/Commands/analytics/Broker/SyncLeads.php:33-69): it loops the target countries (Country::whereIn('slug', CountryEnums::values()); the sales-man commands are Egypt-only, e.g. SalesMan/SyncLeads.php:36). For each country it checks the mirror — AnalyticsBrokerLead::where('country_id', ...)->orderByDesc('created_at')->first(). If no row exists, it dispatches a first-time backfill: one job per half-month across a hard-coded year list (2019–2023 for broker leads, 2023–2025 for broker meetings, 2024–2025 for sales-man). If a row does exist, it dispatches a single incremental job from that row’s created_at onward with an empty end date (:63).

3. The queued job hands off to the ETL action. SyncLeadsCrmJob (app/Jobs/Broker/SyncLeadsCrmJob.php:43-54, implements ShouldQueue) just calls SyncLeadsCrm::handle($country, $start, $end). Sales-man has a parallel job/action tree under app/Actions/SalesMan/Analytics/SyncData/{Leads,Meetings,Sales,SalesSplit,SystemUsers}/.

4. Extract — pull raw rows from the CRM via the middleware. ExtractLeadsCrm.handle (app/Actions/Analytics/SyncData/ExtractLeadsCrm.php:15-51) does a GET {CRMS_MIDDLEWARE_BASE_URL}/api/portals/leads/sync through CrmMiddlewareClient (OAuth token, cached; authenticate() hits services.crms-middleware.auth_url). The country is passed as a country header and the window as an OData-style filter: createdon ge <start>T00:00:00Z, plus or modifiedon ge <start>... when the end is empty (so an incremental run also catches updated rows), else and createdon le <end>T23:59:59Z.

5. Transform — map raw CRM JSON into mirror rows, and fan leads out into sales. TransformLeadsCrm.handle (app/Actions/Analytics/SyncData/TransformLeadsCrm.php:16-135) builds one analytics_broker_leads row per lead and explodes each lead’s nested sales[] array into analytics_broker_sales rows (transformSales, :63-116). It converts every date to an Ymd int (Util::formatDate), resolves the broker_id by country + CRM source id, resolves unit / unit_type / destination from local tables by source_id, resolves currency, and computes is_international from the buyer’s country code. Per-row try/catch — a single bad lead or sale just increments a failed counter and is logged.

6. Load — idempotent upsert into the mirror. LoadLeadsCrm.handle (app/Actions/Analytics/SyncData/LoadLeadsCrm.php:12-35) calls AnalyticsBrokerLead::updateOrCreate(['source_id' => ..., 'country_id' => ...], $row), and LoadSalesCrm does the same for sales. The (source_id, country_id) key makes the whole pipeline safe to replay — re-running a window overwrites, never duplicates. Each loader also wraps every row in try/catch and returns a failedCount so SyncLeadsCrm::handleResponse (SyncData/SyncLeadsCrm.php:53-97) can log “synced N successfully” per batch.

How each surface queries the data

1. Broker — controllers under app/Http/Controllers/Api/Broker/Analytics/ delegate to single-purpose actions that query the broker mirror directly: leads via App\Actions\Analytics\Lead\Count → AnalyticsBrokerLead::whereBetween(...) (Lead/Count.php:23), sales via App\Actions\Analytics\Sale\* (CountPerDestination, BrokersRankingData, GetInternationalSalesStats, CountDailyPerUnitType, …) → AnalyticsBrokerSale. Commissions read the separate local SalesInvoice table (joined to AnalyticsBrokerSale).

2. Sales-Manager — controllers under .../SalesManager/Analytics/ reuse the same App\Actions\Analytics\Sale\* actions (BrokeragesRanking, SalesManagersRanking, BrokersRankingData, GetInternationalSalesStats, CountPerDestination) plus SalesInvoicesController for broker-commission rollups. Same AnalyticsBrokerSale mirror, a manager-level lens.

3. Sales-Man — controllers under .../SalesMan/Analytics/ delegate to app/Actions/SalesMan/Analytics/{Sales,Lead,Meetings,Filters}/*, which query the sales-man mirror: AnalyticsSalesManSale (overview, deals-vs-closed, sale-source / pipeline / category breakdowns), AnalyticsSalesManSaleSplit (agent ranking via the analytics_salesman_sale_splits pivot, AnalyticsSalesManSale.php:36-44), AnalyticsSalesManLead and AnalyticsSalesManMeeting.

4. Shopper-Admin — does not touch the CRM mirror. Its controllers under .../ShopperAdmin/ split two ways:

   • Local operational tables — UnitSale (the portal’s own closed sales, e.g. App\Actions\Analytics\UnitSale\CountSoldUnits reads unit_sale.closed_at_dm, UnitSale/CountSoldUnits.php:14), UserRequest (leads / service requests, App\Actions\Analytics\UserRequest\CountRequests), Shopper (user counts), and SearchableLog / UnitsFilterLog (search behaviour).
   • Live GA4 — every ggl-* / demographics / sessions endpoint calls App\Actions\Analytics\GoogleAnalytics\*, which use Spatie\Analytics\Facades\Analytics (FetchDestinationPageViews.php:23 → Analytics::fetchVisitorsAndPageViews(...)) to hit the live GA4 Data API configured in config/analytics.php (property_id + service account). There is no local mirror for these — they are fetched on every request.

Shopper-admin ggl-* numbers are live GA4, not the nightly mirror

Endpoints like analytics/destinations-ggl-views, analytics/customer-behavior/ggl-most-views-breakdown, analytics/demographics/ggl-page-views-per-country and analytics/logins/sessions-breakdown (routes/api/shopper-admin.php:40,91,111,59) call Google Analytics live on each request. So they have a different freshness and a different failure mode from the rest of the platform: if GA4 auth or the property id is misconfigured, or Google is slow / rate-limiting, those cards fail or hang even though every mirror-backed number is fine. They match on page title (stripping the Orascom Shopper | prefix, FetchDestinationPageViews.php:47), so a renamed page silently stops matching.

Gotchas

• Numbers are only as fresh as the last sync run. All three CRM-backed surfaces read the mirror, and the mirror is refreshed daily by the scheduled commands. If the scheduler or the queue worker isn’t running, the commands dispatch nothing / the jobs never execute and every broker / sales-man / sales-manager dashboard silently shows stale data with no error. Check both the cron (schedule:run) and queue:work.

• Sales-man analytics is Egypt-only by construction. salesman-leads:sync, salesman-sales:sync, salesman-sales:sync-split all resolve countries via Country::whereIn('slug', [CountryEnums::EGYPT->value]) (SalesMan/SyncLeads.php:36). Broker sync spans all CountryEnums::values(). A non-Egypt sales-man dashboard will be empty because nothing is ever synced for it.

• Two sync commands exist but are NOT scheduled. broker-leads:sync-all (Broker/SyncAllLeads.php:18, a full multi-year backfill) and salesman-systemusers:sync (SalesMan/SyncSystemUsers.php:19) are not wired into Kernel::schedule() — they only run when invoked manually. Don’t assume system-users or the wide backfill self-heal on the daily cron.

• First-time backfill is bounded by hard-coded year lists. The “no mirror row yet” branch iterates literal year arrays (2019–2023 for broker leads, 2023–2025 for broker meetings, 2024–2025 for sales-man, SyncLeads.php:38). Records outside those windows are never backfilled by the daily command; a genuinely full historical load needs broker-leads:sync-all.

• The broker “leads” endpoint feeds two mirrors at once. One CRM extract populates both analytics_broker_leads and analytics_broker_sales, because each lead carries a nested sales[] (TransformLeadsCrm.php:63-116). A lead that fails to transform takes its sales with it (they share the same try/catch), so a spike in “failed leads” can also silently depress sales counts.

• Load failures are swallowed per row. Both loaders catch TypeError | Exception per record, log, and continue (LoadLeadsCrm.php:27-31). This keeps a bad row from aborting the batch — but it also means a systematically broken mapping (e.g. an unresolved broker_id) shows up only as a rising failedCount in the logs, never as a hard failure. Grep laravel.log for Failed to load lead with source_id / Failed to transform when a surface looks under-counted.

• Shopper-admin is a genuinely different data source. It is the one surface that never reads the CRM analytics mirror: local unit_sale / user_requests / searchable_logs for the transactional cards, and live GA4 for the ggl-* cards. Don’t debug a shopper-admin discrepancy against the sync commands — they don’t touch it.

Where to go next

Broker submits a leadHow a broker lead is created in the CRM — the upstream of the analytics_broker_leads / _sales mirror.

Sales-man closes a saleThe sales-man deal path that feeds analytics_salesman_sales and the split table.

Commissions & sales invoicesThe SalesInvoice side the broker / sales-manager commission analytics join against.

Models & enumsThe full analytics-mirror model / DateDM / CRM-status enum reference.