Query for a month works faster then for a week
(Some of this is redundant, but I will spell it out anyway.)

I think 5.6.23, with MRR, is smart enough to use all 3 columns of INDEX(site_id, type, created_at) in that order.  (Older versions would not work as well.)

If you are using a 'range' of created_at values, nothing after that column will be used in optimization.  (Unless it is a 'covering' index.)

Instead of using OFFSET, "remember where you left off":  pagination blog.

With 170M rows, you should consider tightening up the datatypes wherever possible.  For example, can type changed to a 1-byte ENUM or normalized to a 1-byte TINYINT UNSIGNED (1 byte) instead of VARCHAR(25)?  subtotal decimal(25,2) takes 11 bytes.  If you don't need the precision of DECIMAL, consider an 8-byte DOUBLE.  Are there more than 16M sites?  If not, consider a 3-byte MEDIUMINT UNSIGNED instead of a 4-byte INT SIGNED.  Etc.

In place of USE INDEX.. this might be safer: IGNORE INDEX(PRIMARY).