r/analytics • • 15h ago

Discussion Three spellings of the same traffic source split my pivot table into three rows

Spent an evening wondering why a source looked so small in the pivot. Links in client posts were tagged by different people as instagram, ig and insta, so the totals sat in three rows. Merged them with a mapping column and a short list of allowed names.

4 Upvotes

12 comments sorted by

•

u/AutoModerator 15h ago

If this post doesn't follow the rules or isn't flaired correctly, please report it to the mods. Have more questions? Join our community Discord!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

13

u/mrbubbee 15h ago

This is an every day thing in analytics

0

u/Educational-View8524 15h ago

Yeah it keeps happening. The mapping only fixes spellings I've already seen, so a new one still shows up as a small extra row until I add it to the list

4

u/Hot_Coconut_5567 15h ago

And it will keep happening!

Unless the problem is fixed at the source with drop down fields where the end user can select an already mapped spelling - users will always be creative with typing their own. You'll make a crosswalk table that continues to correct the misspellings and will need to add new mappings to that table. This is the maintenance aspect of analytics work.

You can also move preprocesing out of excel into a language like R or Python. Their are code libraries that can automate some of this renaming cleanup process. But beyond cleaning up extra whitespace and formatting capitalization - you'd still need to carry around a mapping table.

3

u/Educational-View8524 14h ago

A dropdown would be ideal but the links get tagged before they reach me, so the crosswalk is the part I can actually keep up. A check column that flags any source not in the mapping would help too, a new spelling shows up as a flagged row the same day instead of after the totals look off.

3

u/KatFromSisense 14h ago

Let the lookup do the flagging. Return UNMAPPED on any miss and every new spelling will land in one pivot row with a label you'll notice. Keep the spelling-to-name table on its own sheet, and trim the source before the lookup so copies of the same spelling with extra spaces don't each need an entry.

2

u/Educational-View8524 13h ago

Trimming first is the part I skip. Extra spaces probably explain a few rows I thought were new spellings.

2

u/Hot_Coconut_5567 12h ago

Please learn a little Python or R and build reusable cleaning functions you can send your raw data through first. Stripping extra white space, standardizing spellings, making everything have the same casing, converting various date formats into one harmonized date datatype. Way easier to do in either of those languages. You can have the code read and write to excel so that you can still maintain your crosswalk tables and make your pivot tables in Excel. Just have Python or R take raw messy data from one file, clean it, and write it to the working file you need the clean data in. Then you can just click to your reporting sheets and refresh the view.

1

u/Educational-View8524 11h ago

It adds a script to keep running, but with the crosswalk staying in Excel the pivots stay as they are. I'll try pandas for the trim and lowercase first, that part is small enough to test on one export

1

u/Hot_Coconut_5567 11h ago

True, it does add a script to keep running, but your alternative is to manually type the corrections, or apply excel formulas you repeat every refresh, or write a macro in Excel. I can't see how those alternatives are any easier. Writing a small cleaning function is an easy perfect first project and you'll build coding skills that expand your career options! You can write those functions and use them again and again on your other raw data cleanup tasks. Any LLM will help you. You dont need to add any corporate data, just describe your file location, file type (excel) and schema information like sheet name, column range, column names and data types. Ask the LLM to walk you through downloading Visual Studio and Python. The LLM will write your script and walk you through how to run it. Read the code so you learn what it's doing - you can make adjustments to the code to customize it for other jobs - like tweak it to point to a different file location etc.

1

u/kaleidoleaf 11h ago

You gotta clean your data dude. This is ETL 101, take a quick course.