AVI_SITE_DATASET_snowflake_settings.txt

## Overview
This file provides instructions for extracting data from the SITE_EXTENDED_Snowflake table which should use 'ANALYTICS.SIGMA_SCRATCH.VIEW_AVI_SITE_DATASET_FROM_SQL_4D44494CEF294AF093BA92B8C008977A'. 
For 'like' operator, be sure to use 'like' as opposed to 'Ilike'. 
If a user is looking for 'Enterprise' or 'Creator' site groups, please use the column 'NEW SERVICE CONDENSED'. Please use 'NEW SERVICE CONDENSED' instead of 'SERVICE' OR 'SERVICE CONDENSED', unless specifically requested by the user.
If a user is looking for specific URL's, please use the 'like' operator along with '%' on both ends to help locate the correct URL.
Do not apply a date filter unless specified by the user which date filter they would like to use.

IMPORTANT: All filters on TIMESTAMP columns (e.g., "Install Date (SITE_EXTENDED)") must use string date literals. 
Do NOT output any numeric literals such as 20250401. The correct format is 'YYYY-MM-DD' (e.g., '2025-04-01').

Correct Usage:
    AND "Install Date (SITE_EXTENDED)" > '2025-04-01'

Incorrect Usage:
    AND "Install Date (SITE_EXTENDED)" > 20250401

Rule: Under no circumstances should a TIMESTAMP column filter be written with a numeric literal.


## Example Query:
SELECT
    "SITE ID",
    "STATUS",
    "TIER",
    ...
FROM "ANALYTICS"."SIGMA_SCRATCH"."VIEW_AVI_SITE_DATASET_FROM_SQL_4D44494CEF294AF093BA92B8C008977A"
WHERE
    "STATUS" = 'Active'
    -- Make sure to wrap column names in quotes
    AND "Install Date (SITE_EXTENDED)" >= '2025-04-01'


*********************************************
DATE FILTERING RULES
*********************************************
- The "Install Date (SITE_EXTENDED)" column is defined as TIMESTAMP_LTZ(9).
- **Always** use date or timestamp string literals when filtering this column.
- Do **not** use numeric values (e.g., 20250401). This causes type conversion errors.
- Correct Format: Use the format 'YYYY-MM-DD' (or 'YYYY-MM-DD HH24:MI:SS' if a time component is needed).
*********************************************




## IMPORTANT
All column names should be wrapper with parenthesis. For example, "TIER".


## Example outputs for most common columns used in this table to use as a reference.

{
    "title": "Example outputs for each column",
    "column_names": [
        "TIER",
        "STATUS",
        "SERVICE",
        "SERVICE CONDENSED",
        "NEW SERVICE CONDENSED",
        "SERVICE LEVEL",
        "PRIMARY VERTICAL"
    ],
    "columns": {
        "TIER": [
            "AdThrive 1",
            "AdThrive 2",
            "AdThrive 3",
            "AdThrive 4",
            "AdThrive 5",
            "AdThrive 6",
            "AdThrive 7",
            "AdThrive 8"
        ],
        "STATUS": [
            "Active",
            "Checkup",
            "Dropped",
            "Install",
            "Pending",
            "Pending Manager Recommendation",
            "Setup"
        ],
        "SERVICE": [
            "AdThrive Edge",
            "AdThrive Premium",
            "Prime"
        ],
        "SERVICE CONDENSED": [
            "AdThrive",
            "Prime"
        ],
        "NEW SERVICE CONDENSED": [
            "Creator",
            "Enterprise"
        ],
        "SERVICE LEVEL": [
            "Enterprise",
            "Insider",
            "Luminary",
            "Platinum",
            "Platinum Elite",
            "Rise"
        ],
        "PRIMARY VERTICAL": [
            "Arts & Creativity",
            "Auto",
            "Baby",
            "Beauty",
            "Business",
            "Careers",
            "Clean Eating",
            "Crafts",
            "Deals",
            "Education",
            "Entertainment",
            "Family and Parenting",
            "Fitness",
            "Food",
            "Gaming",
            "Gardening",
            "Green Living",
            "Health and Wellness",
            "History & Culture",
            "Hobbies & Interests",
            "Home Decor and Design",
            "Law, Gov't & Politics",
            "Lifestyle",
            "Mens Style and Grooming",
            "Natural Parenting",
            "News",
            "Other",
            "Personal Finance",
            "Pets",
            "Pregnancy",
            "Professional Finance",
            "Real Estate",
            "Religion & Spirituality",
            "Science",
            "Shopping",
            "Sports",
            "Tech",
            "Toddler",
            "Travel",
            "Vegetarian",
            "Wedding",
            "Womens Style"
        ]
    }
}