Yes, it worked, at least for some things: It's a pretty straightforward treatment for a pretty straightforward injury. A blow to the head can cause bleeding in the dura, the covering for the brain. This bleeding will collect, pressing on the brain, and potentially causing death-- this is a "subdural hematoma" or "epidural hematoma"

Releasing this pressure by drill in a hole is a simple life saving procedure, and was performed in prehistoric times.

Did it work? There's very good forensic evidence that many people survived these procedures-- we can see skulls which have grown back after trepanning-- eg that proves that the patient lived some considerable time afterwards.

What we can't always tell is whether the decision to trepan was the right one. If the patient had, say, migraine headaches or hallucinations-- trepanning would likely have done them no good at all. We've got evidence that trepanning was often performed for reasons that don't seem like they're medically indicated-- so in those cases it wouldn't "work" in a contemporary medical sense.

"Usual outcomes" asks a quantitative question -- and I haven't seen the data to support a quantitative answer. We know that people did trepanning for both medically reasonable and unreasonable reasons, we know that the procedure was often survived -- though it must have killed people too-- but we can't know the numbers to determine "usual" outcomes. We do have references which suggest that in places and times where we have historical records, the procedure was considered "dangerous -- but sometimes worth the risk." You can see precisely this issue being debated by physicians treating Don Carlos (the son of Phillip II and Prince of Asturias) in the 16th century; after many failed attempts, trepanation is performed and he survives.

See:

Loughborough, John Lovell. “Notes on the Trepanation of Prehistoric Crania.” American Anthropologist, vol. 48, no. 3, 1946, pp. 416–422.

Verano, John W., et al. “Holes in the Head: The Art and Archaeology of Trepanation in Ancient Peru.” Studies in Pre-Columbian Art and Archaeology, no. 38, 2016, pp. i-322.

Villalon, Andrew. “The 1562 Head Injury of Don Carlos: A Conflict of Medicine and Religion in Sixteenth-Century Spain.” Mediterranean Studies, vol. 22, no. 2, 2014, pp. 95–134.