us-excel-template-data 0.1.0

//! Query API over the embedded catalog CSV (1,008 rows).
//!
//! The raw file is available in the crate sources (`data/`) and mirrors the CC BY 4.0
//! dataset published on [Zenodo](https://doi.org/10.5281/zenodo.21416251) by
//! [TableTemplates](https://tabletemplates.com/).

const RAW: &str = include_str!("../data/excel-template-search-demand-us-2026.csv");

/// One keyword of the catalog.
#[derive(Debug, Clone, PartialEq)]
pub struct Row {
    /// Template-related search query (US, English).
    pub keyword: &'static str,
    /// Estimated monthly US search volume (2026).
    pub monthly_search_volume_us: u32,
    /// One of the 16 practical categories (kebab-case slug).
    pub category: &'static str,
    /// URL of a free, no-signup implementation in the
    /// [free template library](https://tabletemplates.com/free/), when one exists.
    pub free_template_url: Option<&'static str>,
}

/// Aggregated demand for one category.
#[derive(Debug, Clone, PartialEq)]
pub struct CategoryStats {
    /// Category slug (e.g. `project-management`).
    pub category: &'static str,
    /// Number of keywords in the category.
    pub keywords: usize,
    /// Sum of the monthly US volumes of those keywords.
    pub combined_monthly_volume_us: u64,
}

/// Every row of the catalog, in file order.
pub fn all() -> Vec<Row> {
    RAW.lines()
        .skip(1)
        .filter(|l| !l.trim().is_empty())
        .map(|line| {
            let mut it = line.splitn(5, ',');
            let keyword = it.next().unwrap_or_default();
            let volume = it.next().unwrap_or("0").parse().unwrap_or(0);
            let category = it.next().unwrap_or_default();
            let _has_free = it.next();
            let url = it.next().unwrap_or_default().trim();
            Row {
                keyword,
                monthly_search_volume_us: volume,
                category,
                free_template_url: if url.is_empty() { None } else { Some(url) },
            }
        })
        .collect()
}

/// The `n` highest-volume keywords.
pub fn top(n: usize) -> Vec<Row> {
    let mut rows = all();
    rows.sort_by(|a, b| b.monthly_search_volume_us.cmp(&a.monthly_search_volume_us));
    rows.truncate(n);
    rows
}

/// Rows whose keyword contains `needle` (case-insensitive), ordered by volume.
pub fn search(needle: &str) -> Vec<Row> {
    let q = needle.to_lowercase();
    let mut rows: Vec<Row> = all()
        .into_iter()
        .filter(|r| r.keyword.to_lowercase().contains(&q))
        .collect();
    rows.sort_by(|a, b| b.monthly_search_volume_us.cmp(&a.monthly_search_volume_us));
    rows
}

/// Rows of one category (slug, e.g. `bookkeeping-accounting`), ordered by volume.
pub fn by_category(slug: &str) -> Vec<Row> {
    let mut rows: Vec<Row> = all().into_iter().filter(|r| r.category == slug).collect();
    rows.sort_by(|a, b| b.monthly_search_volume_us.cmp(&a.monthly_search_volume_us));
    rows
}

/// The 16 categories with aggregated stats, ordered by combined volume (descending).
pub fn categories() -> Vec<CategoryStats> {
    let mut cats: Vec<CategoryStats> = Vec::new();
    for row in all() {
        match cats.iter_mut().find(|c| c.category == row.category) {
            Some(c) => {
                c.keywords += 1;
                c.combined_monthly_volume_us += u64::from(row.monthly_search_volume_us);
            }
            None => cats.push(CategoryStats {
                category: row.category,
                keywords: 1,
                combined_monthly_volume_us: u64::from(row.monthly_search_volume_us),
            }),
        }
    }
    cats.sort_by(|a, b| b.combined_monthly_volume_us.cmp(&a.combined_monthly_volume_us));
    cats
}

/// Rows that have a free, no-signup implementation, ordered by volume.
pub fn with_free_template() -> Vec<Row> {
    let mut rows: Vec<Row> = all().into_iter().filter(|r| r.free_template_url.is_some()).collect();
    rows.sort_by(|a, b| b.monthly_search_volume_us.cmp(&a.monthly_search_volume_us));
    rows
}