Macro was targeted at myself of course

Hahaha, see D3022 (and D3021) for some previous discussion (which was a hackathon project from Facebook, I think, but are about 2 years old now).

The two major issues at that time are still relevant. In summary:

1. The usage table needs to be populated in a reasonable (gradual/daemon/offline) way, not built at query time.
2. Every application needs to be supported, not just Differential.

However, we're in a much better position to attack those problems today than we were in 2012. In particular, I'd suggest this approach, which I think is mostly practical, can reasonably be implemented today, and is not particularly more complicated than your implementation:

• Modify the macro remarkup rule to expose usage counts, in the way that the @mention and #project rules expose mentioned users and projects. You can look at those rules for examples (although I think those examples are slightly more complex). The idea is that the Macro rule just saves a list of all the macro PHIDs (and maybe the number of uses) when rendering, and then the caller can later extract that. The major goal here is to avoid duplicating the logic for identifying macros.
• Modify PhabricatorApplicationTransactionEditor->expandRemarkupBlockTransactions(). Currently, this extracts #project mentions and writes links from them. Add a block of code which extracts macro PHIDs and updates the usage table.
• I think the table should look like <id, macroPHID, authorPHID, usagePHID, count> (and maybe epoch if you want to eventually implement time decay) but usagePHID needs to store a Transaction PHID (so we can update the data if the comment is edited or removed). This is a little tricky, because when you extract the macro uses the transactions won't have PHIDs yet (and the transactions may be modified later in the Editor). I don't immediately see a clean way to fix this. Moving extraction to PhabricatorApplicationTransactionCommentEditor might be one approach (it may also make edit/delete easier). For now, you could hook other things up and just use the $object->getPHID() (which will be the revision/task/etc PHID), and we can figure out how to get the transaction PHIDs populating properly later.
• To fill historic data, write a script which iterates over every transaction from every application. The skeleton should look something like this:

$xaction_classes = id(new PhutilSymbolLoader())
  ->setAncestorClass('PhabricatorApplicationTransaction')
  ->loadObjects();
foreach ($xaction_classes as $xaction_class) {
  foreach (new LiskMigrationIterator($xaction_class) as $xaction) {
    // $xaction is now an ApplicationTransaction and you can load
    // the comment and update the macro usage table.
  }
}

• ManiphestTaskQuery->buildPagingClause() has an example of building paging clauses from multiple columns, but it's not much simpler. (I think this is legitimately complicated and not something we could trivially reduce with better API, but 95% of the time the behaviors are simple so there might be room to improve the API that we just haven't figured out yet.)
• Simplifying the query capabilities (at least, in v1) might make this a bit easier. For example, you could rank/show only total popularity, and show top users on the macro detail page. That said, having "Favorites" is actually useful, so maybe this isn't really worthwhile.

The actual code mostly looks fine here, but I think the approach will be hard to move forward with.

ETL (mentioned in D3022) is also slightly farther along than it was at the time, but I think it's no longer the best approach in the modern codebase. Particularly, transactions were not generic across applications in 2012 and the approach outlined above would have been far more complicated.

This looks awesome! What's its status? From @nipunn's last comment, it looks like everything is working except for the backfill?

Whatever happened to this diff? It seems like a really useful and important feature.