Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/andresshm/fini-marketing-intelligence/llms.txt

Use this file to discover all available pages before exploring further.

The Fini warehouse exposes two SQL views that sit on top of the star schema and serve as the primary interface for all downstream analytics. Rather than repeating join logic across every script, the views centralise it once and present a flat, denormalised result set that Python code can query directly with pd.read_sql. Both views are defined in sql/views.sql and are created with CREATE OR REPLACE VIEW so they can be safely re-applied without dropping dependent queries.

​
vw_sales_enriched

vw_sales_enriched is the general-purpose analytics view. It joins all three tables in the star schema — fact_sales, dim_products, and dim_customers — into a single wide result set, giving every transaction its full product and customer context. This is the view the insights engine (insights/generate_insights.py) and the RFM segmentation model (insights/generate_rfm.py) read from.

​
DDL

CREATE OR REPLACE VIEW vw_sales_enriched AS
SELECT
    f.sale_id,
    f.sale_date,
    f.units,
    f.discount,
    f.revenue,
    f.margin,

    p.product_name,
    p.category,
    p.season,
    p.launch_date,

    c.region,
    c.purchase_frequency,
    c.preferred_channel,
    c.age_group

FROM fact_sales f
JOIN dim_products p
    ON f.product_id = p.product_id
JOIN dim_customers c
    ON f.customer_id = c.customer_id;

​
Output Columns

ColumnSourceDescription
sale_idfact_salesTransaction primary key
sale_datefact_salesDate of the transaction
unitsfact_salesUnits sold in this transaction
discountfact_salesFractional discount applied (0.00–0.30)
revenuefact_salesNet revenue after discount
marginfact_salesGross margin (revenue − cost)
product_namedim_productsHuman-readable product name
categorydim_productsProduct family (Gummies, Belts, etc.)
seasondim_productsSeasonal positioning of the product
launch_datedim_productsDate the product was first available
regiondim_customersGeographic region of the customer
purchase_frequencydim_customersBehavioural frequency segment (Low / Medium / High)
preferred_channeldim_customersCustomer’s primary sales channel
age_groupdim_customersCustomer age bracket

​
How It’s Used

insights/generate_insights.py reads the entire view in a single pd.read_sql call and then slices the resulting DataFrame for each business insight — revenue by purchase_frequency, average units and margin across discount groups, and revenue by region. The flat structure means no Python-side joins are required. 
sales = pd.read_sql("""
SELECT *
FROM vw_sales_enriched
""", engine)

​
Example Query

Revenue breakdown by product category and sales channel, ordered by total revenue descending: 
SELECT
    category,
    preferred_channel,
    SUM(revenue)                   AS total_revenue,
    ROUND(AVG(margin)::NUMERIC, 2) AS avg_margin,
    COUNT(*)                       AS transactions
FROM vw_sales_enriched
GROUP BY
    category,
    preferred_channel
ORDER BY
    total_revenue DESC;

​
vw_product_launches

vw_product_launches is a specialised view focused on post-launch performance. It joins fact_sales with dim_products and adds a computed column — days_since_launch — that measures how far each transaction occurred from the product’s release date. A WHERE clause filters out any sales that pre-date the launch (which cannot occur in the synthetic data, but the guard makes the view safe against future real-world data imports).

​
DDL

CREATE OR REPLACE VIEW vw_product_launches AS
SELECT
    f.sale_id,
    f.sale_date,

    p.product_id,
    p.product_name,
    p.category,
    p.launch_date,

    f.units,
    f.revenue,
    f.margin,

    (f.sale_date - p.launch_date) AS days_since_launch

FROM fact_sales f
JOIN dim_products p
    ON f.product_id = p.product_id

WHERE f.sale_date >= p.launch_date;

​
Output Columns

ColumnSourceDescription
sale_idfact_salesTransaction primary key
sale_datefact_salesDate of the transaction
product_iddim_productsProduct surrogate key
product_namedim_productsHuman-readable product name
categorydim_productsProduct family
launch_datedim_productsDate the product was first available
unitsfact_salesUnits sold in this transaction
revenuefact_salesNet revenue after discount
marginfact_salesGross margin for this transaction
days_since_launchComputedsale_date − launch_date as an integer number of days

​
The days_since_launch Column

days_since_launch is a derived integer computed directly in the SELECT list as a PostgreSQL date subtraction. A value of 0 means the product was sold on its launch day; a value of 90 means 90 days after launch. The insights engine uses this to define a first-90-days launch window and rank products by revenue velocity.

​
The WHERE Clause

WHERE f.sale_date >= p.launch_date ensures only valid post-launch transactions are included. This makes days_since_launch always non-negative and prevents edge-case negative values from distorting launch-window aggregations.

​
How It’s Used

insights/generate_insights.py filters the view to the first 90 days after launch and aggregates revenue by product_name to identify the best-performing new product: 
launches = pd.read_sql("""
SELECT *
FROM vw_product_launches
""", engine)

launch_top = (
    launches[launches["days_since_launch"] <= 90]
    .groupby("product_name")["revenue"]
    .sum()
    .sort_values(ascending=False)
)

​
Example Query

Cumulative revenue by product at 30, 60, and 90 days post-launch: 
SELECT
    product_name,
    category,
    SUM(revenue) FILTER (WHERE days_since_launch <= 30)  AS rev_day_30,
    SUM(revenue) FILTER (WHERE days_since_launch <= 60)  AS rev_day_60,
    SUM(revenue) FILTER (WHERE days_since_launch <= 90)  AS rev_day_90,
    SUM(revenue)                                          AS rev_total
FROM vw_product_launches
GROUP BY
    product_name,
    category
ORDER BY
    rev_day_90 DESC NULLS LAST;
Neither view selects cost from fact_sales. The vw_product_launches view omits cost because launch-velocity analysis focuses on revenue and margin; cost can always be reconstructed as revenue - margin from the available columns if needed.

Build docs developers (and LLMs) love

Get started for freeTalk to us