# Step 1: Pixel captures page views with media_source
SELECT id, media_source as source_credit, event_date as credit_event_date
FROM eventapp_usersession
WHERE parent_id = '{user_id}'
  AND event_date BETWEEN '{order_date - 30 days}' AND '{order_date}'
  AND event_type = 'page_view'

# Step 2: When transaction fires, link to Shopify order
token = row.user_journey[0].split('/')[3]  # Extract checkout token
SELECT user_id, order_number, revenue
FROM orders
WHERE checkout_token = '{token}'

# Step 3: Update transaction event with order details
events.at[idx, 'tr_orderid'] = order_number
events.at[idx, 'tr_total'] = revenue * 100  # Convert to cents

# Step 4: Create attribution records
INSERT INTO eventapp_ordercredit (
    tr_orderid, source_credit, tr_total, credit_score, credit_event_date
)
# One row per touchpoint in the 30-day window

-- This query only includes orders that appear in ordercredit table
SELECT SUM(any_click_revenue)/100 as revenue
FROM interaction_insight_summary
WHERE media_source IS NOT NULL

-- Total Shopify orders
SELECT COUNT(*) FROM orders WHERE client_id = 'qalo' AND order_date BETWEEN '2026-02-19' AND '2026-02-25';
-- Result: ~500 orders, $255K revenue

-- Orders with attribution
SELECT COUNT(DISTINCT tr_orderid) FROM eventapp_ordercredit WHERE client_id = 'qalo' AND event_date BETWEEN '2026-02-19' AND '2026-02-25';
-- Result: ~450 orders (50 missing = untracked)

class ShopifyDailyData(models.Model):
    class Meta:
        db_table = 'shopify_daily_summary'  # <- Model points here

-- Executive Summary query
SELECT SUM(gross_sales) FROM shopify_daily_summary
WHERE client_id = %s AND event_date BETWEEN %s AND %s

# Only includes orders where user had page_view events
WHERE event_type = 'page_view'  # Direct orders with no pageviews = excluded

SELECT
    media_source,
    COALESCE(SUM(any_click_order), 0) as orders,
    COALESCE(SUM(any_click_revenue)/100, 0) as revenue
FROM interaction_insight_summary
WHERE event_date BETWEEN '2026-02-19' AND '2026-02-25'
  AND client_id = 'qalo'
GROUP BY media_source
ORDER BY revenue DESC

-- Option 1: Direct from orders table
SELECT COALESCE(SUM(revenue), 0) as shopify_total_revenue
FROM (
    SELECT DISTINCT order_number, revenue
    FROM orders
    WHERE client_id = 'qalo'
      AND order_date BETWEEN '2026-02-19' AND '2026-02-25'
      AND revenue != 0
) tab1

-- Option 2: From daily summary table
SELECT COALESCE(SUM(gross_sales), 0) as gross_sales
FROM shopify_daily_summary
WHERE client_id = 'qalo'
  AND event_date BETWEEN '2026-02-19' AND '2026-02-25'

def CalculateCreditScore(self, CreditData):
    # For Any-Click: credit_score is irrelevant, full order value used
    # Query sums ALL touchpoints without dividing

SELECT SUM(any_click_revenue)/100 as revenue
FROM interaction_insight_summary

SELECT SUM(first_click_revenue)/100 as revenue
FROM interaction_insight_summary

SELECT SUM(tr_total) as first_click_revenue
FROM eventapp_ordercredit
INNER JOIN (
    SELECT MIN(id) as minid, tr_orderid
    FROM eventapp_ordercredit
    WHERE (source_credit != 'Direct' OR credit_score != 0)
    GROUP BY tr_orderid
) ON minid = id

SELECT SUM(last_click_revenue)/100 as revenue
FROM interaction_insight_summary

SELECT SUM(tr_total) as last_click_revenue
FROM eventapp_ordercredit
INNER JOIN (
    SELECT MAX(id) as maxid, tr_orderid
    FROM eventapp_ordercredit
    WHERE (source_credit != 'Direct' OR credit_score != 0)
    GROUP BY tr_orderid
) ON maxid = id

def CalculateCreditScore(self, CreditData):
    excluded_sources = ['Direct', 'Afterpay', 'Clearpay', 'Klarna']
    non_excluded = [t for t in CreditData if t['source_credit'] not in excluded_sources]

    credit_score = 1 / len(non_excluded)
    # Each touchpoint gets 1/N credit

SELECT SUM(linear_click_revenue)/100 as revenue
FROM interaction_insight_summary

SELECT SUM(tr_total * credit_score) as equal_click_revenue
FROM eventapp_ordercredit
WHERE credit_score > 0

intervalmonthVal = client.attr_lookup  # Default: 30 days

SELECT id, media_source, event_date
FROM eventapp_usersession
WHERE parent_id = '{user_id}'
  AND event_date BETWEEN '{order_date - intervalmonthVal days}' AND '{order_date}'
  AND event_type = 'page_view'

l5s=fb&l5m=social&l5ss={{site_source_name}}&l5adid={{ad.id}}&l5p={{placement}}

class MediaAttRule(models.Model):
    # Rules match UTM parameters to media_source
    source_param = models.CharField()  # e.g., 'l5s', 'utm_source'
    source_value = models.CharField()  # e.g., 'fb', 'facebook'
    media_source = models.CharField()  # e.g., 'Facebook Ads'

# MediaAttRule might match on l5s=fb
if url_params.get('l5s') == 'fb':
    media_source = 'Facebook Ads'

# Categorizes media type (social, search, display, email)
if url_params.get('l5m') == 'social':
    media_type = 'Social'

# Already filters by media_type in queries
subQry = " and media_type = '" + media_type + "'"

class UserSession(models.Model):
    mkt_placement = models.CharField()  # Stores l5p value

SELECT COUNT(*) FROM eventapp_usersession WHERE mkt_placement IS NOT NULL;
-- If > 0, it's being captured

SELECT mkt_placement, COUNT(*)
FROM eventapp_ordercredit
GROUP BY mkt_placement
ORDER BY COUNT(*) DESC LIMIT 20;
-- If data exists, check if it's in any reports

l5ss={{site_source_name}}&l5adid={{ad.id}}&l5cid={{campaign.id}}&l5c={{adset.name}}

# System checks BOTH parameter sets
utm_source = url_params.get('utm_source') or url_params.get('l5s')
utm_medium = url_params.get('utm_medium') or url_params.get('l5m')
utm_campaign = url_params.get('utm_campaign') or url_params.get('l5c')

# Option 1: L5 parameters
?l5ss=facebook&l5adid=123456&l5c=spring_sale

# Option 2: Standard UTM
?utm_source=facebook&utm_medium=cpc&utm_campaign=spring_sale

# Option 3: Hybrid (not recommended)
?utm_source=facebook&l5adid=123456&l5c=spring_sale

-- Maps utm_source=facebook to Facebook Ads
INSERT INTO eventapp_mediaattrule (source_param, source_value, media_source, client_id)
VALUES
('utm_source', 'facebook', 'Facebook Ads', 'qalo'),
('utm_source', 'google', 'Google Ads', 'qalo'),
('l5ss', 'fb', 'Facebook Ads', 'qalo'),
('l5ss', 'ig', 'Facebook Ads', 'qalo');

request.GET['attribution'] = 'Any Click'  # <- Hardcoded!

Actual Shopify Revenue: $255,871.60
Revenue Comparison Shows: $298,940.03
Overcount: $43,068.43 (17%)
Average touchpoints per order: 1.17x

class ShopifyDailyData(models.Model):
    class Meta:
        db_table = 'shopify_daily_summary'  # <- Wrong table name?

-- Actual table name
SELECT table_name FROM information_schema.tables
WHERE table_name LIKE 'shopify_daily%';
-- Result: shopify_daily_data (NOT shopify_daily_summary)

# MC ROAS calculation
curr_roas = SalesData['ads_revenue'] / SalesData['total_adsspend']

# MC CPA calculation
curr_cpa = SalesData['total_adsspend'] / SalesData['ads_orders']

# MER = Total Revenue / Ad Spend (exact same formula!)
total_roas = SalesData['total_sales'] / SalesData['total_adsspend']

# CAC = Ad Spend / New Customers (similar to CPA)
curr_cac = SalesData['total_adsspend'] / SalesData['new_customers_acquired']

WHERE event_type = 'page_view'  # <- Orders with no pageviews excluded

-- This only includes orders with at least one touchpoint
SELECT SUM(any_click_revenue)/100
FROM interaction_insight_summary
WHERE media_source IS NOT NULL

request.GET['attribution'] = 'Any Click'

# Option A: Use Last Click (more conservative)
request.GET['attribution'] = 'Last Click'

# Option B: Make it configurable (best solution)
attribution = request.GET.get('attribution', 'Last Click')  # Default to Last Click
request.GET['attribution'] = attribution

# File: interaction_insight/views.py:8273
def get(self, request, *args, **kwargs):
    client_id, timezone, date_range, request = super().get(request, **kwargs)
    try:
        compare_dates_number = int(request.GET.get('compare_dates_number', 1))

        # NEW: Allow attribution to be specified
        attribution_type = request.GET.get('attribution', 'Last Click')

        date_ranges = {}
        for i in range(1, compare_dates_number + 1):
            # ... existing date range logic ...

        for date_range in date_ranges.values():
            start_date, end_date = date_range
            results = []
            request.GET = request.GET.copy()
            request.GET['start_date'] = start_date
            request.GET['end_date'] = end_date
            request.GET['attribution'] = attribution_type  # <- Use configurable value

            # ... rest of logic ...

class ShopifyDailyData(models.Model):
    class Meta:
        db_table = 'shopify_daily_summary'  # <- Wrong!

class ShopifyDailyData(models.Model):
    # ... all fields ...

    class Meta:
        db_table = 'shopify_daily_data'  # <- Correct table name

python manage.py makemigrations
python manage.py migrate

CREATE VIEW shopify_daily_summary AS
SELECT * FROM shopify_daily_data;

# Only add MC ROAS and MC CPA if pixel is installed/has data
if has_pixel_data:
    ResponseData.append({'name': 'ROAS','title': 'MC ROAS', ...})
    ResponseData.append({'name': 'CPA','title': 'MC CPA', ...})

# REMOVE these lines entirely (lines 190-192)
# Keep only MER and CAC

# The dashboard will now show:
# YES MER (Marketing Efficiency Ratio) = Total Sales / Ad Spend
# YES CAC (Customer Acquisition Cost) = Ad Spend / New Customers
# NO MC ROAS (removed - redundant with MER)
# NO MC CPA (removed - redundant with CAC)

CreditData = getQuery(f'''
    SELECT id, media_source, event_date
    FROM eventapp_usersession
    WHERE event_type = 'page_view'
    ...
''')

if len(CreditData) == 0:
    return  # <- Orders with no touchpoints are skipped!

CreditData = getQuery(f'''
    SELECT id, media_source, event_date
    FROM eventapp_usersession
    WHERE event_type = 'page_view'
    ...
''')

# NEW: If no touchpoints found, create a Direct attribution
if len(CreditData) == 0:
    CreditData = [{
        'id': None,
        'source_credit': 'Direct',
        'credit_event_date': order_date,
        'mkt_campaign': None,
        'mkt_content': None,
        'credit_score': 1.0  # Give Direct 100% credit
    }]

# Continue with normal processing
self.CalculateCreditScore(CreditData)

l5s=fb&l5m=social&l5ss={{site_source_name}}&l5adid={{ad.id}}&l5p={{placement}}

l5ss={{site_source_name}}&l5adid={{ad.id}}&l5c={{campaign.name}}

-- Add new rules that use l5ss
INSERT INTO eventapp_mediaattrule (client_id, source_param, source_value, media_source)
VALUES
('qalo', 'l5ss', 'fb', 'Facebook Ads'),
('qalo', 'l5ss', 'ig', 'Facebook Ads'),
('qalo', 'l5ss', 'facebook', 'Facebook Ads'),
('qalo', 'l5ss', 'instagram', 'Facebook Ads'),
('qalo', 'l5ss', 'google', 'Google Ads'),
('qalo', 'l5ss', 'bing', 'Bing Ads');

def get_media_type(media_source):
    """Infer media type from source name"""
    social = ['Facebook Ads', 'Instagram Ads', 'TikTok Ads', 'Snapchat Ads', 'LinkedIn Ads', 'Twitter Ads']
    search = ['Google Ads', 'Bing Ads', 'Yahoo Ads']
    email = ['Email', 'Klaviyo', 'Mailchimp']

    if media_source in social:
        return 'Social'
    elif media_source in search:
        return 'Search'
    elif media_source in email:
        return 'Email'
    else:
        return 'Other'

<!-- Facebook Ads Template -->
https://example.com/?l5ss={{site_source_name}}&l5adid={{ad.id}}&l5c={{campaign.name}}

<!-- Google Ads Template -->
https://example.com/?l5ss=google&l5adid={creative}&l5c={campaignid}

<!-- TikTok Ads Template -->
https://example.com/?l5ss=tiktok&l5adid=__CID__&l5c=__CAMPAIGN_NAME__

┌─────────────────────────────────────────────────────────────────┐
│ REVENUE RECONCILIATION REPORT                                   │
│ Date Range: Feb 19-25, 2026                                     │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│ Shopify Admin Total:              $255,871.60                   │
│ Mission Control Orders Table:     $255,871.60  OK Match         │
│                                                                 │
│ ───────────────────────────────────────────────────────────────│
│                                                                 │
│ Attribution Breakdown:                                          │
│   Last Click Attribution:         $242,150.30  (94.6% tracked) │
│   Untracked Orders:               $ 13,721.30  (5.4% direct)   │
│                                                                 │
│   Any Click Attribution:          $298,940.03  (116.8% <- overcounting!)
│   Average Touchpoints:            1.17x per order               │
│                                                                 │
│ ───────────────────────────────────────────────────────────────│
│                                                                 │
│ Executive Summary Gross Sales:    $255,871.60  OK Match         │
│ Source: shopify_daily_data table                               │
│ Days Synced: 7/7                                                │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

WITH shopify_truth AS (
    SELECT
        COUNT(DISTINCT order_number) as total_orders,
        SUM(revenue) as total_revenue
    FROM orders
    WHERE client_id = 'qalo'
      AND order_date BETWEEN '2026-02-19' AND '2026-02-25'
),
attributed_orders AS (
    SELECT
        COUNT(DISTINCT tr_orderid) as attributed_orders,
        SUM(last_click_revenue)/100 as last_click_revenue,
        SUM(any_click_revenue)/100 as any_click_revenue
    FROM (
        SELECT DISTINCT tr_orderid, last_click_revenue, any_click_revenue
        FROM interaction_insight_summary
        WHERE client_id = 'qalo'
          AND event_date BETWEEN '2026-02-19' AND '2026-02-25'
    ) t
),
daily_summary AS (
    SELECT
        SUM(gross_sales) as daily_gross_sales,
        COUNT(*) as days_synced
    FROM shopify_daily_data
    WHERE client_id = 'qalo'
      AND event_date BETWEEN '2026-02-19' AND '2026-02-25'
)
SELECT
    s.total_orders,
    s.total_revenue,
    a.attributed_orders,
    s.total_orders - a.attributed_orders as untracked_orders,
    s.total_revenue - a.last_click_revenue as untracked_revenue,
    a.last_click_revenue,
    a.any_click_revenue,
    a.any_click_revenue / s.total_revenue as overcount_ratio,
    d.daily_gross_sales,
    d.days_synced
FROM shopify_truth s
CROSS JOIN attributed_orders a
CROSS JOIN daily_summary d;

-- Check which table actually exists
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name LIKE 'shopify_daily%';

WITH qalo AS (
    SELECT client_id FROM client
    WHERE LOWER(client_name) LIKE '%qalo%' LIMIT 1
)
SELECT
    'Orders Table (Truth)' as source,
    COALESCE(SUM(revenue), 0) as revenue
FROM orders, qalo
WHERE orders.client_id = qalo.client_id
  AND order_date BETWEEN '2026-02-19' AND '2026-02-25'

UNION ALL

SELECT
    'Daily Summary Table' as source,
    COALESCE(SUM(gross_sales), 0) as revenue
FROM shopify_daily_data, qalo
WHERE shopify_daily_data.client_id = qalo.client_id
  AND event_date BETWEEN '2026-02-19' AND '2026-02-25'

UNION ALL

SELECT
    'Attribution - Last Click' as source,
    COALESCE(SUM(last_click_revenue)/100, 0) as revenue
FROM interaction_insight_summary, qalo
WHERE interaction_insight_summary.client_id = qalo.client_id
  AND event_date BETWEEN '2026-02-19' AND '2026-02-25'

UNION ALL

SELECT
    'Attribution - Any Click' as source,
    COALESCE(SUM(any_click_revenue)/100, 0) as revenue
FROM interaction_insight_summary, qalo
WHERE interaction_insight_summary.client_id = qalo.client_id
  AND event_date BETWEEN '2026-02-19' AND '2026-02-25';

-- Orders that have no attribution touchpoints
SELECT
    o.order_number,
    o.order_date,
    o.revenue,
    'No touchpoints' as reason
FROM orders o
LEFT JOIN eventapp_ordercredit oc ON o.order_number = oc.tr_orderid
WHERE o.client_id = (SELECT client_id FROM client WHERE LOWER(client_name) LIKE '%qalo%' LIMIT 1)
  AND o.order_date BETWEEN '2026-02-19' AND '2026-02-25'
  AND oc.tr_orderid IS NULL
ORDER BY o.revenue DESC
LIMIT 50;

-- Check if all 7 days exist in daily summary
WITH qalo AS (
    SELECT client_id FROM client WHERE LOWER(client_name) LIKE '%qalo%' LIMIT 1
),
expected_dates AS (
    SELECT generate_series(
        '2026-02-19'::date,
        '2026-02-25'::date,
        '1 day'::interval
    )::date as date
)
SELECT
    ed.date,
    COALESCE(sd.total_orders, 0) as orders,
    COALESCE(sd.gross_sales, 0) as gross_sales,
    CASE WHEN sd.event_date IS NULL THEN 'NO MISSING' ELSE 'OK' END as status
FROM expected_dates ed
LEFT JOIN shopify_daily_data sd ON sd.event_date = ed.date
    AND sd.client_id = (SELECT client_id FROM qalo)
ORDER BY ed.date;