A Simple Spreadsheet-and-Script Workflow for Tracking Many Social Media Accounts

If you oversee numerous social media or advertising accounts, such as clients, various brands, or testing campaigns, there may have come a point where managing them becomes challenging. You might wonder which account has two-factor authentication (2FA) enabled via an authenticator app versus SMS, when the password was last updated, which recovery email is associated with which profile, which account hasn't been accessed in several weeks, and more.

You don't necessarily require a specialized software dashboard for this purpose. By utilizing a well-structured spreadsheet in conjunction with a concise 60-line Python script, you can effectively track and monitor the majority of these aspects. This article outlines the recommended schema, safety measures, and the small script that can generate a daily report highlighting items that demand attention.

Rule zero dictates that the spreadsheet should serve as an inventory rather than a vault. Avoid storing passwords, 2FA seeds, or backup codes within the spreadsheet. Instead, employ a password manager like Bitwarden, 1Password, or KeePassXC to securely store these sensitive pieces of information. The spreadsheet should store metadata, including what exists, where the secrets are located, and when the information was last reviewed.

This separation is crucial because spreadsheets tend to be shared, exported, emailed, and synced to various locations that may be overlooked. While a leaked inventory of account information may be bothersome, having a leaked inventory combined with credentials could result in a significant disaster.

The schema recommended for the spreadsheet consists of one row per account, with several useful columns. These include:

1. id: A stable, short identifier that can be referenced anywhere.

2. platform: The social media or advertising platform (e.g., facebook, twitter).

3. handle: The public name or handle for the account.

4. purpose: The reason for the account (e.g., ads-eu).

5. owner: The person responsible for the account.

6. vault_ref: The location of the account's secrets in the password manager.

7. twofa_method: The type of two-factor authentication (e.g., totp, sms, or none).

8. recovery_email: A backup email address associated with the account.

9. browser_profile: The isolated browser profile used to log into the account.

10. last_login: The date of the account's most recent login.

11. last_pw_rotation: The date the password was last updated.

12. status: The current status of the account (e.g., active, review, restricted).

13. notes: Free text for additional context or information.

To maintain data integrity and ensure that the spreadsheet remains functional, consider the following tips:

* Use data validation (dropdowns) for the platform, twofa_method, and status columns. This will prevent inconsistencies in data entry and help filter the data more effectively.

* Use ISO dates (YYYY-MM-DD) for the last_login and last_pw_rotation columns. This ensures that the dates sort correctly and can be parsed without issues.

* Utilize plus-addressing (e.g., ops+fb003@yourdomain) for recovery emails. This creates a distinct recovery address for each account while maintaining a single inbox for all accounts.

Once the spreadsheet is structured, the health checks can be automated by creating a small Python script. The script should export the sheet as CSV (or pull it via the Google Sheets API for more complex setups). The script checks for various issues, such as:

* No 2FA or only SMS-based 2FA: This is flagged since authenticator apps or hardware keys offer better protection against SIM-swap attacks.

* No vault_ref: If there is no reference to where the account's secrets are stored in the password manager, the account is considered unowned.

* Dormant accounts: If the last_login date is older than a specified number of days (e.g., 30 days), the account is flagged as dormant. This is important because some platforms treat long-idle accounts differently and may impose additional restrictions.

* Password rotation due: If the last_pw_rotation date is older than a specified number of days (e.g., 90 days), the script sends a reminder to update the password.

* Status not active: Any account with a status other than "active" is flagged for review.

By running this script daily, you can maintain a consistent monitoring process without having to manually check each account. This helps ensure that potential issues are identified and addressed promptly, saving time and reducing the risk of security breaches.