Merge sales from 1C, CRM and marketplaces into one table
The same item is called three different things in 1C, the CRM and the marketplace. Sales land in one table, with the leftovers listed openly.
Medium · 30 min · once a month
What you get
Sheet "Sales", June · 4 sources · 8,412 rows SKU Name Channel Qty Amount, ₽ KRS-101 Loft armchair Ozon 118 472,000 KRS-101 Loft chair, grey 1C 34 139,400 TBL-330 Dining table WB 76 304,000 Duplicates removed between 1C and CRM: 92 rows Sheet "Unmatched": 143 rows worth 611,000 ₽ — no shared SKU code and no barcode to match on
A sample on made-up data — your numbers will be your own.
Who it fits
- Every report starts with four exports being copy-pasted into one file.
- You need a raw consolidated base for whatever report comes next, not ready-made conclusions.
When it won't work
The products share neither an SKU code nor a barcode — there is simply nothing to match on.
How the agent does it
Set the matching rules
If you already keep an SKU mapping sheet, attach it and the agent builds on it. Say up front whether we count with or without VAT — redoing it costs more.
Run the merge
Matching goes by SKU code or barcode, overlaps between 1C and the CRM are removed, and pairs are never guessed from similar names.
Read the leftovers
Ask the agent to review its own work: where a pair came from a name, where an order landed in the period by a different date, and what the leftovers are worth.
What you set
Connect 1C, Bitrix24, Ozon and Wildberries. From you: the period, your SKU mapping sheet if you keep one, and whose price counts as primary.
What you'll need
Starter prompt
Copy the prompt or open it straight in a chat with the agent.
Merge sales from 1C, Bitrix24, Ozon and Wildberries for [period] into a single table. Match items by [SKU / barcode / the attached mapping reference], not by name. Bring units of measure and currency to a single form, and fix whether we count revenue with or without VAT. Remove duplicates: one shipment that landed both in 1C and in the CRM as a deal is one row. If an item didn't match, or matched only on a similar name — don't merge it by eye, move it to a "Not matched" sheet with the candidate options. Output an XLSX: a "Sales" sheet broken down as date — channel — unified SKU — quantity — amount, a "Not matched" sheet and a "Mapping reference" sheet that I can extend.
Open in chatReview the merge. List: — items matched by name rather than by SKU or barcode; — rows that could still be duplicates: one shipment in 1C and a deal in the CRM; — channels where revenue was taken with VAT while the others are without; — orders that fell into the period on different dates in different systems: shipment, payment, creation; — the share of rows left unmatched and what amount they carry. Whatever you can match up yourself, show as a "proposed changes" list; leave the rest to me.
A second prompt — the agent uses it to review its own work and show what's left for you.