Writers need useful information, not just a simple statement like "you earned $500 this month."
I've been thinking about this a lot lately. Adsloty's booking process works well, and money transfers correctly. But when a writer logs in, what do they see? A list of bookings and a number. That's not enough information.
If you make money from your newsletter by selling ad slots, you need to know more about how it works. Which sponsors return for more ads? Is your average booking amount increasing or decreasing? Are you turning down too many requests? Which months are busy, and which are slow?
Without this information, it's hard to make informed decisions. You can't set better prices if you don't know your conversion rate. You can't plan your schedule without seeing seasonal trends. You can't negotiate with a returning sponsor if you don’t realize they’ve spent $3,000 with you over six months.
So, I created the dashboard I would want if I were running a newsletter.
Revenue analytics
When a writer logs into Adsloty, the first thing they see is a summary of their revenue. It shows not just one number, but the complete picture.
-- Writer revenue stats: one query, all the context
SELECT
writer_id,
COALESCE(SUM(CASE
WHEN status IN ('completed', 'pending', 'processing')
THEN amount ELSE 0
END), 0) as total_earned,
COALESCE(SUM(CASE
WHEN status = 'completed'
THEN amount ELSE 0
END), 0) as available_balance,
COALESCE(SUM(CASE
WHEN status IN ('pending', 'processing')
THEN amount ELSE 0
END), 0) as pending_balance,
COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_payouts,
COUNT(CASE WHEN status IN ('pending', 'processing') THEN 1 END) as pending_payouts
FROM payouts
WHERE writer_id = $1
You can see all your earnings and balances in one place. Your dashboard shows how much you’ve made, what money is available, and what is still pending. It also compares completed payouts to pending ones, so you can easily understand your financial status.
You can view your earnings by different time periods — this month, last month, or overall — and by each sponsor. This breakdown is useful. If you notice that one company has booked five times in three months, it shows that it's a good relationship to maintain. You might want to reach out with a loyalty discount or offer premium placement. This data helps you make decisions without the platform needing to do it for you.
#[derive(Debug, Serialize)]
pub struct DashboardStatsResponse {
pub total_revenue: String,
pub total_bookings: i32,
pub active_bookings: i32,
pub pending_bookings: i32,
pub available_slots: i32,
pub pending_payout_amount: String,
pub average_booking_amount: Option<String>,
pub average_fit_score: Option<f64>,
pub last_booking_date: Option<String>,
pub cache_age_seconds: i64,
}
Notice the cache_age_seconds at the bottom. Dashboard stats don’t need to be updated in real-time. I save the combined stats and refresh them regularly. The writer can see how recent the data is, and I avoid overloading the database every time someone updates their dashboard.
Performance metrics
Understanding your revenue is important, but knowing the reasons behind it helps even more. Here are some important metrics that writers should track:
Total bookings over time. Look at trends, not just numbers. Are your bookings going up each month? Did you get more bookings after tweeting about sponsorships? Did they drop in December? The trend is more important than the count.
Average booking value. If your average booking was $150 three months ago and is now $200, your price increase hasn’t hurt demand. If it went down, you might have raised prices too quickly.
Conversion rate. This is often overlooked. Out of every 10 booking requests, how many do you accept? If you reject 80% of requests, your listing might attract the wrong sponsors or your standards may be too high for your audience size. This is valuable information to improve upon.
AI fit score trends. This metric is specific to Adsloty. Each ad gets an AI score before you see it. I can show you the average fit score over time. If it’s going up, you’re attracting the right sponsors. If it’s going down, it might be time to update your niche description.
Most popular slots. Which days get the most bookings? Which newsletter positions sell best? If Tuesday slots fill up but Thursday ones don't, you might want to change their prices or stop offering Thursdays altogether.
The PostgreSQL deep dive
I got a bit technical while calculating these metrics. It was quite challenging to do it efficiently.
At first, I used a straightforward method: I ran a separate query for each metric. For total revenue, I made one query. For average booking value, another. I did the same for month-over-month growth and conversion rate. This method worked, but it required eight database requests to load the dashboard, making it feel slow.
Then I discovered PostgreSQL window functions, which changed everything.
-- Running totals, growth rates, and trends in a single pass
SELECT
date_trunc('month', b.created_at) as month,
COUNT(*) as bookings,
SUM(b.writer_payout) as revenue,
AVG(b.writer_payout) as avg_booking_value,
-- Month-over-month growth
SUM(b.writer_payout) - LAG(SUM(b.writer_payout))
OVER (ORDER BY date_trunc('month', b.created_at))
as revenue_change,
-- Running total
SUM(SUM(b.writer_payout))
OVER (ORDER BY date_trunc('month', b.created_at))
as cumulative_revenue,
-- Conversion rate per month
COUNT(CASE WHEN b.status = 'confirmed' OR b.status = 'completed' THEN 1 END)::FLOAT
/ NULLIF(COUNT(*), 0) as conversion_rate
FROM bookings b
WHERE b.writer_id = $1
AND b.created_at >= NOW() - INTERVAL '12 months'
GROUP BY date_trunc('month', b.created_at)
ORDER BY month
I have one question. I want to calculate running totals, monthly growth, conversion rates, and averages directly in the database. This method is faster than using Rust, where I would have to manually go through data.
Using the LAG() function, I can get last month's revenue to work out growth. The SUM() OVER() function lets me find the running total. The NULLIF function helps avoid division by zero when there are months with no bookings.
I am really thankful that I chose Postgres for this. The query optimizer works really well. What used to take eight queries and 200 milliseconds now only takes one query and 30 milliseconds.
The visual layer
Numbers in a table are helpful. Numbers in a chart tell a story.
I used Recharts for my frontend visualizations. I tried other charting libraries like Chart.js, D3, and Nivo, but Recharts was the best fit for my needs. It works well with React, allows for customization, and is easy to use.
<ResponsiveContainer width="100%" height={300}>
<AreaChart data={revenueData}>
<defs>
<linearGradient id="revenueGradient" x1="0" y1="0" x2="0" y2="1">
<stop offset="5%" stopColor="#4F46E5" stopOpacity={0.3} />
<stop offset="95%" stopColor="#4F46E5" stopOpacity={0} />
</linearGradient>
</defs>
<XAxis
dataKey="month"
tickFormatter={(val) => format(new Date(val), 'MMM')}
/>
<YAxis tickFormatter={(val) => `$${val}`} />
<Tooltip
formatter={(value: number) => [`$${value.toFixed(2)}`, 'Revenue']}
labelFormatter={(label) => format(new Date(label), 'MMMM yyyy')}
/>
<Area
type="monotone"
dataKey="revenue"
stroke="#4F46E5"
fill="url(#revenueGradient)"
/>
</AreaChart>
</ResponsiveContainer>
Revenue is shown with an area chart, which highlights trends with a gradient fill. Bookings are displayed as a bar chart, making it easier to compare individual events. The conversion rate uses a line chart to show if it is going up or down.
I have also added a booking calendar view. Writers can quickly see which dates are booked, which have pending requests, and which are available. While this may seem simple, it required bringing together booking data, blackout dates (when the writer is unavailable), and availability settings into one view.
Empty states that don't make you feel bad
This is a small issue that really matters.
When new writers sign up, their dashboard shows no data. They see zero bookings and zero revenue. Every chart is blank. This can feel discouraging, as if the platform is already telling them they are failing.
I focused on improving these empty states. Instead of blank charts, new writers see:
- "No bookings yet. Your first one is coming," with a link to check their listing.
- The revenue chart shows a note saying, "Your revenue trend will appear here."
- The calendar shows available slots highlighted, with a positive note — "You have 12 slots available this month."
There are no sad empty boxes and no "0" in big letters. The dashboard should feel like a starting line, not a report card.
Real-time notifications
When a new booking comes in, the writer should see it right away without refreshing their dashboard.
I thought about using WebSockets for this, but then I realized that I only need one-way communication. The server tells the client about the event, but the client doesn't need to respond back. WebSockets allow two-way communication and are more complicated than I need.
Server-Sent Events (SSE) are a better choice. They use one continuous HTTP connection, where the server sends updates to the client. The client listens for these updates. If the connection drops, the browser reconnects automatically. Every browser supports this with EventSource, so no extra library is needed.
// Backend: SSE endpoint for real-time notifications
async fn notification_stream(
State(state): State<AppState>,
auth_user: AuthUser,
) -> Sse<impl Stream<Item = Result<Event, Infallible>>> {
let stream = BroadcastStream::new(state.notification_tx.subscribe())
.filter_map(move |msg| {
match msg {
Ok(notification) if notification.user_id == auth_user.id => {
Some(Ok(Event::default()
.event("notification")
.data(serde_json::to_string(¬ification).unwrap())))
}
_ => None,
}
});
Sse::new(stream).keep_alive(
axum::response::sse::KeepAlive::new()
.interval(Duration::from_secs(30))
)
}
// Frontend: three lines to listen
const events = new EventSource('/api/notifications/stream');
events.addEventListener('notification', (e) => {
const notification = JSON.parse(e.data);
toast.info(notification.message);
queryClient.invalidateQueries({ queryKey: ['dashboard'] });
});
When a booking comes in, the writer sees a notification pop up. The dashboard updates automatically, so there is no need to refresh or poll for updates.
The backend sends a signal every 30 seconds to keep the connection alive, preventing proxies and load balancers from closing it due to inactivity.
The sponsor's side
Sponsors need a simple dashboard to check their campaigns:
- See all campaigns with their status (active, completed, pending)
- View total spending and spending by newsletter
- Access booking history with ad performance (once click tracking is available)
- Quickly rebook — one click to book the same newsletter again
I made the sponsor dashboard simple on purpose. Sponsors want to know two things: "Did my ad run?" and "Was it worth it?" The dashboard should provide answers to both questions quickly.
// Sponsor booking history — includes writer and newsletter details
let bookings = sqlx::query_as::<_, SponsorBookingView>(
r#"
SELECT
b.id, b.status, b.amount, b.newsletter_date,
b.ad_title, b.ad_url,
w.newsletter_name, w.subscriber_count,
b.ai_fit_score, b.created_at
FROM bookings b
JOIN writers w ON b.writer_id = w.id
WHERE b.sponsor_id = $1
ORDER BY b.created_at DESC
"#
)
.bind(sponsor_id)
.fetch_all(pool)
.await?;
What's still missing
I'm being clear about what still needs to be done:
Click tracking. This is the biggest gap. Sponsors want to know how many people clicked their ad. Writers want to show that their audience is engaged. I need to create a system that tracks clicks before sending users to the sponsor's URL. This is not too difficult, but it involves the widget, the booking model, and the analytics pipeline, so I will tackle it in a focused sprint.
A/B testing insights. Eventually, I want sponsors to test two versions of their ad copy and see which one works better. We need click tracking first, so this is on hold.
Audience overlap analysis. If a sponsor books ads in three newsletters, are they reaching the same audience three times or three different ones? This is a complex issue. I have some ideas, but it will come later.
The bugs I found
Building the dashboard revealed three issues in the booking process:
Some payouts showed "pending" indefinitely. The job that processes payouts looked for bookings marked as "confirmed," but some had already changed to "completed" by the time the job ran. As a result, the payout remained stuck.
Approved bookings did not change to "completed" after the publication date. The date comparison used UTC, while the newsletter dates were stored without time zone information. For example, a booking for February 20th in EST was compared to February 20th UTC. Depending on when the job ran, it might miss the deadline.
The dashboard statistics counted refunded bookings in the "total bookings," which distorted the count and made the conversion rate appear incorrect.
All three issues required only one or two lines of code to fix. Without the dashboard highlighting these problems, they could have gone unnoticed.
The hidden advantage of creating analytics is that it encourages you to examine your data, which can reveal flaws in your logic.
Advice for building a marketplace dashboard
Cache your data calculations. Dashboards refresh often, and running heavy queries each time will slow down your database.
Use window functions. If you are calculating trends or totals in your application code, it’s too much work. Let Postgres do it.
Design empty states first. Your first users will see these before they see any data. Make these states feel like an opportunity, not a failure.
Choose SSE over WebSockets for one-way updates. It’s simpler, reconnects automatically, and doesn’t require a client library.
Start building the dashboard early. Don’t wait for user requests. It will help you spot bugs in your system that you wouldn’t find otherwise.
Next, I might write about AI fit scoring in detail. I will cover how the prompts work, whether the scores are useful, and what to do when the AI gives bad advice confidently.
Top comments (0)