I’m trying to build a visual progress tracker in Google Sheets that displays campaign delivery status similar to what you see in ad management platforms. The progress indicator should calculate delivery performance by comparing actual impressions delivered against the campaign timeline. Here’s how it works: Progress Formula: delivery_rate = (impressions_delivered / impression_target) / (days_passed / total_campaign_days) For instance, if a campaign delivered 40% of its target impressions after 40% of the campaign duration has passed, the delivery rate would be 100% (perfectly on track). But if the same 40% was delivered after 60% of the time elapsed, the rate would be 40/60 = 67%. I also want to add a vertical marker that shows the current position in the campaign timeline. Has anyone created something like this before? What’s the best approach to visualize this data in Sheets?
I built something similar for client dashboards about six months ago. The breakthrough was using a combination chart - I plotted actual vs expected delivery curves as line series, then added columns underneath showing variance percentages. For the timeline marker, I created a helper column that calculates the current date’s position as a percentage of total campaign duration, then used that to position a vertical line. The trickiest part was handling weekends and holidays - I ended up using NETWORKDAYS to get more accurate delivery expectations. One tip: add a secondary axis showing cumulative spend alongside impressions. Delivery issues often correlate with budget pacing problems, and stakeholders get way more value seeing both metrics together instead of just impression delivery.
I developed a similar tracking tool recently for my marketing projects. Utilizing conditional formatting alongside a bar chart allows for clear visual representation without unnecessary complexity. I designated a cell range for the timeline, assigning distinct colors based on the delivery rate: green for achieving or surpassing targets, yellow for nearing targets, and red for underperformance. To illustrate the current campaign position, I included a separate row that dynamically marks progress using symbols based on the date compared to the campaign period. It’s crucial to establish a solid data structure; therefore, I recommend organizing columns for each date, cumulative impressions, target impressions, and the calculated delivery rate. These references will help streamline your visualizations. Additionally, consider using Google Sheets’ sparkline function for a compact progress indicator within a single cell.
stacked bar chart with some smart formulas worked best for me. one bar shows delivered impressions, another shows what’s left to hit target. then i used sparkline with vertical line to mark where we are timeline-wise. took about 20 minutes once i got the cell refs right.