agnav/agmission
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
master
agmission/Development/client/AgMission-BigQuery-Analytics-Mapping.md

44 KiB
Raw Permalink Blame History

AgMission BigQuery Analytics Mapping

Comprehensive Mapping of Analytics Questions to BigQuery Parameters

Overview

This document maps specific analytics questions from the AgMission role-based analytics framework to their corresponding BigQuery queries and parameters. It identifies which parameters are already available in the current GA4 events interface and specifies how to collect any missing parameters.

This document is based on:

  • Unified Event Reference: /client/AgMission-GA4-Complete-Reference.csv
  • Interface Definition: /src/app/shared/types/ga4-events.interface.ts
  • Implementation Status: Focus on E-commerce subscription tracking with correct package names

Table of Contents

  1. Available Parameters Inventory
  2. Event Structure Reference
  3. Role-Based Analytics Mapping
  4. E-commerce Analytics
  5. Missing Parameters Analysis
  6. Implementation Recommendations

Available Parameters Inventory

Current GA4 Event Parameters (From Interface)

Base Context (Available on All Events)

  • user_id - User identifier
  • session_id - Session identifier
  • user_role - User role (admin, applicator, office_admin, client, officer, pilot, inspector, aircraft)
  • subscription_tier - Subscription tier level (string: "1", "2", "3", "4", "5", "addon", "unknown")
  • app_version - Application version
  • platform - Platform type (web)

Job Management Parameters

  • job_type - Type of job (spraying, seeding, fertilizing, harvesting, soil_testing)
  • field_size_acres - Field size in acres
  • crop_type - Crop type
  • equipment_type - Equipment used
  • priority - Job priority (low, medium, high, urgent)
  • client_id - Client identifier
  • weather_dependency - Weather dependency boolean
  • creation_method - Job creation method (manual, template, duplicate)
  • estimated_duration_hours - Expected job duration in hours
  • job_status - Job status (new, ready, downloaded, sprayed, archived, invoiced)
  • efficiency_score - Job efficiency score
  • assignee_id - Assigned user ID
  • assignee_role - Assigned user role (pilot, applicator, officer, admin)
  • assignment_method - Assignment method (manual, auto, bulk)
  • assignment_lead_time_hours - Lead time in hours
  • fields_modified - List of fields changed in updates
  • change_magnitude - Magnitude of changes (minor, major)
  • edit_session_duration - Time spent editing in minutes
  • save_method - How update was saved (manual, auto_save)
  • deletion_reason - Reason for job deletion
  • deletion_method - How deletion was triggered
  • time_since_creation - Time between creation and deletion
  • old_status - Previous status before change
  • new_status - New status after change
  • status_change_reason - Reason for status change
  • completion_time - Time to complete job

Job List Operation Parameters

  • view_type - Type of list view (table, grid, map, calendar)
  • total_jobs - Total jobs available
  • displayed_jobs - Number of jobs shown
  • sort_by - Sort criteria
  • filter_count - Number of active filters
  • load_time_ms - List load time
  • client_filter_applied - Whether client filter is active
  • reload_interval - Auto-reload interval
  • filter_type - Type of filter applied
  • filter_value - Value of applied filter
  • results_before - Results count before filter
  • results_after - Results count after filter
  • filter_effectiveness - Filter effectiveness percentage
  • date_filter_type - Type of date filter
  • custom_date_range - Custom date range
  • selection_method - Method used to select job
  • position_in_list - Position of job in list
  • action_type - Type of bulk action
  • job_count - Number of jobs affected
  • job_ids - List of job IDs affected
  • execution_time - Time to execute action
  • success_rate - Success rate of action

File Management Parameters

  • file_type - File type (field_boundary, prescription_map, application_report, soil_map)
  • file_size_mb - File size in MB
  • related_job_id - Related job identifier
  • upload_source - Upload source (manual, drag_drop, bulk)
  • processing_time_seconds - Processing time
  • validation_status - Validation status (passed, failed, warning)
  • data_quality_score - Data quality score
  • automation_enabled - Automation status
  • error_type - Error type for failed uploads
  • error_message - Error message
  • retry_attempted - Whether retry was attempted
  • deletion_reason - File deletion reason
  • file_age_days - File age in days
  • confirmation_required - Whether confirmation was required
  • download_method - Download method
  • file_format - File format (original, converted)
  • download_source - Download source

Library Upload Parameters

  • upload_type - Type of library upload (field_areas, boundary_data, geographic_data)
  • file_count - Number of files uploaded
  • total_areas_uploaded - Total areas uploaded
  • duplicate_areas_found - Number of duplicates found
  • failed_files - Number of failed files
  • file_types - Array of file types
  • total_file_size_mb - Total file size
  • processing_method - Processing method (bulk, individual)

Report Parameters

  • report_type - Type of report (job_summary, financial_analysis, field_report, performance_dashboard)
  • report_id - Report identifier
  • date_range_days - Date range in days
  • jobs_included - Number of jobs included
  • generation_time_seconds - Report generation time
  • data_completeness - Data completeness percentage
  • view_duration_seconds - View duration
  • pages_viewed - Number of pages viewed
  • engagement_quality - Engagement quality (low, medium, high)
  • export_format - Export format (pdf, excel, csv)
  • render_duration_ms - Render duration
  • data_size_mb - Data size
  • complexity_score - Report complexity score
  • design_session_duration - Design session duration
  • modifications_made - Number of modifications
  • scroll_depth - Scroll depth percentage

Invoice Management Parameters

  • invoice_id - Invoice identifier
  • client_id - Client identifier
  • total_amount - Invoice amount
  • currency - Currency type (USD, CAD, EUR)
  • job_count - Number of jobs in invoice
  • creation_method - Creation method (manual, auto_generated, template, recurring)
  • due_date_days - Due date in days
  • payment_terms - Payment terms
  • fields_modified - List of modified fields
  • amount_change - Amount change
  • previous_status - Previous invoice status
  • current_status - Current invoice status
  • modification_type - Type of modification
  • invoice_status - Invoice status (new, draft, open, paid, void, uncollectible)
  • deletion_reason - Deletion reason
  • days_since_creation - Days since creation
  • had_payments - Whether invoice had payments
  • old_status - Previous status
  • new_status - New status
  • status_change_reason - Status change reason
  • days_in_previous_status - Days in previous status
  • payment_amount - Payment amount
  • payment_method - Payment method (cash, check, credit_card, bank_transfer, other)
  • payment_date - Payment date
  • remaining_balance - Remaining balance
  • payment_reference - Payment reference
  • days_to_payment - Days to payment
  • total_invoices - Total number of invoices
  • displayed_invoices - Number of displayed invoices
  • date_range_applied - Whether date range filter applied
  • status_filter_applied - Whether status filter applied
  • multiple_filters_active - Whether multiple filters active
  • invoice_amount - Invoice amount
  • total_amount_affected - Total amount affected by bulk action
  • view_source - View source (list, direct_link, search, navigation)
  • export_method - Export method (single, bulk)
  • file_size_kb - Export file size
  • includes_job_details - Whether export includes job details
  • settings_modified - List of modified settings
  • automation_enabled - Whether automation enabled
  • payment_terms_changed - Whether payment terms changed
  • billing_preferences_updated - Whether billing preferences updated
  • item_id - Item identifier
  • item_type - Item type (service, material, equipment, labor)
  • unit_type - Unit type (per_acre, per_hour, flat_rate, per_unit)
  • base_rate - Base rate
  • affects_existing_invoices - Whether affects existing invoices

Authentication Parameters

  • method - Login/signup method (email, google, microsoft, sso)
  • last_login_days_ago - Days since last login
  • session_duration_minutes - Session duration
  • logout_method - Logout method (manual, timeout, forced)
  • page_location - Page location at logout
  • signup_method - Signup method
  • user_type - User type (client, applicator, admin, office_admin)
  • source - Signup source (landing_page, referral, advertisement, direct)
  • invitation_code - Invitation code
  • company_name - Company name
  • signup_duration_minutes - Signup duration
  • profile_completed - Whether profile completed
  • verification_required - Whether verification required
  • email_address_hash - Hashed email address
  • request_method - Request method for password/email actions
  • user_exists - Whether user exists
  • reset_token_age_minutes - Reset token age
  • success - Whether action was successful
  • failure_reason - Failure reason
  • verification_token_age_minutes - Verification token age

Error Tracking Parameters

  • error_type - Error type (network_error, server_error, client_error, timeout, unknown_error)
  • http_status_code - HTTP status code
  • error_message - Error message
  • request_method - Request method (GET, POST, PUT, DELETE, PATCH, HEAD, OPTIONS)
  • request_url - Request URL
  • request_endpoint - Request endpoint
  • response_time_ms - Response time
  • affected_feature - Affected feature

Session & Performance Parameters

  • entry_page - Entry page
  • referrer - Referrer URL
  • page_title - Page title
  • load_time_ms - Page load time
  • connection_type - Connection type (wifi, cellular, ethernet, unknown)
  • device_type - Device type (desktop, mobile, tablet)
  • api_endpoint - API endpoint
  • response_time_ms - API response time
  • payload_size - Payload size
  • cache_hit - Whether cache hit

E-commerce Parameters

  • subscription_type - AgMission package name (e.g., "AgMission Essentials 1", "AgMission Enterprise 3")
  • subscription_duration - Duration (monthly, quarterly, annual)
  • subscription_price - Subscription price
  • previous_subscription_type - Previous subscription type
  • payment_method - Payment method (credit_card, bank_transfer, paypal, invoice)
  • billing_frequency - Billing frequency (monthly, quarterly, annual)
  • promo_code - Promotional code
  • discount_amount - Discount amount
  • subscription_start_date - Start date
  • auto_renewal - Auto renewal setting
  • upgrade_from - Upgrade from package
  • upgrade_to - Upgrade to package
  • trial_conversion - Whether trial conversion
  • subscription_value - Subscription value
  • user_tenure_days - User tenure in days
  • service_type - Service category (essential, enterprise, addon)
  • is_trial - Whether trial subscription

Event Structure Reference

Event Categories and Names (From Unified Reference)

Job Management Events

  1. job_created - User creates a new agricultural job
  2. job_updated - User modifies an existing job
  3. job_deleted - User removes a job from system
  4. job_assigned - Job assigned to pilot or operator
  5. job_status_changed - Job status transitions

Job List Operations

  1. job_list_viewed - User accesses the jobs list interface
  2. job_list_filtered - User applies filters to narrow job results
  3. job_selected - User clicks/selects a specific job
  4. job_bulk_action - User performs action on multiple jobs

File Management Events

  1. file_upload_started - User initiates file upload
  2. file_upload_completed - File upload completes successfully
  3. file_upload_failed - File upload fails
  4. file_deleted - User deletes a file
  5. file_downloaded - User downloads a file

Library Upload Events

  1. library_upload_started - User starts library upload
  2. library_upload_completed - Library upload completes

Report Events

  1. report_generated - System generates a report
  2. report_viewed - User views a report
  3. report_exported - User exports a report
  4. report_filtered - User applies filters to report
  5. report_rendered - Report rendering completes
  6. report_design_mode_entered - User enters design mode
  7. report_view_duration - Report view duration tracking

Invoice Management Events

  1. invoice_created - User creates new invoice
  2. invoice_updated - User modifies existing invoice
  3. invoice_deleted - User deletes invoice
  4. invoice_status_changed - Invoice status transitions
  5. invoice_payment_logged - Payment logged for invoice

Invoice List Operations

  1. invoice_list_viewed - User accesses invoice list
  2. invoice_list_filtered - User filters invoice list
  3. invoice_selected - User selects specific invoice
  4. invoice_bulk_action - User performs bulk action on invoices
  5. invoice_viewed - User views invoice details
  6. invoice_exported - User exports invoice

Invoice Settings & Configuration

  1. customer_invoice_settings_updated - Customer invoice settings changed
  2. invoice_costing_item_managed - Invoice costing item managed

Authentication Events

  1. login - User logs into system
  2. logout - User logs out of system
  3. signup - User signs up for account
  4. signup_completed - User completes signup process
  5. password_reset_requested - User requests password reset
  6. password_reset_completed - User completes password reset
  7. email_verification_requested - User requests email verification
  8. email_verification_completed - User completes email verification

Error Tracking Events

  1. http_error - HTTP error occurs

Session & Performance Events

  1. session_start - User session starts
  2. slow_page_load - Page loads slowly
  3. api_response_slow - API response is slow

E-commerce Events

  1. subscription_purchased - User purchases subscription

Role-Based Analytics Mapping

🔧 ADMIN Role Analytics

Question: "Which user roles generate the most system activity?"

BigQuery Parameters Available:

  • user_role (available)
  • event_name (available)
  • event_timestamp (available)
  • session_duration_minutes (available)

SQL Query:

SELECT 
  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_role') as user_role,
  COUNT(*) as total_events,
  COUNT(DISTINCT user_id) as unique_users,
  AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_duration_minutes') AS NUMERIC)) as avg_session_duration
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY user_role
ORDER BY total_events DESC;

Question: "What's the subscription purchase conversion rate by user role?"

BigQuery Parameters Available:

  • user_role (available)
  • subscription_type (available)
  • subscription_price (available)
  • trial_conversion (available)
  • service_type (available)

SQL Query:

WITH signup_users AS (
  SELECT DISTINCT
    user_id,
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_role') as user_role,
    MIN(TIMESTAMP_MICROS(event_timestamp)) as signup_time
  FROM `agmission-analytics.analytics_12345678.events_*`
  WHERE event_name = 'signup_completed'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                           AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
),
subscription_purchases AS (
  SELECT DISTINCT
    user_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_type') as subscription_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'service_type') as service_type,
    CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_price') AS NUMERIC) as subscription_price,
    (SELECT value.bool_value FROM UNNEST(event_params) WHERE key = 'trial_conversion') as trial_conversion
  FROM `agmission-analytics.analytics_12345678.events_*`
  WHERE event_name = 'subscription_purchased'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                           AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
)
SELECT 
  s.user_role,
  COUNT(s.user_id) as total_signups,
  COUNT(p.user_id) as total_purchases,
  ROUND(COUNT(p.user_id) / COUNT(s.user_id) * 100, 2) as conversion_rate_percent,
  COUNT(CASE WHEN p.trial_conversion = true THEN 1 END) as trial_conversions,
  ROUND(AVG(p.subscription_price), 2) as avg_subscription_price,
  COUNT(CASE WHEN p.service_type = 'essential' THEN 1 END) as essential_purchases,
  COUNT(CASE WHEN p.service_type = 'enterprise' THEN 1 END) as enterprise_purchases
FROM signup_users s
LEFT JOIN subscription_purchases p ON s.user_id = p.user_id
GROUP BY s.user_role
ORDER BY conversion_rate_percent DESC;

Question: "Which roles have the highest system adoption rates?"

BigQuery Parameters Available:

  • user_role (available)
  • signup_completed event (available)
  • job_created event (available)
  • file_upload_started event (available)

SQL Query:

WITH user_adoption AS (
  SELECT 
    user_id,
    (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_role') as user_role,
    MIN(CASE WHEN event_name = 'signup_completed' THEN TIMESTAMP_MICROS(event_timestamp) END) as signup_time,
    MIN(CASE WHEN event_name = 'job_created' THEN TIMESTAMP_MICROS(event_timestamp) END) as first_job_time,
    MIN(CASE WHEN event_name = 'file_upload_started' THEN TIMESTAMP_MICROS(event_timestamp) END) as first_upload_time
  FROM `agmission-analytics.analytics_12345678.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                           AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY user_id, user_role
)
SELECT 
  user_role,
  COUNT(*) as total_signups,
  COUNT(first_job_time) as users_created_jobs,
  COUNT(first_upload_time) as users_uploaded_files,
  ROUND(COUNT(first_job_time) / COUNT(*) * 100, 2) as job_adoption_rate_percent,
  ROUND(COUNT(first_upload_time) / COUNT(*) * 100, 2) as upload_adoption_rate_percent
FROM user_adoption
WHERE signup_time IS NOT NULL
GROUP BY user_role
ORDER BY job_adoption_rate_percent DESC;

E-commerce Analytics

Subscription Purchase Tracking

Question: "What's the distribution of subscription purchases by package type and tier?"

BigQuery Parameters Available:

  • subscription_type - AgMission package name (e.g., "AgMission Essentials 1", "AgMission Enterprise 3")
  • service_type - Service category (essential, enterprise, addon)
  • subscription_tier - Tier level (string: "1", "2", "3", "4", "5")
  • subscription_price - Subscription price
  • subscription_duration - Duration (monthly, quarterly, annual)

SQL Query:

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_type') as subscription_package,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'service_type') as service_type,
  (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'subscription_tier') as tier,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_duration') as duration,
  COUNT(*) as purchase_count,
  COUNT(DISTINCT user_id) as unique_customers,
  SUM(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_price') AS NUMERIC)) as total_revenue,
  ROUND(AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_price') AS NUMERIC)), 2) as avg_price
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'subscription_purchased'
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY subscription_package, service_type, tier, duration
ORDER BY total_revenue DESC;

Question: "What's the trial to paid conversion rate by subscription type?"

BigQuery Parameters Available:

  • trial_conversion - Whether this is a trial conversion
  • subscription_type - Package name
  • is_trial - Whether this is a trial subscription
  • user_tenure_days - User tenure in days

SQL Query:

WITH trial_tracking AS (
  SELECT 
    user_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_type') as subscription_type,
    (SELECT value.bool_value FROM UNNEST(event_params) WHERE key = 'trial_conversion') as trial_conversion,
    (SELECT value.bool_value FROM UNNEST(event_params) WHERE key = 'is_trial') as is_trial,
    CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'user_tenure_days') AS NUMERIC) as user_tenure_days,
    TIMESTAMP_MICROS(event_timestamp) as purchase_time
  FROM `agmission-analytics.analytics_12345678.events_*`
  WHERE event_name = 'subscription_purchased'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY))
                           AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
)
SELECT 
  subscription_type,
  COUNT(CASE WHEN is_trial = true THEN 1 END) as trial_subscriptions,
  COUNT(CASE WHEN trial_conversion = true THEN 1 END) as trial_conversions,
  COUNT(CASE WHEN is_trial = false AND trial_conversion = false THEN 1 END) as direct_purchases,
  ROUND(
    CASE 
      WHEN COUNT(CASE WHEN is_trial = true THEN 1 END) > 0 
      THEN COUNT(CASE WHEN trial_conversion = true THEN 1 END) / COUNT(CASE WHEN is_trial = true THEN 1 END) * 100
      ELSE 0 
    END, 2
  ) as trial_conversion_rate_percent,
  ROUND(AVG(CASE WHEN trial_conversion = true THEN user_tenure_days END), 1) as avg_trial_duration_days
FROM trial_tracking
GROUP BY subscription_type
ORDER BY trial_conversion_rate_percent DESC;

Question: "What's the upgrade/downgrade pattern analysis?"

BigQuery Parameters Available:

  • upgrade_from - Previous package
  • upgrade_to - New package
  • previous_subscription_type - Previous subscription
  • subscription_type - Current subscription
  • subscription_price - Price

SQL Query:

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_subscription_type') as previous_package,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_type') as new_package,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'upgrade_from') as upgrade_from,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'upgrade_to') as upgrade_to,
  COUNT(*) as change_count,
  COUNT(DISTINCT user_id) as unique_users,
  AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_price') AS NUMERIC)) as avg_new_price,
  CASE 
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'upgrade_from') IS NOT NULL 
    THEN 'UPGRADE'
    WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'previous_subscription_type') != 'none'
    THEN 'CHANGE'
    ELSE 'NEW'
  END as change_type
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'subscription_purchased'
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY previous_package, new_package, upgrade_from, upgrade_to
ORDER BY change_count DESC;

Question: "What's the revenue impact by payment method and billing frequency?"

BigQuery Parameters Available:

  • payment_method - Payment method (credit_card, bank_transfer, paypal, invoice)
  • billing_frequency - Billing frequency (monthly, quarterly, annual)
  • subscription_price - Subscription price
  • discount_amount - Discount amount
  • promo_code - Promotional code used

SQL Query:

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'payment_method') as payment_method,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'billing_frequency') as billing_frequency,
  COUNT(*) as transaction_count,
  COUNT(DISTINCT user_id) as unique_customers,
  SUM(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_price') AS NUMERIC)) as gross_revenue,
  SUM(COALESCE(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'discount_amount') AS NUMERIC), 0)) as total_discounts,
  SUM(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_price') AS NUMERIC)) - 
  SUM(COALESCE(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'discount_amount') AS NUMERIC), 0)) as net_revenue,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'promo_code') IS NOT NULL THEN 1 END) as promo_usage_count,
  ROUND(AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_price') AS NUMERIC)), 2) as avg_transaction_value
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'subscription_purchased'
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY payment_method, billing_frequency
ORDER BY net_revenue DESC;

📊 OFFICER Role Analytics

Question: "What's the optimal job assignment lead time?"

BigQuery Parameters Available:

  • assignment_lead_time_hours (available)
  • job_type (available)
  • efficiency_score (available)
  • assignee_role (available)

SQL Query:

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'job_type') as job_type,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'assignee_role') as assignee_role,
  ROUND(AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'assignment_lead_time_hours') AS NUMERIC)), 2) as avg_lead_time_hours,
  ROUND(AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'efficiency_score') AS NUMERIC)), 2) as avg_efficiency_score,
  COUNT(*) as total_assignments
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'job_assigned'
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY job_type, assignee_role
HAVING COUNT(*) >= 10
ORDER BY avg_efficiency_score DESC, avg_lead_time_hours;

Question: "Which pilots/applicators have the highest efficiency scores?"

BigQuery Parameters Available:

  • assignee_id (available)
  • assignee_role (available)
  • efficiency_score (available)
  • job_type (available)

SQL Query:

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'assignee_id') as assignee_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'assignee_role') as assignee_role,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'job_type') as job_type,
  COUNT(*) as total_jobs,
  ROUND(AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'efficiency_score') AS NUMERIC)), 2) as avg_efficiency_score,
  MIN(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'efficiency_score') AS NUMERIC)) as min_efficiency,
  MAX(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'efficiency_score') AS NUMERIC)) as max_efficiency
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'job_status_changed'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'new_status') = 'sprayed'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'assignee_role') IN ('pilot', 'applicator')
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY assignee_id, assignee_role, job_type
HAVING COUNT(*) >= 5
ORDER BY avg_efficiency_score DESC;

✈️ PILOT Role Analytics

Question: "What's my personal efficiency score compared to team average?"

BigQuery Parameters Available:

  • user_id (available)
  • efficiency_score (available)
  • job_type (available)
  • user_role (available)

SQL Query:

WITH pilot_performance AS (
  SELECT 
    user_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'job_type') as job_type,
    AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'efficiency_score') AS NUMERIC)) as avg_efficiency_score
  FROM `agmission-analytics.analytics_12345678.events_*`
  WHERE event_name = 'job_status_changed'
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'new_status') = 'sprayed'
    AND (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_role') = 'pilot'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                           AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY user_id, job_type
),
team_averages AS (
  SELECT 
    job_type,
    AVG(avg_efficiency_score) as team_avg_efficiency
  FROM pilot_performance
  GROUP BY job_type
)
SELECT 
  p.user_id,
  p.job_type,
  p.avg_efficiency_score as personal_efficiency,
  t.team_avg_efficiency,
  ROUND(((p.avg_efficiency_score - t.team_avg_efficiency) / t.team_avg_efficiency) * 100, 2) as performance_vs_team_percent
FROM pilot_performance p
JOIN team_averages t ON p.job_type = t.job_type
WHERE p.user_id = 'CURRENT_USER_ID'  -- Replace with actual user ID
ORDER BY p.job_type;

Question: "How does weather dependency affect my job completion rates?"

BigQuery Parameters Available:

  • weather_dependency (available)
  • job_status (available)
  • user_id (available)

SQL Query:

SELECT 
  (SELECT value.bool_value FROM UNNEST(event_params) WHERE key = 'weather_dependency') as weather_dependent,
  COUNT(*) as total_jobs,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'new_status') = 'sprayed' THEN 1 END) as completed_jobs,
  ROUND(COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'new_status') = 'sprayed' THEN 1 END) / COUNT(*) * 100, 2) as completion_rate_percent
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'job_status_changed'
  AND user_id = 'CURRENT_USER_ID'  -- Replace with actual user ID
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY weather_dependent
ORDER BY completion_rate_percent DESC;

🚁 APPLICATOR Role Analytics

Question: "Which file types do I upload most frequently?"

BigQuery Parameters Available:

  • file_type (available)
  • user_id (available)
  • file_size_mb (available)
  • processing_time_seconds (available)

SQL Query:

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'file_type') as file_type,
  COUNT(*) as upload_count,
  ROUND(AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'file_size_mb') AS NUMERIC)), 2) as avg_file_size_mb,
  ROUND(AVG(CAST((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'processing_time_seconds') AS NUMERIC)), 2) as avg_processing_time_seconds,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'validation_status') = 'passed' THEN 1 END) as successful_uploads,
  ROUND(COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'validation_status') = 'passed' THEN 1 END) / COUNT(*) * 100, 2) as success_rate_percent
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'file_upload_completed'
  AND user_id = 'CURRENT_USER_ID'  -- Replace with actual user ID
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY file_type
ORDER BY upload_count DESC;

Question: "What's my file upload success rate compared to team average?"

BigQuery Parameters Available:

  • user_id (available)
  • validation_status (available)
  • file_type (available)
  • user_role (available)

SQL Query:

WITH user_upload_success AS (
  SELECT 
    user_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'file_type') as file_type,
    COUNT(*) as total_uploads,
    COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'validation_status') = 'passed' THEN 1 END) as successful_uploads,
    ROUND(COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'validation_status') = 'passed' THEN 1 END) / COUNT(*) * 100, 2) as success_rate
  FROM `agmission-analytics.analytics_12345678.events_*`
  WHERE event_name = 'file_upload_completed'
    AND (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_role') = 'applicator'
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                           AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY user_id, file_type
),
team_averages AS (
  SELECT 
    file_type,
    AVG(success_rate) as team_avg_success_rate,
    COUNT(DISTINCT user_id) as total_team_members
  FROM user_upload_success
  GROUP BY file_type
)
SELECT 
  u.file_type,
  u.total_uploads as my_uploads,
  u.success_rate as my_success_rate,
  t.team_avg_success_rate,
  t.total_team_members,
  ROUND(u.success_rate - t.team_avg_success_rate, 2) as performance_vs_team
FROM user_upload_success u
JOIN team_averages t ON u.file_type = t.file_type
WHERE u.user_id = 'CURRENT_USER_ID'  -- Replace with actual user ID
ORDER BY u.file_type;

🌾 CLIENT Role Analytics

Question: "What's the completion rate for my jobs?"

BigQuery Parameters Available:

  • client_id (available)
  • job_status (available)
  • job_type (available)

SQL Query:

SELECT 
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'job_type') as job_type,
  COUNT(*) as total_jobs,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'new_status') = 'sprayed' THEN 1 END) as completed_jobs,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'new_status') = 'archived' THEN 1 END) as archived_jobs,
  ROUND(COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'new_status') = 'sprayed' THEN 1 END) / COUNT(*) * 100, 2) as completion_rate_percent
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'job_status_changed'
  AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'client_id') = 'CURRENT_CLIENT_ID'  -- Replace with actual client ID
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY job_type
ORDER BY completion_rate_percent DESC;

🔍 INSPECTOR Role Analytics

Question: "What's the compliance rate across all jobs?"

BigQuery Parameters Required:

  • job_type (available)
  • efficiency_score (available)
  • MISSING: compliance_score, inspection_result, regulatory_standard

Collection Method for Missing Parameters:

  • Add compliance_score to job completion events
  • Track inspection_result when inspectors review jobs
  • Include regulatory_standard for compliance tracking

Missing Parameters Analysis

Critical Missing Parameters

1. Weather & Environmental Data

Missing Parameters:

  • weather_conditions - Current weather conditions
  • temperature - Temperature at job time
  • wind_speed - Wind speed during operation
  • humidity - Humidity levels
  • precipitation - Precipitation data

Collection Method:

  • Integrate with weather API (OpenWeatherMap, WeatherAPI)
  • Capture at job creation and completion
  • Store in job events as additional parameters

2. Financial & Business Metrics

Missing Parameters:

  • profit_margin - Profit margin per job
  • cost_per_acre - Cost per acre calculation
  • revenue_per_job - Revenue generated per job
  • customer_lifetime_value - CLV calculation
  • account_creation_cost - Cost to create account
  • support_cost_per_user - Support cost per user
  • feature_usage_cost - Feature usage cost

Collection Method:

  • Calculate from invoice and cost data
  • Add to invoice and job completion events
  • Integrate with accounting system
  • Track support system costs
  • Add to signup and subscription events

3. Performance & Quality Metrics

Missing Parameters:

  • application_accuracy - Application accuracy percentage
  • coverage_quality - Coverage quality score
  • rework_required - Whether rework was needed
  • customer_satisfaction - Customer satisfaction score
  • compliance_score - Compliance score
  • inspection_result - Inspection outcomes
  • regulatory_standard - Regulatory compliance standard

Collection Method:

  • GPS tracking integration for accuracy
  • Post-job quality assessment
  • Customer feedback collection
  • Compliance system integration
  • Add to job completion events

4. Equipment & Maintenance Data

Missing Parameters:

  • equipment_id - Specific equipment identifier
  • maintenance_status - Equipment maintenance status
  • fuel_consumption - Fuel consumption data
  • operating_hours - Equipment operating hours

Collection Method:

  • Equipment tracking integration
  • Maintenance system integration
  • Add to job assignment and completion events

5. Compliance & Safety Data

Missing Parameters:

  • safety_incidents - Safety incident reports
  • regulatory_compliance - Compliance status
  • certification_status - Certification validity

Collection Method:

  • Safety system integration
  • Regulatory compliance tracking
  • Inspection result recording
  • Add to job and user events

6. Enhanced E-commerce Metrics

Missing Parameters:

  • churn_risk_score - Calculated churn risk
  • subscription_health_score - Subscription health metric
  • feature_usage_frequency - Feature usage patterns
  • support_ticket_count - Number of support tickets
  • user_engagement_score - Overall engagement metric

Collection Method:

  • Calculate from usage patterns
  • Track support interactions
  • Add to subscription and user events
  • Implement engagement scoring system

Implementation Recommendations

Phase 1: Core Parameter Enhancement (Immediate)

  1. Add Weather Data Integration

    • Implement weather API integration
    • Add weather parameters to job events
    • Create weather-based analytics

  2. Enhance Financial Tracking

    • Add profit margin calculations
    • Include cost tracking parameters
    • Implement revenue analytics

  3. Improve Performance Metrics

    • Add quality scoring system
    • Implement accuracy tracking
    • Create performance dashboards

  4. Complete E-commerce Implementation

    • Ensure all subscription purchase tracking is active
    • Validate string-based package names in tracking
    • Add missing trial conversion tracking
    • Implement addon purchase tracking (currently placeholder)

Phase 2: Advanced Analytics (Next 3 months)

  1. Equipment Integration

    • Connect with equipment systems
    • Add maintenance tracking
    • Implement utilization analytics

  2. Compliance System

    • Add regulatory tracking
    • Implement safety monitoring
    • Create compliance dashboards

  3. Customer Experience

    • Add satisfaction tracking
    • Implement feedback collection
    • Create customer analytics

  4. Enhanced E-commerce Analytics

    • Implement churn prediction
    • Add subscription health scoring
    • Create customer lifetime value tracking

Phase 3: Predictive Analytics (Next 6 months)

  1. Machine Learning Integration

    • Predictive maintenance
    • Weather-based scheduling
    • Demand forecasting
    • Churn prediction models

  2. Advanced Reporting

    • Custom dashboard creation
    • Automated insights
    • Real-time monitoring

  3. Subscription Optimization

    • Package recommendation engine
    • Pricing optimization
    • Retention improvement analytics

BigQuery Schema Enhancements

Recommended Event Parameter Additions

// Add to existing interfaces
export interface EnhancedJobParams extends JobCreatedParams {
  weather_conditions?: string;
  temperature?: number;
  wind_speed?: number;
  humidity?: number;
  equipment_id?: string;
  maintenance_status?: string;
  profit_margin?: number;
  cost_per_acre?: number;
}

export interface QualityMetricsParams extends AgMissionBaseContext {
  application_accuracy?: number;
  coverage_quality?: number;
  rework_required?: boolean;
  customer_satisfaction?: number;
  compliance_score?: number;
}

export interface EnhancedSubscriptionParams extends SubscriptionPurchasedParams {
  churn_risk_score?: number;
  subscription_health_score?: number;
  feature_usage_frequency?: number;
  support_ticket_count?: number;
  user_engagement_score?: number;
}

Current Implementation Status

✅ Completed Events & Parameters

  • Job Management: All events implemented with comprehensive parameters
  • File Management: Full upload/download tracking with validation metrics
  • Invoice Management: Complete invoice lifecycle tracking
  • Authentication: Full signup/login tracking with verification flows
  • E-commerce: Subscription purchase tracking with correct package names
  • Performance: Page load and API response tracking
  • Error Tracking: HTTP error monitoring

🔄 In Progress

  • E-commerce Addon Tracking: Currently placeholder implementation
  • Trial Conversion Optimization: Basic tracking in place, needs enhancement
  • Customer Lifecycle Analytics: Partial implementation

❌ Missing Implementation

  • Weather Integration: API integration needed
  • Equipment Tracking: System integration required
  • Compliance Monitoring: Regulatory system integration
  • Quality Scoring: Post-job assessment system
  • Customer Satisfaction: Feedback collection system

Data Quality Validation Queries

Verify E-commerce Data Integrity

-- Check subscription purchase data completeness
SELECT 
  COUNT(*) as total_purchases,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_type') IS NOT NULL THEN 1 END) as with_package_name,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'service_type') IS NOT NULL THEN 1 END) as with_service_type,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'subscription_tier') IS NOT NULL THEN 1 END) as with_tier
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE event_name = 'subscription_purchased'
  AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE());

Validate Parameter Consistency

-- Check for consistent parameter usage across events
SELECT 
  event_name,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_id) as unique_users,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'user_role') IS NOT NULL THEN 1 END) as with_user_role,
  COUNT(CASE WHEN (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'subscription_tier') IS NOT NULL THEN 1 END) as with_subscription_tier
FROM `agmission-analytics.analytics_12345678.events_*`
WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
                         AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
GROUP BY event_name
ORDER BY event_count DESC;