In my experience with algebraic geometry we are interested in either
- Coherent sheaves with the Zariski topology (for geometry)
- Abelian group or sets or groupoid sheaves with the etale topology (respectively useful for arithmetic geometry and moduli spaces)
How come we're not interested in etale coherent sheaves; by which I mean using the etale cohomology (Say big site) s.t on a scheme $X$, the sheaf is a $\Gamma(X)$ module (i.e similiar to the Zariski topology).
Studying coherent sheaves on the small étale site of a scheme $S$ is not tremendously interesting: according to Proposition 35.8.9 [03DX] of The Stacks Project, there is a natural functor which gives an equivalence of categories between the category of quasi-coherent sheaves on $S$ and the category of quasi-coherent $\mathcal{O}$-modules on the small étale site of $S$.
On the other hand, once you go deeper down the rabbit hole and start considering sheaves on stacks, the étale site reappears. For example, one can define quasi-coherent sheaves on a Deligne-Mumford stack by descending quasi coherent sheaves from its étale presentation by a scheme.