Challenge #411: Link a New CRM to an Existing Financial System

Hi Maveryx,

A solution to last week’s challenge can be found here.

Don't forget that we are running a campaign inviting all of you to contribute to our upcoming 2024 Weekly Challenges! Your ideas and participation are crucial in shaping an engaging and innovative year ahead. Visit our blog post to review the guidelines and submit your challenge to earn our brand-new accolade, the Weekly Challenge Contributor Badge!

A special thanks to Mark Thompson (@Watermark) for submitting this challenge some time ago. Your contribution is greatly appreciated, Mark! It is a fantastic opportunity for our users to test and enhance their RegEx tool skills.

Your company recently adopted a new customer relationship management (CRM) tool but overlooked a crucial detail: how to link company records in the new CRM to the companies in the legacy financial system. The common link between these systems is the company’s URL, but the website data entered by the sales team in the CRM is inconsistent and often incorrect.

As the person responsible for solving this issue, your tasks are to:

Match each company in the legacy financial system with its corresponding record in the new CRM.
Analyze the CRM data to identify how many entries contain “dirty data,” meaning entries with subdirectories in the URL.
Determine the number of distinct websites (base URL) that matched during the data integration process.
Identify companies within the US that have multiple opportunities in the CRM (more than one). (Hint: Use the domain URL.)
Identify companies with multiple opportunities (more than one) outside of the US.

Hint: To identify the countries, look for two-character top-level domain (TLD) codes in the URLs. Assume that any other code (for example, .com, .net, or .org) is associated with a US-based company.

If you want to learn how to use regular expressions to parse your data, you can review the following lessons in Academy:

Good luck!

AYX-Maveryx_Micro Identity-Maveryx Academy_wordmark.png

Challenge_411_start_file.yxzp

Challenge_411_solution.yxzp

Advanced

Intermediate

Data Preparation

Accepted answers

All comments

RWvanLeeuwen

here's my take

Spoiler

I did not filter down to the .net and .org because to assume is... well you know

Challenge_411_start_file - RWvL.yxmd

alexnajm

This Regex will be the death of me! 😂

Edit: I got #2 and #3 correct before having to break, I'll come back to it as I can and edit further (or repost).

Challenge_411_start_file_alex Abi-Najm.yxzp

ahsanaali

Spoiler

Challenge_411_Solution_AAA.yxzp

braveraj

Spoiler

Challenge_411_website_regex.yxmd

TurboToad

Love me some Regex! Spent way too much time on this but was fun. Had some slight variations in the answers.

Spoiler

TT_Challenge_411_solution_file.yxzp

Kenda

Spoiler

team brute force!

Challenge_411_KS.yxzp

Gawa

Data migration from legacy system is a kind of challenging task. It reminds me of the need for a proper "data catalog".

I had to make assumption on input data to complete this challenge. I ended up not matching to the result perfectly but parsing URL was a fun for me.

Spoiler

Challenge_411_start_file_gawa.yxzp

LKM

CRM Num 4712 and 16415 are incorrect in output task 1.

Some outputs in output task 4 look incorrect such as "AMERICAN-DATA.NET" and "AMFIBI.COMPANY".

Anyway it is an interesting challenge.

Spoiler

Challenge_411_LKM.yxmd

RolandSchubert

Spoiler

Challenge_411_solution_rsc.yxzp

olga_strubbe

Thank you, @Watermark and @AYXAcademy, for the challenge. It was a tough one, that's for sur Helped me to dive deeper into RegEx.

My approach follows a similar path & logic as @TurboToad - thinking this is a straightforward approach with just as few tools as possible from what I can gather

My results for tasks 2 & 5 match perfectly while tasks 1,3 & 4 are off just a tiny bit. Let me know what might need to be fixed there if you can.

Challenge_411_OS.yxmd

Aurora_M

Good challenge! That took a while and a lot of regex attempts.

Some slight variation in the outcome of task 4!

Spoiler

Spoiler!

Watermark

@olga_strubbe ,

@AYXAcademy will post a solution next week, but I will say the solution that I submitted when I did it last year involved a complete isolation of all the elements of a web URL that you see in the "HINT" on URL structure, for every entry (see the answer for #2). The beauty of course is that everyone has an approach, and there is no 1 'right' way.

mmontgomery

Yikes. Needed some chatGPT help on the regex, tbh

Spoiler

c411.yxzp

AkimasaKajitani

My solution. I tried to parse the url structure by RegEx, but as a result, some task are not matched.

Spoiler

In real business, this my direction is not so good. But in the parsing the url structure, I think that it's not so bad.

Challenge_411_start_file_AK.yxzp

Watermark

@Aurora_M ,

You're 'so' close on your answer to #4.

Here's a couple hints, read the question 'very' carefully.

You didn't provide your flow so I can't be 100%, but think I see it from your notations. If all else fails, compare your results and provided results to identify discrepancies. You'll see it. Good luck.

TurboToad

@olga_strubbe , being as I've stared at this way too long I was happy to take a look. There was just a few records your expression was missing. I made some slight tweaks and this should resolve the mismatches.

These were the websites your expression wasn't quiet parsing correctly:

HTTP://WWW.WWD-INC.COM
HTTP://WWW.WWS-CPA.COM
WWW--EMBROIDME-COCKEYSVILLE--COM.RTRK.COM
WWW--EMBROIDME-FULLERTONMD--COM.RTRK.COM
HTTP://WWW-REDIRECT.EXT.HP.COM

2 of them have extra W's in front the url and the others have the hyphens between the WWW. I tweaked your expression and this should resolve it.

Spoiler

(HTTPS?\:\/\/)?(ww[w\d]*(?:\.w{2})?\.?)?([a-z0-9\-\.]*)([\/\?:].*)?$

Non-capturing group

(?:\.w{2})?

? matches the previous token between zero and one times, as many times as possible, giving back as needed (greedy)

\. matches the character . with index 4610 (2E16 or 568) literally (case insensitive)

matches the character w with index 11910 (7716 or 1678) literally (case insensitive)

{2} matches the previous token exactly 2 times

matches the character . with index 4610 (2E16 or 568) literally (case insensitive)

? matches the previous token between zero and one times, as many times as possible, giving back as needed (greedy)

There are also 2 records in task #2 that I believe are not correct in the solution:

Field:Domain URL Row:206 Expected:B-S-CORP.COM Actual:D-B-S-CORP.COM

Field:Domain URL Row:641 Expected:AND-INSPECTION-SERVICE.COM Actual:STATE-CERTIFIED-HOME-BUILDER-AND-INSPECTION-SERVICE.COM

balajilolla2

Spoiler

Challenge_411_start_file.yxmd

TurboToad

@olga_strubbe, I took a look at your solution and think I can see why you're getting the variances.

There's a few oddball formatted websites that your expression isn't extracting the Base URL correctly.

I made a few tweaks to your expression and this get's it to match up.

Spoiler

(HTTPS?\://)?(ww[w\d]*(?:\.w{2})?\.?)?([a-z0-9\-\.]*)([/\?:].*)?$

yyGuo

Only task4 has a different result

Spoiler

Challenge_411_start_file_yyGuo.yxmd

HCJ1212

Spoiler

Challenge_411_start_file HJ.yxmd

olga_strubbe

Re-publishing my solution . Big thanks to @TurboToad for helping me correct my RegEx formula - now I am matching the solution output, yay!

ch 411.png

Challenge_411_OS.yxmd

MitchyMitch

Got everything parsed, but took a few different attempts before getting all the outcomes to match the solutions. Good Regex practice! Pretty proud of myself on this one! 😊

Spoiler

Challenge_411_MA.yxmd

alacoume

Not easy ! I have only done task 1...

Challenge_411_start_file.yxmd

AGomes4

Spoiler

Challenge_411_Cleansed.yxmd

RashedDS

Challenge_411_start_file.yxzp

Dev73

here is my take 📌

Spoiler

Challenge_411_Dev.yxmd

sergejs_kutkovics

Better later than never! My try

RegEx is always very fun. Thank you Mark Thompson (@Watermark)!

Spoiler

Also I'd say @olga_strubbe initial answer was correct. I've got the same five rows difference as she have.

Spoiler

IMHO we can't just drop any WW sequence from URL, e.g. from 'HTTP://WWW.WWD-INC.COM' I expect domain to be 'WWD-INC' not 'D-INC'.

Challenge_411_SK.yxmd

Watermark

@sergejs_kutkovics , @olga_strubbe , @LKM , @TurboToad ,

You are all correct, and in a real world situation, I'd look at your results and contact the finance group to let them know that you were confident that there are 2 entries that need to be updated in the base finance data.

Bravo for those who have found the discrepancy, it's very subtle.

3728

9857

AYXAcademy

Well spotted @LKM !

We will ensure that the accurate response is posted.

Thanks for the heads-up!

😉

Reesetrain2

Spoiler

Challenge_411_Reesetrain2.yxzp

Qiu

Done

Spoiler

Challenge_411_start_file-Qiu.yxzp

K_Vinogradov

This definitely wasn't an easy weekly challenge, and I only managed to perfectly match tasks #1, 3 and 5, with some variations in #2 and #4 since I didn't manage to correctly parse all subdomains and URL TLDs.

Will definitely check out other solutions as I found hard to solve it without overfitting the solution to the data. I can already see some great solutions that I wish I had thought about sooner!

Challenge_411_Solution.yxmd

philipmannering

Solution attached.

Spoiler

Challenge 411 - Link a New CRM to an Existing Financial System.yxmd

Watermark

Have to keep my count up for the totals posted, here's my version.

Spoiler

Challenge_411_MT_Solutionp.yxmd

ggruccio

I struggle with super-complex Regex expressions so I waited for the solution and learned what I could. The path I started down required way too many manual corrections to add missing results. I'm also not as familiar with URL structure so was a good learning exercise.

Challenge_411_solution.yxzp

nSpire

Thanks @olga_strubbe for the inspiration!

Spoiler

Challenge_411_nSpire.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15873

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703