How to find your video IDs and delete those you no longer use

[Gapuccino]

Hello, I have a question.
Is it possible to obtain the IDs of the videos on my sites?
I mean, there are many videos that are no longer in use and I would like to delete them. I know that a script could be written to do this. Could you help me understand how this process works?

[radominchev]

Hi @Gapuccino ,

Yes — it’s absolutely possible to obtain the IDs of all videos published on your Arc XP sites and identify which ones are no longer in use.

To make this easier, we’ve prepared a Bash script that connects to the Arc Content API, lists all type:video items, and checks how many stories reference each video.

The script generates two CSV reports:

• videos_audit.csv — all videos with their usage counts
• videos_unused.csv — only videos that are not referenced anywhere
  You can run it locally on macOS, Linux, or Windows (WSL).

---

Prerequisites

Make sure you have curl and jq installed:

sudo apt install jq curl -y      # Debian/Ubuntu
# or
brew install jq                  # macOS

---

⚙️ Steps to Run

1. Save the provided script as arcxp_video_audit.sh and make it executable:

   chmod +x arcxp_video_audit.sh

2. Export your Arc XP credentials and configuration in your terminal:

   export ORG="env.your-org"
   export WEBSITE="your-website"
   export TOKEN="YOUR_BEARER_TOKEN"
   # Optional: specify sandbox or staging environment
   export API_HOST="https://api.sandbox.your-org.arcpublishing.com"

3. Run the script:

   ./arcxp_video_audit.sh

4. When finished, you’ll see:

   All videos CSV:       /path/to/videos_audit.csv
   Unused videos CSV:    /path/to/videos_unused.csv
   

5. Open the files in Excel or Google Sheets to review results.

---

Copy an save the script locally -> arcxp_video_audit.sh & execute the steps above

#!/usr/bin/env bash
set -euo pipefail

# -------- CONFIG --------
# Your Arc org shortname, e.g. "sandbox.myorg"
ORG="${ORG:-<env>.<org>}"
# Website handle exactly as defined in Arc (e.g. "the-herald")
WEBSITE="${WEBSITE:-<site>}"
# Arc Bearer token with Content API access
TOKEN="${TOKEN:-<token>}"
# Page size for search (max sensible ~100)
SIZE="${SIZE:-50}"
# Base API host (switch to sandbox if needed)
API_HOST="https://api.${ORG}.arcpublishing.com"

# Output files
ALL_CSV="videos_audit.csv"
UNUSED_CSV="videos_unused.csv"

# -------- INIT --------
echo "video_id,title,display_date,usage_count" > "$ALL_CSV"
echo "video_id,title,display_date,usage_count" > "$UNUSED_CSV"

# Track pagination
FROM=0
TOTAL=-1

echo "Enumerating videos for website: ${WEBSITE}"

while : ; do
  SEARCH_URL="${API_HOST}/content/v4/search/published?website=${WEBSITE}&q=type:video&sort=display_date:desc&size=${SIZE}&from=${FROM}&_sourceInclude=_id,headlines.basic,display_date"
  RESP="$(curl -sS -H "Authorization: Bearer ${TOKEN}" "$SEARCH_URL")"

  # On first page, capture expected total hits (if available)
  if [[ "$TOTAL" -lt 0 ]]; then
    TOTAL="$(echo "$RESP" | jq -r '.hits.total.value // .hits.total // 0')"
    echo "Estimated total videos: ${TOTAL}"
  fi

  COUNT="$(echo "$RESP" | jq -r '.content_elements | length')"
  if [[ "$COUNT" -eq 0 || "$COUNT" == "null" ]]; then
    break
  fi

  # Iterate videos on this page
  echo "$RESP" | jq -c '.content_elements[]' | while read -r vid; do
    VID_ID="$(echo "$vid" | jq -r '._id')"
    TITLE="$(echo "$vid" | jq -r '.headlines.basic // ""' | tr '\n' ' ' | tr -d '\r' | sed 's/"/""/g')"
    DISP_DATE="$(echo "$vid" | jq -r '.display_date // ""')"

    # Count how many stories reference this video ID
    # Query: stories that contain a video element with matching _id
    # Using track_total_hits to get an accurate count
    REF_Q="type:story+AND+content_elements.type:video+AND+content_elements._id:\"${VID_ID}\""
    REF_URL="${API_HOST}/content/v4/search/published?website=${WEBSITE}&q=$(python3 - <<EOF
import urllib.parse
print(urllib.parse.quote('''$REF_Q'''))
EOF
)&size=1&track_total_hits=true&_sourceInclude=_id"

    REF_RESP="$(curl -sS -H "Authorization: Bearer ${TOKEN}" "$REF_URL")"
    USAGE_COUNT="$(echo "$REF_RESP" | jq -r '.hits.total.value // .hits.total // 0')"

    # Write to CSV (quote the title)
    echo "${VID_ID},\"${TITLE}\",${DISP_DATE},${USAGE_COUNT}" >> "$ALL_CSV"

    if [[ "$USAGE_COUNT" -eq 0 ]]; then
      echo "${VID_ID},\"${TITLE}\",${DISP_DATE},${USAGE_COUNT}" >> "$UNUSED_CSV"
    fi
  done

  # Next page
  FROM=$((FROM + SIZE))
  if [[ "$FROM" -ge "$TOTAL" && "$TOTAL" -ge 0 ]]; then
    break
  fi
done

echo "Done."
echo "All videos CSV:       $(pwd)/${ALL_CSV}"
echo "Unused videos CSV:    $(pwd)/${UNUSED_CSV}"

Hope this helps!
Rado