# Advertiser Metrics - CSV Export

CSV export system for advertisers' click data with enriched demographics, creative metadata, and device information.

## Usage

### Full Generation (All Advertisers)
```bash
php advertiser_metrics.php
```
- Generates CSV for Amazon (20378) and Hearst (13421)
- Uses last 6 months of data
- Does NOT sync tables (uses existing data)
- Default chunking: **daily** (one file per date)
- Processes all records in each month

### Test Run (Limited Records)
```bash
php advertiser_metrics.php --limit=5000
```
- Generates test CSV with max 5000 records per advertiser
- Useful for quick validation and testing
- Customizable limit (e.g., --limit=1000, --limit=10000)

### Custom Month Range
```bash
php advertiser_metrics.php --months=3
```
- Generates CSV for last 3 months (instead of default 6)
- Valid range: 1-12 months
- Useful for shorter time periods or faster testing

### Custom Date Range
```bash
php advertiser_metrics.php --start-date=2026-01-01 --end-date=2026-03-31
```
- Generates CSV for specific date range (inclusive)
- Format: YYYY-MM-DD
- Overrides --months parameter if provided

### File Chunking
```bash
php advertiser_metrics.php --chunk=daily
```
- **daily** (default): One CSV file per date (format: `YYYYMMDD/advertiser_clicks_YYYY-MM-DD.csv`)
- **weekly**: One CSV file per ISO week (format: `YYYYMM/advertiser_clicks_YYYY-W##.csv`)
- **none**: Single CSV file per advertiser (format: `advertiser_clicks.csv`)

### With Drest Sync
```bash
php advertiser_metrics.php --drest
```
- Syncs tables from remote source before processing (only if missing locally)
- Ensures data is fresh from remote
- Smart optimization: only syncs missing tables
- Takes longer due to sync operations
- Recommended for full data refresh

### Single Advertiser
```bash
php advertiser_metrics.php --advertiser=20378
```
- Generates CSV for Amazon only (advertiser_id=20378)
- Valid advertiser IDs: 20378 (Amazon), 13421 (Hearst)

### Combined Arguments Examples
```bash
php advertiser_metrics.php --limit=2000 --advertiser=13421 --chunk=none
```
- Hearst test run with 2000 records limit, single file output

```bash
php advertiser_metrics.php --months=3 --limit=1000 --drest --chunk=weekly
```
- 3 months of data, 1000 records max per advertiser, with drest sync, weekly chunks

```bash
php advertiser_metrics.php --start-date=2026-01-01 --end-date=2026-04-22 --advertiser=20378
```
- Amazon data for specific date range, daily chunks (default)

```bash
php advertiser_metrics.php --limit=5000 --months=12 --chunk=none
```
- Full year of data (all advertisers), 5000 records max, single file per advertiser

## Arguments Reference

| Argument | Value | Default | Description |
|----------|-------|---------|-------------|
| `--limit` | Number | 0 (unlimited) | Max records per advertiser (0 = full data) |
| `--months` | 1-12 | 6 | Number of months to process |
| `--start-date` | YYYY-MM-DD | null | Start date for custom range (overrides --months) |
| `--end-date` | YYYY-MM-DD | null | End date for custom range (overrides --months) |
| `--chunk` | daily/weekly/none | daily | File chunking strategy |
| `--drest` | Flag | disabled | Enable table sync from remote source |
| `--advertiser` | ID | all | Specific advertiser ID (20378, 13421) |

## Output

### File Organization
- **Chunked Output (daily/weekly)**: `./debug/{advertiser_id}/YYYY-MM/` - organized by advertiser and month
  - Daily: `amazon_20378_clicks_YYYY-MM-DD.csv`
  - Weekly: `amazon_20378_clicks_YYYY-W##.csv`
- **No Chunking**: `./debug/` - root directory
  - `amazon_20378_clicks.csv`
  - `hearst_13421_clicks.csv`
- **Log File Location**: `./debug/logs/`
- **Log Filename**: `clicks_csv_generator_YYYYMMDD.log`

### CSV Columns (36 total)

| # | Column | Source | Description |
|---|--------|--------|-------------|
| 1 | id | Computed | Format: `advertiser_id + YYYYMMDD + original_id` (e.g., 2037820260315.12345) |
| 2 | date | DB | Click date (YYYY-MM-DD) |
| 3 | hour | DB | Hour of click (0-23) |
| 4 | campaign_id | DB | Campaign identifier |
| 5 | creative_id | DB | Creative identifier |
| 6 | source | DB | Traffic source |
| 7 | country | DB | Country code |
| 8 | os | DB | Operating system |
| 9 | browser | DB | Browser name |
| 10 | keyword | DB | Search keyword |
| 11 | search_query | DB | Full search query |
| 12 | destination_url | DB | Landing page URL |
| 13 | ip | DB | IP address |
| 14 | state | DB | State/Province code |
| 15 | city | DB | City name |
| 16 | zip | DB | Postal code |
| 17 | impressions | DB | Impression count |
| 18 | conversions | DB | Conversion count |
| 19 | spent | DB | Cost spent |
| 20 | media_cost | DB | Media cost |
| 21 | status | DB | Status code |
| 22 | cpm_clicks | DB | CPM clicks |
| 23 | campaign_type | DB | Campaign type ID (1=Banner, 2=Text Search, etc.) |
| 24 | adgroup_id | DB | Ad group identifier |
| 25 | clicks | Computed | Calculated: IF campaign_type=1 THEN cpm_clicks ELSE status |
| 26 | age_group | Synthetic | Generated: 18-24, 25-34, 35-44, 45-54, 55-64, 65+ |
| 27 | gender | Synthetic | Generated: 99.9% Male/Female, 0.1% Others |
| 28 | device_type | Computed | Mapped from OS + Browser: Desktop, Mobile, Tablet |
| 29 | campaign_name | Admin.campaign | Campaign name |
| 30 | campaign_label | Admin.campaign_type | Campaign type label (Display, Text Search, Video, Native, Shopping, etc.) |
| 31 | campaign_mode | Admin.campaign | Campaign mode (CPC, CPM, etc.) |
| 32 | creative_name | Admin.creatives | Creative asset name |
| 33 | creative_label | Admin.creatives_types | Creative type label (Image, Script, Text, Video, Native, Social Media, Interstitial) |
| 34 | landing_page_url | Admin.creatives | Creative destination URL |
| 35 | creative_url | Admin.creatives | Creative asset URL |
| 36 | domain | adv_domains | Random domain from month's domain list |

### Data Sources
- **Keywords DB**: adv_clicks_{advertiser_id}_{month}, adv_domains_{advertiser_id}_{month}
- **Admin DB**: campaign, creatives, campaign_type, creatives_types lookup tables

### Optimization Features
- **Memory Limit**: 2048MB
- **Batch Processing**: 500k rows per batch
- **Lookup Table Caching**: campaign_type and creatives_types tables loaded once at startup
- **Per-ID Caching**: Campaign and creative metadata cached to avoid redundant queries
- **No COUNT queries**: Batch loop uses early-exit instead of pre-counting rows (major performance improvement)
- **Auto-reconnect**: Admin DB connection auto-reconnects if dropped during long runs
- **Garbage Collection**: Enabled after each batch to manage memory
- **Smart Drest Syncing**: Only syncs missing tables (no redundant remote calls)
- **Streaming Output**: fputcsv for efficient file writing

## Running in Background

To run the script without blocking the terminal (recommended for full runs):
```bash
nohup php /path/to/advertiser_metrics.php --drest > /path/to/logs/bg_run.log 2>&1 &
```

Monitor progress:
```bash
tail -f /path/to/logs/bg_run.log
```

Check if still running:
```bash
ps aux | grep advertiser_metrics.php | grep -v grep
```