Per-Flock Profitability Analysis with Business Intelligence: A Practical Guide

In the layer poultry industry, calculating profitability per flock is often done with manual spreadsheets after harvest. Yet, with an 18-20 month production cycle and fluctuations in feed and egg prices, decisions such as when to cull or change feed must be made quickly. For example, a farm with 10,000 layers can lose Rp 15-20 million per flock just because it detects an FCR decline late. Business intelligence (BI) allows you to combine feed, production, mortality, and sales data into one real-time dashboard. This article discusses practical steps to build per-flock profitability analysis using PostgreSQL 15, Metabase 0.46, and Python 3.10, complete with example queries and best practices.

Why Is Per-Flock Profitability Difficult to Measure?

Farm data is usually scattered in many places: feed records in coop spreadsheets, egg production in manual books, sales in the POS, and operating costs in the cash book. As a result, per-flock profit calculations often ignore shared costs such as electricity, labor, and medicines. A real example: flock A with 5,000 birds generates egg sales of Rp 600 million and feed costs of Rp 450 million. If electricity costs of Rp 15 million and medicine costs of Rp 10 million are not allocated, profit appears to be Rp 150 million, when it is actually Rp 125 million—a 20% overestimate.

The second problem is data quality. Feed entries in sacks (50 kg) are mixed with kilograms, or there are transactions that are not recorded at harvest. Without validation, BI queries will produce misleading margins. Metabase 0.46 can perform joins between tables, but data quality must still be maintained at the source. Use PostgreSQL 15 constraints such as NOT NULL and CHECK on the feed_usage table to ensure feed quantities are always positive.

Third, report latency. Monthly profit reports are not enough for making weekly decisions. You need a pipeline that loads daily data from the POS and coops into the data warehouse. Python 3.10 with pandas 2.0 can extract data from the POS API, clean it, and then load it into PostgreSQL in minutes. That way, the dashboard always displays the latest per-flock profit.

Building a Data Pipeline and Profitability Query

The first step is to design a simple star schema: fact tables feed_usage, egg_production, sales, expenses, and dimension tables flocks, dates, feed_types. Allocate shared costs to each flock based on the proportion of the number of birds or coop area. For example, a monthly electricity cost of Rp 30 million for 3 flocks with populations of 5,000, 8,000, and 10,000 birds. The first flock gets 5,000/23,000 × Rp 30 million = Rp 6.5 million.

After the data is clean, create a profitability query. The following query calculates profit and margin per flock assuming costs have been allocated:

SELECT f.flock_id, f.start_date, f.end_date,
       SUM(feed.cost) AS total_feed_cost,
       SUM(sale.revenue) AS total_revenue,
       SUM(expense.amount) AS total_expense,
       (SUM(sale.revenue) - SUM(feed.cost) - SUM(expense.amount)) AS profit,
       ROUND((SUM(sale.revenue) - SUM(feed.cost) - SUM(expense.amount)) / SUM(sale.revenue) * 100, 2) AS margin
FROM flocks f
LEFT JOIN feed_usage feed ON f.flock_id = feed.flock_id
LEFT JOIN sales sale ON f.flock_id = sale.flock_id
LEFT JOIN expenses expense ON f.flock_id = expense.flock_id
GROUP BY f.flock_id, f.start_date, f.end_date
ORDER BY margin DESC;

Save this query as a native question in Metabase 0.46. Create a bar chart visualization of profit per flock, then add a date filter. For validation, compare the query results with the manual calculation for flock 2023-01 (8,000 birds, FCR 2.1, profit Rp 120 million). If the difference is more than 5%, check the cost allocation or missing entries.

The ETL pipeline can be run with a daily cron at 02:00. Use Python pandas to read CSV from the POS, transform columns, then to_sql to PostgreSQL. Add simple logging: record the number of rows loaded and errors. That way, you know if any data failed to load before the dashboard is used in the morning.

Dashboard and Key Metrics for Quick Decisions

The per-flock profitability dashboard must contain five main metrics: profit per flock, margin (%), FCR (feed conversion ratio), mortality (%), and IP (income over feed cost). Display them on one Metabase page: a bar chart of profit per flock, a line chart of the margin trend over the last 6 months, and a scatter plot of FCR vs profit. That way, the coop manager immediately sees which flock needs attention.

Take advantage of the Metabase 0.46 alert feature. Create an alert if a flock's margin falls below 10% or FCR exceeds 2.2. Alerts are sent via email or Slack every day. Example: flock B has an 8% margin because of an FCR of 2.4—the alert triggers a check on feed quality or coop temperature. This saves time compared with waiting for monthly reports.

Integrate the dashboard with the Poultry/Layer ERP you already use. If the ERP provides an API, schedule synchronization of flock master data and daily transactions. That way, profitability analysis does not require duplicate entries. For teams that do not yet have an ERP, start with a structured Google Sheets, then migrate to PostgreSQL after data volume increases.

Best Practices

1. Define shared cost allocation in writing. Use the proportion of the number of birds or coop area, and be consistent across all flocks.
2. Standardize units and date formats. Store feed in kg, eggs in pieces, and money in Rupiah without decimals.
3. Automate daily ETL with validation. Check row counts, negative values, and duplicates before data enters the fact table.
4. Create a data quality dashboard. Display the percentage of missing feed entries per coop so they can be fixed immediately.
5. Review the dashboard weekly with the coop manager. Discuss flocks with low margins and improvement action plans.

FAQ

How long does BI implementation for per-flock analysis take?

For one farm with already digital data, basic implementation (pipeline + dashboard) takes 2-4 weeks. If data is still manual, add 2 weeks for digitizing feed and production forms.

Is it mandatory to use Metabase?

No. Metabase 0.46 was chosen because it is open-source and easy. Other alternatives: Power BI, Looker Studio, or Apache Superset. What matters is that the tool supports SQL queries and alerts.

What if feed data is still recorded manually in the coop?

Start with Google Sheets or a validated online form. After 1-2 cycles, migrate to PostgreSQL. The key to success is consistency of daily recording, not expensive tools.

Which metrics are most important for flock profitability?

Margin per flock and FCR. Margin shows final profitability, while FCR detects feed efficiency earlier. Also monitor mortality and IP for complete context.

Building per-flock profitability analysis with BI is not a large project if started from available data. Focus on correct cost allocation, daily pipelines, and actionable dashboards. If you want to accelerate implementation or integrate this analysis with ERP Poultry/Layer, consult your needs with Nugroho Setiawan. With experience as an Operations Manager and Full Stack Developer, he can help design data architecture, build dashboards, and train your team. Visit this blog for other technical articles about BI and livestock information systems.

Related solutions by PT Pilar Inovasi Informatika (Pilar Inovasi IT products): PilarPOS Agriculture ERP. Need a free consultation?