What's in the UGC tracking template
| Column | What goes in it |
|---|---|
| Creator | The creator's handle or name. Keep one spelling so totals per creator add up. |
| Platform | TikTok, Instagram, or YouTube. A video cross-posted to two platforms gets two rows. |
| Post URL | The link to the post — your proof of what was delivered. |
| Date posted | YYYY-MM-DD, so the sheet sorts and filters by week. |
| Views | The post's current view count. Update it each time you review the sheet. |
| Amount paid | What you paid for this post: fee, bonus, and any extras. |
| CPM | Calculated: amount paid ÷ views × 1,000. Blank until a post has views. |
The CPM column uses this formula on every row, so it fills in as soon as a post has views:
=IF(N(E2)>0, ROUND(F2/E2*1000, 2), "")
How to set it up
- Google Sheets: File → Import → Upload, choose the CSV, and keep “Convert text to numbers, dates, and formulas” ticked. Excel: open the CSV, then save as .xlsx.
- Delete the three example rows and add one row per post as creators publish.
- Pick one day a week to update every post's views, so CPMs compare like with like.
- Add a summary per creator with
=SUMIF(A:A, "@creator", E:E)for views and the same on column F for spend. Their CPM is spend ÷ views × 1,000.
Want to check one number without the sheet? The UGC CPM calculator does the same math.
Where a UGC tracking spreadsheet breaks
A spreadsheet is the right tool for a handful of creators. It gets expensive in time — and in payout mistakes — as the program grows:
- Views go stale immediately. A post's views keep growing for days. Every number in the sheet is only as fresh as the last time someone copied it by hand.
- Three apps, one post at a time. Each view count means opening TikTok, Instagram, or YouTube, finding the post, and typing the number in. At 50 posts a week that's hours.
- Missed posts. The sheet only knows about posts someone added. If a creator posts and doesn't send the link, it never gets tracked — or paid.
- Per-view and bonus pay multiply the errors. When pay depends on views, a stale or mistyped count is a wrong payout. Milestone bonuses also need you to notice the day a post crosses a tier.
- Paid vs owed gets tangled. Knowing which posts a payment covered — so nothing is paid twice or missed — turns into colour-coding and a second tab.
- Cross-posts and deleted posts. The same video on two platforms, or a post the creator later deletes, quietly skews totals and CPM.
That's the gap Viralytics, UGC tracking software, fills: add each creator's handles once and it finds every post across TikTok, Instagram, and YouTube, refreshes the views, calculates CPM and what each creator is owed, and records which posts you've paid for. Same columns as this sheet — kept up to date for you.
Frequently asked questions
How do I track UGC in a spreadsheet?
Give every post its own row with the creator, platform, post URL, date posted, views, and amount paid, then add a CPM column (amount paid ÷ views × 1,000). Update the views on a fixed day each week, and use SUMIF formulas to total views and spend per creator.
How do I open the template in Google Sheets?
Download the CSV, then in Google Sheets choose File → Import → Upload and select the file. Keep 'Convert text to numbers, dates, and formulas' ticked so the CPM column calculates. In Excel, open the CSV directly and save it as .xlsx.
What columns should a UGC tracking sheet have?
At minimum: creator, platform, post URL, date posted, views, amount paid, and CPM. Programs that pay per view or with bonuses usually add the rate, the amount owed, and whether each post has been paid.
When should I stop using a spreadsheet to track UGC?
When updating view counts takes longer than reviewing them — usually somewhere past 10 creators or 50 posts a month — or when you pay per view or with bonuses, because then every stale view count is a payout error. A UGC tracking tool like Viralytics pulls posts and views automatically.
Is the template free?
Yes. It's a plain CSV file with no sign-up, no email, and no macros. Use and adapt it however you like.
Related
Track every creator's views, CPM & payouts
Paste in your creators' handles and Viralytics tracks every post across TikTok, Instagram, and YouTube — automatically. Setup takes under 5 minutes.
From $40/mo · Unlimited seats · Cancel anytime